# Multiple-column index example

The following queries do provide start and stop conditions for a scan of the primary key index on the flight_id and segment_number columns in Flights:

    -- the where clause compares both columns with valid
    -- operators to constants
    SELECT *
    FROM Flights
    WHERE flight_id = 'AA1115'
    AND segment_number < 2
    -- the first column is in a valid comparison
    SELECT *
    FROM Flights
    WHERE flight_id < 'BB'
    -- LIKE is transformed into >= and <=, providing
    -- start and stop conditions
    SELECT *
    FROM Flights
    WHERE flight_id LIKE 'AA%'

The following queries do not:

    -- segment_number is in the index, but it's not the first column;
    -- there's no logical starting and stopping place
    SELECT *
    FROM Flights
    WHERE segment_number = 2
    -- Derby  would scan entire table; comparison of first column
    -- is not with a constant or column in another table
    -- and no covering index applies
    SELECT *
    FROM Flights
    WHERE orig_airport = dest_airport
    AND segment_number < 2

Parent topic: Covering indexes
Related reference
Single-column index examples