Post reply

SQL Limitation

  • March 8, 2004 at 9:35 am

    #107932

    Is there a limitation to the number of tables used in a SQL query or a limitation in the number of times that one particular table can be used as an alais in a SQL query?

    Thanks

    Tina Poettker

  • March 8, 2004 at 9:50 am

    #497822

    Tables per select: 256

    For a comprenhensive list look for :Maximum Capacity Specifications in BOL

    HTH

     


    * Noel

  • March 8, 2004 at 9:57 am

    #497830

    If you hit this limit, you probably have other problems

  • March 9, 2004 at 2:38 am

    #497936

    My belief always has been that joining more than four tables at a time is likely to impact performance as well as maintainability - no evidence; just a belief! Can anyone verify or contradict this?

  • March 9, 2004 at 2:48 am

    #497938

    Quoted from Inside SQL Server 2000

    Multiple-Table Joins

    According to some folklore, SQL Server "does not optimize" joins of more than four tables. There was some truth to this in earlier versions of SQL Server, but in SQL Server 2000 optimization doesn't happen in the same way at all. There are so many different possible permutations of join strategies and index usage that the query optimizer goes through multiple optimization passes, each considering a larger set of the possible plans. On each pass, it keeps track of the best plan so far and uses a cost estimate to reduce the search in succeeding passes. If your query contains many tables, the query optimizer is likely to interrupt the last pass before completion because the last pass is quite exhaustive. The cheapest plan found will then be used.

    HTH

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • March 9, 2004 at 2:51 am

    #497939

    As I am using SQL Server 7, I'll stick to my 4-table guidline, then.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply