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

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


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')
GO

SELECT * FROM [dbo].[Employee]
GO


-- Inserting Explicit value in IDENTITY column:

SET IDENTITY_INSERT [dbo].[Employee] ON

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

SET IDENTITY_INSERT [dbo].[Employee] OFF
GO


SELECT * FROM [dbo].[Employee]
GO

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

SELECT * FROM [dbo].[Employee]
GO

 

–> 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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: