Carl0s_ (10/8/2012)
Thanks for reply ChrisM@WorkI have the same structure in my city table, I refer to (state region) with entity cat (federative entity), my doubt here is, is correct to apply composite key in each table like that? I mean, is not bad to use composite primary key clustered ?, some people say is very bad for performance to use it composite primary key clustered, i dont know what to do, if use subrogate key now, or ..
tnx in advance for help
On one or two occasions I've used clustered indexes which included most of the columns in the table and the performance has been excellent. Very similar to your configuration here. If queries against the table(s) will almost always use all of the columns of the clustered index in joins, then it's very likely that a composite clustered index will work well.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden