0

I am new to sql server. i was trying to learn creating index. i tried to create a table with below query:

CREATE TABLE [Ref].[Country](
    [CountryId] [uniqueidentifier]  NOT NULL,
    [CountryName] [varchar](50) NOT NULL,
    [CreatedDate] [datetime2](2) NOT NULL,
    [ModifiedDate] [datetime2](2) NULL,
    CONSTRAINT [UCI_CountryName] UNIQUE CLUSTERED ([CountryName] ASC),
    CONSTRAINT [PK_Country] PRIMARY KEY ([CountryId])
)

The non clustered index got created automatically on primary key. Now, i want to create NCI on Primary key column with INCLUDE. I did create it with below query:

CREATE UNIQUE NONCLUSTERED INDEX [NCI_CountryId] ON [Ref].[Country]
(
    [CountryId] ASC
)
INCLUDE ([CountryName])

As you can see in below image, there is two NCI on same column that is CountryId, one is created by default while creating a table(PK_Country) and another which i created with INCLUDE using above query(NCI_CountryId).

I do not want two NCI's on same column, need only NCI created with INCLUDE. Is there a way to create NCI with include on PK at time of creating table ?

I'm working on sql server on Azure VM.

enter image description here

1
2

With this:

CREATE TABLE [Ref].[Country](
    [CountryId] [uniqueidentifier]  NOT NULL,
    [CountryName] [varchar](50) NOT NULL,
    [CreatedDate] [datetime2](2) NOT NULL,
    [ModifiedDate] [datetime2](2) NULL,
    CONSTRAINT [UCI_CountryName] UNIQUE CLUSTERED ([CountryName] ASC),
    CONSTRAINT [PK_Country] PRIMARY KEY ([CountryId])
)

i want to create NCI on Primary key column with INCLUDE.

You already have it. A non-clustered index always contains the "row locator", which for a table with a clustered index is the clustered index key. The leaf level of the non-clustered PK with have (CountryId,CountryName) rows, but sorted by CountryId, whereas in the clustered index the rows are sorted by CountryName.

For a unique index the clustered index columns are stored as "included columns" only on the leaf level. For a non-unique index the clustered index columns are added as trailing index keys.

But you cannot include additional columns in a Primary Key constraint. You can implement the identical physical design by adding a unique, non-clustered index with included columns.

Also there's nothing wrong with using a UNIQUEIDENTIFIER for the clustered index key, so long as your table isn't large, or you generate the value with NEWSEQUENTIALID().

5
  • 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, CONSTRAINT [UCI_UserName] UNIQUE CLUSTERED ([UserName] ASC), CONSTRAINT [PK_User] PRIMARY KEY ([UserRegisteredId]) ) How to create NCI on PK with INCLUDE(FirstName, LastName) while creating table ?
    – Heta Desai
    Apr 23 '20 at 14:50
  • See update to answer. Apr 23 '20 at 17:00
  • 1
    do you mean CountryName is already included in NCI creted on PK CountryId ? i don't need to create another NCI on PK CountryId with INCLUDE(CountryName) ? correct me if i'm wrong
    – Heta Desai
    Apr 24 '20 at 9:15
  • Yes. The clustered index key columns are always included. Apr 24 '20 at 10:43

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.