Clustered Constraint Index?

Question

Clustered Constraint Index?

Frank Nostro

Mr or Mrs. 500

Points: 596
More actions
March 2, 2005 at 6:32 pm

#109328

Hello again everyone,
I have a table that has a Clustered Index - Or should I say a Clustered Constraint?
If you go into the table designer for the table we see that no primary Key is defined. When I bring up the "Index/Keys" tab in the properties popup for the table I see the following:
1. That only 1 Index is defined
2. It is a compound Index that uses 2 integer fields
3. The "Create UNIQUE" checkbox is selected
4. The "Constraint" radio button is selected
5. The "Create as CLUSTERED" checkbox is selected.
6. There is a Fill Factor of 90%
So - my questions...
Does this actually create an index or does it simply prevent duplicate entries into the specified columns? (My understanding about constraints is that it is simply an enforced rule).
How does a Clustered Constraint Index work - what's going on behind the scenes? Is there a performance benefit to using an index this way when the table is subject to a high update rate (say 400+ updates/sec) on the back end *AND* is queried regularly by users on the front end.
I cannot get a clear answer from the Transact SQL help files.
Any insights would be appreciated
--Frank

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

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

PW-201837 SSC-Insane Points: 20805 More actions · Answer 1

Behind the scenes, Sql Server creates a physical index to implement the constraint.

You can see this by querying the sysindexes system table by querying "where sysindexes.id = object_id('YourTableName')".

Queries that have the reference the indexed columns in their WHERE clause should use the underlying index of the constraint, but you'd need to paste the SELECT into QA and hit CTRL-K to see the query plan to verify this.

rhunt SSCertifiable Points: 5868 More actions · Answer 2

Also, primary keys do not allow the use of NULL values. Because the unique constraint is not the PK, I believe it will allow at least one null value or combination of null values since this is a composite constraint.

PW-201837 SSC-Insane Points: 20805 More actions · Answer 3

You may also want to look into adding locking hints to the processes which are reading the data. Depending on your requirements, you may be able to use WITH NO_LOCK hints on the SELECT statements to reduce the contention between updates and selects.

Frank Nostro Mr or Mrs. 500 Points: 596 More actions · Answer 4

Thanks for the info fellas,

I do know that I see "with [nolock]" all over the place when look at the stored procedures.

I'm going to have to start becoming very familiar with the execution plan utility.

athurgar SSCrazy Points: 2816 More actions · Answer 5

A primary key is implemented as a unqiue index not allowing nulls - which is a constraint. Not elegant in its execution, but acceptable by the ANSI standard as a method of implementing a PK. But the reason you create a primary key is to have a relationship with a foreign key table - this is part of relational database design (Codd & Date). I am amazed at ho wmany times I see a design with lots of primary keys and no foreign keys.