|
Subject:
|
Auto generate IDs in MS SQL...
|
|
Posted By:
|
Lynn
|
Post Date:
|
11/17/2006 12:07:25 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
|
|
Reply By:
|
Jeff Moden
|
Reply Date:
|
11/17/2006 3:25:50 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...
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...
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.
|
|
Reply By:
|
Jeff Moden
|
Reply Date:
|
11/17/2006 8:06:20 PM
|
Some feedback would be nice... how'd it work for you?
--Jeff Moden
|
|
Reply By:
|
merydith
|
Reply Date:
|
11/27/2006 6:27:18 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
|
|
Reply By:
|
rstelma
|
Reply Date:
|
11/27/2006 12:46:26 PM
|
Lynn....??? Are you there?
|
|
Reply By:
|
Jeff Moden
|
Reply Date:
|
11/27/2006 2:38:14 PM
|
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
|
|
Reply By:
|
Jeff Moden
|
Reply Date:
|
11/27/2006 2:57:26 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
|
|
Reply By:
|
robprell
|
Reply Date:
|
11/27/2006 9:45:41 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....
|
|
Reply By:
|
Jeff Moden
|
Reply Date:
|
11/28/2006 9:00:43 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
|
