Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

database: primary key, Clustered or NonClustered

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?

like image 488
YtotheZ Avatar asked May 22 '12 17:05

YtotheZ


People also ask

Is primary key clustered or nonclustered?

By Default Primary Keys Of The Table is a Clustered Index. It can be used with unique constraint on the table which acts as a composite key. A clustered index can improve the performance of data retrieval.

Should a primary key be clustered?

Primary Key can be Clustered or Non-clustered but it is a common best practice to create a Primary Key as Clustered Index.

Is primary key a clustered index?

In SQL Server, the primary key constraint automatically creates a clustered index on that particular column. Notice here in the “student” table we have set primary key constraint on the “id” column. This automatically creates a clustered index on the “id” column.

Can primary key have non-clustered index?

Yes it can be non-clustered. However, it has to be unique. You can uncheckmark it the table designer. SQL Server creates a Clustered index by default whenever we create a primary key.


1 Answers

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);  
like image 157
Pedro Custódio Avatar answered Oct 03 '22 08:10

Pedro Custódio