- 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
- Full Text
- Index with included columns
- Index on computed columns
- 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
- Indexes are stored separately from the table data
- Slower than clustered index, has to refer back to table
- Requires additional disk space
- 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.
sp_helpindex – execute sp_helpindex <tableName> – view index info for a table