Can we create clustered index on non primary key column
Answers were Sorted based on User's Feedback
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
What are the different types of lock modes in sql server 2000?
Explain alternate key, candidate key and composite key in sql server?
What is the order in which the sql query is executed?
What is the xml datatype?
How can you ensure that the database and sql server based application perform well?
Can binary strings be converted into numeric or float data types?
What is normalization of database? What are its benefits?
What do you mean by sql server agent?
What is used to replicate sessions between instances in coldfusion clusters?
How to get a list of all tables with "sys.tables" view in ms sql server?
Explain the different types of joins?
What is the main purpose of having conversation group?