SQL Server：如何重置年份+序号格式的自定义标识列？

Got it, let's tackle this problem. The key here is that your ID isn't a standard numeric identity column—it's a year-prefixed custom format, so the usual DBCC CHECKIDENT tricks don't apply. Your current stored procedure works for incrementing the sequence, but it won't automatically reset the two-digit number when the year changes, which I assume is what you're looking for (since you mentioned "resetting" the custom identity).

First, Let's Fix the Year-Reset Logic #

Your existing code calculates the max sequence number across all years, which means when a new year rolls around, it'll keep incrementing from the previous year's last number. To reset the sequence to 01 each year, you need to only look at records from the current year when calculating the max sequence.

Here's the modified stored procedure with that fix:

CREATE PROCEDURE spAddSubWareHouse (@SubDetail VARCHAR(50))
AS
BEGIN
    SET NOCOUNT ON; -- Good practice to prevent extra result sets

    DECLARE @currentYear VARCHAR(4) = CAST(YEAR(GETDATE()) AS VARCHAR);
    DECLARE @maxDetailID INT;

    -- Only get the max sequence number for the CURRENT year
    SELECT @maxDetailID = MAX(CAST(RIGHT(ID, 2) AS INT))
    FROM SubWareHouse
    WHERE ID LIKE @currentYear + '%'; -- Filter by current year prefix

    -- If no records exist for this year, start at 1; else increment
    SET @maxDetailID = ISNULL(@maxDetailID, 0) + 1;

    -- Generate the new ID with leading zero for single-digit numbers
    DECLARE @newID VARCHAR(6) = @currentYear + RIGHT('00' + CAST(@maxDetailID AS VARCHAR), 2);

    INSERT INTO SubWareHouse (ID, SubDetail)
    VALUES (@newID, @SubDetail);
END

Key Improvements Explained #

• Year-specific filtering: The WHERE ID LIKE @currentYear + '%' clause ensures we only consider records from the current year when calculating the next sequence number. This automatically resets the sequence to 01 when the year changes (since there will be no records for the new year initially).
• ISNULL simplification: Replaced the IF/ELSE block with ISNULL(@maxDetailID, 0) + 1 to make the code cleaner—if there are no records for the current year, @maxDetailID will be NULL, so ISNULL sets it to 0, then we add 1 to start at 1.
• Explicit column names: Added (ID, SubDetail) to the INSERT statement to make the code more maintainable (avoids issues if the table's column order changes later).
• SET NOCOUNT ON: Prevents SQL Server from returning the "X rows affected" message, which is standard for stored procedures.

Handling Concurrency (Important!) #

If multiple users might run this stored procedure at the same time, you could run into race conditions where two processes get the same @maxDetailID and try to insert duplicate IDs. To fix this, you can add a transaction with table locking to ensure only one process calculates the next ID at a time:

CREATE PROCEDURE spAddSubWareHouse (@SubDetail VARCHAR(50))
AS
BEGIN
    SET NOCOUNT ON;
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; -- Highest isolation level for this operation

    BEGIN TRANSACTION;

    DECLARE @currentYear VARCHAR(4) = CAST(YEAR(GETDATE()) AS VARCHAR);
    DECLARE @maxDetailID INT;

    SELECT @maxDetailID = MAX(CAST(RIGHT(ID, 2) AS INT))
    FROM SubWareHouse WITH (UPDLOCK, HOLDLOCK) -- Lock the table to prevent concurrent reads
    WHERE ID LIKE @currentYear + '%';

    SET @maxDetailID = ISNULL(@maxDetailID, 0) + 1;
    DECLARE @newID VARCHAR(6) = @currentYear + RIGHT('00' + CAST(@maxDetailID AS VARCHAR), 2);

    INSERT INTO SubWareHouse (ID, SubDetail)
    VALUES (@newID, @SubDetail);

    COMMIT TRANSACTION;
END

The WITH (UPDLOCK, HOLDLOCK) hint locks the SubWareHouse table during the SELECT operation, so other processes have to wait until the transaction completes before they can read the table—this eliminates duplicate IDs from concurrent inserts.

Final Notes #

• If you ever need to manually reset the sequence for a specific year (e.g., if you deleted all records for 2024 and want to start over from 01), you don't need a special "reset" command—just delete the records for that year, and the next insert will start at 01 automatically.
• If your ID format ever changes (e.g., adding a month), you can adjust the RIGHT(ID, 2) part to match the length of the sequence portion, and update the filter to include the month as well.

内容的提问来源于stack exchange，提问作者binh nguyen