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

+6  A: 

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.

+5  A: 

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.

Eric Z Beard
+3  A: 

I Write All My SQL Code In PascalCase. EG:

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

[Name] Not In ('tempdb')
It's pretty readable :) i like
Welcome to Visual Basic for Databases. Yikes.
...and your English too, by the looks of it.

CAPS suck.

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

I'm a fan of lower case too. Let IDE code highlighter do the work.
+2  A: 

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

Optimal Solutions
+4  A: 

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

this is nice to read but a lot of maintenance.
+4  A: 

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.

Eran Galperin
Fortunately, i keep at least one syntax-highlighting editor ready at hand...
+1  A: 

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.

Ryan Farley
+1  A: 

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.


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.

Robert C. Barth
+15  A: 

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

  , CASE
      WHEN = 'hello'
      THEN 'goodbye'
    END as something_calculated
  , some_long_expression
FROM fooz f
  JOIN baz b
    ON f.baz_id =
  AND <> '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.

There's another great reason to start with a comma in a select list, or a logical operator in a where clause: you can easily add or comment out any single column/criteria without much (if any) change to the rest of the query.
I personally find this very hard to read...but as you said, it's a holy war :)
The ability to add or comment out columns and criteria is important for me because I'm usually *really* writing a template where, depending on the report parameters, specific bits might be included.
That's not really true.. leading commas allow you to easily comment out the last column but not the first column, vice versa for trailing commas.
I use a very similiar style (I like more than two space margins).
Nathan Koop
Commas on the front of the line, brilliant. I'm going to have to adopt that from now on.
If you are going to go down the page with your select list, I would put the first column name on a list of it's own for consistancy.A good reason to put SELECT WHERE FROM in uppercase is because it makes them easier to ignore so you can concentrate on the interesting part of the query.
I also use a very similar style myself. Having the SQL statements in all caps helps you readily distinguish them from table/field names. I'd add to this example a subquery (which I usually indent a tab plus the normal indentation).
Joe Pineda
Of course, it's subjective, but my eyes bleed when I am seeing leading commas; leading "AND"s in WHERE; not having SELECT, FROM, JOIN, and WHERE alone on their lines... Very inconsistent. Puts editability above readability - one of the worst coding sins, :-((
+3  A: 

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.

+1  A: 

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.

+1  A: 

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.

+7  A: 

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.

   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
   end                              as Long_Case_Name
   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 )
   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 Savage
Not ideal for my taste, but very very close... :-)
Do you indent with spaces or tab? =p

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)
  • Proper indentation of the code.

With style, consistency is more important than the specifics of the style you choose. If you're looking for style opinions, I agree with 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:

 ,CASE WHEN = 'hello'
       THEN 'goodbye'
  END as something_calculated
  fooz f
 ,baz b
WHERE f.baz_id =
  AND <> 'something random'
why eek? .
Advice #1 - good; #2 - very bad (only aggravates readability); #3 - very very bad; #4 - neutral (good in simple cases). Overall: eeeek! :-)
@Yanik,why is #3 so very, very bad? /Adam
Adam Asham
Because it is not clear, immediately, what the intention of the query is. Multiple sources in a from-clause typically imply a cartesian-cross-join, whereas the intention is, in this example, an inner-join.

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.


Why use uppercase keywords in SQL?

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

David B
How can you make SSMS do this?
Right click on a table in the object browser. Select "Script as Insert To New Tab". Observe the code generation.
David B
+3  A: 

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?

  t1.[Field1] [FirstAlias],  
  t2.[Field2] [SecondAlias],  
  t3.[Field3] [ThirdAlias],
  t2.[F1]     [FourthAlias],
  t2.[F2]     [FifthAlias],
  t2.[F3]     [SixthAlias]
  dbo.Table1 t1
  dbo.Table2 t2 ON t1.[Field1] = t2.[F1]
  t1.[Field1] =    'foo'    AND  
  t2.[F2]     =    'baroo'  AND  
  t1.[Field3] LIKE 'bubba%'
  t2.[F3]     DESC,
  t1.[Field1] DESC
Andy Frieders

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.

Gern Blandston

I'd format your example like this:

    , CASE
     WHEN = 'hello' THEN 'goodbye'      
      END as something_calculated  
    , some_long_expression
    fooz f  
    JOIN baz b ON f.baz_id =
    AND <> '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?

Mitch Wheat
It's useful at the time of writing the sql when you want to peek into other columns for context.
+3  A: 

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.

        FieldA      = D.Field1
        ,FieldB     = D.Field3 + Coalesce(D.Field4, 100)
        ,FieldC     = Case D.Field5
                            When 1  Then 'Active'
                            When 2  Then 'Closed'
                            Else 'Unknown'
        ,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,
--     FieldC
    From ...

But the following wouldn't;

Select FieldA
--      ,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


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!

Chris McGuigan
Alas this causes an errorSelect --FieldA ,FieldB-- ,FieldC From ...
Conrad Frix

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


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


    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


    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
Conrad Frix