Can we create clustered index on non primary key column

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

Question Posted / dinesh gupta

20 Answers
54792 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 / ashok

we can creta clustered index on non primary column.
create table abc(eid int,ename varchar(50))
create clustered index abc_clustered on abc (eid)

Is This Answer Correct ?

67 Yes

9 No

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

Answer / pavan kumar

YES, We can create a clustered index on a non-primary
column. Check the folowing queries...

CREATE TABLE EMPLOYEES
(
empid int NOT NULL CONSTRAINT ix_pkEMPLOYEES PRIMARY KEY
NONCLUSTERED
, name varchar(25) NOT NULL
, age tinyint NOT NULL
)

CREATE CLUSTERED INDEX ixcEMPLOYEES ON EMPLOYEES (name)

Is This Answer Correct ?

57 Yes

6 No

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

Answer / rima

NO.If the table has the primary key column, then by default
it will have a clustered index on that primary key
cloumn.So if you want to create a clustered index on non
primary key column then you have to create it before
setting a column as the primary key column or u have to
delete the clustered Primary key columns index to create a
new one.but it is recommended to have a clustered index on
primary key column.

Is This Answer Correct ?

17 Yes

5 No

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

Answer / kaushik ganguly

We know sql server creates a clustered index by default
when we create a primary key. If you create a PK first and
then try to assign a candidate key a clustered index then
it is not possible bcos sql server allows one clustered
index per table.

But...

If you make a candidate key a clustered index and then
define the primary key sql server doesnot create clustered
index for the Primary Key column.

So the answer is yes...you can create a clustered index on
non-pk column :D

Is This Answer Correct ?

11 Yes

2 No

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

Answer / surya prakash

Finally, i want to conclude by saying.

1. We can create Clustered Index on Non-Primary Key Columns
and this table should not contain any other column with
Primary Key.

2. It is recommended that we create Clustered Indexing on
Primary Key Columns

Is This Answer Correct ?

9 Yes

3 No

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

Answer / sandeep

Yes, We can, only thing is that, we can create clustered
index only on one key.If table contains any clustered index
on any key(even on primary or on unique key) just drop it
and create it on desired key. after it you can create a non
clustered index on primary or on unique key.

Is This Answer Correct ?

4 Yes

0 No

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

Answer / vidit

Yes, But on that table primary key should be created with
non-clustered index and the columun u want to create a
cluster index is should be unique

Is This Answer Correct ?

3 Yes

0 No

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

Answer / laxman2610

Yes, But on that table primary key should be created with
non-clustered index and the columun u want to create a
cluster index is should be unique

Is This Answer Correct ?

7 Yes

5 No

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

Answer / suraj

-- YES.

drop table Employees
create table Employees(
EmpID int Primary Key NonClustered,
[Name] varchar(20),
SSNo varchar(20) )

CREATE CLUSTERED INDEX ci_EMPLOYEES_SS ON EMPLOYEES (Name)

insert Employees values (101,'John','123-45-6789')
insert Employees values (102,'Mike','123-45-6789')

select * from Employees

-- We have to specifically define NonClustered in the
Primary Key column because Clustered Index is created by
default in Primary Key.
-- And, We are able to define Clustered Index in
another column because every table is eligible to have one
clustered index.
-- Clustered Index has to be created after table
creation.
-- From above example, EmpID doesn't take duplicates,
because it is primary key. But, SSNo takes duplicates.

Is This Answer Correct ?

2 Yes

0 No

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

Answer / vikas kant

Yes We can create clustered index on a non primary key
column by using the following syntax--:

create clustered index abc_index on Table1(student_class)

Is This Answer Correct ?

1 Yes

0 No

Post New Answer