database: primary key, Clustered or NonClustered

Question

I am creating a database in SQL server 2008,

CREATE TABLE Users
(
    U_Id INT NOT NULL
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    Email VARCHAR(200)
    Password VARCHAR(50)
)

I want to make U_Id the primary key. I would like to ask what is the difference between

 CONSTRAINT pk_UserID PRIMARY KEY (U_Id)

this

 CONSTRAINT pk_UserID PRIMARY KEY CLUSTERED (U_Id)

and this

CONSTRAINT pk_UserID PRIMARY KEY NONCLUSTERED (U_Id)

When to use each?

I read some article but it is still unclear to me. Can someone give me a quick explanation?

Solution

The following statement:

CONSTRAINT pk_UserID PRIMARY KEY (U_Id)

Is the same as this one:

CONSTRAINT pk_UserID PRIMARY KEY CLUSTERED (U_Id)

You can only have the table data physicality ordered by one of the indexes, and by default that index is the one used for the primary key (the primary key unique constraint is always supported by an index).

If you want to leave the order of the table data to be stored according to some other index then you should create the primary key with:

CONSTRAINT pk_UserID PRIMARY KEY NONCLUSTERED (U_Id)

And then create the clustered index with:

CREATE CLUSTERED INDEX ix_Email ON Users (Email); 

Comments

[Granger]

Just be aware that any non-clustered indexes you create will always include the data in the clustered-index (because that's how the actual data row in the table is found). Microsoft docs So don't think you'll be saving space by making a large/wide index be the clustered-index unless you don't have any other indexes on the table.

[brianfeucht]

Primary Key is the identifier of a row. It must be unique and not null. Clustered Index tells SQL how to store data on disk. The actual data rows are stored in the order as specified by this index. Since it also contains data on where the data is stored on disk the clustered index key is stored in every index. In almost all cases it is best to make these be the same. And it is almost always best to have the primary key be a identifier so that data is stored on disk sequentially.