November 21, 2003 at 7:21 am
In theory, you have a primary key there for a reason. If the question had given more information as to the purpose of the primary key, then a better decision to drop the index on the primary key could have been made. As it stands, the question is poorly worded.
November 21, 2003 at 8:09 am
I would agree. Index creation is more complex than just looking at one query. You would have to hope that the primary key was created for a real reason. The question also asked which of the following could be done to improve performance. If you're going to balance total system performance, you're better off trying a non-clustered index on LAST_ORDER_DT or the indexed view first before jumping to the removal of the primary key clustered index.
November 21, 2003 at 8:21 am
I agree..the answer is not necessarily right. Changing the Clusterd index does not necessarily mean changing the primary key. But how often is the Primary key selected from?
There is not enough information provided to decided what the best approach is. What about the selectivity of the query, and the selectivity a year from now? In all fairness changing the date field to a clustered index may be the best approach. Not my first choice, but it may be the best.
To answer the question you need to know a lot more than what was provided..Are there fifty columns on the table? Do we need a covered index? What other statements run against the table? When and how often are they executed? What is the size of the table? What is the selectivity of the date ranges? What other indexes might be needed?
etc..etc..etc.
"Keep Your Stick On the Ice" ..Red Green
November 21, 2003 at 8:40 am
Please keep threads together, post in http://www.sqlservercentral.com/forum/topic.asp?TOPIC_ID=18456&FORUM_ID=120&CAT_ID=10&Topic_Title=QOD+11%2F21&Forum_Title=Question+of+the+Day+%28QOD%29
November 21, 2003 at 9:32 am
The answer is wrong. START_DT isnt even in the query.
I want my money back ;=)
November 21, 2003 at 2:37 pm
quote:
In theory, you have a primary key there for a reason. If the question had given more information as to the purpose of the primary key, then a better decision to drop the index on the primary key could have been made. As it stands, the question is poorly worded.
November 21, 2003 at 2:39 pm
I agree with the remark below. If you modify indexes, other queries may now perform poorly. It is just a tip of the iceberg.
In theory, you have a primary key there for a reason. If the question had given more information as to the purpose of the primary key, then a better decision to drop the index on the primary key could have been made. As it stands, the question is poorly worded.
[/quote]
November 22, 2003 at 1:30 am
I also want to add following to above:
Change the primary key clustered index to non-clustered and create a new one on START_DT.
I interpreted this as create a new primary key (a new one). Primary key and index is not the same thing. You can only have one primary key - this option is not possible. I am not native in english, so that may be the problem here. But I believe the questions should not allow this type of "problems".
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply