Articles → SQL SERVER → Indexes in SQL server

Indexes in SQL server

From the last few days I am planning to include some of the important technical concepts which are normally asked in all the technical interviews. I searched so many topics and I found ‘indexes in SQL server’ as priority one which I feel should be included first.

When I searched the topic I found so many good tutorials. So I am not going to say that this is different, better than others and so on but I could simply say that this topic is yet another tutorial on indexes in SQL server.

What do I need on my system?

First thing first. First of all you need to install Microsoft SQL server on your system because the concept will be elaborated by keeping Microsoft SQL server in mind. You can install any version of SQL server on your machine to practice the examples on indexes.

What are technical concepts I need to know?

Unless the tutorial doesn’t fall in ‘introduction to’ category then it is definitely required to know other concepts which helps you to understand the tutorial in a better way and indexes doesn’t come in the ‘introduction to’ category. So here is the list of concepts you should know

1. What are tables in SQL server?

2. What is a primary key?

What is a database index?

An index is a data structure which improves the speed of data retrieval operations on database table at the cost of additional writes and storage space to maintain the index data structure (http://en.wikipedia.org/wiki/Database_index)

If we split the above definition then there are 3 points which are important to notice

   1. Index is a data structure.

   2. Index helps in speeding up the data retrieval operations.

   3. Additional cost and storage space is required to maintain index

Point no 2 and 3 are pretty straight forward, but when I see point no 1 then a question comes to my mind – Why index is a data structure? And I got the answer when I searched the definition of data structure.

In computer science, a data structure is a particular way of organizing data in a computer so that it can be used efficiently (http://en.wikipedia.org/wiki/Data_structure)

So index is a way to organize the data so that it can be retrieved faster so that is the reason why index is a data structure.

Types of database index in SQL server

Microsoft SQL server supports many types of indexes like hash index, unique index etc., but in this tutorial we will discuss about only 2 types of indexes

1. Clustered index

2. Non-clustered index

How physical sorting and logical sorting are different

In case of physical sorting rows are physically stored in the disk in the same order as index. But in case of logical sorting a list of pointers (which is pointing to physical memory) is maintained. These pointers get sorted without affecting the physical memory.

Before we understand the difference between clustered and non-clustered index, it is important to understand B-tree.

B-Tree

B-Tree is a data structure which keeps data sorted and allows searches.

Click to Enlarge

As shown in diagram above there are different levels of nodes. At each level, a node should have a parent and child nodes.

If the node does not have any parent node, then that node is a root level (top level) node and if a node does not have any child node then those nodes are called leaf level (bottom level) nodes.

Each node contains data. Whenever data is inserted or deleted, B-Tree restructures itself and number of child nodes at a particular level increases or decreases.

Cluster index

A cluster index contains following features

   1. Only one clustered index per table

   2. Data is stored in leaf node of B-Tree. All other nodes contain index rows.

   3. Data is physically sorted

Syntax

-- Syntax for creating clustered index
CREATE CLUSTERED INDEX index_name ON table_name (column_name)

Non-Clustered index

A non-clustered index contains following features

   1. More than one non-clustered index can be implemented on a table

   2. Leaf node of B-Tree contains the address of data

   3. Data is logically sorted.

Syntax

-- Syntax for creating non clustered index
CREATE NONCLUSTERED INDEX index_name ON table_name (column_name)

I hope you like this tutorial. Please let me know your feedback.

Posted By -	Karan Gupta

Posted On -	Wednesday, January 28, 2015

Query/Feedback

Your Email Id

Subject

Query/Feedback	Characters remaining 250