SQL coding style guide

Question

I AM SO TIRED OF READING THE WHOLE SQL STATEMENT / STORED PROCEDURE IN FULL CAPS. WHAT THE HELL WERE THE INITIAL DEVELOPERS THINKING?

What's the best style (in terms of cap, indentation, lines breaks, etc) to write SQL in?

Answer 1

I like SQL commands in all caps and column names and such in lowercase. I don't know if that's the "best" style, but it helps when I need to add or remove columns from a query or add in new clauses.

Also, I prefer line breaks between most commands, so having the caps helps there too; if I see caps way out in a line, I probably forgot to add the break.

Answer 2

I don't get the CAPS thing either. I write a lot of sql and it's all lower case. Syntax highlighting does a good enough job of making different things stand out. No need for caps.

Answer 3

I Write All My SQL Code In PascalCase. EG:

Declare @DatabaseName sysname
Declare @SQLCommand varchar(1024)
Declare curDBName Cursor For

Select
[Name]
From
Master..Sysdatabases
Where
[Name] Not In ('tempdb')

Answer 4

CAPS suck.

I use lowercase only for my SQL. It feels much neeter then.

Answer 5

The style is really up to you. Caps don't bother me, but everyone is different.

Answer 6

I am not too fussed about caps, although I admit I do make my keyword commands (select, from, where insert) capitals. I can be very anal about tabbing and line breaks though. My preferred style is to keep things like select statements in blocks that are lined up....

SELECT   [Name]
FROM     [tablename]
WHERE    [column1] = [value1]
AND      [column2] < [value2]

Please pretend that the space between items (such as "select" and "[name]") have been done with tabs rather than spaces. :)

Answer 7

The all-CAPS convention for reserved words is pretty useful in my opinion. When you are composing complex (re: very) queries, the CAPS differentiating the operators from the domain text (table, column names etc.) can be a deal-breaker for maintenance.

Also, if there's a possibility that others might need to read your code and understand it, be considerate and use the most common convention for their sake if nothing else.

Answer 8

I believe certain ANSI and ISO standards require the use of upper case to conform to the standard. Technically, the SQL keywords/commands really are in upper-case, although they are allowed to be used in any case if you don't need to conform to a standard. So, it is not really always a matter of style and personal choice.

Also, as far as database object names go, if the database is using a case-sensitive collation then those are required to match the case of the database object (meaning if they are in upper case then you're queries must also include those names in upper case.

Answer 9

There is a reason why uppercase is relatively rare in printed texts: typographers use more legible glyphs (lowercase) for the main text body. Caps are reserved for special occasions. And ALLCAPS is plain annoying to read and to type.

Answer 10

I write SQL in all lowercase because it's easier to type, and case the field and tablenames according to how they are cased in the definition, just in case someone flips on the ole "case matters" flag in your favorite SQL implementation.

Answer 11

Style is the original programming holy war. However here is my style

SELECT f.foo
  , b.bar
  , CASE
      WHEN f.foo = 'hello'
      THEN 'goodbye'
      ELSE b.bar
    END as something_calculated
  , some_long_expression
FROM fooz f
  JOIN baz b
    ON f.baz_id = b.id
WHERE b.bar IS NOT NULL
  AND f.foo <> 'something random'

I have reasons for virtually every part of this style. For example the leading commas in the SELECT make it easier for me to later on add a column. The indentation of the CASE statement allows me to stay within 80 columns and make my code skimmable. I like the way that a 2 space indent with the AND makes the WHERE expressions line up. And so on and so forth. (I care about this because my job essentially boils down to, "Write a ton of very complex SQL statements for reports, and maintain those reports.")

Ironically one of the exceptions to the rule that I have reasons for things I do is the capital key words. I only do that out of established habit.

Answer 12

I break everything down per line, then it's easy to add/remove/comment-out lines w/o needing to edit other lines:

select t1.col1
     , t2.col2
     , t2.col3
from table1 t1
   , table2 t2
where t1.col1 = t2.col2
  and t2.col2 is not null
;

fewer typos, quicker iterations.

Answer 13

Some good tips, I agree with the commentors who uppercase all SQL keywords lowercase all table- and column-names.

I generally indent much like @bentilly above, including the leading commas, but I usually have the JOIN and ON on the same line. Also, this is always only in the SQL IDE; when placing SQL in code (in pre-Linq days) I typically make one long statement. The broken-up style breaks up my code too much and, (IMO) makes it harder to read.

EDIT: I have to admit, though, that when I'm typing SQL in the MySQL command-line client for quick smoke-testing, I usually use all lower-case. Just for speed, I guess; never really thought about it. I always maintain the casing as mentioned above for inclusion in my code, though.

Answer 14

I also prefer having commands, functions and other reserved words in capitals, with identifiers in lowercase.

It's helpful to be able to spot the difference, especially if you're keying or reading chunks of SQL in a primitive client which doesn't offer any syntax highlighting.

Since I usually adhere to this convention where possible, for my part, oddly malformed SQL sticks out like a sore thumb.

Answer 15

My style:

Indents, commas at the end of fields (in front looks like crap - and just moves the "missing comma" to the first field rather than the last one), vertical lineup, single line per element, extra whitespace, comments - all for the primary goal of readability. Code is for people to read, not compilers.

select
   it.field1                        as Field1_Name,
   it.field2                        as Field2_Name,
   it.field3                        as Field3_Name,
   st.field4                        as Field4_Name,
   st.field5                        as Field5_Name,
   tt.field6                        as Field6_Name,
   isnull(it.field4,0)/it.field5    as Calc_Name,

   -- Whitespace and comment if necessary for clarity
   case tt.field8
      when 1 then 27
      when 3 then 42
      else 100
   end                              as Short_Case_Name,

   -- Whitespace and comment if necessary for clarity
   case tt.field9
      when 'Multi-polymorphiotic' 
         then 27
      when 'Quasi-polymorphiotic'
         then 42
      else
         'Mega-polymorphiotic'
   end                              as Long_Case_Name
from
   IntialTable it

   -- Comment if necessary
   JOIN SecondaryTable st
      on ( st.idField = it.idField )

   -- Comment if necessary
   JOIN TertiaryTable tt
      on ( tt.idField = st.idField 
           and tt.otherField = st.otherField )
where
   it.field1 = 'foo'

   -- Group conditions from the same table together for clarity, comment if needed
   and st.field1 = 1
   and st.field2 = 2

   -- Whitespace between groups for readability
   and tt.field1 = 'blah'

My coworkers think I'm a nut-ball, but they love working on my code. :-)

Ron

Answer 16

Readability and clarity of code is the most important for me. If SELECT is better for some than Select, then it's their choice. But personally everything in Caps looks wrong to me.

My style for writing SQL :

• Every Keyword begins with Caps but thereafter has small letters

 Select * From 

• All the tables name begin with tbl and stored procs begin with sp.New words first letter being in caps. (this is more to do with namings and notations conventions)

 tblCustomerAccount

 spCustomerNameSelect

• Proper indentation of the code.

Answer 17

With style, consistency is more important than the specifics of the style you choose. If you're looking for style opinions, I agree with bentilly.blogspot.com about most of the style he's described, with the following differences:

1 - Go ahead and indent the very first SELECTed item just like the others

2 - Why put spaces after your commas?

3 - I much prefer JOIN conditions in the WHERE clause.

4 - I use a slightly different indentation on my CASE statements

So to reformat his example:

SELECT 
  f.foo
 ,b.bar
 ,CASE WHEN f.foo = 'hello'
       THEN 'goodbye'
       ELSE b.bar
  END as something_calculated
 ,some_long_expression
FROM
  fooz f
 ,baz b
WHERE f.baz_id = b.id
  and b.bar IS NOT NULL
  AND f.foo <> 'something random'

Answer 18

It doesn't matter which style you select, but select one. Better yet, use a SQL formatter. There are free web-based ones that you can use. I would advise against putting comments directly into your SQL statement because of 2 things 1) not every development tool understands things like

select 6 --mycomment

      -5 from dual;

2) someday you may want to run all the code in your shop through a formatter. Imbedded comments will really mess things up.

