Can we create a clustered index on composite primary key.

Question Posted / guest

3 Answers
18488 Views
IGT, I also Faced
E-Mail Answers

Answers were Sorted based on User's Feedback

Can we create a clustered index on composite primary key...

Answer / monal

You can also do this.

create table Test
(officeid integer not null,
empid integer not null,
age integer ,
sex varchar(5),
name varchar(20),
PRIMARY KEY (OFFICEID, EMPID))

this will also create composite primary key and cluster
index on composite primary key.

Is This Answer Correct ?

17 Yes

2 No

Can we create a clustered index on composite primary key...

Answer / anil kumar

Here's the procedure:

Create a table with no primary key defined.
Create clustered index on the primary key columns.
Alter the table to define the primary key.

Table creation:

create table CDSWEB.Anil (officeid integer not null,
empid integer not null,
age integer ,sex varchar(5),
name varchar(20));

Index creation:
create index CDSWEB.AN001 on CDSWEB.Anil(officeid,empid)
CLUSTER;

Primary key defined:

alter table CDSWEB.ANIL
add primary key (officeid,empid);

Is This Answer Correct ?

2 Yes

0 No

Can we create a clustered index on composite primary key...

Answer / shatrunjay shukla

Yes
If you are creating a composite Primary Key, or a composite Clustered Index that is NOT a Primary Key, you are creating a single index that uses both column values as the clustering key.

CREATE TABLE T(id INT, cat INT, uName SYSNAME);

CREATE UNIQUE CLUSTERED INDEX ix_T_id_cat ON T (id,cat);

SELECT * FROM SYS.INDEXES WHERE object_id = OBJECT_ID('T');

Is This Answer Correct ?

1 Yes

0 No

Post New Answer