Introduction
In this blog, I will explain the indexes in SQL Server. Indexes are special lookup tables that the database search engine can use to speed up data retrieval. Simply put, an index is a pointer to data in a table. The index is a physical structure containing pointers to the data. Indices are created in an existing table to locate rows more quickly and efficiently. It is possible to create an index on one or more columns of a table, and each index is given a name. The users cannot see the indexes, they are just used to speed up queries. Effective indexes are one of the best ways to improve performance in a database application. The following facets of indices should be considered:
1. Indexes should not be used on small tables.
2. Tables that have frequent, large batch updates or insert operations.
3. Indexes should not be used on columns that contain a high number of NULL values.
4. Columns that are frequently manipulated should not be indexed.
Syntax:
- CREATE INDEX INDEX_NAME ON TB_NAME (Column1,Column2);
Types:
1. Clustered Indexes (Implicit Indexes)
2. Nonclustered Indexes (Explicit Indexes)
Clustered Index:
The clustered index is for physical data stored in a table and the primary key constraint automatically creates a clustered index on that particular column. There can be only one clustered index per table.
NonClustered Index:
The non-clustered index doesn’t sort the physical data inside the table and stored table data at one place to another place. More than one non-clustered index is permitted per table.