Prev Next

Indexes

An Index in SQL Server is used to speed up the retrieval of data from a table by creating a data structure that improves query performance. Indexes are similar to the index of a book, which helps you quickly locate information.

Types of Indexes

  • Clustered Index: Sorts and stores the data rows in the table based on the key values. There can be only one clustered index per table because the data rows themselves are stored in order.
  • Non-Clustered Index: Contains a sorted list of pointers to the data rows in the table. A table can have multiple non-clustered indexes.
  • Unique Index: Ensures that the values in the index key columns are unique.
  • Composite Index: An index on two or more columns.

Creating an Index

Example: Creating a Clustered Index

CREATE CLUSTERED INDEX IX_FreedomFighters_Name
ON FreedomFighters (Name);

Explanation: This command creates a clustered index on the Name column of the FreedomFighters table. The table data is physically sorted based on the Name column.

Example: Creating a Non-Clustered Index

CREATE NONCLUSTERED INDEX IX_FreedomFighters_City
ON FreedomFighters (City);

Explanation: This command creates a non-clustered index on the City column of the FreedomFighters table. The index helps speed up queries that filter or sort by the City column.

Dropping an Index

Example: Dropping an Index

DROP INDEX IX_FreedomFighters_City ON FreedomFighters;

Explanation: This command drops the non-clustered index IX_FreedomFighters_City from the FreedomFighters table.

Best Practices for Using Indexes

  • Use indexes on columns that are frequently used in WHERE, ORDER BY, GROUP BY, or JOIN clauses.
  • Limit the number of indexes on a table to avoid performance issues during data modifications.
  • Use composite indexes when multiple columns are often queried together.

Monitoring Index Performance

Use system views like sys.dm_db_index_usage_stats to monitor index usage and identify indexes that are not being used.

Do's and Don'ts

Do's

  • Analyze query performance and use indexes to optimize frequently run queries.
  • Regularly maintain indexes by rebuilding or reorganizing them to avoid fragmentation.
  • Test index performance in a development environment before deploying to production.

Don'ts

  • Don't create too many indexes; it can slow down INSERT, UPDATE, and DELETE operations.
  • Don't use indexes on columns with low cardinality (few unique values), as it may not provide a performance benefit.
  • Don't forget to drop unused or duplicate indexes to improve database performance.
Prev Next