你遵循什么 SQL 编码标准？

What SQL coding standard do you follow?

提问by thebat

Is there any widely used SQL coding standard out there? SQL is little bit different from C/C++ type of programming languages. Really don't know how to best format it for readability.

是否有任何广泛使用的 SQL 编码标准？SQL 与 C/C++ 类型的编程语言略有不同。真的不知道如何最好地格式化它以提高可读性。

回答by DJ.

Wouldn't call it coding standard - more like coding style

不会称之为编码标准 - 更像是编码风格

SELECT
    T1.col1,
    T1.col2,
    T2.col3
FROM
    table1 T1
    INNER JOIN ON Table2 T2 ON T1.ID = T2.ID
WHERE
    T1.col1 = 'xxx'
    AND T2.Col3 = 'yyy'

• capitalize reserved words
• main keywords on new line
• can't get used to commas before columns
• always use short meaningful table aliases
• prefix views with v
• prefix stored procs with sp (however don't use "sp_" which is reserved for built in procs)
• don't prefix tables
• table names singular

• 保留字大写
• 新行的主要关键字
• 不能习惯列前的逗号
• 始终使用有意义的简短表别名
• 使用 v 前缀视图
• 使用 sp 前缀存储过程（但是不要使用为内置过程保留的“sp_”）
• 不要为表添加前缀
• 表名单数

回答by Ryan Guill

I like the comma preceding way:

我喜欢逗号前面的方式：

SELECT
      column1
    , column2
    , column3
    , COALESCE(column4,'foo') column4
FROM
    tablename
WHERE
    column1 = 'bar'
ORDER BY 
      column1
    , column2

it makes it the easiest to read and debug in my opinion.

在我看来，它使它最容易阅读和调试。

回答by SnapJag

I know this is long, but bear with me, it's important. This question opened a cool can of worms. And if you don't like database blocks, read on.

我知道这很长，但请耐心等待，这很重要。这个问题打开了一个很酷的蠕虫罐头。如果您不喜欢数据库块，请继续阅读。

And, before anyone thinks about knocking down my response, pleasesee the following article and connected articles to it about locking, and recompiles; two of the most damaging resources hits on a SQL database.

并且，在有人考虑取消我的回复之前，请参阅以下关于锁定和重新编译的文章和相关文章；两个最具破坏性的资源命中 SQL 数据库。

http://support.microsoft.com/kb/263889

http://support.microsoft.com/kb/263889

I can type pretty quickly, and I don't like to type any more than the next person. But the points below I follow extremely closely, even if it is more typing. So much that I've built my own SP apps to do it for me.

我可以很快打字，而且我不喜欢打字比下一个人多。但是下面的几点我非常关注，即使它打字更多。以至于我已经构建了自己的 SP 应用程序来为我做这件事。

The points I bring up are really important! You might even say to yourself, "are you kidding, that's not an issue", well, then you didn't read the articles above. AND, it's totally moronic that M$ would put these points in as NOTEs. These issues to me should be BOLD and SCREAMING.

我提出的观点非常重要！你甚至可以对自己说，“你在开玩笑吗，这不是问题”，那么你没有阅读上面的文章。和，这是完全鲁钝的是M $会把这些点作为笔记。这些问题对我来说应该是大胆而尖叫的。

I also do a lot of coding to build my basic scripts using C# applications to speed up development and these practices are very sound (10 years worth) to make scripting SPs easier and especially faster.

我还编写了大量代码来使用 C# 应用程序构建我的基本脚本以加快开发速度，这些实践非常合理（值得 10 年），使脚本 SP 更容易，尤其是更快。

There are more than this, but this is what I do for the first 60% of everything.

还有更多，但这就是我对前 60% 所做的一切。

---

---

Best practices

最佳实践

• Use the brackets around objects, so the query engine excplicitly knows a field when it sees it
• Use THE SAME CASE as table object names and field names
• When calling SPs from application, use the fully qualified [dbo].[procName] with correct owner AND case. Not Kidding! Read the articles above!
• Reference the owner of the object so security is explicitly known and doesn't have to be figured out
• DON'T us "sp_" as this refers to system stored procs, and overhead
• Use SET NOCOUNT ON and SET NOCOUNT OFF to eliminate the extra overhead to keep track of how many records are updated in the stored proc unless you need them. Normally, you don't and you can gain a huge increase in performance.

• 在对象周围使用方括号，以便查询引擎在看到某个字段时明确知道该字段
• 使用相同的案例作为表对象名称和字段名称
• 从应用程序调用 SP 时，使用具有正确所有者和大小写的完全限定 [dbo].[procName]。不开玩笑！阅读以上文章！
• 引用对象的所有者，以便明确知道安全性并且不必弄清楚
• 不要使用“sp_”，因为这是指系统存储过程和开销
• 使用 SET NOCOUNT ON 和 SET NOCOUNT OFF 来消除额外开销以跟踪存储过程中更新的记录数，除非您需要它们。通常情况下，您不会这样做，并且您可以获得性能的巨大提升。

Preferences

喜好

• Prefix stored procs with proc
• Suffix every stored proc with SEL, UPD, DEL, INS (or SELECT, UPDATE, DELETE, INSERT)
• Capitalize reserved words
• Main keywords on new line (scripting)
• Use commas before columns (scripting)
• Prefix views with vw
• Don't prefix tables
• Table names singular
• Add a suffix to the standard names like "_ByPK", "_OrderByLastName", or "_Top15Orders" for variations on the stock SP

• 使用 proc 前缀存储过程
• 使用 SEL、UPD、DEL、INS（或 SELECT、UPDATE、DELETE、INSERT）为每个存储的过程添加后缀
• 保留字大写
• 新行上的主要关键字（脚本）
• 在列前使用逗号（脚本）
• 使用 vw 前缀视图
• 不要为表添加前缀
• 表名单数
• 为标准名称添加后缀，如“_ByPK”、“_OrderByLastName”或“_Top15Orders”以表示股票 SP 的变体

---

---

Select

选择

CREATE PROC [dbo].[procTable_SEL]
AS
SET NOCOUNT ON
SELECT
    [Column1] = T1.[col1]
  , [Column2] = T1.[col2]
  , [Column3] = T2.[col3]
FROM [dbo].[Table] T1    
INNER JOIN ON [dbo].[Table2] T2 ON T1.ID = T2.ID
WHERE
      T1.[col1] = 'xxx'
  AND T2.[Col3] = 'yyy'
SET NOCOUNT OFF
GO

CREATE PROC [dbo].[procTable_SEL]
AS
SET NOCOUNT ON
SELECT
    [Column1] = T1.[col1]
  , [Column2] = T1.[col2]
  , [Column3] = T2.[col3]
FROM [dbo].[Table] T1    
INNER JOIN ON [dbo].[Table2] T2 ON T1.ID = T2.ID
WHERE
      T1.[col1] = 'xxx'
  AND T2.[Col3] = 'yyy'
SET NOCOUNT OFF
GO

---

---

Update

更新

CREATE PROC [dbo].[procTable_UPD]
AS
SET NOCOUNT ON
UPDATE t1 SET
    [Column1] = @Value1
  , [Column2] = @Value2
  , [Column3] = @Value3
FROM [dbo].[Table1] T1
INNER JOIN ON [dbo].[Table2] T2 ON T1.[ID] = T2.[ID]
WHERE
      T1.[col1] = 'xxx'
  AND T2.[Col3] = 'yyy'
SET NOCOUNT OFF
GO

CREATE PROC [dbo].[procTable_UPD]
AS
SET NOCOUNT ON
UPDATE t1 SET
    [Column1] = @Value1
  , [Column2] = @Value2
  , [Column3] = @Value3
FROM [dbo].[Table1] T1
INNER JOIN ON [dbo].[Table2] T2 ON T1.[ID] = T2.[ID]
WHERE
      T1.[col1] = 'xxx'
  AND T2.[Col3] = 'yyy'
SET NOCOUNT OFF
GO

---

---

Insert

插入

CREATE PROC [dbo].[procTable_INS]
AS
SET NOCOUNT ON
INSERT INTO [Table1] (
[Column1]
  , [Column2]
  , [Column3]
)
VALUES (
    @Value1
  , @Value2
  , @Value3
)
SET NOCOUNT OFF
GO

CREATE PROC [dbo].[procTable_INS]
AS
SET NOCOUNT ON
INSERT INTO [Table1] (
[Column1]
  , [Column2]
  , [Column3]
)
VALUES (
    @Value1
  , @Value2
  , @Value3
)
SET NOCOUNT OFF
GO

OR

或者

CREATE PROC dbo.procTable_INS
AS
SET NOCOUNT ON
INSERT INTO [table1] (
    [Column1]
  , [Column2]
  , [Column3]
)
SELECT
    [Column1] = T1.col1
  , [Column2] = T1.col2
  , [Column3] = T2.col3
FROM dbo.Table1 T1    
INNER JOIN ON Table2 T2 ON T1.ID = T2.ID
WHERE
      T1.[col1] = 'xxx'
  AND T2.[Col3] = 'yyy'
SET NOCOUNT OFF
GO

CREATE PROC dbo.procTable_INS
AS
SET NOCOUNT ON
INSERT INTO [table1] (
    [Column1]
  , [Column2]
  , [Column3]
)
SELECT
    [Column1] = T1.col1
  , [Column2] = T1.col2
  , [Column3] = T2.col3
FROM dbo.Table1 T1    
INNER JOIN ON Table2 T2 ON T1.ID = T2.ID
WHERE
      T1.[col1] = 'xxx'
  AND T2.[Col3] = 'yyy'
SET NOCOUNT OFF
GO

---

---

Delete

删除

CREATE PROC dbo.procTable_DEL
AS
SET NOCOUNT ON
DELETE
FROM [dbo].[Table1] T1
INNER JOIN ON [dbo].[Table2] T2 ON T1.[ID] = T2.[ID]
WHERE
      T1.[col1] = 'xxx'
  AND T2.[Col3] = 'yyy'
SET NOCOUNT OFF
GO

CREATE PROC dbo.procTable_DEL
AS
SET NOCOUNT ON
DELETE
FROM [dbo].[Table1] T1
INNER JOIN ON [dbo].[Table2] T2 ON T1.[ID] = T2.[ID]
WHERE
      T1.[col1] = 'xxx'
  AND T2.[Col3] = 'yyy'
SET NOCOUNT OFF
GO

回答by Rob Prouse

If you google, there are plenty of coding standards out there. For example,

如果你用谷歌搜索，那里有很多编码标准。例如，

Database Coding Standard and Guideline

数据库编码标准和指南

and

和

SQL SERVER Database Coding Standards and Guidelines Complete List

SQL SERVER 数据库编码标准和指南完整列表

回答by Alex. S.

From a really very nice blog on PostgreSQL, but this topic is applicable in general:

来自一篇关于 PostgreSQL 的非常好的博客，但这个主题一般适用：

Maintainable queries - my point of view (depesz.com)

可维护的查询 - 我的观点 (depesz.com)

...I decided that my priorities for writing maintainable queries:

1. Avoid useless typing.

2. Use aliases for tables/views. Always. And make them sensible aliases.

3. Indent code in some way.

4. Avoid quotations (yes, this is why I hate Django)

5. Use join syntax

...我决定编写可维护查询的优先级：

1. 避免无用的打字。

2. 为表/视图使用别名。总是。并使它们成为合理的别名。

3. 以某种方式缩进代码。

4. 避免引用（是的，这就是我讨厌 Django 的原因）

5. 使用连接语法

I do agree with capitalization of reserved words and every other identifier, except my own.

我同意保留字和所有其他标识符的大写，除了我自己的。

回答by ZombieSheep

I personally don't like to prefix a stored procedure name with sp_ - it is redundant, IMO. Instead, I like to prefix them with a "unit of functionality" identifier. e.g. I'll call the sprocs to deal with orders order_Save, order_GetById, order_GetByCustomer, etc. It keeps them all logically grouped in management studio and makes it harder to pick the wrong one. (GetOrderByProduct, GetCustomerById, etc...)

我个人不喜欢用 sp_ 作为存储过程名称的前缀 - 这是多余的，IMO。相反，我喜欢用“功能单元”标识符作为前缀。例如，我将调用 sprocs 来处理订单 order_Save、order_GetById、order_GetByCustomer 等。它使它们在管理工作室中保持逻辑分组，并且更难选择错误的。（GetOrderByProduct、GetCustomerById 等...）

Of course, it is personal preference, other people may prefer to have all the Get sprocs together, all the Save ones, etc.

当然，这是个人喜好，其他人可能更喜欢将所有 Get sproc 放在一起，所有 Save 等。

Just my 2c.

只是我的2c。

回答by adamJLev

SELECT c.id
     , c.name
     , c.folder
     , cs.num_users active_members
     , cs.num_videos

  FROM campaign c
  JOIN campaign_stats cs
    ON cs.campaign_id = c.id
  JOIN (SELECT _c.id
             , _c.name

          FROM campaign _c
         WHERE _c.type = 9) t_c 
    ON t_c.id = c.id

 WHERE c.id IN (1,2,3)
   AND cs.num_videos > 10

This works pretty good for us.

这对我们很有用。

This actual query doesn't make much sense since I tried to build it quickly as an example... but that's not the point.

这个实际的查询没有多大意义，因为我试图快速构建它作为示例......但这不是重点。

• t_c stands for category table sub-query or "temp category".
• _underscoring of stuff inside sub-queries.
• alias column names to make sense in the context of the query. e.g. "active_members"

• putting commas at the beginning of the new lines makes it easier to build dynamic queries:

  $sql .= ", c.another_column"
  

• everything else is straightforward.

• t_c 代表类别表子查询或“临时类别”。
• _underscoring 子查询中的内容。
• 别名列名在查询的上下文中有意义。例如“active_members”

• 将逗号放在新行的开头可以更轻松地构建动态查询：

  $sql .= ", c.another_column"
  

• 其他一切都很简单。

回答by Robin

I generally keep very little per line, ie:

我通常每行保留很少，即：

select
    col1,
    col2,
    col3
from
    some_table tabl1
where
    col1 = 'some'
and 
(
    col2 = 'condition'
or  col2 = 'other'
)

回答by Thorsten

Google for sql pretty printer or look here. I haven't tried it out myself, but it gives you a good start. Most commercial tools like Toad have a "formatting" option which helps, too.

Google for sql 漂亮的打印机或看这里。我自己还没有尝试过，但它为您提供了一个良好的开端。大多数像 Toad 这样的商业工具都有一个“格式化”选项，这也有帮助。

回答by Patrick Harrington

Play around with www.sqlinform.com- I recommend using the ANSI-92 standard, and then pretty it up with that site.

玩弄www.sqlinform.com-我建议使用ANSI-92标准，再漂亮的它与该网站。