SDU Tools: Reset Sequence using SQL Server T-SQL

Our free SDU Tools for developers and DBAs, now includes a very large number of tools, with procedures, functions, and views. Users that are new to working with sequences often don’t have simple options for resetting them back to a specific value. Many business intelligence ETL or ELT processes need to have a way to do this. So we added a tool that can help. It’s called ResetSequence.

The procedure takes three parameters.

@SchemaName - the name of the schema that the sequence exists in @SequenceName - the name of the sequence @NextValueToUse - the value to reset the sequence to (and it defaults to the value 1 if not supplied)

Find out more

You can see it in action in the main image above, and in the video here. The full current version of the code is also shown below:

https://youtu.be/mCvnqOAO58w

You can use our tools as a set or as a great example of how to write functions like these.

Access to SDU Tools is one of the benefits of being an SDU Insider, along with access to our other free tools and eBooks. Please just visit here for more info:

http://sdutools.sqldownunder.com

Latest version of the code

Note: the code might wrap when displayed below.

CREATE PROCEDURE SDU_Tools.ResetSequence
@SchemaName sysname,
@SequenceName sysname,
@NextValueToUse bigint = 1
AS
BEGIN

-- Function:      ResetSequence
-- Parameters:    @SchemaName sysname           -> Schema for the sequence to process
-- @SequenceName sysname         -> Sequence to process
-- @NextValueToUse bigint        -> Next value for sequence (defaults to 1)
-- Action:        Resets the next sequence value to start with 1
-- Return:        Nil
-- Refer to this video: https://youtu.be/mCvnqOAO58w
--
-- Test examples: 
/*

EXEC SDU_Tools.ResetSequence
     @SchemaName = N'Sequences', 
     @SequenceName = N'CustomerID'; 

EXEC SDU_Tools.ResetSequence
     @SchemaName = N'Sequences', 
     @SequenceName = N'CustomerID',
     @NextValueToUse = 25; 

*/

    SET NOCOUNT ON;
    SET XACT_ABORT ON;
 
    DECLARE @SQL nvarchar(max);
    DECLARE @CRLF nchar(2) = NCHAR(13) + NCHAR(10);

    SET @SQL = N'ALTER SEQUENCE ' 
             + QUOTENAME(@SchemaName) 
             + N'.' 
             + QUOTENAME(@SequenceName) 
             + N' RESTART WITH '
             + CAST(@NextValueToUse AS nvarchar(20))
             + ';';
    EXEC (@SQL);

END;

2025-03-13