# Generation of Auto Series based on logic

• Yes.  And, it's easy.  Start an IDENTITY column at "0".  Add a computed column with the formula from my previous query but replace the "t.N" in both places with the name of the ID column and you're done.  You'll still suffer missing values if someone rolls back an INSERT but it will auto-magically calculate the desired value for you.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

• Just for convenience, here's the formula.  Replace the "t.N" with the name of your IDENTITY column that starts at zero.

`CONCAT('10.',CHAR(66+t.N/1000),RIGHT(CONCAT('00',t.N%1000),3))`

EDIT:  Forgot to add that it would be VERY helpful if you made that a PERSISTED computed column so that it could be indexed.

• This reply was modified 1 year, 6 months ago by  Jeff Moden.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

• If you don't want the code ending with 3 zeros (e.g. 10.C000) then

`drop table if exists testcomp;gocreate table testcomp(    id int identity(1,1) not null,    calseq as (convert(varchar(20), CONCAT('10.',CHAR(ascii('B')+(id-1)/999),RIGHT(CONCAT('00',((id-1)%999)+1),3)))) PERSISTED,    comment varchar(20));goINSERT INTO [dbo].[testcomp]([comment]) VALUES ('hello')GO 1000SELECT * FROM [dbo].[testcomp];`
• kirti wrote:

Just a small query. Can we start this series from '000' instead of 001?

Just saw that comment:

`drop table if exists testcomp;gocreate table testcomp(    id int identity(1,1) not null,    calseq as (convert(varchar(20), CONCAT('10.',CHAR(ascii('B')+(id-1)/1000),RIGHT(CONCAT('00',((id-1)%1000)),3)))) PERSISTED,    comment varchar(20));goINSERT INTO [dbo].[testcomp]([comment]) VALUES ('hello')GO 1001SELECT * FROM [dbo].[testcomp];`

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply