When we Create Primary Key !!!

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 :

1- CREATE TABLE

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

SELECT obj.name,
ind.name,
ISNULL(INDEXPROPERTY(OBJECT_ID(obj.name),ind.name,’IndexFillFactor’),0) [fill_factor],
create_date, modify_date, ind.type_desc,
fill_factor, has_filter,
ISNULL(INDEXPROPERTY(OBJECT_ID(obj.name),ind.name,’IndexDepth’),0) [IndexDepth],
ISNULL(INDEXPROPERTY(OBJECT_ID(obj.name),ind.name,’IsAutoStatistics’),0) [IsAutoStatistics],
ISNULL(INDEXPROPERTY(OBJECT_ID(obj.name),ind.name,’IsStatistics’),0) [IsStatistics],
ISNULL(INDEXPROPERTY(OBJECT_ID(obj.name),ind.name,’IsUnique’),0) [IsUnique],
ISNULL(INDEXPROPERTY(OBJECT_ID(obj.name),ind.name,’IsClustered’),0) [IsClustered]
FROM sys.objects obj
INNER JOIN sys.indexes ind
ON obj.object_id = ind.object_id
WHERE obj.type = ‘U’
and obj.name = ‘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 (Pkey_ID ASC
)
GO

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

4- Drop the Primary Key

ALTER TABLE Table_primaryKey
DROP CONSTRAINT [PK_Table_primaryKey_Pkey_ID]
GO

Now the Clustered index Dropped

Check Database >> Tables >> Indexes – No indexes

5- Create Clustered Index

CREATE CLUSTERED INDEX
[CL_Table_primaryKey_Pkey_ID] ON Table_primaryKey
( Pkey_ID ASC)
GO

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]
PRIMARY KEY NONCLUSTERED (Pkey_ID ASC
)
GO

13 – Check the index on Table 

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