Importance of Indexing

Indexing

While you are fetching data from table, it is very important that your table should be indexed because indexing makes retrieval of data very easy. Without indexing, whole table is scanned to fetch the records as per needs which is time consuming and most of the times creates performance issues because it would require significantly more I/O and would be much slower, it doesn’t mean that you create more indexes on a single table. A table should have very few indexes. For that we need to know how many ways we can crate indexes in SQL Server.

There are two types of indexes: 1) Clustered Index 2) Non- Clustered Indexes which can contain two types of keys: i) Unique Key ii) Composite Key.

Keys

i)                    Unique:

Unique key means an index having only single column value. Most of the times we provide primary key to any table which means we are providing a unique key to that column.

ii)                  Composite Key:

Composite key means an index having multiple or combination of column values.  You can add 16 columns (maximum) in composite until you reach the size of 900 bytes per key.

Index Structure

                You can create indexes on any data type available in the structure but there are some exceptions here, come large object data types cannot be indexed like image, text and varchar (max). You can create indexes on XML but they are different from normal indexes and also if you want to use XML indexes then a table must have clustered index. While we are applying index on XML, a XML should be shredded which means data is parted from the XML tags, and organized into relational format.

Indexes are having hierarchical nature with a root node at the top and leaf nodes at the bottom. Between them it will be intermediate nodes as you can see in the Figure 1.

Figure1: B-tree structure of a SQL Server index

Types

     1)     Clustered Index

A clustered index is stored in the leaf nodes of the tree. An important characteristic of clustered index is that indexes remains sorted in either ascending or descending manner. As a result, there can be only one index per table or view and data in that table or view will also be sorted as index is sorted.

A table which is having a clustered index is called clustered table and a table which doesn’t have a clustered index is called ‘heap’.  You can have 8 indexes per page and 900 bytes per key in clustered indexes.

   

     2)     Non-Clustered Index

Non-Clustered indexes can contain only the values from the indexed columns and pointers to the actual data rows, rather than containing the data which means query engine will need additional step to locate the actual data.

Locater structure depends on whether it is pointing to clustered index or heap. If it is pointing to clustered index then it will directly go to the data. If it is referencing heap, pointer will point to actual data row.

Non-Clustered indexes are not sorted like clustered indexes. You can create as many non-clustered indexes as you can. You can have 4 indexes per page and 1700 bytes (increased for T- SQL) per key in non-clustered index.