Why Indexing

I decided to create some sample table with different features. All the fields are the same, except in the primary key or indexes. In the next step, I inserted nearly a million records to them and check the fragmentation and storage size. After checking those values, I re-organize their existing indexes and compare to each other and results are as follow:

SQL Version 2008 Express Edition:

Microsoft SQL Server Management Studio            10.50.1600.1

Microsoft Data Access Components (MDAC)            3.85.1132

Microsoft MSXML                        2.6 3.0 4.0 5.0 6.0

Microsoft Internet Explorer                    8.0.6001.18702

Microsoft .NET Framework                    2.0.50727.3603

Operating System                        5.1.2600

Use TestDB



Go



--Only Primary key

Create Table tblJobDetail_PK
    (JobNo Varchar(10),
    JobMonth SmallDateTime,
    JobTransNo SmallInt,
    TransSource Varchar(10),
    SmallMoneyField SmallMoney,
    CharField Char(20),
    DateTimeField DateTime,
    DateField Date,
    FloatField Float,
    IntField Int,
    SmallIntField SmallInt,
    VarcharField Varchar(300)
   
    Constraint PK_tblJobDetail_PK Primary Key (JobNo, JobMonth, JobTransNo)
)

Go

--Only Clustered Index
Create Table tblJobDetail_IX
    (JobNo Varchar(10),
    JobMonth SmallDateTime,
    JobTransNo SmallInt,
    TransSource Varchar(10),
    SmallMoneyField SmallMoney,
    CharField Char(20),
    DateTimeField DateTime,
    DateField Date,
    FloatField Float,
    IntField Int,
    SmallIntField SmallInt,
    VarcharField Varchar(300)     )

    Create Clustered Index IX_tblJobDetail_IX On tblJobDetail_IX (JobNo, JobMonth, JobTransNo ASC) With FillFactor = 80

Go

--Only Non-Clustered Index
Create Table tblJobDetail_NIX
    (JobNo Varchar(10),
    JobMonth SmallDateTime,
    JobTransNo SmallInt,
    TransSource Varchar(10),
    SmallMoneyField SmallMoney,
    CharField Char(20),
    DateTimeField DateTime,
    DateField Date,
    FloatField Float,
    IntField Int,
    SmallIntField SmallInt,
    VarcharField Varchar(300)
     )

    Create NonClustered Index IX_tblJobDetail_NIX On tblJobDetail_NIX (JobNo, JobMonth, JobTransNo ASC) With FillFactor = 80

Go

--Only Unique Clustered Index
Create Table tblJobDetail_UIX
    (JobNo Varchar(10),
    JobMonth SmallDateTime,
    JobTransNo SmallInt,
    TransSource Varchar(10),
    SmallMoneyField SmallMoney,
    CharField Char(20),
    DateTimeField DateTime,
    DateField Date,
    FloatField Float,
    IntField Int,
    SmallIntField SmallInt,
    VarcharField Varchar(300)   )
    Create Unique Clustered Index IX_tblJobDetail_UIX On tblJobDetail_UIX (JobNo, JobMonth, JobTransNo ASC) With FillFactor = 80

Go

--Primary Key with Non-Clustered Index (Non-Clustered Index include Primary Key fields)
Create Table tblJobDetail_PKIX
    (JobNo Varchar(10),
    JobMonth SmallDateTime,
    JobTransNo SmallInt,
    TransSource Varchar(10),
    SmallMoneyField SmallMoney,
    CharField Char(20),
    DateTimeField DateTime,
    DateField Date,
    FloatField Float,
    IntField Int,
    SmallIntField SmallInt,
    VarcharField Varchar(300)    Constraint PK_tblJobDetail_PKIX Primary Key (JobNo, JobMonth, JobTransNo )
)

   Create NonClustered Index IX_tblJobDetail_NPKIX On tblJobDetail_PKIX (JobNo, JobMonth, JobTransNo, TransSource ASC)With FillFactor = 80

Go

--Primary Key With Non-Clustered Index (Non-Clustered Index does not include any field from Primary Key)
Create Table tblJobDetail_NPKIX
    (JobNo Varchar(10),
    JobMonth SmallDateTime,
    JobTransNo SmallInt,
    TransSource Varchar(10),
    SmallMoneyField SmallMoney,
    CharField Char(20),
    DateTimeField DateTime,
    DateField Date,
    FloatField Float,
    IntField Int,
    SmallIntField SmallInt,
    VarcharField Varchar(300)
    Constraint PK_tblJobDetail_NPKIX Primary Key (JobNo, JobMonth, JobTransNo )
)

    Create NonClustered Index IX_tblJobDetail_NPKIX1 On tblJobDetail_NPKIX (TransSource ASC)With FillFactor = 80

Go

--NO Primary Key and NO Index as well
Create Table tblJobDetail_NOIX
    (JobNo Varchar(10),
    JobMonth SmallDateTime,
    JobTransNo SmallInt,
    TransSource Varchar(10),
    SmallMoneyField SmallMoney,
    CharField Char(20),
    DateTimeField DateTime,
    DateField Date,
    FloatField Float,
    IntField Int,
    SmallIntField SmallInt,
    VarcharField Varchar(300)
    )

Go


Declare @RowNo Int
Declare @JobNo Varchar(10)
Declare @JobMonth SmallDateTime
Declare @JobTransNo Int
Declare @TransSource Varchar(10)

Set @RowNo = 1
Set @JobNo = ''
Set @JobMonth = '07/01/2032'
Set @JobTransNo = 1
Set @TransSource = ''

While @RowNo <= 210000
Begin
    While @JobTransNo <= 1000
    Begin
        Set @JobNo = Cast(DATEPART(Month, @JobMonth) As CHAR(2)) + Cast(DATEPART(Day, @JobMonth) As CHAR(2)) + Cast(DATEPART(Year, @JobMonth) As CHAR(4))

        Set @TransSource = DATENAME(Month, @JobMonth)

      

        Insert Into tblJobDetail_PK (JobNo, JobMonth, JobTransNo, TransSource, SmallMoneyField, CharField, DateTimeField, DateField, FloatField, IntField, SmallIntField, VarcharField)

        Select @JobNo, @JobMonth, @JobTransNo, @TransSource, @RowNo, @TransSource,
GETDATE(), CONVERT(Date, GetDate()), CONVERT(float, @RowNo), CONVERT(int, @RowNo), @JobTransNo, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ abcdefghijklmnopqrstuvwxyz'

      

        Insert Into tblJobDetail_IX (JobNo, JobMonth, JobTransNo, TransSource, SmallMoneyField, CharField, DateTimeField, DateField, FloatField, IntField, SmallIntField, VarcharField)

        Select @JobNo, @JobMonth, @JobTransNo, @TransSource, @RowNo, @TransSource,
GETDATE(), CONVERT(Date, GetDate()), CONVERT(float, @RowNo), CONVERT(int, @RowNo), @JobTransNo, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ abcdefghijklmnopqrstuvwxyz'



        Insert Into tblJobDetail_NIX (JobNo, JobMonth, JobTransNo, TransSource, SmallMoneyField, CharField, DateTimeField, DateField, FloatField, IntField, SmallIntField, VarcharField)

        Select @JobNo, @JobMonth, @JobTransNo, @TransSource, @RowNo, @TransSource,
GETDATE(), CONVERT(Date, GetDate()), CONVERT(float, @RowNo), CONVERT(int, @RowNo), @JobTransNo, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ abcdefghijklmnopqrstuvwxyz'



        Insert Into tblJobDetail_NPKIX (JobNo, JobMonth, JobTransNo, TransSource, SmallMoneyField, CharField, DateTimeField, DateField, FloatField, IntField, SmallIntField, VarcharField)

        Select @JobNo, @JobMonth, @JobTransNo, @TransSource, @RowNo, @TransSource,
GETDATE(), CONVERT(Date, GetDate()), CONVERT(float, @RowNo), CONVERT(int, @RowNo), @JobTransNo, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ abcdefghijklmnopqrstuvwxyz'



        Insert Into tblJobDetail_PKIX (JobNo, JobMonth, JobTransNo, TransSource, SmallMoneyField, CharField, DateTimeField, DateField, FloatField, IntField, SmallIntField, VarcharField)

        Select @JobNo, @JobMonth, @JobTransNo, @TransSource, @RowNo, @TransSource,
GETDATE(), CONVERT(Date, GetDate()), CONVERT(float, @RowNo), CONVERT(int, @RowNo), @JobTransNo, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ abcdefghijklmnopqrstuvwxyz'



        Insert Into tblJobDetail_UIX (JobNo, JobMonth, JobTransNo, TransSource, SmallMoneyField, CharField, DateTimeField, DateField, FloatField, IntField, SmallIntField, VarcharField)

        Select @JobNo, @JobMonth, @JobTransNo, @TransSource, @RowNo, @TransSource,
GETDATE(), CONVERT(Date, GetDate()), CONVERT(float, @RowNo), CONVERT(int, @RowNo), @JobTransNo, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ abcdefghijklmnopqrstuvwxyz'



        Insert Into tblJobDetail_NOIX (JobNo, JobMonth, JobTransNo, TransSource, SmallMoneyField, CharField, DateTimeField, DateField, FloatField, IntField, SmallIntField, VarcharField)

        Select @JobNo, @JobMonth, @JobTransNo, @TransSource, @RowNo, @TransSource,
GETDATE(), CONVERT(Date, GetDate()), CONVERT(float, @RowNo), CONVERT(int, @RowNo), @JobTransNo, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ abcdefghijklmnopqrstuvwxyz'



        Set @JobTransNo = @JobTransNo + 1

        Set @RowNo = @RowNo + 1

    End

  

    Set @JobTransNo = 1

    Set @JobMonth = DATEADD(Month, -1, @JobMonth)

End



Go

  

Run the routine five times and got 1,050,000 records.


After insert is being done in the table, the index space and data space for each table is as follow:

1. tblJobDetail_PK
   

   	Before Re-Organizing Index	After Re-Organizing Index	Variance
   Row Count	1,050,000	1,050,000
   Index Space	0.953 MB	0.953 MB
   Data Space	155.711 MB	147.008 MB	8.703 MB
   Page Fullness	91.89 %	96.78 %	-4.89 %
   Total Fragmentation	16.23 %	0.67 %	15.56 %
   Average Row Size	138	138
   Dept	3	3
   Leaf Level Rows	1,060,600	1,056,500	4,100
   Maximum Row Space	141	141
   Minimum Row Space	135	135
   Pages	19,931	18,817	1,114

2. tblJobDetail_IX
   

   	Before Re-Organizing Index	After Re-Organizing Index	Variance
   Row Count	1,050,000	1,050,000
   Index Space	1.086 MB	1.086 MB
   Data Space	159.133 MB	154.445 MB	4.688 MB
   Page Fullness	93.16 %	92.87 %	0.29 %
   Total Fragmentation	15.95 %	0.72 %	15.23 %
   Average Row Size	140	140
   Dept	3	3
   Leaf Level Rows	1,055,800	1,020,700	35,100
   Maximum Row Space	143	143
   Minimum Row Space	137	137
   Pages	20,369	19,769	600

   
    

1. tblJobDetail_NIX
   

   	Before Re-Organizing Index	After Re-Organizing Index	Variance
   Row Count	1,050,000	1,050,000
   Index Space	48.758 MB	40.109 MB	8.649 MB
   Data Space	148.063 MB	148.063 MB
   Page Fullness	67.05 %	81.66 %	-14.61 %
   Total Fragmentation	51.03 %	1.06 %	49.97 %
   Average Row Size	30	30
   Dept	3	3
   Leaf Level Rows	1,050,000	1,050,000
   Maximum Row Space	30	30
   Minimum Row Space	30	30
   Pages	6,189	5,082	1,107

   
    

1. tbl JobDetail_UIX
   

   	Before Re-Organizing Index	After Re-Organizing Index	Variance
   Row Count	1,050,000	1,050,000
   Index Space	1.063 MB	1.063 MB
   Data Space	155.711 MB	152.391 MB	3.32 MB
   Page Fullness	91.26 %	93.37 %	-2.11 %
   Total Fragmentation	16.31 %	0.72 %	15.59 %
   Average Row Size	138	138
   Dept	3	3
   Leaf Level Rows	1,043,900	1,051,900
   Maximum Row Space	141	141
   Minimum Row Space	135	135
   Pages	19,931	19,506	425

   
    

1. tbl JobDetail_PKIX
   

Clustered Index	Before Re-Organizing Index	After Re-Organizing Index	Variance
Row Count	1,050,000	1,050,000
Index Space	47.680 MB	47.680 MB
Data Space	155.711 MB	147.008 MB	8.703 MB
Page Fullness	91.89 %	96.78 %	-4.89 %
Total Fragmentation	16.22 %	0.67 %	15.55 %
Average Row Size	138	138
Dept	3	3
Leaf Level Rows	1,060,600	1,056,500	4,100
Maximum Row Space	141	141
Minimum Row Space	135	135
Pages	19,931	18,817	1,114

Non-Clustered Index	Before Re-Organizing Index	After Re-Organizing Index	Variance
Row Count	1,050,000	1,050,000
Index Space	47.680 MB	40.672 MB	7.008 MB
Data Space	155.711 MB	147.008 MB	8.703 MB
Page Fullness	70.35 %	82.89 %	-12.54 %
Total Fragmentation	53.23 %	1.05 %	52.18 %
Average Row Size	30	30
Dept	3	3
Leaf Level Rows	1,050,000	1,050,000
Maximum Row Space	33	33
Minimum Row Space	27	27
Pages	5,931	5,034	897

1. tbl JobDetail_NPKIX
   

Clustered Index	Before Re-Organizing Index	After Re-Organizing Index	Variance
Row Count	1,050,000	1,050,000
Index Space	47.680 MB	47.680 MB
Data Space	155.711 MB	147.008 MB	8.703 MB
Page Fullness	91.89 %	96.78 %	-4.89 %
Total Fragmentation	16.23 %	0.67 %	15.56 %
Average Row Size	138	138
Dept	3	3
Leaf Level Rows	1,054,900	1,050,800	4,100
Maximum Row Space	141	141
Minimum Row Space	135	135
Pages	19,931	18,817	1,114

Non-Clustered Index	Before Re-Organizing Index	After Re-Organizing Index	Variance
Row Count	1,050,000	1,050,000
Index Space	47.680 MB	40.883 MB	6.797 MB
Data Space	155.711 MB	147.008 MB	8.703 MB
Page Fullness	70.35 %	82.45 %	-12.1 %
Total Fragmentation	53.08 %	0.97 %	52.11 %
Average Row Size	30	30
Dept	3	3
Leaf Level Rows	1,050,000	1,050,000
Maximum Row Space	33	33
Minimum Row Space	27	27
Pages	5,931	5,061	870

1. tblJobDetail_NOIX
   

   	Before Re-Organizing Index
   Row Count	1,050,000
   Index Space	0 MB
   Data Space	148.063 MB
   Page Fullness	0 %
   Total Fragmentation	0 %
   Average Row Size	0
   Dept	0
   Leaf Level Rows	0
   Maximum Row Space	0
   Minimum Row Space	0
   Pages	0

   
    

In the next step, I am trying to do an update on all tables on same column. Here are the results:

      Update tblJobDetail_IX Set DateField = CONVERT(Date, JobMonth)

Update tblJobDetail_NIX Set DateField = CONVERT(Date, JobMonth)

Update tblJobDetail_NOIX Set DateField = CONVERT(Date, JobMonth)

Update tblJobDetail_NPKIX Set DateField = CONVERT(Date, JobMonth)

Update tblJobDetail_PK Set DateField = CONVERT(Date, JobMonth)

Update tblJobDetail_PKIX Set DateField = CONVERT(Date, JobMonth)

Update tblJobDetail_UIX Set DateField = CONVERT(Date, JobMonth)