August 9, 2012 at 12:38 am
Am creating a table where I am creating a clustered index on a column which is not a primary key but it is combination of two columns in which the primary key id the second column. Now can I create a primary key without any index as it is already part of clustered index?
Ex - There are two columns named loadtime,id on which I have created clusted index
id is a identity column and PK of the table. While creating this, can I do it without any index applied on it?
Will this have any performance issues when using primary key?
August 9, 2012 at 1:33 am
Hi! You can have a non-clustered primary key. All you have to do is make your column not nullable and it should be unique.
create table t1(date datetime, id int);
create clustered index ixc_t1 on t1(date,id);
go
--(skip it if column is not nullable) 1-st if column allows nulls - alter it to not null
--to do this first drop clustered, alter column and create once again
drop index t1.ixc_t1;
alter table t1 alter column id int not null;
create clustered index ixc_t1 on t1(date, id);
go
--2-nd create a unique constraint
alter table t1 add constraint pk_t1id primary key (id);
go
--drop table t1
But, the truth is that constraints are implemented as unique indexes any way.
So after creating a pk - you'll have unique non-clustered index implementing it.
August 9, 2012 at 1:36 am
Thanks for your thoguhts ..
I believed that Primary key needs an index (clustred/ non-clustred) but wanted to see if we can create it without index.
I have attempted with an example and it shows primary key needs an index.
August 9, 2012 at 1:56 am
bala.sevva (8/9/2012)
Thanks for your thoguhts ..I believed that Primary key needs an index (clustred/ non-clustred) but wanted to see if we can create it without index.
I have attempted with an example and it shows primary key needs an index.
Well, it doesn't need any index to be created by you, instead of it Database Engine creates unique index itself to implement the uniqueness of PK.
Here is what BOL says:
Creating and Modifying PRIMARY KEY Constraints
The Database Engine automatically creates a unique index to enforce the uniqueness requirement of the PRIMARY KEY constraint. If a clustered index does not already exist on the table or a nonclustered index is not explicitly specified, a unique, clustered index is created to enforce the PRIMARY KEY constraint.
So, I think you can't create PK without any unique index, this is how sql server works.
August 9, 2012 at 2:18 am
Confirmed.
UNIQUE and PRIMARY KEY constraints must be enforced by a UNIQUE index.
-- Gianluca Sartori
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply