{"id":68606,"date":"2016-10-05T11:55:30","date_gmt":"2016-10-05T11:55:30","guid":{"rendered":"https:\/\/www.simple-talk.com\/?p=68606"},"modified":"2021-09-29T16:21:18","modified_gmt":"2021-09-29T16:21:18","slug":"devil-wrote-code","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/devil-wrote-code\/","title":{"rendered":"Who the Devil Wrote This SQL Code?"},"content":{"rendered":"
\"smalldevil\"<\/div>\n

Have you ever had that moment when you open up some T-SQL code and you involuntarily shout out, \u201cWho the devil wrote this SQL code?\u201d It\u2019s that moment when you spot code that\u2019s not just visually wrong, such as in putting commas in front of column names, but wrong because of the mistakes that people are going to make because of all the difficulty in reading, understanding and editing that code. There are horrific things that people do to their T-SQL that absolutely shouldn\u2019t happen, let\u2019s talk about them and the problems that they cause.<\/p>\n

Alphabet Soup<\/h2>\n

We\u2019ll look first at three, very simplified, examples of what I call alphabet soup. It basically consists of aliasing the tables using the alphabet and the order in which they were referenced: It is a strange habit:<\/p>\n

select a.Name,b.Name,c.Name from production.product as a \r\njoin production.ProductSubcategory as b\r\non a.productsubcategoryid = b.productsubcategoryid\r\njoin production.productcategory as c\r\non b.productcategoryid = c.productcategoryid\r\nwhere a.class = 'L'\r\n\r\nselect a.Name,b.Name,c.Name from production.productsubcategory as a \r\njoin production.Product as b\r\non a.productsubcategoryid = b.productsubcategoryid\r\njoin production.productcategory as c\r\non a.productcategoryid = c.productcategoryid\r\nwhere a.productcategoryid = 4\r\n\r\nselect a.Name,b.Name,c.Name from production.productcategory as a \r\njoin production.productsubcategory as b\r\non b.productcategoryid = a.productcategoryid\r\njoin production.product as c\r\non b.productsubcategoryid = c.productsubcategoryid\r\nwhere a.name like 'C%'\r\n<\/pre>\n
You are going to cause all sorts of problems if you write code like this. First off, if you really do have these three queries within the same stored procedure, how hard will it be to confuse which is table \u2018a\u2019 in each of the queries when you go back to edit them? Pretty easy.<\/p>\n
It gets worse though. I know that none of us will ever write a query that exceeds 26 tables in a JOIN\u2026 well, except that one time\u2026 and that other time. In fact, it happens. Oh, it\u2019s not always a good thing, but it\u2019s a thing. How do we respond to that? I\u2019ve seen this:<\/p>\n
select a.Name,b.Name,c.Name,A.OrderQty from production.product as a \r\njoin production.ProductSubcategory as b\r\non a.productsubcategoryid = b.productsubcategoryid\r\njoin production.productcategory as c\r\non b.productcategoryid = c.productcategoryid\r\n...\r\njoin sales.salesorderdetail as A\r\n...\r\n<\/pre>\n
Yeah, turn on case-sensitivity and start referring to tables \u2018a\u2019 and \u2018A\u2019. What could ever go wrong there? What happens when you exceed 52 tables? Or when people do this:<\/p>\n
select a.Name,b.Name,c.Name,aaa.OrderQty from production.product as a \r\njoin production.ProductSubcategory as b\r\non a.productsubcategoryid = b.productsubcategoryid\r\njoin production.productcategory as c\r\non b.productcategoryid = c.productcategoryid\r\n...\r\njoin sales.salesorderdetail as aaa\r\n...\r\n<\/pre>\n
Yeah, because table \u2018aaa\u2019 is going to be so easy to discern from \u2018aa\u2019 and \u2018a\u2019. At least we don\u2019t have to worry about running out of letters, \u2018aaaaaaa\u2019 table is so clear.<\/p>\n
This approach does nothing but cause confusion and a lack of clarity. I absolutely believe that we must use aliases, if for no other reason, I\u2019m lazy and I want to be able to type less while still referring to object owners. Instead of alphabet soup, let\u2019s name aliases in a meaningful fashion:<\/p>\n
SELECT  p.Name,\r\n        pc.Name,\r\n        ps.Name\r\nFROM    Production.Product AS p\r\nJOIN    Production.ProductSubcategory AS ps\r\n        ON ps.ProductSubcategoryID = p.ProductSubcategoryID\r\nJOIN    Production.ProductCategory AS pc\r\n        ON pc.ProductCategoryID = ps.ProductCategoryID\r\nWHERE   p.Class = 'L';\r\n<\/pre>\n
Not only is it easy to discern which table is which in each of the queries, but the consistency makes the code that much more readable. You will have fewer errors and better understanding of what the code is doing.<\/p>\n
Object Owners<\/h2>\n
While we\u2019re on the subject, there is no excuse for code like this:<\/p>\n
SELECT  ExpectedDeliveryDate,\r\n        OrderDate,\r\n        LastReceiptDate,\r\n        pol.LastEditedWhen,\r\n        ValidFrom,\r\n        ValidTo\r\nFROM    Purchasing.PurchaseOrders AS po\r\nJOIN    Purchasing.PurchaseOrderLines AS pol\r\n        ON pol.PurchaseOrderID = po.PurchaseOrderID\r\nJOIN    Application.People\r\n        ON PersonID = ContactPersonID\r\nWHERE LastReceiptDate > '1\/1\/2016';\r\n<\/pre>\n
There are a couple of problems here, and they will cause you lots of headaches. First off, which table does OrderDate<\/strong> belong to? Can\u2019t tell, can you? Next, why doesn\u2019t Application.People<\/strong> have an alias? Because it doesn\u2019t need one. See, they only put aliases on tables that have columns that might repeat in the query. For example, many of the tables have a LastEditedWhen<\/strong> column, so it needs an owner to distinguish it. The Same thing goes for PurchaseOrderID<\/strong> which is in more than one table, so they supply aliases or, in some cases not use aliases but completely retype the column name.<\/p>\n
This kind of inconsistency makes lots of retyping and re-editing necessary. As soon as you add in the LastEditedWhen<\/strong> column from the People table, you\u2019ll have to either add an alias to that table or supply the full table name where that column is referenced. Either way, you\u2019re going to be doing a lot more typing to get your code to work correctly. Let\u2019s also note that we\u2019re adding a little bit of work to SQL Server because it will have to go and find each of the objects that don\u2019t have specific owners. Yes, that amount of work is vanishingly miniscule, but it\u2019s extra work that it could be doing on your queries.<\/p>\n
Let\u2019s just do this:<\/p>\n
SELECT  po.ExpectedDeliveryDate,\r\n        po.OrderDate,\r\n        pol.LastReceiptDate,\r\n        pol.LastEditedWhen,\r\n        p.ValidFrom,\r\n        p.ValidTo\r\nFROM    Purchasing.PurchaseOrders AS po\r\nJOIN    Purchasing.PurchaseOrderLines AS pol\r\n        ON pol.PurchaseOrderID = po.PurchaseOrderID\r\nJOIN    Application.People AS p\r\n        ON p.PersonID = po.ContactPersonID\r\nWHERE pol.LastReceiptDate > '1\/1\/2016';\r\n<\/pre>\n
With over 10,000 executions of each query on my system, the average for the query without object ownership was 129ms while the average for the query with object ownership was 115ms. The difference was the work required to discern object owners.<\/p>\n
Statement Terminators<\/h2>\n
Oh, I get it. Semi-colons are syntactic sugar. Sure they are. Well, as long as you don\u2019t count the fact that more and more statements are now requiring sugary icing in order to even function. What am I talking about? This horror:<\/p>\n
;WITH    x AS (SELECT    bg.BuyingGroupName,\r\n                        c.CustomerID\r\n              FROM      Sales.BuyingGroups AS bg\r\n              JOIN      Sales.Customers AS c\r\n                        ON c.BuyingGroupID = bg.BuyingGroupID\r\n             )\r\n    SELECT  x.BuyingGroupName,\r\n            COUNT(x.CustomerID)\r\n    FROM    x\r\n    GROUP BY x.BuyingGroupName\r\n<\/pre>\n
The semi-colon is a statement terminator. Terminator, the end of something, like, I don\u2019t know, Sarah Connor. No, I mean the end of the statement. So why is it going at the start of the statement above? Because people kept getting this error:<\/p>\n
Msg 336, Level 15, State 1, Line 96\r\nIncorrect syntax near 'x'. If this is intended to be a common table expression, you need to explicitly terminate the previous statement with a semi-colon.\r\n<\/pre>\n
Rather than change their code to start using terminators, they threw it at the front of the WITH statement. Heck, I\u2019ve seen internal documents for companies that have declared that a coding standard. Since this is only for a CTE, what the heck, we don\u2019t really have to worry about just doing that, right? Put the semi-colon just in front of the WITH statement and we\u2019re all good\u2026 as long as we\u2019re not using Service Broker, because those commands require it too. Oh who cares, no one is using Service Broker. Just so long as you don\u2019t need to ENABLE\/DISABLE a trigger, ever. Oh, and use the THROW command or MERGE\u2026<\/p>\n
Microsoft is requiring the semi-colon in more and more statements. You\u2019re going to hit issues if you don\u2019t use it as a statement-terminator in all your statements. Yes, older code can still work, but as you add newer functions, you\u2019re going to hit the issue. Eventually they\u2019re going to require the terminator for all statements, just start writing it that way now and avoid the issue.<\/p>\n
Single Line Queries<\/h2>\n
I see these all the time and I just wonder, how on earth people manage to get their tasks\u00a0done when they\u2019re looking at this:<\/p>\n
SELECT po.ExpectedDeliveryDate, po.OrderDate, pol.LastReceiptDate, pol.LastEditedWhen, p.ValidFrom, p.ValidTo FROM Purchasing.PurchaseOrders AS po JOIN Purchasing.PurchaseOrderLines AS pol ON pol.PurchaseOrderID = po.PurchaseOrderID JOIN Application.People AS p ON p.PersonID = po.ContactPersonID WHERE LastReceiptDate > '1\/1\/2016';<\/pre>\n
Seriously? It\u2019s a single line in SSMS. It\u2019s a tiny query and it\u2019s practically unreadable. No wonder people have a hard time query tuning, they don\u2019t know what they\u2019re looking at. It\u2019s all one amorphous blob. Approaching coding like this will absolutely slow you down and lead to errors. You can\u2019t help but mess up in a situation like this. For example, did you notice that there is a missing object owner up there? I put it near the end so you might be able to catch it. Imagine how much harder that would be if it was somewhere in the middle of that mess.<\/p>\n
What should we use instead? Line breaks. I actually don\u2019t care where, that much, but somewhere. Please. There are multiple studies on how white space enables readability and provides a mechanism for focus. A consistent set of line breaks and maybe some spaces or tabs will provide a common structure that you can easily communicate to your team, making all your code that much easier to read, making it easier to avoid errors or fix them.<\/p>\n
Too Much White Space<\/h2>\n
On the other hand, it\u2019s entirely possible to have too much of a good thing. It\u2019s like the development team that was told a query hint would help this one query, this one time, and all they heard was that this query hint made queries run faster, so they put it everywhere. Others hear that white space makes things more readable, so we end up stuff like this:<\/p>\n
<\/p>\n
At least it has a semi-colon.<\/p>\n
I\u2019m using a screen capture on this one so you can see how line 12 shoots out to character 119 on the far side of the screen. Thankfully this is a very small and simple query otherwise I\u2019d be scrolling all over creation to try to read it. This suffers from the same problems as the previous example, but instead of no white space, this is ALL THE WHITE SPACE. Not only is this query hard to read, and therefore hard to understand, you can easily think you\u2019ve moved between queries at a certain point.<\/p>\n
Abnormal Characters<\/h2>\n
When you crack open a database and you see this, do you let slip some expletives before you regain control, causing yet another trip to HR, because it sure does happen to me:<\/p>\n
CREATE TABLE dbo.[Table Name]\r\n([IDENTITY] INT IDENTITY(1,1) NOT NULL,\r\n[SELECT] VARCHAR(25) NULL,\r\nRelativelyNormalColumn DATETIME2 NULL,\r\n\"[NOT NULL]\" varchar(50) NOT NULL);\r\n<\/pre>\n
Oh yeah, please, this is just what I need to see on a Friday afternoon when there\u2019s a performance problem in a query. We have a mixed case of restricted characters, the space in the name of the table, [Table Name], reserved words, IDENTITY, SELECT, NOT, NULL, and, oh, just to add insult to injury, we\u2019re using quotation marks around the object name so that the name can contain brackets.<\/p>\n
From this point forward, we\u2019re in hell. Some of you actually like using brackets around all the object names in T-SQL. Heck, the SSMS scripting tool does it by default (because, you guys and your poor choices force it to). However, admit it, you don\u2019t want to look at this any more than I do:<\/p>\n
SELECT  [po].[ExpectedDeliveryDate],\r\n        [po].[OrderDate],\r\n        [pol].[LastReceiptDate],\r\n        [pol].[LastEditedWhen],\r\n        [p].[ValidFrom],\r\n        [p].[ValidTo]\r\nFROM    [Purchasing].[PurchaseOrders] AS po\r\nJOIN    [Purchasing].[PurchaseOrderLines] AS pol\r\n        ON [pol].[PurchaseOrderID] = [po].[PurchaseOrderID]\r\nJOIN    [Application].[People] AS p\r\n        ON [p].[PersonID] = [po].[ContactPersonID]\r\nWHERE [pol].[LastReceiptDate] > '1\/1\/2016';\r\n<\/pre>\n
Seriously, you want to type [p] over and over instead of just p? And you\u2019re OK that you ALIAS to po<\/strong>, but then you have to reference it as [po]<\/strong>? The clutter is insane, but the inconsistency is worse. That\u2019s before we get to the issues that can be caused by using the quotes around, not only reserved words, but special characters such as brackets. Now, we can\u2019t even have a consistent use of brackets, but instead are forced to write our SELECT statement against the [Table Name]<\/strong> table like this:<\/p>\n
SELECT [tn].\"[NOT NULL]\",\r\n   [tn].[IDENTITY],\r\n   [tn].[SELECT],\r\n   [tn].[RelativelyNormalColumn] \r\nFROM dbo.[Table Name] AS tn;\r\n<\/pre>\n
Even if we wanted to use brackets, we can\u2019t. This will end in tears, not counting what I\u2019m going to do. What do we do instead? Follow the rules for Identifiers as laid out by Microsoft. Just because you can do something, doesn\u2019t mean you should. As your mom always said, \u201cJust because all your friends are using non-standard characters in T-SQL are you going to get shoved off that cliff by Grant too?\u201d<\/p>\n
Conclusion<\/h2>\n
I understand. You\u2019re in a hurry. The niceties of coding well are not necessarily always going to be observed because\u2026 No, actually I don\u2019t understand. There are simple and clear standards out there. Following them doesn\u2019t add time, especially if you use a tool like SQL Prompt<\/a> to help you do it. In fact, following them makes for greater clarity and understanding, which speeds things up. Avoiding errors and eliminating non-standard approaches is a performance enhancing mechanism. So, no, I don\u2019t understand. You\u2019re in a hurry, so do it right the first time.\u00a0<\/p>\n
And put your commas at the end of the line like a good pagan.<\/p>\n
References<\/h2>\n
    \n
  1. Cleaning up common T-SQL coding issues with SQL Prompt: Grant Fritchey<\/a><\/li>\n
  2. Formatting SQL Code – Part the First: Joe Celko<\/a><\/li>\n
  3. Formatting SQL Code – Part the Second: Joe Celko<\/a><\/li>\n
  4. Transact-SQL Formatting Standards (Coding Styles): Robert Sheldon<\/a><\/li>\n
  5. SQL Code Layout and Beautification: William Brewer<\/a><\/li>\n
  6. Laying Out SQL Code: Phil Factor<\/a><\/li>\n
  7. How Should You Lay Our Code: Tony Davis<\/a><\/li>\n
  8. T-SQL Formatting: A Sure-Fire Way to Start a Conversation: Jonathan Allen<\/a><\/li>\n<\/ol>\n","protected":false},"excerpt":{"rendered":"
    The way that you format T-SQL code can affect the productivity of the people who have to subsequently maintain your work.  It is never a good experience to see SQL Code, cry out  \u201cWho the devil wrote this code?\u201d, and then realise that it was you. Grant gives some examples of bad formatting and explains why you should never check-in badly-formatted SQL code.…<\/p>\n","protected":false},"author":221792,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143531],"tags":[45110],"coauthors":[6785],"class_list":["post-68606","post","type-post","status-publish","format-standard","hentry","category-t-sql-programming-sql-server","tag-t-sql-formatting"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/68606","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221792"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=68606"}],"version-history":[{"count":15,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/68606\/revisions"}],"predecessor-version":[{"id":73445,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/68606\/revisions\/73445"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=68606"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=68606"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=68606"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=68606"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}