So I was building a database the other day, and I used sequence objects for all of the defaults because it was FAR easier to load data into the table not needing to set identity_insert on and off over and over and over. Worked great. Went through all of my tests, everything was great. Got out Redgate Data Generator to load up a few hundred thousand noise rows and boom, primary key error. “Blerg,” I said, realizing that sequences don’t manage themselves like identities. So I wrote some code that looked a lot like this:
DECLARE @nextValue numeric(38) = COALESCE((SELECT MAX(singleSequenceId)
FROM dbo.singleSequence),0) +1
DECLARE @statement NVARCHAR(1000) = CONCAT(‘ALTER SEQUENCE dbo.singleSequence_sequence RESTART WITH ‘,@nextValue)
EXEC (@statement)
Because, like every good programmer, I wanted to at least somewhat automate the task. But like very at least slightly better than that good programmer, I wanted something a bit more like DBCC CHECKIDENT so I didn’t have to do that work… And what if the sequence was not started with 1, or with an increment other than 1? So you know what comes next, right? We automate that. In the end, I created three procedures that could serve the purpose.
1. Utility.Sequence$ResetBasedOnSpecificTable – Which simply lets you pass in the table and column, along with a sequence, and it resets it based on the values of the table and the metadata of the sequence.
2. Utility.Sequence$ResetBasedOnSequence – Takes the sequence object, looks to see if it is used in one default constraint, not zero, which would be useless, not two, as that would be too many, and three is right out.
3. Utility.Sequence$ResetBasedOnTableAndColumn – Takes the name of a table and column, and if it has a default that is for a single table, it uses that.
If you want the code as a downloadable package, it is available here: https://drsql.org/code under the name “sequence reset objects.sql”. As usual, when downloading code from the internet, use at your own risk, test, test, test, test and test again before trusting my code in your production environment and I will do the same with yours.
The second two procedures use the first one, but just do the work of fetching and verifying the metadata. Here is the first procedure in which you specify table and sequence:
create procedure Utility.Sequence$ResetBasedOnSpecificTable
@tableSchema sysname,
@tableName sysname,
@columnName sysname,
@sequenceSchema sysname,
@sequenceName sysname,
@ignoreDataTypeFlag bit = 0 –allow the user to ignore the check for a proper datatype
–note that it uses the metadata for restarts, but it does not care if your default is
–NEXT VALUE FOR sequenceName * 100, so you need to determine if this fits your needs..
AS
SET NOCOUNT ON
–make sure the datatype of the target column is numeric (as you can cast the NEXT VALUE FOR in the query)
if @ignoreDataTypeFlag = 0 AND
not exists (
select *
from sys.columns
join sys.types
on columns.system_type_id = types.system_type_id
where columns.scale = 0
and types.name in (‘tinyint’,’smallint’,’int’,’bigint’,’numeric’,’decimal’)
and columns.object_id = object_id(concat(@tableschema,’.’,@tableName))
and columns.name = @columnName
)
begin
throw 50000,’Either column does not exist, or is not of the base type for a sequence’,1
return -100
end
–fetch the metadata for the sequence
declare @increment numeric(38), @current_value numeric(38), @startWith numeric(38)
select @increment = cast(increment as numeric(38)),
@startWith = cast(start_value as numeric(38))
from sys.sequences
join sys.schemas
on schemas.schema_id = sequences.schema_id
where sequences.name = @sequenceName
and schemas.name = @sequenceSchema
–no checking for real object names, because the following statement will take care of it
DECLARE @statement NVARCHAR(4000) =
–I am pretty sure my new favorite function is CONCAT! Note that if your input is bad, this statement will fail, so it will be
–up to you do make sure that doesn’t occur, and check for errors.
CONCAT(‘DECLARE @nextValue numeric(38) = COALESCE((SELECT MAX(‘,@columnName,’)
FROM ‘,@tableSchema,’.’,@tableName,’),’,@startWith – 1,’) + ‘,@increment,’
DECLARE @statement NVARCHAR(1000) = CONCAT(”ALTER SEQUENCE ‘,@sequenceSchema,’.’,@sequenceName,’ RESTART WITH ”,@nextValue)
EXEC (@statement)
‘
)
EXEC (@Statement)
GO
Using it is simple, just take one sequence and one table (they needn’t be linked to use this first procedure, but it will be useful in later examples):
create sequence singleSequence_sequence as int start with 20 increment by 2
go
create table singleSequence
(
singleSequenceId int constraint pkSingleSequence primary key default (next value for singleSequence_sequence)
)
go
Then run/test it:
insert into singleSequence
values (20),(22),(24),(26) –The first two values in sequence, so the first two default values inserts will be primary key violations
go
Then, try to insert the value using the defaults:
insert into singleSequence
default values
go
You will get the following error, Execute it twice, and you will see another duplicate for 22, and it will keep failing until it gets to 28 if you go that way.
Msg 2627, Level 14, State 1, Line 56
Violation of PRIMARY KEY constraint ‘pkSingleSequence’. Cannot insert duplicate key in object ‘dbo.singleSequence’. The duplicate key value is (20).
The statement has been terminated.
But, instead of trying again, execute the reset, and you will see it works:
exec Utility.Sequence$ResetBasedOnSpecificTable ‘dbo’,’singleSequence’,’singleSequenceId’,’dbo’,’singleSequence_sequence’
go
insert into singleSequence
default values
go
You will see that a new row has been created:
select *
from singleSequence
singleSequenceId
—————-
20
22
24
26
28
Next, let’s create the procedure that looks up the sequence based on the sequence name.
create procedure Utility.Sequence$ResetBasedOnSequence
@sequenceSchema sysname,
@sequenceName sysname
AS
SET NOCOUNT ON
declare @tableSchema sysname,
@tableName sysname,
@columnName sysname,
@referenceCount int
–count to see that this sequence is referenced by one default (note that it could be used in a procedure, and there is no reason why
–you can’t use the sequence along with the table as @keyValue = NEXT VALUE FOR… insert … values (@keyvalue, as this is a better
–pattern than using scope_identity() ever was
SELECT @referenceCount = (SELECT COUNT(*)
FROM sys.dm_sql_referencing_entities (CONCAT(@sequenceSchema,’.’,@sequenceName), ‘Object’) as dsre
join sys.default_constraints
on dsre.referencing_id = default_constraints.object_id)
–note too that you may have cases where the sequence is referenced twice in the same table. This is another fringe case I would relegate
–to manually specifying table and column along with sequence.
if @referenceCount = 0
begin
throw 50000,’Sequence either does not exists, or is not referenced in a default’,1
return -100
end
if @referenceCount > 1
begin
throw 50000,’Sequence is referenced by more than one default constraint’,1
return -100
end
–since we know there is one table and column using the sequence object, we can use it.
SELECT @tableSchema = schemas.name,
@tableName = tables.name,
@columnName = columns.name
FROM
sys.dm_sql_referencing_entities (concat(@sequenceSchema,’.’,@sequenceName), ‘Object’) as dsre
join sys.default_constraints
on dsre.referencing_id = default_constraints.object_id
join sys.columns
on default_constraints.parent_object_id = columns.object_id
and default_constraints.parent_column_id = columns.column_id
join sys.tables
on columns.object_id = tables.object_id
join sys.schemas
on schemas.schema_id = tables.schema_id
execute Utility.Sequence$ResetBasedOnSpecificTable
@tableSchema =@tableSchema,
@tableName = @tableName,
@columnName = @columnName,
@sequenceSchema = @sequenceSchema,
@sequenceName = @sequenceName
GO
To test this, we use the same code as previous, but just specify the sequence name:
insert into singleSequence
values (20),(22),(24),(26) –The first two values in sequence, so the first two default values inserts will be primary key violations
go
insert into singleSequence
default values
go
Same error:
Msg 2627, Level 14, State 1, Line 56
Violation of PRIMARY KEY constraint ‘pkSingleSequence’. Cannot insert duplicate key in object ‘dbo.singleSequence’. The duplicate key value is (20).
The statement has been terminated.
But, instead of trying again, execute the reset, and you will see it works:
exec Utility.Sequence$ResetBasedOnSequence ‘dbo’,’singleSequence_sequence’
go
insert into singleSequence
default values
go
And again, you will see that a new row has been created:
select *
from singleSequence
singleSequenceId
—————-
20
22
24
26
28
Finally, the procedure to let you specify the table and column, which is what you probably want to do realistically (again with the caveat that sequences are independent objects, and as such can be used for multiple purposes, use with care.
create procedure Utility.Sequence$ResetBasedOnTableAndColumn
@tableSchema sysname,
@tableName sysname,
@columnName sysname
AS
SET NOCOUNT ON
declare @sequenceSchema sysname,
@sequenceName sysname,
@referenceCount int
–using sys.sql_expresssion_dependencies, check to see if the column has a default that references
–the sequence
select @sequenceSchema = referenced_schema_name, @sequenceName = referenced_entity_name
from sys.schemas
join sys.tables
on schemas.schema_id = tables.schema_id
join sys.columns
on columns.object_id = tables.object_id
join sys.default_constraints
on default_constraints.parent_object_id = columns.object_id
and default_constraints.parent_column_id = columns.column_id
join sys.sql_expression_dependencies
on default_constraints.object_id = sql_expression_Dependencies.referencing_id
where schemas.name = @tableSchema
and tables.name= @tableName
and columns.name = @ColumnName
–then use the referencing entitys dmv, and see if it is being referenced > 1 time. It is important to be careful or you may
–have the case where you are referencing it by two or more tables and resetting it to the next value for one won’t be the next
–value for another. This is a very fringe case, and would be way too much work to do to automate for the few use cases.
SELECT @referenceCount = (SELECT COUNT(*)
FROM sys.dm_sql_referencing_entities (CONCAT(@sequenceSchema,’.’,@sequenceName), ‘Object’) as dsre
join sys.default_constraints
on dsre.referencing_id = default_constraints.object_id)
if @referenceCount = 0
begin
throw 50000,’Sequence either does not exists, or is not referenced in a default’,1
return -100
end
if @referenceCount > 1
begin
throw 50000,’Sequence used by column is referenced by more than one default constraint’,1
return -100
end
execute Utility.Sequence$ResetBasedOnSpecificTable
@tableSchema =@tableSchema,
@tableName = @tableName,
@columnName = @columnName,
@sequenceSchema = @sequenceSchema,
@sequenceName = @sequenceName
go
I won’t repeat that test code, as it is just repetitive to the others, but suffice it to say that replace the call for resetting the sequence to:
exec Utility.Sequence$ResetBasedOnTableAndColumn ‘dbo’,’singleSequence’,’singleSequenceId’
And you get the same results. Finally, let’s test a few fringe cases, so I have proved that I tested them:
What if the sequence is used in multiple tables? It would be bad to reset based on one table, but not the other.. So the procedures (other than the first one, where you explicitly provide object names to be used) check the metadata of the sequence object to make sure you aren’t shooting yourself in the foot.
create sequence doubleSequence_sequence as int start with 1 increment by 2
go
create table doubleSequence1
(
doubleSequence1Id int constraint pkdoubleSequence1 primary key default (next value for doubleSequence_sequence)
)
GO
create table doubleSequence2
(
doubleSequence2Id int constraint pkdoubleSequence2 primary key default (next value for doubleSequence_sequence)
)
GO
Trying to use the reset procedures:
exec Utility.Sequence$ResetBasedOnSequence ‘dbo’,’doubleSequence_sequence’
Gives you this error:
Msg 50000, Level 16, State 1, Procedure Sequence$ResetBasedOnSequence, Line 249
Sequence is referenced by more than one default constraint
And the other, a slightly different wording of the same error:
exec Utility.Sequence$ResetBasedOnTableAndColumn ‘dbo’,’doubleSequence1′,’doubleSequence1Id’
Msg 50000, Level 16, State 1, Procedure Sequence$ResetBasedOnTableAndColumn, Line 272
Sequence used by column is referenced by more than one default constraint
Finally, let’s take the case where you have the same sequenced referenced twice in the same table:
create sequence oneMoreDouble_sequence as int start with 1
go
create table oneMoreDouble
(
oneMoreDoubleId int constraint pkoneMoreDouble primary key default (next value for oneMoreDouble_sequence),
anotherColumn varchar(10) default (‘T’ + cast(next value for oneMoreDouble_sequence as varchar(9)))
)
GO
The first usage is a surrogate key value, and the other is a formatted version of that value (A ‘T’ prepended to it, fancy!) The calls used in the last double test will give you the same errors:
exec Utility.Sequence$ResetBasedOnSequence ‘dbo’,’oneMoreDouble_sequence’
exec Utility.Sequence$ResetBasedOnTableAndColumn ‘dbo’,’oneMoreDouble’,’oneMoreDoubleId’, ‘dbo’,’oneMoreDouble_sequence’
But what if you specify the column? Specifying the text column will not work:
Utility.Sequence$ResetBasedOnSpecificTable ‘dbo’,’oneMoreDouble’,’anotherColumn’, ‘dbo’,’oneMoreDouble_sequence’
Which will return the following error:
Msg 50000, Level 16, State 1, Procedure Sequence$ResetBasedOnSpecificTable, Line 269
Either column does not exist, or is not of the base type for a sequence
But this call will work just fine, as it would have technically for the doubleSequence tables, though in this case it is a safe operation, as it is just referenced by the one table.
Utility.Sequence$ResetBasedOnSpecificTable ‘dbo’,’oneMoreDouble’,’oneMoreDoubleId’, ‘dbo’,’oneMoreDouble_sequence’
Load comments