SQL格式化标准 [英] SQL formatting standards
问题描述
在我的最后一份工作中，我们研究了一个非常重的数据库应用程序，并开发了一些格式化标准，以便我们可以使用通用布局编写SQL。我们还开发了编码标准，但是这些都是平台特定的，所以我不在这里。
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.
我有兴趣知道其他人使用的SQL格式标准。不像大多数其他编码环境，因为我没有找到很多在线的共识他们。
I'm interested to know what other people use for SQL formatting standards. Unlike most other coding environments as I haven't found much of a consensus online for them.
覆盖主要的查询类型：
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
在选择，从和在哪里之间对换行符存在一些不同意见。选择线上的意图是允许诸如top X之类的其他操作符，而不改变布局。接下来，简单地在关键查询元素之后保持一致的换行符，似乎导致了良好的可读性水平。
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.
在from和where之后删除换行符是一个可以理解的修订版本。然而在查询，如下面的更新，我们看到换行后的位置给我们良好的列对齐。类似地，group by或order by之后的换行符使我们的列布局清晰易读。
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
最后插入：
insert into TargetTable (
ColumnName1,
ColumnName2,
ColumnName3
) values (
@value1,
@value2,
@value3
)
大多数情况下，这些不会偏离MS SQL Server Managements Studio /查询分析器写出SQL，但它们有所不同。
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.
我期待看到Stack Overflow社区对这个主题是否有任何共识。我不断惊讶有多少开发人员可以遵循其他语言的标准格式化，并突然在点击SQL时这么随意。
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.
推荐答案
我认为只要你可以轻松地阅读源代码，格式化是次要的。只要这个目标实现，就有很多好的布局样式可以采用。
I am of the opinion that so long as you can read the source code easily, the formatting is secondary. So long as this objective is achieved then there are a number of good layout styles that can be adopted.
对我来说唯一的另一个方面是，无论编码布局/ style你确定要在你的商店采用，确保它被所有的编码器一致使用。
The only other aspect that is important to me is that whatever coding layout/style you choose to adopt in your shop, ensure that it is consistently used by all coders.
只是为了参考，这里是我将提出你提供的例子，只是我的布局偏好。特别注意的是，ON子句与连接在同一行，只有主连接条件在连接中列出（即密钥匹配），其他条件被移动到where cluase。
Just for your reference, here is how I would present the example you provided, just my layout preference. Of particular note, the ON clause is on the same line as the join, only the primary join condition is listed in the join (i.e the key match) and other conditions are moved to the where cluase.
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
where
ST.SourceTableID = X
and JT.ColumnName3 = Y
and JT.Column3 = SJT.Column4
一个提示，从Red Gate获取SQL提示的副本。您可以自定义该工具以使用所需的布局首选项，然后商店中的编码器都可以使用它，以确保每个人都采用相同的编码标准。
One tip, get yourself a copy of SQL Prompt from Red Gate. You can customise the tool to use your desired layout preferences, and then the coders in your shop can all use it to ensure the same coding standards are being adopted by everyone.
这篇关于SQL格式化标准的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
