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



  • 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:

    enter image description here

    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 CreateDate in addition the other columns of interest. Yet another reason to avoid it.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2