255 Tables in a query

Hi All,

SQL Server 2005 used to have a limit of 255 tables in a query. Is it same in SQL Server 2005 or it has been improved? Will appreciate any reference link confirming the same, if it has been changed

Regards,
Amit

You could reduce the number of "tables" in your query by creating one or more views to resolve a group of JOINs. Now you can return the columns from a single view, instead of from a larger number of tables.

How weird ... so the only work-around would be to use temp tables to combine data?

Well, let's hope that this is a query that doesn't run every 5 minutes.

Perhaps it's a query that is supposed to be used for everything? That would of course be a bad idea.

quote:Originally posted by FrankKalis

Actually I think, when you get anywhere near this limit you certainly have other serious problems than a single query.

--
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de

how true. I don't even want to think about maintainability of that SQL.

iirc, in SQL 7, the limit was lower? 16 comes to mind... am I wrong?

Panic, Chaos, Disorder ... my work here is done --unknown

Ok, thanks Dinesh

Panic, Chaos, Disorder ... my work here is done --unknown

SQL 7 can definitely have more than 16 - must be 256 as well.

At the risk of starting up an old discussion, this might be an over-normalized database.

I recall now - this was a limitiation on one of the DB2 for MVS versions I worked on - about 5-6 years ago. iirc, views would allow you past that limitation.

Panic, Chaos, Disorder ... my work here is done --unknown

Guys,

Thanks for replying. Please try and not digress from the actual thread. I am doing work arounds (temp table) for not running into this issue right now (SQL Server 2000) and was just wondering if i need to do the same in SQL Server 2005.

Dinesh,

Thanks for pointing out on 256 (instead of 255) although it doesn't matter much.

Adrian,

Views cannot solve this problem as i have tried using them. It actually counts the underlying tables and not view.

Frank,
You are correct. Views are not a solution. Can you explain on what problems i will face if i have 255 tables in a query.

SQL_Guess,
I don't think your reply is related to what i asked. Still you are most welcome to share your experiences.

Thanks,
Amit

Dinesh,

If my design is working for me then you can't really say it is a problem. All i wanted to know was whether i can go beyond 256 tables or not.

I am able to manage all my queries even with such large table count. Actually the number is just logical for the query. Based on report duration only one or few tables are accessed at any point of time.

Amit

quote:Originally posted by amitm79
Based on report duration only one or few tables are accessed at any point of time.

Then why are all those other tables included in the query? Sounds like you have one query that you use everywhere, instead of several queries that you use where appropriate.

If that is the case, then you're causing yourself unnecessary headaches.

The only way I see you getting around your 255 limit is to split this into seperate, more manageable queries (say 50 tables at a time) and store the resultset in a temp table, and then have a query that can address the growing resultset, or split this in some other manner?

A 256+table join - would I be correct in guessing you are using dynamic SQL to generate this, or is it a static query?

Panic, Chaos, Disorder ... my work here is done --unknown

Adriaan,

I have a partitioned database and all partitions are encapsulated behind a view. Based on check constraints, the SQL query optimizer picks the correct table and fetches the data. The model has been working well for me.

Why should i be doing something which SQL query optimizer can do for me?

Amit

Amit,<br /><br />No doubt you know more about partitioning than me.[<img src='/community/emoticons/emotion-1.gif' alt='' />]<br /><br />Stupid question time: any chance of reducing the number of partitions?<br /><br />And if partitioning means you have to resort to temp tables to do any querying, then have you gained anything by partitioning?

... and for those still in suspense over the 16 table limit: that was the limit in Microsoft SQL Server 6.5.