11. dropping the clustered index shows all secondary indexes at + 9 bytes
( integer index replaced with guid index )
|
Index
id |
index_typec |
Index
depth |
Index
level |
Page
count |
Record
count |
avg_record
size_in
bytes |
|
0 |
HEAP |
1 |
0 |
153847 |
2000000 |
579 |
|
datetime 2 |
NONCLUSTERED INDEX |
3 |
0 |
4695 |
2000000 |
17 |
|
2 |
NONCLUSTERED INDEX |
3 |
1 |
15 |
4695 |
23 |
|
2 |
NONCLUSTERED INDEX |
3 |
2 |
1 |
15 |
23 |
|
Guid 3 |
NONCLUSTERED INDEX |
3 |
0 |
6689 |
2000000 |
25 |
|
3 |
NONCLUSTERED INDEX |
3 |
1 |
28 |
6689 |
31 |
|
3 |
NONCLUSTERED INDEX |
3 |
2 |
1 |
28 |
31 |
|
char(20) 4 |
NONCLUSTERED INDEX |
3 |
0 |
7663 |
2000000 |
29 |
|
4 |
NONCLUSTERED INDEX |
3 |
1 |
36 |
7663 |
35 |
|
4 |
NONCLUSTERED INDEX |
3 |
2 |
1 |
36 |
35 |
12. Compound clustered key; Integer+guid
secondary keys take int + guid + 1byte = + 21 bytes per entry
As expected from deduction in step 10
|
Index
id |
index_typec |
Index
depth |
Index
level |
Page
count |
Record
count |
avg_record
size_in
bytes |
|
1 |
CLUSTERED INDEX |
4 |
0 |
153847 |
2000000 |
577 |
|
1 |
CLUSTERED INDEX |
4 |
1 |
552 |
153847 |
27 |
|
1 |
CLUSTERED INDEX |
4 |
2 |
2 |
552 |
27 |
|
1 |
CLUSTERED INDEX |
4 |
3 |
1 |
2 |
27 |
|
2 |
NONCLUSTERED INDEX |
3 |
0 |
7663 |
2000000 |
29 |
|
2 |
NONCLUSTERED INDEX |
3 |
1 |
36 |
7663 |
35 |
|
2 |
NONCLUSTERED INDEX |
3 |
2 |
1 |
36 |
35 |
|
3 |
NONCLUSTERED INDEX |
3 |
0 |
9662 |
2000000 |
37 |
|
3 |
NONCLUSTERED INDEX |
3 |
1 |
54 |
9662 |
43 |
|
3 |
NONCLUSTERED INDEX |
|