2

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

  • The posession of Column1 values in all indexes is ordered. Whereas the posession of Column3 values in last 2 indexes is random. Remember - only whole index expression or its prefix part is ordered, tail is not. – Akina Jul 22 at 7:05
  • Hi Akina,can you explain in in more detail,I am not able to understand – user185981 Jul 22 at 7:08
  • 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... – Akina Jul 22 at 7:14
  • So for my second update ,i need to have my second index i cant delete it right?and also,then what is the point of adding columns in index ,just keep the column used in predicate rest all columns should be present in INCLUDE? – user185981 Jul 22 at 7:24
  • where Column3='' needs the index where column3 is the whole index expression or a prefix of index expression (in index by column3, column2, for example) - the records in the index will be sorted by column3 values in these cases only. If column3 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. – Akina Jul 22 at 7:30
2

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.

The updates will always work fine. Is this an update query you run very frequently?
Because in general (extra) non clustered indexes slow down inserts.

All your data is in the Clustered Index (CIX). The Non-Clustered Indexes (NCIX) are a copy of your Clustered Index, but in a order you specify generally in a different manner than your Clustered Index.

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

In the first query, your server has to look where "Column1=''". The most quick way to do that is to use the NCIX that is sorted on Column1 and than look up the record in the CIX (that is probably not).

In your second query, it uses the other index because it is sorted on Column3

Based on the Update I thought I can delete 2nd index and update the first one with

Unfortunately no, when you include the column it is not used in sorting.

CREATE NONCLUSTERED INDEX [IX_1] ON [dbo].[TableA]
(
[Column1] ASC,
[Column2] ASC,
[Column3] asc
)

In this NCIX it is sorted on last, so it is probably not sorted well enough for the server to quickly find the record.

Look at this example where we sort on Column1, Column2, Column3:

Product      | Category   | Price 
----------------------------------- 
Apple        | Fruit      | 1.10  
Apple pie    | Pastry     | 5.00  
Apple Iphone | Electronic | 899.00  
Apple Watch  | Electronic | 459.00  
Bananas      | Fruit      | 1.50
Pear         | Fruit      | 1.25  
Pineapple    | Fruit      | 2.00  

See how the price is all over the place? and even the second column in the "index" isn't "sorted"?
The server has to scan the whole index/table to find the right price.

That's why it recommends a new Index on just Column3. But in my honest opinion, unless you run this query frequently do not create an index for this, more indexes make inserts and updates take longer (because they all need the latest values).

  • Thanks.I have a question on this Then when creating index all the columns used in (SELECT or update ) should be under INCLUDE except the one used in Predicate? – user185981 Jul 22 at 8:02
  • @user185981 In described case the index acts as a compact variant of table. For SELECT server does not need to look the table body at all in that case, it will get all data needed from the index directly. – Akina Jul 22 at 8:07
  • so if I have query : SELECT a,b,c,d from tableA where e=e,Then which index is good for server and query.Create non clustered INDEX ix1 on tableA (e asc,a asc,b asc,c asc,d asc) OR CREATE NON CLUSTERED INDEX ix2 on table A(e asc) include(a ,b ,c ,d ) – user185981 Jul 22 at 8:11
  • @user185981 You just need to have e sorted, and then include the rest, that's correct and good thinking of you! – DrTrunks Bell Jul 22 at 8:30
  • 1
    Just based on discussion the order of columns in create has importance ,does order of columns in include also matters?if we have SELECT a,b,c,d from tableA where e=e then if we have index : create NON CLUSTERED INDEX IX_1 on tableA (e asc)include(d,c,b,a).will sql use it ? – user185981 Jul 23 at 5:18
2

Clustered Index : Help to store table data on on pages in sorted order according to key column. So you can say it's sorted table (clustered table).

Non-Clustered Index : Store key value on IAM (Index Allocation Map) pages along with row address, like page id and row offset, that helps query to find rows on data pages.

So you can say, after creating clustered index on HEAP or Clustered table you are two time storing value of the key column. One on data pages another on index pages.

Ex.

You have table

CREATE TABLE tbe_TableName
(
Column_1 [datatype],
Column_2 [datatype],
Column_3 [datatype],
Column_4 [datatype],
Column_5 [datatype]
Index IDX_Clus CLUSTERED (Column_1)
)

Column_1 has a clustered index IDX_Clus and a non-clustered index. So SQL Server will write value of the Column_1 two times, in data and index pages.

Now come to you question.

Executing first update statement writes value on two times in the database.

  1. In index pages of IX_2
  2. In the database pages of the clustered table

So optimizer finding key value with data address from IDX_1 and using key lookup to find the data page of clustered table and write given value into it.

Second update statement.

Almost similar to first statement, it'll also write value two time, on index and data pages.

Even you INCLUDE Column_3 into the index, same process will be followed.

Note: Creating more non-clustered index on the table occupy more space on disk and decrease write performance.

Including all columns in index or creating index on all columns of a table is like data duplicasy.

So, I would suggest you to go through Expensive Key Lookup by Brent and decide.

Thanks!

  • Thanks Rajesh ,a question on this ,in my second update if column3 is not in non clustered index ,sql ask me to create this and once i create this ,I cant see the way sql traveled to update Data Page for clustered index? – user185981 Jul 23 at 5:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.