SQL Coding Standard - Things On The Right End Of The Line

Transact-SQL
41

The compile time savings from compressed code would be hard to measure, I think. I'd expect the diffs to be very, very small. In any case it's only an O(1) difference, so not really significant. However gobs of comments will take up cache space. That might turn out to be significant in a busy system with lots of heavily-commented procs. Again, it would be hard to measure effectively.

42

I could stick a MB of rubbish into each of our most commonly called Sprocs on our busiest production system for a week or two ... and then measure the volume of abuse from the users :smile:

43

For those folk that think that code formatting is important here's an interesting article by Aaron Bertrand that I have just read. There is virtually not one single point where I disagree with him.

For those folk that thing that code formatting is NOT important here's an interesting article by Aaron Bertrand that I think you should read :smile:

http://sqlblog.com/blogs/aaron_bertrand/archive/2008/10/30/my-stored-procedure-best-practices-checklist.aspx

44

I'm having a re-think about the choice between Comma at End-of-line or Start-of-next-line

I used to think that there was little difference between

SELECT
       Col1,
       Col2,
       Col3

and

SELECT
         Col1  -- My preference is to add leading-space for alignment
       , Col2
       , Col3

my view was that I am just as likely to want to comment-out the First line as the Last line

However, I don't like things at the end of the line, most especially "long lines" that are off the right edge of the screen, which might be overlooked; also things which don't align easily and thus need more eye-movement to spot that they are there (such as column aliases like "Col1 + Col2 + LongList AS MyAliasName")

Thus I prefer this:

SELECT
       Col1
       + Col2

to

SELECT
       Col1 +
       Col2

because (assuming "col1" was a long formula) I might miss the "+" at the right end of the line and assume that the line terminated with a "," and thus Col2 was another column.

On that basis I think I am better off with leading-commas because I think that this

SELECT
       Col1
       + Col2,
       Col3

is less readable than this:

SELECT
       Col1
       + Col2,
       , Col3

I hadn't thought of Leading-commas as "Line continuation descriptors" before, I'd only considered "+" and other arithmetic operators in that way, but my new thought is that is exactly what "," is, and having it at the start of the line is unambiguous as to how the previous line finished.

One thorn that comes about with this is the increasing need to use ";" as a statement terminator. The only thing that has needed that, of late, was ;CTE ... but now ;THROW needs it too. MS are avoiding adding reserve words (which might break existing code) and largely relying on statements being terminated with a ";" instead. I can't see any point putting ";" at the Right Hand End either - for the same reason - it gets lost / overlooked on a long line.

Also if you want to add a column to an ORDER BY (say) then

ORDER BY 
       Col1
       , Col2;

requires changing BOTH the Col2 line and adding the Col3 line to get

ORDER BY 
       Col1
       , Col2
       , Col3;

so perhaps

ORDER BY 
       Col1
       , Col2
       ;

is better because I can then just insert a row

       , Col3

On that basis I think I will carry on NOT using ";" at all - and just use it as a prefix to each statement

SELECT Col1
       Col2,
       ...
FROM MyTable
Where ...
ORDER BY ...
--
; SELECT ColX
       ...

and following that line of thought further I will only add ";" as a prefix to each statement that actually needs it"! - so basically continuing NOT to use it :smile:

45

I, OTOH, try to use semi-colon as much as possible. It certainly helps with reserved keywords, and also on the rare occasion when I need to/want to have two statements on a single line. This is a ridiculous example because you can use initialization while declaring, but something like this case:

DECLARE @name VARCHAR(32); SET @name = 'James';
DECLARE @HeIs VARCHAR(32); SET @HeIs = 'Great';

Regarding use of continuation comma at the beginning or at the end - an additional advantage that I see to using the comma the beginning is that, that makes it very easy to comment out columns in the SELECT list, GROUP BY list etc. when you are debugging.

That said, I end up inserting the commas at the end of the line when I am writing code.

46

