Av rating:

Total votes: 52
Total comments: 30

send to a friend

printer friendly version

SQL Code Layout and Beautification

11 May 2008

by William Brewer

William Brewer takes a look at the whole topic of SQL Code layout and beautification, an important aspect to SQL programming style. He concludes that once you are tired of laying SQL out by hand, you had better choose a tool with plenty of knobs to twiddle, because nobody seems to agree on the best way of doing it.

The problem

Badly-formatted SQL is the bane of my life. That is because my life is spent staring at SQL Code. Other peoples SQL code often looks ridiculous to me. As there is no common standard for laying out TSQL, even for SQL Server, we all have strongly-held opinions on how to lay out code. I've yet to find two database developers who agree in detail on how it should be done. I've seen entire projects written in lower case, with Javadocs headers, others done entirely in upper case with hardly any line-breaks.

The reason that every database developer has such strongly held views is that he gets used to a particular format. I find that I can read SQL code that is laid out to my taste far faster than SQL code written inconsistently or to one of the wilder fringe-formats. Actually, it is worse than that, going back to look at old code that I've written can be a painful experience if the old code is written to a style that I've since discarded.

The worst experience a database developer can suffer is to be obliged to support a SQL application that has been written by someone else. It is difficult to look a programmer in the eye after you've had to maintain his SQL code. A SQL Code Beautifier is the only way to stay sane and keep friendships.

Some solutions

I was brought up with CB, the C beautifier, and BCPP the C++ beautifier. Almost every language has a beautifier. Some text editors such as Visual Slickedit even have beautifiers built-into them. I've never found a quick and simple way of beautifying SQL code. Just in case you don't know, a code beautifier just makes the best of what you produce. It doesn't criticize. A 'Lint' will tell you what's bad about your code, telling you about things like deprecated syntax, unused or uninitialised variables, errors, dangerous techniques and so on.

There are a couple of unique problems with beautifying SQL.

It isn't easy to lay out SQL code since the language was designed to be easy for mere mortals rather than conformant to the standards of procedural languages: an example of this is that certain keywords mean different things in different contexts. The only way for a utility to understand SQL well-enough to lay it out to reflect its meaning is to parse it. A TSQL parser isn't the sort of thing you knock off on a wet Friday.

A lot of shops, especially in the larger enterprises, have existing standards for SQL code, and any beautifier has to be configurable to comply with these standards. Besides Joe Celko's brave attempt at publishing a standard for the layout of SQL code, you'll find that code that meets the standards of one company is no dice with another one.

What tools are there?

SSMS/Query analyzer add-ins

I challenge you to find a better TSQL code beautifier than Red-Gate’s strangely-named SQL Refactor. This is a fairly easy challenge because there are very few others on the market, and many of them seem to die in mysterious ways, crash the system, or have stupid restrictions on their evaluation versions. One recent arrival is SQL Enlight, which is surprisingly like parts of SQL Refactor’s beautifier, It needs work before it is serviceable, although it is improving rapidly, Its worst fault is that it makes silly mistakes such as trying to layout strings and comments with tabbification! It has the start of a Lint facility as well as a beautification, but it is not as advanced as SQLTAC’s

I suppose I’m easily confused, but I originally came across the Beautifier in SQL Refactor almost by accident. I’d been using SQL Prompt as a sort of primitive beautifier in the Query Analyser when I programmed in SQL Server 2000. (It will do simple things like putting keywords in upper-case). SQL Refactor wouldn’t work in the Query Analyser whereas SQL Prompt worked with everything, so I didn’t then pay SQL Refactor the attention it deserved. When I got to SSMS and SQL Server 2005, I discovered the delights of SQL Refactor, and so I stopped using SQL Prompt. SQL Refactor did everything I wanted.

Red-Gate have, at long last, released a version of SQL Prompt, SQL Prompt 3.8 Pro, that has SQL Refactor’s excellent Beautifier. It will now lay existing SQL code, as well as the code you are typing in. This is more exciting than you might think, because SQL Prompt will work in Visual Studio and Query Analyser as well as SSMS, so all those poor folks stuck in those corporates that still haven’t moved to SQL Server 2005 will, at last, have a decent SQL Code formatter!

Online tools

Wangz Instant SQL Formatter. This performs the function of Beautification and prettification. It has the great advantage of being free. SQLInformm is another one which has interesting features.

