How do I declare a field to be an identity in Microsoft SQL Server?

Question

For example, the column ID. I want it to start at 1, and then increment by 1 for each row.

Thank you!

Answer 1

create table TableName
(
    ID int identity(1, 1) primary key
)

The identity(1, 1) is what's important here. The first argument represents the seed (meaning initial) value, the second argument represents the increment rate. This means that the first record will have a value of 1, and each subsequent record will increment this value by 1.

Had we specified, for example, (7, 2), the first record would have a value of 7, then each record would increment by 2 (so 7, 9, 11, and so on).

Note that obviously the primary key part is not required, but the identity column in a table (if it has one) is usually the primary key. If it isn't in your case, then just remove that portion.

Answer 2

set column to identity column

CREATE TABLE dbo.Tmp_DataTable  
(  
  id int NOT NULL IDENTITY (1, 1)
  col1 varchar(50)
)

Answer 3

For SQL 2005/2008:

Under Column Properties for your column, expand "Identity Specification", then change "IsIdentity" to true.

Answer 4

Programmatically or manually?

In SQL Server - right-click the table, select modify, click on the column, under "Identity Specification" set "Is Identity" to true and leave the increment and seed each at 1.

(If you meant programmatically... a couple of people already answered as I was typing :) )