How to create Non-clustered Covering index on PK while creating a table
1
vote
0
answers
31
views
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).
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 ?

Asked by Heta Desai
(21 rep)
Apr 23, 2020, 03:05 PM