All we need is an easy explanation of the problem, so here it is.
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:
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.
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
Adding includes on constraints
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)
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.
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.
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