This is a script that I wrote to help me next time I might need to bulk reseed out-of-sync SQL Server sequences tied to default constraints.
Setting the scene
Someone, probably with more experience on Oracle* than with SQL Server, decided it was a good idea to use sequences with default constraints for primary key columns.
*I blame Oracle-oriented people here because, from my experience, they seem to have a preference for sequences instead of IDENTITY columns.
Probably because Oracle themselves couldn’t wrap their heads around the concept of IDENTITY columns until Oracle 12c, much later than Microsoft’s implementation in SQL Server.
Demo sequences and tables
So, let’s say we have 3 tables referencing sequences.
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 | /*Create sequences*/ CREATE SEQUENCE [sq_tab1] START WITH 1 INCREMENT BY 1 ; CREATE SEQUENCE [sq_tab2] START WITH 1 INCREMENT BY 1 ; CREATE SEQUENCE [sq_tab3] START WITH 1 INCREMENT BY 1 ; GO /*Create tables and indexes/constraints*/ CREATE TABLE [tab1] ( [id] INT NOT NULL CONSTRAINT [df_seq_tab1_id] DEFAULT NEXT VALUE FOR [sq_tab1], [some_column] NVARCHAR(128)); GO CREATE UNIQUE CLUSTERED INDEX [cix_tab1] ON [tab1]([id]); GO CREATE TABLE [tab2] ( [id] INT NOT NULL CONSTRAINT [df_seq_tab2_id] DEFAULT NEXT VALUE FOR [sq_tab2], [some_column] NVARCHAR(128)); GO ALTER TABLE [tab2] ADD CONSTRAINT [pk_tab2_id] PRIMARY KEY CLUSTERED ([id]); GO CREATE TABLE [tab3] ( [id] INT NOT NULL CONSTRAINT [df_seq_tab3_id] DEFAULT NEXT VALUE FOR [sq_tab3], [some_column] NVARCHAR(128)); GO ALTER TABLE [tab3] ADD CONSTRAINT [uq_tab3_id] UNIQUE ([id]); GO |
Note: I’ve used different objects to enforce uniqueness
Someone then loads some data, but…
Said data also has values for the id columns of those tables.
Which means that the default constraints don’t have any reason to be used and pull new values from the sequences.
The origin of the records doesn’t really matter.
They can be from csv files, insert statements generated on another database with the same structure, etc.
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 | /*lazy way to generate some dummy data*/ ;WITH [insert_cte] AS (SELECT [name] AS [some_column], ROW_NUMBER() OVER ( ORDER BY [name] ASC) AS [id] FROM sys.[columns]) /*tab1*/ INSERT INTO [tab1] ([id], [some_column]) SELECT TOP 10 [id], [some_column] FROM [insert_cte]; /*tab2*/ WITH [insert_cte] AS (SELECT [name] AS [some_column], ROW_NUMBER() OVER ( ORDER BY [name] ASC) AS [id] FROM sys.[columns]) INSERT INTO [tab2] ([id], [some_column]) SELECT TOP 25 [id], [some_column] FROM [insert_cte]; /*tab3*/ WITH [insert_cte] AS (SELECT [name] AS [some_column], ROW_NUMBER() OVER ( ORDER BY [name] ASC) AS [id] FROM sys.[columns]) INSERT INTO [tab3] ([id], [some_column]) SELECT TOP 33 [id], [some_column] FROM [insert_cte]; |
Eventually your sequences and default constraints will come back to haunt you
So far so good, but when someone tries to insert data in a way that would populate the id column with values from the sequence…
| 1 2 3 | INSERT INTO [tab1] ([some_column]) VALUES ('x'); INSERT INTO [tab2] ([some_column]) VALUES ('y'); INSERT INTO [tab3] ([some_column]) VALUES ('z'); |
They’ll start getting errors such as these:
Msg 2601, Level 14, State 1, Line 104
Cannot insert duplicate key row in object ‘dbo.tab1’ with unique index ‘cix_tab1’. The duplicate key value is (1).
The statement has been terminated.
Msg 2627, Level 14, State 1, Line 105
Violation of PRIMARY KEY constraint ‘pk_tab2_id’. Cannot insert duplicate key in object ‘dbo.tab2’. The duplicate key value is (1).
The statement has been terminated.
Msg 2627, Level 14, State 1, Line 106
Violation of UNIQUE KEY constraint ‘uq_tab3_id’. Cannot insert duplicate key in object ‘dbo.tab3’. The duplicate key value is (1).
The statement has been terminated
But why?
Well, unlike IDENTITY columns, sequences aren’t kept in sync with the values manually inserted in the columns that reference them.
Looking at the sys.sequences catalog view, we can see that the sequences are out of sync with the tables referencing them.
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 | SELECT [name],[current_value], [last_used_value] FROM [sys].[sequences] ORDER BY [name]; SELECT 'tab1' AS [table_name], MAX([id]) AS [max_id] FROM [tab1] UNION SELECT 'tab2' AS [table_name], MAX([id]) AS [max_id] FROM [tab2] UNION SELECT 'tab3' AS [table_name], MAX([id]) AS [max_id] FROM [tab3]; |
Notice how none of the sequences match the max_id values of their referencing tables.
Funny how nature does that.
Sure, if you have just one table in this situation, it’s pretty easy to fix with the following command:
| 1 | ALTER SEQUENCE [sq_tab1] RESTART WITH 11; |
But if you have multiple tables in this situation, some of which you might not even know of, it becomes a hassle dealing with it.
The Script
As with all of my scripts, you can also find it in my SQL Server Scripts GitHub repo, complete with comments and instructions.
What it does
In short, it allows you to mass reseed and sync sequences that are referenced by default constraints in SQL Server.
- It gets a list of tables with columns that have default constraints referencing sequences
- Iterates through each table and checks if the MAX() value of the referencing column is either
- Greater than the current value of the sequence
- Less than the current value-1 of the sequence
- If it matches either of those scenarios it reseeds the sequence to MAX()+1
What it doesn’t do, is fix this situation if you have * shudders * triggers that handle populating the id column with values from sequences instead of using default constraints.
This is because the query used in the cursor joins on sys.default_constraints to get to the sequence info.
And people have a bunch of different ways of formatting the T-SQL in their triggers which makes for more guess work than I’m comfortable with to get to the sys.sequences record.
That’s it, you just paste into a Query Editor window and press F5.
The actual script
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 | DECLARE @TabName NVARCHAR(261), @ColName SYSNAME, @SeqName NVARCHAR(261), @CurrVal BIGINT, @MaxID BIGINT, @State NVARCHAR(9), @ParamDef NVARCHAR(300), @SQL NVARCHAR(MAX); DECLARE seq_reseed_cursor CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY FOR SELECT QUOTENAME(SCHEMA_NAME([t].[schema_id])) + N'.' + QUOTENAME([t].[name]) AS [table_name], [c].[name] AS [column_name], QUOTENAME(SCHEMA_NAME([sq].[schema_id])) + N'.' + QUOTENAME([sq].[name]) AS [sequence_name], TRY_CAST([sq].[current_value] AS BIGINT) AS [current_value] FROM sys.[columns] AS [c] INNER JOIN sys.[tables] AS [t] ON [c].[object_id] = [t].[object_id] INNER JOIN sys.[default_constraints] AS [dc] ON [c].[default_object_id] = [dc].[object_id] INNER JOIN sys.[sequences] AS [sq] /*yes, I'm joining on a function, call the cops*/ ON OBJECT_ID(REPLACE(REPLACE([dc].[definition], N'(NEXT VALUE FOR ', N''), N')', N'')) = [sq].[object_id] WHERE [t].[type] = 'U' OPEN seq_reseed_cursor; FETCH NEXT FROM seq_reseed_cursor INTO @TabName, @ColName, @SeqName, @CurrVal; WHILE @@FETCH_STATUS = 0 BEGIN SET @SQL = N'SELECT @MaxIDOut = ISNULL(MAX(' + QUOTENAME(@ColName) + N'),0) FROM ' + @TabName + N' WITH (NOLOCK);' SET @ParamDef = N'@MaxIDOut BIGINT OUTPUT'; EXEC sp_executesql @SQL, @ParamDef, @MaxIDOut = @MaxID OUTPUT; IF((@MaxID > @CurrVal) OR(@MaxID < @CurrVal - 1)) BEGIN SELECT @State = CASE WHEN @MaxID > @CurrVal THEN N'behind' ELSE N'ahead of' END; RAISERROR ('Sequence %s is %s column %s.%s - will be reseeded.',10,1,@SeqName,@State,@TabName,@ColName) WITH NOWAIT; SET @SQL = N'ALTER SEQUENCE ' + @SeqName + N' RESTART WITH ' + CAST(@MaxID+1 AS NVARCHAR(30)) + N';'; EXEC sp_executesql @SQL; END; FETCH NEXT FROM seq_reseed_cursor INTO @TabName, @ColName, @SeqName, @CurrVal; END; CLOSE seq_reseed_cursor; DEALLOCATE seq_reseed_cursor; GO |
The script will inform you if it found and fixed any sequences that were out of sync.
![Sequence [dbo].[sq_tab1] is behind column [dbo].[tab1].id - will be reseeded. message repeats for the other two sequences](https://vladdba.com/wp-content/uploads/2025/07/reseed-sequences-sql-server-001.png)
Comparing again the sequences with the tables that reference them through default constraints, shows that they’re now back in sync.
And the next value that will be used from the sequence is MAX(id)+1.
As mentioned in the “what it does” section, the script also works the other way around.
If your sequences are way ahead of your referencing columns as a result of deletes/table truncates it also reseeds them to get them back in sync with the referencing columns.
I truncate the tables.
| 1 2 3 | TRUNCATE TABLE [tab1]; TRUNCATE TABLE [tab2]; TRUNCATE TABLE [tab3]; |
And rerun the script.
![Sequence [dbo].[sq_tab1] is ahead of column [dbo].[tab1].id - will be reseeded. message repeats for the other two sequences](https://vladdba.com/wp-content/uploads/2025/07/reseed-sequences-sql-server-003.png)
Cleanup demo objects
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | IF ( OBJECT_ID('tab1') ) IS NOT NULL BEGIN DROP TABLE [tab1]; END; IF ( OBJECT_ID('tab2') ) IS NOT NULL BEGIN DROP TABLE [tab2]; END; IF ( OBJECT_ID('tab3') ) IS NOT NULL BEGIN DROP TABLE [tab3]; END; IF ( OBJECT_ID('sq_tab1') ) IS NOT NULL BEGIN DROP SEQUENCE sq_tab1; END; IF ( OBJECT_ID('sq_tab2') ) IS NOT NULL BEGIN DROP SEQUENCE sq_tab2; END; IF ( OBJECT_ID('sq_tab3') ) IS NOT NULL BEGIN DROP SEQUENCE sq_tab3; END; |
Conclusion
If you ever run into such a situation, now you have a way to reseed and sync sequences in SQL Server so that they match again the columns referencing them via default constraints.
Also, if it’s not already clear, I’m not a big fan of sequences.
If you’ve liked this you might also want to check out the following posts:
- Getting away with multiple NULLs in a unique constraint
- Find object dependencies in SQL Server
- Script to count all NULLs in a table in SQL Server
- Testing large insert speeds in SQL Server