Creating Primary key without cluster index!!!

Is there a way to create Primary Key without Cluster Index

We all know, when we create a column with primary key, SQL Server automatically create cluster index on primary key field. I have been frequently asked by my friends as well as interviewer; Is it possible to create primary key field without cluster index?

Yes it is quite possible to create primary key field without cluster index in it. Let’s see

1. First we are creating a simple table without any constraint and will see whether any index exist

create table testIndex

(id int,

name varchar(20))

Command(s) completed successfully.

select OBJECT_NAME(object_id) as [Table], name as [IndexName],type_desc as [IndexType] from sys.indexes

where object_id = (select object_id from sys.objects where name = 'testIndex')

Table              IndexName   IndexType

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

testIndex           NULL       HEAP

(1 row(s) affected)

As we can see there is no index present in the above table

2. Now let’s try to create a table tableIndex2 with primary key and then will check for index present in the table.

create table testIndex2

(id int primary key,

name varchar(20))

select OBJECT_NAME(object_id) as [Table], name as [IndexName],type_desc as [IndexType] from sys.indexes

where object_id = (select object_id from sys.objects where name = 'testIndex2')

Table        IndexName                        IndexType

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

testIndex2   PK__testInde__3213E83F07F6335A   CLUSTERED

(1 row(s) affected)

3. Now we will be trying to create a primary key field without clustered index.

create table testIndex3

(id int primary key nonclustered,

name varchar(20))

select OBJECT_NAME(object_id) as [Table], name as [IndexName],type_desc as [IndexType] from sys.indexes

where object_id = (select object_id from sys.objects where name = 'testIndex3')

Table        IndexName                       IndexType

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

testIndex3   NULL                            HEAP

testIndex3   PK__testInde__3213E83E0BC6C43E  NONCLUSTERED

(2 row(s) affected)

Conclusion: So from this example we can see, that it is quite possible to create table with Primary Key without cluster index.

Put out of your mind and do code- it’s very effortless 

Post Reference: Vikram Aristocratic Elfin Share