April 3, 2012 at 12:40 pm
Syntax help on how to change a column from asc to desc?
I need to do this across all our tables our column is a date field
April 3, 2012 at 12:45 pm
Can you explain in more detail what you're trying to do? Columns by themselves don't have an ascending or descending property.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 3, 2012 at 12:55 pm
Most of the tables have a composite PK, and usually the last column in the key is EffectiveDate. All tables have an EffectiveDate column but we don’t really use it in all tables in our queries. We want to change the order for that column only, EffectiveDate from ASC to DESC. We want to start with those tables that have UDFs using the EffectiveDate - meaning, those that retrieve the “top of the stack” grouping the data on some criteria and then taking the Max(EffectiveDate). The order for the other columns part of the PK should remain the same.
Thanks
April 3, 2012 at 1:00 pm
D-SQL (4/3/2012)
Most of the tables have a composite PK, and usually the last column in the key is EffectiveDate. All tables have an EffectiveDate column but we don’t really use it in all tables in our queries. We want to change the order for that column only, EffectiveDate from ASC to DESC. We want to start with those tables that have UDFs using the EffectiveDate - meaning, those that retrieve the “top of the stack” grouping the data on some criteria and then taking the Max(EffectiveDate). The order for the other columns part of the PK should remain the same.Thanks
HUH???
As Gail said, columns do not have an order, tables don't have an order, queries CAN have an order but IF AND ONLY IF an order by clause is specified in the query.
Can you explain clearly what you are trying to do? If so, maybe somebody can help point you in the right direction.
_______________________________________________________________
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/
April 3, 2012 at 1:26 pm
D-SQL (4/3/2012)
Most of the tables have a composite PK, and usually the last column in the key is EffectiveDate. All tables have an EffectiveDate column but we don’t really use it in all tables in our queries. We want to change the order for that column only, EffectiveDate from ASC to DESC.
I assume you mean that you want to change the index so that the column is DESC, not ASC.
Why? There are few reasons to do that.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 3, 2012 at 1:39 pm
Yes that is correct one column in the index. This should have been set these way before. We are changing a few thinks to use RANK and it's going to help our performance by 3 times.
Thanks
April 3, 2012 at 1:41 pm
D-SQL (4/3/2012)
Yes that is correct one column in the index. This should have been set these way before. We are changing a few thinks to use RANK and it's going to help our performance by 3 times.Thanks
Have you taken the time to lookup CREATE INDEX in Books Online? I think you will find this information there.
April 3, 2012 at 2:05 pm
D-SQL (4/3/2012)
We are changing a few thinks to use RANK and it's going to help our performance by 3 times.
Are you sure about that? Have you tested and seen a 3-fold improvement in performance?
Given that it's the last column of the primary key, I doubt it will have that kind of effect unless there are huge numbers of rows that only differ by the effective date and you have a very specific query form)
btw, the way you do it is:
ALTER TABLE ... DROP CONSTRAINT (to drop the primary key constraint)
ALTER TABLE ... ADD CONSTRAINT (recreate the primary key as you want it)
Won't be a quick process on a large table. Needs to be done when no one is accessing the system.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 3, 2012 at 2:14 pm
Yes this has een tested and I saw it also. My boss and development team has been working on this and then tasked me with the change.
Thanks for your help
April 3, 2012 at 2:26 pm
I'm not talking about a performance improvement from the use of the RANK. Have you seen and verified a performance improvement solely from changing the column in the index from ASC to DESC?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 3, 2012 at 3:01 pm
Yes we tested both ways. Having the index in asc slows our system down. The developer stated because all the rest of our things are created to use desc order.Some how this index were missed.
April 9, 2012 at 6:00 pm
Hello Im still struggling with this. Any suggestions on he syntax. THis would be one column in the index to desc leaving the rest to asc.
We have to do this on multiple databases and this is something new to me my SR DBA is out on LOA. Thanks
April 9, 2012 at 6:55 pm
D-SQL (4/9/2012)
Hello Im still struggling with this. Any suggestions on he syntax. THis would be one column in the index to desc leaving the rest to asc.We have to do this on multiple databases and this is something new to me my SR DBA is out on LOA. Thanks
Check Books Online for the CREATE INDEX or ALTER INDEX syntax.
April 10, 2012 at 2:03 am
GilaMonster (4/3/2012)
btw, the way you do it is:ALTER TABLE ... DROP CONSTRAINT (to drop the primary key constraint)
ALTER TABLE ... ADD CONSTRAINT (recreate the primary key as you want it)
Won't be a quick process on a large table. Needs to be done when no one is accessing the system.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply