CODEWITHSUNDEEP
sql-server
Hardik Shah
Hardik Shah

Reputation: 186

Custom Unique ID Generation In MS SQL

I want to create custom id in sql table which contains below information

ID = 'SRIG'+ 'DDMMYY'+ 5 digit number.

Table Details

CREATE TABLE SR
(
    SRREQID NVARCHAR(100) PRIMARY KEY, 
    -- Your @Entity ID might be another candidate here
    SRID INT
);

SQL Query

DECLARE @EntityId INT;
DECLARE @SRREQID NVARCHAR(100);

-- BEGIN TRAN
 SELECT @EntityId = ISNULL(MAX(SRID),0) FROM SR;

 Select @SRREQID = 'SRIC'+RIGHT('00'+CONVERT(varchar,datepart(YY,getdate())),2)+
              RIGHT('00'+CONVERT(varchar,datepart(MM,getdate())),2)+
              RIGHT('00'+CONVERT(varchar,datepart(DD,getdate())),2)+                 
              RIGHT('000000'+ CONVERT(NVARCHAR(10), @EntityId+1),6);

 INSERT INTO SR(SRREQID,SRID) VALUES(@SRREQID,@EntityId+1);

 SELECT * FROM SR;


-- COMMIT TRAN;

Query Output

SRREQID             SRID
SRIC160518000100    100

Upvotes: 0

Views: 1604

Answers (2)

Kahn
Kahn

Reputation: 1660

You can use a computed column as well, like so:

create table SR
    (SRREQID AS -- Your computed column
        'SRIC'+RIGHT('00'+CONVERT(varchar,datepart(YY,getdate())),2)+
              RIGHT('00'+CONVERT(varchar,datepart(MM,getdate())),2)+
              RIGHT('00'+CONVERT(varchar,datepart(DD,getdate())),2)+                 
              RIGHT('00000'+ CONVERT(NVARCHAR(10), SRREQID_SEQ),5)
    , SRREQID_SEQ INT IDENTITY(1,1) -- ID used by your computed column
    , MyRandomVal varchar(25)) -- Random actual data for the table

Then, create a job that at precisely at the beginning of every day at 00.00 executes the following command to restart the identity of SRREQID_SEQ at 1 for the next day.

DBCC CHECKIDENT ('dbo.SR', RESEED, 0);

There may be a smarter way to go about this, but then I haven't actually run into any scenarios where something like this would be useful, yet.

The advantage of all this is that you don't need a complex script to insert new data.

You can just insert your data normally, and the DB will automatically take care of your SRREQID for you. Like so:

INSERT SR (MyRandomVal) VALUES ('My data')

Edit: Also, I advise you to check Zohar Peled's comment below, where he has a suggestion that can further simplify this solution, and depending your circumstances and DB load and usage, may fit your scenario better than my suggestion above.

Upvotes: 2

gofr1
gofr1

Reputation: 15977

For SQL Server 2012 and onward.

At first create a SEQUENSE:

CREATE SEQUENCE dbo.testing
    START WITH 1
    INCREMENT BY 1 ;
GO

Then you can run this to get new id:

SELECT  'SRIG' + REPLACE(CONVERT(nvarchar(10),GETDATE(),4),'.','') + 
        RIGHT('0000'+CAST(NEXT VALUE FOR dbo.testing as nvarchar(5)),5)

Output:

SRIG18051600001

EDIT:

If you need to restart sequence every day then use this query:

ALTER SEQUENCE dbo.testing RESTART WITH 1;

Upvotes: 1

Related Questions