Archive for March 2018

How SQL Server Executes a Query   1 comment

For every developer whether he is fresher or experience should know how SQL internally execute the query in order to dig if there is challenge in future related to performance.

  • whenever there is new request come for the SQL server in the form of SQL statement then, request created and pass to SQL server using network protocol, this task is queued in Task Queue and wait for the worker to take it further.
  • Idle worker take the task and pass to execution unit, task execution unit perform the syntax checking using parsing and compilation process and once that is completed it look for the best execution plan using optimization process.
  • Once execution plan is prepared, Operators access the data through buffer pool.
  • Buffer pool is responsible for extracting data from file system (database).
  • Data returns to the requested client and not wait for the completion, the worker process process not released till complete data extracted and return to the calling program.
  • Once task completed, worker returns to the idle state.

 

Advertisements

Posted March 18, 2018 by Izharuddin Shaikh in SQL

Tagged with

Best way to build LDF   Leave a comment

Generally, when the log file size become very huge and even after shrinking the same still we face file size issue in that case we take an action to regenerate the new LDF file for our database. There are different ways to achieve the same, but the best way to achieve it is as follow:

  1. use sp_helpdb yourdbname and note down the path where .mdf and .ldf files are stored.
  2. execute the sp_detach_dp yourdbname command to detach the database Syntax
    sp_detach_db [ @dbname = ] 'dbname' 
                 [ , [ @skipchecks = ] 'skipchecks' ]

    example:

    EXEC sp_detach_db 'youdbname', 'false'
  3. Move the original database files to some other location or rename them.
  4. Create database with same name using CREATE DATABASE yourdbname command.
  5. Stop SQL server and replace the .mdf file.
  6. Start SQL server.
  7. Database will be shown in pending recovery mode.
  8. Issue below list of commands to bring the database online

Use Master

Go

Alter Database yourdbname Set Emergency

Go

Alter Database yourdbname Set Single_User

Go

DBCC CheckDB(yourdbname, Repair_Allow_Data_Loss)

Go

Alter Database yourdbname Set Multi_User

Go

Alter Database yourdbname Set Online

Go

 

Note*: You will receive error messages but database will be online.

 

Posted March 13, 2018 by Izharuddin Shaikh in SQL

Tagged with

Data types   Leave a comment

Data Types:

String, Boolean, Null, Object, Integer, Double, Min/Max Keys, Arrays, TimeStamps, Symbol, Date, Binary Data, ObjectID, RegularExpression etc.

Posted March 11, 2018 by Izharuddin Shaikh in MongoDB

Tagged with

Aggregate Functions   Leave a comment

Below are the aggregate functions:

$Sum

$avg

$min

$max

$push

$addtoset

$first

$last

Posted March 11, 2018 by Izharuddin Shaikh in MongoDB

Tagged with

Useful Functions   Leave a comment

Below are some of the useful functions

Limit(n): To limit the number of records.

Skip(n): Skip the number of records.

Sort({key:1}): Sort the data.

ensureIndex(): To create index.

 

Posted March 11, 2018 by Izharuddin Shaikh in MongoDB

Tagged with

Collection   Leave a comment

Collection is similar to table.

Below are some basic commands for collection related operations:

db.createCollection(“name”): Create the collection.

db.collectionname.insert(“”): Insert the data in collection.

db.collectionname.drop(): Drop the collection.

db.collectionname.find(): Search the record in collection.

db.collectionname.findone(): Top one in collection.

db.collectionname.find().pretty(): Return the formatted data.

show collections: Return all the collections.

db.collectionname.remove(criteria, 1): Delete record.

db.collectionname.save(): Replace the data.

db.collectionname.update(): Update data.

 

 

 

Posted March 11, 2018 by Izharuddin Shaikh in MongoDB

Tagged with

Create / Drop Database Commands   Leave a comment

  1. MongoDB use DATABASE_NAME is used to create database. The command will create a new database if it doesn’t exist, otherwise it will return the existing database.   Example: use DATABASE_NAME
  2. To drop the database use db.dropDatabase() command. Make sure you have selected correct database using use Database_Name command.

Posted March 10, 2018 by Izharuddin Shaikh in MongoDB

Tagged with ,

%d bloggers like this: