Indexes and Keys

Question

Creating a primary key automatically creates clustered index. What if the primary key is created on 2 columns? Will it create clustered index on 2 columns?
Non-clustered index doesn't sort?
I am asking because I have read mixed and multiple answers and I am confused. So, please confirm.

Answer 1

When you create a composite primary key with 2 columns, it will create a single clustered index that uses both column values as clustering key.

Hope this helps

Answer 2

SQL Server creates a unique index on the primary key columns. This index includes all key columns in the case of a composite key.

You have the choice regarding whether the primary key index is clustered or non-clustered. SQL Server will create the primary key as clustered by default unless a clustered index already exists on the table. Although clustered is often the best choice for the clustered index (a table can have only one clustered index), you can explicitly specify non-clustered for the primary key index when a different clustered index is more beneficial to overall query performance.

Non-clustered index doesn't sort?

The leaf nodes of all b-tree indexes (clustered and non-clustered) are ordered by the index key(s). Clustered index leaf nodes are the actual table rows so those have all columns in the table. This allows the clustered index can be scanned to provide ordering by the clustered index key without the need for additional key lookups in the execution plan to retrieve additional columns required by a query.

Answer 3

Hi @Chaitanya Kiran ，

A primary key that is made up of 2 or more columns is called a composite primary key, it is just like a primary key on a single comlumn, all the rules still apply.
Create a composite key on table columm like:

create table table_name (  
column1 datatype1   ,  
column1 datatype1  ,  
column1 datatype1  , ..  
primary key (column_name1, column_name2,..)   

-------------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

Creating a primary key automatically creates clustered index

A PK as CI is default, but you can create the PK-index as non clustered index as well and the index can be composed over more then one column.

Non-clustered index doesn't sort?

A index is no garantuee for sorting, only ORDER BY clause do.