SDU Tools: Reseed Sequences in T-SQL
Prior to SQL Server 2012: IDENTITY Columns
When we needed to automatically number rows in SQL Server prior to SQL Server 2012, one option was to use IDENTITY columns.
(Apologies to purists who think these were the spawn of the devil but real people did use them).
One of the challenges with IDENTITY columns was when you wanted to move data from table to table but needed to retain the same value from the IDENTITY column. To do that, we used SET IDENTITY_INSERT tablename ON; That was fine but an issue that arises, is that if the next IDENTITY value was going to be 245001 and you just inserted a row with 245001, what would happen with you turned IDENTITY_INSERT back off and tried to insert another row.
The first thing to understand is that IDENTITY columns aren’t by default unique. If however, you had used them as a unique value with either a UNIQUE constraint, or as a PRIMARY KEY, then it was the constraint that made them unique, not the fact that they were IDENTITY columns.
So let’s assume that you really didn’t want two rows with the value 245001, so how did we fix it? The tool that helped was DBCC CHECKIDENT. It allowed you to check the current identity value, but more importantly, if you had just inserted a bunch of rows, you could use it to reseed the IDENTITY value up past the last value that you inserted (or that was already there).
SQL Server 2012 and Sequences
OK, so now SQL Server 2012 came into the picture. We can use sequences instead of IDENTITY columns, and we usually prefer to do so for two reasons:
IDENTITY columns have weird behaviors and don’t work everywhere. For example, you can’t set IDENTITY INSERT ON for a table on a linked server.
Sequences can be used across multiple tables. For example, if I want to have a single sequence called BookingID but used in the HotelBookings table, the CarBookings table, and the FlightBookings table, that works just fine.
One of the things that’s been missing with sequences though, is the equivalent of DBCC CHECKIDENT. You can see the current sequence values by querying sys.sequences.
Well that almost works. Unfortunately it’s design is broken and it returns the same value for a sequence that’s never been used as for one that’s been used once. We get around that by always using a sequence once immediately after creating it.
Where the fun begins though, is when you’ve used the next value for a sequence as a column default, and you’ve inserted a bunch of rows, how do you automatically point the sequence value past all the values in the table? Worse, what if the sequence was used as the default for more than one column in different tables?
One of our free SDU Tools is designed to help. ReseedSequenceBeyondTableValues does exactly this, as you can see here:
The sequence Sequences.CustomerID was used in the WideWorldImporters database and this procedure found that the highest value that was used anywhere in the database was 1061 so it’s set it to 1062 as the next value to be used.
But I need to fix all the sequences in my database!
We’ve got you covered here too An associated tool ReseedSequences can reseed a whole lot of sequences all at once. You can choose to do a list of them like this:
Or you can choose to do all the sequences in a database as shown in the main image above.
You can see them both in action here:
For more information and downloads for SDU Tools and our other resources, register to become one of our SDU Insiders. You’ll find more info here:
http://sdutools.sqldownunder.com
2018-02-14