autonumber in sql server

Lynn · December 6th, 2006, 11:31 AM

Thanks for the help. The code looks logical to me, so hopefully it works. Thanks! :)

MunishBhatia · December 6th, 2006, 02:08 PM

Hello
the column to which u want to assign autogenerate number set IDENTITY Property as follows:

CREATE TABLE dbo.tablethree
    (
    /*
    [ID]is the Column Name,
        Int is its Datatype of an Integer,
        As this is our Identity Column we do not want to allow null values,
        IDENTITY (1,1) Sets the Column as Identity, (1 represents the very first seed to start with
        ,1) is the amount to increment by
    */
    [ID] int NOT NULL IDENTITY (1, 1),

    --here you write another colum declaration

it will work fine and will generate the auto values

thanks......

okboy · March 24th, 2007, 11:41 AM

Quote:

quote:Originally posted by Jeff Moden
...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.

okboy · March 24th, 2007, 11:47 AM

[quote]quote:Originally posted by okboy

Quote:

Originally posted by Jeff Moden

Quote:

...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, if i wanna let AlphaID to be primary key then how to do? example: A000, A0001 to be primary key.

Peso · March 24th, 2007, 03:12 PM

What happens after "Z9999"?

Jeff Moden · March 29th, 2007, 06:34 PM

Quote:

quote:Originally posted by Peso
What happens after "Z9999"?

Boooommmmm! That's why I hate these types of numbering systems... they're pretty well finite.

--Jeff Moden