What is the purpose of a non clustered index without fields?

Question

The DB of a legacy application I recently started maintaining has 2 indexes whose scope I do not understand.

The table on which the indexes are created is like this (it is a table that stores links between the SW objects (it allows for example to link a Customer and 2 Orders, so a relationship between a Customer and 2 orders is established):

CREATE TABLE [LINKS](
    [LINK_ID] [int] NOT NULL, -- Counter Primary Key
    [LINK_TYPE] [int] NOT NULL,-- Foreign Key to "link types" table
    [ID_LINK_FROM] [int] NOT NULL, -- this is not FK, just an int
    [ID_LINK_TO] [int] NOT NULL, -- this is not FK, just an int
    [ID_LINK_TO_DETAIL] [int] NULL, -- this is not FK, just an int
    [HAS_PRIORITY] [nchar](1) NOT NULL -- this is 'Y'/'N' "Boolean-like"
)

the table has a Counter PK and three integer fields that are in fact missing FK, because depending on LINK_TYPE the destination is a different table. Of course the complete solution would be a DB normalization, but this is not an option now.

This table clearly introduces performance issues in all the queries that use it in a JOIN. To try to solve this problem somebody in the past created 2 (useless from my point of view) indexes:

CREATE NONCLUSTERED INDEX [IDX_LINKS_ID_LINK_FROM] ON [dbo].[LINKS]
(
    [ID_LINK_FROM] ASC
)

and

CREATE NONCLUSTERED INDEX [IDX_LINKS_ID_LINK_TO] ON [dbo].[LINKS]
(
    [ID_LINK_TO] ASC
)

Those indexes contain just the "missing FK" so they are useless,because the missing FK are for sure used for JOIN but then any other field is missing from the index.

So my "reverse engineering" question is: do you confirm these 2 indexes are useless?

To my knowledge the following 2 would be a better "patch" for the lack of DB normalization:

CREATE NONCLUSTERED INDEX [IDX_LINKS_ID_LINK_FROM] ON [dbo].[LINKS]
(
    [ID_LINK_FROM] ASC
)
INCLUDE ( LINK_TYPE,
ID_LINK_TO,
ID_LINK_TO_DETAIL,
HAS_PRIORITY)

and

CREATE NONCLUSTERED INDEX [IDX_LINKS_ID_LINK_TO] ON [dbo].[LINKS]
(
    [ID_LINK_TO] ASC
)
INCLUDE ( LINK_TYPE,
ID_LINK_FROM,
ID_LINK_TO_DETAIL,
HAS_PRIORITY)

At least the last 2 indexes contain all the fields I could need in a query so they are much better.

Can you please confirm I am on the correct path?

From first measurements I did on some "significant queries" I see an improvement, but I'd like to have some feedback before continuing on this path.

Answer 1

There are more uses for indexes than just "covering" indices, which is what your proposed ones are.

The optimizer may still decide that an index seek/scan on a narrow index followed by bookmark lookups in the clustered index is more efficient than a table scan. It's sometimes (but by no means always) right in making that decision.

Bear in mind that INCLUDE didn't exist before SQL Server 2008. Non-clustered indexes date far further back than that.

As with all indexing decisions, only you are in the right place to determine all of the querying/modification patterns on your data and able to test proposed changes to indexing. Make sure you do always profile a representative sample of queries, not just the one that you think will specifically be sped up by a particular index change.

---

Use the book analogy if it helps clear things up for you. Imagine a textbook about some subject. That book will be arranged by sub-subjects into chapters and those chapters will follow some logical ordering. The table of contents at the front of the book will tell you what order the sub-subjects are dealt with and allow you to jump to the chapter that's of interest, if you're clear on which sub-subject you need to know about.

Main book contents (leaves) + Table of contents (non-leaves) make up the clustered index.

But the book may also have one or more actual indexes, usually located at the back of the book. These will allow you to perform a search narrowly tailored by, say, keywords, and will tell you which page(s) of the book to go to which mention that keyword. You still have to go to the main contents of the book to get the information you need.

book indices = non-clustered indices. Locating keyword in index = index seek/scan. Referencing pages in contents = bookmark lookup.

A Non-Clustered index with INCLUDE columns is a bit tricky to shoe-horn into this analogy, but picture e.g. a "glossary" that may group several terms together, expand out their definitions in more detail and has "see also" references that are again references to pages within the main book's contents.

Answer 2

Use a query I usually use to find out whether indexes of a table are useful or not. Check it with your current indexes and then with the new one:

SELECT i.name, s.*
FROM sys.dm_db_index_usage_stats s
    JOIN sys.indexes i ON i.object_id = s.object_id
        AND i.index_id = s.index_id
    JOIN sys.sysindexes si ON si.id = i.object_id
        AND si.indid = i.index_id
WHERE s.object_id = OBJECT_ID('LINKS')
ORDER BY i.name