There is some confusion between a Beautifier and a Prettifier. A Prettifier, such as Simple-talk's Prettifier, is a program that renders a block of code so it can be published. (printed, put in a website, or intranet, or into PDF). It doesn't attempt to rearrange the code but merely render it as the programmer intended.

The problem with online Beautification is that it is more of an effort for the programmer. There is a lot of difference between merely clicking a menu item within your chosen IDE, and having to cut and paste into a website. The worst problem, though, is that they generally don't remember your detailed preferences between sessions.

The task

So what does a beautifier do?

Any SQL Code beautifier must parse the code to work out what each token means in its context. (AND , or a bracket, for example) It then lays out SQL code as follows:

To maintain a consistency in the way that space is laid out between statements.
To group together distinct parts within the statement. Simple statements are generally moved onto a single line and complex statements are divided up logically so that each part of the statement is separated with a line. Any white space preceding a semicolon that ends a statement is generally removed.
To enforce consistent indentation
To enforce whatever rules you decide for the capitalisation of tokens

The problem with attempting any general rules about layout is that SQL plays a range of roles, such as DML and DDL. Not only that but it allows complex expressions and supports both blocks and conditional statements. The rules you use for laying things out will be different for different purposes. Not only that, but because a single Select statement can go on for pages or could be no more than a handful of tokens, your rules need to be different for different types of Select statement.

A good example of this problem is the way that parentheses are used. In SQL, parentheses are used in a number of different contexts, such as mathematical expressions, schema statements, function parameters, derived tables and subqueries. For the purposes of layout, it is important to distinguish the types of parentheses. Where, for example, parentheses are used in expressions to enforce the order of evaluation, or for the parameters of mathematical/logical functions, you might wish to treat them differently from the parentheses that delimit a subquery. If you try to create general rules for parentheses, then your code is bound to look odd.

Fortunately, there are a few general rules about beatification that we can deal with

General rules

Commas

Commas should be used in exactly the same way as in English grammar, there should be no space before the comma, and one space after the comma to separate it from the next token.

SELECT Orders.ShippedDate, Orders.OrderID, "Order Subtotals" Subtotal,
DATENAME(yy,ShippedDate) AS YEAR
FROM Orders

Commas should never be at the start of lines. Surprisingly, there will be those that disagree, and so it is possible to have a space before the comma, start lines with commas and so on. You can even have the commas lining up with the start of the select statement if the mood takes you. I've come across SQL Layout conventions that require that the comma is left aligned with the statement start or that the comma is placed close to the column. It should be possible to specify the minimum number of items that the list should contain before it is split into several lines, or the maximum number of columns before a line break is inserted after, or before, the comma.

Indentation

When formatting multiple-line statements, the first line is normally not indented as far as the subsequent lines, so it is easy to see the start of the statement..

SELECT Employees.Country,
      Employees.LastName,
      Employees.FirstName,
      Orders.ShippedDate,
      Orders.OrderID,
      "Order Subtotals"Subtotal AS SaleAmount
   FROM Employees INNER JOIN
     (Orders INNER JOIN "Order Subtotals"
            ON Orders.OrderID = "Order Subtotals"OrderID
         )
   ON Employees.EmployeeID = Orders.EmployeeID
   WHERE Orders.ShippedDate BETWEEN @Beginning_Date AND @Ending_Date

Statements that contain a block of SQL code to be executed (such as IF statements) are indented to group the block.

You can set up the number of spaces to use for a tab stop, though conventional wisdom suggests that three spaces is the best setting. Some people like to indent statements by the number of the characters in the initial keyword plus one character, though this seems to me to spread code too widely across the page without adding much to its clarity. It just panders to the mystical urge to make things line up.

Wrapping

Just like the text on the page, SQL code is likely to need to be wrapped. I generally like to wrap SQL code at around the 80 column point, just so it is visible on the printed page when printed in 12 point Courier

Capitalisation rules

The tokens in SQL Server can be categorized as follows

Reserved words (e.g. SELECT, DECLARE, MESSAGE_LENGTH, ROLLBACK)
Scalars (column names, parameters, variables)
Schema object names (names of tables, views and stored procedures)

Although you will find fanatics who will insist that everything should be in lower case, it is more likely that the three different types of token will need to be handled differently. A Beautifier should allow the user to specify the way that you apply rules about use of capitals and lower-case.

I'd like to be able to choose from the following options

Make it lower-case
Make it upper-case
Make it lower-case but for an initial capital
Take the style from the initial declaration or existing database schema.
Use the style from the schema, but enforce an initial capital and a capital after a _ character

You'll note that I don't mention CamelCase because there is no way of enforcing it. If, however, your definition of a stored procedure goes like:

ALTER PROCEDURE [dbo].[SalesByYear]
@Beginning_Date DATETIME, @Ending_Date DATETIME AS
----do something clever here

Then every time your code mentions the procedure, the capitalisation should be changed to the way it is initially done in your definition, along with its variables. (SQL Prompt is helpful for doing this)

So that...

EXECUTE salesbyyear @beginning_DATE='1 jan 2000',
@end_DATE='1 jan 2000'

...Gets changed to...

EXECUTE SalesbyYear @Beginning_Date='1 jan 2000',
@Ending_Date='1 jan 2000'

The same should go for any other DDL

My take on the rules is as follows.

Reserved words.: These should be in upper case so as to make it easier to pick them out from a large block of SQL..
Scalars.: These look better in Lower case, unless they represent an abbreviation
Schema Object names: I prefer CamelCase for object names, with an initial capital, because they ought to be one word and one can pack a lot more readable description into CamelCase whilst making it obvious that it is one token.

Beautifying expressions.

You may want to reformat string, or numerical, expressions in DML statements. It helps to distinguish this sort of expression from the conditions in a ON, WHERE, HAVING clause because of the particular keywords such as BETWEEN, and the slightly different syntax.

Operator placement

Some styles demand that a line be broken when a particular operator token (e.g. AND, OR or NOT) is used. If so, then the break should be either before, or after, the token, depending on the style chosen

Operator spacing

Ideally, we should be able to decide on separate rules for arithmetic, comparison, unary, assignment and logical operators. These should, some believe, have a space before and after them. It should be possible to leave them alone, to enforce spaces or to remove them. Should there be a line break before or after them, and should the line break only happen at a particular nesting level in the expression?. What should be the minimum number of logical operators in a logical expression before the line break is inserted, and which type of logical operator should trigger a line-break?

Parentheses

Opening Parentheses within expressions should, in some layouts, be on a new line. However, it could be that the contents of parenthases should be on a new line, and suitably indented by placing the line break after the opening parenthesis The closing parentheses can be handled in a number of ways. The closing parenthesis is placed on a new line, or the line break is added after the closing parenthesis. There should be an option to leave the formatting of parentheses alone.

If parentheses are given a line-break, then how should the contents be indented?

Now this isn't particularly straightforward. How should you format the following SQL Statement which is basically a mathematical expression? I doubt if you will find two programmers to agree, so the rules have to accommodate this.

-- Calculate the Correlation between two normally-distributed
-- variables contained in two columns in a table
SELECT Correlation=
    (
  SUM((FirstVariable*1.0 - AVG(FirstVariable*1.0))
            * (SecondVariable*1.0-AVG(SecondVariable*1.0)))
       / COUNT(*)
  )
    / (
    STDEVP(FirstVariable*1.0)*STDEVP(SecondVariable* 1.0)
    )
FROM MyTable

Beautifying Schema (DDL) Statements

There should be a number of options that act on data statements such as SELECT, INSERT, DELETE, and UPDATE

The length of a data statement will vary enormously, and the way that you will want to format it will be different according to its length. There will, at least, be a length below which the statement should be in one line.

Parentheses

How should parentheses and definitions in CREATE and ALTER statements be formatted? Should opening parentheses be on a new line? Should the first definition be placed on the line below the opening parentheses. If you believe that either is correct then should you indent the parenthesis and the definition, the contents of stored procedures, functions, DDL triggers, or DML triggers, or should you align them with the start of the statement?.

You should be able to opt to pad the inside of parentheses with spaces, or remove any existing padding. It is better not to add padding inside parentheses, around function parameters, column alias lists, and data types.

Column placement

It is often the practice to place the first column of a list on a new line, and many who will argue that every subsequent column in the list should be on a new line. I would only add a new line if, by leaving the column list item where it was, it exceeded the column width, in much the same way that text is wrapped. Some people argue that one should line up the columns on the end of the Select keyword. I find this a strange practice quite different from general programming practice, but any Beautifier has to accommodate the practice.

There is also the issue of how commas are treated. I prefer to handle the same way as in written English, but some people like to insert a space before, as well as after, a column so as to make each column definition stand out.

Join placement

The keywords that form the various parts of a multi-line DML expression should line up if they are on separate lines so it is easy to read the structure of the statement. In particular, the JOIN keyword and the FROM keyword should align, and the FROM should generally be on a new line. The JOIN conditions should probably be on a new line indented from the JOIN keyword,

