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?
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.
Method 1 -
GO
SELECT * FROM tbl_Example
ROLL SNAME SCLASS ROWNUM
1 JOYDEEP 1 1
-- Execution-2
EXECUTE my_proc
@param_roll = 2,
@param_name = 'SUKAMAL',
@param_calss = 1
SELECT * FROM tbl_Example
ROLL SNAME SCLASS ROWNUM
1 JOYDEEP 1 1
2 SUKAMAL 1 2
-- Execution-3 [ Primary Key Error ]
EXECUTE my_proc
@param_roll = 2,
@param_name = 'SANGRAM',
@param_calss = 1
0 row(s) affected)
ERROR EXISTS
Checking identity information: current identity value '3', current column value '2'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
SELECT * FROM tbl_Example
ROLL SNAME SCLASS ROWNUM
1 JOYDEEP 1 1
2 SUKAMAL 1 2
-- Execution-4
EXECUTE my_proc
@param_roll = 3,
@param_name = 'SANGRAM',
@param_calss = 1
SELECT * FROM tbl_Example
ROLL SNAME SCLASS ROWNUM
1 JOYDEEP 1 1
2 SUKAMAL 1 2
3 SANGRAM 1 3
How to insert rows into table with identity column
Imagine you have two billion rows on employee table, you have to archive this table. After archiving you are going to insert back only 110 million rows to employee table from employee_temp, There is a column emp_id is identity, so how can you insert data back from archived tables. Here you can find the tested solution , first you need to disable an indentity column to auto insert.
Step 1: Make sure to stop all process(application connection) against employee table
Step2 :Create a new table employee_copy from employee table
Find max(emp_id) from employee eg: 2000000001and RESEED this value to employee_copy
DBCC CHECKIDENT (employee_copy , RESEED, 2000000001)
Step3: Rename the orginal table using sp_rename 'employee' , 'employee_temp'
Step4: Rename the new table using sp_rename 'employee_copy' , 'employee'
Step5: Insert the rows from employee_temp table to employee table
SET IDENTITY_INSERT employee ON
GO
INSERT INTO employee (emp_id, emp_name, country_code)
SELECT emp_id, emp_name, country_code from employee_temp(nolock) where emp_id > 110000000
GO
SET IDENTITY_INSERT employee OFF
GO
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?
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
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.
Method 1 -
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.
Method 2-
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
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.
Method 2-
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
IDENTITY Columns Violation
We all know about the identity columns of SQL Server and how important it is. This article is related to it but I am representing this article to solving a common proble.
First take a quick look about the identity columns and how it works. Then we are going to discuss about the problem and how to solve it programmatically.
Definition of Identity Columns
An IDENTITY column contains a value for each row, generated automatically by Adaptive Server that uniquely identifies the row within the table.
Each table can have only one IDENTITY column. You can define an IDENTITY column when you create a table with a create table or select into statement, or add it later with an alter table statement. IDENTITY columns cannot be updated and do not allow nulls.
You define an IDENTITY column by specifying the keyword identity, instead of null or not null, in the create table statement. IDENTITY columns must have a datatype of numeric and scale of 0. Define the IDENTITY column with any desired precision, from 1 to 38 digits, in a new table:
CREATE TABLE table_name
(column_name INT NOT NULL IDENTITY(1,1))
The Problem comes for Identity
One of my friends told me that, he has a problem for IDENTITY columns. He told me that when he make the INSERT entry into table objects, he never check the uniqueness of the records for that the primary key violation error occurs. He just shows the error.
The problem is the table objects contain identity columns. The value of the identity columns increases each time, even the PK violation exists.
The problem is mentioned by T-SQL statements
Step-1 [ Create the Base Table with IDENTITY columns ]
-- Table defination
DROP TABLE tbl_Example
CREATE TABLE tbl_Example
(
ROLL INT NOT NULL PRIMARY KEY,
SNAME VARCHAR(50) NULL,
SCLASS INT NULL,
ROWNUM INT NOT NULL IDENTITY(1,1)
)
Step-2 [ Normal Insertions of Values ]
INSERT INTO tbl_Example
(ROLL,
SNAME,
SCLASS)
VALUES (1, 'JOYDEEP', 1)
INSERT INTO tbl_Example
(ROLL,
SNAME,
SCLASS)
VALUES (2, 'SUKAMAL', 1)
Step-3 [ See the Output ]
SELECT * FROM tbl_Example
-- Output
ROLL SNAME SCLASS ROWNUM
---- ----- ------ ------
1 JOYDEEP 1 1
2 SUKAMAL 1 2
Step-4 [ Make an Invalid entry that generate an error ]
-- Violation of Primary Key
INSERT INTO tbl_Example
(ROLL,
SNAME,
SCLASS)
VALUES (2, 'RAJESH', 1)
-- Output
Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK__tbl_Exam__44C28DB623BDA346'.
Cannot insert duplicate key in object 'dbo.tbl_Example'.
The statement has been terminated.
Step-5 [ Now Make the Correction entry after correction of data ]
-- Correction of Entry
INSERT INTO tbl_Example
(ROLL,
SNAME,
SCLASS)
VALUES (3, 'RAJESH', 1)
Step-6 [ Look the INDENTITY columns has Increased it gives 4 instead of 3 ]
SELECT * FROM tbl_Example
--Output
ROLL SNAME SCLASS ROWNUM
---- ----- ------ ------
1 JOYDEEP 1 1
2 SUKAMAL 1 2
3 RAJESH 1 4
Note that: It is the Normal behavior of the IDENTITY columns. It maintains the uniqueness. Here we are going to break it depends on our needs and it is not good for development. However I am just demonstrating it that we can do it if needed.
Here I am going to make a stored procedure to demonstrate it.
CREATE PROCEDURE my_proc
(
@param_roll INT,
@param_name VARCHAR(50),
@param_calss INT
)
AS
DECLARE @v_NOREC INT
BEGIN
BEGIN TRY
BEGIN TRANSACTION
INSERT INTO tbl_Example
(ROLL,
SNAME,
SCLASS)
VALUES (@param_roll,
@param_name,
@param_calss)
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
PRINT 'ERROR EXISTS'
SELECT @v_NOREC=COUNT(*) FROM tbl_Example
DBCC CHECKIDENT (tbl_Example, reseed, @v_NOREC)
END CATCH
END
Now we execute it and see the result step by step.
-- Execution-1
EXECUTE my_proc
@param_roll = 1,
@param_name = 'JOYDEEP',
@param_calss = 1
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
IDENTITY Columns Violation
We all know about the identity columns of SQL Server and how important it is. This article is related to it but I am representing this article to solving a common proble.
First take a quick look about the identity columns and how it works. Then we are going to discuss about the problem and how to solve it programmatically.
Definition of Identity Columns
An IDENTITY column contains a value for each row, generated automatically by Adaptive Server that uniquely identifies the row within the table.
Each table can have only one IDENTITY column. You can define an IDENTITY column when you create a table with a create table or select into statement, or add it later with an alter table statement. IDENTITY columns cannot be updated and do not allow nulls.
You define an IDENTITY column by specifying the keyword identity, instead of null or not null, in the create table statement. IDENTITY columns must have a datatype of numeric and scale of 0. Define the IDENTITY column with any desired precision, from 1 to 38 digits, in a new table:
CREATE TABLE table_name
(column_name INT NOT NULL IDENTITY(1,1))
The Problem comes for Identity
One of my friends told me that, he has a problem for IDENTITY columns. He told me that when he make the INSERT entry into table objects, he never check the uniqueness of the records for that the primary key violation error occurs. He just shows the error.
The problem is the table objects contain identity columns. The value of the identity columns increases each time, even the PK violation exists.
The problem is mentioned by T-SQL statements
Step-1 [ Create the Base Table with IDENTITY columns ]
-- Table defination
DROP TABLE tbl_Example
CREATE TABLE tbl_Example
(
ROLL INT NOT NULL PRIMARY KEY,
SNAME VARCHAR(50) NULL,
SCLASS INT NULL,
ROWNUM INT NOT NULL IDENTITY(1,1)
)
Step-2 [ Normal Insertions of Values ]
INSERT INTO tbl_Example
(ROLL,
SNAME,
SCLASS)
VALUES (1, 'JOYDEEP', 1)
INSERT INTO tbl_Example
(ROLL,
SNAME,
SCLASS)
VALUES (2, 'SUKAMAL', 1)
Step-3 [ See the Output ]
SELECT * FROM tbl_Example
-- Output
ROLL SNAME SCLASS ROWNUM
---- ----- ------ ------
1 JOYDEEP 1 1
2 SUKAMAL 1 2
Step-4 [ Make an Invalid entry that generate an error ]
-- Violation of Primary Key
INSERT INTO tbl_Example
(ROLL,
SNAME,
SCLASS)
VALUES (2, 'RAJESH', 1)
-- Output
Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK__tbl_Exam__44C28DB623BDA346'.
Cannot insert duplicate key in object 'dbo.tbl_Example'.
The statement has been terminated.
Step-5 [ Now Make the Correction entry after correction of data ]
-- Correction of Entry
INSERT INTO tbl_Example
(ROLL,
SNAME,
SCLASS)
VALUES (3, 'RAJESH', 1)
Step-6 [ Look the INDENTITY columns has Increased it gives 4 instead of 3 ]
SELECT * FROM tbl_Example
--Output
ROLL SNAME SCLASS ROWNUM
---- ----- ------ ------
1 JOYDEEP 1 1
2 SUKAMAL 1 2
3 RAJESH 1 4
Note that: It is the Normal behavior of the IDENTITY columns. It maintains the uniqueness. Here we are going to break it depends on our needs and it is not good for development. However I am just demonstrating it that we can do it if needed.
Here I am going to make a stored procedure to demonstrate it.
CREATE PROCEDURE my_proc
(
@param_roll INT,
@param_name VARCHAR(50),
@param_calss INT
)
AS
DECLARE @v_NOREC INT
BEGIN
BEGIN TRY
BEGIN TRANSACTION
INSERT INTO tbl_Example
(ROLL,
SNAME,
SCLASS)
VALUES (@param_roll,
@param_name,
@param_calss)
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
PRINT 'ERROR EXISTS'
SELECT @v_NOREC=COUNT(*) FROM tbl_Example
DBCC CHECKIDENT (tbl_Example, reseed, @v_NOREC)
END CATCH
END
Now we execute it and see the result step by step.
-- Execution-1
EXECUTE my_proc
@param_roll = 1,
@param_name = 'JOYDEEP',
@param_calss = 1
SELECT * FROM tbl_Example
ROLL SNAME SCLASS ROWNUM
1 JOYDEEP 1 1
-- Execution-2
EXECUTE my_proc
@param_roll = 2,
@param_name = 'SUKAMAL',
@param_calss = 1
SELECT * FROM tbl_Example
ROLL SNAME SCLASS ROWNUM
1 JOYDEEP 1 1
2 SUKAMAL 1 2
-- Execution-3 [ Primary Key Error ]
EXECUTE my_proc
@param_roll = 2,
@param_name = 'SANGRAM',
@param_calss = 1
0 row(s) affected)
ERROR EXISTS
Checking identity information: current identity value '3', current column value '2'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
SELECT * FROM tbl_Example
ROLL SNAME SCLASS ROWNUM
1 JOYDEEP 1 1
2 SUKAMAL 1 2
-- Execution-4
EXECUTE my_proc
@param_roll = 3,
@param_name = 'SANGRAM',
@param_calss = 1
SELECT * FROM tbl_Example
ROLL SNAME SCLASS ROWNUM
1 JOYDEEP 1 1
2 SUKAMAL 1 2
3 SANGRAM 1 3
How to insert rows into table with identity column
Imagine you have two billion rows on employee table, you have to archive this table. After archiving you are going to insert back only 110 million rows to employee table from employee_temp, There is a column emp_id is identity, so how can you insert data back from archived tables. Here you can find the tested solution , first you need to disable an indentity column to auto insert.
Step 1: Make sure to stop all process(application connection) against employee table
Step2 :Create a new table employee_copy from employee table
Find max(emp_id) from employee eg: 2000000001and RESEED this value to employee_copy
DBCC CHECKIDENT (employee_copy , RESEED, 2000000001)
Step3: Rename the orginal table using sp_rename 'employee' , 'employee_temp'
Step4: Rename the new table using sp_rename 'employee_copy' , 'employee'
Step5: Insert the rows from employee_temp table to employee table
SET IDENTITY_INSERT employee ON
GO
INSERT INTO employee (emp_id, emp_name, country_code)
SELECT emp_id, emp_name, country_code from employee_temp(nolock) where emp_id > 110000000
GO
SET IDENTITY_INSERT employee OFF
GO
