THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Louis Davidson

Utility to reset a sequence next value based on a table/sequence relationship

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: http://www.drsql.org/Pages/DownloadablePackages.aspx 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'

Published Wednesday, July 22, 2015 8:42 PM by drsql
Filed under: , ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Links to my other sites

Archives

Privacy Statement