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 ?