This is the 51th episode of SQL in Sixty Seconds Video and we will see in this episode how to RESEED identity of the table column. Identity column is every increasing (or decreasing) value based on the interval specified in its property. In today’s SQL in Sixty Seconds video we will see that how we can reseed the identity value to any other value. In the video I demonstrate that we can set the value to any value which is greater than the current column value however, you can also set the identity value to any value lower than the current column as well.

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.

I HAVE TO SELECT A VARCHAR COLUMN HAVING LENGTH 4 OR 5 CHARACTERS I HAVE TO PUT ‘000’ (ZEROS) BEFORE THIS COLUMN IF IT’S LENGTH IS 4 THEN I HAVE TO PUT 4 ZEROS BEFORE IT’S AND IF IT’S LENGTH IS 5 THEN I HAVE TO PUT 3 ZEROS I HAVE TO MAKE TOTAL LENGTH EQUAL TO 8
LIKE THIS
SELECT POSTCODE,
CASE LEN(POSTCODE) < 8 THEN
WHILE LEN(POSTOCDE) < 8 LOOP
POSTCODE = '0'+POSTCODE
END LOOP
END
FROM ABCTABLE.

or in a scenario where so many rows are deleted and inserted, that eventually you reach the maximum of the identity column’s data type, while there are substantially less records in the table than is possible to hold by the identity column. In these scenarios working through foreign key properties gets a little tiresome when reseeding though!

Community Initiatives

About Pinal Dave

Pinal Dave is a Pluralsight Developer Evangelist. He has authored 11 SQL Server database books, 17 Pluralsight courses and have written over 3200 articles on the database technology on his blog at a http://blog.sqlauthority.com. Along with 11+ years of hands on experience he holds a Masters of Science degree and a number of certifications, including MCTS, MCDBA and MCAD (.NET). His past work experiences include Technology Evangelist at Microsoft and Sr. Consultant at SolidQ. Follow @pinaldave
Send Author Pinal Dave
an email at pinal@sqlauthority.com

Email Subscription

Enter your email address to subscribe to this blog and receive notifications of new posts by email.