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.