I have a employe table which has SlNo as Identity column. Currently the identity is 100. Now due to some reason last 50 rows (SlNo 51-100) has been removed. Now write a synatx to reset the identity to 51.

Answer:

To Reset the identity we need to use the following statement. But caution to be taken that the identity column should not be the primary key.

DBCC CHECKIDENT('tbl_Employee', RESEED, 51) 

On executing the above query the output will be

Checking identity information: current identity value '18', current column value '51'.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.