Home Forums SQL Server 2008 SQL Server 2008 - General Index - included column RE: Index - included column
January 14, 2019 at 10:27 am
WhiteLotus - Thursday, January 10, 2019 4:53 PMHi All ,
I have tried 2 different approaches on changing the index structure
Current Index structure :
CREATE NONCLUSTERED INDEX [IX_ABC] ON [Table XX].[ColumnXX]
(
[ID] ASC
)
INCLUDE ( [ABCID],
[XYZID],
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
GOFirst approach :
I modified the current index by adding 2 columns as included column , it shows improvement in logical read (from 50.000 down to 750 ) on the table which contains those 2 columns
But on the other hand it shows the decrease of logical read from other table which participate in the query ( from 18000 to 28000)Second approach :
I deleted current index and created a new one with those 2 columns added as included column . it shows improvement in logical read (from 50.000 down to 750 )on the table which contains those 2 columns .
And shows the small decrease of logical read from other table which participate in the query ( from 18000 to 21000)Question :
Why second approach is better than the first one ?Thanks and appreciate your feedback.
Still around? Could we get the information we requested?