Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL formatting standards [closed]

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.

like image 657
Timbo Avatar asked Feb 06 '09 10:02

Timbo


People also ask

What is proper SQL formatting?

Let's start with a basic: use uppercase for the SQL keywords, and lowercase for your tables and columns. It's also a good practice to use uppercase for the SQL functions (FIRST_VALUE(), DATE_TRUNC(), etc) even though it's more debatable. Avoid select id, name from company.customers.

Does SQL care about indentation?

Indenting makes SQL easier to follow, as it makes it visually structured. It's recommended not to indent the first line in a multiple line statement, so it would be clear where the statement starts. Make sure that the SQL left margin is indented per the section nesting.

How do I indent my SQL code?

To indent codeSelect the text you want to indent. Press TAB, or click the Indent button on the Standard toolbar.

Do new lines matter in SQL?

Yes, it is perfectly fine to add newlines ( \n or \r\n ) to your query. Most parsers consider newlines as whitespace (just like a space or tab), and so does the one used by Oracle (and all other database I have used).


1 Answers

Late answer, but hopefully useful.

My experience working as part of the larger development team is that you can go ahead and define any standards you like, but the problem is actually enforcing these or making it very easy for developers to implement.

As developers we sometimes create something that works and then say “I’ll format it later”, but that later never comes.

Initially, we used SQL Prompt (it was great) for this, but then switched to ApexSQL Refactor, because it’s a free tool.

like image 156
John Emeres Avatar answered Oct 13 '22 19:10

John Emeres