SQL SERVER – RESEED Identity Column in Database Table – Rest Table Identity Value – SQL in Sixty Seconds #051

In the real world, developers sometimes delete few rows from the table and due to any reasons they want to reseed the identity value to a lower value, this feature can be very much useful. However, there is one question which keeps on ringing in my mind that is why would anyone need to reseed identity. Anyway here is the SQL in Sixty Seconds Video which explains how we can reseed identity value.

Let us see the same concept in following SQL in Sixty Seconds Video:

Here is the script used in this episode:

USE tempdb
GO-- Create TableCREATE TABLE TestTable (ID INT IDENTITY(1,1), Col1 VARCHAR(100));-- Insert TableINSERT INTO TestTable (Col1)SELECT 'First'UNION ALLSELECT 'Second'UNION ALLSELECT 'Third'UNION ALLSELECT 'Fourth'UNION ALLSELECT 'Fifth';-- Select dataSELECT *FROM TestTable
GO-- Returns current Identity ValueDBCC CHECKIDENT ('TestTable', NORESEED);-- Resets the current Identity value to maximum valueDBCC CHECKIDENT ('TestTable', RESEED, 11);-- Insert TableINSERT INTO TestTable (Col1)SELECT 'First'UNION ALLSELECT 'Second'UNION ALLSELECT 'Third'UNION ALLSELECT 'Fourth'UNION ALLSELECT 'Fifth';-- Select TableSELECT *FROM TestTableORDER BY ID;-- Drop TableDROP TABLE TestTable;




http://blog.sqlauthority.com/2013/05/08/sql-server-reseed-identity-in-table-column-rest-table-identity-value-sql-in-sixty-seconds-051/