Can we create clustered index on non primary key column

Categories >> Software >> Databases >> SQL Server
Suggest New Category

Question Posted / dinesh gupta

20 Answers
49126 Views
ABC, CTS, I also Faced
E-Mail Answers

Answers were Sorted based on User's Feedback

Can we create clustered index on non primary key column..

Answer / venkat

yes we can create clustured index on non primary key,
But that column has unique values.

Is This Answer Correct ?

1 Yes

0 No

Can we create clustered index on non primary key column..

Answer / grish varshney

yes, we can create the cluster index on non prime attribute
if it is unique & there is no prime attribute bcoz the
cluster index should be once..

Is This Answer Correct ?

4 Yes

4 No

Can we create clustered index on non primary key column..

Answer / kaveri

Clustered index can be created in non primary key column
but the primary key column shoulb be created with non
clustered index. because by default primary key will have
clustered index. Each table should have only one clustered
index. So first we have to remove clustered index from
primary key column by creating it with non clustered index
and then create clustered index for non primary key column.

Note: The non primary key column should contain unique
values

Is This Answer Correct ?

0 Yes

0 No

Can we create clustered index on non primary key column..

Answer / vamsi krishna

Create table Testing(testingID int,testingdesc varchar(20))
Note: I am not defining TestingID as primaryKey
create clustered index IX_TestingID on Testing(TestingID)
-- This will allow u to insert duplicate values to the
column that has clustered index created.

Instead run the below command to create unique Clustered
Index
create unique clustered index IX_TestingID on Testing
(TestingID)

-- The above statment will not allow u to add duplicate
records.
For your better conformation insert some records into the
tabel and execute a select statement having the clustered
index column in the where clause and check the execution
plan. You can see Index Seek.

Conclusion : Clustered index can be created on non
primaryKey column

Is This Answer Correct ?

0 Yes

0 No

Can we create clustered index on non primary key column..

Answer / madhav

yes we can create clusterd index on non primary key. but
the primary key must should be a non clusteresd index

Is This Answer Correct ?

0 Yes

0 No

Can we create clustered index on non primary key column..

Answer / leelavinoth

I have created CLUSTERED on NON-PRIMARY Attribute because i
want my table has to be ordered in NON-PRIMARY column.
Because, most on my search and filtering will be on this
NON-PRIMARY column.

Is there any harm in this! Please recommend

Is This Answer Correct ?

1 Yes

2 No

Can we create clustered index on non primary key column..

Answer / guest

untill unless primary creating on the table we can create
cl index on a table

Is This Answer Correct ?

3 Yes

13 No

Can we create clustered index on non primary key column..

Answer / durgaprasad

no its not possible to place clustered index on non key
column once the table contains any one key column, bcoz key
column itself contains one clustered index based on that
data sorted in ascending order.if u place one more
clustered index how it is possible to arrange the data in
soreted order.

Is This Answer Correct ?

15 Yes

29 No

Can we create clustered index on non primary key column..

Answer / gunjan sapra

no,we can't create clustered index on non primary key just
because of the simple reason that in clstered
index,physical sorting is done while in non primary key
there might be hundreds of duplicate entries. so, if we
create clusterd index on non primary key it will get
confused and then error.

Is This Answer Correct ?

34 Yes

49 No

Can we create clustered index on non primary key column..

Answer / selvaraj.v

No, Because Constraints must have integrity. These are,
Clustered and Non-Clustered. So, to use Primary key with
any one Clustered or Non-Clustered index.

Example in SQL Server 2000:
Query :
-------
create table BookDetails
(
Book_ID int identity(1,1) not null clustered,
Book_Title varchar(20),
Book_Type varchar(15),
Book_Author varchar(25),
Book_Edition float,
Book_Publisher varchar(20),
Book_Pub_Year datetime,
Book_Price int
)

Answer:
-------
Server: Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'clustered'.

Prevent this Error To use Clustered Primary Key:
------------------------------------------------
Query In SQL Server 2000:
-------------------------

create table BookDetails
(
Book_ID int identity(1,1) not null primary key clustered,
Book_Title varchar(20),
Book_Type varchar(15),
Book_Author varchar(25),
Book_Edition float,
Book_Publisher varchar(20),
Book_Pub_Year datetime,
Book_Price int
)

Answer:
-------
The command(s) completed successfully.

Is This Answer Correct ?

4 Yes

21 No

Post New Answer