How to find the last identity value in a table ?

In SQL server you can get the last value of the identity column in table using the Ident_Current function.

Syntax

IDENT_CURRENT('TABLE_NAME')

Example

Select IDENT_CURRENT ( 'StudentDetail')

Output

11

In the studentdetail table the last generated value in the identity column is 11.

Note : The table name should always have the identity column.

How to insert the Value in the Identity column of the table ?

In SQL server each table has identity column to auto increment the value , but there is sometimes we need to insert the value in the identity column.

You can use the following syntax to insert the value manually.

Syntax

To Switch ON 

SET IDENTITY_INSERT TABLE_NAME ON

To Switch OFF

SET IDENTITY_INSERT TABLE_NAME OFF

Example

In the below example i tried to insert the value in the identity column of the identitytable, After executing the below query i got the error message in the messages tab as shown below.

After i switched ON the below property and try to execute the statement still i am getting the error message but the message is different.

If we switched ON the below property we have to use the column list in the insert statement.

After using the columns in the insert statement it works fine and executed successfully.

Do not forget to switch OFF the IDENTITY_INSERT property.

Use of DATALENGTH function in SQL server ?

DATALENGTH function in SQL server is used to return the no of bytes used by the expression.

SYNTAX

DATALENGTH (EXPRESSION)

EXAMPLE

The following example demoanstrates the use of datalength function in SQL server.

In the above example i have passed the EmailAddress column to the DATALENGTH function.

In the Results tab the data_length column is showing the corresponding datalength for the row.

The emailaddress is nvarchar data-type and it takes 2 bytes per character, so it returns the value as double of the length column.

Using the above DATALENGTH function we can easily find the bytes used by the expression.

The bytes occupied per each character will be differ for different data-type.

How to find the data file location of the Database ?

While creating the database the two types of data files such as MDF & LDF will be created in the drive.

If we want to see the data file location of the particular database we can find the location via T-SQL query.

SYNTAX

SP_HELPFILE

EXAMPLE

I just executed the below query to find the location of the adventure works database in my instance.

The above command will return the name of the data files , location and its filegroup etc as shown above.

You can easily identify the location using the above command in your instance.

How to Get the Running query text using DMV ?

Following is the one of the way to find the running query in SQL server.

In the query window 1 i ran the below query and  my server process ID is 55 and it is highlighted below.

For demonstration purpose i used the following query.

In the query window 2 i ran the below query to find the running query as shown below.

How to find the time of running query in MilliSeconds?

In SQL server we always found the running query in Seconds but we have never the seen the time in Milliseconds.

To find the time in Milliseconds we can use the following Syntax.

SYNTAX

SET STATISTICS TIME ON

T-SQL STATEMENT

SET STATISTICS TIME OFF

EXAMPLE 

The following example i used the STATISTICS TIME ON & OFF statement to find the time take by the query in milliseconds.

In the message tab we can find the CPU time & Elapsed time in milliseconds as shown below.

How to find all the user defined Stored Procedure in SQL server ?

In SQL server we can get the name and definition of the stored procedure & function using System View called INFORMATION_SCHEMA.

The following example provides you the list of stored procedure in the AdventureWorks database.

We can get the definition of the stored procedure in the ROUTINE_DEFINITION Column.

In above query i used the Routine_Type as PROCEDURE , So it returns only the stored procedure list and you can get the list of functions by passing the Routine_Type as Function.

How to find the Database last Restored Date ?

In SQL server we have used many times to restore the database for Developing and testing purposes, But we don't consider the restored date.

In SQL server we have the option to find the restored date of a particular database in the MSDB database.

There you can find the restored date in the Restore_date column and the database name etc. 

How to Get the Running query text using SPID ?

While performing the tuning in SQL server sometimes we found that some of the queries are running for a long time and it would block the other transaction.

To get the running query in SQL server we can use the Database Console Command (DBCC) to get the running query.

SYNTAX

DBCC INPUTBUFFER (SPID)

If you know the SPID of the blocking query then the above command would be helpful to get the blocking query.

EXAMPLE

In the query window 1 i ran the below query and  my server process ID is 55 and it is highlighted below.

For demonstration purpose i used the following query.

In the query window 2 i ran the DBCC command by passing the Server Process ID obtained from the previous query window 1 as shown below.

After executing the above command I found the Running query in the EventInfo column as shown above.

From this you can find the running query in SQL server.
Refer here to find another way of getting the running query text.

Use of SPID in SQL server ?

SPID stands for Server Process ID. SPID returns the session ID of the Current User Process.

In SQL server each session is identified by its own SPID.

If you get the SPID then you can get the running query and also possible to KILL the query.

SYNTAX

SELECT @@SPID

EXAMPLE

The following example provide you the clear idea about the Session ID of the session.

In the above example i have used the system function @@SPID to get the session ID.

It returns ID value as 55.

Without using the system function you can also get the Session ID at the bottom of the query window (Highlighted in Red Box)

This session ID will be helpful when you identifying the blocking queries in SQL server.

Use of NOWAIT in SQL server ?

If the requested table is locked by any other transaction then you can make use of NOWAIT in SQL server.

SYNTAX

SELECT * FROM TABLE_NAME WITH (NOWAIT)

EXAMPLE

SELECT * FROM StudentMarks WITH (NOWAIT)

If the requested table is locked by any other resources then the following error message will appear

Msg 1222, Level 16, State 45, Line 2

Lock request time out period exceeded.

