Northwind Mystery

[Albert D.Kallal]

In this case, the rows in the dependent table are not grouped together in

time, and therefore not in their numbering. Doesn't this blow away the
organization you would have if the autonumbers were assigned "close
together in time"?

Yes. It does. I did point out that things like invoice details, and many
other types of child tables are entering in the same time frame however.

And isn't this an example of what happens in most table relationships in
most applications written?

Sure...for the most part, I would be a hard egg shell to argue otherwise.
So, I do have a number of examples were the autonumber does tend to
group as well as the FK. (but, this would not be the "larger" norm!!).

This presumes you would typically have BOTH the identity key and a unique
natural key. That's OK, isn't it? So, which should be the PK? I do not
believe you lose anything making the natural key the PK, and you would
often gain significantly. Is that what your analysis tell you, too?

Yes..I do agree with the above. The question then becomes how many users,
how
large of a application, and what efforts are gain by having a natural PK, or
some
other way.

However, it would be EASY for me to make the primary key of those child
tables of mine the FK + autonumber. I will test this, and if it gives me the
FK
order (for our "sql clustering"), and the autonumber for unique id (for my
top 1
quires, then we really have gained by this discussion!!

Further more, if I make the autonumber index unique, then I have a primary
key,
but I don't have to set it as such...do I !!

In other words...this discussion has resulting in me realizing a nice
performance tip, but NOT having to give up my autonumber...

Gee....I did not expect to reach such a valuable lesson here...

So, in conclusion

no index on those FK fields is bad...I can't blame the NorthWind
design
for having them.

If a choice between no index, and having a index is at issue, likely
one is still better off to index these fk id type fields.

You *still* need a index on those fields for performance, even when
you
can't achieve the ideal goal of clustering

The ideal here is to not use the autoumber as the primary key in the
child table. And, as we *just* concluded, we are actually free to using a
autonumber id as a primary key..but NOT SET is as such!!!

So, it looks like I can have my cake...and eat it.....

Well done...and a *really* cool performance tip is learned here....