Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
|
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old November 17th, 2006, 01:07 AM
Registered User
 
Join Date: Nov 2006
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default Auto generate IDs in MS SQL...

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


 
Old November 17th, 2006, 04:25 AM
Friend of Wrox
 
Join Date: Oct 2006
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default

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.
 
Old November 17th, 2006, 09:06 PM
Friend of Wrox
 
Join Date: Oct 2006
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default

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

--Jeff Moden
 
Old November 27th, 2006, 07:27 AM
Registered User
 
Join Date: Nov 2006
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

 
Old November 27th, 2006, 01:46 PM
Friend of Wrox
 
Join Date: Jul 2003
Posts: 599
Thanks: 6
Thanked 3 Times in 3 Posts
Default

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

 
Old November 27th, 2006, 03:38 PM
Friend of Wrox
 
Join Date: Oct 2006
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default

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
 
Old November 27th, 2006, 03:57 PM
Friend of Wrox
 
Join Date: Oct 2006
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default

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
 
Old November 27th, 2006, 10:45 PM
Friend of Wrox
 
Join Date: Aug 2004
Posts: 385
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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....

 
Old November 28th, 2006, 10:00 AM
Friend of Wrox
 
Join Date: Oct 2006
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
How to Generate Unique values for IDs mphare XSLT 7 November 6th, 2008 12:20 PM
Generating auto-ids using asp and sql Shuchik Classic ASP Basics 2 August 30th, 2007 01:57 AM
Custom Generate IDs wackoyacky SQL Language 4 June 4th, 2007 08:53 AM
How to Auto Generate ID (Primary Key) SQL Database havering SQL Server ASP 1 December 9th, 2004 05:33 AM
How to Auto Generate ID (Primary Key) SQL database havering SQL Server 2000 9 December 1st, 2004 10:38 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.