Index autoincrement for Microsoft SQL Server 2008 R2

sql sql-server tsql identity
Magnetic_dud picture Magnetic_dud · Nov 19, 2010 · Viewed 54.5k times · Source

I created a new table in SQL Server 2008 R2, and i would like that the index is on autoincrement. How to do that? There is no identity data type; i selected int

Answer

marc_s picture marc_s · Nov 19, 2010

In SQL Server, it's not a separate datatype ("autoincrement") - but you can define an INT column to be an IDENTITY.

How are you creating your table - visual designer or T-SQL script??

In T-SQL, you would use:

CREATE TABLE dbo.MyTable(ID INT IDENTITY(1,1) ......

and in the visual table designer, you need to check:

alt text

It's an option for a column of type INT - you can define the seed (starting value) and the increment - typically both are set to 1.

Related questions

Handling identity columns in an "Insert Into TABLE Values()" statement?

In SQL Server 2000 or above is there anyway to handle an auto generated primary key (identity) column when using a statement like the following? Insert Into TableName Values(?, ?, ?) My goal is to NOT use the column names at all.

Read More
@@IDENTITY, SCOPE_IDENTITY(), OUTPUT and other methods of retrieving last identity

I have seen various methods used when retrieving the value of a primary key identity field after insert. declare @t table ( id int identity primary key, somecol datetime default getdate() ) insert into @t default values select SCOPE_IDENTITY() --returns 1 select @@…

Read More
Reuse identity value after deleting rows

Is it possible to reuse an identity field value after deleting rows in SQL Server 2008 Express? Here is an example. Suppose I have a table with an Id field as a primary key (identity). If I add five rows, I …

Read More