Is Separator First Formating (SFF) with SQL Code Formating easier to read/maintain?

Question

Do you place separators (commas, and, or operators) at the front of the line?

Select Field1
    , Field2
  --, Field3
From [some_table] as ST
Inner Join [other_table] as OT
   ON ST.PKID = OT.FKID
Where [this] = [that]
   and [one_other] > 53;

I think the best feature is to help expose important operators (AND/OR). As a secondary advantage it seems easier to comment out.

Not sure where I picked it up initially, but Andrew Novick mentions it in "Transact-SQL User-Defined Function" (Heard him speak; got the book for free and highly recommend.)

Answer 1

I've gotten sloppy with this, but that is my preferred method of writing things out - especially when I am debugging a new script.

Answer 2

I personally don't find this easier to read, but that is only because I am not used to it. The important thing to remember is that consistency is the key in these matters. Make sure that whatever style you choose is the one and only style used for the entire project.

Answer 3

At my workplace (small consultancy company with about 10 developers, specialising in Oracle) that is what our convention is, as so:

SELECT
  p.id pd_id
, p.var_no
, p.status
, o.name operator
, o.r_id
, o.r_type
, o.start_datetime
, o.end_datetime
--, p.id rd_id
--, p.s_control
, p.xml_data last_d_res_xml
FROM schema_a.table_x p
JOIN schema_b.table_y o ON p.id = o.pd_id
WHERE p.some_id = 11
ORDER BY pd_id DESC, end_datetime DESC NULLS FIRST

We find it is quite clear and allows easy commenting of columns when debugging. It took a while to get used to but I prefer this style now - and it is consistently used by our whole team.

Answer 4

It's easier to comment out, but I'd rather go for readability - Using a column layout like the one below is a bit awkward while the code is changing a lot, but it's very comfortable to get an overview:

select foo.bar,
       baz.ban,
       foobar.bazban
  from foomatic  foo
  join bartastic bar  on bar.id  = foo.id
  join anemone   anem on anem.id = bar.id
 where foo.bar <> 1 and
       baz.ban =  'foo' and
       (
         anem.bear in ('a', 'b') or
         anem.zoo  is null
       )
;

Answer 5

"Easier to comment out" - unless you want to comment out the first item.

It doesn't matter if your separators are at the start or the end of the line - there will always be an edge case where you can't apply the "easily comment out" criterion.

And if you're looking at the operator level (AND/OR), then an easily commented out line may:

• Change the meaning of the query
• Result in a syntax error

In general, for the where clauses, if it's not all ANDs, then I tend to nest:

WHERE
    ColumnA = 1 AND
    ColumnB = 2 AND
    (
        ColumnC = 3 OR
        ColumnD = 4
    )

This helps you to spot the edge cases (mentioned above) where you'll need to comment an operator or add a "1=1" condition in front of the comment block.

Answer 6

This is the standard at my most recent engagement. Took a little while to get used to, now anything else looks strange to me :)

SELECT e.emp_id
      ,e.emp_name
      ,d.dept_name
FROM   emp   e
      ,dept  d
WHERE  e.dept_id        = d.dept_id
AND    d.is_active      = 'Y'
AND    e.current_status = 'ACTIVE'
AND    (e.class IN ('x','y','z')
        OR e.class_na = 'Y')
ORDER BY e.emp_name
        ,d.dept_name;

We found having everything lined up in columns made queries more easily read, especially when you've got a number of SQL statements in PL/SQL. The SQL stood out neatly from the surrounding procedural code.

Answer 7

I prefer SELECT clause separation.

This highlights the important parts of a query. Separator front or end only matters now in the SELECT list

I find all above examples difficult to read.

Select
    Field1
    , Field2
    --, Field3
From
    [some_table] as ST
    Inner Join
    [other_table] as OT ON ST.PKID = OT.FKID
Where
    [this] = [that]
    and
    [one_other] > 53;


Select
    Field1,
    Field2 --,
    --Field3
From
    [some_table] as ST
    Inner Join
    [other_table] as OT ON ST.PKID = OT.FKID
Where
    [this] = [that]
    AND
    [one_other] > 53;