Articles → SQL SERVER → Indexes In SQL Server

Indexes In SQL Server

Over the past few days, I have been planning to cover key technical concepts frequently asked in interviews. After researching various topics, I prioritized Indexes in SQL Server as the most important and decided to start with it.

While researching this topic, I came across numerous excellent articles. I do not claim this one is unique or superior to the rest. Rather, it is simply another tutorial on indexes in SQL Server.

What Do I Need On My System?

To begin with, install SQL Server Management Studio (any version) on your system.

What Are The Technical Concepts I Need To Know?

Unless the article is part of the Introduction to series, it assumes familiarity with several foundational concepts. Indexes, for example, are not introductory topics. To fully grasp the content, you should be familiar with the following concepts: -

What are tables in SQL server?
What is a primary key?

What Is A Database Index?

A database index is a data structure that improves the speed of data retrieval operations on a 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 3 points are important to notice: -

An index is a data structure.
Indexes improve the speed of data retrieval operations.
Maintaining indexes incurs additional costs in terms of storage and processing overhead.

Points 2 and 3 are straightforward. However, point 1 raised my question: Why is an index considered a data structure? I found the answer after looking up the definition of a data structure.

In computer science, a data structure is a data organization and storage format that is usually chosen for efficient access to data. (http://en.wikipedia.org/wiki/Data_structure)

An index organizes data for faster retrieval and is considered a data structure.

Types Of Database Indexes In SQL Server

Microsoft SQL Server supports various indexes, such as hash and unique indexes. In this article, we will discuss only two types of indexes: -

Clustered index
Non-clustered index

How Physical Sorting And Logical Sorting Are Different

In physical sorting, rows are stored on the disk in the same order as the index. In contrast, logical sorting maintains a list of pointers referencing physical memory. These pointers are sorted without impacting the physical memory.

Before understanding the difference between clustered and non-clustered indexes, it is important to grasp the concept of a B-tree.

B-Tree

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

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

If a node does not have any parents, then it is a root-level (top-level) node. Conversely, if a node does not have any children, then those nodes are called leaf-level (bottom-level) nodes.

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

Cluster Index

A cluster index contains the following features: -

Only one clustered index is allowed per table.
Data is stored in the leaf node of the B-Tree. All other nodes contain index rows.
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 the following features: -

A table can have more than one non-clustered index implemented on it.
Leaf node of B-Tree contains the address of the data.
Data is logically sorted.

Syntax

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

Thank you for reading. Your feedback would be greatly appreciated.

Posted By -	Karan Gupta

Posted On -	Wednesday, January 28, 2015

Query/Feedback

Your Email Id		**

Subject		*

Query/Feedback	Characters remaining 250	**