Indexes Good Practices

1. Build index on columns of integer type

Integers take less space to store, which means the query will be faster. If the column you want to build an index for is not of type integer, consider creating a surrogate integer key (or simply a surrogate column of type integer) which maps one-to-one to the column you want to build the index for.

2. Keep index as narrow as possible

Narrower indexes take less space, require less time to process, which in turn means the query will run faster.

3. Column order is important

For indexes covering multiple columns, the order of the columns in the index is important. The best practice is to use the column with the lowest cardinality first, and the column with the highest cardinality last. Recall cardinality means the number of distinct values for that column. So, if "SELECT DISTINCT (COLUMN1) FROM TABLE_NAME;" returns 5, that means the cardinality for COLUMN1 is 5.

4. Make sure the column you are building an index for is declared NOT NULL

This can decrease the size of the index, which in turn will speed up the query.


Also look at

http://blog.sqlauthority.com/2009/01/18/sql-server-difference-between-index-scan-and-index-seek/

http://blog.sqlauthority.com/2009/08/24/sql-server-index-seek-vs-index-scan-diffefence-and-usage-a-simple-note/

http://blog.sqlauthority.com/2007/03/30/sql-server-index-seek-vs-index-scan-table-scan/