Primary Key and Clustered Index

Question

Post reply

Primary Key and Clustered Index

JMI

SSChampion

Points: 11831
More actions
June 16, 2004 at 1:37 pm

#162040

Table User has a PK on [User_ID] field.
A lot of queries do searches by [User_ID].
Do I need to create a Clustered Index on [User_ID]
or PK by default is an Index?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Antares686 SSC Guru Points: 125444 More actions · Answer 1

It is clustered constraint (same as index with prechecking before insert) by default.

JMI SSChampion Points: 11831 More actions · Answer 2

So I don't need to create an Index on [User_ID] ?

By the way when you create an Index

in EM you have three options:

1) Create UNIQUE

2) Constraint

3) Index

"Creates As Clustered" checkbox.

And one little thing that always was a mystery for me

"Fill Factor"

I thought there are just two Index types,

Clustered,Non-Clustered.

I can understand the first option

UNIQUE Index

But I don't undersatnd 2)Constraint and 3)Index (Index/index?)

JMI SSChampion Points: 11831 More actions · Answer 3

Can a PK be not an Index at all?

Because I want to have a Clustered Index on

other than PK field?

Xearo SSC-Addicted Points: 448 More actions · Answer 4

Yes a Primary Key can indeed be made so that it is not a clustered index. You will have to specify it while creating the key.

If you are using EM while creating the column right click on that key,go to properties, and on index/key tab uncheck create clustered option.

If you are using T-SQL then you will have to specify "nonclustered" keyword while creating the primery key. Thus you can use some other column for clustered index.

The fillfactor governs how "full" your index pages are. So 90% fillfactor means that the index pages are 90% full thus less pages are needed to read for reading activity, but higher fillfactor can cause page spliting and thus writing can become problematic.

Do some reading on indexes and their creation rules etc. You should have no problem understanding it. This very site has some great articles on indexing.

Regards.

Antares686 SSC Guru Points: 125444 More actions · Answer 5

In addition the difference between index and constraint seems to be the time at which data is checked. COnstraint gets checked before insert but it does have some overhead involved. As far as details I am still playing to find out myself until I find good solid documentation otherwise.

Tom Goltl SSCertifiable Points: 5815 More actions · Answer 6

Constraints are also not kept in sorted order like indexes.

Tom