Monday, June 20, 2011

Dropping Massive objects

For most of us could happen that we will be assign to a project or responsible for maintenance of database that has Tables, Views, or stored procedures which have been created as test or any other reason and we need to clean them up.

Here is a routine that will do the job based on Criteria. My sample is for Views but can be replaces for Stored Procedures or Tables.


 

Declare @RowNo Int, @RecordCount Int, @strSQL Varchar(Max), @ViewName Varchar(128), @SchemaName Varchar(128)


 

Declare @myViewInfo Table (ObjectId Int, ViewName Varchar(128), ViewParentId Int, SchemaId Int, SchemaName Varchar(128), RN Int)


 

Insert Into @myViewInfo

Select

    sysView.object_id As ObjectId,

    sysView.name As ViewName,

    sysView.parent_object_id As ViewParentId,

    sysView.schema_id As SchemaId,

    sysSchem.name As SchemaName,

    Row_Number() Over(Order
By sysView.object_id) As RN

From

    sys.views As sysView

    Inner Join sys.schemas As SysSchem On sysView.schema_id = SysSchem.schema_id

Where

    sysSchem.name = 'dbo'

    And sysView.name Like '%Test'


 

Set @RecordCount = @@ROWCOUNT

Set @RowNo = 1


 

While @RowNo <= @RecordCount

Begin

    Select @SchemaName = SchemaName, @Viewname = ViewName From @myViewInfo Where RN = @RowNo

    Set @strSQL = 'Drop View ' + @SchemaName + '.' + @ViewName

    Execute (@strSQL)

        

    Set @RowNo = @RowNo + 1

End


 

Wednesday, June 15, 2011

Identity


Most of the time IDENTITY property in tables is being used when we do not have specific candidate for our primary key. After this some problems could rise and one of them is "What If I want to rearrange my field, how I am going to do that?"
There could be three different approach take care of this issue:
  1. Create a new table and transfer the table to new table. Next, rename the old table to another name. Finally, rename the new table to the old table's original name.
  2. Alter the table and drop existing IDENTITY column. Next, add a new column as IDENTITY.
  3. Update the current IDENTITY column with appropriate values.
Thus, I am not going to discuss on how to do the first two choices and will try to brainstorm my idea on the third choice.
First, I will create a table with two columns which one of them would be IDENTITY column.
Create Table testIdentity
(
    IdentityID Int Not Null Identity(1, 1)
    ,IdentityDescription Varchar(10)
)


Next, I will try to INSERT some records into the table.
Insert
Into dbo.testIdentity (IdentityDescription)
Values ('ABC')
,('A')
,('AB')
,('ABCD')
,('ABCDE')
,('ABCDEF')
,('ABCDEFG')
,('ABCDEFGH')
,('ABCDEFGHI')


And check the records that have been inserted.
Select * From dbo.testIdentity --9 records has been added


In the next step I will try to DELETE some records from my table.
    Delete From dbo.testIdentity Where IdentityID In (3, 6)
-- two sample records has been deleted



My process will start from here. So far, everything was very simple and fine.
I will transfer all my data in the table to a local table variable and DELETE all data from table, INSERT them back again with correct sequence number.
Set Identity_Insert dbo.testIdentity On

Go
This will let to INSERT new values in the table with our desired ID.
In the next step,
Declare @myTempTable Table (RowNo Int, IdentityDescription Varchar(10))
-- Create a local variable table to transfer data
Insert Into @myTempTable
Select Row_Number() Over (Order by IdentityID) As RowNo, IdentityDescription From dbo.testIdentity

Delete From dbo.testIdentity --Delete all records from main table


So, I transfer all my data to local variable table and delete all of them from main table.
Insert Into dbo.testIdentity (IdentityId, IdentityDescription) --Insert from local table to main table
Select * From @myTempTable


I inserted all my data back into original table. So, next step would be to RESET the IDENTITY to the MAX of values.
Declare @RecordCount Int
Select @RecordCount = Max(RowNo) From @myTempTable
DBCC CheckIdent('dbo.testIdentity', Reseed, @RecordCount);


And turn off "Identity_Insert".
Set Identity_Insert dbo.testIdentity Off
Finally, for the test will try to INSERT and new record and see what would be my new ID.
Insert Into dbo.testIdentity (IdentityDescription) Values ('AB')


testIdentity

Tuesday, May 31, 2011

Calendar Table


One of the useful user created tables that would benefit in most of the queries is Calendar table.
It is easy to populate the data in it one time only and used it as many as possible.
This is one of the most common questions that being asked in Microsoft Forums as I seen.
I am creating the Calendar table with following fields in it.


use DBTest
Go
Create Table Calendar
(CalendarDate Date Primary key, IsWeekend Bit, YearNo SmallInt, QuarterNo TinyInt, MonthNo TinyInt, DayOfYearNo SmallInt, DayNo TinyInt, WeekNo TinyInt, WeekDayNo TinyInt)

Go


I am assigning the permissions to this table.


Grant Select On Object::dbo.Calendar to Public
Grant Insert On Object::dbo.Calendar to Public
Grant Update On Object::dbo.Calendar to Public
Grant Delete On Object::dbo.Calendar to Public
Go



Populate the data and insert it into Calendar table.


Declare @beginDate Date, @endDate Date
Select @beginDate = '01/01/1900', @endDate = '12/31/2030'


While @beginDate <= @endDate
Begin
    Insert Into dbo.Calendar (CalendarDate, IsWeekend, YearNo, QuarterNo, MonthNo, DayOfYearNo, DayNo, WeekNo, WeekDayNo)
    Select
        @beginDate As CalendarDate     
        ,(Case When DATEPART(Weekday, @beginDate) In (7, 1) Then 1 Else 0 End) As IsWeekend
        ,DATEPART(Year, @beginDate) As YearNo
        ,DATEPART(QUARTER, @beginDate) As QuarterNo
        ,DATEPART(MONTH, @beginDate) As MonthNo
        ,DATEPART(DayOfYear, @beginDate) As DayOfYearNo
        ,DATEPART(Day, @beginDate) As DayNo         
        ,DATEPART(Week, @beginDate) As WeekNo
        ,DATEPART(WEEKDAY, @beginDate) As WeekDayNo
    Set @beginDate = DateAdd(Day, 1, @beginDate)
End


Select CalendarDate, IsWeekend, YearNo, QuarterNo, MonthNo, DayOfYearNo, DayNo, WeekNo, WeekDayNo From dbo.Calendar


Sample of fetching data based on Year, Week Number And Week Day No and compare it simple select statement from Calendar table
Declare @myDate As Varchar(30)

Set @myDate = '2010-38-6' --(Year, WeekNo, WeekDayNo)


Declare @YearNo SmallInt, @WeekNo TinyInt, @DayNo TinyInt


Set @YearNo = Convert(SmallInt, Left(@myDate, 4))
Set @WeekNo = Convert(TinyInt, SubString(@myDate, PatIndex('%-%', @myDate) + 1, 2))
Set @DayNo = Convert(TinyInt, Right(@myDate, 1))


Select
    DateAdd(WK, @WeekNo - 1,
    DateAdd(Year, @YearNo - 1900, 0)) - 6 + @DayNo
--Or Easiest way
Select CalendarDate, IsWeekend, YearNo, QuarterNo, MonthNo, DayOfYearNo, DayNo, WeekNo, WeekDayNo
From dbo.Calendar
Where YearNo = @YearNo And WeekNo = @WeekNo And WeekDayNo = @DayNo


Checking the performance
Select
    CalendarDate
    ,IsWeekend
    ,YearNo
    ,QuarterNo
    ,MonthNo
    ,DayOfYearNo
From
    dbo.calendar
Where
    DATEPART(Year, CalendarDate) = 2011
    And DATEPART(Month, CalendarDate) = 6
    
Select
    CalendarDate
    ,IsWeekend
    ,YearNo
    ,QuarterNo
    ,MonthNo
    ,DayOfYearNo
From
    dbo.calendar
Where
    CalendarDate >= DATEADD(Month, 12 * DatePart(Year, GetDate()) - 22801 + DatePart(Month, GetDate()), 0)
    And CalendarDate < DateAdd(Month, 1, DATEADD(Month, 12 * DatePart(Year, GetDate()) - 22801 + DatePart(Month, GetDate()), 0))

CalendarPerformance

Monday, April 4, 2011

Change Data Capture and Track changes based on User

    One of tasks that a DBA has to do in the most of the companies is to answer the common question of "WHO CHANGED a TRSANACTION???". SQL Server 2008 R2 has new feature named CHANGE DATA CAPTURE (CDC).

This feature is only for track of changes that happening to a table, but it is NOT reporting the user who made the changes. So, in our company we thought about a workaround that will use all the features of CDC and will give us the user who made the changes.

 To accomplish this goal we are adding two fields in each table or any table that you would like to enable CDC on them. Those fields are "LastUpdatedBy" and "LastUpdatedDate". LastUpdatedBy will be user name and the other will track the date and time when a row has been changed. It could be an Insert, Update, or Delete transaction.

    In the first step, we will enable the CDC feature by using "sys.sp_cdc_enable_db". In the next step, I am using a cursor to enable CDC on all my tables. You can enable all tables and disable any CDC that does not need them by sp_cdc_disable_table. While I am enabling CDC on each table, I will add default to those columns as well.

    After you enable CDC on any table, you can find them under system tables. The naming on each table is as follow: cdc.[Schema Name]_[Table Name]_CT. So If you have table that it is under [dbo] schema and table name is [EmployeeInformation] you will see something like "cdc.dbo_EmployeeInformation_CT" under system tables. CDC will create same table structure for each table and will add 5 columns in front of the other columns.

      


  
 

 

EXEC

sys.sp_cdc_enable_db

Go

 

Create Default LastUpdatedBy_Def As SUser_Name()

Create Default LastUpdatedDate_Def As GetDate()

 

Go

 

Declare @tblObjectId Int, @tblName Varchar(128), @SchemaId Int, @SchemaName Varchar(128)

Declare @strSQL Varchar(2000)

 

Declare mySysCursor Cursor For

Select

SysTbls.OBJECT_ID As tblObjectId


,(SysTbls.name) As tblName


,SysTbls.schema_id As SchemaId


,(SysSchem.name) As SchemaName

From


sys.tables As SysTbls


Inner Join Sys.schemas As SysSchem On SysTbls.schema_id = SysSchem.schema_id

Where

SysTbls.type = 'U'


And SysTbls.SCHEMA_ID <> 12


And SysTbls.name Not In ('sysdiagrams',
'systranschemas')

Order By

SysTbls.schema_id


,SysTbls.OBJECT_ID


 

Open mySysCursor

Fetch Next From mySysCursor

Into @tblObjectId, @tblName, @SchemaId, @SchemaName

 


 

While
@@FETCH_STATUS = 0

Begin


 

    Set @strSQL = 'Alter Table ' + @SchemaName + '.' + @tblName

    Set @strSQL = @strSQL + ' Add LastUpdatedBy Int Null'

    Exec (@strSQL)

 

    Set @strSQL = 'Alter Table ' + @SchemaName + '.' + @tblName

    Set @strSQL = @strSQL + ' Add LastUpdatedDate DateTime Null'

    Exec (@strSQL)

 

    Set @strSQL = 'Update ' + @SchemaName + '.' + @tblName

    Set @strSQL = @strSQL + ' Set '

    Set @strSQL = @strSQL +
' , LastUpdatedBy = (Case When IsNull(LastUpdatedBy, '-') = '-' Then '-' Else LastUpdatedBy End)'

    Set @strSQL = @strSQL +
' , LastUpdatedDate = (Case When IsNull(LastUpdatedDate, ''01/01/1900'') = ''01/01/1900'' Then ''01/01/1900'' Else LastUpdatedDate End)'

    Set @strSQL = @strSQL +
' Where LastUpdatedBy Is Null Or LastUpdatedDate Is Null '

    Exec (@strSQL)

 
 

    Set @strSQL = 'Alter Table ' + @SchemaName + '.' + @tblName

    Set @strSQL = @strSQL + ' Alter Column LastUpdatedBy Int Not Null'

    Exec (@strSQL)

 

    Set @strSQL = 'Alter Table ' + @SchemaName + '.' + @tblName

    Set @strSQL = @strSQL + ' Alter Column LastUpdatedDate DateTime Not Null'

    Exec (@strSQL)

 
 

    Set @strSQL = 'EXEC sp_bindefault ''LastUpdatedBy_Def'', ''' + @SchemaName + '.' + @tblName + '.LastUpdatedBy'''

    Exec (@strSQL)

 
 

    Set @strSQL = 'EXEC sp_bindefault ''LastUpdatedDate_Def'', ''' + @SchemaName + '.' + @tblName + '.LastUpdatedDate'''

    Exec (@strSQL)

 

    Set @strSQL = 'EXECUTE sys.sp_cdc_enable_table '

    Set @strSQL = @strSQL + ' @source_schema = N''' + @SchemaName + ''''

    Set @strSQL = @strSQL + ' ,@source_name = N'''
+ @tblName +
''''

    Set @strSQL = @strSQL +
' ,@role_name = N''cdc_Admin''';

    Exec (@strSQL)

 

    Fetch Next From mySysCursor

    Into @tblObjectId, @tblName, @SchemaId, @SchemaName


 

End

 


 

Close mySysCursor

DeAllocate mySysCursor

  

 
 

  
 

GO

    In the next step, I will create and Audit table that will hold all the CDC tables extracted information in a more user friendly way. I will extract any changes from CDC tables and store them in my Audit table. Advantage of storing CDC information in an Audit table is that, I can store this information in a different server or database and free my CDC space from current database.

 
 

 CREATE TABLE [dbo].[Audit](

    [AuditID] [int] IDENTITY(1,1) NOT NULL,

    [AuditDate] [datetime] NOT NULL,

    [AuditType] [char](1) NOT NULL,

    [SchemaName] [varchar](50) NOT NULL,

    [TableName] [varchar](128) NOT NULL,

    [PrimaryKeyFields] [varchar](512) NOT NULL,

    [PrimaryKeyValues] [varchar](max) NOT NULL,

    [FieldName] [varchar](128) NOT NULL,

    [OldValue] [varchar](max) NULL,

    [NewValue] [varchar](max) NULL,

    [UserName] [varchar](255) NOT NULL

CONSTRAINT [PK_Audit] PRIMARY KEY CLUSTERED ([AuditID] ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON)ON [PRIMARY] )

ON [PRIMARY]

 
 

GO

    In the last step, I will collect all the information from CDC tables and populate those in my Audit table. CDC for each Insert and Delete is creating a single row, but for Update is creating two rows. The assign code for Delete is "1" and Insert is "2". In Update case as I mention there are two rows, one of them keeps Old Values (assign code is "3") and the other row is New Values with assign code of "4".

Every time this procedure is running it will catch all the changes that happened during the last 24 hours and store them in my Audit table. So, my job will not get millions of records each time is running and so not long time running job.

  
 

 
 

 

Declare @SchemaId Int, @SchemaName Varchar(128), @TableId Int, @TableName Varchar(128), @ColumnId Int, @ColumnName Varchar(128)

Declare @strSQL Varchar(4000), @CDCTableLoopNo SmallInt, @CountCDCTables SmallInt

Declare @CDCTableName Varchar(128), @CDCSchemaName Varchar(128), @CDCCaptureInstance Varchar(128)

Declare @PrimaryKeyFields Varchar(Max)

 
  

Set @CDCTableLoopNo = 0

 
  

Declare @CDCTables Table (CDCObjectId Int, TableObjectId Int, Capture_Instance Varchar(128), Role_Name Varchar(128), Index_Name Varchar(128), TableName Varchar(128), SchemaId Int, SchemaName Varchar(128), RN SmallInt)

Insert Into @CDCTables

Select

    CDC_CT.Object_Id As CDCObjectId

    ,CDC_CT.Source_Object_Id As TableObjectId

    ,QuoteName(CDC_CT.Capture_Instance +'_CT') As Capture_Instance

    ,CDC_CT.Role_Name

    ,CDC_CT.Index_Name

    ,QuoteName(SysTbl.name) As TableName

    ,SysSchem.schema_id

    ,QuoteName(SysSchem.name) As SchemaName

    ,ROW_NUMBER() Over (Order By CDC_CT.Object_Id) As RN

From

    CDC.change_tables CDC_CT

    Inner Join Sys.tables As SysTbl On CDC_CT.Source_Object_Id = SysTbl.object_id

    Inner Join Sys.schemas As SysSchem On SysTbl.schema_id = SysSchem.schema_id

    
  

Set @CountCDCTables = @@ROWCOUNT

 
  

Declare @IndexColumns Table (TableObjectId Int, TableName Varchar(128), SchemaId Int, SchemaName Varchar(128), IndexId Int, ColumnId Int, ColumnName Varchar(128))

Insert Into @IndexColumns

Select

    sysTbls.object_id As TableObjectId

    ,QuoteName(sysTbls.name) As TableName

    ,SysSchem.schema_id As SchemaId

    ,QuoteName(SysSchem.name) As SchemaName

    ,SysIndex.index_id

    ,SysIndexCols.column_id

    ,QuoteName(SysCols.name) As ColumnName

From

    sys.tables As sysTbls

    Inner Join sys.schemas As SysSchem On sysTbls.schema_id = SysSchem.schema_id

    Inner Join sys.indexes As SysIndex On sysTbls.object_id = SysIndex.object_id

    Inner Join sys.index_columns As SysIndexCols On SysIndex.object_id = SysIndexCols.object_id And SysIndex.index_id = SysIndexCols.index_id

    Inner Join sys.columns As SysCols On SysIndexCols.column_id = SysCols.column_id And SysIndexCols.object_id = SysCols.object_id

Where

    SysIndex.type = 1

    And SysSchem.schema_id <> 12

Order By

    sysTbls.object_id

    
  

 
  

While @CDCTableLoopNo < @CountCDCTables

Begin

 
  

    Select @CDCTableName = '', @CDCSchemaName = '', @CDCCaptureInstance = '', @PrimaryKeyFields = ''


    Select @CDCTableName = TableName, @CDCSchemaName = SchemaName, @CDCCaptureInstance = Capture_Instance From @CDCTables Where RN = @CDCTableLoopNo + 1

    
  

    Select

     @PrimaryKeyFields = STUFF((Select ',' + SubQry.ColumnName From @IndexColumns As SubQry Where SubQry.TableObjectId = MainQry.TableObjectId And SubQry.SchemaId = MainQry.SchemaId For XML Path('')), 1, 1, '')

    From

        @IndexColumns As MainQry

    Where

        MainQry.SchemaName = @CDCSchemaName

        And MainQry.TableName = @CDCTableName

    Group By

        MainQry.SchemaId

        ,MainQry.TableObjectId

        ,MainQry.TableName

        
  

    If @PrimaryKeyFields = ''

    Begin

        Set @PrimaryKeyFields = '''-'''

    End

    
  

    Declare mySysCursor Cursor For

    Select

        SysSchem.schema_id As SchemaId

        ,QuoteName(SysSchem.name) As SchemaName

        ,SysTbls.object_id As TableId

        ,QuoteName(SysTbls.name) As TableName

        ,SysCols.column_id As ColumnId

        ,QuoteName(SysCols.name) As ColumnName

    From

        sys.schemas As SysSchem

        Inner Join sys.tables As SysTbls On SysSchem.schema_id = SysTbls.schema_id

        Inner Join Sys.columns As SysCols On SysTbls.object_id = SysCols.object_id

    Where

        QuoteName(SysTbls.name) = @CDCTableName

        And QuoteName(SysSchem.name) = @CDCSchemaName

        And SysCols.name Not In ('LastUpdatedBy', 'LastUpdatedDate')

    
  

    Open mySysCursor;     

