Image courtesy of Grant Cochrane / FreeDigitalPhotos.net
IDENTITY as SQL Property and IDENTITY as SQL Function
IDENTITY as SQL Property:
Almost all the time, We have a table with a primary key and would like to insert a new value automatically in the primary key field.
To achieve this, We have IDENTITY property in SQL which creates an auto-increment field in the table.
IDENTITY [ (seed , increment) ]
An identity column has a name, initial seed and step. Seed is the starting value for IDENTITY and Increment is the incremental value.
In the example, Seed is 1 and Increment is 1.
Note: In case if values are not specified the default is (1,1).
Example: Create a new table Employee with Employee Id as an IDENTITY column.
CREATE TABLE Employee ( Employee_ID int IDENTITY(1,1) PRIMARY KEY, Employee_FirstName varchar(255), Employee_LastName varchar(255) ) INSERT INTO Employee (Employee_FirstName, Employee_LastName) VALUES (‘Code’, ‘Spread’) INSERT INTO Employee< (Employee_FirstName, Employee_LastName) VALUES (‘Code1’, ‘Spread1’)
It returns last identity value entered into a table.
Example: The following example inserts a row into a table with an identity column (Employee_ID)
INSERT INTO Employee (Employee_FirstName, Employee_LastName) VALUES (‘Code2’, ‘Spread2’) GO SELECT @@IDENTITY AS 'Identity'; GO
Here, It uses @@IDENTITY to display last identity value entered into a table.
What if I want to insert values into an IDENTITY column?
SQL provides SET IDENTITY_INSERT to insert values into an IDENTITY column. But before that,
set the IDENTITY_INSERT option to “ON”.
Note: The setting of SET IDENTITY_INSERT is set at execute or run time.
SET IDENTITY_INSERT dbo. Employee ON; CREATE TABLE Employee ( Employee_ID int IDENTITY(1,1) PRIMARY KEY, Employee_FirstName varchar(255), Employee_LastName varchar(255) ) INSERT INTO Employee VALUES (3,‘Code1’, ‘Spread1’) SET IDENTITY_INSERT dbo. Employee OFF;
The following line resets the Identity value for the Customer table to 0 so that the next record added starts at 1.
DBCC CHECKIDENT('Customer', RESEED, 0)
IDENTITY as SQL Function
The IDENTITY function can only be used when the SELECT statement has an INTO clause.
SELECTIDENTITY (INT, 100, 5) ASNEW_ID,Employee_FirstName,Employee_LastName) INTO #tempEmployee From Employee
- The first parameter for the IDENTITY function is that data type
- The second parameter is the seed.
- The third parameter is the increment.
Note: Only one Identity column is possible for a table.