TYPES OF INDEX
- 1 Clustered Indexes: Clustered indexes store row data in order. Only a single clustered index can be created on a database table. This works efficiently only if data is sorted in increasing and decreasing order or a limit is specified on the columns involved in the table. Such a sequential arrangement of data on disks reduces block reads.
- 2 Non-Clustered Indexes: In non-clustered indexes, data is arranged in a random way, but a logical ordering is internally specified by the index. Thus, the index order is not the same as the physical ordering of data. A "create index" statement creates a non-clustered index by default, which also creates a clustered index on the primary key. The index keys are sorted here with the leaf containing a pointer to the page. Non-clustered indexes work well with tables where data is modified frequently and the index is created on columns used in order by WHERE and JOIN statements
3 Dense Index
In dense index, there is an index record for every search key value in the database. This makes searching faster but requires more space to store index records itself. Index records contain search key value and a pointer to the actual record on the disk.
4 Sparse Index
In sparse index, index records are not created for every search key. An index record here contains a search key and an actual pointer to the data on the disk. To search a record, we first proceed by index record and reach at the actual location of the data. If the data we are looking for is not where we directly reach by following the index, then the system starts sequential search until the desired data is found.
Indexes and Constraints
Indexes are automatically created when PRIMARY KEY and UNIQUE constraints are defined on table columns. For example, when you create a table and identify a particular column to be the primary key, the Database Engine automatically creates a PRIMARY KEY constraint and index on that column.
How Indexes are used by the Query Optimizer
Well-designed indexes can reduce disk I/O operations and consume fewer system resources therefore improving query performance. Indexes can be helpful for a variety of queries that contain SELECT, UPDATE, DELETE, or MERGE statements. Consider the query
SELECT Title, HireDate FROM HumanResources.Employee WHERE EmployeeID = 250 in theAdventureWorks2012 database. When this query is executed, the query optimizer evaluates each available method for retrieving the data and selects the most efficient method. The method may be a table scan, or may be scanning one or more indexes if they exist.
When performing a table scan, the query optimizer reads all the rows in the table, and extracts the rows that meet the criteria of the query. A table scan generates many disk I/O operations and can be resource intensive. However, a table scan could be the most efficient method if, for example, the result set of the query is a high percentage of rows from the table.
When the query optimizer uses an index, it searches the index key columns, finds the storage location of the rows needed by the query and extracts the matching rows from that location. Generally, searching the index is much faster than searching the table because unlike a table, an index frequently contains very few columns per row and the rows are in sorted order.
The query optimizer typically selects the most efficient method when executing queries. However, if no indexes are available, the query optimizer must use a table scan. Your task is to design and create indexes that are best suited to your environment so that the query optimizer has a selection of efficient indexes from which to select. SQL Server provides the Database Engine Tuning Advisor to help with the analysis of your database environment and in the selection of appropriate indexes.
courtsey..tutorialspoint,https://docs.microsoft.com
