Auto generate IDs in MS SQL...

Lynn · November 17th, 2006, 01:07 AM

Hi,
Sorry, I have one problem on autogenerating ID too. I'm using Microsoft SQL and my IDs are in A001, A002, A003...and so on. How can I auto generate this IDs? Like A001 +1=A002? Please help...
Thanks
Lynn

Jeff Moden · November 17th, 2006, 04:25 AM

You've double posted... but I'll post the answer here, as well...

...And I assume that when you get to A999 and insert one more record, it should roll over to B000, huh?

Ok, in order to keep this from becoming "Death by SQL", here's how to do it... your table should have two column in it that are very similar to the ID and AlphaID columns... like this...

Code:

CREATE TABLE AlphaID 
        (
        ID INT IDENTITY(0,1) PRIMARY KEY CLUSTERED ,
        AlphaID AS CHAR(ID/1000+65)+REPLACE(STR(RIGHT(ID,3),3),' ','0'),
        SomeOtherCol VARCHAR(20)
        )

  ALTER TABLE dbo.AlphaID 
    ADD CONSTRAINT CK_AlphaID_MaxID 
        CHECK (ID < 26999)

The key here is two things... the ID column and the calculated column called AlphaID. Neat formula, huh?

And, to demo the table in the code above, let's do 10,000 quick little inserts and then select from the table... don't blink or you'll miss it...

Code:

INSERT INTO AlphaID (SomeOtherCol)
 SELECT TOP 10000 'TestOnly'
   FROM Master.dbo.SysColumns sc1,
        Master.dbo.SysColumns sc2

 SELECT * FROM AlphaID

--Jeff Moden

p.s. The first number is A000 and the rollover numbers are B000, C000, D000, etc. It would be a fair bit more difficult to generate rollover numbers that were B001, C001, D001, etc.

Jeff Moden · November 17th, 2006, 09:06 PM

Some feedback would be nice... how'd it work for you?

--Jeff Moden

merydith · November 27th, 2006, 07:27 AM

Hi,

I have a similar problem to the above and also using MS SQL. I am trying to create auto ID with this format

YYYY-999999 where YYYY is the year and 999999 is the incremental serial number.

The serial number will be reset each year

Any idea how to go about it?

Thanks in adv

rstelma · November 27th, 2006, 01:46 PM

Lynn....??? Are you there?

Jeff Moden · November 27th, 2006, 03:38 PM

Quote:

quote:Originally posted by merydith
Hi,

I have a similar problem to the above and also using MS SQL. I am trying to create auto ID with this format

YYYY-999999 where YYYY is the year and 999999 is the incremental serial number.

The serial number will be reset each year Any idea how to go about it?

Thanks in adv

That'll be a pain... I'll have to get back to you...

--Jeff Moden

Jeff Moden · November 27th, 2006, 03:57 PM

Ok... here's the skinny on this, Merydith... in order to do this, we're going to need to make a "Sequence table" with a little logic built into the "GetNextID" proc that we'll also need to build. I've had a fair amount of experience in these and I can tell you for certain, even when done properly, they can be a major, major cause of deadlocks.

You need to tell whoever is designing this "Serial Number" that and ask them if there's any other way they can create serial numbers. The code isn't hard, but it's deadly to a system if the code is EVER used within an explicitly declared transaction.

--Jeff Moden

robprell · November 27th, 2006, 10:45 PM

You could use two columns. One simply an id-identy value. The other triggered by an insert trigger that triggers your special logic for creating "YYYY-999999". Where the 999999 value is stored in a single row single field table. The trigger would also have to know what year it was (servers date) so it could compare with the YYYY value to know if there is a new year. Or it could simply populate with the current system year every time. By doing this you can still use the id-identity value for joining to other tables and not use the slower "YYYY-999999". No rule says you can't have two unique identifiers on a single table.

Just some ideas....

Jeff Moden · November 28th, 2006, 10:00 AM

Licensing is a capital expense that can be written off for tax purposes but, RobPrell and I both have the right idea... why not just do everything in one RDBMS or the other?

--Jeff Moden