Av rating:

Total votes: 46
Total comments: 23

send to a friend

printer friendly version

Laying out SQL Code

21 January 2010

by Phil Factor

'This article is just rudimentary. What are u professional guys fussing about?’
Anonymous comment on Bob Sheldon’s article ‘Transact SQL formatting standards’

It is important to ensure that SQL code is laid out the best way for the team that has to use and maintain it. Before you work out how to enforce a standard, one has to work out what that standard should be for the application. So do you dive into detail or create an overall logic to the way it is done?

SQL, unlike a language such as Python, doesn’t require any formatting, or laying out, of its code in order 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. Laying SQL out carefully, and choosing sensible object names helps greatly.

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, Java programmers often like their SQL code to be in lower case, and BASIC programmers never seem to get used to multi-line strings. In 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.

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. 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.

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). 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 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 in a way that is generally acceptable. It isn't easy to come up with the detail for doing this.

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, 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.

Object Naming Conventions.

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. 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. 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. Here are a few points that cause arguments.

Tibbling.

The habit most resistant to eradication is ‘Tibbling’, the use of reverse Hungarian notation, a habit caught from starting out with Microsoft Access. ‘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’. Stored procedures will be called something like spCreateCustomer and table-valued functions will be called tvfSubscription. This makes talking about your data difficult. 'Tibbling' is now rather 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), so I doubt 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.

Pluralising

A Pluraliser will always name a table after a quantity of entities rather than an entity. The Customer table will be called Customers, and Invoice will be Invoices. Ideally, 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.

Abbreviating (or abrvtng)

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 becomes Sbscrptn, but the urge towards the mad extreme will end up with Sn. I’ve heard of this being call ‘Custing’, after the habit of using the term Cust instead of Customer. To them, I dedicate the following routines

CREATE TABLE ## ( # INT )

DECLARE @ INT set @=8

INSERT INTO ##

