CODEWITHSUNDEEP
sqlsql-server
binh nguyen
binh nguyen

Reputation: 127

SQL Server - Reset custom identity column

I have a table with two columns ID and SubDetail. I have created custom identity like 2018XX for the ID columns with 2018 as current year and XX as a number and it will start from "01". I have searched for some topic about "Reset identity field - SQL Server" but It only showed how to reset only number identity field. I'm confused that is there anyway to reset custom identity field.

Here is my SQL query code:

CREATE PROCEDURE spAddSubWareHouse
     (@SubDetail VARCHAR(50))
AS
BEGIN
    DECLARE @maxDetailID INT 

    SELECT @maxDetailID = MAX(CAST(RIGHT(ID, 2) AS INT)) 
    FROM SubWareHouse

    IF (@maxDetailID IS NULL)
        SET @maxDetailID = 1 
    ELSE 
        SET @maxDetailID += 1

    --insert
    INSERT INTO SubWareHouse 
    VALUES (CAST(YEAR(GETDATE()) AS VARCHAR) + RIGHT('00' + CAST(@maxDetailID AS VARCHAR), 2), @SubDetail) 
END

Upvotes: 1

Views: 95

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

Don't bother doing this. Simply have a numeric identity column defined as:

id int identity(1, 1) primary key

You can then store the year as a separate column, because that appears to be information that you want.

Why do it this way? There are many reasons. First, identity is built into the database. So, identity columns are guaranteed to be unique even when multiple applications are doing inserts at the same time.

Second, integers are more efficient (slightly) for foreign key references.

Third, the locking required to do what you really want is very cumbersome and could significantly slow down your system. Plus, any implementation to prevent duplicates could have a bug -- so why not use the built-in mechanisms?

If you need to get the sequence number for a given year, it is easy enough using row_number():

select row_number() over (partition by year order by id) as year_sequence_number

Upvotes: 1

Related Questions