September 30, 2009 at 1:10 pm
Hey guys, i am pretty new to sql server and am trying to wrap my head around the difference between primary key which creates a unique clustered index on a column in the table and an actualy clustered index. My issue is that i create a table Employer with three columns empid, lastname, firstname. empid is an identity column and defined it as the primary key. when i tried to remove the index on the empid column it said that the index does not exist .
Now my question is that when i assigned empid as the primary key it is supposed to create a unique clustered index for that column. now is this a logical index that sql server creates on that column or do i have to create an index on that column empid for it to actually work.
thanks
sql guy
September 30, 2009 at 1:22 pm
usually when you create a primary key, the default it creates is a Clustered Primary Key. But you do not need to create it that way. You can create a non clustered primary key for that column. You can create a clustered key that does not have to be Unique. Did I make sense to you?
And when you create a primary key, it actually creates an Index.
-Roy
September 30, 2009 at 1:42 pm
You can create a table with a clustered index but no primary key. You can create a table with a primary key but no clustered index. You can create a table with both, but on different columns. You can create a table with neither. Most often, you create a table where the primary key and the clustered index key are the same, since that's the default.
If you do this:
create table dbo.PrimaryClustered (
ID int identity primary key);
go
select *
from sys.indexes
where object_id = object_id(N'dbo.PrimaryClustered');
You'll find an index has been created, with a name like PK__PrimaryClustered__37703C52.
If you then give this command:
drop index PK__PrimaryClustered__37703C52 on dbo.PrimaryClustered;
You'll get an error message:
Msg 3723, Level 16, State 4, Line 1
An explicit DROP INDEX is not allowed on index 'dbo.PrimaryClustered.PK__PrimaryClustered__37703C52'. It is being used for PRIMARY KEY constraint enforcement.
What you would need to do at that point is alter the table to drop the PK constraint first.
A primary key is implemented in SQL Server with a unique index. It's usually also the clustered index, but it doesn't have to be.
Does that help?
Are you familiar with the difference between clustered and non-clustered indexes? Do you know what a "heap" table is?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 30, 2009 at 4:35 pm
Thank you for the reply. I implemented your example and was able to see the difference.
I am aware of the diff between clustered and non-clustered but i dont know what a heap table is. I will however look it up.
September 30, 2009 at 4:36 pm
Hey Roy
Thank you for the reply. I am clear on the subject of primary keys now. I guess i still have to do some reading to be fully aware of the nuances.
October 1, 2009 at 7:47 am
A heap table is simply a table without a clustered index. May or may not have a primary key defined, the lack of a clustered index is the defining characteristic.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply