How To Create an Index for Multiple Columns in SQL Server?

An index for multiple columns works similarly to a sorting process on multiple columns. If an index is defined for two columns, the index key is composed by values from those two columns.

A multi-column index will be used to speed up the search process based on the same columns in the same order as the index definition. For example, if you define an index called "combo_index" for "url" and "counts". "combo_index" will be used only when searching or sorting rows by "url" and "counts".

The tutorial exercise below shows you how to create an index for two columns:

-- Create an index for two columns
CREATE INDEX combo_index ON fyi_links (url, counts);
GO

-- View indexes
EXEC SP_HELP fyi_links;
GO
index_name         index_description               keys
-----------------  --------------------------      ----
fyi_links_created  nonclustered located on PRIMARY created
fyi_links_url      clustered located on PRIMARY    url
combo_index        nonclustered located on PRIMARY url, counts

-- Drop the index
DROP INDEX fyi_links.combo_index;
GO

⇒Understanding and Managing Indexes in SQL Server

⇒⇒SQL Server Database Tutorials