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.