How many records in a table?

[Bill]

Now, I know that *theoretically* one can have some unattainably great
number of records in a table.

But, in practicality (and I know "it depends"), how many records can I
plan to have in a table in a fairly lightweight (low transaction
density) environment?

Would ten million be HUGE, or moderate? A hundred million?

-- Bill

[Erland Sommarskog]

Bill (w.*****@snet.net) writes:

Now, I know that *theoretically* one can have some unattainably great
number of records in a table.

But, in practicality (and I know "it depends"), how many records can I
plan to have in a table in a fairly lightweight (low transaction
density) environment?

Would ten million be HUGE, or moderate? A hundred million?

I am afraid that the question is not easily answerable.

First of all, it is not only the number of rows that matter, but also
how wide they are. If each row is 10 bytes or 1000 bytes makes a lot
of difference! I'd say that a ten-million row table with a rowsize of
ten bytes is still lightweight, but if the rowsize is 1000 bytes, it's
heavy-duty.

Second, indexes and how the tables are queried also matters. If all
queries are along the primary key or some clustered index, the only
time you notice the size is when you take a full backup or restore
the database. But if you are running ad hoc-queries all day on about
any column, then a table of 100000 rows can be painful.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

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