{"id":82178,"date":"2015-07-22T20:42:52","date_gmt":"2015-07-22T20:42:52","guid":{"rendered":"https:\/\/www.webstaging.red-gate.com\/simple-talk\/?p=73548"},"modified":"2019-05-22T09:39:16","modified_gmt":"2019-05-22T09:39:16","slug":"utility-to-reset-a-sequence-next-value-based-on-a-tablesequence-relationship","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/utility-to-reset-a-sequence-next-value-based-on-a-tablesequence-relationship\/","title":{"rendered":"Utility to reset a sequence next value based on a table\/sequence relationship"},"content":{"rendered":"

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. \u201cBlerg,\u201d I said, realizing that sequences don\u2019t manage themselves like identities. So I wrote some code that looked a lot like this:<\/p>\n

DECLARE @nextValue numeric(38) = COALESCE((SELECT MAX(singleSequenceId)
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM\u00a0\u00a0 dbo.singleSequence),0) +1
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0
\nDECLARE @statement NVARCHAR(1000) = CONCAT(‘ALTER SEQUENCE dbo.singleSequence_sequence RESTART WITH ‘,@nextValue)
\nEXEC (@statement)<\/p>\n

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\u2019t have to do that work\u2026 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.\u00a0 In the end, I created three procedures that could serve the purpose.<\/p>\n

1. Utility.Sequence$ResetBasedOnSpecificTable \u2013 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.<\/p>\n

2. Utility.Sequence$ResetBasedOnSequence \u2013 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.<\/a><\/p>\n

3. Utility.Sequence$ResetBasedOnTableAndColumn \u2013 Takes the name of a table and column, and if it has a default that is for a single table, it uses that.<\/p>\n

If you want the code as a downloadable package, it is available here:\u00a0https:\/\/drsql.org\/code<\/a><\/em>\u00a0under the name \u201csequence reset objects.sql\u201d. 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.<\/em><\/p>\n

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:<\/p>\n

create procedure\u00a0 Utility.Sequence$ResetBasedOnSpecificTable
\n\u00a0\u00a0\u00a0 @tableSchema sysname,
\n\u00a0\u00a0\u00a0 @tableName\u00a0\u00a0\u00a0 sysname,
\n\u00a0\u00a0\u00a0 @columnName sysname,
\n\u00a0\u00a0\u00a0 @sequenceSchema sysname,
\n\u00a0\u00a0\u00a0 @sequenceName sysname,
\n\u00a0\u00a0\u00a0 @ignoreDataTypeFlag bit = 0 –allow the user to ignore the check for a proper datatype
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 –note that it uses the metadata for restarts, but it does not care if your default is
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 –NEXT VALUE FOR sequenceName * 100, so you need to determine if this fits your needs..
\nAS
\n\u00a0\u00a0\u00a0 SET NOCOUNT ON<\/p>\n

\u00a0\u00a0\u00a0 –make sure the datatype of the target column is numeric (as you can cast the NEXT VALUE FOR in the query)
\n\u00a0\u00a0\u00a0 if @ignoreDataTypeFlag = 0 AND
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 not exists (
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 select *
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 from\u00a0\u00a0 sys.columns
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 join sys.types\u00a0\u00a0\u00a0
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 on columns.system_type_id = types.system_type_id
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 where\u00a0 columns.scale = 0
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 and\u00a0\u00a0 types.name in (‘tinyint’,’smallint’,’int’,’bigint’,’numeric’,’decimal’)
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 and\u00a0\u00a0 columns.object_id = object_id(concat(@tableschema,’.’,@tableName))
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 and\u00a0\u00a0 columns.name = @columnName
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 )
\n\u00a0\u00a0\u00a0\u00a0\u00a0 begin
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 throw 50000,’Either column does not exist, or is not of the base type for a sequence’,1
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 return -100
\n\u00a0\u00a0\u00a0\u00a0\u00a0 end<\/p>\n

\u00a0\u00a0\u00a0 –fetch the metadata for the sequence
\n\u00a0\u00a0\u00a0 declare @increment numeric(38), @current_value numeric(38), @startWith numeric(38)
\n\u00a0\u00a0\u00a0 select @increment = cast(increment as numeric(38)),
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @startWith = cast(start_value as numeric(38))
\n\u00a0\u00a0\u00a0 from\u00a0\u00a0 sys.sequences
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 join sys.schemas\u00a0\u00a0\u00a0
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 on schemas.schema_id = sequences.schema_id
\n\u00a0\u00a0\u00a0 where\u00a0 sequences.name = @sequenceName
\n\u00a0\u00a0\u00a0\u00a0\u00a0 and\u00a0 schemas.name = @sequenceSchema<\/p>\n

\n\u00a0\u00a0\u00a0 –no checking for real object names, because the following statement will take care of it
\n\u00a0\u00a0\u00a0 DECLARE @statement NVARCHAR(4000) =<\/p>\n

\u00a0\u00a0\u00a0 –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
\n\u00a0\u00a0\u00a0 –up to you do make sure that doesn’t occur, and check for errors.
\n\u00a0\u00a0\u00a0 CONCAT(‘DECLARE @nextValue numeric(38) = COALESCE((SELECT MAX(‘,@columnName,’)
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM\u00a0\u00a0 ‘,@tableSchema,’.’,@tableName,’),’,@startWith – 1,’) + ‘,@increment,’
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 DECLARE @statement NVARCHAR(1000) = CONCAT(”ALTER SEQUENCE ‘,@sequenceSchema,’.’,@sequenceName,’ RESTART WITH ”,@nextValue)
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 EXEC (@statement)
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ‘
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 )<\/p>\n

\u00a0\u00a0\u00a0 EXEC (@Statement)
\n\u00a0\u00a0\u00a0
\nGO<\/p>\n

Using it is simple, just take one sequence and one table (they needn\u2019t be linked to use this first procedure, but it will be useful in later examples):<\/p>\n

create sequence singleSequence_sequence as int start with 20 increment by 2
\ngo
\ncreate table singleSequence
\n(
\n\u00a0\u00a0\u00a0 singleSequenceId\u00a0\u00a0\u00a0 int constraint pkSingleSequence primary key default (next value for singleSequence_sequence)
\n)
\ngo<\/p>\n

Then run\/test it:<\/p>\n

insert into singleSequence
\nvalues (20),(22),(24),(26) –The first two values in sequence, so the first two default values inserts will be primary key violations
\ngo<\/p>\n

Then, try to insert the value using the defaults:<\/p>\n

insert into singleSequence
\ndefault values
\ngo<\/p>\n

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.<\/p>\n

Msg 2627, Level 14, State 1, Line 56
\nViolation of PRIMARY KEY constraint ‘pkSingleSequence’. Cannot insert duplicate key in object ‘dbo.singleSequence’. The duplicate key value is (20).
\nThe statement has been terminated.<\/p>\n

But, instead of trying again, execute the reset, and you will see it works:<\/p>\n

exec Utility.Sequence$ResetBasedOnSpecificTable ‘dbo’,’singleSequence’,’singleSequenceId’,’dbo’,’singleSequence_sequence’
\ngo
\ninsert into singleSequence
\ndefault values
\ngo<\/p>\n

You will see that a new row has been created:<\/p>\n

select *
\nfrom\u00a0\u00a0 singleSequence<\/p>\n

singleSequenceId
\n—————-
\n20
\n22
\n24
\n26
\n28 <\/p>\n

Next, let\u2019s create the procedure that looks up the sequence based on the sequence name.<\/p>\n

create procedure Utility.Sequence$ResetBasedOnSequence
\n\u00a0\u00a0\u00a0 @sequenceSchema sysname,
\n\u00a0\u00a0\u00a0 @sequenceName sysname
\nAS
\n\u00a0\u00a0\u00a0 SET NOCOUNT ON<\/p>\n

\u00a0\u00a0\u00a0 declare @tableSchema sysname,
\n\u00a0\u00a0\u00a0 @tableName\u00a0\u00a0\u00a0 sysname,
\n\u00a0\u00a0\u00a0 @columnName sysname,
\n\u00a0\u00a0\u00a0 @referenceCount int<\/p>\n

\u00a0\u00a0\u00a0 –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
\n\u00a0\u00a0\u00a0 –you can’t use the sequence along with the table as @keyValue = NEXT VALUE FOR… insert … values (@keyvalue, as this is a better
\n\u00a0\u00a0\u00a0 –pattern than using scope_identity() ever was
\n\u00a0\u00a0\u00a0 SELECT @referenceCount = (SELECT COUNT(*)
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM\u00a0\u00a0 sys.dm_sql_referencing_entities (CONCAT(@sequenceSchema,’.’,@sequenceName), ‘Object’) as dsre
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 join sys.default_constraints
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 on dsre.referencing_id = default_constraints.object_id)
\n\u00a0\u00a0\u00a0 –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
\n\u00a0\u00a0\u00a0 –to manually specifying table and column along with sequence.<\/p>\n

\u00a0\u00a0\u00a0 if @referenceCount = 0
\n\u00a0\u00a0\u00a0\u00a0\u00a0 begin
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 throw 50000,’Sequence either does not exists, or is not referenced in a default’,1
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 return -100
\n\u00a0\u00a0\u00a0\u00a0\u00a0 end<\/p>\n

\u00a0\u00a0\u00a0 if @referenceCount > 1
\n\u00a0\u00a0\u00a0\u00a0\u00a0 begin
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 throw 50000,’Sequence is referenced by more than one default constraint’,1
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 return -100
\n\u00a0\u00a0\u00a0\u00a0\u00a0 end<\/p>\n

\u00a0\u00a0\u00a0 –since we know there is one table and column using the sequence object, we can use it.
\n\u00a0\u00a0\u00a0 SELECT @tableSchema = schemas.name,
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @tableName = tables.name,
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @columnName = columns.name
\n\u00a0\u00a0\u00a0 FROM
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 sys.dm_sql_referencing_entities (concat(@sequenceSchema,’.’,@sequenceName), ‘Object’) as dsre
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 join sys.default_constraints
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 on dsre.referencing_id = default_constraints.object_id
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 join sys.columns
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 on default_constraints.parent_object_id = columns.object_id
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 and default_constraints.parent_column_id = columns.column_id
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 join sys.tables
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 on columns.object_id = tables.object_id\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 join sys.schemas
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 on schemas.schema_id = tables.schema_id<\/p>\n

\u00a0\u00a0\u00a0 execute Utility.Sequence$ResetBasedOnSpecificTable
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @tableSchema =@tableSchema,
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @tableName\u00a0\u00a0\u00a0 = @tableName,
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @columnName = @columnName,
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @sequenceSchema = @sequenceSchema,
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @sequenceName = @sequenceName<\/p>\n

\nGO<\/p>\n

To test this, we use the same code as previous, but just specify the sequence name:<\/p>\n

insert into singleSequence
\nvalues (20),(22),(24),(26) –The first two values in sequence, so the first two default values inserts will be primary key violations
\ngo<\/p>\n

insert into singleSequence
\ndefault values
\ngo<\/p>\n

Same error:<\/p>\n

Msg 2627, Level 14, State 1, Line 56
\nViolation of PRIMARY KEY constraint ‘pkSingleSequence’. Cannot insert duplicate key in object ‘dbo.singleSequence’. The duplicate key value is (20).
\nThe statement has been terminated.<\/p>\n

But, instead of trying again, execute the reset, and you will see it works:<\/p>\n

exec Utility.Sequence$ResetBasedOnSequence ‘dbo’,’singleSequence_sequence’
\ngo
\ninsert into singleSequence
\ndefault values
\ngo<\/p>\n

And again, you will see that a new row has been created:<\/p>\n

select *
\nfrom\u00a0\u00a0 singleSequence<\/p>\n

singleSequenceId
\n—————-
\n20
\n22
\n24
\n26
\n28<\/p>\n

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.<\/p>\n

create procedure Utility.Sequence$ResetBasedOnTableAndColumn
\n\u00a0\u00a0\u00a0 @tableSchema sysname,
\n\u00a0\u00a0\u00a0 @tableName\u00a0\u00a0 sysname,
\n\u00a0\u00a0\u00a0 @columnName sysname
\nAS
\n\u00a0\u00a0\u00a0 SET NOCOUNT ON<\/p>\n

\u00a0\u00a0\u00a0 declare @sequenceSchema sysname,
\n\u00a0\u00a0\u00a0 @sequenceName sysname,
\n\u00a0\u00a0\u00a0 @referenceCount int<\/p>\n

\u00a0\u00a0\u00a0 –using sys.sql_expresssion_dependencies, check to see if the column has a default that references
\n\u00a0\u00a0\u00a0 –the sequence
\n\u00a0\u00a0\u00a0 select @sequenceSchema = referenced_schema_name, @sequenceName = referenced_entity_name
\n\u00a0\u00a0\u00a0 from\u00a0\u00a0 sys.schemas
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 join sys.tables
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 on schemas.schema_id = tables.schema_id
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 join sys.columns
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 on columns.object_id = tables.object_id\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 join sys.default_constraints
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 on default_constraints.parent_object_id = columns.object_id
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 and default_constraints.parent_column_id = columns.column_id
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 join sys.sql_expression_dependencies
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 on default_constraints.object_id = sql_expression_Dependencies.referencing_id
\n\u00a0\u00a0\u00a0 where schemas.name = @tableSchema
\n\u00a0\u00a0\u00a0\u00a0\u00a0 and tables.name= @tableName
\n\u00a0\u00a0\u00a0\u00a0\u00a0 and columns.name = @ColumnName<\/p>\n

\u00a0\u00a0\u00a0 –then use the referencing entitys dmv, and see if it is being referenced > 1 time. It is important to be careful or you may
\n\u00a0\u00a0\u00a0 –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
\n\u00a0\u00a0\u00a0 –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.
\n\u00a0\u00a0\u00a0 SELECT @referenceCount = (SELECT COUNT(*)
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM\u00a0\u00a0 sys.dm_sql_referencing_entities (CONCAT(@sequenceSchema,’.’,@sequenceName), ‘Object’) as dsre
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 join sys.default_constraints
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 on dsre.referencing_id = default_constraints.object_id)<\/p>\n

\n\u00a0\u00a0\u00a0 if @referenceCount = 0
\n\u00a0\u00a0\u00a0\u00a0\u00a0 begin
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 throw 50000,’Sequence either does not exists, or is not referenced in a default’,1
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 return -100
\n\u00a0\u00a0\u00a0\u00a0\u00a0 end<\/p>\n

\u00a0\u00a0\u00a0 if @referenceCount > 1
\n\u00a0\u00a0\u00a0\u00a0\u00a0 begin
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 throw 50000,’Sequence used by column is referenced by more than one default constraint’,1
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 return -100
\n\u00a0\u00a0\u00a0\u00a0\u00a0 end<\/p>\n

\u00a0\u00a0\u00a0 execute Utility.Sequence$ResetBasedOnSpecificTable
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @tableSchema =@tableSchema,
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @tableName\u00a0\u00a0\u00a0 = @tableName,
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @columnName = @columnName,
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @sequenceSchema = @sequenceSchema,
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @sequenceName = @sequenceName<\/p>\n

go<\/p>\n

I won\u2019t 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:<\/p>\n

exec Utility.Sequence$ResetBasedOnTableAndColumn ‘dbo’,’singleSequence’,’singleSequenceId’<\/p>\n

And you get the same results. Finally, let\u2019s test a few fringe cases, so I have proved that I tested them:<\/p>\n

What if the sequence is used in multiple tables? It would be bad to reset based on one table, but not the other..\u00a0 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.<\/p>\n

create sequence doubleSequence_sequence as int start with 1 increment by 2
\ngo<\/p>\n

create table doubleSequence1
\n(
\n\u00a0\u00a0\u00a0 doubleSequence1Id\u00a0\u00a0\u00a0 int constraint pkdoubleSequence1 primary key default (next value for doubleSequence_sequence)
\n)
\nGO
\ncreate table doubleSequence2
\n(
\n\u00a0\u00a0\u00a0 doubleSequence2Id\u00a0\u00a0\u00a0 int constraint pkdoubleSequence2 primary key default (next value for doubleSequence_sequence)
\n)
\nGO<\/p>\n

Trying to use the reset procedures:<\/p>\n

exec Utility.Sequence$ResetBasedOnSequence ‘dbo’,’doubleSequence_sequence’<\/p>\n

Gives you this error:<\/p>\n

Msg 50000, Level 16, State 1, Procedure Sequence$ResetBasedOnSequence, Line 249
\nSequence is referenced by more than one default constraint<\/p>\n

And the other, a slightly different wording of the same error:<\/p>\n

exec Utility.Sequence$ResetBasedOnTableAndColumn ‘dbo’,’doubleSequence1′,’doubleSequence1Id’<\/p>\n

Msg 50000, Level 16, State 1, Procedure Sequence$ResetBasedOnTableAndColumn, Line 272
\nSequence used by column is referenced by more than one default constraint<\/p>\n

\nFinally, let\u2019s take the case where you have the same sequenced referenced twice in the same table:<\/p>\n

create sequence oneMoreDouble_sequence as int start with 1
\ngo
\ncreate table oneMoreDouble
\n(
\n\u00a0\u00a0\u00a0 oneMoreDoubleId\u00a0\u00a0\u00a0 int constraint pkoneMoreDouble primary key default (next value for oneMoreDouble_sequence),
\n\u00a0\u00a0\u00a0 anotherColumn\u00a0\u00a0\u00a0 varchar(10) default (‘T’ + cast(next value for oneMoreDouble_sequence as varchar(9)))
\n)
\nGO<\/p>\n

The first usage is a surrogate key value, and the other is a formatted version of that value (A \u2018T\u2019 prepended to it, fancy!) The calls used in the last double test will give you the same errors:<\/p>\n

exec Utility.Sequence$ResetBasedOnSequence ‘dbo’,’oneMoreDouble_sequence’<\/p>\n

exec Utility.Sequence$ResetBasedOnTableAndColumn ‘dbo’,’oneMoreDouble’,’oneMoreDoubleId’, ‘dbo’,’oneMoreDouble_sequence’<\/p>\n

But what if you specify the column? Specifying the text column will not work:<\/p>\n

Utility.Sequence$ResetBasedOnSpecificTable\u00a0 ‘dbo’,’oneMoreDouble’,’anotherColumn’, ‘dbo’,’oneMoreDouble_sequence’<\/p>\n

Which will return the following error:<\/p>\n

Msg 50000, Level 16, State 1, Procedure Sequence$ResetBasedOnSpecificTable, Line 269
\nEither column does not exist, or is not of the base type for a sequence<\/p>\n

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.<\/p>\n

Utility.Sequence$ResetBasedOnSpecificTable\u00a0 ‘dbo’,’oneMoreDouble’,’oneMoreDoubleId’, ‘dbo’,’oneMoreDouble_sequence’<\/p>\n","protected":false},"excerpt":{"rendered":"

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……<\/p>\n","protected":false},"author":56085,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[],"coauthors":[19684],"class_list":["post-82178","post","type-post","status-publish","format-standard","hentry","category-blogs"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/82178","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/56085"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=82178"}],"version-history":[{"count":3,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/82178\/revisions"}],"predecessor-version":[{"id":84358,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/82178\/revisions\/84358"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=82178"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=82178"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=82178"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=82178"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}