SQL Server Performance

255 Tables in a query

Discussion in 'SQL Server 2005 General Developer Questions' started by amitm79, Aug 13, 2006.

  1. amitm79 New Member

    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
  2. dineshasanka Moderator

    In SQL Server 2000, limitation is 256 not 255.( will not make big difference)


    ----------------------------------------

  3. Adriaan New Member

    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.
  4. FrankKalis Moderator

    I think you can't fool SQL Server this way. 256 is still the limit no matter if "hidden" in a view or not:<a target="_blank" href=http://msdn2.microsoft.com/en-us/library/ms143432.aspx>http://msdn2.microsoft.com/en-us/library/ms143432.aspx</a><br /><br />But a view will definitely improve readability. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  5. Adriaan New Member

    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.
  6. FrankKalis Moderator

    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
  7. SQL_Guess New Member

    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
  8. dineshasanka Moderator

  9. SQL_Guess New Member

    Ok, thanks Dinesh

    Panic, Chaos, Disorder ... my work here is done --unknown
  10. Adriaan New Member

    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.
  11. SQL_Guess New Member

    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
  12. amitm79 New Member

    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

  13. dineshasanka Moderator

    As Frank Said, if you want to run a query with 256 tables, then some issue with the db design. consider changing the db design

    ----------------------------------------

  14. FrankKalis Moderator

    quote:
    Can you explain on what problems i will face if i have 255 tables in a query.
    You will receive an error message when trying to run such a query.

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

    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
  16. Adriaan New Member

    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.
  17. dineshasanka Moderator

    What do you mean by
    Based on report duration only one or few tables are accessed at any point of time.

    in this case you will not be able to gain much performance.

    ----------------------------------------

  18. SQL_Guess New Member

    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
  19. amitm79 New Member

    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
  20. Adriaan New Member

    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?
  21. Adriaan New Member

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

Share This Page