Generation of Auto Series based on logic

Question

Generation of Auto Series based on logic

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

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

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

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)

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 2

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, 11 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)

Jonathan AC Roberts SSCoach Points: 18361 More actions · Answer 3

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

drop table if exists testcomp;
go
create 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)
);
go
INSERT INTO [dbo].[testcomp]([comment]) VALUES ('hello')
GO 1000
SELECT * FROM [dbo].[testcomp];

Jonathan AC Roberts SSCoach Points: 18361 More actions · Answer 4

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;
go
create 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)
);
go
INSERT INTO [dbo].[testcomp]([comment]) VALUES ('hello')
GO 1001
SELECT * FROM [dbo].[testcomp];