THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

John Paul Cook

Using the Query Designer to convert non-ANSI joins to ANSI

If you have legacy code that you are upgrading, the Query Designer can convert old style joins to ANSI joins. You can invoke the Query Designer from the Query menu in the SSMS toolbar or by pressing the Ctrl Shift Q three key combination, which is what I recommend. If you select the query you want to convert before pressing Ctrl Shift Q, the query will already be in the Query Designer window when it pops up.

Here's some sample code to experiment with:

create table table1 (
    a
int
   ,b
int
);

create table table2 (
   a
int
  ,b
int
);

create table table3 (
    a
int
   ,b
int
);

insert into table1 (a,b) values (1,2);
insert into table2 (a,b) values (1,2
);
insert into table3 (a,b) values (1,2);

insert into table1 (a,b) values (11,12);
insert into table2 (a,b) values (21,22
);
insert into table3 (a,b) values (31,32);

-- Select the following query and then press Ctrl Shift Q to invoke the Query Designer

select t1.a as t1a, t2.b as t2b, t3.a as t3a, t3.b as t3b
from table1 t1, table2 t2, table3
t3
where t1.a = t2.
a
and t2.b = t3.b;

image

Figure 1. Query Designer window with non-ANSI joins converted to ANSI joins.

-- Select the following query and then press Ctrl Shift Q to invoke the Query Designer

select t1.a as t1a, t2.b as t2b, t3.a as t3a, t3.b as t3b
from table1 t1, table2 t2, table3
t3
where t1.a = t2.
a
and t2.b = t3.
b
and t1.a =
1
and t2.b =
2
and (t3.a = 1 or t3.b = 2);

image

Figure 2. Notice that the WHERE clause has more predicates than we started with because of the OR.

-- Don't forget to clean up your mess!

drop table table1;
drop table table2
;
drop table table3;

The Query Designer does a good job converting the first select statement. It doesn't do such a great job on the second select. The OR clause causes the Query Designer to introduce unnecessary complexity into the code.

I did real world performance testing of code matching the general pattern of the example select statement shown above having the OR clause. The generated code with the OR clause was less efficient than code I manually converted to ANSI inner joins. Original code without an OR in a compound predicate was fine.

Used with your critical thinking skills, Ctrl Shift Q can help you when refactoring legacy code. It works well most of the time. Hopefully you now have an understanding of when it might be appropriate and when it might not be.

Published Saturday, March 02, 2013 1:35 PM by John Paul Cook

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit

About John Paul Cook

John Paul Cook is a Technology Solutions Professional for Microsoft's data platform and works out of Microsoft's Houston office. Prior to joining Microsoft, he was a Microsoft SQL Server MVP. He is experienced in Microsoft SQL Server and Oracle database application design, development, and implementation. He has spoken at many conferences including Microsoft TechEd and the SQL PASS Summit. He has worked in oil and gas, financial, manufacturing, and healthcare industries. John is also a Registered Nurse who graduated from Vanderbilt University with a Master of Science in Nursing Informatics and is an active member of the Sigma Theta Tau nursing honor society. He volunteers as a nurse at safety net clinics. Contributing author to SQL Server MVP Deep Dives and SQL Server MVP Deep Dives Volume 2. Opinions expressed in John's blog are strictly his own and do not represent Microsoft in any way.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement