Different types of Indexes in SQL Server
Indexes are maintained in special tables that the database search engine can use for fast retrieval of data .
An index in a database is very similar to an index in the back of a book.
An index helps to speed up SELECT queries and WHERE clauses, but it slows down data input, with the UPDATE and the INSERT statements. Indexes can be created or dropped with no effect on the data.
A single-column index is created based on only one table column.
Create index indexname on tablename(columnname)
A unique index does not allow duplicate values to be inserted into the table
Create UNIQUE INDEX indexname on table(columnname)
A composite index is an index on one or more columns of a table.
Create INDEX indexname on tablename(column1,column2)
You have to decide whether you go for single column index or composite index based on the filter condition usage (i.e where clause)
Index are automaticaly created for primary key constraints.
Avoid indexes on some places:
Index should not be used on small tables
Tables that frequently has large batch updates and inserts
It should not be used on column that has more null values