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

For example, the column ID. I want it to start at 1, and then 开发者_如何学运维increment by 1 for each row.

Thank you!

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.

set column to identity column

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

For SQL 2005/2008:

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

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 :) )