What indenting style do you use in SQL Server stored procedures?

Question

None of my SQL Server stored procedure editing IDEs seem to have any tools to enforce indentation styles, so I find that a lot of the stored procedures I see are all over the place. I find indenting really improves readability though. I would like to codify some stored procedure indenting standards in our company's coding style guide, and I'm wondering if anyone has any best practices they would like to share.

For instance, in a normal SELECT statement, I try to keep the SELECT, FROM, WHERE, ORDER BY, and GROUP BY clauses all on the same level, and indent anything below that. I also try to indent each JOIN one level from the table it's logically joining into.

Does anyone else have similar advice or best practices?

Answer 1

I prefer the following style:

--
-- SELECT statements
--

select field1,
       field2,
       field3,
       fieldn
from tblOne as t1
inner join tblTwo as t2
    on t1.field = t2.field
    and t1.field2 = t2.field2
left outer join tblThree as t3
    on t2.field = t3.field
    and t2.field2 = t3.field2
where t1.field = 'something'
    and t2.field = 'somethin else'
order by fieldn

--
-- IF statements
--

if @someVar = 'something'
begin
    -- statements here
    set @someVar2 = 'something else'
end

--
-- WHILE statements
--

while @count < @max
begin
    set @count = @count + 1
end

Answer 2

My style is almost identical to Justin's. I indent the "and" so the "d" in "and" lines up with the "e" in "where".

Sometimes I capitalize keywords. When I have a sub-select, I indent the whole sub-select and format it the same as a regular select.

One place where I may deviate is if I have dozens of fields being selected. In that case, I put several fields to a line and add white space to make even columns of text.

Answer 3

SELECT      T1.Field1,
            T1.Field2,
            T2.Field1 As Field 3
FROM        Table1 AS T1
LEFT JOIN   Table2 AS T2
ON          T1.Field1 = T2.Field7
WHERE       T1.Field9 = 5
AND         T2.Field1 < 900
ORDER BY    T2.Field1 DESC

INSERT INTO Table1 (
            Field1,
            Filed2,
            Field3 )
VALUES (    'Field1',
            'Field2',
            'Field3' )

UPDATE      Table1
SET         Field1      = SomeValue,
            Field2      = AnotherValue,
            FIeld134567 = A ThirdValue
WHERE       Field9      = A Final Value

I find that I dont necessarily has a set indentation length and instead I try to indent based on the length of the field names and values. I like my left margins to line up along any given vertical plane and I like my Evaluators (such as equal signs) to line up. I always have any command term on a different vertical plane than its accompanying values and fields. I also tend to try to make the space between my SELECT command and the Field list equal in length to the space used by a SELECT DISTINCT Field or INSERT INTO Table.

But in the end, all that is just my preferences. I like neat looking code.

Answer 4

My select formattings:

--
-- SELECT statements
--

select 
    t1.field1, t1.field2, 
    t2.field3, 
    t3.fieldn
from 
    tblOne t1
    inner join tblTwo t2 on t1.field = t2.field and t1.field2 = t2.field2
    left join tblThree t3 on t2.field = t3.field and t2.field2 = t3.field2
    left join (
        select id, sum(quantity) as quantity
        from tbl4
        group by id
    ) t4 on t4.id=t3.id
where 
    t1.field = 'something'
    and t2.field = 'somethin else'
order by 
    fieldn

Optionally (when lines get too long) I split lines at logical boundaries and indent splitted parts:

    inner join tblTwo as t2 
        on t1.field = t2.field and t1.field2 = t2.field2

Sometimes I'm using different syntax for very simple (sub)selects. Main goal is to make code readable and relatively easily modifyable.

--edit--

IMHO (at least in small team) it is not needed to enforce very strict rules, this helps support and maintainig :) In our team, where about 3-4 people write most sql, it is very easy to establish code author, just looking at sql statement - all people are using somewhat different style (capitalzing, aliases, indenting etc).

Answer 5

SELECT      T1.Field1,
            T1.Field2,
            T2.Field1 AS Field 3
FROM        Table1 AS T1
            LEFT JOIN Table2 AS T2 ON T1.Field1 = T2.Field7
WHERE       T1.Field9 = 5
            AND T2.Field1 < 900
ORDER BY    T2.Field1 DESC

INSERT INTO Table1 (Field1,   Field2,   Field3)
            VALUES ('Field1', 'Field2', 'Field3' ) /* for values trivial in length */

UPDATE      Table1
SET         Field1      = SomeValue,
            Field2      = AnotherValue,
            FIeld134567 = A ThirdValue
WHERE       Field9      = A Final Value

I think my preferred format comes from that one COBOL class I took back in college. Something about code in pretty aligned columns that makes me happy inside.

Answer 6

I tend to right-justify the keywords:

SELECT T1.Field1, T2.Field2
  FROM Table1 AS T1
 LEFT JOIN Table2 AS T2 ON T1.Field1 = T2.Field7
 WHERE T1.Field9 = 5
   AND T2.Field1 < 900
 ORDER BY T2.Field1 DESC

Note that it's not hard-and-fast. I favor having the SELECT being left-most that I will break the justification (INNER JOIN, ORDER BY). I'll wrap on ON and its ilk if necessary, preferring to start a line with a keyword, if possible.

 LEFT JOIN Table2 AS T2 
        ON T1.Field1 = T2.Field7 AND T2.Field8 IS NOT NULL

Answer 7

I prefair the following style...

Select
    Id = i.Identity,
    User = u.UserName,
From
    tblIdentities i
Inner Join
    tblUsers u On i.UserId = u.UserId
Where
(
    u.IsActive = 'True'
    And
    i.Identity > 100
)

Also I try and not to use the As keyword. I prefair equals instead. Probably upset a few people but I find this code much easier to read...

Select
    Id = tbl.Identity,
    User = tbl.UserName,
    Age = tbl.Age,
    DOB = tbl.DateOfBirth
From
    tbl

Rather than...

Select
    tbl.Id As Identity,
    tbl.UserName As User,
    tbl.Age As Age,
    tbl.DateOfBirth As DOB
From
    tbl

Answer 8

Since this is the only thread I found that relate to my problem, please permit me to ask some question here. I am using Visual Studio 2008 for SQL stored procedure editor. After I switched to Window 7, the SQL style in VS2008 changed and now is all over the place compare with the styles I used to in Window XP. It shouldn't be the Operating System problem, but could anyone please help me of actually how / where to change the indentation styles? I tried to look into VS2008 options but everything in vain. Please help. Thank you.