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.
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.
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.
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