I have a big table without auto increment id column. I wanna add this column, but I want to add in order of a column defined as date.

What should be the most practical method for this? For example, can I transfer data to a temp table in the order I want, and then add an auto increment id column to the table according to that order, and then match it with the other table? (I don't think this is an efficient method.) Is there a special function or command block or method in MSSQL and how I can do this?

  • Does the table have clustered index? If yes, then on which columns? Can the date column contain two same date values?
    – Sranda
    Jan 26, 2022 at 12:11
  • Let’s start by quantifying “big”. How many rows are in this table, and how much space is it using? Can you also provide the current table definition? Jan 26, 2022 at 12:32
  • Define "efficient". Many use that term when they really mean "simple". What actual purpose does a new identity column serve that cannot be accomplished in some other way? Have you considered the problems of foreign keys that depend on this id column (presumably that name implies it is the primary key). Some dependencies require more advanced logic than currently suggested. Test carefully, make sure you have a backup before you apply any change to your actual table.
    – SMor
    Jan 26, 2022 at 15:45

3 Answers 3


Identity columns have some special rules around them so to accomplish your goal I believe you'd have to take a few roundabout steps:

Create a new table that matches your existing table's schema. You can shortcut this step by using a SELECT INTO statement like so: SELECT TOP 0 * INTO YourTableNew FROM YourTable;. Note this won't keep the same constraints or foreign key references, so if you have any of those you'd have to manually add them as well.

ALTER TABLE YourTableNew ADD SomeIdentityColumn INT IDENTITY(1,1); This adds the new identity column to your new table.

SET IDENTITY_INSERT YourTableNew ON; This allows you to insert values into your identity column.

Use a CTE and window function to generate your identity values and insert into your new table like so:

WITH _IdentityValues AS
        Column2, -- etc, all of your columns
        ROW_NUMBER() OVER (ORDER BY TheDateColumn) AS SomeIdentityColumn
    FROM YourTable

INSERT INTO YourTableNew (Column1, Column2, SomeIdentityColumn)
SELECT Column1, Column2, SomeIdentityColumn
FROM _IdentityValues;

Please note that if TheDateColumn has duplicate values in YourTable then there will be a non-deterministic assignment from the ROW_NUMBER() function for which dupe comes first.

Re-seed the identity specification to the correct new value: DBCC CHECKIDENT ('[TestTable]', RESEED, 9999); (Replace 9999 with whatever the max value currently is in YourTableNew plus 1.)

Turn identity insert off: SET IDENTITY_INSERT YourTableNew OFF;.

If you care about preserving the original table name then use sp_rename to switch the table names around like so:

EXEC sp_rename 'YourTable', 'YourTableOld';
EXEC sp_rename 'YourTableNew', 'YourTable';

Another approach is to use the IDENTITY function. More info here

Here is a test I have created for your reference. Note as J.D. mentioned, you still have to use sp_rename to switch the table and if start_date has duplicate values in the table then there will be a non-deterministic assignment of IDENTITY values

IF OBJECT_ID('dbo.testident', 'U') IS NOT NULL 
  DROP TABLE dbo.testident; 

DECLARE @table table (NAME varchar(100), START_DATE date)


SELECT * FROM @table

  INTO testident
  FROM @table
  FROM testident

SELECT COLUMNPROPERTY(OBJECT_ID('dbo.testident'),'id','IsIdentity') is_identity

Below are the results from the above query

Resultset 1 Original data

| Biju | 2022-01-31 |
| Rick | 2022-01-27 |
| Sam  | 2022-01-28 |

Resultset 2 New table with identity generated based on start_date

| id | name | START_DATE |
|  1 | Rick | 2022-01-27 |
|  2 | Sam  | 2022-01-28 |
|  3 | Biju | 2022-01-31 |

Resultset 3 To check the new column created is an IDENTITY column.

| is_identity |
|           1 |

I have a big table without auto increment id column. I wanna add this column, but I want to add in order of a column defined as date.

A word of warning, here.
The values generated in auto-increment columns guarantee to be unique, nothing more.
These values do not guarantee to be contiguous (running serially, one after the other) or even always-increasing.

You should not rely on these values in any way other than they are unique from one another.

This kind of begs the question "What are you're hoping to achieve with this activity?"

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.