Post reply

Question on Indexes

  • March 10, 2014 at 7:52 am

    #304920

    Hi all,

    Got a couple of quick questions regarding indexes. Lets say I have a table with five columns - we'll call them columns A, B, C, D, and E.

    In this table, we have a primary key clustered index located on columns A sorted ASC, and an index on columns B, C, and D, all sorted ASC. We also have the following three indexes:

    Index 1 - Column B and D

    Index 2 - Column A DESC

    Index 3 - Column B, D, and E

    So here are my questions:

    Question 1 - Can I simply delete Index 1? I know that the order for the primary key is B, C, then D, so I know that the order of the records in the index is such that it is sorted first on B, then C, then D. Which means that it won't be able to directly use the index. But, the fact that the it starts with the same column, to me, means that it probably won't benefit hugely from the addition of another index, relative to the extra time needed for maintenance and insert/update/delete statements.

    Question 2 - Is there any real point in this index? It's the primary key, which means the whole table is ordered according to it. The fact that it is ordered DESC instead of ASC to me shouldn't change much, since it's still going to do effectively the same search.

    Question 3 - If I simply add column E to the index on B, C, and D, will it perform adequately? This is a similar question to Question 1. Again, I understand the way that the index works. If I understand correctly, it will do an Index Scan, since it will be able to locate all of the records associated with the column B part of the query, then scan the results to find the records associated with C and D, since it won't be ordered in that way. But, does adding an extra index really save that much time in this case?

  • March 10, 2014 at 8:31 am

    #1695553

    kramaswamy (3/10/2014)

    Question 1 - Can I simply delete Index 1?

    Yes, it's a left-based subset of Index 3 meaning that it's redundant with Index 3

    Question 2 - Is there any real point in this index?

    You mean Index 2? Probably not, there are some rare cases where a nonclustered index on the same column as the cluster is useful, you'll have to test and see whether removing it has a detrimental effect on a workload in a test environment.

    Question 3 - If I simply add column E to the index on B, C, and D, will it perform adequately?

    No way to answer that. Depends on the queries, the data volumes, the data distribution, etc.

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply