Is there a way to add INCLUDE for a UNIQUE NONCLUSTERED index?
jeanid last edited by
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.
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
CreateDatein addition the other columns of interest. Yet another reason to avoid it.