how to create custom id in sql server with sequence

Question

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!

Answer 1

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

Answer 2

... a table with custom ID like BK-0001001 ...

Why do you feel the need to?

To me, this looks more like two bits of data - "BK" & "0001001" - mashed together into single field.
Or even three bits of data - "BK", "0001" & "001"!

Either way, it's a Bad Idea.

Databases are really good at finding little bits of stuff and putting them together. They're quite rubbish at finding things and pulling them apart again.

Storing different things in different fields will make your life a lot easier.

If you want to "present" this composite field to users, consider providing Views that do the composition:

create or replace view view1 as 
select item_type 
   || '-' 
   || format( category_num, 0000 ) 
   || format( item_num, 000 ) 
   as item_id 
from table1 ;