Fetch Next From mySysCursor

    Into @SchemaId, @SchemaName, @TableId, @TableName, @ColumnId, @ColumnName

 
  

    While @@FETCH_STATUS = 0

    Begin

        Set @strSQL = '

        Insert Into Audit (AuditDate, AuditType, SchemaName, TableName, PrimaryKeyFields, PrimaryKeyValues, FieldName, OldValue, NewValue, UserName)

        Select

            Convert(DateTime, TimeMapping.tran_end_time, 101) As AuditDate

            ,(Case MAX(CDC_Tbl.__$operation) When 1 Then ' +'''D''' + ' When 2 Then ' + '''I''' + ' When 3 Then ' + '''U''' + ' When 4 Then ' + '''U''' + ' End) As AuditType

            ,('''+ @SchemaName + ''') As SchemaName

            ,(''' + @TableName + ''') As TableName

        '

            If @PrimaryKeyFields = '''-'''

            Begin

                Set @strSQL = @strSQL + '    ,''-'' As PrimaryKeyFields'

                Set @strSQL = @strSQL + '    ,''-'' As PrimaryKeyValues'

            End

            Else

            Begin

                Set @strSQL = @strSQL + '    ,''' + @PrimaryKeyFields + ''' As PrimaryKeyFields'

                Set @strSQL = @strSQL + ',(Select Stuff((Select Distinct '','' + Convert(Varchar(Max), SubQry.' + @PrimaryKeyFields + ') From cdc.' + @CDCCaptureInstance + ' As SubQry Where SubQry.__$start_lsn = CDC_Tbl.__$start_lsn And SubQry.__$seqval = DC_Tbl.__$seqval For XML Path('''')), 1, 1, '''')) As PrimaryKeyValues '

            End

        Set @strSQL = @strSQL +

        '            

            ,('''+ @ColumnName + ''') As FieldName

            ,Max(Case CDC_Tbl.__$operation When 2 Then '''' When 3 Then Convert(Varchar(Max), '+ @ColumnName + ') When 4 Then '''' When 1 Then Convert(Varchar(Max), ' + @ColumnName + ') End) As OldValue

            ,Max(Case CDC_Tbl.__$operation When 2 Then Convert(Varchar(Max), '+ @ColumnName +') When 3 Then '''' When 4 Then Convert(Varchar(Max), '+ @ColumnName + ') When 1 Then '''' End) As NewValue             

            ,Max(Case CDC_Tbl.__$operation When 2 Then LastUpdatedBy When 3 Then ''-'' When 4 Then LastUpdatedBy When 1 Then LastUpdatedBy End) As UserName

        From

            cdc.'+ @CDCCaptureInstance + ' As CDC_Tbl

            Left Outer Join cdc.lsn_time_mapping As TimeMapping On CDC_Tbl.__$start_lsn = TimeMapping.start_lsn

        Where

            TimeMapping.tran_end_time Between DATEADD(Day, -1, GetDate()) And DATEADD(Day, 0, GetDate())

        Group By

            Convert(DateTime, TimeMapping.tran_end_time, 101)

            ,CDC_Tbl.__$start_lsn

            ,CDC_Tbl.__$seqval

            '

            If @PrimaryKeyFields <> '''-'''

            Begin

             Set @strSQL = @strSQL + ',' + @PrimaryKeyFields + ''

            End

            Set @strSQL = @strSQL +
'            

        Having

            Max(Case CDC_Tbl.__$operation When 2 Then '''' When 3 Then Convert(Varchar(Max), '+ @ColumnName + ') When 4 Then '''' When 1 Then Convert(Varchar(Max), '+ @ColumnName +') End)

            <> Max(Case CDC_Tbl.__$operation When 2 Then Convert(Varchar(Max), '+ @ColumnName +') When 3 Then '''' When 4 Then Convert(Varchar(Max), '+ @ColumnName +') When 1 Then '''' End)

        Order By

            __$start_lsn

        '

        Exec (@strSQL)

         

        Fetch Next From mySysCursor

        Into @SchemaId, @SchemaName, @TableId, @TableName, @ColumnId, @ColumnName     
 

    End    

 
  

    Close mySysCursor

    DeAllocate mySysCursor

    
  

    Set @CDCTableLoopNo = @CDCTableLoopNo + 1     

End


 
  

Friday, October 22, 2010

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 ASCWith 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 ASCWith 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 ASCWith 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, @JobMonthAs CHAR(2)) Cast(DATEPART(Year, @JobMonthAs 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(DateGetDate()), 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(DateGetDate()), 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(DateGetDate()), 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(DateGetDate()), 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(DateGetDate()), 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(DateGetDate()), 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(DateGetDate()), 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)