How creative are you with manipulating your queries to produce more efficient plans? Try the following puzzle and e-mail your solution to me at [<do_not_mail> @ do_not_mail.com]. Make sure to include an explanation of why it works, as well as your mailing address. The best two solutions/explanations win a free copy of Expert SQL Server 2005 Development, a wonderful feeling of accomplishment, plus eternal fame and glory when I reveal your solutions here on the blog.
Run the following T-SQL to create two tables in TempDB:
CREATE TABLE b1 (blat1 CHAR(5) NOT NULL)
CREATE TABLE b2 (blat2 VARCHAR(200) NOT NULL)
SELECT LEFT(AddressLine1, 5) AS blat1
SELECT AddressLine1 AS blat2
Now consider the following query:
JOIN b2 ON
b2.blat2 LIKE b1.blat1 + '%'
This query takes around three minutes to run on my notebook, and does over 1.8 million logical reads. Can you figure out a way to re-write it so that it performs better? No modification of the base tables or addition of any other objects is allowed (sorry, no indexed views!) -- the challenge is to tune this by doing nothing more than re-writing the query.
Good luck! I'll leave the contest open for submissions until May 1.