So you say that unique constraints are indexes?
-
So you say that unique constraints are indexes?
I've been reading alot lately about indexes and what happens behind the scenes. What most people seem to agree on is:
- Primary keys are simply "logical" and are really simply unique indexes
- Unique constraints(keys) are maintained(and thus are) as unique indexes
- Primary Keys do NOT allow for nulls whereas unique constraints allow for a null(maybe more?? don't care.)
That said and all assumed generally correct, then:
- why would I EVER use a unique key rather than a unique index ?
- are unique keys included in update statistics ?
- are unique keys ever used as an index as a search aid(or are they only for uniqueness)
Please - only true gurus reply as this issue does not need more specualtion by amatuers
- B
-
- why would I EVER use a unique key rather than a unique index ?
Not sure what you mean by "unique key" if what you are trying to refer to is "unique CONSTRAINTS" then you use constraints because they can be DISABLED not such thing with indexes ( well in 2005 you can actually disable the index but that is a brand new feature)
- are unique keys included in update statistics ?
if you do a simple UPDATE STATISTICS [table_name] then yes all indexes including UQ_* will be included. If you use WITH "ALL" then the columns that have statistics will be inclueded in the command also.
- are unique keys ever used as an index as a search aid(or are they only for uniqueness)
Yes they are used as long as it is cheaper from the optimizer point of view. Example are the cases in which the UQ_* index COVERS the query for other queries is probably cheaper to use the PK
Hope this helps!
* Noel -
Thanks Noel,
I assume that since you did not differ with my initial assumptions that you believe them to be correct. What I meant by "Unique Keys" is what SQL Server 2005 calls them explicitly. If I edit table indexes, the index can either be an "Index", or a "Unique Key".
So, since 2005 allows me to disable unique indexes as you state, I see absolutely no difference between a unique index and a unique key. There must be some reason for the difference ?
Regardless, thanks again Noel for the interesting points, especially the search aid as a covering index.
- B
-
>>>So, since 2005 allows me to disable unique indexes as you state, I see absolutely no difference between a unique index and a unique key. There must be some reason for the difference ? <<
There is also a conceptual difference. UNIQUE CONSTRAINT is used in MODELING tools to indicate a property or trait, UNIQUE INDEX is an implementation detail that belongs only to physical modeling. While designers are dealing with a conceptual model they carry out all their operations using constraints while at implementation time DBAs are responsible for "placing" the indexes in the appropriate LUN/DRIVE/etc for tuning purposes.
And, yes all your initial asumptions are correct.
* Noel -
I think Noel pinned what this confusion generally is about..
There are two sides: Logical and Physical.
Per se, a constraint has nothing to do with an index, whatsoever.
As Noel says, a constraint is a Logical thing, an index is a Physical implementation.
(the difference may be fine, but it's there)
- Primary keys are simply "logical" and are really simply unique indexes
Yes, although 'implemented as' may be more accurate than 'are'...
The reason is that currently there is only one way to physically implement the logical concept of a Primary Key, and that is to physically place a unique index on the PK definition. This is also related to efficency.
In theory you could use a CHECK constraint to enforce uniqueness, but a unique index is way more efficient.
- Unique constraints(keys) are maintained(and thus are) as unique indexes
A Unique Constraint is just, as a Primary Key Constraint, a logical concept.
The same applies here, a unique index is how the concept is implemented/applied/enforced (whatever...)
The difference lies in what rules govern the two.
The rules for a UC states that uniqueness is to be enforced. In this case, null is regarded as a 'value', thus you're allowed one occurence of null within the rules.
The rules for a PK states that null is not allowed.
The unique index that is created as a result of either a UC or PK constraint declaration, has no objections itself about null or not null, only uniqueness. You could also say that any index is 'not aware of constraints'
As for when (or why) you would use one constraint or the other or not, that's entirely a modelling (logical) decision.
Hopefully I made it clearer, rather than muddier...?
/Kenneth
-
Thanks - it does help a bit and validates more that UC's are in fact unique indexes.
At one point in time I thought I was "saving" either space, or processing power by using UC's rather than UI's - but that does not appear to be the case.
So, it is still a vague but if the difference it purely related to context - then so be it.
Regards - B
-
Well, you could look at the difference between UC and UI as 'labeled' or 'unlabeled', if you get my meaning.
When you declare a UC, it's the declaration, literally, that differentiates it from an index.
An index may exist for any given reason, while a declared constraint is a part of the model.
Given a declared UC, anyone can look at it and know for a fact - this is intended to be unique by design.
If you happen to look at a unique index alone, you can't be 100% sure that 'it's by design or by chance', since it's not 'officially declared'.
And no, there is no space-difference between a declared UC and a plain index
/Kenneth
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply