SQL Server performance: columns with a non-clustered index + INCLUDE versus clustered index - equivalent?

Question

Hi, experts from SQL Server Engine; please share with us a little of your insight ...

As I understand it, INCLUDE columns in a non-clustered index allow you to store additional fuzzy data with index pages.

I am well aware of the performance benefits of a clustered index on a non-clustered index simply because of the first step that the engine must perform in order to search in order to get data on disk.

However, since INCLUDE columns live in a non-clustered index, it can be expected that the next query will have the same performance in scenarios 1 and 2, since all columns can be retrieved from index pages in script 2, and not ever resort to table data pages ?

QUERY

SELECT A, B, C FROM TBL ORDER BY A 

SCENARIO 1

 CREATE CLUSTERED INDEX IX1 ON TBL (A, B, C); 

SCENARIO 2

 CREATED NONCLUSTERED INDEX IX1 ON TBL (A) INCLUDE (B, C); 

Answer 1

In this example, you can get better performance with a non-clustered index. But it really depends on additional information that you did not provide. Here are a few thoughts.

SQL Server stores information on 8K pages; this includes data and indexes. If your table contains only columns A, B, and C, the data will be stored on approximately the same number of data pages and non-clustered index pages. But, if you have more columns in the table, then the data will require more pages. The number of index pages will not be different.

So, in a table with more columns than your query requires, the query will work better with a non-clustered coverage index (index with all columns). He will be able to process fewer pages to return the desired results.

Of course, performance differences may not be observed until you get a very large number of rows.

Answer 2

Indeed, a non-clustered coated index includes columns that can play the same role as a clustered index. The cost is at the time of updating: more columns are included because more indexes need to be updated when the column value is included in the base table (in the clustered index). In addition, with more included columns, the size of the data increases: the database becomes larger, and this can complicate maintenance operations.

In the end, this is the balance that you should find between the coverage value of additional indexes and more included columns, as well as the cost of updating and increasing the size of the data.