May 3, 2012 at 1:20 pm
How to add a identity column(AutoID) at first position which increase by 1 with service_date column? That is, the small date (earlier date) should be smaller Auto_ID?
May 3, 2012 at 1:23 pm
First observation, I wouldn't try to put the identity column as the first column. Column position is actually meaningless to SQL Server.
Second, how big is your current table (number of rows)?
May 3, 2012 at 1:27 pm
Very big table, there are 4,806,509 records
May 3, 2012 at 1:52 pm
Then definitely do NOT try to make this the first column. Why do you care if this is the first column or the last column?
Here is a code example of adding this.
alter table MyExample
add MyNewColumn int identity
_______________________________________________________________
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/
May 3, 2012 at 1:56 pm
I knew this script but how to make the small date (earlier date) be smaller Auto_ID?
May 3, 2012 at 2:02 pm
I think you are asking if you can define the order or the identity? Why do you think this is important? An identity is NOT going to work like that. What happens when you insert a new row later with an earlier date? It isn't going to adjust all the other rows by one to accommodate the new row. Also an identity column will get gaps eventually. Anytime an insert fails it will still increment the identity.
_______________________________________________________________
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/
May 4, 2012 at 9:55 am
I am not convinced you want an actual IDENTITY column given your comments. Can you please provide the CREATE TABLE statement and an example of the data as you want it to appear with the new "identity" column as it relates to values in the "small date" column?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply