MIME-Version: 1.0 X-Document-Type: Workbook Content-Type: multipart/related; boundary="----=_NextPart_01C83851.E2ED7B60" This document is a Single File Web Page, also known as a Web Archive file. If you are seeing this message, your browser or editor doesn't support Web Archive files. Please download a browser that supports Web Archive, such as Windows® Internet Explorer®. ------=_NextPart_01C83851.E2ED7B60 Content-Location: file:///C:/204BDD12/AnalysingIndexesPart4Sizedoesmatter.htm Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset="us-ascii" <body> <p>This page uses frames, but your browser doesn't support them.</p> </body> ------=_NextPart_01C83851.E2ED7B60 Content-Location: file:///C:/204BDD12/AnalysingIndexesPart4Sizedoesmatter_stylesheet.css Content-Transfer-Encoding: quoted-printable Content-Type: text/css; charset="us-ascii" tr {mso-height-source:auto;} col {mso-width-source:auto;} br {mso-data-placement:same-cell;} .style0 {mso-number-format:General; text-align:general; vertical-align:bottom; white-space:nowrap; mso-rotate:0; mso-background-source:auto; mso-pattern:auto; color:windowtext; font-size:10.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Arial; mso-generic-font-family:auto; mso-font-charset:0; border:none; mso-protection:locked visible; mso-style-name:Normal; mso-style-id:0;} .font6 {color:black; font-size:8.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Tahoma; mso-generic-font-family:auto; mso-font-charset:0;} .font7 {color:black; font-size:8.0pt; font-weight:700; font-style:normal; text-decoration:none; font-family:Tahoma; mso-generic-font-family:auto; mso-font-charset:0;} .font10 {color:black; font-size:8.0pt; font-weight:700; font-style:normal; text-decoration:none; font-family:Tahoma, sans-serif; mso-font-charset:0;} td {mso-style-parent:style0; padding:0px; mso-ignore:padding; color:windowtext; font-size:10.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Arial; mso-generic-font-family:auto; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;} .xl65 {mso-style-parent:style0; text-align:right;} .xl66 {mso-style-parent:style0; font-weight:700; font-family:Arial, sans-serif; mso-font-charset:0; text-align:right;} .xl67 {mso-style-parent:style0; text-align:right; background:#333399; mso-pattern:black none;} .xl68 {mso-style-parent:style0; text-align:left;} .xl69 {mso-style-parent:style0; text-align:right; background:#969696; mso-pattern:black none;} .xl70 {mso-style-parent:style0; font-family:Arial, sans-serif; mso-font-charset:0; text-align:right; background:navy; mso-pattern:black none;} .xl71 {mso-style-parent:style0; text-align:right; background:navy; mso-pattern:black none;} .xl72 {mso-style-parent:style0; color:white; font-size:14.0pt; font-family:Arial, sans-serif; mso-font-charset:0; text-align:right; background:navy; mso-pattern:black none;} .xl73 {mso-style-parent:style0; color:white; font-size:14.0pt; font-family:Arial, sans-serif; mso-font-charset:0; text-align:right; background:#333399; mso-pattern:black none;} .xl74 {mso-style-parent:style0; color:#333399; font-family:Arial, sans-serif; mso-font-charset:0; text-align:right; background:#333399; mso-pattern:black none;} .xl75 {mso-style-parent:style0; color:#F2F2F2; font-size:16.0pt; font-family:Arial, sans-serif; mso-font-charset:0; text-align:right; background:#003399; mso-pattern:black none;} .xl76 {mso-style-parent:style0; text-align:right; border:.5pt solid windowtext;} .xl77 {mso-style-parent:style0; font-weight:700; font-family:Arial, sans-serif; mso-font-charset:0; text-align:right; border:.5pt solid windowtext; background:#DBE5F1; mso-pattern:black none;} .xl78 {mso-style-parent:style0; font-weight:700; font-family:Arial, sans-serif; mso-font-charset:0; text-align:right; background:#DBE5F1; mso-pattern:black none;} .xl79 {mso-style-parent:style0; font-family:Arial, sans-serif; mso-font-charset:0; text-align:right;} .xl80 {mso-style-parent:style0; font-weight:700; font-family:Arial, sans-serif; mso-font-charset:0; text-align:right; background:white; mso-pattern:black none;} ------=_NextPart_01C83851.E2ED7B60 Content-Location: file:///C:/204BDD12/AnalysingIndexesPart4Sizedoesmatter_tabstrip.htm Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset="us-ascii"
 Sheet1   Sheet2   Sheet3 
------=_NextPart_01C83851.E2ED7B60 Content-Location: file:///C:/204BDD12/AnalysingIndexesPart4Sizedoesmatter_sheet001.htm Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset="us-ascii"
index_id index_type_desc index_depth index_level page_count record_count min_record_size_in_bytes max_record_size_in_bytes avg_record_size_in_bytes
0 HEAP 1 0 83341 999999 577 577 577
<= /td>
d= atetime index nullable - no PK
<= /td>
index_id index_type_desc index_depth index_level page_count record_count min_record_size_in_bytes max_record_size_in_bytes avg_record_size_in_bytes
0 HEAP 1 0 83341 999999 577 577 577
2 NONCLUSTERED INDEX 3 0 2718 999999 20 20 20
2 NONCLUSTERED INDEX 3 1 10 2718 26 26 26
2 NONCLUSTERED INDEX 3 2 1 10 26 26 26
name rows reserved data index_size unused
Testtable2 999999 666824 KB 666728 KB 8 KB 88 KB
<= /td>
index_id index_type_desc index_depth index_level page_count record_count min_record_size_in_bytes max_record_size_in_bytes avg_record_size_in_bytes
0 HEAP 1 0 83341 999999 577 577 577
s= ame index not null reduces index size
<= /td>
index_id index_type_desc index_depth index_level page_count record_count min_record_size_in_bytes max_record_size_in_bytes avg_record_size_in_bytes
0 HEAP 1 0 83341 999999 577 577 577
2 NONCLUSTERED INDEX 3 0 2348 999999 17 17 17
2 NONCLUSTERED INDEX 3 1 8 2348 23 23 23
2 NONCLUSTERED INDEX 3 2 1 8 23 23 23
<= /td>
2672 pages less 20.8mb - for 1 million rows,= for a 400 million row table this would equate to  just over 8Gb
<= /td>
n= ow with clustered index but testtable 1 nullable datetime
<= /td>
index_id index_type_desc index_depth index_level page_count record_count min_record_size_in_bytes max_record_size_in_bytes avg_record_size_in_bytes
1 CLUSTERED INDEX 3 0 76923 999999 577 577 577
1 CLUSTERED INDEX 3 1 124 76923 11 11 11
1 CLUSTERED INDEX 3 2 1 124 11 11 11
2 NONCLUSTERED INDEX 3 0 2228 999999 16 16 16
2 NONCLUSTERED INDEX 3 1 7 2228 22 22 22
2 NONCLUSTERED INDEX 3 2 1 7 22 22 22
<= /td>
P= K on the non nullable secondary index
<= /td>
index_id index_type_desc index_depth index_level page_count record_count min_record_size_in_bytes max_record_size_in_bytes avg_record_size_in_bytes
1 CLUSTERED INDEX 3 0 76923 999999 577 577 577
1 CLUSTERED INDEX 3 1 124 76923 11 11 11
1 CLUSTERED INDEX 3 2 1 124 11 11 11
2 NONCLUSTERED INDEX 3 0 1856 999999 13 13 13
2 NONCLUSTERED INDEX 3 1 5 1856 19 19 19
2 NONCLUSTERED INDEX 3 2 1 5 19 19 19
t= esttable 1 now has non clustered PK =3D index 3 original index =3D index 2
index_id index_type_desc index_depth index_level page_count record_count min_record_size_in_bytes max_record_size_in_bytes avg_record_size_in_bytes
0 HEAP 1 0 76923 999999 577 577 577
2 NONCLUSTERED INDEX 3 0 2718 999999 20 20 20
2 NONCLUSTERED INDEX 3 1 10 2718 26 26 26
2 NONCLUSTERED INDEX 3 2 1 10 26 26 26
3 NONCLUSTERED INDEX 3 0 1856 999999 13 13 13
3 NONCLUSTERED INDEX 3 1 3 1856 11 11 11
3 NONCLUSTERED INDEX 3 2 1 3 11 11 11
<= /td>
A= dded a clustered uniuqe constraint on Numkey - see size dif of index 2 and 3
<= /td>
index_id index_type_desc index_depth index_level page_count record_count min_record_size_in_bytes max_record_size_in_bytes avg_record_size_in_bytes
1 CLUSTERED INDEX 3 0 76923 999999 577 577 577
1 CLUSTERED INDEX 3 1 267 76923 26 26 26
1 CLUSTERED INDEX 3 2 1 267 26 26 26
2 NONCLUSTERED INDEX 3 0 3718 999999 28 28 28
2 NONCLUSTERED INDEX 3 1 17 3718 34 34 34
2 NONCLUSTERED INDEX 3 2 1 17 34 34 34
3 NONCLUSTERED INDEX 3 0 3216 999999 24 24 24
3 NONCLUSTERED INDEX 3 1 6 3216 11 11 11
3 NONCLUSTERED INDEX 3 2 1 6 11 11 11
<= /td>
i= ndex 2 is now nearly 8mb bigger whilst index 3 is now nearly 11mb bigger
------=_NextPart_01C83851.E2ED7B60 Content-Location: file:///C:/204BDD12/AnalysingIndexesPart4Sizedoesmatter_sheet002.htm Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset="us-ascii"
1                
name rows reserved data index_size unused
Testtable2 2000000 1333576 KB 1333392 KB 8 KB 176 KB
(with nulls )
index_id index_type_desc index_depth index_level page_count record_count min_record_size_in_bytes max_record_size_in_bytes avg_record_size_in_bytes
0 HEAP 1 0 166674 2000000 577 577 577
 
