Analysing Indexes Part 4 - Size does matter -2
4. Replacing the clustered Primary Key with a non clustered Primary Key on the integer column gives us two secondary indexes, each entry being + 9 bytes
The primary key is index 3
|
Index
id |
index_typec |
Index
depth |
Index
level |
Page
count |
Record
count |
avg_record
size_in
bytes |
|
0 |
HEAP |
1 |
0 |
153847 |
2000000 |
577 |
|
2 |
NONCLUSTERED INDEX |
3 |
0 |
4695 |
2000000 |
17 |
|
2 |
NONCLUSTERED INDEX |
3 |
1 |
15 |
4695 |
23 |
|
2 |
NONCLUSTERED INDEX |
3 |
2 |
1 |
15 |
23 |
|
3 |
NONCLUSTERED INDEX |
3 |
0 |
3711 |
2000000 |
13 |
|
3 |
NONCLUSTERED INDEX |
3 |
1 |
6 |
3711 |
11 |
|
3 |
NONCLUSTERED INDEX |
3 |
2 |
1 |
6 |
11 |
5. A non clustered unique constraint is the same ( index 3 again )
|
Index
id |
index_typec |
Index
depth |
Index
level |
Page
count |
Record
count |
avg_record
size_in
bytes |
|
0 |
HEAP |
1 |
0 |
153847 |
2000000 |
577 |
|
2 |
NONCLUSTERED INDEX |
3 |
0 |
4695 |
2000000 |
17 |
|
2 |
NONCLUSTERED INDEX |
3 |
1 |
15 |
4695 |
23 |
|
2 |
NONCLUSTERED INDEX |
3 |
2 |
1 |
15 |
23 |
|
3 |
NONCLUSTERED INDEX |
3 |
0 |
3711 |
2000000 |
13 |
|
3 |
NONCLUSTERED INDEX |
3 |
1 |
6 |
3711 |
11 |
|
3 |
NONCLUSTERED INDEX |
3 |
2 |
1 |
6 |
11 |
6. A clustered unique constraint is the same as a clustered Primary Key
The results are the same as step 3
( Index 2 is the same index as created in step 2 )
|
Index
id |
index_typec |
Index
depth |
Index
level |
Page
count |
Record
count |
avg_record
size_in
bytes |
|
1 |
CLUSTERED INDEX |
3 |
0 |
153847 |
2000000 |
577 |
|
1 |
CLUSTERED INDEX |
3 |
1 |
248 |
153847 |
11 |
|
1 |
CLUSTERED INDEX |
3 |
2 |
1 |
248 |
11 |
|
2 |
NONCLUSTERED INDEX |
3 |
0 |
3711 |
|