Indexes in SQL

We very well know what the purpose of an Index is and how useful it is when it comes to searching.
Same rule applies to the data stored in Table.
Here we have two types of Indexes, i.e. Cluster Index and Non-Cluster Index. It is very important how you apply Clustered/Non-Clustered index. It completely depends on the requirement.
In general, indexes improve performance when they are created on fields used in table joins.
When dealing with relatively small tables, indexes do not improve performance.
Although indexes improve search performance, they slow the updates, and this might be something worth considering.

Clustered Index:
By applying cluster index, the data is physically sorted on the field on which index is applied. This also implies that we can have just one clustered index per table as this also acts as the ‘RowLocater’. Querying through such a data takes much less time compared non-indexed data.
Clustered indexes are ideal for queries that select by a range of values or where you need sorted results. This is because the data is already presorted in the index for you.
Examples of this include when you are using BETWEEN, <, >, GROUP BY, ORDER BY, JOIN and aggregates such as MAX, MIN, and COUNT in your queries.
Avoid putting a clustered index on columns that increment,
Thus, all primary key constraints of capture DB are modified to Clustered Indexes. Primary keys of table ItemsInBlock and RO_ItemsInBlock are not made Clustered, reason being these table have cluster index on other fields.

Example:
Consider a Table_A, which has 3 columns (ID, Name, Age) and ID being Primary key then,
CREATE UNIQUE CLUSTERED INDEX [PK_Table_A]
ON [dbo].[Table_A] ([ID])


Non-Clustered Index:
In this, the data in the table is not physically sorted but instead it maintains indexed data. The row locater is just a pointer refers the rows depending the on the non-cluster index. Non-cluster indexes should be used as per the needs. Practically a table can have upto 249 non-clustered indexes. But, as mentioned earlier having too many indexes will slow down the performance on INSERT, UPDATE and DELETE will affect the data in Index too.

Example:
Consider a query SELECT Name FROM Table_A WHERE ID = 3 on Table_A, which has 3 columns (ID, Name, Age) where ID being Primary key and we have a non-clustered index :
CREATE INDEX [IDX_Table_A_ByID_Name] ON [dbo].[ExternalInputStatus]([ID], [Name])
In the above example, the data will be fetched from the non clustered indexed object of table instead of actual table (clustered index) because, all the data it needs is in the index.


Covering Indexes:
Apart from the above mentioned Indexes there is one more “concept” know as Covering Indexes. Its not a specific type of index.
The idea is, when you create an index, an object is created in the database that has all of the data in the index. SQL Server can then use this index to speed up your queries. Now, consider a query that only uses a couple of columns in your table. If you create an index that has all of the data from those columns, then there is no reason for the query to use the table (because all of the data is found in the index).

Example:
Consider a query SELECT Name, Age FROM Table_A WHERE ID = 3 on Table_A, which has 3 columns (ID, Name, Age) where ID being Primary key and if we apply an Index like:
CREATE NONCLUSTERED INDEX [IDX_ID_Name_AGE] ON [dbo].[Table_A]([Id], [Name], [FavoriteColor].
In the above example, the data will be fetched from the non clustered indexed object of table instead of Actual table. But, as mentioned earlier having too many indexes will slow down the performance on INSERT, UPDATE and DELETE will affect the data in Index too.