This article contribution from one of my favorite SQL Expert Imran Mohammed. He is one man who has lots of ideas and helps people from all over the world with passion using this community as platform. His constant zeal to learn more about SQL Server keeps him engaging him to do new SQL Server related activity every time.
1. Adding Identity Property to an existing column in a table.
How difficult is it to add an Identity property to an existing column in a table? Is there any T-SQL that can perform this action?
For most, the answer to the above two questions is an absolute NO! There is no straightforward T-SQL like ALTER TABLE… or MODIFY COLUMN to add an Identity Property to an existing column in a table.
However, there is an easy way to accomplish this action. It can be done through SSMS.
Are you finding my answer difficult to believe? Let me explain.
Let’s first see what SSMS does in backend when you add Identity property on an existing column in any table.
Now, let’s create an example table for better understanding.
This table is vacant, with no records, as you can see in the following screenshot.
Take a look at the design of this table in SSMS.
Now let us make eid, an Identity column.
This is very easy. All you have to do is just select Yes from the drop down list and you are done!
But before moving further let’s see what T-SQL SQL Server is using to make this change.
You will notice that T-SQL is used by SQL Server to make this change.
After you make the change for Identity property from No to Yes, on top in tools box, you will see Generate Change Script. This is the T-SQL Script that SQL Server will use to make this change.
Unfortunately, I cannot expand this dialogue box further to show you the complete script, so I have copied this script below.
/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_example1
(
eid INT NOT NULL IDENTITY (1, 1)
) ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.Tmp_example1 ON
GO
IF EXISTS(SELECT * FROM dbo.example1)
EXEC('INSERT INTO dbo.Tmp_example1 (eid)
SELECT eid FROM dbo.example1 WITH (HOLDLOCK TABLOCKX)')
GO
SET IDENTITY_INSERT dbo.Tmp_example1 OFF
GO
DROP TABLE dbo.example1
GO
EXECUTE sp_rename N'dbo.Tmp_example1', N'example1', 'OBJECT'
GO
COMMIT
Let us understand above code in simple words:
Begin Transaction
Step 1: Create similar table structure as example1, say Tmp_Example1 with Identity Column.
Step 2: Set IDENTITY_INSERT ON on new table just created.Step 3: Insert all values from example1 to Tmp_Example1
Step 4: Set IDENTITY_INSERT OFF on Tmp_Example..
Step 5: Drop example1 (What is going on… dropping our original table???)
Step 6: Rename Tmp_Example1 to Example1.
Commit Transaction
What if you have millions and millions of records in your table?
Imagine how much time it would take to copy an entire table?
Just to make a column Identity do you really need to go through all these and take so much pain?
Certainly not!
Same is the case when you want to remove Identity column property from a column.
2. Removing Identity Property from an existing column in a table.
/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_example1
(
eid INT NOT NULL
) ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.example1)
EXEC('INSERT INTO dbo.Tmp_example1 (eid)
SELECT eid FROM dbo.example1 WITH (HOLDLOCK TABLOCKX)')
GO
DROP TABLE dbo.example1
GO
EXECUTE sp_rename N'dbo.Tmp_example1', N'example1', 'OBJECT'
GO
COMMIT
Once again, I would like to congratulate Imran Mohammed for this wonderful article.
Reference : Pinal Dave (https://blog.sqlauthority.com)
107 Comments. Leave new
is there something called sequence in sql server which can be dropped and reset at will? i have 30+ tables and the foreign key relationships between them. i cant really do the methods suggested above as it is going to make my work 100 times more if i start dropping columns or tables and i will have to change the foreign keys also i guess. can i chaneg the order of columns in a table as i dont want to disturb the column sequence of my primary key column after using the above suggested methods.
Hi
thank you for this article.
I’m facing a problem trying to import data from a csv file to a table with identity column :
SET IDENTITY_INSERT mytable ON
BULK INSERT mytable
FROM ‘C:\file.csv’
WITH ( FIELDTERMINATOR = ‘;’, ROWTERMINATOR = ‘\n’ )
GO
SET IDENTITY_INSERT mytable OFF
I got this error
Explicit value must be specified for identity column in table ‘TABLE_NAME’ either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column
The csv file has already the values for ID column.
Any idea about this error ?
Thank you.
I know I am reviving an old comment, but just for future people who find this comment and see no answer. If IDENTITY_INSERT is ON, you need to specify the column names when doing an INSERT operation on the table.
Can i update the identity column value? I know that is not the concept of identity property this will break the concept of identity. but i confirm this.
No it is not possible to update an identity column in anyway
I am using Sqlserver 2008. I want to display it .I am very new in this system(Freshers).I need it urgently.So please help me.Thanks in Advance.
Table Structure :
CREATE TABLE dbo.DwlEntry
(
Id INT IDENTITY(1,1),
UserName VARCHAR(100),
ConfigItem VARCHAR(100),
Component VARCHAR(100),
TimeLogged DECIMAL(13,2)
)
INSERT INTO dbo.DwlEntry(UserName,ConfigItem,Component,TimeLogged) VALUES(‘Bubai’,’AFOTA’,’Documentation’,’3′)
INSERT INTO dbo.DwlEntry(UserName,ConfigItem,Component,TimeLogged) VALUES(‘Bubai’,’GENERAL’,’Troubleshooting’,’3′)
INSERT INTO dbo.DwlEntry(UserName,ConfigItem,Component,TimeLogged) VALUES(‘Bhanu’,’AFOTA’,’CodeChange’,’3′)
INSERT INTO dbo.DwlEntry(UserName,ConfigItem,Component,TimeLogged) VALUES(‘Bubai’,’GENERAL’,’ProjectTraining’,’1′)
INSERT INTO dbo.DwlEntry(UserName,ConfigItem,Component,TimeLogged) VALUES(‘Bhanu’,’GENERAL’,’Troubleshooting’,’5′)
INSERT INTO dbo.DwlEntry(UserName,ConfigItem,Component,TimeLogged) VALUES(‘Bhanu’,’AFOTA’,’CodeChange’,’1′)
INSERT INTO dbo.DwlEntry(UserName,ConfigItem,Component,TimeLogged) VALUES(‘Bubai’,’AFOTA’,’CodeChange’,’5′)
INSERT INTO dbo.DwlEntry(UserName,ConfigItem,Component,TimeLogged) VALUES(‘Bubai’,’GENERAL’,’ProjectTraining’,’4′)
INSERT INTO dbo.DwlEntry(UserName,ConfigItem,Component,TimeLogged) VALUES(‘Bhanu’,’AFOTA’,’Documentation’,’2′)
INSERT INTO dbo.DwlEntry(UserName,ConfigItem,Component,TimeLogged) VALUES(‘Bhanu’,’GENERAL’,’ProjectTraining’,’4′)
I want the Result in below way . Config item would be first row. and grouping come Horizontally like below Format
ConfigItem AFOTA AFOTA GENERAL GENERAL
UserName Documentation CodeChange ProjectTraining Troubleshooting Total
Bhanu 2 4 4 5 15
Bubai 3 5 5 3 16
Total 5 9 9 8 31
hi i have a table which contains 500 records
i do the fallowing process
ALTER TABLE [dbo].[table1] ADD [abc] INT IDENTITY(2,1)
SET IDENTITY_INSERT [dbo].[table1] ON
INSERT INTO [dbo].[table1]([abc])
SELECT sno FROM [dbo].[table1] with (HOLDLOCK TABLOCKX)
ALTER TABLE [dbo].[table1] DROP column [abc]
EXEC sp_rename
@objname = ‘[dbo].[table1].[abc]’,
@newname = ‘sno’,
@objtype = ‘COLUMN’
SET IDENTITY_INSERT [dbo].[table1] off
select * from [table1]
but here my problem was
Now the old values and new values are different
Hi pinal
i have a database with perfect structure with out data(empty tables) where among many tables have identity increment(1,1) ,when ever iam trying to insert records into that empty tables iam getting error as-
(SET IDENTITY_INSERT orders off; you cannot insert value into orders when identity is set off)
what i required is , with out using my insert query in between SET IDENTITY_INSERT orders ON and SET IDENTITY_INSERT orders off- the data has to be inserted into my table ,
Please help me out its very very Ugent.
reply me at
my emaild: [email removed for privacy]
ThankU
Another one that I am bringing up an old comment. But this is an easy one – you don’t include the IDENTITY column when doing your inserts. The whole reason for having the identity column is so you don’t have to think about it when you do an insert; just let SQL deal with it.
how to ID increment like P1,P2……
Not possible in SQL Server as of now.
Hi Pinal,
I have a question..
What is the difference between if I am declaring varchar(25) and varchar(max)?
Is there any performance benefit?
another one that I am bringing up an old comment.
This one is unrelated to the original article, but is a good question. There is a huge performance benefit to picking smaller values for a VARCHAR. When the table gets stored on disk, VARCHAR(25) can fit easily in a single page as it is a max of 25 bytes plus a tiny bit of overhead. VARCHAR(MAX) on the other hand could be a single character or 2 GB of data. So this may end up being on a single page OR it may end up being a LOB (large object) and stored in a different location causing more lookups to disk.
On top of that, if you do a SELECT on a VARCHAR(25) column, you are getting at MOST 25 bytes (plus overhead) back which is basically nothing on a 100 MBps network connection. On the other hand, if you have a VARCHAR(MAX) column that has a 2 GB value in it and you do a SELECT on that, you are pulling 2 GB of data across the network.
My advice is to always pick the proper data type for the problem. If you KNOW your data is ALWAYS going to be 25 characters long, then a CHAR(25) is going to offer better benefits than a VARCHAR(25) column.
Hi Pinal,
I have a question:
what is the difference between declaring varchar(25) and varchar(max)?
Is there any performance benefit?
Thanks in advance..
When you are going to use the variable in a where clause then you need to consider using same data type as column.
Dear Dave
I want to create table like this below.
create table productinfo
(
userid int(IDENTITY=TRUE),
username varchar(50),
password varchar(50),
location varchar(50),
islocked int,
attemptcount int
)
But whenever i execute the code it shows an error like(Incorrect syntax near the keyword ‘IDENTITY’.).
so please say me right way of code..
That is expected. Your syntax is wrong. If you look at the blog post, it gives a good example of how to use IDENTITY:
eid INT NOT NULL IDENTITY (1, 1)
the IDENTITY(1,1). With this example, it is starting at 1 and incrementing by 1.
Saying “IDENTITY=TRUE”, what is your start value or increment value? TRUE doesn’t make sense for this.
>>> Simplest idea would be create copy of existing column and drop identity column
alter table abc add column identitycolumnCOPYNEW bigint
update abc set identitycolumnCOPYNEW = identitycolumn
alter table abc drop column identitycolumn
sp_rename ‘abc.identitycolumncopynew’,’identitycolumn’,’column’
Thanks pinal….
Hi Sir Pinal,
I have a question, how to set identity specification to Yes, if table has already millions of data?
Is this possible? If yes, can you explain us how?
Thank you.
That is what this blog post showed. It shows how to add an IDENTITY column and how to remove one. The process is the same if the table is empty or has millions of rows of data.
The more data you have, the longer it will take.
Dear Pinal,
I have tried this and when I want to save the table “Saving changes are not permitted”.
I have legacy databases where the primary key is an uniqueidentifier (Guid generated with newid()). Those tables have also an ID field as integer that it is used internally as foreign key. The legacy software increments this ID itself. But I want to tranform those ID’s in autoincrement fields, to be able to create records from other applications. So I tried your method and I get this error. Please help.