which answer is right? | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

which answer is right?

I have a table containing production information that your users query frequently, They specifically use this
query most often (that is only use name to search in the where condition):
SELECT Name,Description,Vendor,Instock,Price FROM Products where Name=’name’
Have a nonclustered index on this table on the Name column,but your users are complaining the query is still too
slow,what can you do to speed it up?
A、modify the index to include the Description,vendor,Instock, and price columns as nonkey columns.
B、Create a new nonclustered index on the Description,vendor,Instock, and price as nonkey columns.
C、Create a new clustered index on the Description,vendor,Instock, and price as nonkey columns.
D、You can’t do anything to speed up this query.
Database is MS SQL SERVER.
Above four choices, which answer is right?please tell the reason.Thanks

Welcome to the forum!
This looks pretty much like a question directly copy and pasted from some test. Don’t you think it is better for you to do some research on your own, tell us what you would answered and why and then we can discuss? [;)]

thanks.
It’s my friend asking for me, but I do not know the answer, I think the choice D is the answer and I’m not sure of it.
Could you help me on it?

If you’re on SQL 7 or 2000, then please note that for answers A-C the technical implementation of your options are different from SQL 2005 onwards. But indeed the answers seem to be appropriate for 7.0 or 2000
Since this question mentiones nonkey columns, I suspect this is for SQL Server 2005 and above.
I would pick this one. That way the index covers the query but not at the cost of widening it more than necessary.
Doesn’t offer any advantage provided the [name] column is not the clustered index key
Can’t be done with a clustered index. You can’t have nonkey columns (with the INCLUDE clause) on a clustered index.
True only if you are on SQL Server 2000. Wrong, if 2005 and above.

Have a nonclustered index on this table on the Name column,but your users are complaining the query is still too
slow,what can you do to speed it up?
A、modify the index to include the Description,vendor,Instock, and price columns as nonkey columns.
B、Create a new nonclustered index on the Description,vendor,Instock, and price as nonkey columns.
C、Create a new clustered index on the Description,vendor,Instock, and price as nonkey columns.
D、You can’t do anything to speed up this query.
FrankKalis, thank for your help!

]]>

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |