Q&A

Q What is the maximum number of tables I can join in a single query?

A The legal limit is 16 tables, but the practical limit is often much less. There are plenty of queries that run well with 8, 10, or even 12 tables. Good performance often depends on the characteristics of those tables and the way in which the tables are joined.

The optimizer analyzes tables in groups, considering possible join orders four tables at a time. For joins consisting of more than four tables, the server decides on a join order using an iterative process. First, the server tries to find a useful outer table, followed by a useful second table, and so on, until only four tables are left to analyze. At that point, it considers all possible join orders of those four tables and develops a final optimization plan.

The problem with the iterative approach is that the server cannot see a large multitable query through to completion at optimization time. Errors can creep into the estimation process and result in a poor optimization plan.

What’s more, the optimizer can’t always find a good solution, and sometimes a good solution just doesn’t exist. (A good solution means one that performs well. Except when you stumble across a bug in SQL Server, you will always get a correct result. It just might take a while.) In cases where there is no good solution, you might need to break up the query into parts, building a temporary table from some of the tables, and then joining that table back to other tables in the query. This will depend on your knowledge of the data and some trial and error.

Q What’s reformatting?

A Sometimes SQL Server can’t resolve a query using the existing indexing resources, or it decides that its own clustered index or temporary result set will work better than a typical join given the table structure. In that case, the server can reformat the query. In the showplan output, the server reports reformatting as a separate step in the query plan, like this:

STEP 1
The  type  of  query  is  INSERT
The  update  mode  is  direct
Worktable  created  for  REFORMATTING

If important queries based on a particular table are consistently being reformatted, try adding the index the server seems to be looking for. Often, reformatting takes time and can result in poor query performance. (When it reformats, the server is creating a nonreusable index for every query. If the same index were available, it wouldn’t need to create it, so the query would have to be faster.) Sometimes, the only way to stop reformatting is to change your clustered index. You will have to decide if it’s worth it.

Q Is there a right outer join?

A Yes, there are left, right, and full outer joins. The right outer join simply forces all the rows to appear from the right-hand table in the join relationship rather than the left.

Q Are there other types of self-joins?

A The other major category of self-joins looks for matching data values. To find matching values on a column, you set up an equality join between the tables on that column. For example, here is a query that displays the names of authors living in the same city:

select    a1.au_lname,       a2.au_lname,   a1.city
     from  authors  a1, authors  a2
    where  a1.city  =  a2.city
      and  a1.id  >  a2.id

With matching self-joins, alias names don’t matter—the two tables are indistinguishable. The a1.city column is drawn from the authors table, a1, but it could just as well be drawn from the second version, a2. Notice that the query excludes matches where the IDs are the same: rows should not match each other.

Matching queries can be useful for finding possible duplicate data, but otherwise they are seldom used in production systems.

Q Can I join other tables with a self-join?

A Yes, a self-join can be used as part of a larger query involving many tables, and it can be used in combination with any other SQL syntax.

Workshop

The Workshop provides quiz questions to help you solidify your understanding of the material covered, as well as exercises to provide you with experience in using what you’ve learned. Try to understand the quiz and exercise answers before continuing on to the next day’s lesson. Answers are provided in the appendix.

Quiz

1.  How many times does SQL Server scan the outer table of a two-table query?

2.  How many times does SQL Server scan the inner table of a two-table query?

3.  How are indexes used in join queries?

4.  Why might an outer join be bad for performance?

5.  True or false: When performing a self-join, SQL Server must create two copies of the data and then join those two copies.

Exercises

1.  Write a query in the pubs database to show all the authors and the titles they have written. Include in the output authors who have not written books.

2.  The result set from the exercise at the end of yesterday’s lesson was missing data values for several months because there were no sales in several of the months of the year. Here’s that result set:

Month                           $7066   $7067         $7131
--------------------    -----------     -----------      -----------
May                            50       0             85
June                          0         80            0
September                     75        10            45

How could you force the nine missing months into that report?