SQL Server Performance

which answer is right?

Discussion in 'SQL Server Clustering' started by tramp168, Sep 9, 2009.

  1. tramp168 New Member

    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
  2. FrankKalis Moderator

    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? [;)]
  3. tramp168 New Member

    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?
  4. Adriaan New Member

    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
  5. FrankKalis Moderator

    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.
  6. tramp168 New Member

    FrankKalis, thank for your help!

Share This Page