Indexes
One of the most important routes to high performance in SQL server database is an index.
It is a database object which is used to speed up the querying process by providing quick access to rows in the database tables.
By using Indexes we can save time and can improve the performance of database queries and applications.
An Index contains keys built from one or more columns in the table mapped to the storage location of the specified data.
When we create an index on any column, SQL server internally maintains a separate table called index table, so that whenever a user tries to retrieve the data from the existing table, depending on the index Table SQL server goes directly to the table and retrieves the required data very quickly.
In the Table we can use a maximum of 250 Indexes. The Index Type refers to the way the index is stored internally by SQL server. So a Table can contain two types of indexes:
- Clustered Index
- Non-clustered Index
Clustered Indexes
The only time the data rows in a table are stored in sorted (ascending order only) order structure is when the table contains a clustered index.
When a table has a clustered index, then it is called a clustered table. If a table has no clustered index, its data rows are stored in an unordered structure.
A table can have only 1 clustered Index on it, which will be created when a primary key constraint is used in a Table.
Non-Clustered Indexes
Non-clustered Indexes will not have any arrangement order (unordered structure) of the data in the table. In a table, we can create 249 non clustered Indexes.
If we don't mention clustered indexes in a table then a default is stored as non-clustered Indexes.