Help: autonumber in MS SQL server 1

[skurpyun]

new to the sql server world and I have a simple(?) question for anyone here who would like to help. How do i go about creating a column in an sql table where the number for each row is automatically generated? This column would be the primary key for the table. In short, how do i go about creating an 'auto-number' field?

 

[SQLSister]

This threw me too when I converted to SQL server from Access, but it isn't hard. From Enterprise manager, go to the Design table menu item (right click on the table to find it) and define the data type as integer and set the Identity property to yes. This will autonumber the field and will set the field property to where it will not allow nulls. Then you can set the field as a primary key.

Identity seed and Identity increment proprties are also helpful in perfoming autonumbering. The an identity field will start counting at 1 unless you set the identity seed. If you want the first record to start at 100, you would set this property to 999. Identity increment will tell it whether to increment by 1 (default value) or more. If you set it to 5 for instance your numbers would be 1, 6, 11, etc.

In T-SQL, it goes like this (Creates a table with one field which is an autonumber and sets the field as the primary key):

CREATE TABLE [dbo].[Aircraft4Sale]
([ListingNo] [int] IDENTITY (1000, 1) NOT NULL)
ON [PRIMARY]
GO

ALTER TABLE [dbo].[Aircraft4Sale] WITH NOCHECK ADD
CONSTRAINT [PK_Aircraft4Sale] PRIMARY KEY CLUSTERED
([ListingNo]) ON [PRIMARY]
GO

Of course if the table is already created you would use the Alter Table command.

Hope this helps.

Judy

 

[skurpyun]

SQLSister,
1. nice handle...!
2. Thank you very much for helping me out. I kept staring at it and couldn't figure it out. And of course it turned out to be so easy! Thanx again.