{"id":160,"date":"2018-05-17T00:31:34","date_gmt":"2018-05-16T22:31:34","guid":{"rendered":"https:\/\/sql.marcus-belz.de\/?p=160"},"modified":"2020-04-20T00:10:45","modified_gmt":"2020-04-19T22:10:45","slug":"structuring-and-formatting-sql-statements","status":"publish","type":"post","link":"https:\/\/sql.marcus-belz.de\/?p=160","title":{"rendered":"Structuring and Formatting SQL Statements"},"content":{"rendered":"<p><a href=\"https:\/\/sql.marcus-belz.de\/?p=158\" target=\"_blank\" rel=\"noopener noreferrer\">Dieser Artikel in Deutsch&#8230;<\/a><\/p>\n<h2>Overview<\/h2>\n<p>Recently, I had to revise a Stored Procedure with nearly 2.000 lines of code. The procedure was edited by three developers, one after another. It contained sub statements for about 30 tables with varying degrees of complexity. But essentially all Sub-Statements were designed to fulfill a similar task. While all contributions followed a more or less general style it was obvious that each developer coded his or her statements in a slightly different style. In fact, there were even deviations within the work of a single developer. Due to the different styles and coding techniques it was far from being easy to read and understand the statements for 30 tables. Different styles and coding techniques did have an impact on the readability and maintainability of the procedure.<\/p>\n<ul>\n<li>Did the developers use delimiters for object names?<\/li>\n<li>Was there a coding convention with respect to using small or capital letters for key words or functions, etc.?<\/li>\n<li>Were there inline comments available on each change of an entity?<\/li>\n<li>What about using temporary tables vs table variables?<\/li>\n<li>\u2026<\/li>\n<\/ul>\n<p>In the past 20 years of my work as a Consultant, I found it quite often difficult to achieve a common-sense concerning structuring and formatting code. With each developer having found his or her very individual coding style, it appears to be a hard piece of work for everyone agreeing on and applying a common style guide.<\/p>\n<p>A review of another procedure (that was developed by a highly appreciated colleague) found that it was structured and formatted in a way that was very different from my preferences. Without thinking of the consequences, I reformatted the procedure while reviewing the code. The code itself was just perfect. The check-in process marked the procedure as being modified although there was no functional modification. Comparing the previous version with the reformatted version of course produced a bunch of differences between both versions, all due to reformatting the code and with no functional modification in place.<\/p>\n<p>Any criticism was more than justified.<\/p>\n<p>As SQL Server Management Studio does not offer support for a halfway reasonable structuring and formatting of SQL statements it is up to developers to defining a coding style guide and \u2013 of course \u2013 to adhering to this coding convention. With that a project team will achieve sufficient readability and maintainability of SQL Server artifacts.<\/p>\n<p>This series of articles collects best practices with respect to structuring and formatting of SQL statements. It covers the following issues:<\/p>\n<ul>\n<li><a href=\"https:\/\/sql.marcus-belz.de\/?p=236\" target=\"_blank\" rel=\"noopener noreferrer\">Editor Options in SSMS<\/a><\/li>\n<li><a href=\"https:\/\/sql.marcus-belz.de\/?p=358\" target=\"_blank\" rel=\"noopener noreferrer\">Formatting SQL Statements (Part 1)<\/a><\/li>\n<li><a href=\"https:\/\/sql.marcus-belz.de\/?p=465\" target=\"_blank\" rel=\"noopener noreferrer\">Formatting SQL Statements (Part 2)<\/a><\/li>\n<li><a href=\"https:\/\/sql.marcus-belz.de\/?p=584\" target=\"_blank\" rel=\"noopener noreferrer\">Commenting a complex SQL Statements<\/a><\/li>\n<li>Structuring Stored Procedures an Functions, etc.<\/li>\n<li>Commenting Stored Procedures an Functions, etc.<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>Dieser Artikel in Deutsch&#8230; Overview Recently, I had to revise a Stored Procedure with nearly 2.000 lines of code. The procedure was edited by three developers, one after another. It contained sub statements for about 30 tables with varying degrees <a href=\"https:\/\/sql.marcus-belz.de\/?p=160\" class=\"read-more\">Read More &#8230;<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4,6],"tags":[8,13,10],"class_list":["post-160","post","type-post","status-publish","format-standard","hentry","category-all-languages","category-english","tag-format","tag-ssms-options","tag-t-sql"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.4 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>Structuring and Formatting SQL Statements - Just another SQL blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/sql.marcus-belz.de\/?p=160\" \/>\n<meta property=\"og:locale\" content=\"de_DE\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Structuring and Formatting SQL Statements - Just another SQL blog\" \/>\n<meta property=\"og:description\" content=\"Dieser Artikel in Deutsch&#8230; Overview Recently, I had to revise a Stored Procedure with nearly 2.000 lines of code. The procedure was edited by three developers, one after another. It contained sub statements for about 30 tables with varying degrees Read More ...\" \/>\n<meta property=\"og:url\" content=\"https:\/\/sql.marcus-belz.de\/?p=160\" \/>\n<meta property=\"og:site_name\" content=\"Just another SQL blog\" \/>\n<meta property=\"article:published_time\" content=\"2018-05-16T22:31:34+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2020-04-19T22:10:45+00:00\" \/>\n<meta name=\"author\" content=\"marcus\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Geschrieben von\" \/>\n\t<meta name=\"twitter:data1\" content=\"marcus\" \/>\n\t<meta name=\"twitter:label2\" content=\"Gesch\u00e4tzte Lesezeit\" \/>\n\t<meta name=\"twitter:data2\" content=\"2\u00a0Minuten\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/sql.marcus-belz.de\\\/?p=160#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sql.marcus-belz.de\\\/?p=160\"},\"author\":{\"name\":\"marcus\",\"@id\":\"https:\\\/\\\/sql.marcus-belz.de\\\/#\\\/schema\\\/person\\\/7b46a383907dc48ca44fae32ceb24744\"},\"headline\":\"Structuring and Formatting SQL Statements\",\"datePublished\":\"2018-05-16T22:31:34+00:00\",\"dateModified\":\"2020-04-19T22:10:45+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/sql.marcus-belz.de\\\/?p=160\"},\"wordCount\":441,\"keywords\":[\"Format\",\"SSMS options\",\"T-SQL\"],\"articleSection\":[\"All Languages\",\"English\"],\"inLanguage\":\"de\"},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/sql.marcus-belz.de\\\/?p=160\",\"url\":\"https:\\\/\\\/sql.marcus-belz.de\\\/?p=160\",\"name\":\"Structuring and Formatting SQL Statements - Just another SQL blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sql.marcus-belz.de\\\/#website\"},\"datePublished\":\"2018-05-16T22:31:34+00:00\",\"dateModified\":\"2020-04-19T22:10:45+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/sql.marcus-belz.de\\\/#\\\/schema\\\/person\\\/7b46a383907dc48ca44fae32ceb24744\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/sql.marcus-belz.de\\\/?p=160#breadcrumb\"},\"inLanguage\":\"de\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/sql.marcus-belz.de\\\/?p=160\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/sql.marcus-belz.de\\\/?p=160#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/sql.marcus-belz.de\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Structuring and Formatting SQL Statements\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/sql.marcus-belz.de\\\/#website\",\"url\":\"https:\\\/\\\/sql.marcus-belz.de\\\/\",\"name\":\"Just another SQL blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/sql.marcus-belz.de\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"de\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/sql.marcus-belz.de\\\/#\\\/schema\\\/person\\\/7b46a383907dc48ca44fae32ceb24744\",\"name\":\"marcus\",\"url\":\"https:\\\/\\\/sql.marcus-belz.de\\\/?author=1\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Structuring and Formatting SQL Statements - Just another SQL blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/sql.marcus-belz.de\/?p=160","og_locale":"de_DE","og_type":"article","og_title":"Structuring and Formatting SQL Statements - Just another SQL blog","og_description":"Dieser Artikel in Deutsch&#8230; Overview Recently, I had to revise a Stored Procedure with nearly 2.000 lines of code. The procedure was edited by three developers, one after another. It contained sub statements for about 30 tables with varying degrees Read More ...","og_url":"https:\/\/sql.marcus-belz.de\/?p=160","og_site_name":"Just another SQL blog","article_published_time":"2018-05-16T22:31:34+00:00","article_modified_time":"2020-04-19T22:10:45+00:00","author":"marcus","twitter_card":"summary_large_image","twitter_misc":{"Geschrieben von":"marcus","Gesch\u00e4tzte Lesezeit":"2\u00a0Minuten"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/sql.marcus-belz.de\/?p=160#article","isPartOf":{"@id":"https:\/\/sql.marcus-belz.de\/?p=160"},"author":{"name":"marcus","@id":"https:\/\/sql.marcus-belz.de\/#\/schema\/person\/7b46a383907dc48ca44fae32ceb24744"},"headline":"Structuring and Formatting SQL Statements","datePublished":"2018-05-16T22:31:34+00:00","dateModified":"2020-04-19T22:10:45+00:00","mainEntityOfPage":{"@id":"https:\/\/sql.marcus-belz.de\/?p=160"},"wordCount":441,"keywords":["Format","SSMS options","T-SQL"],"articleSection":["All Languages","English"],"inLanguage":"de"},{"@type":"WebPage","@id":"https:\/\/sql.marcus-belz.de\/?p=160","url":"https:\/\/sql.marcus-belz.de\/?p=160","name":"Structuring and Formatting SQL Statements - Just another SQL blog","isPartOf":{"@id":"https:\/\/sql.marcus-belz.de\/#website"},"datePublished":"2018-05-16T22:31:34+00:00","dateModified":"2020-04-19T22:10:45+00:00","author":{"@id":"https:\/\/sql.marcus-belz.de\/#\/schema\/person\/7b46a383907dc48ca44fae32ceb24744"},"breadcrumb":{"@id":"https:\/\/sql.marcus-belz.de\/?p=160#breadcrumb"},"inLanguage":"de","potentialAction":[{"@type":"ReadAction","target":["https:\/\/sql.marcus-belz.de\/?p=160"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/sql.marcus-belz.de\/?p=160#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/sql.marcus-belz.de\/"},{"@type":"ListItem","position":2,"name":"Structuring and Formatting SQL Statements"}]},{"@type":"WebSite","@id":"https:\/\/sql.marcus-belz.de\/#website","url":"https:\/\/sql.marcus-belz.de\/","name":"Just another SQL blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/sql.marcus-belz.de\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"de"},{"@type":"Person","@id":"https:\/\/sql.marcus-belz.de\/#\/schema\/person\/7b46a383907dc48ca44fae32ceb24744","name":"marcus","url":"https:\/\/sql.marcus-belz.de\/?author=1"}]}},"_links":{"self":[{"href":"https:\/\/sql.marcus-belz.de\/index.php?rest_route=\/wp\/v2\/posts\/160","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sql.marcus-belz.de\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sql.marcus-belz.de\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sql.marcus-belz.de\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sql.marcus-belz.de\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=160"}],"version-history":[{"count":8,"href":"https:\/\/sql.marcus-belz.de\/index.php?rest_route=\/wp\/v2\/posts\/160\/revisions"}],"predecessor-version":[{"id":848,"href":"https:\/\/sql.marcus-belz.de\/index.php?rest_route=\/wp\/v2\/posts\/160\/revisions\/848"}],"wp:attachment":[{"href":"https:\/\/sql.marcus-belz.de\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=160"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sql.marcus-belz.de\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=160"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sql.marcus-belz.de\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=160"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}