• Indexes are used to optimize SQL operations by preventing table scans
  • Indexes are a trade off between the time the cost to create / maintain them vs the benefit we will get from improved operations..

Types of Indexes

  • Clustered
  • Nonclustered
  • Unique
  • Filtered
  • XML
  • Full Text
  • Spatial
  • Columnstore
  • Index with included columns
  • Index on computed columns

Clustered Index

  • A clustered index determines the physical order of data in a table. For this reason, a table can have only one clustered index.
  • A primary key creates a clustered index if one does not exist on the table
  • Can contain multiple columns in clustered index, known as composite clustered index.
  • Faster than nonclustered index.
  • Does not require additional disk space

NonClustered Index

  • Indexes are stored separately from the table data
  • Slower than clustered index, has to refer back to table
  • Requires additional disk space

Unique Index

  • Enforces uniqueness of key values in the index
  • Primary key uses a unique index to enforce uniqueness
  • Uniquness is a property of an index. Both clustered and non-clustered indexes can be unique.
  • No major difference between a unique constraint and a unique index.
  • Use constraint when data integrity is the objective. This makes the objective clear.

Advantages

Disadvantages

Commands

sp_helpindex – execute sp_helpindex <tableName> – view index info for a table