Adding identity to an existing column in SQL Server

We can not directly change a column to identity columns, say we have a primary key column or any normal column in our table and want to make it identity column then we cannot do it directly by altering the table but there are only two way to change column to identity column

  1. Create a new table with same structure and identity column and copy existing table records to this table and drop the existing table OR
  2. Add new column to table with identity and drop the existing column

These are the case if we can not open our database in SQL Server Management Studio if we can then

  1. Right click on table name
  2. Click on Design
  3. Click on the column which you want to make identity column
  4. Scroll Column Properties to "Identity Specification"
  5. Expand Identity Specification
  6. Change (Is Identity) to Yes
  7. Identity Increment and Identity Seed
  8. Save your table to complete.

Now let's back to our previous point that if we cannot see our table in design view then

1. Create a new table with same structure and identity column and copy existing table records to this table and drop the existing table

In this approach we can save our existing IDs as it is, suppose we have a table named Tags without identity then we can create a table Tags1 with same column but with Identity column like this

CREATE TABLE [Tags1]
(
    [TagID] [smallint] IDENTITY(1,1) NOT NULL,
    [TagName] [varchar](50) NOT NULL,
    [Count] [int] NULL,
) 

Now write the script to copy the data from Tags table to Tags1

SET IDENTITY_INSERT [dbo].[Tags1] ON
Go

INSERT  INTO [dbo].[Tags1] ( [TagID],  [TagName], [Count] )
SELECT  [TagID],  [TagName], [Count]
    FROM    [dbo].[Tags]
Go

SET IDENTITY_INSERT dbo].[Tags1] OFF

Now we get every thing in our new table Tags1 so time to drop our old table Tags and rename Tags1 to Tags

DROP TABLE dbo].[Tags]
Go

EXEC sp_rename 'Tags1', 'Tags'
Go

Now our Tags table have identity column as well as our previous records as well.

2. Add new column to table with identity and drop the existing column

ALTER TABLE Tags ADD TagID1 INT IDENTITY(1,1)
Go

ALTER TABLE Tags DROP COLUMN TagID
Go

EXEC sp_rename 'Tags.TagID1', 'TagID', 'COLUMN'
Go

This way of adding new column and dropping existing one and finally renaming new column name to old column name is easy but in this way we cannot save our existing data (IDs).

See how to remove the Identity property of a column in SQL Server 2008

Ali Adravi Having 13+ years of experience in Microsoft Technologies (C#, ASP.Net, MVC and SQL Server). Worked with Metaoption LLC, for more than 9 years and still with the same company. Always ready to learn new technologies and tricks.
  • sql server
  • identity
By Ali Adravi On 02 May, 13  Viewed: 606

Other blogs you may like

some useful sql tricks

There are many ways which can help us to save our time to write some repeated code or to do some repeated action, in this post we will see some very common code which we need very frequently in our development life. I am not saying that after this post you will save your half say work every day but... By Ali Adravi   On 31 Mar 2013  Viewed: 670

Get precision and decimal value in SQL Server by using PARSENAME

In SQL server there are decimal and money data type to store precision and scale both together or say decimal values. Suppose we want to get precision and scale separately then how to get it. There are many ways to do this but there is a function in SQL named PARSENAME which can be used to get... By Myghty   On 26 Mar 2013  Viewed: 1,764

Available wildcards in sql server with examples

To write a good performing procedure or function we should be aware about available wildcards in SQL Server. I found most developer depends on ‘%’ wildcard only while we have others as well. In this article we will discuss all the available wildcards in detail with examples. First of all let’s see... By Dev D   On 25 Feb 2013  Viewed: 453

Concatenate multiple columns in SQL Server with NULL value

When we need to concatenate two columns simply we can use `+` sign and that is correct, but what if any of them is null, Will it return what we want, NO, it will return null. So let's discuss how we should concatenate two or more columns without creating extra space or extra comma problem. Let's... By Ali Adravi   On 13 Feb 2013  Viewed: 8,398

Error cannot be caught by a TRY…CATCH Construct in SQL Server

TRY…CATCH constructs do not trap the following conditions: - Warnings or informational messages that have a severity of 10 or lower. - Errors that have a severity of 20 or higher those stop the SQL Server Database Engine task processing for the session. If an error occurs that has severity of... By Ali Adravi   On 28 Dec 2012  Viewed: 440