Tuesday, August 5, 2014

IDENTITY Column in SQL Server

In this article, I am giving a quick overview about IDENTITY FUNCTION in SQL Server. After completing this article you will understand:

What is IDENTITY Function in SQL Server?
How to create IDENTITY Column in SQL Server?
How to explicitly insert Identity Column Data?
How to find all Identity Columns in a Database?
How to Reset Identity Function SEED value?
How to remove Identity Function from Column?
How to add Identity Function after creating the Table?

Please give your valuable suggestions and feedback to improve this article.

What is IDENTITY Function in SQL Server?

Generally for any column if we want to insert only unique values then we can make that column as IDENTITY column. It automatically inserts a unique value whenever a new record will be inserted into table. We can have only one IDENTITY column in a table. Generally we use this on Primary Key column. We have to define the IDENTITY Column at the time of Table Creation. After creating a table you can’t use ALTER Command to add IDENTITY function. Once IDENTITY Column is created we can’t use ALTER Command to drop that IDENTITY Function. IDENTITY Column data range will depend on the Column data type. If IDENTITY Value reaches to the Max data range then new record can’t be inserted it will raise error so it’s better to choose a proper data type for the IDENTITY Column. We don’t provide IDENTITY column value in INSERT Statement means we can’t explicitly insert any values into the IDENTITY column, Identity column value automatically gets populated in Identity Column. You can’t perform Insert operation on Identity Column; if you want to do so then you have to set Identity Insert property to ON and you have to define column list in the insert statement, note you can't perform Update operation on Identity column means We can’t update Identity Column value.. In this article I will try to explain every concept with a simple example.

How to create IDENTITY Column in SQL Server?

Syntax to Create Identity Column   

ColumnName <DataType>  [Data Width] IDENTITY (SEED,INCR)

SEED – It is the staring value for the IDENTITY Function
INCR – It is the difference between to subsequent values generated by the IDENTITY Function.
Both SEED and INCR are optional, if value is not specified then 1 and 1 will be taken as default

Let’s create an IDENTITY Column

USE TEACHMESQLSERVER
GO
IF OBJECT_ID('DEP','U') IS NOT NULL
DROP TABLE DEP
GO
CREATE TABLE DEP(DEPID INT IDENTITY(1,1), DEPNAME VARCHAR(MAX))
/*
We have successfully created IDENTITY Column (DEPID), now lets insert data into DEP table.
*/

Inserting new record in Identity Column

INSERT INTO DEP VALUES (1,'HR')
/*REMEMBER WE CAN'T EXPLICITLY INSERT INTO IDENTITY COLUMN IF IDENTITY_INSERT IS SET TO OFF, THAT IS WHY ABOVE QUERY FAILED WITH BELOW ERROR MESSAGE*/

Msg 8101, Level 16, State 1, Line 1
An explicit value for the identity column in table 'DEP' can only be specified when a column list is used and IDENTITY_INSERT is ON.

GO
INSERT INTO DEP VALUES ('HR')
GO
SELECT * FROM DEP
/*WE SUCCESSFULLY INSERTED THE RECORD, LET'S INSERT MORE RECORDS INTO DEP TABLE*/
GO
INSERT INTO DEP VALUES ('IT'),('FINANCE'),('MARKETING'),('SALES')
GO

SELECT * FROM DEP

You can see DEPID value was automatically generated by the Identity function and all generated values are unique. DEPID value started with 1 and every new value is just incremented by 1. It is because at the time of declaration we gave SEED value as 1 and INCR value as 1.

How to explicitly insert Identity Column Data?

If you want to Insert values into Identity column then first you have to set  identity insert to on and second you have to define list of columns in the insert statement.


SET IDENTITY_INSERT DEP ON

GO

INSERT INTO DEP (DEPID,DEPNAME) VALUES(1000,'FUNCTIONAL')

GO

SELECT * FROM DEP

You successfully inserted a row with Identity column value. If you want Identity function to generate unique value and automatically and insert it into the Identity column then You have to Set Identity insert property to OFF.

 SET IDENTITY_INSERT DEP OFF


How to delete records from Identity Table?

You can delete rows without any problem, let’s delete recently added record.

DELETE FROM DEP WHERE DEPID =1000


Note- Delete Command will not reset the Identity Column value, only Truncate can reset Identity column value. Identity column SEED value is always incrementing, it never fill the Gap which were created after deleting the records. Suppose your SEED value is 5 and you inserted a new record that records will be inserted with value 6. If you delete that newly inserted row and reinsert then it will be inserted with value 7 not 6.

How to find all Identity Columns in a Database?

You can easily find all Identity columns list from IDENTITY_COLUMNS System Table using the below query.

SELECT * FROM SYS.IDENTITY_COLUMNS


If you want to find out Identity column Table name for above query then you can pass Object Id in below query.

