1

I am trying to create a table where PK will not be a clustered index. PK will be non clustered with INCLUDE clause to add cover.

CREATE TABLE [Ref].[User](
    [UserRegisteredId] [varchar](100) NOT NULL,
    [UserName] [varchar](500) NOT NULL,
    [FirstName] [varchar](500) NULL,
    [LastName] [varchar](500) NULL,
    [PhoneNumber] [varchar](20) NULL,
    [Email] [nvarchar](500) NOT NULL,
    [CreatedDate] [datetime2](2) NOT NULL,
    [ModifiedDate] [datetime2](2) NULL,
    CONSTRAINT [UCI_UserName] UNIQUE CLUSTERED ([UserName] ASC),
    CONSTRAINT [PK_User] PRIMARY KEY ([UserRegisteredId])
)

as you can see above UserName is unique clustered index. Now I created a non-clustered index on PK with INCLUDE as below:

CREATE UNIQUE NONCLUSTERED INDEX NCI_UserId ON [Ref].[User] ([UserRegisteredId]) INCLUDE ([FirstName],[LastName])

After creating table and covering NCI on PK i noticed that there is already non-clustered index is created on PK automatically at the time table creation (Highlighted in attached image).

enter image description here

How can i avoid creation of two NCI on same column(PK) ? i need NCI which is created as covering index (NCI_UserId)

Is there any way that i can create covering non clustered index on PK while creating a table ?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.