June 16, 2004 at 1:37 pm
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?
June 16, 2004 at 2:06 pm
It is clustered constraint (same as index with prechecking before insert) by default.
June 16, 2004 at 2:14 pm
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?)
June 16, 2004 at 2:40 pm
Can a PK be not an Index at all?
Because I want to have a Clustered Index on
other than PK field?
June 16, 2004 at 3:16 pm
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.
June 16, 2004 at 4:19 pm
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.
June 17, 2004 at 8:47 am
Constraints are also not kept in sorted order like indexes.
Tom
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply