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;
    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];
  • 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];

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

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