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 that 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 a question for me: 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; therefore, it is considered a data structure.

Types Of Database Indexes In SQL Server

Microsoft SQL Server supports various types of indexes, such as hash indexes 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 that reference physical memory. These pointers are sorted without impacting 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.

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 increase or decreases.

Cluster Index

A cluster index contains following features: -

Only one clustered index per table
Data is stored in leaf node of 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 following features: -

More than one non-clustered index can be implemented on a table
Leaf node of B-Tree contains the address of data
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	**