locked
Variable Columns Index Design RRS feed

  • Question

  • Hello,

      This is a hypothetical scenario but I want learn on how you approach this index design.

    1. We have an application where users can select what columns they  want to see and also, search  on any column . the searched field may or may not be in the list 'select' columns.

    let's assume a scenario,  the available options for select columns are:

    FirstName,LastName, AddressLine1,Addressline2 , City,State,Country,Zip

    User1 searches on 'City' and retrives LastName,AddressLine1,AddressLine2,Zip

    User2 : chooses FirstName,City,State,Country and Zip and searches on City and LastName.

    So, my question is when sargable  columns and select columns vary, how to approach index design. To me it looks better if we design individual NC for each column but I hope to here from guys and learn how to do this. also, if there are any good blog references, please let me know. Thanks, 


    Hope it Helps!!

    Tuesday, March 5, 2013 3:32 AM

Answers

  • >>>. We have an application where users can select what columns they  want to see and also, search  on any column . the searched field may or may not >>be in the list 'select' columns.

    http://www.sommarskog.se/dyn-search-2008.html

    In above scenario you may have a NCI on City and LastName (or even two NCI on City and LastName) and INCLUDE (AddressLine1,Addressline2 , City,State,Country,Zip) --- all columns needed in SELECT


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Tuesday, March 5, 2013 6:11 AM
  • Well, you would have to test  the query having all possible variation in WHERE clause.  Some suggestions 

    Try creating a index with most selective column as first column in the composite index like (EmployeeNo,City and .....) 

    Add all columns you return to the client as INCLUDEd  to cover the SELECT statement...

     

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Wednesday, March 6, 2013 6:54 AM

All replies

  • >>>. We have an application where users can select what columns they  want to see and also, search  on any column . the searched field may or may not >>be in the list 'select' columns.

    http://www.sommarskog.se/dyn-search-2008.html

    In above scenario you may have a NCI on City and LastName (or even two NCI on City and LastName) and INCLUDE (AddressLine1,Addressline2 , City,State,Country,Zip) --- all columns needed in SELECT


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Tuesday, March 5, 2013 6:11 AM
  • Generally, individual column indexes are the best choice for dynamic search.

    Dynamic SQL: http://www.sqlusa.com/bestpractices/dynamicsql/

    For a business critical query, consider covering index:

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


    Kalman Toth Database & OLAP Architect
    Paperback / Kindle: Windows Azure SQL Database Programming & Design


    Tuesday, March 5, 2013 7:44 AM
  • >>>. We have an application where users can select what columns they  want to see and also, search  on any column . the searched field may or may not >>be in the list 'select' columns.

    http://www.sommarskog.se/dyn-search-2008.html

    In above scenario you may have a NCI on City and LastName (or even two NCI on City and LastName) and INCLUDE (AddressLine1,Addressline2 , City,State,Country,Zip) --- all columns needed in SELECT


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Thanks for the link Uri..I will go through it and see ..

    I am not too sure about the index suggestion you made, I can certainly understand doing the same, if this was a static condition.. If I start doing index every possible way, I may end up with something 10 folds bigger....my search criteria could be different from your's..

    I do not have this application now but certainly has worked with it in the past..this application was like a portal where users can configure there stuff..  Let me know if you have any additional recommendations... Thank you sir..


    Hope it Helps!!


    • Edited by Stan210 Tuesday, March 5, 2013 4:10 PM
    Tuesday, March 5, 2013 4:09 PM
  • Well, you would have to test  the query having all possible variation in WHERE clause.  Some suggestions 

    Try creating a index with most selective column as first column in the composite index like (EmployeeNo,City and .....) 

    Add all columns you return to the client as INCLUDEd  to cover the SELECT statement...

     

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Wednesday, March 6, 2013 6:54 AM