Sql-server – Usage of NON Clustered Index in SQL Server

indexmonitoringperformancequery-performancesql serversql-server-2012

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

Best Answer

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).

Related Question