Table size

16 Mar 2007 12:38 AM

sean_mcad

I have one table which has about 6 columns each containing very small data
(most are an int with a value of either 0,1 or 2).
I predict this table will reach 200,000 rows in 12 months.

How many rows given such a "thin" table should I be concerned? I will not
have access to this database once I am finished so I want to check to see if
I am not digging them a hole. Although even a year is pretty long time in
this industry the entire application might change in 2

16 Mar 2007 1:42 AM

Greg D. Moore (Strider)

"sean_mcad" <seanm***@discussions.microsoft.com> wrote in message
news:B416C4B8-3F51-4446-91DE-224EC072F1BC@microsoft.com...
>I have one table which has about 6 columns each containing very small data
> (most are an int with a value of either 0,1 or 2).
> I predict this table will reach 200,000 rows in 12 months.
>
> How many rows given such a "thin" table should I be concerned?

Concerned about what?

200,000 rows, especially 6 int columns is trivial.

But really depends on the application, indexes involved, etc.

Show quoteHide quote

> I will not
> have access to this database once I am finished so I want to check to see
> if
> I am not digging them a hole. Although even a year is pretty long time in
> this industry the entire application might change in 2

--
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com

16 Mar 2007 2:36 AM

David Portas

On 16 Mar, 00:38, sean_mcad <seanm***@discussions.microsoft.com>
wrote:

> I have one table which has about 6 columns each containing very small data
> (most are an int with a value of either 0,1 or 2).
> I predict this table will reach 200,000 rows in 12 months.
>
> How many rows given such a "thin" table should I be concerned? I will not
> have access to this database once I am finished so I want to check to see if
> I am not digging them a hole. Although even a year is pretty long time in
> this industry the entire application might change in 2

With the right design you needn't be concerned even with 200 million
rows.
With the wrong design you could fail with much less than 200,000.

The most pertinent question is: did you test it with the expected
200,000 rows?

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

16 Mar 2007 10:02 PM

sean_mcad

Well when I looked at other options the only option I had was to make a table
that might not be as long but a lot "wider" and I know that is not a good
thing. By wide I mean and extra 39 columns as well as violating good
practices with a 1:m realtionship.

Index would be an int and only one column so I think they are in good shape.

I read all the time in database books that use the term "large tables" but I
have only seen one reference to what is considered a "large table" and it
said over 100,000 but that written statement was more of an abstract
reference to what a large table would be. Naturally I understand the
structures of the Keys makes a huge difference as well.

Show quoteHide quote

"David Portas" wrote:

> On 16 Mar, 00:38, sean_mcad <seanm***@discussions.microsoft.com>
> wrote:
> > I have one table which has about 6 columns each containing very small data
> > (most are an int with a value of either 0,1 or 2).
> > I predict this table will reach 200,000 rows in 12 months.
> >
> > How many rows given such a "thin" table should I be concerned? I will not
> > have access to this database once I am finished so I want to check to see if
> > I am not digging them a hole. Although even a year is pretty long time in
> > this industry the entire application might change in 2
>
> With the right design you needn't be concerned even with 200 million
> rows.
> With the wrong design you could fail with much less than 200,000.
>
> The most pertinent question is: did you test it with the expected
> 200,000 rows?
>
> --
> David Portas, SQL Server MVP
>
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
>
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
>