Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Primary Key vs Clustered Index - Very newbie question Expand / Collapse
Author
Message
Posted Thursday, November 19, 2009 5:43 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, October 16, 2010 2:37 PM
Points: 19, Visits: 69
First my most humble apologies as I am sure most of you are going to roll your eyes in dispair.

I am basically an Access person whose databases outgrew the Access size limit a long time ago and we moved our data to Sql Server 2005 but basically left the programming as is. Now we have probably about 35 million records more or less and I was thinking it was about time to learn a little SQL Server.

So I read the recent articles on indexes and found that bad bad me I have no clustered indexes on the major tables which consist of several million records. Probably a bad thing.

And I decided to make the primary key clustered and got all set to do it last Sunday and "IT" wouldn't let me.

Now I think I discovered that if there is a primary "KEY" defined - and there is, then this becomes a clustered index. That's what I just read. True??? I do have a primary key on each table. I didn't create them but I did run some automatic performance tools right after we moved the data and that is probably how they got there.

If this is so, then fine as that is how I would have defined the clustered index, although I might want to change one slightly.

Do I even want a primary and then a unique index that is identically defined? Is this some sort of magic that I want or is this a very bad thing?
Post #822033
Posted Thursday, November 19, 2009 10:00 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 8:20 PM
Points: 32,764, Visits: 14,928
A PK is a logical structure. It means the field(s) that uniquely identify each row in the table.

This is implemented in SQL Server as a unique index. If you set up a PK, you get a unique index on the table.

Clustered index (CI) v nonclustered index (NCI) is completely separate. Either can be unique. You get one CI on a table because it is the data. A table without a CI is a heap, and doesn't perform as well in queries. The optimizer in SQL Server is built to work with clustered indexes, so it's recommended that you have a CI on the table.

The choice of a CI is usually made based on query performance. The PK creation defaults to clustered, but this isn't always the best choice. Often we pick the CI based on range queries. Since it's the data, if you query on some range data, often dates, then a CI on that field can be more efficient.

For the PK, you can have it as a CI or NCI. If you don't think you have a better choice for a table, then leave the PK as a CI.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #822093
Posted Friday, November 20, 2009 1:49 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:59 AM
Points: 41,508, Visits: 34,425
Carolyn Stern (11/19/2009)
Now I think I discovered that if there is a primary "KEY" defined - and there is, then this becomes a clustered index. That's what I just read.


By default, when a primary key is created, if there is no existing clustered index on the table, the primary key is enforced by a clustered index. This is only be default, not a requirement

If this is so, then fine as that is how I would have defined the clustered index, although I might want to change one slightly.


If you want to change one, drop the primary key (and I would suggest you learn to do this in script as it gives more control than the GUI designer), then create the clustered index that you want, then go back and recreate the primary key.

Do I even want a primary and then a unique index that is identically defined?


Highly unlikely. If one is the clustered and one is not then there might be a very occasional reason for doing that, but very unlikely.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #822157
Posted Saturday, November 21, 2009 7:27 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Saturday, April 12, 2014 11:40 AM
Points: 2,795, Visits: 8,297
Carolyn Stern (11/19/2009)
First my most humble apologies as I am sure most of you are going to roll your eyes in dispair.

Not at all.

A well asked question often leads to well worded answers which help many of us also.



Post #822948
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse