SQL: which columns should have an index

Tweet

In SQL tables might have primary keys and indexes. The primary key identifies each record. The key must be unique, it can't be NULL and each table must have just one key.
Indexes are something different: they are used to speed up the queries on a specific table. That means we absolutely need indexes on large tables which we often query.

However there's something we need to take in account: indexed tables are slower to update because the index itself need to be updated as well. Thus, we need to carefully decide which are the columns we want to be indexed.

Deciding which columns should be indexed is not that difficult. Consider your frequently used queries and create index for those columns that are used in WHERE clauses. Obviously it's a general idea, because if we query a table to get 10% to 20% of the contained records, an index might be useless.
I suggest to use 1 or 2 indexes in a table: too many of them will definitely slow down your updates.

Generally indexes speed up and optimise queries against a table. However we should not over use them. In that regard, there is an interesting tool I found on the net which will analyse your database and determine possible improvements by suggesting indexes. It's call Automated Auto-Indexing and it's worth giving it a try.

We can get all the already created indexes in a database by querying the sys.indexes and the sys.index_columns system tables:

select * from sys.indexes

select * from sys.index_columns

In addition, I've already explained how to create indexes without SSMS in another post and if you're interested you can go there and see for yourself.