Maximum number of rows?

Hello Everyone:
I have been told by our DBA that SQL Server 2000 has problems
with tables that contain more than 13 million reocords, in particular
with indexes. He was not more specific(!).
Our group is in the process of developing an application and a couple
of the tables we will be using will contain up to 50 million records.
My worry here, is that if what our DBA said is correct that we need to
take this into account early in the DB design phase because any
structural changes to the DB have impact on the procedures, functions,
etc as well as the application.
I had not heard any such statement previously made and in fact my experience
has been quite positive with larger tables (over 20 million).
Nevertheless I am still somewhat concerned at the prospect of finding
out he was correct "after the fact", so to speak, and thus causing considera
ble
effort going into a work around after we are in production.
If anyone has heard or knows of anything of the sort I would be very thankfu
l
for your advice or to hear your experiences, especially under what
circumstances if any does SQL Server have problems.
Regards,
MarioN of rows is limited only with storage capacity (check
http://msdn.microsoft.com/library/d...br />
8dbn.asp).
Personally, I worked with a table with 3.5 billions rows without a
problem...
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Mario" <anonymous@.discussions.microsoft.com> wrote in message
news:94D5DF8D-D5BE-4563-A4A3-E29485D36E09@.microsoft.com...
> Hello Everyone:
> I have been told by our DBA that SQL Server 2000 has problems
> with tables that contain more than 13 million reocords, in particular
> with indexes. He was not more specific(!).
> Our group is in the process of developing an application and a couple
> of the tables we will be using will contain up to 50 million records.
> My worry here, is that if what our DBA said is correct that we need to
> take this into account early in the DB design phase because any
> structural changes to the DB have impact on the procedures, functions,
> etc as well as the application.
> I had not heard any such statement previously made and in fact my
experience
> has been quite positive with larger tables (over 20 million).
> Nevertheless I am still somewhat concerned at the prospect of finding
> out he was correct "after the fact", so to speak, and thus causing
considerable
> effort going into a work around after we are in production.
> If anyone has heard or knows of anything of the sort I would be very
thankful
> for your advice or to hear your experiences, especially under what
> circumstances if any does SQL Server have problems.
> Regards,
> Mario
>|||I am not aware of any problems at all. 13 million rows is quite a small
number as far as SQL Server is concerned. I have no ideas where the "DBA"
plucked this figure from - I would be interested to hear where. Even if you
use bigint as the PK for a table you could have 9,223,372,036,854,775,807
rows (I can't say that number, so have to write it down as digits). :-)
Mark Allison
SQL Server MVP
http://www.allisonmitchell.com
"Mario" <anonymous@.discussions.microsoft.com> wrote in message
news:94D5DF8D-D5BE-4563-A4A3-E29485D36E09@.microsoft.com...
> Hello Everyone:
> I have been told by our DBA that SQL Server 2000 has problems
> with tables that contain more than 13 million reocords, in particular
> with indexes. He was not more specific(!).
> Our group is in the process of developing an application and a couple
> of the tables we will be using will contain up to 50 million records.
> My worry here, is that if what our DBA said is correct that we need to
> take this into account early in the DB design phase because any
> structural changes to the DB have impact on the procedures, functions,
> etc as well as the application.
> I had not heard any such statement previously made and in fact my
experience
> has been quite positive with larger tables (over 20 million).
> Nevertheless I am still somewhat concerned at the prospect of finding
> out he was correct "after the fact", so to speak, and thus causing
considerable
> effort going into a work around after we are in production.
> If anyone has heard or knows of anything of the sort I would be very
thankful
> for your advice or to hear your experiences, especially under what
> circumstances if any does SQL Server have problems.
> Regards,
> Mario
>|||You'll run against the storage capacity limits of SQL Server long before you
reach that number. The maximum size for a database is 1,048,516 Tera Bytes,
and you're talking about approximately 9,000,000 Tera rows.
"DBA" indeed. Mario, both Mark and me are on the market at the moment ;-)
Jacco Schalkwijk
SQL Server MVP
"Mark Allison" <mark@.allisonmitchellyourpants.c0m> wrote in message
news:uieUWhW8DHA.2524@.TK2MSFTNGP11.phx.gbl...
> I am not aware of any problems at all. 13 million rows is quite a small
> number as far as SQL Server is concerned. I have no ideas where the "DBA"
> plucked this figure from - I would be interested to hear where. Even if
you
> use bigint as the PK for a table you could have 9,223,372,036,854,775,807
> rows (I can't say that number, so have to write it down as digits). :-)
> --
> Mark Allison
> SQL Server MVP
> http://www.allisonmitchell.com
> "Mario" <anonymous@.discussions.microsoft.com> wrote in message
> news:94D5DF8D-D5BE-4563-A4A3-E29485D36E09@.microsoft.com...
> experience
> considerable
> thankful
>|||Jacco,
my feelings exactly.
Earlier today I asked him to provide a source for his statement should anyth
ing turn up I'll post back here.
Thanks again!
Mario