performance question

Hi,
What's the best way to store a certain kind of information in an SQL-server database:
-having one table with many records (e.g. millions)
or
-having many tables (e.g. thousands) with less records (e.g. thousands)Your question cannot be answered with a simple "statement x is correct" as the answer varies wildly based on the scenario. The best way to find an answer is to do some reading. I suggest you start by reading up on normalisation (www.r937.com/relational.html).|||Ah, the ever popular answer: It depends!

With one pseudo-exception I'll explain in a moment, if you have one single type of thing, then one table is the best way to store it. If you have one thosand similar but slightly different things, then one thousand tables are usually better.

The one place that I'll make an exception to that rule (which is really just a clarification, not a true exception) is that lookups are a different kind of beast. While people sometimes argue that all lookups belong in a single table (this is known as One True Lookup Table or OTLT), lookups really belong in separate tables.

If you have 96 kinds of lookup items, those are really 96 quite different things. If you can put hat sizes and truck colors into a lookup table, but you can't exchange a hat size for a truck color (no one I know wears a "Sunburnt Orange" size hat or drives a 6 7/8 colored truck), then the hat sizes are a different thing than the truck colors. Because they are different things, they ought to be in a different table.

-PatP