locked
If I use more then one column in Where then Non clustered Indexes are not used. RRS feed

  • Question

  • Question: Consider that I have table called tb_Person and it contains SSN, First_name, Middle_Name Last_name, DOB and so many other fields. When the Application was designed all the above given fields are in search text box. Now the user can search by one field or Multiple field, so I would like to know is do we need to create a Non clustered index individually ? or how to create a index on this case. please advise.

    I tried to create a Non clustered index individually but the Non clustered Indexes are not used in by SQL Server when I checked the execution plan, it's always try to use the clustered index scan (if I use more then one column in search).

    If I use only one column in search then SQL Server is using the clustered.
     

    Thanks

    Karthick
    Thursday, November 20, 2014 10:47 PM

Answers

  • The optimizer decides on a cost basis which also has to do with the number of records in that table. If you only have a handful in there it might decide that it's cheaper to scan the table (clustered index) than seeking through an index and perform a key lookup.

    To give you a more detailed answer, please provide table, query and index definitions as well as the number of records in that table.


    Bodo Michael Danitz - MCT, MCITP - free consultant - performance specialist - www.sql-server.de

    Thursday, November 20, 2014 11:03 PM
  • Hi,

    Order of the column in index really matters. The selection of index is entirely based on first column. An index is considered for use only if the first column listed in the index is used in the query. So if there is no match on first column and column used in JOIN, ORDER BY, or WHERE clauses of the query, index is completely ignored.


    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    Praveen Dsa | MCITP - Database Administrator 2008 | My Blog | My Page

    Friday, November 21, 2014 4:21 AM

All replies

  • The optimizer decides on a cost basis which also has to do with the number of records in that table. If you only have a handful in there it might decide that it's cheaper to scan the table (clustered index) than seeking through an index and perform a key lookup.

    To give you a more detailed answer, please provide table, query and index definitions as well as the number of records in that table.


    Bodo Michael Danitz - MCT, MCITP - free consultant - performance specialist - www.sql-server.de

    Thursday, November 20, 2014 11:03 PM
  • Hi,

    Order of the column in index really matters. The selection of index is entirely based on first column. An index is considered for use only if the first column listed in the index is used in the query. So if there is no match on first column and column used in JOIN, ORDER BY, or WHERE clauses of the query, index is completely ignored.


    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    Praveen Dsa | MCITP - Database Administrator 2008 | My Blog | My Page

    Friday, November 21, 2014 4:21 AM
  • Composite index works only for specific queries such as all columns in the WHERE clause. The second column on in the composite is ineffective to achieve individual index seek (i.e. second column only in WHERE clause).

    Covering index: http://www.sqlusa.com/bestpractices/coveringindex/




    Kalman Toth Database & OLAP Architect SQL Server 2014 Database Design
    New Book / Kindle: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2014




    Friday, November 21, 2014 9:58 AM
  • The composite nonclustered indexes which are created based on columns used in filter is a called a covering index as it "covers" the query. The optimizer will choose to use it only if it feels cost is less in traversing. Thats why when you use one or two columns alone in filter it may not hit the index.

    Please Mark This As Answer if it solved your issue
    Please Mark This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Friday, November 21, 2014 11:07 AM