Autonumbering & Identity Columns

By Bill Graziano on 25 June 2000 | 2 Comments | Tags: Identity


John writes "I'd like to be able to automatically number each row as it comes into the table. Is possible to do this? How do I know what the value of the row I just inserted is?"

Well John,

What you can do is set up an identity column in SQL Server. This will autonumber the rows. In Enterprise Manager, start by setting a column with a datatype of int. This column cannot allow nulls. Check the Identity checkbox and the Identity Seed and Identity Increment will be set to 1 automatically. These do just what they say they will. The Identity Seed is the value of the first entry in the table. The Identity Increment is the value that will be added to the previous row to get the next identity value.

Note that if you delete all the rows in a table and start adding rows, the identity column will pick up where it left off. You can reset it but that's a topic for another day.

The script to create a table looks like this:

CREATE TABLE [dbo].[Items] (
        [ItemID] [int] IDENTITY (1, 1) NOT NULL ,
	[ItemName] [char] (50) NOT NULL
) 

You can use the @@IDENTITY function to return the value you just inserted.

-graz

Discuss this article: 2 Comments so far. Print this Article.

If you like this article you can sign up for our weekly newsletter. There's an opt-out link at the bottom of each newsletter so it's easy to unsubscribe at any time.

Email Address:

Related Articles

Efficiently Reuse Gaps in an Identity Column (9 February 2010)

How to Insert Values into an Identity Column in SQL Server (6 August 2007)

Custom Auto-Generated Sequences with SQL Server (24 April 2007)

Using the OUTPUT Clause to Capture Identity Values on Multi-Row Inserts (14 August 2006)

Understanding Identity Columns (9 March 2002)

Identity and Primary Keys (28 February 2001)

Alternatives to @@IDENTITY in SQL Server 2000 (19 September 2000)

Uniqueidentifier vs. IDENTITY (12 September 2000)

Other Recent Forum Posts

Replication fails after snapshot delivery (4 Replies)

Dude about partition an existing table (1 Reply)

successive records with self join (2 Replies)

SP Dynamic Update (3 Replies)

trim empty spaces (2 Replies)

Group/Union Statement (1 Reply)

Failover Clustering (2 Replies)

Stored Procedure Parameters Update Dynamically (1 Reply)

Subscribe to SQLTeam.com

Weekly SQL Server newsletter with articles, forum posts, and blog posts via email. Subscribers receive our white paper with performance tips for developers.

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -