{"id":781,"date":"2010-01-21T00:00:00","date_gmt":"2010-01-21T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/laying-out-sql-code\/"},"modified":"2021-09-29T16:22:01","modified_gmt":"2021-09-29T16:22:01","slug":"laying-out-sql-code","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/laying-out-sql-code\/","title":{"rendered":"Laying out SQL Code"},"content":{"rendered":"
\n

‘This article is just rudimentary. What are u professional guys fussing about?’
\n<\/i>Anonymous comment on Bob Sheldon’s article ‘Transact SQL formatting standards’<\/p>\n

\u00a0SQL, unlike a language such as Python, doesn’t require any formatting, or laying out, of its code in order\u00a0 to compile and run. On the other hand, the layout of SQL is important because SQL was always intended to be close to a real declarative human sentence, with phrases for the various parts of the command. It was written in the days when it was considered that a computer language should be easy to understand. \u00a0Laying SQL out carefully, and choosing sensible object names helps greatly.<\/p>\n

You may feel compelled to format SQL code as if it were Pascal or Java, for the reason that you may have come from a Pascal background and find any other way of doing it disconcerting. Every language marks its practitioners for keeps. ForTran programmers tend to write thin columns of abbreviated code, \u00a0Java programmers often like their SQL code to be in lower case, and BASIC programmers never seem to get used to multi-line strings. \u00a0In fact, it is often possible to detect what language a database developer first cut his teeth on from looking at the way they format SQL. Because we all have had different influences on the way we write code, there will never be any agreement about the way that code is laid out.<\/p>\n

There is no single correct way of laying out SQL or naming your database objects. However, when a developer is spending forty hours a week staring at SQL code, he or she gets to like it laid out to a particular style. Code in this style will be considerably easier for them to read and understand. Other people’s code looks all wrong.\u00a0 This only causes difficulties when team members find no way of agreeing on a format, and much time is wasted lining things up or changing the case of object names before starting to work on existing code.<\/p>\n

Why revisit this topic? It has been discussed in a number of places and was the subject of a book (Joe Celko’s SQL Programming Style)<\/strong>. \u00a0 The reason I’m interested is that a growing number of applications have been devised that will do this automatically, and I even wrote one myself in SQL <\/a>that did this in a rudimentary way. The subject is even more interesting to publishers such as Simple Talk, because they have to render SQL code for publication\u00a0 in a way that is generally acceptable. It isn’t easy to come up with the detail for doing this.<\/p>\n

There are several things that need to be decided before you start a development project. One job that is worth tackling is to produce a style guide before any code is cut. Any style guide should, I think, cover Object Naming Conventions and Code Layout. I would keep the topic of structured code-headers, and code-portability, separate. Although ISO\/IEC 11179 will help a great deal in defining a common language for talking about metadata, it is, inevitably,\u00a0 less prescriptive when discussing the practicalities of a style guide for a project. I have not found any adopted standard at all for layout, so I hope I can help with some suggestions for this in this article.<\/p>\n

Object Naming Conventions.<\/h1>\n

Object naming is really a different subject altogether from layout, and it is impossible to refactor the naming of SQL objects automatically as one can the layout.\u00a0 It has to be done right from the start. Because object-naming is so bound-up with our culture, it causes many arguments in development teams.\u00a0 There are standards for doing this (ISO\/IEC 11179-5. Naming and identification principles for data elements), but everyone likes to bring their own familiar rituals to the process.\u00a0 Here are a few points that cause arguments.<\/p>\n

\n

Tibbling.<\/h2>\n

The habit most resistant to eradication is ‘Tibbling’, the use of reverse Hungarian notation, a habit caught from starting out with Microsoft Access.\u00a0 ‘Tibbling’ is named after the habit of prefixing the name of a table with the ‘tbl’ prefix, thereby making it difficult to pronounce. A tibbler will take a table that should be called ‘Node’, and call it ‘tblNode<\/b>‘.\u00a0 Stored procedures will be called something like spCreateCustomer <\/b>and table-valued functions will be called tvfSubscription<\/b>. This makes talking about your data difficult.\u00a0 ‘Tibbling’ is now\u00a0rather entrenched at Microsoft, in a mutated version that gives a PK_, FK_ IX_SP_or DF_ prefix to object names (but not mercifully to tables yet),\u00a0so I doubt\u00a0 that it will ever be eradicated amongst SQL Server programmers. It has never been part of any national or international standard for naming data objects.<\/p>\n

Pluralising<\/h2>\n

A Pluraliser will always name a table after a quantity of entities rather than an entity.\u00a0 The Customer<\/b> table will be called Customers<\/b>, and Invoice <\/b>will be Invoices.<\/b> \u00a0Ideally, the use of a collective name for the entities within a table is best, but failing that, the singular noun is considered better than the plural.<\/p>\n

Abbreviating (or abrvtng)<\/h2>\n

An Abbreviator usually picks up the habit from interpreted procedural programs of trying to abbreviate all names, under the mistaken belief that the code will run faster, take less space or be, in some mystical sense, more efficient. Heaving out the vowels (the ‘vowel movement’) is a start, so that Subscription <\/b>becomes Sbscrptn<\/b>, but the urge towards the mad extreme will end up with Sn<\/b>. I’ve heard of this being call ‘Custing’, after the habit of using the term Cust<\/b> instead of Customer<\/b>. To them, I dedicate the following routines<\/p>\n

\n
CREATE TABLE ## ( # INT ) \r\nDECLARE @ INT\u00a0 set @=8\r\nINSERT\u00a0 INTO ##\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ( # )\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT\u00a0 @%2\r\nSELECT\u00a0 *\r\nFROM\u00a0\u00a0\u00a0 ##\r\n<\/pre>\n<\/div>\n
This habit came from the old ForTran days when you could only use 6 characters at the most. SQL 92 allows 18 characters, but SQL Server has no limit.<\/p>\n
[Escaping]<\/h2>\n
Because spaces are not allowed in object names, unless the name is escaped, SQL names need some way of separating words.\u00a0 One could write customerAccounts<\/b>, CustomerAccounts<\/b>, customer_Accounts<\/b> or Customer_Accounts<\/b>.\u00a0 Yes, you need to make up your mind.<\/p>\n
Because desktop databases such as Access are more liberal about the character set you can use for object names, the idea came of ‘escaping’, ‘quoting’, or delimiting \u00a0such names so that they could be copied without modification into a full relational database.\u00a0 Those of us who take trouble to write legal SQL object names find the rash of square brackets that are generated by SSMS acutely irritating. Here is some code that really runs perfectly happily in SQL Server, purely because of the use of escaping with square brackets.<\/p>\n
\n
\/* we see if we can execute a verse of Macauley's famous poem 'Horatius'. *\/\r\n\u00a0\r\n--create a table with a slightly unusual name\r\ncreate table [many a stately market-place;\r\n\u00a0\u00a0\u00a0 From many a fruitful plain;\r\n\u00a0\u00a0\u00a0 From many a lonely hamlet,]\r\n\u00a0\u00a0\u00a0 (\r\n[The horsemen and the footmen\r\n\u00a0\u00a0\u00a0 Are pouring in amain] int,\r\n\u00a0\u00a0\u00a0 [, hid by beech and pine,] varchar(100)\r\n)\u00a0\u00a0\u00a0 \r\n--put a value into this table\r\ninsert into [many a stately market-place;\r\n\u00a0\u00a0\u00a0 From many a fruitful plain;\r\n\u00a0\u00a0\u00a0 From many a lonely hamlet,] ([The horsemen and the footmen\r\n\u00a0 \u00a0\u00a0Are pouring in amain], [, hid by beech and pine,])\r\n\u00a0\u00a0\u00a0 Select 1,'an eagle's nest, hangs on the crest\r\n\u00a0\u00a0\u00a0 Of purple Apennine;'\r\n\u00a0\r\n\/* now, with that preparation work done, we can execute the third verse *\/\r\nSelect\u00a0\u00a0\u00a0 \r\n\u00a0\u00a0\u00a0 [The horsemen and the footmen\r\n\u00a0\u00a0\u00a0 Are pouring in amain]\r\n\u00a0\u00a0\u00a0 From [many a stately market-place;\r\n\u00a0\u00a0\u00a0 From many a fruitful plain;\r\n\u00a0\u00a0\u00a0 From many a lonely hamlet,]\r\n\u00a0\u00a0\u00a0 Where[, hid by beech and pine,]\r\n\u00a0\u00a0\u00a0 Like 'an eagle's nest, hangs on the crest\r\n\u00a0\u00a0\u00a0 Of purple Apennine;'\r\n\u00a0\r\n<\/pre>\n<\/div>\n
It is true that ‘delimited’ names used to be handy \u00a0for non-Latin languages such as Chinese, but nowadays you can use Unicode characters for names so this compiles perfectly happily<\/p>\n
\n
CREATE TABLE \u00e4\u00b8\u00ad\u00e5\u00e6\u00b8\u00e6\u00e5\u00ba\u00ab\u00e8\u00a1\u00a8\r\n(\u00e6\u00e7\u00e4\u00b8\u00bb\u00e9\u00b5 CHAR(2) NOT NULL PRIMARY KEY, \r\n\u00a0\u00e6\u00e7\u00e6\u00b8\u00e6\u00e5 INTEGER NOT NULL\r\n\u00a0 CHECK (\u00e6\u00e7\u00e6\u00b8\u00e6\u00e5 > 0));\u00a0 \r\n<\/pre>\n<\/div>\n
Herein lies another horrifying possibility. SQL Server will allow you to use ‘shapes’.<\/p>\n
\n
CREATE TABLE \"\u00e2\u00e2\u00a6\u00e2\u00a9\u00e2\" ( \"\u00e2\u00e2\u00ac\u00e2\u00b4\u00e2\" nvarchar(10)) \r\nDECLARE @ nvarchar(10)\u00a0 set @='\u00e2'\r\nINSERT\u00a0 INTO \"\u00e2\u00e2\u00a6\u00e2\u00a9\u00e2\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ( \"\u00e2\u00e2\u00ac\u00e2\u00b4\u00e2\" )\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT\u00a0 replicate(@,5)\r\nSELECT\u00a0 *\r\nFROM\u00a0\u00a0\u00a0 \"\u00e2\u00e2\u00a6\u00e2\u00a9\u00e2\"\r\n<\/pre>\n<\/div>\n
Restricting<\/h2>\n
A habit that has crept into SQL from ex-Cobol programmers, I believe, is the use of a very restricted vocabulary of terms. \u00a0This is rather like the development of cool street-argot with a highly restricted set of 400 words, rather than the 40,000 that are within the grasp of the normal adult.\u00a0 With sql, this typically uses words like ‘GET’, ‘PUT’\u00a0 or ‘SAVE’ in a variety of\u00a0 SQL is perfectly happy to oblige, even though the results are difficult to understand.\u00a0<\/p>\n
Taking this to extremes, this code is perfectly acceptable to SQL Server.<\/p>\n
\n
--first create a GetDate schema\r\nCREATE SCHEMA GetDate\r\n--and a GetDate table to go in it.\r\nCREATE TABLE GetDate.GetDate\r\n(\r\nGetDate DATETIME,\r\n[GetDate GetDate] DATETIME\r\n)\r\nGO\r\n--and a function called GetDate\r\nCREATE FUNCTION GetDate()\r\nRETURNS TABLE\r\nAS RETURN\r\n(\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT GetDate() AS [GetDate]\r\n)\r\nGO\r\n-- Now we can write some startlingly silly code\r\nINSERT INTO GetDate.GetDate(GetDate.GetDate.GetDate.GetDate, [GetDate GetDate])\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT GetDate() AS GetDate, GetDate FROM GetDate()\r\n--but we can do far far siller stuff if we wanted purely because there is no restiction on what goes between angle-brackets\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \r\nCREATE FUNCTION [GetDate.GetDate.GetDate.GetDate\r\nGetDate.GetDate.GetDate.GetDate\r\nGetDate.GetDate.GetDate.GetDate]()\r\nRETURNS TABLE\r\nAS RETURN\r\n(\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT GetDate() AS [GetDate]\r\n)\r\nGO\r\n\u00a0\r\nINSERT INTO GetDate.GetDate(GetDate.GetDate.GetDate.GetDate, [GetDate GetDate])\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT GetDate() AS GetDate, GetDate FROM [GetDate.GetDate.GetDate.GetDate\r\nGetDate.GetDate.GetDate.GetDate\r\nGetDate.GetDate.GetDate.GetDate]()\r\n<\/pre>\n<\/div>\n<\/div>\n
The existing standards for naming objects are more concerned with the way of discussing how you name database objects, and the sort of ways you might document your decisions. However, one can come to a reasonable basis for agreement with the following:<\/p>\n