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

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...