Question : Choose the wrong Answer

When We Create primary key on Table :

1- it Always Create ” Clustered index ”

2- It Always Create ” Non Clustered index ”

3- It Always Create ” Clustered and Non Clustered index ”

Answer :

The Wrong answer in number 3 primary key create one index on table (Clustered or Non Clustered ) Automatically .

When we created Primary key on any table this primary key Check the Table if this table have Non Clustered Index Automatic Create Clustered index Else if the table have Clustered index primary key Automatic Create Non Clustered index


Demo :


Create table Table_primaryKey


Pkey_ID int  Not Null,

Pkey_name Nvarchar(50) ,

Pkey_Lsn bigint


2- Check Index type on Table Name ” Table_PrimaryKey ” 

after the result Show we will check the Column Name ” typ_desc ” to know the type of the indexes on this table .

Note : Typ_desc = HEAP ( Meaning this Table not have ” CLUSTERED INDEX ” .


A- Check the index on table by Script

ISNULL(INDEXPROPERTY(OBJECT_ID(,,’IndexFillFactor’),0) [fill_factor],
create_date, modify_date, ind.type_desc,
fill_factor, has_filter,
ISNULL(INDEXPROPERTY(OBJECT_ID(,,’IndexDepth’),0) [IndexDepth],
ISNULL(INDEXPROPERTY(OBJECT_ID(,,’IsAutoStatistics’),0) [IsAutoStatistics],
ISNULL(INDEXPROPERTY(OBJECT_ID(,,’IsStatistics’),0) [IsStatistics],
ISNULL(INDEXPROPERTY(OBJECT_ID(,,’IsClustered’),0) [IsClustered]
FROM sys.objects obj
INNER JOIN sys.indexes ind
ON obj.object_id = ind.object_id
WHERE obj.type = ‘U’
and = ‘Table_primaryKey’



now we see the result is HEAP  So we don’t have any clustered index or Non Clustered index also because the result not show any thing expected the HEAP

B -Check index table from SQL Server Management Studio 

Check Database >> Tables >> Indexes – you will not found any index

3- Create Primary Key

ALTER TABLE Table_primaryKey
ADD CONSTRAINT [PK_Table_primaryKey_Pkey_ID]

Primary key will Create Automatic Clustered index ” PK_Table_primaryKey_Pkey_ID ” on the table

Clustered index

4- Drop the Primary Key

ALTER TABLE Table_primaryKey
DROP CONSTRAINT [PK_Table_primaryKey_Pkey_ID]

Now the Clustered index Dropped

Check Database >> Tables >> Indexes – No indexes

5- Create Clustered Index

[CL_Table_primaryKey_Pkey_ID] ON Table_primaryKey
( Pkey_ID ASC)

Check Database >> Tables >> Indexes – Index Name (CL_Table_primaryKey_Pkey_ID) Clustered Index

6- Create Primary Kay again (Check point No 3 )

Check Database >> Tables >> Indexes – PK_Table_primaryKey_Pkey_ID (NONCLUSTERED)


Now we now if the table have Clustered iindex and you create Primary key it will Create automatic Non-clustered Index

7- drop the primary key again (check point No 4)

Check Database >> Tables >> Indexes – Index Name PK_Table_primaryKey_Pkey_ID (NONCLUSTERED) DELETED

8- Create Non-Clustered Index 

9- Create primary Key

Check Database >> Tables >> Indexes -you will find New Non Clustered index now we have 2 Non-Clustered Index

10- drop All index in table 

Check Database >> Tables >> Indexes >>> Delete one by one

11 -drop the primary key again (check point No 4)

Note : Now i i Create New primary key it will Create Automatic New Clustered Index but if you need to Create Primary Key With Non-Clustered index .

12- Create Primary Key With Non-Clustered Index

ALTER TABLE Table_primaryKey
ADD CONSTRAINT [PK_Table_primaryKey_Pkey_ID]

13 – Check the index on Table 

Primary Key With NonClustered

Primary Key Can Create Non-Clustered Index When we don’t have Clustered index in on Table .

I Wish Success To Everyone

Eng. Mostafa Elmasry

Database Administrator

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.