SQL Server 2012 – How to Create Custom ID with Sequence

sequencesql-server-2012

How can I create a table with custom ID like BK-0001001 with a sequence. Sorry I'm a newbie with databases. All help will be appreciated thank you!

Best Answer

You can use a mix between a SEQUENCE and formatting a DEFAULT CONSTRAINT:

CREATE SEQUENCE MySequence
AS int
START WITH 1
INCREMENT BY 1;
GO

CREATE TABLE MyTable
(
    MySeq varchar(20) CONSTRAINT [DF_MyTable_MySeq]
                      DEFAULT FORMAT((NEXT VALUE FOR MySequence), 'BK-000000#'),
    Foo   int,
    Bar   int,

    CONSTRAINT [PK_MyTable] PRIMARY KEY (MySeq)
);
GO

INSERT INTO MyTable (Foo, Bar) 
VALUES (1,2),(2,3),(3,4),(5,6);
GO

SELECT * FROM MyTable;
GO

MySeq      | Foo | Bar
:--------- | --: | --:
BK-0000001 |   1 |   2
BK-0000002 |   2 |   3
BK-0000003 |   3 |   4
BK-0000004 |   5 |   6

db<>fiddle here

Related Question