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!
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; GOMySeq | Foo | Bar :--------- | --: | --: BK-0000001 | 1 | 2 BK-0000002 | 2 | 3 BK-0000003 | 3 | 4 BK-0000004 | 5 | 6
... 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 ;