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.