You may want to specify whether the Join condition is placed on a new line or on the same line as the join. And whether it is indented. Should it be aligned with the statement. Or the table sources in the join list. How are you going to tackle the indentation of nested joins?

Beautifying DDL (Definition) statements

Schema statement layout rules control a set of options that can modify the way schema statements are written. Actually, I would only include tables and views in this as these are the only cases where special formatting is required.

Column definitions

The main formatting issue that is special to DDL is the list of columns. How should they be indented? Because they are never nested, this is a case where I prefer to indent rather more generously than in a routine, where there is likely to be far more levels of indentation.

With table definitions, it it is customary to always enforce a line break after each column definition . However, this is not always the case.

Parentheses

How should parentheses and definitions in CREATE and ALTER statements be formatted? Should the opening parentheses be on a new line? Should the first definition be placed on the line below the opening parentheses? If you believe that either is correct then should you indent the parenthesis and the definition, the contents of stored procedures, functions, DDL triggers, or DML triggers, or should you align them with the start of the statement?.

Here is how I'd choose to format a simple table definition. I doubt if you'd get even a majority to agree that this is the right way

CREATE TABLE dbo . Categories
  (
  CategoryID INT IDENTITY(1,1) NOT NULL,
  CategoryName NVARCHAR (15) NOT NULL,
  Description NTEXT NULL,
  Picture IMAGE NULL,
  CONSTRAINT PK_Categories PRIMARY KEY CLUSTERED
     (
      CategoryID ASC
     )
  WITH
     (
     PAD_INDEX = OFF,
     STATISTICS_NORECOMPUTE = OFF,
     IGNORE_DUP_KEY = OFF,
     ALLOW_ROW_LOCKS = ON,
     ALLOW_PAGE_LOCKS = ON
     )
  ON PRIMARY
  )
ON PRIMARY TEXTIMAGE_ON PRIMARY

This is why it is so important that any SQL Beautifier should be able to have lots of knobs to twiddle to allow you to get it how you want it.

Conclusions

I can't see any chance in a general consensus on how we should format SQL. There isn't even a real detailed consensus in languages like Java. In the meantime, SQL Beautifiers that lay out SQL in a particular style, and to particular rules, should allow the programmer to change the rules to suit him/herself and to come with a whole range of ready-made styles. I'd suggest that the same is true of a SQL Lint, when anyone is ever brave enough to come out with one.

This article has been viewed 11173 times.

Subject:	SQL Assistant
Posted by:	Matt (view profile)
Posted on:	Monday, May 12, 2008 at 10:09 AM
Message:	One more addin for your list: http://www.softtreetech.com/sqlassist/index.htm

Subject:	Read it like a book
Posted by:	Catherine (not signed in)
Posted on:	Tuesday, May 13, 2008 at 10:53 PM
Message:	Agreed that formatting is important. I'm of the opinion that it should be "readable" - if you can't read it easily, then don't write it. Things like policy on aliasing should also be included if "readability" is the desired outcome - rather than "beautiful".

