How do I Create an AutoCounter Column in a Table in SQL 2008 R2?

There is nothing in the "Data Type" drop-down that indicates an auto-counter.

I selected int and I am assuming there is a setting somewhere else to turn it into an auto-counter.

The idea is to have a 100% un开发者_开发知识库ique ID for my primary key, and every time a row is added it gets the next available ID number assigned to it.

http://img64.imageshack.us/img64/2472/primarykey.png

What you're after is called IDENTITY in SQL Server terminology.

The column must be a numeric data type (INT typically, check the range of values to what suits your needs) before you can set the column to be IDENTITY -- you can see the listing in your screenshot, under Table Designer. There can only be one IDENTITY column per table -- this will change in SQL Server 2011 when they start support of sequences.

CREATE TABLE dbo.mytable
     (
    [MY_ID] int identity (1,1) PRIMARY KEY CLUSTERED NOT NULL,
    [LAST_NAME] [nvarchar](50) NULL,
    [FIRST_NAME] [nvarchar](30) NULL
     ) 

Of course if it isn't the primary key, you don't need the primary key clustered part.

The (1,1) part indicates that the numbering will start at 1 and increment by 1. Do not expect identities to never have gaps though. If an insert is rolled back or a record is deleted later, the gaps will not be filled in. You also can start at any number you chose and you can even increment in a different number but (1,1) is the default and is the most common value.

It is best to create tables (and especially alter them) using scripts, then you can commit them to source control like any other code and you have them ready to deploy.

Mark it as a Primary key and set the Identity column to yes. This will make it auto-increment.