SQL formatting standards

Question

In my last job, we worked on a very database-heavy application, and I developed some formatting standards so that we would all write SQL with a common layout. We also developed coding standards, but these are more platform-specific so I'll not go into them here.

I'm interested to know what other people use for SQL formatting standards. Unlike most other coding environments, I haven't found much of a consensus online for them.

To cover the main query types:

select
    ST.ColumnName1,
    JT.ColumnName2,
    SJT.ColumnName3
from 
    SourceTable ST
inner join JoinTable JT
    on JT.SourceTableID = ST.SourceTableID
inner join SecondJoinTable SJT
    on ST.SourceTableID = SJT.SourceTableID
    and JT.Column3 = SJT.Column4
where
    ST.SourceTableID = X
    and JT.ColumnName3 = Y

There was some disagreement about line feeds after select, from and where. The intention on the select line is to allow other operators such as "top X" without altering the layout. Following on from that, simply keeping a consistent line feed after the key query elements seemed to result in a good level of readability.

Dropping the linefeed after the from and where would be an understandable revision. However, in queries such as the update below, we see that the line feed after the where gives us good column alignment. Similarly, a linefeed after group by or order by keeps our column layouts clear and easy to read.

update
    TargetTable
set
    ColumnName1 = @value,
    ColumnName2 = @value2
where
    Condition1 = @test

Finally, an insert:

insert into TargetTable (
    ColumnName1,
    ColumnName2,
    ColumnName3
) values (
    @value1,
    @value2,
    @value3
)

For the most part, these don't deviate that far from the way MS SQL Server Managements Studio / query analyser write out SQL, however they do differ.

I look forward to seeing whether there is any consensus in the Stack Overflow community on this topic. I'm constantly amazed how many developers can follow standard formatting for other languages and suddenly go so random when hitting SQL.

Answer 1

Yeah I can see the value of laying out your sql in some rigourously defined way, but surely the naming convention and your intent are far more important. Like 10 times more important.

Based on that my pet hates are tables prefixed by tbl, and stored procedures prefixed by sp - we know they're tables and SPs. Naming of DB objects is far more important than how many spaces there are

Just my $0.02 worths

Answer 2

I would suggest the following style, based on the John's suggestion:

/*
<Query title>
<Describe the overall intent of the query>
<Development notes, or things to consider when using/interpreting the query>
*/
select
    ST.ColumnName1,
    JT.ColumnName2,
    SJT.ColumnName3
from 

    -- <Comment why this table is used, and why it's first in the list of joins>
    SourceTable ST

    -- <Comment why this join is made, and why it's an inner join>
    inner join JoinTable JT
        on ST.SourceTableID = JT.SourceTableID

    -- <Comment why this join is made, and why it's an left join>
    left join SecondJoinTable SJT
        on  ST.SourceTableID = SJT.SourceTableID
        and JT.Column3 = SJT.Column4

where

    -- comment why this filter is applied
    ST.SourceTableID = X

    -- comment why this filter is applied
    and JT.ColumnName3 = (
            select 
                somecolumn
            from 
                sometable
        )
;

Advantages:
- Comments are an essential part of making code readable and detecting mistakes.
- Adding -all- "on"-filters to the join avoids mistakes when changing from inner to left join.
- Placing the semicolon on a newline allows for easy adding/commenting of where clauses.

Answer 3

If I am making changes to already written T-SQL, then I follow the already used convention (if there is one).

If I am writing from scratch or there is no convention, then I tend to follow your convention given in the question, except I prefer to use capital letters for keywords (just a personal preference for readability).

I think with SQL formatting as with other code format conventions, the important point is to have a convention, not what that convention is (within the realms of common sense of course!)

Answer 4

I use Red Gate SQL ReFactor within SSMS, but another tool that does reformating (and is a replacement for SSMS) is Apex's SQL Edit. If you're looking to post code on-line there's The Simple-Talk SQL Prettifier.

Answer 5

I think that having a good formatting rules are really important because you can spot and fix bugs easily. As it's said - "You’re writing code once, this code is read then 10000000 of times", so it always good to spend some time on formatting. The primary goals are:

• Make your code easier to read and understand
• Minimize the effort required to maintain or extend your code
• Reduce the need for users and developers of a system to consult secondary documentation sources such as code comments or software manuals

Some rules I always use:

• Always use . notation
• Always use alias before column, so . notation
• I put and and or to the end of the line
• Don't use unneseccary brackets
• Don't use UPPERCASE
• Usually prefer cte to nested subqueries

As an example, here how I'd format query used as an example in this question:

select
    ST.ColumnName1,
    JT.ColumnName2,
    SJT.ColumnName3
from <schema>.SourceTable as ST
    inner join <schema>.JoinTable as JT on
        ST.SourceTableID = JT.SourceTableID
    inner join <schema>.SecondJoinTable as SJT on
        SJT.SourceTableID = ST.SourceTableID and
        SJT.Column4 = JT.Column3
where
    ST.SourceTableID = X and
    JT.ColumnName3 = Y

And "students" query:

select
    term,
    student_id,
    case
        when (ft_credits > 0 and credits >= ft_credits) or (ft_hours_per_week > 3 and hours_per_week >= ft_hours_per_week) then 'F'
        else 'P'
    end as [status]
from (
    select
        a.term,
        a.student_id,
        pm.credits as ft_credits,
        pm.[hours] as ft_hours_per_week,
        sum(a.credits) as credits,
        sum(a.hours_per_week) as hours_per_week
    from (
        select
            e.term, e.student_id, NVL(o.credits, 0) credits,
            case
                when NVL(o.weeks, 0) > 5 then
                    (NVL(o.lect_hours, 0) + NVL(o.lab_hours, 0) + NVL(o.ext_hours, 0)) / NVL(o.weeks, 0)
                else
                    0
            end as hours_per_week
        from enrollment as e
            inner join offering as o using (term, offering_id)
            inner join program_enrollment as pe on pe.student_id = e.student_id and pe.term = e.term and pe.offering_id = e.offering_id
        where
            e.registration_code Not in ('A7', 'D0', 'WL')
    ) as a
        inner join student_history as sh using (student_id)
        inner join program_major as pm on pm._major_code = sh.major_code_1 and pm.division_code = sh.division_code_1
    where
        sh.eff_term = 
            (
                select max(eff_term)
                from student_history as shi
                where
                    shi.student_id = sh.student_id and
                    shi.eff_term <= term
             )
    group by
        a.term,
        a.student_id,
        pm.credits,
        pm.[hours]
) as a
order by
    term,
    student_id

Answer 6

This is the format that I use. Please comment if it can be make better.

CREATE PROCEDURE [dbo].[USP_GetAllPostBookmarksByUserId]
    @id INT,
    @startIndex INT,
    @endIndex INT
AS
BEGIN

    SET NOCOUNT ON

    SELECT      *
    FROM
            (   SELECT      ROW_NUMBER() OVER ( ORDER BY P.created_date ) AS row_num, P.post_id, P.title, P.points, p.estimated_read_time, P.view_count, COUNT(1) AS "total_attempts" -- todo
                FROM        [dbo].[BOOKMARKED] B
                INNER JOIN  [dbo].[POST] P
                ON          B.entity_id = P.post_id
                INNER JOIN  [dbo].[ATTEMPTED] A
                ON          A.entity_id = P.post_id
                WHERE       B.user_id = 1 AND P.is_active = 1
                GROUP BY    P.post_id, P.title, P.points, p.estimated_read_time, P.view_count
            )   AS PaginatedResult
    WHERE       row_num >= @startIndex
    AND         row_num < @endIndex
    ORDER BY    row_num

END