SQL Server indexes are essential for optimizing database performance. They function similarly to a book index, providing quick lookup values that correspond to specific row locations within a table. SQL Server primarily uses two types of indexes: clustered and non-clustered.
Clustered indexes dictate the physical sort order of the data within the table. Because data can only be physically sorted in one way, a table can have only one clustered index. Non-clustered indexes, conversely, do not enforce a physical sort order, allowing for multiple non-clustered indexes per table (up to 249 in SQL Server). However, range searches using non-clustered indexes can be less efficient due to the lack of physical ordering.
The T-SQL command for creating an index is:
CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name ON { table | view } ( column [ ASC | DESC ] [ ,…n ] ) [ WITH < index_option > [ ,…n] ] [ ON filegroup ]
Key index options include:
* PAD_INDEX: Specifies the percentage of free space on non-leaf levels.
* FILLFACTOR: Determines the fill percentage of leaf pages.
* SORT_IN_TEMPDB: Uses tempdb for intermediate sort results, potentially improving index creation speed at the cost of increased disk space.
* STATISTICS_NORECOMPUTE: Prevents automatic updates to index statistics.
Indexes can also be managed through the Enterprise Manager using the Create Index Wizard, Database Diagrams, or Table Designer.
While indexes significantly improve query performance, they introduce overhead. They consume disk space and require updates whenever data is modified. When loading substantial data volumes, consider dropping indexes beforehand and recreating them afterward. Indexes can be removed using the Table Designer or the DROP INDEX command.
Indexes can become fragmented over time. To defragment an index, either drop and recreate it, or use the `dbcc indexdefrag` command.