SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Do you write ugly T-SQL?

twittergoogle_plusredditlinkedinmail

Are your scripts littered with commented blocks of T-SQL code?

select fname,lname 
from usertable
where id=1
--and lname like 'smith%'
/*
-- Who knows why this block is commented out
select fname,lname 
from usertable
where id=1
*/

Do you just start typing and rely on word wrap to fit your query into the SSMS query window?

select users.fname,users.lname,users.address,users.city,users.state,users.zip,
users.phone,users.email from users inner join managers on managers.id = 
users.id where id=1 and manager.lname = 'smith'

Do you omit whitespace in your queries in an effort to save space?

SELECT fname,lname FROM users WHERE lname='smith'


If any or all of those cases define your scripts then yes, I may just be talking to you.

“Chris, Surely you jest!” No I am being totally serious, and stop calling me Shirley! I will admit that this sounds nitpicky, but chances are many of us haven’t even thought about how our code looks and why it might matter. We’re just happy that it runs most of the time, right?

Consistent formatting enhances the readability of your code. If you are part of a team, there will come a time when you have to support each other’s code. There is also a good chance that you will write a piece of code and not have to look at it again for years. If you are a solo DBA you may think that formatting doesn’t matter because you are the only one who has to support it, but you couldn’t be more wrong. I will say it again, consistent formatting enhances the readability of your code. Developers have known this and been following formatting standards for quite some time. It’s typically a part of their peer review processes because it’s that important to them.

Take these 2 examples of the exact same T-SQL code. I promise you they are identical except for the formatting.

select case
when ( exists (
select 1 AS [C1] from [dbo].[MyTable] AS [Extent1]
WHERE (((UPPER([Extent1].[Column1])) = (UPPER(@Column1))) OR ((UPPER([Extent1].[Column1]) IS NULL)
AND (UPPER(@Column1) IS NULL)
))AND (([Extent1].[Column2] = @Column2)
OR (
([Extent1].[Column2] IS NULL) AND (@Column2 IS NULL)
)or ([Extent1].[Column2] = @Column3) OR (([Extent1].[Column2] IS NULL)
and (@Column3 IS NULL)
)
)AND ([Extent1].[Column4] <> @Column4)))
then cast(1 AS BIT) else cast(0 AS BIT)
end AS [C1]
FROM (
SELECT 1 AS X
) AS [SingleRowTable1]

SELECT CASE 
  WHEN (
    EXISTS (
     SELECT 1 AS [C1]
     FROM [dbo].[mytable] AS [Extent1]
     WHERE (
       ((Upper([Extent1].[column1])) = (Upper(@Column1)))
       OR (
        (Upper([Extent1].[column1]) IS NULL)
        AND (Upper(@Column1) IS NULL)
        )
       )
      AND (
       ([Extent1].[column2] = @Column2)
       OR (
        ([Extent1].[column2] IS NULL)
        AND (@Column2 IS NULL)
        )
       OR ([Extent1].[column2] = @Column3)
       OR (
        ([Extent1].[column2] IS NULL)
        AND (@Column3 IS NULL)
        )
       )
      AND ([Extent1].[column4] <> @Column4)
     )
    )
   THEN Cast(1 AS BIT)
  ELSE Cast(0 AS BIT)
  END AS [C1]
FROM (
 SELECT 1 AS X
 ) AS [SingleRowTable1]

Which one would you prefer to read at 3:00 AM when the application is down?

Honestly the hardest part is getting the team to agree on the formatting standards (Commas before or after column names, Keywords upper or lower case, Indentation using tabs or spaces, Break join statements, etc.). Actual formatting of the code couldn’t be simpler really. There are plenty of tools that do it for us. Here are a few examples (I use the poorsql plugin for SSMS and I love it).

Commercial Tool: http://www.red-gate.com/products/sql-development/sql-prompt/
Free Tool with an SSMS plugin: http://poorsql.com/
ApexSQL Refactor is another great free tool: https://www.apexsql.com/sql_tools_refactor.aspx
Online Tool: http://www.dpriver.com/pp/sqlformat.htm

So if you are not already using some sort of formatting standard for your T-SQL code I highly recommend you start. A consistent look and feel helps you understand what you are looking at more quickly, which is especially important at 3:00 AM.

twittergoogle_plusredditlinkedinmail

cjsommer.com

Chris started in computing and I.T. in the mid to late 1990's. After the dust had settled from Y2K, he found himself a bit more focused and working with database servers on the Unix platform. His first exposure to SQL Server was in 2007 and he has been working on that platform ever since. Chris is currently a Sr. SQL Server DBA in more of a DevOps role with a main focus on operational reliability, stability and performance. His specialty is automation. He truly enjoys the days he gets to spend building tools with PowerShell to help his fellow DBA's manage their ever growing SQL Server environment.

Comments

Leave a comment on the original post [www.cjsommer.com, opens in a new window]

Loading comments...