Example
CREATE INDEX ix_cars_employee_id ON Cars (EmployeeId);
This will create an index for the column EmployeeId in the table Cars. This index will improve the speed of queries asking the server to sort or select by values in EmployeeId, such as the following:
SELECT * FROM Cars WHERE EmployeeId = 1
The index can contain more than 1 column, as in the following;
CREATE INDEX ix_cars_e_c_o_ids ON Cars (EmployeeId, CarId, OwnerId);
In this case, the index would be useful for queries asking to sort or select by all included columns, if the set of conditions is ordered in the same way. That means that when retrieving the data, it can find the rows to retrieve using the index, instead of looking through the full table.
For example, the following case would utilize the second index;
SELECT * FROM Cars WHERE EmployeeId = 1 Order by CarId DESC
If the order differs, however, the index does not have the same advantages, as in the following;
SELECT * FROM Cars WHERE OwnerId = 17 Order by CarId DESC
The index is not as helpful because the database must retrieve the entire index, across all values of EmployeeId and CarID, in order to find which items have OwnerId = 17.
(The index may still be used; it may be the case that the query optimizer finds that retrieving the index and filtering on the OwnerId, then retrieving only the needed rows is faster than retrieving the full table, especially if the table is large.)
