Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Maximum Number of SQL tables
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

Ask SQLTeam Question

0 Posts

Posted - 06/05/2002 :  09:32:39  Show Profile  Visit AskSQLTeam's Homepage
Napoleon writes "Hi Guys,
What is the maximum number of tables in SQL 2000? Please...I need your idea...

Most Valuable Yak

15732 Posts

Posted - 06/05/2002 :  09:34:25  Show Profile  Visit robvolk's Homepage
In Books Online there is an entry for "capacity specifications" and it lists all of the numerical limits for various features of SQL Server. In the case of tables/objects, that limit is approx. 2 billion. If you are designing a database that has 2 billion objects, trust me, you're doing it wrong.

Go to Top of Page

Slow But Sure Yak Herding Master

United Kingdom
3608 Posts

Posted - 06/05/2002 :  09:50:40  Show Profile
If you mean within T-SQL, it is 256, if you are using this many, your head will explode....



Go to Top of Page

Flowing Fount of Yak Knowledge

1242 Posts

Posted - 01/09/2007 :  01:19:40  Show Profile  Visit timmy's Homepage
Sorry to dredge up an old topic, but someone told me recently that this limitation has been lifted for SQL 2000 SP4. Doesn't seem right to me - it seems like a fairly substantial change.
The list of fixes for SP4 doesn't mention it.

Does anyone know?


Go to Top of Page

In (Som, Ni, Yak)

17689 Posts

Posted - 01/09/2007 :  01:29:26  Show Profile
Don't think so.

Does not make sense if it was lifted in SP4 but still limit it in 2005


Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 01/09/2007 :  09:10:04  Show Profile
Having a limit of 2 billion+ in the database doesn't seem like much of a problem.

Having a limit of 256 tables in a query doesn't seem like that big of a problem either. I don't think I have ever run into this limit.

Go to Top of Page

Flowing Fount of Yak Knowledge

1242 Posts

Posted - 01/09/2007 :  16:15:07  Show Profile  Visit timmy's Homepage
We've come close to it doing some fairly complex FX calculations that use 3 views and a stored proc for one operation. Admittedly it could be improved, but it works well and much easier than crunching the numbers manually in a client-side app using a bunch of arrays.....
Go to Top of Page

Flowing Fount of Yak Knowledge

6065 Posts

Posted - 01/09/2007 :  17:32:45  Show Profile
I agree that if you are bumping up against sql limits for anything you're way off base with design.

I recall someone here posted a problem of exceding the table limit in a query. They insisted they only used 6 tables. Turned out it was because they had several views, each having a boat-load of self joins because of "normalization issues". Then made different views of the base views Joined together, and finally an SP joined the views of views. Although there were only about 6 underlying tables the cumulitive effect of views of views with self joins blew out the 256 table limit...ugly

Be One with the Optimizer
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000