Last bit of advice; leave good enough alone. Once you have the code in a 'reasonably understandable' format, don't obsess over it. Focus on the optimal use of your time which is probably to move on to the next project. Of course, 'good enough' is always subjective.

Answer 19

Why use uppercase keywords in SQL?

The tools do it (at least MS SSMS does).

Answer 20

I guess I have invented my own style. I like SELECT, FROM, JOIN, WHERE, ORDER BY all at position 1 on new lines, 2 spaces of indent, caps for most T-SQL reserved words, brackets around all field names, and a general attempt to align everything else that I can. What do you think of it?

SELECT
  t1.[Field1] [FirstAlias],  
  t2.[Field2] [SecondAlias],  
  t3.[Field3] [ThirdAlias],
  t2.[F1]     [FourthAlias],
  t2.[F2]     [FifthAlias],
  t2.[F3]     [SixthAlias]
FROM
  dbo.Table1 t1
INNER JOIN
  dbo.Table2 t2 ON t1.[Field1] = t2.[F1]
WHERE  
  t1.[Field1] =    'foo'    AND  
  t2.[F2]     =    'baroo'  AND  
  t1.[Field3] LIKE 'bubba%'
ORDER BY
  t2.[F3]     DESC,
  t1.[Field1] DESC

Answer 21

As you can see from the answers here, there is no one style that is 'best'.

Probably most important is consistency within your team in whatever you decide. And in reality, you should be spending your time on WHAT you are writing as opposed to how it looks.

To those ends, you should use a formatting tool like SQLInform or SQL Refactor after any changes to your scripts.

SQLInform is free and terrific. I use SQL Refactor, though, because it integrates into Mgt. Studio and is two keystrokes away from beauty. Truth be told, most everything from Red-Gate software is gold.

Both allow you to decided on what styles you wish to apply.

Answer 22

I'd format your example like this:

SELECT
    f.foo  
    , b.bar  
    , CASE
     WHEN f.foo = 'hello' THEN 'goodbye'      
     ELSE b.bar    
      END as something_calculated  
    , some_long_expression
FROM 
    fooz f  
    JOIN baz b ON f.baz_id = b.id
WHERE 
    b.bar IS NOT NULL  
    AND f.foo <> 'something random'

Although I can take or leave the commas at the start of each of the SELECT items, rather than at the end. The whole argument about it being easy to comment out this way is true, but how useful is it really. After all, we don't program by trial and error, right?

Answer 23

I have been coding t-sql for 10 years now and have, over time, evolved the following format which for me is the easiest to come back to and debug.

Select
        FieldA      = D.Field1
        ,FieldB     = D.Field3 + Coalesce(D.Field4, 100)
        ,FieldC     = Case D.Field5
                            When 1  Then 'Active'
                            When 2  Then 'Closed'
                            Else 'Unknown'
                        End
        ,FieldD     = L1.Name
        ,FieldE     = L2.Description
    From MyDataTable              D
        Left Join MyLookupTable   L1  On D.LookupId = L1.Id
        Left Join MyOtherLookup   L2  On D.OtherLookupId = L2.Id
    Where D.Field6 = 1
        Or D.Field7 Between 1000 and 2000
    Order By FieldA

This layout allows me to easily comment out bits without having to do much correction. For instance, when debugging you often want to comment out fields to see what's producing an error and it's rarely the first field, so the following would give an error;

Select FieldA,
       FieldB,
--     FieldC
    From ...

But the following wouldn't;

Select FieldA
        ,FieldB
