What is an Identity property?

An Identity Column in a table is a column whose values are generated automatically by SQL Server. The values of this column gives the Identity for each row.

                 It works only for Integer columns and also used for numeric data types which has a scale of 0. But it does not work for character type fields. We need not pass value to Identity column when we are inserting data into the table.

This is similar to Sequence in Oracle. 

Syntax:

IDENTITY (seed, increment)

Seed: is the value that is going to be inserted in the first row of the Identity column.

Increment: Is the incremental value that should added to the identity value of the previous row that was inserted.

By default it takes Identity (1, 1) if we are have not mentioned the Seed and Increment.

Example:

/*————————————————————
Identity Column example.
————————————————————-*/
USE [AdventureWorks2012];
GO

CREATE TABLE [dbo].[Idenntity_Check](
[ID_Column] int IDENTITY(1,1),
[Emp_Fname] varchar(20),
[Emp_Mname] varchar(20),
[Emp_Lname] varchar(20)
);
————————————————————-
INSERT [dbo].[Idenntity_Check]
([Emp_Fname],[Emp_Mname],[Emp_Lname])
VALUES(‘Chandra’, ‘Sekhar’, ‘Raparthi’),
(‘Avinash’, ‘Reddy’, ‘Munnangi’),
(‘Chander’, ‘Sharma’, ‘ ‘)
————————————————————-
SELECT * FROM [dbo].[Idenntity_Check]
————————————————————-

Even though we are not passing the values for Identity column, it is generating values for the [ID_Column] in the below result set.

Result set:

Identity Column Example

Leave a Reply

Your email address will not be published. Required fields are marked *