SELECT Name FROM SYS.OBJECTS WHERE OBJECT_ID=306100131 /*In my case Object id is 306100131, it may differ in your case*/


System Function for Identity column

1-      IDENT_CURRENT – It will return the current value of Identity Function
Select IDENT_CURRENT('DEP') Returns 5
2-      IDENT_SEED – It will return the SEED Value  of Identity Function
Select IDENT_SEED('DEP')Returns 1
3-      IDENT_INCR – It will return the INCR value of Identity Function
Select IDENT_INCR('DEP')Retunrs 1

How to Reset Identity Function SEED value?

If you want to reset the SEED Value then you can reset using DBCC CHECKIDENT command. Identity column will allow duplicates values until it was not created with UNIQUE constraint. Suppose we created Identity function on a column that has not UNIQIUE Constraint defined, if we reset SEED value to already existing value in that case it will insert it. When we create Identity function on Primary Key column in that case it will not allow duplicates. We will understand this with example.

Syntax

DBCC CHECKIDENT (TABLENAME, RESEED, NEWVALUE)


Let’s change SEED value to 4 and insert new record

GO

DBCC CHECKIDENT (DEP,RESEED,4)

On successful run you will get below message

Checking identity information: current identity value '5', current column value '4'.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.


GO

INSERT INTO DEP VALUES('FUNCTIONAL’)

GO

SELECT * FROM DEP

As you can see Identity function taking latest SEED value for new record. You can also see our DEPID has duplicates records it is because DEPID column doesn’t have Primary Key Constraint or Unique Constraint defined.

How to remove Identity Function from Column?

You can’t use ALTER command to Drop Identity Function, but there is work around to handle this.

Drop Identity Column

If you don’t want to keep data then you can drop the column and recreate the column using ALTER command.

Method1 -  
GO
ALTER TABLE DEP DROP COLUMN DEPID
GO
ALTER TABLE DEP ADD DEPID INT
GO
SELECT * FROM DEP

But who wants to lose their data, every single record is important. Below example shows without losing the data we can drop the Identity column.

Let’s Drop DEP table and recreate it with Identity function, because in our previous exercise we dropped the Identity column.

Method2-
DROP TABLE DEP
GO
CREATE TABLE DEP(DEPID INT IDENTITY(1,1), DEPNAME VARCHAR(MAX))
/*LET'S POPULATE SOME DATA INTO DEP TABLE*/
GO
INSERT INTO DEP VALUES ('IT'),('FINANCE'),('MARKETING'),('SALES')
GO
SELECT * FROM DEP
GO
SELECT * INTO ##DEP FROM DEP /*Copying DEP table data into Temp table DEP*/
GO
DROP TABLE DEP /*Dropping DEP Table*/
GO
CREATE TABLE DEP(DEPID INT,DEPNAME VARCHAR(MAX)) /*Creating DEP Table with same definition but without Identity function on DEPID*/
INSERT INTO DEP SELECT * FROM ##DEP /*Populating Old DEP table data into DEP table from TEMP DEP table*/
DROP TABLE ##DEP /*Dropping Temp DEP table*/

 We have successfully dropped the identity column without losing the data. You can confirm this by running the below queries.
GO
SELECT * FROM DEP
GO
SELECT * FROM SYS.IDENTITY_COLUMNS

How to add Identity Function after creating the Table?

You can’t use ALTER command to ADD Identity Function after creating the table, but there is work around to handle this.

In our previous exercise we DROPPED the Identity function from DEPID column; in the exercise we will add Identity Column on existing DEP Table.

GO
SELECT * INTO ##DEP FROM DEP /*Copying DEP table data into Temp table DEP*/
DROP TABLE DEP /*Dropping DEP Table*/
GO
SELECT MAX(DEPID) FROM ##DEP /*Finding Max DEPID and we will use that when we will define IDENTITY FUNCTION*/
GO
CREATE TABLE DEP(DEPID INT IDENTITY(5,1),DEPNAME VARCHAR(MAX)) /*Creating DEP Table with same defintion WITH Identity function on DEPID*/
GO
SET IDENTITY_INSERT DEP ON /*SETTING IDENTITY INSERT ON SO THAT WE CAN INSERT RECORDS INTO THE TABLE*/
GO
INSERT INTO DEP (DEPID,DEPNAME) SELECT * FROM ##DEP  /*Copying TEMP DEP table data into REAL DEP TABLE*/
GO
SET IDENTITY_INSERT DEP OFF /*SETTING IDENTITY INSERT OFF SO THAT IDENTITY FUNCTION CAN WORK IN TABLE*/
GO

We have successfully ADDED INDETITY FUNCTION ON DEPID column without losing the data. You can confirm this by running the below queries.

SELECT * FROM DEP
GO
SELECT * FROM SYS.identity_columns

I hope you liked the article, please don't forget to comment if you find any mistake or you want me to include anything in this article.

No comments: