OM Asphyxiate OM Asphyxiate - 1 month ago 5
SQL Question

Nonclustered Index on date columns used for BETWEEN

I have a table used for fiscal Periods that has a start and end date for when the period ends and begins. I use this table to determine when transactions have occurred for services and revenue has been collected in a query such as...

SELECT p.PeriodID, p.FiscalYear, SUM(t.Amount) AS Revenue
FROM Transactions t
JOIN Period p ON t.TransactionDate BETWEEN p.PeriodStart AND p.PeriodEnd


My question is, would it be beneficial to create a nonclustered index on the Period table for the date column? If so, should the index include both
PeriodStart
and
PeriodEnd
or just the
PeriodStart
column.

Thanks in advance!

Answer

I will suggest you to create index on both the columns,

CREATE NONCLUSTERED INDEX IX_Period
  ON Period (PeriodStart, PeriodEnd)
  include (PeriodID, FiscalYear); 

Include columns will help you to avoid the Lookup from Heap

creating index only on PeriodStart will not be useful