Part of an index Usage

Posted on 2012-09-10
Last Modified: 2012-09-10
Does anyone know if SQL is smart enough to that it has an index already setup if you only use part of the keys in the index? For example, Lets say field1,field2,field3 are setup as an index on a table, and you want to do a read on just field2 and field3. Does it do a serial read of does it use the index that is setup with field1,field2 and field3? Thank you all for all your help in advance.
Question by:simpkinst
    LVL 59

    Assisted Solution

    by:Kevin Cross
    In order for the SQL query optimizer to select the multi-column index, ALL three columns would need to appear in the condition--typically in the same order as in the index definition. If you are missing one, the QO is likely to perform a full scan unless you have another index that satisfy the criteria (e.g., an index on field2, field3, or both).
    LVL 2

    Expert Comment

    Just check it at: Display estimated execution plan in MS SQL Server Management Studio.
    LVL 75

    Accepted Solution

    the index on (field1+field2+field3) Will be used only for these searches
    - field1 alone
    -field1 + field2
    - field1+field2 + field3

    may use it partially for  (field1 + field3) using a bookmark  provided no other index which covers these two

    anything emse it uses a scan

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Join & Write a Comment

    by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now