Sql-server – difference between create constraint non clustered and create index non clustered

clustered-indexnonclustered-indexsql server

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

Best Answer

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().

Related Question