February 13, 2017 at 1:38 pm
I have a table with a CreatedDate column. I want to add an identity column and have the ints in order by the created date. I'd thought that by putting the clustered index on the CreatedDate column before adding the identity column, I'd get the identities in the order of the CreatedDate. This does not seem to always be the case.
Shouldn't the identity match the ordering of the clustered index?
Is there a way to do this other than creating a new table, turning identity_insert on, and moving the data to the new table?
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
February 13, 2017 at 1:49 pm
Not sure you need such a column. All you need in any given query is:
SELECT ROW_NUMBER() OVER(ORDER BY CreatedDate) AS NewIdentityColumn
FROM dbo.YOUR_TABLE
...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
February 13, 2017 at 3:00 pm
If you truly need an identity there is no way to ensure the "order" without using a order by. You correctly identified that this would require the steps you identified.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 13, 2017 at 10:01 pm
1) Don't forget to turn identity_insert back off when done! 🙂
2) You may want to consider secondary sorts for the case where the date you are ordering by is exactly the same. Otherwise you will get "random" sequential numbering within such sets (which could be perfectly fine, obviously).
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply