Usage of NON Clustered Index in SQL Server

Question

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

Answer 1

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

Answer 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!