Indexes | SQL
Tags: sql, datapages, index, indexes
Published: 2022-02-07 20:51:54

---
title: Indexes | SQL
published: true
date: 2022-01-12 17:23:34 UTC
tags: SQL,DataPages,Index,Indexes
canonical_url: 
---

Let’s talk a little bit about indexes, their relation with **[data pages](https://docs.microsoft.com/en-us/sql/relational-databases/pages-and-extents-architecture-guide?view=sql-server-ver15)**, and their importance in querying data from databases.

Database indexes are used to provide faster access to relational data. An index is a separated physical data structure that enables faster access to one or multiple rows to an entity or table.

Analogous, we can think of a book index or the yellow pages (phone numbers) as examples of indexes.

**So, what it means to have or not have an index?**

Well, let’s imagine that we have a table, with a primary key, and no index on it. To find the row(s) we want on the data pages, we’ll blindly start our search with no clue of where to find them. Thus, to ensure that we end on the aimed row(s) successfully, we must start reading sequentially from beginning to end until we find what we want. The higher is the number of rows on the table the higher will be the time consumed on this task. Image what would be to try to find a row in the middle of 1000000.

Here is where indexes are useful… Since they are built in a separated physical data structure, more exactly on a **[B+-tree](https://en.wikipedia.org/wiki/B%2B_tree)** structure, will build a sort of “route map” that will perform faster access to data. On average, the operation time complexities will be faster than without indexes.

We can have two types of indexes on our tables:

- **[Clustered](https://docs.microsoft.com/en-us/sql/relational-databases/sql-server-index-design-guide?view=sql-server-ver15#clustered-index-architecture)** – The **[B+-tree](https://en.wikipedia.org/wiki/B%2B_tree)** of a clustered index will be fulfilled with root, intermediary and leaves nodes. The root and intermediary nodes will be in index pages structures, containing a key value and a pointer to the data row on the leaf level, seeking through them in cascading way. This type of index is unique by table and it is created taking in basis the table primary key.

![Diagram showing the structure of a clustered index in a single partition.](https://docs.microsoft.com/en-us/sql/relational-databases/media/bokind2.gif?view=sql-server-ver15)
_Took from docs.microsoft.com_

- **[Nonclustered](https://docs.microsoft.com/en-us/sql/relational-databases/sql-server-index-design-guide?view=sql-server-ver15#Nonclustered)** – They have the same structure as clustered indexes, having a difference that the leaves are also index pages instead of **[data pages](https://docs.microsoft.com/en-us/sql/relational-databases/pages-and-extents-architecture-guide?view=sql-server-ver15)**. For this type of indexes, we have no maximum number for them, but should be taken into consideration that the SQL server will need to balance these **[B+-tree](https://en.wikipedia.org/wiki/B%2B_tree)** structures on any insert or delete operation, and these comport and effort. This effort will increase with the number of indexes, therefore these indexes should be added wisely after deeper analysis.

![Diagram showing the structure of a nonclustered index in a single partition.](https://docs.microsoft.com/en-us/sql/relational-databases/media/bokind1a.gif?view=sql-server-ver15)
_Took from docs.microsoft.com_

Friendliest representation of **[Clustered](https://docs.microsoft.com/en-us/sql/relational-databases/sql-server-index-design-guide?view=sql-server-ver15#clustered-index-architecture)** indexes…

![](https://toolgbrmaker.files.wordpress.com/2022/01/image-9.png?w=1024)

Friendliest representation of **[Nonclustered](https://docs.microsoft.com/en-us/sql/relational-databases/sql-server-index-design-guide?view=sql-server-ver15#Nonclustered)** indexes…

![](https://toolgbrmaker.files.wordpress.com/2022/01/image-8.png?w=1024)

Now, that you know that index pages are a “route map” to faster **[data page](https://docs.microsoft.com/en-us/sql/relational-databases/pages-and-extents-architecture-guide?view=sql-server-ver15)** access and how they work, you might end with a final doubt…

**Are we able to see how it looks like a [data page](https://docs.microsoft.com/en-us/sql/relational-databases/pages-and-extents-architecture-guide?view=sql-server-ver15)?**

To exemplify what we have inside of a **[data page](https://docs.microsoft.com/en-us/sql/relational-databases/pages-and-extents-architecture-guide?view=sql-server-ver15)**, I’ll use the structure created on this **[post](https://wordpress.com/post/toolgbrmaker.wordpress.com/48)** (if you didn’t read it, now it is a good time to jump into it).

Let’s add some dummy data to it…

```sql
INSERT INTO dbo.customer (CustomerNo,CustomerFirstName,CustomerLastName,CountryCode)
	VALUES (1,'Bruno','Guedes','PT'),
		   (2,'Marcelo','Sousa','PT'),
		   (3,'Antonio','Costa','PT');

```

Now let’s get a look at our table’s page structure by using the following query:

```sql
DBCC IND('SampleTest',customer,-1)
GO
```

![](https://toolgbrmaker.files.wordpress.com/2022/01/image-5.png?w=1024)

Here we are seeking the rows with **PageType** 1 that is the type of **[data page](https://docs.microsoft.com/en-us/sql/relational-databases/pages-and-extents-architecture-guide?view=sql-server-ver15)**. ( **PageType** 2 – index pages and **PageType** 10 – IAM page that maintains the index itself).

Now that we know the **PagePID** from our **[data page](https://docs.microsoft.com/en-us/sql/relational-databases/pages-and-extents-architecture-guide?view=sql-server-ver15)**, let’s use the following query to check the answer to our final doubt…

```sql
DBCC TRACEON(3604)
DBCC PAGE('SampleTest',1,400,3) WITH TABLERESULTS
GO
```

![](https://toolgbrmaker.files.wordpress.com/2022/01/image-6.png?w=609)

If you scroll until the end you can see that the data rows are stored in slots that start with a zero offset.

And that’s it, the complex monster (aka Indexes), and its herculean relation with **[data pages](https://docs.microsoft.com/en-us/sql/relational-databases/pages-and-extents-architecture-guide?view=sql-server-ver15)** ended, in this post, by being diminished for the world. Well, at least hopefully! ![😀](https://s0.wp.com/wp-content/mu-plugins/wpcom-smileys/twemoji/2/72x72/1f600.png)

**What’s next?**

Above you can find a small explanation for the index behavior on SQL server, but if you are looking to dive deeper into the concept I can suggest you the reading of this **[post](https://www.brentozar.com/archive/2006/04/sql-server-training-for-developers-primary-keys-indexes/)** from Brent Ozar that is light reading and very explanatory of how indexes act. I can also suggest the reading of the following **[book](https://www.amazon.com/gp/product/1260458873/ref=as_li_tl?ie=UTF8&camp=1789&creative=9325&creativeASIN=1260458873&linkCode=as2&tag=toolgbrmaker2-20&linkId=692d78d9987c5fa3b22e673107e26228)** that has one chapter fully dedicated to this subject. Good readings!

Stay safe!