name rows reserved data index_size unused
TestTable 2000000 1333576 KB 1333392 KB 8 KB 176 KB
( no nulls )
index_id index_type_desc index_depth index_level page_count record_count min_record_size_in_bytes max_record_size_in_bytes avg_record_size_in_bytes
0 HEAP 1 0 166674 2000000 577 577 577
[1]
2  
Testtable2
index_id index_type_desc index_depth index_level page_count record_count min_record_size_in_bytes max_record_size_in_bytes avg_record_size_in_bytes
0 HEAP 1 0 166674 2000000 577 577 577
2 NONCLUSTERED = INDEX 3 0 4695 2000000 17 17 17
2 NONCLUSTERED = INDEX 3 1 15 4695 23 23 23
2 NONCLUSTERED = INDEX 3 2 1 15 23 23 23
TestTable
index_id index_type_desc index_depth index_level page_count record_count min_record_size_in_bytes max_record_size_in_bytes avg_record_size_in_bytes
0 HEAP 1 0 166674 2000000 577 577 577
2 NONCLUSTERED = INDEX 3 0 5435 2000000 20 20 20
2 NONCLUSTERED = INDEX 3 1 19 5435 26 26 26
2 NONCLUSTERED = INDEX 3 2 1 19 26 26 26
[2]
3  
Testtable2 clustered PK
index_id index_type_desc index_depth index_level page_count record_count min_record_size_in_bytes max_record_size_in_bytes avg_record_size_in_bytes
1 CLUSTERED IND= EX 3 0 153847 2000000 577 577 577
1 CLUSTERED IND= EX 3 1 248 153847 11 11 11
1 CLUSTERED IND= EX 3 2 1 248 11 11 11
2 NONCLUSTERED = INDEX 3 0 3711 2000000 13 13 13
2 NONCLUSTERED = INDEX 3 1 10 3711 19 19 19
2 NONCLUSTERED = INDEX 3 2 1 10 19 19 19
testTable  clustered PK
index_id index_type_desc index_depth index_level page_count record_count min_record_size_in_bytes max_record_size_in_bytes avg_record_size_in_bytes
1 CLUSTERED IND= EX 3 0 153847 2000000 577 577 577
1 CLUSTERED IND= EX 3 1 248 153847 11 11 11
1 CLUSTERED IND= EX 3 2 1 248 11 11 11
2 NONCLUSTERED = INDEX 3 0 4455 2000000 16 16 16
2 NONCLUSTERED = INDEX 3 1 14 4455 22 22 22
2 NONCLUSTERED = INDEX 3 2 1 14 22 22 22
[3]
4  
Testtable2 non clustered PK
index_id index_type_desc index_depth index_level page_count record_count min_record_size_in_bytes max_record_size_in_bytes avg_record_size_in_bytes
0 HEAP 1 0 153847 2000000 577 577 577
2 NONCLUSTERED = INDEX 3 0 4695 2000000 17 17 17
2 NONCLUSTERED = INDEX 3 1 15 4695 23 23 23
2 NONCLUSTERED = INDEX 3 2 1 15 23 23 23
3 NONCLUSTERED = INDEX 3 0 3711 2000000 13 13 13
3 NONCLUSTERED = INDEX 3 1 6 3711 11 11 11
3 NONCLUSTERED = INDEX 3 2 1 6 11 11 11
[4]
5  
Testtable2 unique constraint
index_id index_type_desc index_depth index_level page_count record_count min_record_size_in_bytes max_record_size_in_bytes avg_record_size_in_bytes
0 HEAP 1 0 153847 2000000 577 577 577
2 NONCLUSTERED = INDEX 3 0 4695 2000000 17 17 17
2 NONCLUSTERED = INDEX 3 1 15 4695 23 23 23
2 NONCLUSTERED = INDEX 3 2 1 15 23 23 23
3 NONCLUSTERED = INDEX 3 0 3711 2000000 13 13 13
3 NONCLUSTERED = INDEX 3 1 6 3711 11 11 11
3 NONCLUSTERED = INDEX 3 2 1 6 11 11 11
[5]
6  
Testtable2 clustered unique constraint
index_id index_type_desc index_depth index_level page_count record_count min_record_size_in_bytes max_record_size_in_bytes avg_record_size_in_bytes
1 CLUSTERED IND= EX 3 0 153847 2000000 577 577 577
1 CLUSTERED IND= EX 3 1 248 153847 11 11 11
1 CLUSTERED IND= EX 3 2 1 248 11 11 11
2 NONCLUSTERED = INDEX 3 0 3711 2000000 13 13 13
2 NONCLUSTERED = INDEX 3 1 10 3711 19 19 19
2 NONCLUSTERED = INDEX 3 2 1 10 19 19 19
[6]
7  
testtable2 sguid as clustered PK
index_id index_type_desc index_depth index_level page_count record_count min_record_size_in_bytes max_record_size_in_bytes avg_record_size_in_bytes
1 CLUSTERED IND= EX 4 0 153847 2000000 577 577 577
1 CLUSTERED IND= EX 4 1 477 153847 23 23 23
1 CLUSTERED IND= EX 4 2 2 477 23 23 23
1 CLUSTERED IND= EX 4 3 1 2 23 23 23
2 NONCLUSTERED = INDEX 3 0 6689 2000000 25 25 25
2 NONCLUSTERED = INDEX 3 1 28 6689 31 31 31
2 NONCLUSTERED = INDEX 3 2 1 28 31 31 31
[7]
8  
testtable
index_id index_type_desc index_depth index_level page_count record_count min_record_size_in_bytes max_record_size_in_bytes avg_record_size_in_bytes
1 CLUSTERED IND= EX 4 0 153847 2000000 577 577 577
1 CLUSTERED IND= EX 4 1 477 153847 23 23 23
1 CLUSTERED IND= EX 4 2 2 477 23 23 23
1 CLUSTERED IND= EX 4 3 1 2 23 23 23
2 NONCLUSTERED = INDEX 3 0 7435 2000000 28 28 28
2 NONCLUSTERED = INDEX 3 1 34 7435 34 34 34
2 NONCLUSTERED = INDEX 3 2 1 34 34 34 34
[8]
9  
testtable2
index_id index_type_desc index_depth index_level page_count record_count min_record_size_in_bytes max_record_size_in_bytes avg_record_size_in_bytes
1 CLUSTERED IND= EX 4 0 153847 2000000 579 579 579
1 CLUSTERED IND= EX 4 1 477 153847 23 23 23
1 CLUSTERED IND= EX 4 2 2 477 23 23 23
1 CLUSTERED IND= EX 4 3 1 2 23 23 23
2 NONCLUSTERED = INDEX 3 0 6689 2000000 25 25 25
2 NONCLUSTERED = INDEX 3 1 28 6689 31 31 31
2 NONCLUSTERED = INDEX 3 2 1 28 31 31 31
3 NONCLUSTERED = INDEX 3 0 5682 2000000 21 21 21
3 NONCLUSTERED = INDEX 3 1 10 5682 11 11 11
3 NONCLUSTERED = INDEX 3 2 1 10 11 11 11
[9]
10  
testtable2
index_id index_type_desc index_depth index_level page_count record_count min_record_size_in_bytes max_record_size_in_bytes avg_record_size_in_bytes
1 CLUSTERED IND= EX 4 0 153847 2000000 579 579 579
1 CLUSTERED IND= EX 4 1 477 153847 23 23 23
1 CLUSTERED IND= EX 4 2 2 477 23 23 23
1 CLUSTERED IND= EX 4 3 1 2 23 23 23
2 NONCLUSTERED = INDEX 3 0 6689 2000000 25 25 25
2 NONCLUSTERED = INDEX 3 1 28 6689 31 31 31
2 NONCLUSTERED = INDEX 3 2 1 28 31 31 31
3 NONCLUSTERED = INDEX 3 0 5682 2000000 21 21 21
3 NONCLUSTERED = INDEX 3 1 10 5682 11 11 11
3 NONCLUSTERED = INDEX 3 2 1 10 11 11 11
4 NONCLUSTERED = INDEX 3 0 9662 2000000 37 37 37
4 NONCLUSTERED = INDEX 3 1 54 9662 43 43 43
4 NONCLUSTERED = INDEX 3 2 1 54 43 43 43
[10]
11  
testtable2
index_id index_type_desc index_depth index_level page_count record_count min_record_size_in_bytes max_record_size_in_bytes avg_record_size_in_bytes
0 HEAP 1 0 153847 2000000 579 579 579
dat= etime 2 NONCLUSTERED = INDEX 3 0 4695 2000000 17 17 17
2 NONCLUSTERED = INDEX 3 1 15 4695 23 23 23
2 NONCLUSTERED = INDEX 3 2 1 15 23 23 23
Gui= d  3 NONCLUSTERED = INDEX 3 0 6689 2000000 25 25 25
3 NONCLUSTERED = INDEX 3 1 28 6689 31 31 31
3 NONCLUSTERED = INDEX 3 2 1 28 31 31 31
cha= r(20) 4 NONCLUSTERED = INDEX 3 0 7663 2000000 29 29 29
4 NONCLUSTERED = INDEX 3 1 36 7663 35 35 35
4 NONCLUSTERED = INDEX 3 2 1 36 35 35 35
[11]
12  
testtable2
index_id index_type_desc index_depth index_level page_count record_count min_record_size_in_bytes max_record_size_in_bytes avg_record_size_in_bytes
1 CLUSTERED IND= EX 4 0 153847 2000000 577 577 577
1 CLUSTERED IND= EX 4 1 552 153847 27 27 27
1 CLUSTERED IND= EX 4 2 2 552 27 27 27
1 CLUSTERED IND= EX 4 3 1 2 27 27 27
2 NONCLUSTERED = INDEX 3 0 7663 2000000 29 29 29
2 NONCLUSTERED = INDEX 3 1 36 7663 35 35 35
2 NONCLUSTERED = INDEX 3 2 1 36 35 35 35
3 NONCLUSTERED = INDEX 3 0 9662 2000000 37 37 37
3 NONCLUSTERED = INDEX 3 1 54 9662 43 43 43
3 NONCLUSTERED = INDEX 3 2 1 54 43 43 43
4 NONCLUSTERED = INDEX 3 0 10639 2000000 41 41 41
4 NONCLUSTERED = INDEX 3 1 65 10639 47 47 47
4 NONCLUSTERED = INDEX 3 2 1 65 47 47 47
Testtable2 PK_TestTable2 1 CLUS   [12]
Tes= ttable2 idx_Test2_Dat= etimeNullable 2 nc datetime
Tes= ttable2 idx_test2_cgu= id 3 nc guid
Tes= ttable2 idx_test2_cCh= ar1 4 nc char(20)
13  
index_id index_type_desc index_depth index_level page_count record_count min_record_size_in_bytes max_record_size_in_bytes avg_record_size_in_bytes
1 CLUSTERED IND= EX 4 0 153847 2000000 577 577 577
1 CLUSTERED IND= EX 4 1 1315 153847 67 67 67
1 CLUSTERED IND= EX 4 2 12 1315 67 67 67
1 CLUSTERED IND= EX 4 3 1 12 67 67 67
[13]
14  
index_id index_type_desc index_depth index_level page_count record_count min_record_size_in_bytes max_record_size_in_bytes avg_record_size_in_bytes
1 CLUSTERED IND= EX 4 0 153847 2000000 577 577 577
1 CLUSTERED IND= EX 4 1 1315 153847 67 67 67
1 CLUSTERED IND= EX 4 2 12 1315 67 67 67
1 CLUSTERED IND= EX 4 3 1 12 67 67 67
2 NONCLUSTERED = INDEX 4 0 17544 2000000 69 69 69
2 NONCLUSTERED = INDEX 4 1 168 17544 75 75 75
2 NONCLUSTERED = INDEX 4 2 2 168 75 75 75
2 NONCLUSTERED = INDEX 4 3 1 2 75 75 75
3 NONCLUSTERED = INDEX 4 0 17544 2000000 69 69 69
3 NONCLUSTERED = INDEX 4 1 168 17544 75 75 75
3 NONCLUSTERED = INDEX 4 2 2 168 75 75 75
3 NONCLUSTERED = INDEX 4 3 1 2 75 75 75
4 NONCLUSTERED = INDEX 4 0 17544 2000000 69 69 69
4 NONCLUSTERED = INDEX 4 1 168 17544 75 75 75
4 NONCLUSTERED = INDEX 4 2 2 168 75 75 75
4 NONCLUSTERED = INDEX 4 3 1 2 75 75 75
[14]
15  
index_id index_type_desc index_depth index_level page_count record_count min_record_size_in_bytes max_record_size_in_bytes avg_record_size_in_bytes
1 CLUSTERED IND= EX 5 0 1384615 2000000 3527 3527 3527
1 CLUSTERED IND= EX 5 1 21038 1384615 67 67 67
1 CLUSTERED IND= EX 5 2 337 21038 67 67 67
1 CLUSTERED IND= EX 5 3 5 337 67 67 67
1 CLUSTERED IND= EX 5 4 1 5 67 67 67
2 NONCLUSTERED = INDEX 4 0 15625 2000000 61 61 61
2 NONCLUSTERED = INDEX 4 1 134 15625 67 67 67
2 NONCLUSTERED = INDEX 4 2 2 134 67 67 67
2 NONCLUSTERED = INDEX 4 3 1 2 67 67 67
3 NONCLUSTERED = INDEX 4 0 17544 2000000 69 69 69
3 NONCLUSTERED = INDEX 4 1 168 17544 75 75 75
3 NONCLUSTERED = INDEX 4 2 2 168 75 75 75
3 NONCLUSTERED = INDEX 4 3 1 2 75 75 75
4 NONCLUSTERED = INDEX 4 0 17544 2000000 69 69 69
4 NONCLUSTERED = INDEX 4 1 168 17544 75 75 75
4 NONCLUSTERED = INDEX 4 2 2 168 75 75 75
4 NONCLUSTERED = INDEX 4 3 1 2 75 75 75
[15]
16  
index_id index_type_desc index_depth index_level page_count record_count min_record_size_in_bytes max_record_size_in_bytes avg_record_size_in_bytes
1 CLUSTERED IND= EX 4 0 1000000 2000000 3527 3527 3527
1 CLUSTERED IND= EX 4 1 1608 1000000 11 11 11
1 CLUSTERED IND= EX 4 2 3 1608 11 11 11
1 CLUSTERED IND= EX 4 3 1 3 11 11 11
2 NONCLUSTERED = INDEX 4 0 15625 2000000 61 61 61
2 NONCLUSTERED = INDEX 4 1 134 15625 67 67 67
2 NONCLUSTERED = INDEX 4 2 2 134 67 67 67
2 NONCLUSTERED = INDEX 4 3 1 2 67 67 67
3 NONCLUSTERED = INDEX 3 0 3711 2000000 13 13 13
3 NONCLUSTERED = INDEX 3 1 10 3711 19 19 19
3 NONCLUSTERED = INDEX 3 2 1 10 19 19 19
4 NONCLUSTERED = INDEX 3 0 5682 2000000 21 21 21
4 NONCLUSTERED = INDEX 3 1 21 5682 27 27 27
4 NONCLUSTERED = INDEX 3 2 1 21 27 27 27
 