( # )

SELECT @%2

SELECT *

FROM ##

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.

[Escaping]

Because spaces are not allowed in object names, unless the name is escaped, SQL names need some way of separating words. One could write customerAccounts, CustomerAccounts, customer_Accounts or Customer_Accounts. Yes, you need to make up your mind.

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 such names so that they could be copied without modification into a full relational database. 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.

/* we see if we can execute a verse of Macauley's famous poem 'Horatius'. */

--create a table with a slightly unusual name

create table [many a stately market-place;

From many a fruitful plain;

From many a lonely hamlet,]

(

[The horsemen and the footmen

Are pouring in amain] int,

[, hid by beech and pine,] varchar(100)

)

--put a value into this table

insert into [many a stately market-place;

From many a fruitful plain;

From many a lonely hamlet,] ([The horsemen and the footmen

Are pouring in amain], [, hid by beech and pine,])

Select 1,'an eagle’s nest, hangs on the crest

Of purple Apennine;'

/* now, with that preparation work done, we can execute the third verse */

Select

[The horsemen and the footmen

Are pouring in amain]

From [many a stately market-place;

From many a fruitful plain;

From many a lonely hamlet,]

Where[, hid by beech and pine,]

Like 'an eagle’s nest, hangs on the crest

Of purple Apennine;'

It is true that ‘delimited’ names used to be handy for non-Latin languages such as Chinese, but nowadays you can use Unicode characters for names so this compiles perfectly happily

CREATE TABLE 中國數據庫表

(我的主鍵 CHAR(2) NOT NULL PRIMARY KEY,

我的數據列 INTEGER NOT NULL

CHECK (我的數據列 > 0));

Herein lies another horrifying possibility. SQL Server will allow you to use ‘shapes’.

CREATE TABLE "╚╦╩╗" ( "└┬┴┐" nvarchar(10))

DECLARE @ nvarchar(10) set @='═'

INSERT INTO "╚╦╩╗"

( "└┬┴┐" )

SELECT replicate(@,5)

SELECT *

FROM "╚╦╩╗"

Restricting

A habit that has crept into SQL from ex-Cobol programmers, I believe, is the use of a very restricted vocabulary of terms. This 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. With sql, this typically uses words like ‘GET’, ‘PUT’ or ‘SAVE’ in a variety of SQL is perfectly happy to oblige, even though the results are difficult to understand.

Taking this to extremes, this code is perfectly acceptable to SQL Server.

--first create a GetDate schema

CREATE SCHEMA GetDate

--and a GetDate table to go in it.

CREATE TABLE GetDate.GetDate

(

GetDate DATETIME,

[GetDate GetDate] DATETIME

)

--and a function called GetDate

CREATE FUNCTION GetDate()

RETURNS TABLE

AS RETURN

(

SELECT GetDate() AS [GetDate]

)

-- Now we can write some startlingly silly code

INSERT INTO GetDate.GetDate(GetDate.GetDate.GetDate.GetDate, [GetDate GetDate])

SELECT GetDate() AS GetDate, GetDate FROM GetDate()

--but we can do far far siller stuff if we wanted purely because there is no restiction on what goes between angle-brackets

CREATE FUNCTION [GetDate.GetDate.GetDate.GetDate

GetDate.GetDate.GetDate.GetDate

GetDate.GetDate.GetDate.GetDate]()

RETURNS TABLE

AS RETURN

(

SELECT GetDate() AS [GetDate]

)

INSERT INTO GetDate.GetDate(GetDate.GetDate.GetDate.GetDate, [GetDate GetDate])

SELECT GetDate() AS GetDate, GetDate FROM [GetDate.GetDate.GetDate.GetDate

GetDate.GetDate.GetDate.GetDate

GetDate.GetDate.GetDate.GetDate]()

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:

Procedures should be a phrase with singular nouns and a verb, in the present tense, in it to describe what they do
Be consistent with the way you denote word-boundaries (the use of the underline character, the use of a capital letter, or hyphen.)
Tables, sets, views and other collections should use a collective name, a name for a group of entities, such as ‘flock’, ‘ledger’, ‘team’, ‘staff’
Scalar names should be in the singular
Any object name should use only commonly-understood abbreviations. Such as ZIP for ‘Zone Improvement Plan’
Use standard and consistent postfixes (eg _ID, _name, _date, _quantity)
Use commonly understood words for relationship tables. (e.g. meeting, booking, marriage, purchase)
names should consist of one or more of the following components
- Object Class: The name can include just one 'Object class' which is the terminology used within the community of users of the application. Words like 'Cost' or purchase
- Property Term: Property terms represent the category of the data. e.g: Total Amount, Date, Sequence, size, height
- Qualifiers These can be used if necessary to describe the data element and make it unique within a specified context, in no particular order, but they must precede the term being qualified. Qualifier terms are optional. Example: Budget Period
- The Representation term This describes the representation of the valid value set of the data element. There should be only one, as the final part of the name, and it should add precision to the preceding terms.

It isn't always easy to come up with a word to attach to a table.

'Not all ideas are simply expressed in a natural language, either. For example, "women between the ages of 15 and 45 who have had at least one live birth in the last 12 months" is a valid object class not easily named in English'.

ISO/IEC 11179-1:2004(E): Page 19

Coding layout

Indenting and line-breaking.

Line Breaks

SQL code doesn’t have to be broken into short lines like a Haiku poem. Since SQL is designed to be as intelligible as an English sentence, it can be written as an English sentence. It can, of course, be written as a poem, but not as a thin smear down the left-hand side of the query window. The urge to insert large numbers of line-breaks comes from procedural coders where a vertical style is traditional, dating back to the days of FORTRAN and Basic. An advantage of the vertical style is that, when an error just reports a line-number, it takes less time to work out what the problem was. However, it means an over familiarity with the scroll-bar, if the routine runs to any length.

Indenting and Line-Breaking is done purely to emphasize the structure of SQL, and aid readability. line-breaks have to be inserted at certain points (I rather like to have a line-break at around the 80^th character), and they shouldn’t be mid-phrase. However, to specify that here must always be a line-break between each phrase (before the FROM, ON, and WHERE clause, for example) can introduce an unnecessary amount of white-space into code. Such indenting should never become mere ritual activity to make things look neat, like obsessively painting the rocks in front of your house with white paint.

Generally, the use of tabs for doing indenting has resulted in indenting that is way too wide. Of course, written text can have wide indents, but it isn’t done to around eight levels, skidding the text hard-against the right-hand side of the page. Usually, two or three spaces is fine.

Indenting

Code without indenting is very difficult to follow. Indentation follows a very similar practice to a structured document where the left margin is indented according to the nesting of the section heading. It should be a fixed number of spaces for each level of nesting.

It is at the point where we need to decide what comprises a change in the nesting level that things get difficult. We can be sure that, in a SELECT statement, all clauses are subordinate to the SELECT. Most of us choose to indent the FROM or the WHERE clause at the same level, though, but one usually sees the lists of columns indented. On the other hand, it is quite usual to see AND, ORDER BY, OR, and so on indented to the next level. What rules lie behind the current best-practice? Many of us like to have different rules for DDL code, such as CREATE TABLE statements, from DML such as INSERT, UPDATE or SELECT statements.

Formatting lists

Lists occur all over the place in code. As in printed text, you can handle them in a number of different ways. If, for example, you are just listing entities then you’d do it like this. I like many French cheeses, including Abbaye de Belloc, Baguette Laonnaise, Brie de Melun, Calenzana, Crayeux de Roncq, Esbareich, Frinault, Mixte, Pave du Berry, Port-Salut, Quercy Petit, Regal de la Dombes, Sainte Maure, Sourire Lozerien, Truffe and Vignotte. Now no typesetter would agree to arrange this in a vertical list because the page would contain too much white space.

I like many French cheeses, including

Abbaye de Belloc,
Baguette Laonnaise,
Brie de Melun,
Calenzana,
Crayeux de Roncq,
Esbareich,
(…etc …)

…and they’d be most unlikely to want to put commas at the beginning of list elements. However, if the list elements consisted of longer strings, then it would be perfectly acceptable. In the same way, the rules for formatting SQL have to take into account the type of SQL statement being formatted, and the average length of each list element.

Punctuation

Commas used as list-separators are often put at the beginning of lines. I realise that it makes the ‘commenting out’ of list-members easier during development, but it makes it difficult for those of us that are used to reading English text in books. Commas are at the end of phrases, with no space before them, but if they are followed by a word or phrase on the same line, then there is a space after the comma. Semi-colons are a rather more unfamiliar, but increasing, punctuation mark in SQL. Their treatment should be similar. In development work, one can, and should, do all sorts of wild formatting of SQL, but once it is tested, and ‘put to bed’, it should be tidied up to make it easier for others to understand.

Capitalisation

Before we start. I’d like to define what I mean by the various terms

This_Is_Capitalised
This_is_lowercase –or minuscule
this_Is_Camelcase
THIS_IS_UPPERCASE—or majuscule

Schema objects are, I believe, better capitalised. I would strongly advise against using a binary or Case-sensitive collation for the database itself, since this will cause all sorts of unintended errors. A quirk of all European languages is that words mean the same thing whether capital or lower-case letters are used. Uppercase, or majuscule, lettering was used exclusively by the Roman Empire, and lowercase or minuscule was developed later on purely as a cursive script. The idea that the case of letters changed the meaning of words is a very recent novelty. Of course, one would not expect programmers to be so perverse as to do this sort of thing, but I’ve seen code that approaches this scale of awfulness in C#

Create database casesensitive

Alter Database casesensitive Collate SQL_Latin1_General_CP1_CS_AS

use casesensitive

create table thing

(

Thing int identity(1, 1),

tHing Varchar(20),

thIng int not null,

thiNg float not null,

thinG datetime not null

)

insert into

thing

(tHing, thIng, thiNg, thinG)

select

'thing', 1, 34.659, '1 Oct 2009'

select * from thing

drop table thing

So get off the fence

I wouldn't want to impose my views on anyone else. However, if you are looking for recommendations, here what I usually suggest. I'd stick to the conventions of

Keeping your database Case-insensitive even if your data has to be case-sensitive, unless you are developing in a language for which this is inappropriate.
Capitalising all the Scalars and Schema object names (e.g. Invoice, Basket, Customer, CustomerBase, Ledger)
Uppercasing all reserved words (such as SELECT, WITH, PIVOT, FROM, WHERE)
putting a line-break between list items only when each list item averages more than thirty or so characters.
putting block delimiters (such as BEGIN END) on a new line by themselves, correctly indented
putting line-breaks within SQL statements before the clause (FROM, ON, WHERE, HAVING, GROUP BY) only where it aids clarity in long statements, but not in every case.
use the semi-colon to aid the reading of code, even where SQL syntax states that it is only optional.
using an increased indent for subordinate clauses if the ON, INTO, and HAVING statement is at the start of the line

For sheer practicality, I'd opt for a layout that can be achieved automatically by your favorite code-layout tool (I use SQL Refactor or SQL Prompt, but there are several others) There is nothing more irritating than to find that someone has trashed a beautifully laid-out procedure by mangling it with a badly-set-up layout tool.

I tend to write my SQL fast and sloppily, to get some initial results quickly, and then refining and rewriting the code until it is fast and efficient. At that point, it is usually a mess and it it very satisfying to run it through a layout tool to smarten it up. In fact, some time ago, before layout tools existed for SQL, I created a stored procedure that tidied up SQL code. It gradually ended up as the SQL Prettifier, repurposed to render SQL in HTML, and with the formatting part taken out once SQL Refactor appeared. A tool like this can save a lot of inevitable arguments amongst developers as to the 'correct' way to format SQL code.

Subject:	Doesn't anyone read my books?
Posted by:	Celko (view profile)
Posted on:	Saturday, January 23, 2010 at 4:24 PM
Message:	I wrote a book entitled SQL PROGRAMMING STYLE which has formatting rules for SQL based on the years of actual research we did in the early days of Software Engineering. We already knew a lot of how people read from typography research -- Law of Proxmity, Boumas, rivers, eye movement, etc. The new stuff that applies to reading code had to be discovered by torturing college sophomores with low grades, just like all work in psychology!

Subject:	Tabs vs spaces
Posted by:	moose482 (view profile)
Posted on:	Saturday, January 23, 2010 at 6:14 PM
Message:	Nice article - my coding style is very similar to your suggestions with just a few differences. However, I have one disagreement. I prefer to use tabs rather than spaces for indenting - one tab per level of nesting. Since all (most, anyway) IDEs and editors permit you to set the number of display spaces per tab, each developer can see the indentation as they like to see it. I like two spaces, some folks I work with like three or four. With tabs, we all get what we want.

Subject:	Go, Joe
Posted by:	Mischa (view profile)
Posted on:	Saturday, January 23, 2010 at 7:34 PM
Message:	You'd be surprised; bet that there are twenty times as many "Smarties" and "Trees" books on people's shelves than your style book. Frankly, I wish you could write (and get paid for) a plugin like Resharpers' that would just make SQL source right (NOT like StyleCop, which uses its powers for the forces of darkness in nagging about irrelevancies :-)

Subject:	Blame it on COBOL
Posted by:	BuggyFunBunny (view profile)
Posted on:	Sunday, January 24, 2010 at 3:56 PM
Message:	From my experience, two of these items are directly from COBOL (and TTYs) - the use of 80 columns "because it's easier to read" in COBOL, but predates it back to the Hollerith card. - the leading commas; these are standard COBOL practice. Both are truly silly when we all have 19" widescreen tubes, where vertical real estate is still more valuable than horizontal. Listening to this tripe from 20-something java coders makes my teeth itch. As to indent, 2,3,4 column stepping is still a matter of religious fervour among coders; as is the battle against tabs. With SQL code, column indenting is less the point; indenting should be used to keep sub-elements vertically aligned within their (sub-)clause. Most modern editors are smart enough to keep the current indentation until an empty line. And they will generally fill with spaces.

Subject:	Although MS is not Helping
Posted by:	EdH204 (not signed in)
Posted on:	Sunday, January 24, 2010 at 7:15 PM
Message:	I've got to feel that MS is not helping, with the three or so different coding standards that it applies in different modules of Management Studio (Query Designer, DML Scripting and DDL Scripting), and no consistency in SQL Server's system procedures and functions. It would be nice if they would standardise, or better still allow the developer to select a prefered standard.

Subject:	Put it on my tab
Posted by:	Anonymous (not signed in)
Posted on:	Monday, January 25, 2010 at 10:33 AM
Message:	I like the tab until I use a different tool to view the code. It can get real ugly when both spaces and tabs have been used. Some apps use 8 spaces for a tab by default. So I normally opt for replacing tabs by spaces. A space is a space.

Subject:	I don't know, I think I may switch to symbols...
Posted by:	randyvol (view profile)
Posted on:	Tuesday, January 26, 2010 at 6:34 AM
Message:	I don't know Phil, I think I see a means to job security in your article. Just think of being in a position to inject 100's of thousands of lines of code such as that you laid out below, into your company's systems, and you are the only one who knows what "╚╦╩╗" is and what "└┬┴┐" is. CREATE TABLE "╚╦╩╗" ( "└┬┴┐" nvarchar(10)) DECLARE @ nvarchar(10) set @='═' INSERT INTO "╚╦╩╗" ( "└┬┴┐" ) SELECT replicate(@,5) SELECT * FROM "╚╦╩╗" This is positive genius ! <insert mad, evil giggle here> ;-) randyvol

Subject:	I'm Glad There Aren't "Standards" Police
Posted by:	Anonymous (not signed in)
Posted on:	Wednesday, January 27, 2010 at 3:58 PM
Message:	Because of lists like this, I'm glad there are not standards that everyone has to stick to. If this list works for someone, great. But there are several items on this list, such as tibbling, that I have found make code much easier to read. That's why I do my style--regardless of where it came from. What I find troubling about this list is the focus on extremes and sources of practices. While there may be some cases where you have seen real examples of coding like you show above, it doesn't mean the entire practice is always bad. You end up throwing out the baby with the bath water. Showing an extreme does not make your case for it being a bad practice. Similarly, just because a practice comes from Fortran or Access doesn't make it necessarily bad. My advice to people: Use tibbling where it helps. Use escape brackets when it helps for readability or when it is needed for return results. Etc. Example for needed brackets: if your query must return a header that the user will see. In that case, you may need a space in the header. Ex: SELECT ThisExpression as [The Subject], MyField as Cost FROM ...

Subject:	Yes, but ...
Posted by:	HercG (view profile)
Posted on:	Thursday, January 28, 2010 at 3:41 AM
Message:	As usual, Phil speaks sense in his idiosyncratic but amusing way. A definition difference, though: This_Is_Pascal_Case although I dislike the underline characters in there. Pascal case differs from capitalisation conventions in that capitalisation does not involve all the little words ("The Case of the Omitted Capitals on Small Words"), whereas Pascal case does, making it useful when you omit the underlines in identifiers. A nitpick, but with a reason - and yes, there is some abiguity with something like ABookTitle. I agree with Anonymous of the 27th Jan at 15:58 that the square brackets are useful in SELECT statements, since you can write routines which simply display the field name as a column heading, allowing code which has no "knowledge" of the the data. But Phil's comment was about using the to define objects in the DB, and I agree entirely with him on this. As for tibbling (a new word to me): I hate it with a passion; the prefixes seldom add anything useful to my knowledge, since it's almost always obvious from context what kind of object is being referenced. But to each his own, I suppose - I can ignore them as I read, and will verbalise the name without the prefix. Something I do dislike: SELECT field, field, ... FROM table INNER JOIN othertable ON key-match-expression It's so much clearer with INNER JOIN and ON on one line. But I'm probably outnumbered. My view is that people started with bad habits from If condition Then action which has always seemed unnatural to me, but I've had to use it because of compilers that insist.

Subject:	Line Breaks
Posted by:	JoshRoss (view profile)
Posted on:	Sunday, January 31, 2010 at 8:39 PM
Message:	I agree with you on the line-breaks with the exception of breaking before the ON clause. When you have more than one join, the extra noise, introduced by the break, interrupts the interpretation of schema of the statement. Or at least it does for me.

Subject:	Formatting large strings?
Posted by:	Jollytin (not signed in)
Posted on:	Sunday, January 31, 2010 at 9:48 PM
Message:	Here is a piece of a stored procedure that builds and executes some strings (ie varchars). Does anyone have formatting that can make it easier to read when you have sql like this that is inside quotes and has places where more quotes are needed because you have to 'jump out' into the surrounding domain to obtain some data?--select @sql EXEC(@sql) SET @sql = 'update ' SET @sql = @sql + '[' + @Project + '].dbo.STRUCT_tblCorrugationPartStringAttributeValues ' SET @sql = @sql + 'set ' SET @sql = @sql + 'AttributeValue = dbo.Parts.Location, ' SET @sql = @sql + 'IsDeferred = 0 ' SET @sql = @sql + 'FROM ' SET @sql = @sql + '[' + @Project + '].dbo.STRUCT_tblCorrugationPartStringAttributes EPSA ' SET @sql = @sql + 'INNER JOIN [' + @Project + '].dbo.STRUCT_tblCorrugationPartStringAttributeValues EPSAV ON EPSA.CorrugationPartStringAttributeGUID = EPSAV.CorrugationPartStringAttributeGUID ' SET @sql = @sql + 'INNER JOIN [' + @Project + '].dbo.GEN_tblStringAttributes SA ON EPSA.StringAttributeGUID = SA.StringAttributeGUID ' SET @sql = @sql + 'INNER JOIN [' + @Project + '].dbo.STRUCT_tblCorrugationParts EP ON EPSAV.CorrugationPartGUID = EP.CorrugationPartGUID ' SET @sql = @sql + 'INNER JOIN dbo.Parts ON EP.CorrugationPartGUID = dbo.Parts.PartGUID ' SET @sql = @sql + 'WHERE ' SET @sql = @sql + 'SA.StringAttributeName = N''LOCATION'' ' SET @sql = @sql + 'and len(dbo.Parts.Location) > 0 ' SET @sql = @sql + 'and dbo.Parts.PartType = ''Corrugation'' ' SET @sql = @sql + 'and case when ''' + @UpdateStyle + '''= ''All'' then 1 when ''' + @UpdateStyle + ''' = ''Blanks'' and LEN(EPSAV.AttributeValue) = 0 then 1 else 0 end = 1 ' --select @sql EXEC(@sql)

Subject:	The title should read...
Posted by:	Bob (view profile)
Posted on:	Monday, February 01, 2010 at 3:24 AM
Message:	I wanted to see some tips on layout of sql code but got an article that talks a lot about the wierd stuff that you could do if you had a lot of time to spare... but where are the layout guides and rules of thumb clearly stated. No, I did not read the article in detail just like most folk I came because of the title and have been disappointed. The title should not be "layout of sql code" but instead "things to consider when writing a sql code style guide"

Subject:	Re: The title should read...
Posted by:	Phil Factor (view profile)
Posted on:	Monday, February 01, 2010 at 3:42 AM
Message:	Sorry you didn't find what you wanted. Unfortunately we can't change the title at this late stage. If you find the article you're looking for, then by all means leave the URL in a comment. I suspect you stumbled over the code at the beginning and didn't quite reach the section after 'so get off the fence', where the layout guides and rules of thumb are clearly stated. Also, the bullet point list of tips at the end of 'Object Naming conventions' give a series of tips on the subject where the rules of thumb for object naming are clearly stated.

Subject:	Re: Formatting large strings?
Posted by:	Phil Factor (view profile)
Posted on:	Monday, February 01, 2010 at 4:58 AM
Message:	SET @sql = ' UPDATE [' + @Project + '].dbo.STRUCT_tblCorrugationPartStringAttributeValues SET AttributeValue = dbo.Parts.Location, IsDeferred = 0 FROM [' + @Project + '].dbo.STRUCT_tblCorrugationPartStringAttributes EPSA INNER JOIN [' + @Project + '].dbo.STRUCT_tblCorrugationPartStringAttributeValues EPSAV ON EPSA.CorrugationPartStringAttributeGUID = EPSAV.CorrugationPartStringAttributeGUID INNER JOIN [' + @Project + '].dbo.GEN_tblStringAttributes SA ON EPSA.StringAttributeGUID = SA.StringAttributeGUID INNER JOIN [' + @Project + '].dbo.STRUCT_tblCorrugationParts EP ON EPSAV.CorrugationPartGUID = EP.CorrugationPartGUID INNER JOIN dbo.Parts ON EP.CorrugationPartGUID = dbo.Parts.PartGUID WHERE SA.StringAttributeName = N''LOCATION'' AND len(dbo.Parts.Location) > 0 AND dbo.Parts.PartType = ''Corrugation'' AND CASE WHEN ''' + @UpdateStyle + '''= ''All'' THEN 1 WHEN ''' + @UpdateStyle + ''' = ''Blanks'' AND LEN(EPSAV.AttributeValue) = 0 THEN 1 ELSE 0 end = 1 ' --select @sql EXEC(@sql)

Subject:	Great article
Posted by:	gianluca.sartori (view profile)
Posted on:	Friday, February 05, 2010 at 2:49 AM
Message:	Great article, Phil. I already came across your "SQL poem", but the CREATE TABLE "╚╦╩╗" and other weird namings are incredibly ingenious and provoking. My two cents on laying-out: I do it as I write code, because I have to understand what I'm doing. I have to see the picture or I get rapidly lost. I wish that code formatting tools such as SQL refactor (I bought it two years ago) had more options for line breaks and indents. Most code formatters lay out the way the programmer who wrote it prefers, not the way I would like to see it laid out. Guido Thelen's SQLInForm (www.sqlinform.com) is the only prettifier I found that allows you to enter your preferences, but using it outside SSMS is a pain.

Subject:	Re: SQLinform
Posted by:	Phil Factor (view profile)
Posted on:	Friday, February 05, 2010 at 4:46 AM
Message:	SQLInform is intriguing as it aims to work on so many SQL dialects. I agree that there don't seem to be quite enough knobs to twiddle in the Prompt/Refactor formatter, but I've reached a workable compromise with it. I've discussed with the team the idea of a system that learns your preferences from your existing 'model' code. It is an interesting idea. Who knows? If enough people ask them for it.... On the subject of formatting tools, I'd really like a code formatting tool with a command-line interface. This would make the use of a Programmer's Text Editor even more attractive as an alternative to SSMS!

Subject:	I like to adding line breaks before AND or OR statements
Posted by:	Matt Penner (view profile)
Posted on:	Friday, February 05, 2010 at 11:58 AM
Message:	I like to do this, especially when writing T-SQL off the cuff. It keeps it very readable. I used to put the AND at the end of the statement, since it looks a little nicer, but then I realized that I would constantly play with my restrictions commenting them out and back again. Having the AND on the same line as the actual restriction is a little more direct and makes commenting code much easier. Here is an example: SELECT LastName, FirstName FROM Employees WHERE Age > 25 AND Salary > 40000 -- AND Status = 'Active'

Subject:	Good article
Posted by:	graymer (view profile)
Posted on:	Friday, February 05, 2010 at 11:59 AM
Message:	One of the things I do in my own formatting that isn't in the norm but is handy is to place the commas infront of the items when I have them in a column format instead of behind. For example: Select blah , bah , ah Instead of: Select blah, bah, ah, Since I'm a indentor the front side of my column is always lined up. When the commas or any other kind of delimiter is in front or "@" with variables, a quick alt + mouse select can grab the column of commmas and strip them out or strip the "@" of the front of that column and I can cut and paste the whole column somewhere else which comes in handy. I do know that the replace tool comes in pretty handy too, but with this I can also see upfront that everything is in place and ready to go.

Subject:	Could not stop lauging
Posted by:	Ed.Carden (view profile)
Posted on:	Friday, February 05, 2010 at 2:58 PM
Message:	Great piece Phil and done in an artisticly sarcastic way. Note to those who like their commas at the begining of a line for easy commenting out: You can easily comment out yoru lines and still have the comma at the end by adding a new line (after the last item in your list) that has no effect on the results like a 0 or an empty string. Exampe: THIS: SELECT 1 ,2 ,3 Becoimes This: SELECT 1, 2, 3, 0

Subject:	I am not so sure about collective names
Posted by:	Alex_Kuznetsov (view profile)
Posted on:	Tuesday, February 09, 2010 at 10:52 AM
Message:	Phil, I have concerns about "the use of a collective name for the entities within a table is best". Even native speakers get confused whether a collective for employees is Team or Party. What is the collective for bikes - Gang or Team? Table School in scientific context - is it for fish or for universities? Not to mention that collective names complicate code generation.

Subject:	Hmmm
Posted by:	Mark Robbins (view profile)
Posted on:	Wednesday, November 10, 2010 at 6:08 AM
Message:	"Women between the ages of 15 and 45 who have had at least one live birth in the last 12 months" RecentMothersOfChildbearingAge MothersOfChildbearingAgeWithInfantsLessThanOneYearOld ParentsOfChildrenYetToHaveABirthdayWhoAreCapableOfBearingChildren F15To45_LiveBirthLast12Mo

Subject:	About indenting and line breaks
Posted by:	Mark Robbins (view profile)
Posted on:	Wednesday, November 10, 2010 at 6:20 AM
Message:	I think its basically crazy to let your code run on until it hits 80 cols (or more). The main reason to break it into the running columns IS for legibility. But more importantly, for editability - lines are more easily shifted around, copied and mutated. Think of it this way, when you format, you are adding information -- metadata about whats going on. You learn to recognize this structuring, and can navigate easier through code. "Painting rocks white" my butt.

Subject:	history
Posted by:	DVroman (view profile)
Posted on:	Saturday, March 24, 2012 at 1:05 PM
Message:	It wasn't until we no longer did programming on IBM machines that we were able to break the variable size and line length limitations. Historically, FORTRAN was the father of these limitations. COBOL just followed suit.

Object Naming Conventions.

Tibbling.

Pluralising

Abbreviating (or abrvtng)

[Escaping]

Restricting

Coding layout

Indenting and line-breaking.

Line Breaks

Indenting

Formatting lists

Punctuation

Capitalisation

So get off the fence

Further reading