I have 2 Index:
CREATE NONCLUSTERED INDEX [IX_1] ON [dbo].[TableA]
(
[Column1] ASC,
[Column2] ASC
)
CREATE NONCLUSTERED INDEX [IX_2] ON [dbo].[TableA]
(
[Column3] asc
)
Now I have 2 update:
Update TableA SET Column3='' where Column1=''
the above Update was using Index 1st and then Key lookup to clustered Index
Update TableA SET Column3='' where Column3=''
This update was using 2nd index
Based on the Update I thought I can delete 2nd index and update the first one with
CREATE NONCLUSTERED INDEX [IX_1] ON [dbo].[TableA]
(
[Column1] ASC,
[Column2] ASC
)
INCLUDE(Column3 asc)
So with the updated index the First Update works fine,it uses the updated Non Clustered Index , but for second update it ask me to create a non clustered index on column3.
Then I tried
CREATE NONCLUSTERED INDEX [IX_1] ON [dbo].[TableA]
(
[Column1] ASC,
[Column2] ASC,
[Column3] asc
)
But still for second update it ask me to create a new non clustered index.
But why it ask me to create new, because what I understand is Column1
, Column2
, Column3
will be present in Root level for non clustered Index
Column1
values in all indexes is ordered. Whereas the posession ofColumn3
values in last 2 indexes is random. Remember - only whole index expression or its prefix part is ordered, tail is not.Column2
is now excess, let's it out. Now look - we have the data like (f1,f2)=(3,2),(2,3),(1,1). And we create index (f1,f2). The records in the index will posess in the next order: (1,1),(2,3),(3,2). As you can see, f1 is ordered, 1-2-3, and we can easily find the value using binary search. But f2 values are not ordered, they have random order 1-3-2, so we must scan all of them for to find some particular value. If we create (f2,f1) index, the records will have the next order in it: (1,1),(3,2),(2,3) - f2 is ordered, but now f1 is random...where Column3=''
needs the index wherecolumn3
is the whole index expression or a prefix of index expression (in index bycolumn3, column2
, for example) - the records in the index will be sorted bycolumn3
values in these cases only. Ifcolumn3
is the middle/tail part of index expression the values of this column are not sorted within the whole data array, they are sorted only in small groups for which the perfix part is the same.