Index Fundamentals Part 4

Today we have the focus on nonclustered indexes.
Nonclustered indexes doesn't modify the logical order of tabledata only clustered index does this.
They are working with pointers to the data in a clustered index or the table (heap).

For example we got a table with:
ID;Text;Timestamp
1;Test;01-01-2000 8:50:10:001
2;Test;02-01-2000 8:50:10:001
3;Test;03-01-2000 8:50:10:001
4;Test;04-01-2000 8:50:10:001
5;Test;05-01-2000 8:50:10:001
6;Test;06-01-2000 8:50:10:001
7;Test;07-01-2000 8:50:10:001
8;Test;08-01-2000 8:50:10:001
9;Test;09-01-2000 8:50:10:001
10;Test;10-01-2000 8:50:10:001
11;Test;11-01-2000 8:50:10:001
12;Test;12-01-2000 8:50:10:001
13;Test;13-01-2000 8:50:10:001
14;Test;14-01-2000 8:50:10:001
15;Test;15-01-2000 8:50:10:001
16;Test;16-01-2000 8:50:10:001
17;Test;17-01-2000 8:50:10:001
18;Test;18-01-2000 8:50:10:001
19;Test;19-01-2000 8:50:10:001
20;Test;20-01-2000 8:50:10:001

No we create a nonclustered index on Timestamp.
The pages look like
ROOT PAGE
Timestamp;Pointer to Page
01-01-2000 8:50:10:001;1
06-01-2000 8:50:10:001;2
11-01-2000 8:50:10:001;3
16-01-2000 8:50:10:001;4

PAGE 1
Timestamp;Pointer
01-01-2000 8:50:10:001;1
02-01-2000 8:50:10:001;2
03-01-2000 8:50:10:001;3
04-01-2000 8:50:10:001;4
05-01-2000 8:50:10:001;5

PAGE 2
Timestamp;Pointer
06-01-2000 8:50:10:001;6
07-01-2000 8:50:10:001;7
08-01-2000 8:50:10:001;8
09-01-2000 8:50:10:001;9
10-01-2000 8:50:10:001;10

PAGE 3
Timestamp;Pointer
11-01-2000 8:50:10:001;11
12-01-2000 8:50:10:001;12
13-01-2000 8:50:10:001;13
14-01-2000 8:50:10:001;14
15-01-2000 8:50:10:001;15

PAGE 4
Timestamp;Pointer
16-01-2000 8:50:10:001;16
17-01-2000 8:50:10:001;17
18-01-2000 8:50:10:001;18
19-01-2000 8:50:10:001;19
20-01-2000 8:50:10:001;20

After that we execute
select Text, Timestamp from table where Timestamp = '17-01-2000 8:50:10:001'
SQL Server uses the clustered index, rootpage points to Intermediate Page 4 and Intermediate Page 4 points to Pointer 16 in clustered index or heap and the result retrieves.

As you can see in my example the ID for the clustered index is int. Let us imagine it was a unique identifier
ID 1 int and a unique identifier ID 1C3C7DEA-D440-45C4-923B-6FF988957A41 you can see the difference the storage needed for a unique identifier is much more as for int.
So if you have a unique identifier in clustered index as index column all your nonclustered indexes on the same table use it as pointer and needs lot more space in storage.
To keep your indexes smart and fast you have to use a smart column for clustered index column.

In the next part we have a look at index maintenance.