add an identity column to existing table as the primary key and change order

Question

I have a table that has over 7 million records in it. The table does not have a primary key. I would like to add a new identity column and set this as the primary key. I tried adding the column using SSMS, then I set it as the primary key. I called this new column Id.

This almost worked, however I wanted to change the default order of the table to be based another column, for instance a date time column in descending order. Is this possible? Perhaps I need to use a temp table and the ROW_NUMBER() function.

However, I am not very good at SQL. Can someone help?

I also need to have a rollback script so I can get back to the original table.

Answer 1

Here is one more idea:

Step1 - Create temporary clustered index on "date time column in descending order"

CREATE CLUSTERED INDEX ix_YourTableTEMP ON YourTable (DateTimeColumn DESC)

Step2 - Add identity column. Now the IDs should be in order of previously created index - although I don't think there is 100% guarantee on this.

ALTER TABLE YourTable ADD IdColumn INT IDENTITY(1,1)

Step3 - Drop temporary index

DROP INDEX  ix_YourTableTEMP ON YourTable 

Step4 - Create new clustered PK on new column

ALTER TABLE YourTable 
ADD CONSTRAINT PK_YourTable PRIMARY KEY CLUSTERED (IdColumn)

Answer 2

The Link Martin Smith has given is probably the best answer, but here is an alternative:

-- CREATE TABLE WITH SOME DATA IN
CREATE TABLE T (X INT);
INSERT T VALUES (1), (2), (3);

-- CREATE A CLONE OF THIS TABLE, ADDING AN IDENTITY COLUMN
-- USING ORDER BY TO AFFECT THE ORDER OF THE INSERT
SELECT  ID = IDENTITY(INT, 1, 1),
        T.*
INTO    T_Clone
FROM    T
ORDER BY X DESC;

-- DROP ORIGINAL TABLE
DROP TABLE T;

-- RENAME CLONE TABLE TO ORIGINAL TABLE NAME
EXECUTE SP_RENAME 'dbo.T_Clone', 'T', 'OBJECT';

-- SELECT FROM TABLE TO CHECK RESULTS
SELECT  *
FROM    T;

To rollback:

ALTER TABLE T DROP COLUMN ID;

EDIT

It has been pointed out that SELECT ID = IDENTITY(INT, 1, 1).. INTO.. FROM .. ORDER BY ... does not guarantee the order of the insert. So it would appear that the fail safe option is to create your clone table using CREATE TABLE syntax and adding the IDENTITY column:

CREATE TABLE T_Clone
(       ID      INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
        <your columns>
);
INSERT T_Clone (<your columns>)
SELECT  <your columns>
FROM    T
ORDER BY ...;

Then carry on with the Drop and rename as above. I can find no documentation to say this method is not reliable for ordering the insert, if it proves to still not be reliable you could use:

SET IDENTITY_INSERT T_Clone ON;
INSERT T_Clone (ID, <your columns>)
SELECT  ROW_NUMBER() OVER(ORDER BY ...),
        <your columns>
FROM    T;

SET IDENTITY_INSERT T_Clone OFF;

Then reseed T_CLone after the insert.