Posted by Erland Sommarskog on 01/02/07 22:41

Emin (emin.shopper@gmail.com) writes:
> If I have a number of related groups of data (e.g., stock prices for
> different stocks) is it better to put them into many tables (e.g., one
> table per stock) or into one big table (e.g., a single table where one
> column is the stock id other columns are the price and other data)?

There are two reasons why you would have more than one table:

1) Information is so different for different (groups of) stocks,
that you get different set of columns.

2) The table is really big. Then you would might make into a partioned
view or table, based on condition. But that would not be one table
per stock id.

So, in the logical design, that's one table.

> The single table approach with a clustered primary key including the
> stock id and other information that is different for each stock seems
> cleaner to me. But I worry that having a single table could hurt
> preformance when the database gets very large. The clustered primary
> key should make queries on the single table fast, but I'm worried about
> inserts. According to my vague understanding of a clustered primary
> key, records are physically stored on disk based on the primary key. So
> if I insert a record for a given stock does the database have to
> physicall "move down" all the records that are below that or does it do
> something smarter?

If the PK is monotonically ascending new rows are just added at the
end, all continues smoothly. If the PK is not ascending, but for instance
a random GUID, then you will get a lots of page splits, and that
takes extra power. But the answer in this case is that you should
not cluster on the GUID, but on something else. It's perfectly possible
to have the clustered index on some other column(s).


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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]