SQL Server Performance Forum – Threads Archive

Automatic Increment field

How can I create a numeric field in a table that automatically increments? i.e as soon as i run the application my serial no. field should be incremented automatically. Thanks
Issolla

An Identity column is used in SQL server to create a surrogate key value for a table. This will be a unique identifier usually in sequential order. Starting at some predefined number, the Identity column increments every time a new record is added to the table. You need to create the field by specifying IDENTITY with first number and seed value, refer to the books online for more information on IDENTITY topic. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

I did that. The problem in this is I am getting an error message saying "cannot insert explicit value for identity column in a table<tablename> when identity_insert is set OFF"
After that in the query analyser I have given the command "SET IDENTITY_INSERT inbound ON". It successfully completed. After that also I am getting the same error

The IDENTITY property is maintained by SQL Server itself. Usually you use SET IDENTITY_INSERT ON to initially insert data into such a column. Thereafter SQL Server does all the work. No need to try to provide a value yourself. Either your script or your app is trying to explicitely insert a value and this won’t work. Btw, you’ll also find that you can’t update such a column. —
Frank
http://www.insidesql.de

]]>