I've been going with leading commas for a while now. And always use semicolons (ANSI again + heterogeneous environment).

As for other operators. I would write

select col1
     , col2
     + col3
     , col4
47

I usually indent continued lines

SELECT col1 
     , col2
          + col3
     , col4;
48

Useful feedback, thanks chaps.

I see that mentioned as a benefit, but I don't get it ...

SELECT
     Col1,
     Col2,
     Col3

I can comment out (without adjusting any other lines) Col1 or Col2, but not Col3

SELECT
       Col1
     , Col2
     , Col3

I can comment out Col2 or Col3, but not Col1. Even-Stevens I reckon ...

I have a similar usage. I have snippets of code to check on stuff / whatever, that use a #TEMP table and I pre-delete it "if it exists". Can't be bothered to d a check for Exists, so I used to just do:

DROP TABLE #Temp
GO
CREATE TABLE #Temp
(
...

but now I much prefer

DROP TABLE #Temp;
CREATE TABLE #Temp
(
...

Its one block of code, rather than two (and a separating GO) which I feel more comfortable with.

1 Like
49

This is logically 100% correct :smile:

Usually when I have a problem - for example, trying to insert data from a staging table where it may be all varchar columns of large widths into a destination table - and when SQL Server gives incomprehensible messages, I debug it by halves (binary search). First, I comment out half the columns at the end, and then if that succeeds, half of what is remaining and so on. In that case, I find that having to deal with commas is a pain when the commas are at the end.

But, your point well-taken. I can start my binary search by commenting out the first half of columns and then proceed from there and I would be fine when the commas are at the end of the line. In fact I hadn't thought of that up until now!!

50

Usually the first columns are the unique or strategic ones. :smiley:

51

I don't care for the commas at the front, because then I can't tell if a line is being continued or not. That is, as I'm readling:

SELECT col1
 , col2 + ... + expression + ... **--is this expression over ... can't tell until I see the next line, ARGHHH**
 + col3 --wait, it's still going
 , col4
52

I wouldn't have thought of that at all!! Useful tip though, now you've mentioned it :smile:

For me that's exactly the reason I like it. Before I start reading lines, from the left, I can see if it is a continuation, or a new column (i.e. starting with a ",").

I always used to start a continuation line with the "+" (i.e. "+" on left of continuation line, not at right of previous line) because that gave me a clue (on long lines) that it was a continuation line.

I am now thinking that the "," at the start of the line will do the same thing.

I'm only typing this just to clarify my thought, sorry if that was already obvious, hard to tell with forums and so much easier face-to-face when I can wave my arms about and see the reaction on your face!!

But clearly readability is different from person to person, and it comes down to deciding on a style - and then being consistent.

Hopefully the discussion is useful to others who are just starting out and wanting to decide on a style, or who have not given thought to how such things might effect productivity and reducing risk of bugs etc.

53

Formatting is key to readability of code. I also dislike right ended code, I prefer code to line up.
Things to the right can get missed. Also when there is a lack of new lines can make code unreadable.
I always place sql commands on their own line , columns line up, etc

SELECT
    Col1,
    Col2,
    CASE
        WHEN Col3 = 2
            THEN X
        WHEN COL3 = 3
            THEN Y
        ELSE Z
    END
FROM
    Table1 t1
LEFT JOIN
    Table2 t2
        ON t2.Col1 = t1.Col1
        AND t2.Col2 = t2.Col2
WHERE
    t2.Col5 = 345
AND
    (
        t2.Col3 = 43
    OR
        t2.Col4 > 56
    );

I also ensure I use spaces rather than tabs as tabs can vary. Whilst this can initially be a pain you quickly adjust and then the code I find more readable. We all have our own quirks but it's essential whoever comes to our code after we have moved on can read and understand it. This also brings in mind the need for meaningful comments i.e. not just describing what we can read in the code but perhaps some of the rational of why a certain approach was taken and also some of the business reasoning logic.

54

Nice article