Home > SQL Basics > SQL Basics – IDENTITY property of a Column (in a table)

SQL Basics – IDENTITY property of a Column (in a table)

October 19, 2010 Leave a comment Go to comments

With IDENTITY property you can:

1. Creates an identity column in a table.

2. Used for generating key values, based on the current seed & increment.

3. Each new value for a particular transaction is different from other concurrent transactions on the table.

–> You can check the demo about IDENTITY property here:


–> IDENTITY property on a column does not guarantee:

1. Uniqueness of the value,

2. Consecutive values within a transaction,

3. Consecutive values after server restart or other failures,

4. Reuse of values,

–> SQL Script used in Demo:

-- IDENTITY property of a Column

CREATE TABLE [dbo].[Employee](
	[EmployeeID]	int				NOT NULL IDENTITY (100, 1),
	[EmployeeName]	nvarchar(100)	NOT NULL,
	[Gender]		nchar(1)		NULL,
	[DOB]			datetime		NULL,
	[DOJ]			datetime		NULL,
	[DeptID]		int				NULL

INSERT INTO [dbo].[Employee] (EmployeeName, Gender, DOB, DOJ, DeptID)
VALUES ('MANOJ PANDEY', 'M', '1990-01-01', '2010-01-01', 101)

INSERT INTO [dbo].[Employee] (EmployeeName, Gender, DOB, DOJ, DeptID)
VALUES ('JHON K', 'M', NULL, '2010-01-01', NULL)

INSERT INTO [dbo].[Employee] ([EmployeeName])
VALUES ('Brock H')

SELECT * FROM [dbo].[Employee]

-- Inserting Explicit value in IDENTITY column:


INSERT INTO [dbo].[Employee] ([EmployeeID],[EmployeeName])
VALUES (1000, 'Brock H')


SELECT * FROM [dbo].[Employee]

INSERT INTO [dbo].[Employee] ([EmployeeName])
VALUES ('Jhon B')

SELECT * FROM [dbo].[Employee]


–> Check more articles on IDENTITY property:

RE-SEED an IDENTITY value of a Column

Using IDENTITY() function with SELECT into statement

All about IDENTITY columns, some more secrets

IDENTITY property behavior with SQL Server 2012 and above

Categories: SQL Basics Tags: ,
  1. No comments yet.
  1. October 19, 2015 at 7:46 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: