Sample Header Ad - 728x90

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). 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 ?
Asked by Heta Desai (21 rep)
Apr 23, 2020, 03:05 PM