Home Forums SQL Server 2008 T-SQL (SS2K8) help me to create useful index (while using dynamic query) RE: help me to create useful index (while using dynamic query)

  • I had a similar problem. The table included a date, so I made the date a compulsory filter & added an index with date first, then all the other filter columns.

    If the user didn't filter by date, I added a filter of "Date between 01 Jan 1980 and today" (ie all dates) at the start of the filter expression.

    SQL Server will use an index if the first column is used in the query filter - so this index will always be used when Date is the first column in the filter expression. The degree of benefit will depend on the exact filter conditions, but it's as good as you'll get with 1 index (unless anyone knows better).

    The table I indexed was read-only - so index updating was not an issue. I therefore used fill factor = 100% to minimise page reads.

    If you think specific filters are going to be used, you could add extra indexes to cover them.

    So to recap - I would try a single index with the date first & the other search expressions following, in likely order of most common use.

    You would need to check the execution plan to ensure that any functions in the date filter don't disable use of the index. This will also show wether or not the index is being used for various different filters.