Finding My Identity

My son, the toilet paper mummy

Microsoft SQL Server has a wonderful property called Identity.  This is similar to the auto-number field in Microsoft Access in that it creates a unique ID for each row inserted into the database.

I had a user come to me and said "We need the table to generate the ID automatically, but it's not doing it.  Can you fix it?"

Sure, I'm a DBA. I can fix anything.  Or at least, I will create an approximation of fixing close enough that you don't know the difference.

The data had been imported awhile ago from an Access database. So the existing data had id's assigned.  Typically, Identity columns start at one and increment by one whenever a row is added.

Luckily, SQL identity columns allow you to set the seed (aka the starting number) and the increment value (so you can change the increment interval).  I could find the seed by doing a Max(ID) +1.  You can insert into an Identity column, but you can't update an identity column.

I thought about creating a new Identity column and copying the ID  values into it. 

Problem one, by default, one cannot insert into a column that is designed to be filled automatically.  It's just not done.  I learned that SQL allows us to ignore that constraint.  by using SET IDENTITY_INSERT ON.  

When I tried to update the values in the new Identity column, I learned that YOU CAN"T UPDATE and Identity column. 

My solution was to create a script that created a new table with our identity value and insert from one into the other.

 The process is comprised of four steps

1. Script the existing table to your query window
2. Find the seed by finding the maximum ID and adding one. (you can script this as well)
3. Modify the ID field to be an Identity with your seed
4. Do an INSERT INTO SELECT command 
5. Drop the old table
6. Rename the new table
7. Recieve praise for doing the impossible.

USE [yourDatabase GO

SET ANSI_NULLS ONGO
SET QUOTED_IDENTIFIER ONGO

                             -- This creates the table

CREATE TABLE [dbo].[AutoNumberTemp](

[ID] [numeric](18, 0) IDENTITY(1807,1) NOT NULL, [userid] [nvarchar](50) NULL, ) ON [PRIMARY]go

                             -- This allows us to insert into our Identity column

SET IDENTITY_INSERT [dbo].[AutoNumberTemp] ON
GO

                             -- This copies data from our original table into our new table

INSERT INTO [dbo].[AutoNumberTemp] ( [ID]      ,[userid]
  FROM [dbo].[AutoNumber] WITH (TABLOCKX)

-- Turn off Identity_Insert, because it's not a good idea to leave on 
SET IDENTITY_INSERT [dbo].[AutoNumberTemp] OFF

                             /* Braver DBA's may omit the backup and replace it with a drop table */
exec sp_rename '[dbo].[AutoNumber]','[AutoNumber_old]';
/* Now we rename the new table with the old tables names */

exec sp_rename 'AutoNumberTemp','AutoNumber';go