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]