How to control order of assignment for new identity column in SQL Server?

Question

I have a table with CreateDate datetime field default(getdate()) that does not have any identity column.

I would like to add identity(1,1) field that would reflect same order of existing records as CreateDate field (order by would give same results). How can I do that ?

I guess if I create clustered key on CreateDate field and then add identity column it will work (not sure if it's guaranteed), is there a good/better way ?

I am interested in SQL Server 2005, but I guess the answer will be the same for SQL Server 2008, SQL Server 2000.

Answer 1

As you suspect, it will add them according to the clustered index. Otherwise, you'll have to do it in code from somewhere.

Answer 2

IDENTITY values are orthogonal to the physical storage order in general. In particular an identity will not always match a datetime clustered key order because of the datetime resolution of 3ms that allows multiple rows with the same datetime. Also if the original time is bound to the client machine (ie. mid tier, asp layer, user machine etc) then the time drift between machines will also ensure a difference between insert order (what IDENTITY would give) and storage order.

If you need a row order integer, use ROW_NUMBER() in the projection list. If you need an IDENTITY primary key for ORM purposes, use an IDENTITY column and index it as a non-clustered index.

Never confuse physical storage requirement (clustered key) with logical modeling requirements (primary key).

Answer 3

Following on from Remus' theoretical answer... you need to generate a list first with your ideal ordering

SELECT
    ID, CreateDate
INTO
    MyNewTable
FROM
    (
    SELECT
        CreateDate,
        ROW_NUMBER() OVER (ORDER BY CreateDate ASC) AS ID
    FROM
        MyTable
    ) foo

Then, the best solution is to use SSMS to add the IDENTITY property to MyNewTable. SSMS will generate a script that includes SET IDENTITY INSERT to preserve the order

Note: IDENTITY columns are just numbers that have no implicit meaning and nothing should be inferred by their alignment with the CreateDate after this exercise...