If a table has 'id' column as it's clustered index, is there any benefit in adding 'id' column as an included column in any other non-clustered index

Question

If a table has 'id' (the primary key) column as its clustered index, is there any benefit in adding 'id' column as an included column in any other non-clustered index in Microsoft SQLServer?

eg:- Table 'xyz'

id	name	status	date
1	abc	active	2021-06-23

CREATE NONCLUSTERED INDEX [NonClusteredIndex_status_Date]
                ON [xyz]
                    (   
                        [status] ASC,
                        [date] ASC
                    )
                INCLUDE
                    (   [id],
                        [name]
                    )

And this non-clustered index is targeted for a query similar to bellow on a large data set. In the actual case there could be some other queries as well.

select * from xyz where status='active' and date > '2021-06-20'

Unlike some other databases, SQL Server does not (AFAIK) tag on the clustered index column to the end of every non clustered index. But, your question is missing an important point: What is the query you are trying to optimize here. That will determine whether or not the above index makes any sense. — Tim Biegeleisen, Jun 23 '21 at 07:08
It would be for a query like "select * from xyz where status='active' and date > '2021-06-20' " for a large data set. — Priyal, Jun 23 '21 at 07:11
@Tim - the last time it was true that NC indexes ended with a "row pointer" rather than the clustered index column values was, IIRC, SQL Server 7.0, so last century. — Damien_The_Unbeliever, Jun 23 '21 at 07:20
@Tim - see this [article](https://techcommunity.microsoft.com/t5/sql-server/what-happens-to-non-clustered-indexes-when-the-table-structure/ba-p/383292) which discusses "new" behaviour in SQL Server 2000 when changing between a heap and clustered index. — Damien_The_Unbeliever, Jun 23 '21 at 07:26

score 3 · Answer 1 · answered Jun 23 '21 at 08:00

The answer to your question is essentially No, there is no benefit.

When you create a non-clustered index on a table, each row in the index needs to be able to point to the row in the base table.

If the base table is a heap, each row in the index will contain a pointer to the rid (row identifier) which is what SQL Server uses to uniquely identify each row.

When the table is defined with a clustered index, every non-clustered index will automatically contain the clustered index column(s) as keys to the row in the base table.

You can see this in an execution plan, where a non-clustered index is used and SQL Server has to retrieve additional columns from the base table; if the table is a heap, it will be an RID lookup, if the table has a clustered index it will be a Key lookup.

Additionally, if the clustered index is not unique, SQL Server adds its own uniquifier value to ensure uniqueness, and this is also included in non-clustered indexes.

So when it comes to non-clustered indexes, it does not matter if you specify the clustered index column(s) - you can, and there is no harm in doing so, but it/they are always included.

While that's true for the 'name' column @TimBiegeleisen, the question as titled refers to including the clustered key `id` in a non-clustered included column; covering (no pun intended) every aspect of indexing and use-cases with dependencies on data cardinality, statistics, estimates, available resources etc all affecting the chosen execution plan can quickly become a very large digression! — Stu, Jun 23 '21 at 10:42

Tim Biegeleisen · Accepted Answer · 2021-06-23T07:52:54.697

This answer assumes you are planning to run the following query:

SELECT * FROM xyz WHERE status = 'active' AND date > '2021-06-20';

If you only created a non clustered index on (status, date), then it would cover the WHERE clause, but not the SELECT clause. What this means is that SQL Server might choose to use the index to find the matching records in the query. But when it gets to evaluating the SELECT clause, it would be forced to seek back to the clustered index to find the values for the columns not included in the index, other than the id clustered index column (which includes the name column in this case). There is performance penalty in doing this, and SQL Server might, depending on your data, even choose to not use the index because it does not completely cover the query.

To mitigate this, you can define the index in your question, where you include the name value in the leaf nodes. Note that id will be included by default in the index, so we do not need to explicitly INCLUDE it. By following this approach, the index itself is said to completely cover the query, meaning that SQL Server may use the index for the entire query plan. This can lead to fast performance in many cases.

As id column is clustered index, it is by default added as included column in every non-clustered index, if we specify or not, as it needs clustered index to traverse back to main table for other columns in select query, if needed. — Venkataraman R, Jun 23 '21 at 07:49
@Venkata Answer has been updated per your helpful information. — Tim Biegeleisen, Jun 23 '21 at 07:55
Yes, I see the benefit on include as it's avoids the key look up operations in the execution plan and directly fulfil the query through index itself without going back to table. My only doubt was in including the id column in the non-clustered index as id was anyway part of any non-clustered index. Thanks, this clears me out. — Priyal, Jun 23 '21 at 09:00

If a table has 'id' column as it's clustered index, is there any benefit in adding 'id' column as an included column in any other non-clustered index

2 Answers2