Reordering Identity primary key in sql server

Question

Yes i am very well aware the consequences. But i just want to reorder them. Start from 1 to end.

How do I go about reordering the keys using a single query ?

It is clustered primary key index

Reordering like

First record Id 1 
second record Id 2

The primary key is Int

Solution 1

1. Drop PK constraint
2. Drop Identity column
3. Re-create Identity Column
4. Re-Create PK

USE Test
go

if(object_id('IdentityTest') Is not null)
drop table IdentityTest

create table IdentityTest
(
Id int identity not null,
Name varchar(5),
constraint pk primary key (Id)
)

set identity_insert dbo.IdentityTest ON
insert into  dbo.IdentityTest (Id,Name) Values(23,'A'),(26,'B'),(34,'C'),(35,'D'),(40,'E')
set identity_insert dbo.IdentityTest OFF



select * from IdentityTest

------------------1. Drop PK constraint ------------------------------------ 
ALTER TABLE [dbo].[IdentityTest] DROP CONSTRAINT [pk]
GO
------------------2. Drop Identity column -----------------------------------
ALTER table dbo.IdentityTest
drop column Id
------------------3. Re-create Identity Column -----------------------------------
ALTER table dbo.IdentityTest
add Id int identity(1,1)
-------------------4. Re-Create PK-----------------------
ALTER TABLE [dbo].[IdentityTest] ADD  CONSTRAINT [pk] PRIMARY KEY CLUSTERED 
(
[Id] ASC
)

--------------------------------------------------------------
insert into  dbo.IdentityTest (Name) Values('F')
select * from IdentityTest

Solution 2

IDENTITY columns are not updatable irrespective of SET IDENTITY_INSERT options.

You could create a shadow table with the same definition as the original except for the IDENTITY property. Switch into that (this is a metadata only change with no movement of rows that just affects the table's definition) then update the rows and switch back though.

A full worked example going from a situation with gaps to no gaps is shown below (error handling and transactions are omitted below for brevity).

Demo Scenario

/*Your original table*/
CREATE TABLE YourTable
(
Id INT IDENTITY PRIMARY KEY,
OtherColumns CHAR(100) NULL
)

/*Some dummy data*/
INSERT INTO YourTable (OtherColumns) VALUES ('A'),('B'),('C')

/*Delete a row leaving a gap*/
DELETE FROM YourTable WHERE Id =2 

/*Verify there is a gap*/
SELECT *
FROM YourTable

Remove Gaps

/*Create table with same definition as original but no `IDENTITY`*/
CREATE TABLE ShadowTable
(
Id INT PRIMARY KEY,
OtherColumns CHAR(100)
)

/*1st metadata switch*/
ALTER TABLE YourTable SWITCH TO ShadowTable;

/*Do the update*/  
WITH CTE AS
(
SELECT *,
       ROW_NUMBER() OVER (ORDER BY Id) AS RN
FROM  ShadowTable     
)
UPDATE CTE SET Id = RN

/*Metadata switch back to restore IDENTITY property*/  
ALTER TABLE ShadowTable SWITCH TO YourTable;

/*Remove unneeded table*/
DROP TABLE ShadowTable;

/*No Gaps*/
SELECT *
FROM YourTable

Solution 3

CREATE TABLE Table1_Stg (bla bla bla)

INSERT INTO Table1_Stg (Column2, Column3,...) SELECT Column2, Column3,... FROM Table1 ORDER BY Id

Here the Id column is excluded from the SELECT column list.

Or, you can do:

SELECT * INTO Table1_Stg FROM Table1 ORDER BY Id

DROP Table1

sp_rename Table1_stg Table1

Please lookup the usage for sp_rename as I am doing this from memory.

Hope this helps.

EDIT: Please save a script with all your indexes and constraints if any on Table1.

EDIT2: Added second method of creating table and inserting into table.

Solution 4

UPDATE tbl SET id = (SELECT COUNT(*) FROM tbl t WHERE t.id <= tbl.id);

Solution 5

I don't think there is any way to do this in a single query. Your best bet is to copy the data to a new table, drop and recreate the original table (or delete the data and reseed the identity) and reinsert the data in the original order using the previous identity as the ordering (but not re-inserting it).

View more solutions

Comments

[jazzytomato]

is it the clustered index ?

[MonsterMMORPG]

it is clustered index. reordering start from 1 to the record count

[Nabheet]

Do you want to do this because you are missing values? is the new order going to be in the same order as you have now? If not, you still can but you need another column to define the order - like a timestamp or something else.

[MonsterMMORPG]

@NabheetSandhu Yes it is possible i suppose. Create another column and set them as row number ? and then update primary key to equal that ?

[Nabheet]

Yup, that is one perfectly valid way. In SQL Server, you "might" run into issues with "updating primary key". I don't remember if IDENTITY INSERT ON will help. However, you can create a staging table insert all rows into that, drop original table and rename staging table to original. This would also keep the original order of columns.

[MonsterMMORPG]

yes seems like best solution creating new temporary table , inserting real table into it and then deleting real table, then inserting temporary table into that and remove temporary table. are there any query that creates temporary table ?

[MonsterMMORPG]

my only left problem is " when a column list is used " I made a temporary table inserted data there deleted data at real server but when i come to insert data to real server it wants this

[Nabheet]

You need to specify column names in both the INSERT and SELECT clauses. I have updated my answer to demonstrate.

[MonsterMMORPG]

@NabheetSandhu yes noticed that. i wondered would it be possible without that. seems like this is not possible with single query. requires another program.

[Martin Smith]

@TomTom - No they are not. Test it. You will get the error message "Cannot update identity column 'Id'."

[TomTom]

They are. Read the documentation. "SET IDENTITY_INSERT ON". Read the correct answer here.

[Martin Smith]

@TomTom - That is for inserts not updates. Happy to accept your apology in advance.

[tvanfosson]

You won't get the exact same table this way, in particular any indexes, constraints, etc. will be missing. Better to save the script to create the table and re-insert the data.

[MonsterMMORPG]

yes i manually created new temp table. but it does not let me insert because it wants explicit column name An explicit value for the identity column in table 'mytable' can only be specified when a column list is used and IDENTITY_INSERT is ON.

[Gabriele Petrioli]

@TomTom, IDENTITY_INSERT , as the name implies, is for inserting only, not updating

[Nabheet]

Yes, I totally forgot about the other indexes and constraints.

[tvanfosson]

@MonsterMMORPG don't reinsert the id, let it be automatically determined as the identity column. This will work if you recreate the original table or if you explicitly reseed it to start at 1.

[Martin Smith]

Copying the entire table into a new one is certainly not required and could greatly add to the amount of logging etc if doing this on a large table. ALTER TABLE ... SWITCH doesn't touch the data pages.

[tvanfosson]

@MartinSmith yes, you need to be aware of that. you might want to change the logging model and/or increase the size if necessary. In general I think it's a bad idea. I would only do it in extreme circumstances and, if that's the case, I would choose the simplest, safest way to do it. In all honesty, I'd probably use the RedGate tools to do it for me rather than hand-coding it.

[Martin Smith]

@TomTom - Are you ever going to remove your unwarranted downvote then?

[Martin Smith]

@LittleBobbyTables - Thanks! I don't object to the downvote. But the refusal to retract it when the reason for giving it is wrong is pretty bizarre.

[MonsterMMORPG]

fantastic solution ty

[Seafish]

This is black magic but wow! it worked for me. Thanks! Only catch was I had to drop foreign keys, re-number the keys, update the foreign tables to point to the new ids, then re-create the foreign keys.