Which index will be used in this scenario?

Question

SQL Server 2014 Standard Edition

I need to find the number of flights that are to and from specific cities for certain months. E.g.

select count(*) 
from flights 
where flightTo_AirportCode = 'aaaa' 
and flightFrom_Airportcode = 'bbbb' 
and flightdate < '2016-04-01' 
and flightdate > '2016-02-28' ;

The table schema is below.

I am trying to estimate if index modelA or index modelB (below) is preferable (it takes many hours to build the index, and disk space allows only one to exist at a time, so I am trying to look before I leap).

From my experience, either index will do. Am I right?

  create index [modelA] on flights (flightTo_AirportCode, flightFrom_AirportCode, flightDate)

  create index [modelB] on flights (flightDate, flightTo_AirportCode, flightFrom_AirportCode)

(Or, better, is there a binary index or advanced mechanism I can use to approach this?)

CREATE TABLE [dbo].[flights](
    [flightId] [uniqueidentifier] NOT NULL,
    [accountId] [uniqueidentifier] NULL,
    [flightDate] [datetime] NULL,
    [flightTo_AirportCode] [nvarchar](30) NULL,
    [flightFrom_AirportCode] [nvarchar](30) NULL,
    -- ... 45 more fields
    CONSTRAINT [PK_flight] PRIMARY KEY CLUSTERED 
(
    [flightId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Answer 1

Index A is better for this query. When all the conditions in the WHERE are equality checks except one that is using a range condition or IN operator on a column, then that last column should be last in the index, after all the columns that have an equality check.

This allows the optimizer to use an index seek to the first row that matches the conditions and then traverse the index until it finds a row that doesn't match it. All the rows in between are a match, too.

So, the best index for this query would be either (to, from, date) (your model A) or (from, to, date).

The model B index has the date first so it is not the best, although it is still a covering index for the query. If this was used, the query plan would be almost the same. An index seek to find the first row that matches the range condition (date > '2016-02-28') and then traverse the index until it finds a row that doesn't match the date < '2016-04-01'. But all the rows in between do not necessarily match the 2 other conditions so they would have to be checked against these conditions and (possibly many of them) rejected.

So while the plans would be similar, the model A plan would have to only go through the part of the index that has all the needed rows and only them, while the model B plan would go through a (possibly much) larger part of the index.

---

• It would also be best to use a 100% safe format for dates (YYYYMMDD).

• And if you want the dates in March, you should use an inclusive-exclusive check:

  AND flightdate >= '20160301' AND flightdate < '20160401' 
  

  Guaranteed to work with date and datetime types. Your current query will include also any row that has '2016-02-28' but a time different to '00:00:00' (can you guarantee that there isn't any?) which I assume you don't want. The inclusive-exclusive method will also work in leap years (reminding that 2016 is a leap year so there was a February 29th date as well which your query will return).

Read also these blog posts by Aaron Bertrand:

• What do BETWEEN and the devil have in common?
• Bad habits to kick : mis-handling date / range queries