How to Create an Index in SQL Server

Having the right indexes on tables are critical to making your queries performant, especially when your data grows. Not having the needed indexes will typically result in high CPU usage in your database server, slow response times, and ultimately unhappy users.

To create indexes, use the CREATE INDEX command:

-- syntax
create index index_name
on table_name(column1, column2, .., columnN);

-- create index on one column
create index products_category
on products(category);

You can create an index on multiple columns. When used for columns that are frequently used together as filters, a multiple-column index performs better than multiple single-column indexes:

-- create index on multiple columns
create index products_category_brand
on products(category, brand_id);

A unique index prevents duplicate data for a defined key:

-- two rows can not have same category and brand_id key values
create unique index products_category_brand
on products(category, brand_id);

Create filter indexes to only index rows where a certain condition is met:

create index products_brand
on products(brand_id)
where brand_id = 1;

You can also have a unique filter index. For example, imagine if you want to prevent rows having duplicate category and brand_id key values for particular brand:

-- this will prevent duplicate categories within a brand that has id=1
create unique index products_brand_1_category
on products (category, brand_id)
where brand_id = 1;