Is there a way to add INCLUDE for a UNIQUE NONCLUSTERED index?

by

The question:

we have a table with 4 columns ( example ):

create TABLE [dbo].[myTable]
(
        [Id] BIGINT NOT NULL,
        [Id2] SMALLINT NOT NULL,
        [Id3] SMALLINT NOT NULL,
        [IdUnique] UNIQUEIDENTIFIER NOT NULL,
        [CreateDate] DATETIME NOT NULL,
        CONSTRAINT [PK_MyTable_Id_Id2_Id3] PRIMARY KEY CLUSTERED ([Id], [Id2], [Id3]),
        CONSTRAINT [UQ_MyTable_IdUnique] UNIQUE NONCLUSTERED ([IdUnique] ASC )  
)

The idea is to use IdUnique in the WHERE to retrieve ID,ID2 and ID3:

Is there a way to add INCLUDE for a UNIQUE NONCLUSTERED index?

but obviously we are having a keylookup.

How can I INCLUDE a include column on a unique nonclustered index?

I can’t find anything related to this.

The Solutions:

Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.

Method 1

Adding includes on constraints

An INCLUDE is not supported on CONSTRAINTS, either inline or during standalone creation. If you would like your CONSTRAINT to have an INCLUDE, you will need to create the CONSTRAINT as a UNIQUE INDEX instead.

However, as pointed out by Paul White, inline index creation with includes is not supported until SQL Server 2019. On SQL Server versions prior to 2019, the workaround would be to create a UNIQUE INDEX as a standalone statement.

Further, your query is using SELECT *, which means CreateData is also being returned. This is the column actually driving the Key Lookup. So, CreateDate would be the column you need to include. Otherwise, you’ll need to explicitly list only the three ID columns you want to select. If CreateDate is to be returned, the following index would help you avoid the Key Lookup.

Create an inline unique index with includes, SQL Server 2019 and above

CREATE TABLE [dbo].[myTable2]
(
    [Id] BIGINT NOT NULL,
    [Id2] SMALLINT NOT NULL,
    [Id3] SMALLINT NOT NULL,
    [IdUnique] UNIQUEIDENTIFIER NOT NULL,
    [CreateDate] DATETIME NOT NULL,
    CONSTRAINT [PK_MyTable_Id_Id2_Id3] PRIMARY KEY CLUSTERED ([Id], [Id2], [Id3]),
    INDEX [UQ_MyTable_IdUnique] UNIQUE NONCLUSTERED ([IdUnique] ASC) INCLUDE (CreateDate) 
)

Create a unique index with includes, prior to SQL Server 2019

CREATE UNIQUE NONCLUSTERED INDEX UQ_MyTable_IdUnique
    ON dbo.myTable (IdUnique)
    INCLUDE (CreateDate)

CREATE INDEX

Bigger picture

Is there a reason to be worried about this specific Key Lookup? Key Lookups are typically a bad thing in general, but that does not mean you always need to get rid of them every time you find them. In your case, you’re querying a table against a unique column. This should always return, at most, one row. Thus, at most, have one Key Lookup. I would not consider that a significant amount of overhead.

My point is, make sure you have a “real problem” here, before you go trying to fix it.

Method 2

Unique constraint indexes do not allow included columns. You need to create a regular unique nonclustered index instead to include columns.

That said, it is not necessary to include those columns because they are the clustered index key. The clustered index key is implicitly included in all non-clustered index leaf nodes for use as the row locator.

The only reason you see a key lookup in the plan is because of SELECT *, which requires CreateDate in addition the other columns of interest. Yet another reason to avoid it.


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Comment