Posted by Erland Sommarskog on 10/01/59 11:25

(eric.nave@gmail.com) writes:
> create table books (
> authorid int,
> bookid int identity (1,1)
> )
>
> Is there any disadvantage to having the primary key and the clustered
> index as a compound key, like this:
>
> alter table books add constraint PK_books primary key clustered
> (authored, bookid)

That looks a little funny. Since bookid is unique, why add authorid
to the PK?

Then again, PK of a books table should probably be the ISBN, as that
is a natural key.

And there should probably be a relation table, as a book can more than
one author.

> Normally, I would make bookid the key, but then I got to thinking, most
> of the queries are going to be "select * from books where authorid =
> @@some_authorID"
>
> So, wouldn't a compound key and index make this a little faster?

Having the clustered index on authorid is probably better than clustering
on ISBN, yes. But there not really any reason to add it to the PK of
books. (In an bookauthors table that covers the many-to-many relation
between books and authors, the key would make sense.)



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]