Auto increment using identity in SQL Server

Auto increment feature can be implemented very easily in an SQL server table using the IDENTITY property.

The following query can be used for creation of one such table that can implement auto increment feature on one of its columns

CREATE TABLE EMPLOYEE(EID INT IDENTITY(1,1), ENAME VARCHAR(30), E_DOB DATETIME)

when the above query is executed, a table with the name EMPLOYEE is created with a column EID whose values are auto incremented.

Syntax
The syntax of the identity property is very simple

DataType IDENTITY [ (seed ,increment) ]

DataType : Identity only supports INT, BIGINT, SMALLINT, TINYINT, DECIMAL data types

seed: seed is the value that is assigned for the first row that is loaded into the table

increment: Is the incremental value that is added to the identity value of the previous row that was loaded.

seed and increment are optional, but not individually, that means we have to either specify both the values or none of them.

If the values are not provided for seed and increment than the default values of (1, 1) are taken

For more information on IDENTITY property visit  http://msdn.microsoft.com/en-us/library/aa933196(SQL.80).aspx

Advertisement

---