[1]
contray to poplular belief the table w= ith the nulls doesn't show up as being any different in size
[2]
The non null column index is 5.8mb sma= ller in size , that's 1.1gb on my 400 million row table
[3]
Adding a clustered PK reduces the size= of the secondary index by 7.7mb - that's 1.5gb on my 400 million row table<= /div>
[4]
adding a non clustered primary key is t= he same as adding a secondary index to the HEAP
[5]
a unique constraint is just another ind= ex
[6]
a unique clustered constraint produces = the same result as a clustered PK
[7]
Changing the clustered pk from an inte= ger to a unique identifier increases the size of the secondary index significantly. = 2996 extra pages, 23mb. 4.6gb larger for the target table. Our datetime, nominal= ly 8bytes, now requires an extra 17 bytes per index entry. Note also that the = PK depth has increased by one level compared to the integer, It appears the overhead for the clustered PK is to add 7 bytes to the data size
[8]
the nullable column with the index rem= ains 3 bytes wider per entry
[9]
index 3 is the nc pk, int, index 2 the datetime
[10]
Adding an index on a char(20) shows the= usual + 17 bytes
[11]
three non clustered indexes on a HEAP a= ll +9 bytes
[12]
int clustered key =3D + 5 bytes
guid clustered key =3D + 17 bytes
int+guid clustered key =3D + 21 bytes
so index keys take width of clustered columns + 1 byte
[13]
alter table dbo.testtable2 add constrai= nt PK_TestTable2 primary key clustered  (NumKey,cSguid,cBig,cInt,cDate2,cChar1); ( 60 bytes wide )
[14]
despite the three secondary indexes bei= ng defined on different columns their sizes are the same
[15]
The table has been widened to emphasise= the impact of the table on the clustered index, despite the column selection for the clustered index it's size is always the table, NOT the size of the colu= mns in the clustered index
------=_NextPart_01C83851.E2ED7B60 Content-Location: file:///C:/204BDD12/AnalysingIndexesPart4Sizedoesmatter_sheet003.htm Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset="us-ascii"
------=_NextPart_01C83851.E2ED7B60 Content-Location: file:///C:/204BDD12/AnalysingIndexesPart4Sizedoesmatter_filelist.xml Content-Transfer-Encoding: quoted-printable Content-Type: text/xml; charset="utf-8" ------=_NextPart_01C83851.E2ED7B60--