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