--      ,FieldC
    From ...

Having field aliases in the form;

Alias = datafield

rather than

datafield As Alias

makes it massively easier to home in on a specific field you might be looking up the definition for in a complex query, especially if the definition is so long the alias is actually off screen. For example find the definition of FieldC in my first example above compared with finding it in the more traditional layout below;

Select D.Field1 As FieldA, 
        D.Field3 + Coalesce(D.Field4, 100) As FieldB,
        Case D.Field5 When 1  Then 'Active' When 2  Then 'Closed' Else 'Unknown' End As FieldC,
        L1.Name As FieldD,
        L2.Description as FieldE
    From MyDataTable D
    Left Join MyLookupTable L1 On D.LookupId = L1.Id
    Left Join MyOtherLookup L2 On D.OtherLookupId = L2.Id
    Where D.Field6 = 1 Or D.Field7 Between 1000 and 2000
    Order By FieldA

If you didn't see the issue, imagine trying to find a specific field in a query with 40 or more fields written like that. In my format, you just scan down the left side.

I also break logical conditions so that the operand is on a new line, again so I can easily comment out sections of the logic for debugging.

    Where Field1 = Field2
--      Or Field3 = Field4

I use tabs heavily to layout the code neatly, setting the tab to 4 characters rather than 8 as is often the default.

I use verbose names as a form of self documentation plus CamelCase to make it easier to read;

Select AnnualSalesValue = Sum(D.SaleAmount)

is far easier on the eye and easier to understand the meaning of than;

Select tot_sv = sum(d.saleamt)

I also line up table aliases so that I can quickly scan for what table a prefix refers to. For instance if a field S.Id is referred to it's no big deal to quickly see it's from the Staff table and I use short table aliases because once I know S is pointing to the Staff table for instance, I don't need the to see the word Staff in front of every field being shown from that table;

Select S.FirstName, S.LastName, S.BirthDate, S.Address1
    From Staff As S

is easier to read than

Select Staff.FirstName, Staff.LastName, Staff.BirthDate, Staff.Address1
    From Staff

If I use more than one table in a query I always give them short alias and always prefix each field referenced with the appropriate alias. When debugging, this saves me having to look at individual table definitions to work out where an actual field is coming from.

I also detest the current trend of wrapping every object with square brackets. The purpose of the square brackets was to allow for illegal names to be used. Illegal names in SQL terms are those with spaces, special characters or reserved words in them. It was meant for the exceptions. Their proliferation is due to the use of GUI's that automatically generate the SQL for you and they do it so that the code is always legal. But it makes the code that much harder to read in my view especially with multipart names, which is preferable?;

Select [Staff].[FirstName], [Staff].[LastName], [Staff].[BirthDate], [Staff].[Address1], [Staff].[City]
    From Staff

Or

Select S.FirstName, S.LastName, S.BirthDate, S.Address1, S.City From Staff As S

My format does take a little more effort initially but reaps huge rewards if you have to debug, analyse or document the code later. Add copious amounts of comments to explain logic that is not immediately obvious and your debugging tasks are made much less of a chore.

Happy coding!

Answer 24

This question just confirmed my feeling that SQL is a very functional but ugly language. It's like a verse horseshoed into prose.

Answer 25

Two things that were neglected by other answers. 1) When do you with LEFT JOIN with an Inside Inner Join what do you do.

E.G.

SELECT
    ....
FROM
    customer c
    LEFT JOIN contract cx
        INNER JOIN employee e 
            ON cx.employee_id = e.employee_id
    ON c.customer_id = cs.customer_id

2) What do you do with selects in the join

 SELECT
        ....

FROM
    customer c
    LEFT JOIN contract cx
        INNER JOIN (SELECT Max(contract_id) contract_id
                    FROM contract cx
                    GROUP BY customer_id) last_contract
        on cx.contract_id = last_contract.contract_id
    ON c.customer_id = cs.customer_id