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]