Index vs. Unique Key in SQL Server 2008_问答_开发者

I have the following table that serves to join 3 tables:

ClientID int
BlogID  int
MentionID   int

Assuming that queries will always come via ClientID, I can create 1 multi-column index (ClientID, BlogID, MentionID).

The question is, should I create it as a clustered index or a unique key? I understand a clustered index sto开发者_如何转开发res the data on its leaf nodes. Of course, in this case, the index is the data, so I don't know if SQL Server will duplicate the data or not. Be that as it may, I can't find anything on MSDN about the significance of using "unique key".

How does this differ from Type = Index & IsUnique = yes?

Can someone tell me the advantages each way?

Clustered index is "the table itself", that is, index nodes are arranged in a tree, and its leaf nodes contains row data. Clustered index doesn't have to be declared as unique (though it usually is); if it is not unique, the server implicitly adds a "uniqalizer" to this index, so that each row is uniquely identified.

Other indexes store clustered index value as their leaf nodes (and possibly some other columns if they are included with INCLUDE clause in CREATE INDEX staetment).

Any index might be decalred as unique, so the server would perform an additional check to prevent duplicate values forom getting into the table.

It seems you are asking for the difference among:

 MYTABLE
 id integer primary key autoincrement
 clientid integer
 blogid integer
 mentionid integer
 -- with a unique composite index on (clientid, blogid, mentionid) and three foreign key constraints

and

 MYTABLE
 clientid 
 blogid
 mentionid
 -- with a composite primary key on (clientid, blogid, mentionid) and three foreign key constraints

and

 MYTABLE
 id integer primary key autoincrement
  clientid integer
  blogid integer
  mentionid integer
  with an index on clientid and also an index on blogid and the three foreign key constraints

In the first, you have the index on the integer primary key and also the alternative unique index on the triad. If the second, you have only the unique index on the triadic primary key. In the third, you have a unique index on the integer primary key and two other non-unique indexes, one on clientid and the other on blogid.

The performance gain with the second option's marginally greater efficiency would be de minimis, and so I'd base the decision on other factors. The third is the most flexible in terms of queries and offers greater simplicity of coding; it offers the benefit of indexes on client and blog both, in case you wanted to have a query with blog, not client, in the WHERE clause. As for coding, some GUI tools and middleware have trouble with multi-part primary keys, and your update/insert/delete logic will be simpler when it has to deal with a single integer PK column. I have found that code simplicity and ease of maintenance are far better things than a few seconds or only a few fractions of seconds of improvement in query response time.

A unique index, a unique key and a unique constraint are basically the same thing. They result in an index that enforces uniqueness.
Clustered means that the index becomes the table itself. It's good to have a clustered index, otherwise the table hangs around in an unordered heap.

Unique and clustered are unrelated properties. You can combine them in any way you like. So in your case, I'd create a unique clustered index. The normal way to do that is by creating the index as a clustered primary key.

The data will not be duplicated if you create a clustered unique index on your three columns.

The unique clustered index will be the data - and the index at the same time :-)

Since this is a three-way join table, this clustered index probably does make a lot of sense. I'd say: go for it!

UNIQUE INDEX and UNIQUE CONSTRAINT are somewhat different concepts.

UNIQUE CONSTRAINT is a logical concept and means "make sure this column is unique, no matter how"
UNIQUE INDEX is a physical concept and means "create a B-Tree index on this column and fail whenever duplicates are inserted there"

The latter implies the former but not vice versa.

For instance, in Oracle, if you have a non-unique index on col1:

CREATE UNIQUE INDEX (col1) will fail and say "these columns are already indexed"
ALTER TABLE ADD CONSTRAINT UNIQUE(col1) will succeed and use the existing index to police the constraint.

Use CONSTRAINT if you just want the column to be unique and INDEX if you know a B-Tree index is what you want (to speed up searches etc).