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.

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.

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.

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.

ROWCOUNT_BIG function in SQL server ?

ROWCOUNT_BIG() function is used to return the no of rows affected by the last statement.

This function operates like @@ROWCOUNT function in SQL server.

@@ROWCOUNT  Returns INT datatype but ROWCOUNT_BIG returns BIGINT.

Syntax

ROWCOUNT_BIG ()

This function returns the no of affected by the last statement such as select, insert, update and delete statements.

Use of @@RowCount function in SQL server ?

To get the total No of Rows affected by the last statement, we can use the system function @@Rowcount in SQL server.

Syntax

@@ROWCOUNT

Example

In the above example, we are assigning the value of @@Rowcount to the int variable.

Based on the value obtained in the variable we are printing the No of Rows.

Instead of assigning the value we can directly check the value using @@Rowcount (e.g If (@@Rowcount > 0))

If the no of rows are more than 2 Billion  then use RowCount_Big. function.

Set NoCount ON statement will not impact the @@Rowcount function.

Note : This @@Rowcount function will return only the no of rows affected by Previous statement.(i.e the Statement before the @@Rowcount function)