Range query against 2 columns | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Range query against 2 columns

Hi, I’m have a table with articles create table Articles
(
ArticleId int not null primary key,
ArticleVisible bit not null,
ArticleVisibleFrom datetime not null,
ArticleVisibleTo datetime not null
— … other
) I need to print only the articles that have ArticleVisibile=1 and getdate() between ArticleVisibleFrom and ArticleVisibleTo. And also this result has to be joined with several other tables. The table is used mainly for reading. I now use a covering index on (ArticleVisibleFrom, ArticleVisibleTo, ArticleVisible, ArticleId) to select ArticleIds then join this result with all other tables including the Articles table to get all columns I need. When not using the covering index the system has to perform an table or index scan. When I use the covering index it only uses index scan, but i have to join the data again with Articles table. My question is: is the use of the covering index justified? Or should I create a clustered index on the data Visibility columns?

IMHO the covering index is OK – The current (and desirable) index scan on this table will not be affected by the ‘joins’ with other tables. However,
  • This is only a small part of your final query; you should be worrying about the bigger picture i.e. Join types, how the other tables are being accessed. Performance is never considered partially.
  • With a read-intensive table, you might want to consider creating an indexed view (from the joined tables)
  • If you are joining on the ArticleID field, this covering index will not be used. In your case, the first key, Articlevisible is the one that is useful for WHERE and JOIN clauses

Nathan H.O.
There is no need to include ArticleID in the covered index if it is the clustered index key. Since the non-clustered index have pointer to the record on the datapage and use clustered index key to point to record, the clustered index key column is implicitly included in every non-clustered record. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

]]>