Subject:	Clear & Self Documenting
Posted by:	PCNuttall (view profile)
Posted on:	Tuesday, May 13, 2008 at 11:53 PM
Message:	I agree with most the ideas, but not all the formating. I perfer to have the commas leading rather than trailing. I also perfer that everything lines up rather than the incremental indentation that was shown. Each statement should highlight what it does without adding confusion. Blocks of statements (within BEGIN END should line up on the left. Like the post above.. It should read like a book, only with every statement separated like a new paragraph

Subject:	Re: Agreeing
Posted by:	WBrewer (view profile)
Posted on:	Wednesday, May 14, 2008 at 3:19 AM
Message:	This is one of a minority of articles on SQL where one needn't feel obliged to agree or disagree. Layout really is a matter of taste. There are only two points in the article that I would like you to agree with. One is that any SQL Beautifier must be highly configurable so as to conform with whatever style is in force in your workplace, and should remember your settings! The other point is that you should always select the most readable SQL layout style if you are working in a team. Apologies for missing SQL Assistant. I should also have mentioned the third-party SQL Server IDEs that have layout/Beautification built-in. Perhaps anyone who has used them in the workplace could please chip in with an opinion

Subject:	Security vs. Online tools
Posted by:	vi (not signed in)
Posted on:	Wednesday, May 14, 2008 at 10:49 AM
Message:	Isn't their a security concern with using online tools? When you submit code to be parsed, You are sending open text across the wire with sql code that reveals your database structure info and sql code most likely to be used in your production environment(all traceable to your ip address). The 3rd party hosting the website could be recording/sharing all that database information for malicious purposes.

Subject:	On the subject of beautification
Posted by:	Tim Cullen (not signed in)
Posted on:	Wednesday, May 14, 2008 at 11:19 AM
Message:	I agree with a good portion of the article, with two exceptions. First, I put the commas at the beginning of the new row for each column I'm working with...that way you can comment things that aren't working out easier during development/testing. Second, which is one you used in an example, is using keywords for column names (i.e.Year in the example above). Using keywords for columns can be a bit confusing. Thanks for the great article! Tim

Subject:	SQL Code Layout and Beautification
Posted by:	Anonymous (not signed in)
Posted on:	Thursday, May 15, 2008 at 7:49 AM
Message:	Hi, Nice article. This topic could be considered as a generic "coding best practice" for a group of developers. I learned my formatting style from the George Koch book on Oracle. I would appreciate it if you would cite the Joe Celco article or book. Small SQL is easy to format, but as it gets larger, then it becomes cumbersome and time consuming to format manually, and it's easy to let a few inconsistencies slip through (which then makes future maintenance more difficult) After you get used to a style, it's hard to read another style, or to write another style. Which brings me to wonder what is "best practice". A lot of examples of different approaches would be very good. Especially since "best practice" should mean readability by more than one person, or readability in the future by the same person. (For example I dont prefer the extra indentation shown, however I would consider that way carefully, since it conserves line width, and it's good to see someone more experienced show another way to format) In that light, different ways of commenting SQL should also be considered as part of the formatting. Having a 14 day trial on the software you prefer is nice; however if you use another database that doesnt help you learn what the tool could do in SQL Server. What would be good is a PDF download with a lot of examples. Simply using ANSI SQL to illustrate would be good. When people learn something, it stays with them, so learning from a good tool would definitely prepare them for any ANSI SQL authoring, as well as prepare them to use such a tool later or in a different job. Most people I know end up using several database engines. I dont know if there is a two-way SQL to code and code to SQL tool which allows you to pull SQL from the code (like Java or VB or C#) and drop it into a SQL formatting and execution environment and vice versa. It's a real pain to go back and forth during debugging; particularly if you are using variables passed into the SQL statement, or variables as parameters for stored procedures (you will need to make a substitution in order to execute it for debugging, and possibly multiple values). It's a difficult and time consuming part of formating to convert back and forth to a "String" value for use in code. For my personal use, software would have to be in the $30 or less range, and it's OK if it's only SQL Server compatible. For my professional use, I would have to have a multi-database capable tool, and $300 is acceptable. This reminds me somewhat of the way anti-virus vendors provide a "free" personal use version to help keep viruses from spreading from home to work. A "free" or very low cost non-commercial use license for something like a formatting tool would be interesting, in order to help programmers learn (at home) to format better for team and maintenance efficiency.

Subject:	Joe Celko's SQL Programming Style
Posted by:	WBrewer (view profile)
Posted on:	Thursday, May 15, 2008 at 8:06 AM
Message:	http://www.simple-talk.com/community/forums/thread/1512.aspx http://www.amazon.com/Celkos-Programming-Kaufmann-Management-Systems/dp/0120887975 for some reason, on Simple-Talk, we always spell Joe Celko's name Celco, even though we're great fans of his writing. Perhaps it is because the editor is a dyslexic.

Subject:	And then there is Microsoft's attitude
Posted by:	Ralph Wilson (not signed in)
Posted on:	Thursday, May 15, 2008 at 2:01 PM
Message:	One of my real irritations in working with SQL Server to build views, etc., is that Microsoft has taken the attitude that everyone else is wrong in the way that they format SQL statements and, as a result, will arbitrarily reformat your SQL into a nearly unreadable mess. I believe in using reasonably short aliases for tables and in following pretty much the conventions you outlined. Microsoft, in its infinite wisdom/arrogance, mangles my neatly formatted SQL beyond recognition!

Subject:	Wangz SQL Beautifier
Posted by:	Anonymous (not signed in)
Posted on:	Friday, May 16, 2008 at 10:53 AM
Message:	The Wangz beautifier is not only an excellent & free online tool, but it is also an excellent & free SSMS add-in! Use it every day

Subject:	Standard Formatting ugly
Posted by:	Robert (view profile)
Posted on:	Monday, May 26, 2008 at 12:26 AM
Message:	Good Formating -uses all CAPITALS -1 column per line -Aliases - AS statement - ON line after JOIN statement like SELECT A.COL1 AS MYCOL1 ,B.COL2 AS MYCOL2 FROM TABLEA A JOIN TABLEB B ON( A.PK = B.PK )

Subject:	Thank you for the plug ..
Posted by:	Joe Celko (not signed in)
Posted on:	Monday, May 26, 2008 at 7:25 AM
Message:	Thanks for the link and the plug for my book. I did research on code formatting for the US Army and I set type for years, so my book goes into reasons for each decision rather than opinion. >> on Simple-Talk, we always spell Joe Celko's name Celco .. Perhaps it is because the editor is a dyslexic. << Nay! He's probably Italian. That is the Italian version, since they have no K. The original Slavic has a hychek (upside down ^) on it for a "ch" sound.

Subject:	Re: Thanks for the plug
Posted by:	Andrew Clarke (view profile)
Posted on:	Monday, May 26, 2008 at 7:51 AM
Message:	Joe's books are all well-worth reading. We rate them highly here at Simple-talk.

Subject:	Leading comma's
Posted by:	Jeff Moden (view profile)
Posted on:	Monday, May 26, 2008 at 9:02 AM
Message:	I know it's possible to format code in these posts because I've seen it done... I just don't know what formatting is used, so pardon the mess if this comes out wrong. Since the subject of preference came up... I've seen the argument, many times, that leading comma's make it easier to comment out columns. So, let's consider the following... --===== Trailing commas SELECT ColumnA, ColumnB, ColumnC, ColumnD FROM SomeTable --===== Leading commas SELECT ColumnA ,ColumnB ,ColumnC ,ColumnD FROM SomeTable In the "Trailing Commas" example, columns A through C are very easy to comment out. Column D is a pain because of the trailing comma left on Column C. In the "Leading Commas" example, columns B through D are easy to comment out. Column A is a pain because of the leading comma left on Column B. The leading commas are cute and maybe even useful to some folks... I guess it's a matter of what you're used to or personal preference. Don't play the old saw about leading commas making columns easier to comment out because it really doesn't. It just shifts the ease to include the last column instead of the first. For me, SQL is supposed to be an "English-like" language... I've never seen a sentence in a book start with a comma so leading commas doesn't seem like a natural read to me. By the way, for those who want to be able to easily comment out ANY column, the format required is in the code that follows... SELECT ColumnA , ColumnB , ColumnC , ColumnD FROM SomeTable Heh... imagine that with a 20 column Select! And why all the emphasis on commenting out columns, anyway? Production code is supposed to be pretty well tested and stable. It's takes nearly zero seconds to comment out any "middle" column and only slightly longer for either the first or last column for testing no matter how it's formatted.

Subject:	I was right about formatting...
Posted by:	Jeff Moden (view profile)
Posted on:	Monday, May 26, 2008 at 9:05 AM
Message:	Yeah, I figured this would eat the leading spaces in the code above... can anyone tell me what format I should use to keep things formatted on these posts? Would it be HTML, IFCode, or ???. I don't need something like a prettifier to post the code... I just want something that will preserve leading spaces and used a fixed space font like Courier New. Thanks, folks.

Subject:	begin-end blocks
Posted by:	Fred Williams (not signed in)
Posted on:	Monday, May 26, 2008 at 12:41 PM
Message:	Of course, when the comment-posting tool reformats one's example, it makes communication more difficult... Imagine all lines in the above example indented, except the IF and the ELSE: IF <conditional> ________BEGIN ________<Do TRUE stuff> ________END ELSE ________BEGIN ________<Do FALSE stuff> ________END

Subject:	Just testing... please ignore this post...
Posted by:	Jeff Moden (view profile)
Posted on:	Monday, May 26, 2008 at 5:56 PM
Message:	Just testing... please ignore the following test... <pre style="font-size: 12px;"><font color="black"><br></font><font color="green">--===== Trailing commas<br></font><font color="blue">SELECT </font><font color="black">ColumnA</font><font color="gray">,<br></font><font color="black"> ColumnB</font><font color="gray">,<br></font><font color="black"> ColumnC</font><font color="gray">,<br></font><font color="black"> ColumnD<br></font><font color="blue"> FROM </font><font color="black">SomeTable<br><br></font><font color="green">--===== Leading commas<br></font><font color="blue">SELECT </font><font color="black">ColumnA<br></font><font color="gray"> ,</font><font color="black">ColumnB<br></font><font color="gray"> ,</font><font color="black">ColumnC<br></font><font color="gray"> ,</font><font color="black">ColumnD<br></font><font color="blue"> FROM </font><font color="black">SomeTable</font></pre>

Subject:	SQL Server Development Coding Standards and Real Ale
Posted by:	Ted Cooper (not signed in)
Posted on:	Tuesday, May 27, 2008 at 9:39 PM
Message:	I'm in the San Francisco Bay area and the closest thing we get to real ales is "barleywine style." Our local brewer "Anchor Steam" makes a delicious "Old Foghorn" (and a darn good gin "Junipero") which is exquisite. Barley wines have really caught on with the "brewtique" breweries only just this year (2008) and now there's a plethora out there to relish. I would welcome suggestions on where to visit in Britain for true real ales as well as comments on recipes for barley wine style home brews. As for T-SQL standardization my opinion is that it should be based on how the human mind works, i.e., one construct per line per indentation as in: SELECT HisBarleyWine AS 'Worse', OverMine AS 'Better' FROM MyBarleyWine mbw LEFT OUTER JOIN BillsRealAle bra ON bra.Flavor = mbw.Flavor WHERE mbw.Style >= bra.Style AND YourStyle = WhatWorksForYou ... As far as recommendations to companies about standardizing goes my opinion is forget it - management really doesn't care about coding standards. If they do then they are micromanaging. Their time is better spent managing the expectation of what is delivered to the customer. Thanks! Ted Cooper tedcooper@cooperconsultants.com

Subject:	indentation
Posted by:	Anonymous (not signed in)
Posted on:	Thursday, July 31, 2008 at 6:44 AM
Message:	Anyone who uses this standard? (I recently "switched" to leading comma's: much less error-prone...) SELECT ··a.col1 AS c1 ··,a.col2 AS c2 ··,b.col3 AS c3 FROM ··dbo.tablea a ··LEFT JOIN tableb b ON b.a = a.id WHERE ··a.col1 > 0 ··AND b.col1 IN ··( ····SELECT ······c.col1 ····FROM ······tablec ··) ··AND ··( ····someOtherCondition ····OR yetAnotherCondition ··)

Subject:	'I don't need something like the Prettifier'
Posted by:	Phil Factor (view profile)
Posted on:	Monday, May 26, 2008 at 10:34 AM
Message:	Fortunately, the editor has given me special powers to pretty code in comments, using the Prettifier, when the mood takes me, which it just has.... http://extras.sqlservercentral.com/prettifier/prettifier.aspx?skin=st Unfortunately, we're wrestling with a rather archaic system here which doesn't allow any formatting, so we have to improvise--

Subject:	IF ELSE question
Posted by:	Tom Garth (view profile)
Posted on:	Monday, May 26, 2008 at 11:18 AM
Message:	Great article, especially the lead in discussion on readability. One item I have yet seen discussed, is the formatting of conditionals. Maybe I'm just too anal, but I can't seem to find a method that is completely satisfactory for me. The basic... IF <Some conditional> BEGIN <Do TRUE stuff> END is easy enough, yet still has alternatives (like should the BEGIN be on the same line), but expand it a little... IF <Some conditional> BEGIN <Do TRUE stuff> END ELSE BEGIN <Do FALSE stuff> END and it just don't look right. I've used several methods of trying to make it appear more like it's meaning,... END ELSE BEGIN <Do FALSE stuff> END and... END ELSE BEGIN <Do FALSE stuff> END but just can't settle on one, especially when there are nested conditionals. Has anyone ever seen a "RULE" for this? Just curious.

Subject:	Wow! That stunk...
Posted by:	Jeff Moden (view profile)
Posted on:	Monday, May 26, 2008 at 5:57 PM
Message:	Phil, Archaic system or not, you've gotta let us know how to post formatted code on this forum. Gotta template by any chance?

Subject:	Re:Archaic System
Posted by:	Phil Factor (view profile)
Posted on:	Tuesday, May 27, 2008 at 3:31 AM
Message:	There ain't no way I know of, Jeff. Yours would have worked had you been doing a Simple-Talk forum entry. I've been using my special powers, granted me by the editor, to format the code in these comments as it is particularly embarrassing to have a good article on code formatting and not allow formatted code in the discussion. Help is almost at hand, Jeff. We're due to launch a new Scriptorium on SSC soon where you just stick the code in and it formats itself. If you are mixing text and code you just put the code between [SQL] [/SQL] tags.

Subject:	Another Goal for Beautification is Change Management
Posted by:	Anonymous (not signed in)
Posted on:	Tuesday, May 27, 2008 at 7:42 AM
Message:	For me, human readability is the number one goal for beautification. Another reason is to support change management. When code is changed and checked in, it should be obvious to the user what changed. You don't want a line that is long and difficult for the human to see why it has changed. So, rules such as one column per line go a long way in support of that. I like to go a step further and limit to one clause per line too. KenS

Subject:	SQL Prompt & Prettifier
Posted by:	Anonymous (not signed in)
Posted on:	Tuesday, May 27, 2008 at 7:51 AM
Message:	We just got both of these and everyone thinks they're great! (No, I'm not a Red Gate employee!)

Subject:	Barley Wine
Posted by:	WBrewer (view profile)
Posted on:	Wednesday, May 28, 2008 at 5:48 PM
Message:	There was once a craze for Barley Wines here too. Although there seems to be some historical use of the term, I think that the modern usage, meaning a beer of 10%-12%, must have been invented at about the same time as the ridiculous 'Ploughman's lunch'. It is, of course, a marketing term. I do, however, recommend Fuller’s Vintage Ale (8.5%). Normally, I'm a bit wary of anything over 5.5% as I'm a traditionalist, but I make an exception of Fuller's. The West Coast has some of the best beers in the world. The IPAs are generally very well done indeed but I've also had some excellent Californian stouts. I recently had a good look at micro-breweries in Colorado. Top marks go to Rosie's Brew Pub in Leadville, Colorado, 80461 at 14,000 ft above sea level, and The Walnut Brewery, Boulder, Colorado. Their beers would have won prizes in Britain. The joy of beer is variety, which is why I don't like recommending a particular British Beer. There are so many good ones now. When visiting the UK check out CAMRA's guide, which is excellent. http://www.camra.org.uk/page.aspx In much the same way. there are many subtle flavours of SQL Formatting and layout and I wouldn't wish to recommend one particular formatting style. The joy of writing SQL is in varying the layout to suit the content and context to make it as meaningful as possible.

Subject:	A couple of terms: "Wall Hugging" and "Wall Huggers"
Posted by:	LANdango (view profile)
Posted on:	Thursday, June 12, 2008 at 9:06 AM
Message:	Recently I have been involved with working with a security audit and impact analysis with a couple hundred stored procedures written by a number of "DBAs" and junior developers. Thanks Bill for writing this article! I'm really glad others have posted because not only is formatting bad nearly everywhere I go, I find that lack of comments and proper formatting will be (not can be) problematic. All too often when I open a stored procedure, trigger, or function for review, I see that the person who coded it must have broken the tab key ;) and have every line of sql lined up along the left side: SELECT column1, column2, column3 from table t join table2 t2 on t1.id = t2id where t1.type = 'A' and t2.somecolumn = 'B' order by t1.name, you get, the, point I've given the name for the type of t/sql "Wall Hugging" SQL and the person who coded it a "Wall Hugger". This goes for any code really. I'm not normally a person to give a person a tag or title, but it's sure to make friendly ribbing of a junior co-worker or a former employee's coding practice. "C'mon Tom! Back away from the wall!" Wall hugger! DISCLAIMER: THIS BLOG POST IS NOT INTENDED TO OFFEND PEOPLES IN ANY PROFESSION, COUNTRY, RACE, SEX, SEXUAL ORIENTATION, POLITICAL ORIENTATION, NOR SOFTWARE DEVELOPMENT LANGUAGE. NO SMALL ANIMALS WERE HURT NOR ENDANGERED DURING THE CREATION OF THIS POST. ;-)

Subject:	Preferences...
Posted by:	Anonymous (not signed in)
Posted on:	Thursday, July 31, 2008 at 6:42 AM
Message:	Anyone who uses this standard? (I recently "switched" to leading comma's: much less error-prone...) SELECT a.col1 AS c1 ,a.col2 AS c2 ,b.col3 AS c3 FROM dbo.tablea a LEFT JOIN tableb b ON b.a = a.id WHERE a.col1 > 0 AND b.col1 IN ( SELECT c.col1 FROM tablec ) AND ( someOtherCondition OR yetAnotherCondition )