Transact-SQL Formatting Standards (Coding Styles) [closed]

As it currently stands, this question is not a good fit for our Q&A format. We expect answers to be supported by facts, references, or expertise, but this question will likely solicit debate, arguments, polling, or extended discussion. If you feel that this question can be 开发者_运维问答improved and possibly reopened, visit the help center for guidance. Closed 11 years ago.

1. How should SQL code be formatted?

2. What sort of indentation should you use?

3. Should keywords be in upper case?

4. How should lists be lined up?

---

Though I realize this question may be closed due to being "subjective"... I think it's important as I hate going behind some other developers who put everything in one line all lowercase.

My style that I like all my devs to adopt is:

1. Each "clause" is on it's on line.
2. Key words are in all caps
3. If a secondary line is needed, it is tabbed further in (such as the "ON" clause of an inner join).

Example

SELECT
    *
FROM
    dbo.Customers c INNER JOIN dbo.Orders o
        ON o.CustomerID = c.ID
WHERE
    o.OrderDate > @BlahDate

I mean, really people... this is nasty

select * from dbo.customers c inner join dbo.orders o on o.customerid = c.id where o.orderdate > @blahdate

---

Please consider using SSMS tools pack to format your code (what is uppercase and not). Indentation is a matter of preference. A rule of thumb would be that 'TALLER' the query, the more readable it is.

Here's an example of how I would format (a little different than the examples shown here)

SELECT  id, 
        lastfirst, 
        type1, 
        type2,
        email,
        CASE
            WHEN termdate < GETDATE() THEN 'Y'
            ELSE 'N'
        END termed
FROM something man WITH (NOLOCK)
    INNER JOIN city cty WITH (NOLOCK)
        ON cty.code = man.cty_code
            AND cty.state != 'OH'
            AND cty.city != 'CANTON'
WHERE email IS NOT NULL
ORDER BY    type2,
            type1,
            lastfirst

---

Check this

http://www.simple-talk.com/sql/t-sql-programming/transact-sql-formatting-standards-%28coding-styles%29/

---

There is no hard and fast rule to use anything regarding formatting.

The standards are normally organization specific and they are implemented as the organization suggests.

---

I favour the following sort of layout (a nonsense query, that just includes a sample of some of the SELECT statement bits that benefit from some sensible formatting.)

There is always a specific goal in mind too- to make it as easy as possible to debug, and identify what is included. So:

SELECT
    a.id,
    b.id,
    CASE
        WHEN a.id + b.id = 1 THEN 
            '1'
        ELSE 
            '2'
    END 'SomeExpresion'
FROM
    sysobjects a
LEFT JOIN
    (
        SELECT TOP 10
            *
        FROM
            sysobjects
    ) b
ON
    a.id = b.id AND
    a.version = b.version
WHERE
    a.id = 1
GROUP BY
    a.id,
    b.id
HAVING
    COUNT(a.id) = 1     

The idea being I can easily look through the list of join conditions, see nested tables (and their aliases), look through CASE options, see group by orders in a natural list. I can also generally fairly easily prefix a line with a -- to comment out a specific clause, with usually minimal other amendments to SQL. My fairly unusual JOIN layout means I can just scan down the left hand side of the query to see whether it's an equi/left/right/cross join. When I find the join I'm looking for, I can then scan down the columns involved (usually while thinking about what columns I should be seeing there)

One caveat - if the statement is particularly short, e.g.

SELECT Id FROM sysobjects where xtype='u'

I'd typically not bother separating it on to different lines.