From the above message we can identify that the table is locked by other resources, So that you can wait for sometime to read the data.

How to Rebuild the Index online in SQL server ?

The following option in SQL server provides you to rebuild the index online.

SYNTAX

ALTER INDEX INDEX_NAME

ON TABLE_NAME REBUILD WITH (ONLINE = ON)

EXAMPLE

ALTER INDEX IX_NAME

ON StudentMarks REBUILD WITH (ONLINE = ON)

Use of NOLOCK in SQL Server ?

To Read the Not committed data in SQL server we can use the NOLOCK option.

Sometimes if you are trying to read the data from the table you can notice that the query is executing for a long time, because the table is locked by other transaction.

Once the transaction is completed the lock will be released from the table then you can the see the data in the table.

To avoid this we can use the NOLOCK option to read the uncommitted data in the table.Following example explain how to use the NOLOCK option in SQL query.

Example

Select * from StudentDetail (NOLOCK)

You can also use the With Keyword as shown below.

Select * from StudentDetail WITH (NOLOCK)

If you want to read the uncommitted data you can go with this option else you need to wait for the transaction to commit.

How to Fix 'int' is not a recognized CURSOR option Error ?

The following example simulate the  'int' is not a recognized CURSOR option error.

We declare the int variable without using '@' Symbol. For all variable declaration we need to use the @ symbol as prefix to avoid the above error.

In above example we declared int variable so that the error is showing as int is not recognized. In case if you declare float variable the error will be
'float' is not a recognized CURSOR option

How to find the Disk Space Used by SQL tables ?

In SQL server management studio we have the option to view the Disk space used by the tables.

Select the Database --> Go to Reports --> Select Standard Reports --> Disk Usage by Top Tables as shown below.

Once you selected the above option the following report will appear, there you can see the total no of records and its disk space used by the tables

How to create a Maintenance Plan in SQL server ?

Following are the steps to create the maintenance plan in SQL server.

Step 1

Go To Management -- > Maintenance Plan --> Right Click -->Select New Maintenance Plan as shown below.

Step 2

Provide the Name of the maintenance plan as given below.

Step 3

The toolbox  will appear automatically and from the toolbox select the Backup Database Task as shown below.

Step 4

After selecting the Task, just drag and drop it as shown below.

Step 5

The following backup database task window will appear once you double clicked the back up task.

Step 6

You can choose the required database and you can ignore the database if the database is offline by choosing the below checkbox as shown below.

Step 7

You can choose the path for the backup file to store and choose the back compression as well.

Step 8

You can generate the T-SQL script using the following T-SQL option as shown below.

Step 9

You can schedule the job by clicking the following button as shown below.

Step 10

You get to see the Job schedule properties and here you can give the job name for easy identification and select the schedule type as well.

Enabled option to be checked always.

Step 11

You can give the occurrence of the job such as Daily,Weekly and Monthly as shown below.

Choose the occurrence time and duration as well.

Step 12

Once you done with your changes click OK button.

Step 13

Save the maintenance job as shown below.

Step 14

You get to see the created plan under the management option and also the job under the jobs folder as shown below.

.

Step 15

You can start the job by selecting the Start Job at Step as shown below.

Finally we have created the Maintenance Plan successfully.

NULLIF function in SQL server ?

NULLIF function in SQL server is used to check the two given expression are equal.

Syntax

NULLIF (Expression,Expression)

Example 

In above example the function check for the both expression value, it returns the null value if the both expression are equal else it will return the first expression value.

This function is equal to case function in SQL server.

How to get the Host-Name in SQL server ?

You can get the Host Name in SQL server using the following function.

Syntax

HOST_NAME()

Example

Select Host_Name()

This will be very helpful if you want to trace the query using SQL server profiler.

How to import data from Excel Using SSMS ?

Following are the steps to import data from Excel to SQL server.

 Right Click the database --> Select Tasks --> Import Data as shown below.

  

 Click Next button.

Choose the data-source as Microsoft Excel and select the excel path which you want to import.

If the first row has column names then select the option as shown below.

Choose the destination source as SQL server native client and choose the authentication based on your configuration.

If you want to copy entire columns and its data you can choose the first option.

You can choose the second option if you want to transfer the data based on query.

You can choose the sheet from the excel source and give the destination table name in the destination as shown below.

You can also change the destination table data type by choosing the edit mapping option.

Once done click next option.

Click finish button to import the data.

As you can see the data has been imported successfully and you can see the no of rows transferred in the below image.

 After importing the data you can check the destination table name by running the following query as shown below.

The excel data has been imported successfully.

How to generate scripts in SQL server ?

Following are the steps to generate the scripts in SQL server.

Step 1 

Right Click the database -- > Select Tasks -->Generate Scripts as shown below.

 Step 2 

Click Next button

Step 3

You can choose any database from the following wizard and to generate scripts for all database objects choose the option "Script all objects in the selected database".

Step 4

Here is the customization wizard to generate the scripts based on our requirement.

You can generate the data by changing the script data option as shown below.

Step 5

Here you can choose the objects which you want to generate scripts by choosing the below option.

Step 6

I have selected only the tables for demo purpose and in the following wizard i have selected two tables as shown below.

Step 7

The following wizard is the output option for script file.

You can save the scripts to new file or in a new query window as shown below.

Step 8

Finally the following wizard is the summary wizard to view the selected options.

Step 9

After clicking finish button you can see the script has been generated in the new query window as shown below.