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
No comments:
Post a Comment