How to create an AutoCounter column in a table in SQL 2008 R2?

Question

There is nothing in the "Data Type" drop-down list that indicates an automatic counter.
I chose int , and I assume there is a setting elsewhere to turn it into an auto-counter.
The idea is to have a 100% unique identifier for my primary key, and each time a row is added, it gets the next available identification number assigned to it.

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

Answer 1

What you need is called IDENTITY in SQL Server terminology.

The column should be a numeric data type ( INT usually, check the range of values ​​to suit your needs ) before you can set the IDENTITY column - you can see the list in the screenshot under Table Designer . There can only be one IDENTITY column in a table — this will change in SQL Server 2011 when they start supporting sequences.

Answer 2

 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 this is not a primary key, you do not need the cluster part of the primary key.

Part (1.1) indicates that the numbering will start from 1 and increase by 1. Do not expect identifiers to ever have spaces. If the rollback insert or record is deleted later, the spaces will not be filled. You can also start with any number you choose, and you can even increase it by another number, but (1,1) is the default and most common value.

It is best to create tables (and especially modify them) using scripts, then you can pass them to the source control, like any other code, and you are ready to deploy them.

Answer 3

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