auto incrementing id in sql server

Question

How to auto increment an id in SQL Server whenever a new row is inserted in the table? This id is primary key of the table.

Answer 1

You're looking for IDENTITY.

e.g.

CREATE TABLE MyTable
(
ID INTEGER IDENTITY(1,1) PRIMARY KEY,
FieldA VARCHAR(10)
)

The ID field will auto increment, starting at 1 and increasing by one each time.

Answer 2

And to return the id in your code lookup scope_identity() and the OUTPUT clause. De not use @@identity as it can return the wrong value if triggers are put on the table, therefore it is not safe to use if you value data integrity.