How to control order of assignment for new identity column in SQL Server?
- by alpav
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.