“Psssst….”

“Hey buddy! Yeah, you there in the propeller hat. You’re a SQL nerd, right? Can you help me tune this query? Sorry for the formatting, I generated this with my pointy-clicky development tool, and you know the kind of crap they generate. Anyway, I need you to review this and tell me if it can be improved.”

WITH [EMP_cte]([EmployeeID], [ManagerID], [FirstName], [LastName], 
[Title], [RecursionLevel]) AS (SELECT e.[EmployeeID], e.[ManagerID], 
c.[FirstName], c.[LastName], e.[Title], 0 FROM [HumanResources].[Employee] 
e INNER JOIN [Person].[Contact] c ON e.[ContactID] = c.[ContactID] WHERE 
e.[EmployeeID] = @EmployeeID UNION ALL SELECT e.[EmployeeID], 
e.[ManagerID], c.[FirstName], c.[LastName], e.[Title], [RecursionLevel] + 
1 FROM [HumanResources].[Employee] e INNER JOIN [EMP_cte] ON e.[EmployeeID] 
= [EMP_cte].[ManagerID] INNER JOIN [Person].[Contact] c ON e.[ContactID] 
= c.[ContactID]) SELECT [EMP_cte].[RecursionLevel], [EMP_cte].[EmployeeID], 
[EMP_cte].[FirstName], [EMP_cte].[LastName], [EMP_cte].[ManagerID], 
c.[FirstName] AS 'ManagerFirstName', c.[LastName] AS 'ManagerLastName' 
FROM [EMP_cte] INNER JOIN [HumanResources].[Employee] e ON 
[EMP_cte].[ManagerID] = e.[EmployeeID] INNER JOIN [Person].[Contact] c ON 
e.[ContactID] = c.[ContactID] ORDER BY [RecursionLevel], [ManagerID], 
[EmployeeID] OPTION (MAXRECURSION 25)

Sound familiar? How many times have you been handed an endless multi-line string of text claiming to be a SQL query, with the expectation that you can parse and comprehend what it’s doing?

It happens too many times in my opinion. In spite of my love for nHibernate, LINQ, and their brethren of SQL generating doodads, the code that they produce can be a nightmare to try to read, not to mention tune. Fortunately, I stumbled across a tool that does wonders for the readability of these things. It won’t help ‘em run better, but it will sure make ‘em look pretty. Kind of like a pig wearing lipstick.

Last weekend was SQL Saturday #238 here in Minneapolis. One of the sponsors of the event was a company that I’d never heard of before – ApexSQL. Curious, I looked them up after the event. Turns out that they have an impressive collection of SQL Server tools, some of which are totally free. One of those is ApexSQL Refactor, and that’s what I’m writing about today.

Remember that ugly string of T-SQL that you were asked to review?

WITH [EMP_cte]([EmployeeID], [ManagerID], [FirstName], [LastName], 
[Title], [RecursionLevel]) AS (SELECT e.[EmployeeID], e.[ManagerID], 
c.[FirstName], c.[LastName], e.[Title], 0 FROM [HumanResources].[Employee] 
e INNER JOIN [Person].[Contact] c ON e.[ContactID] = c.[ContactID] WHERE 
e.[EmployeeID] = @EmployeeID UNION ALL SELECT e.[EmployeeID], 
e.[ManagerID], c.[FirstName], c.[LastName], e.[Title], [RecursionLevel] + 
1 FROM [HumanResources].[Employee] e INNER JOIN [EMP_cte] ON e.[EmployeeID] 
= [EMP_cte].[ManagerID] INNER JOIN [Person].[Contact] c ON e.[ContactID] 
= c.[ContactID]) SELECT [EMP_cte].[RecursionLevel], [EMP_cte].[EmployeeID], 
[EMP_cte].[FirstName], [EMP_cte].[LastName], [EMP_cte].[ManagerID], 
c.[FirstName] AS 'ManagerFirstName', c.[LastName] AS 'ManagerLastName' 
FROM [EMP_cte] INNER JOIN [HumanResources].[Employee] e ON 
[EMP_cte].[ManagerID] = e.[EmployeeID] INNER JOIN [Person].[Contact] c ON 
e.[ContactID] = c.[ContactID] ORDER BY [RecursionLevel], [ManagerID], 
[EmployeeID] OPTION (MAXRECURSION 25)

To make this readable, you could spend 20 minutes adding line breaks, indenting, taking the occasional break to avoid carpal tunnel. Or, you could pipe this beast through ApexSQL Refactor, and in a matter of seconds find yourself looking at this:

WITH [EMP_cte]( EmployeeID , 
                ManagerID , 
                FirstName , 
                LastName , 
                Title , 
                RecursionLevel
              )
    AS ( SELECT e.EmployeeID , 
                e.ManagerID , 
                c.FirstName , 
                c.LastName , 
                e.Title , 
                0
           FROM
                HumanResources.Employee e INNER JOIN Person.Contact c ON e.ContactID
                                                                         = 
                                                                         c.ContactID
           WHERE e.EmployeeID
                 = 
                 @EmployeeID
         UNION ALL
         SELECT e.EmployeeID , 
                e.ManagerID , 
                c.FirstName , 
                c.LastName , 
                e.Title , 
                RecursionLevel + 1
           FROM
                HumanResources.Employee e INNER JOIN EMP_cte ON e.EmployeeID
                                                                = 
                                                                EMP_cte.ManagerID
                                          INNER JOIN Person.Contact c ON e.ContactID
                                                                         = 
                                                                         c.ContactID
       )
    SELECT EMP_cte.RecursionLevel , 
           EMP_cte.EmployeeID , 
           EMP_cte.FirstName , 
           EMP_cte.LastName , 
           EMP_cte.ManagerID , 
           c.FirstName AS 'ManagerFirstName' , 
           c.LastName AS 'ManagerLastName'
      FROM
           EMP_cte INNER JOIN HumanResources.Employee e ON EMP_cte.ManagerID
                                                           = 
                                                           e.EmployeeID
                   INNER JOIN Person.Contact c ON e.ContactID
                                                  = 
                                                  c.ContactID
      ORDER BY RecursionLevel , ManagerID , EmployeeID
      OPTION( MAXRECURSION 25
            );

Pretty cool, huh? Sure, there are SQL query formatting web sites that will do the same thing, but what if your employer has strict rules around the control of source code? You’re probably violating that rule by pasting your T-SQL code into such external tools. Plus, the few that I’ve used are somewhat limited, both in terms of the size of the text that you can format, or in how you can customize the formatting.

For example, one such site that I’ve used is Poorsql.com. It works, and works well, and in a pinch is a perfectly capable tool. It offers some flexibility in how it formats your code, but the options available to you are pretty limited.

ApexSQL Refactor, on the other hand, offers a staggering number of options.

Don’t like those stupid square brackets that always seem to show up around object names? Get rid of ‘em…

Can’t remember to terminate your statements with a semicolon? Have it done for you…

Tired of developers omitting the AS keyword in front of table aliases? Or not putting nested SELECT statements on their own lines? You can fix those too…

SQL query formatting is just the tip of the iceberg. There are a ton of other features built in to ApexSQL Refactor. I haven’t had time to try them all out yet, but some of these look more than mildly interesting.

Seriously folks – if it’s in your budget (you can afford FREE, right?), give this thing a try. You can download it here: http://www.apexsql.com/sql_tools_refactor.aspx

If you do give it a try, let me know in the comments what you think of it. I’m curious to see if anybody else finds it useful.

The post SQL Query Formatting – Putting Lipstick On A Pig appeared first on RealSQLGuy.