在SQL Server中什么时候使用分号?

While checking some code on the web and scripts generated by SQL Server Management Studio I have noticed that some statements are ended with a semicolon.

在检查web上的一些代码和SQL Server Management Studio生成的脚本时，我注意到有些语句以分号结尾。

So when should I use it?

我应该什么时候使用它?

13 个解决方案

#1

From a SQLServerCentral.Com article by Ken Powers:

SQLServerCentral。Ken Powers的文章:

The Semicolon

分号

The semicolon character is a statement terminator. It is a part of the ANSI SQL-92 standard, but was never used within Transact-SQL. Indeed, it was possible to code T-SQL for years without ever encountering a semicolon.

分号字符是一个语句结束符。它是ANSI SQL-92标准的一部分，但从未在Transact-SQL中使用。实际上，编写T-SQL长达数年却从未遇到过分号。

Usage

使用

There are two situations in which you must use the semicolon. The first situation is where you use a Common Table Expression (CTE), and the CTE is not the first statement in the batch. The second is where you issue a Service Broker statement and the Service Broker statement is not the first statement in the batch.

有两种情况，您必须使用分号。第一种情况是使用公共表表达式(CTE)，而CTE不是批处理中的第一个语句。第二种方法是发布服务代理语句，而服务代理语句不是批处理中的第一个语句。

#2

By default, SQL statements are terminated with semicolons. You use a semicolon to terminate statements unless you've (rarely) set a new statement terminator.

默认情况下，SQL语句以分号结束。使用分号来终止语句，除非您(很少)设置了新的语句结束符。

If you're sending just one statement, technically you can dispense with the statement terminator; in a script, as you're sending more than one statement, you need it.

如果你只发送一条语句，理论上你可以省去语句终止符;在脚本中，当您发送多个语句时，您需要它。

In practice, always include the terminator even if you're just sending one statement to the database.

实际上，即使只向数据库发送一条语句，也要包含终止符。

Edit: in response to those saying statement terminators are not required by [particular RDBMS], while that may be true, they're required by the ANSI SQL Standard. In all programming, if we can adhere to a Standard without loss of functionality, we should, because then neither our code or our habits are tied to one proprietary vendor.

编辑:对于[特定RDBMS]不需要语句终止符的说法，尽管这可能是正确的，但ANSI SQL标准需要它们。在所有的编程中，如果我们能够坚持一个标准而不丢失功能，那么我们应该这样做，因为我们的代码或习惯都不会与一个专有供应商绑定。

With some C compilers, it's possible to have main return void, even though the Standard requires main to return int. But doing so makes our code, and ourselves, less portable.

使用一些C编译器，虽然标准要求main返回int，但是main返回void是可能的，但是这样做会使我们的代码和我们自己的代码变得不那么可移植性。

The biggest difficulty in programming effectively isn't learning new things, it's unlearning bad habits. To the extent that we can avoid acquiring bad habits in the first place, it's a win for us, for our code, and for anyone reading or using our code.

有效编程的最大困难不是学习新东西，而是改掉坏习惯。在某种程度上，我们首先可以避免养成坏习惯，这对我们、对我们的代码、对任何阅读或使用我们代码的人来说都是一种胜利。

#3

In SQL2008 BOL they say that in next releases semicolons will be required. Therefore, always use it.

在SQL2008 BOL中，他们说在下一个版本中将需要分号。因此,总是使用它。

Reference:

参考:

• Transact-SQL Syntax Conventions (Transact-SQL)
• transact - sql语法约定(transact - sql)
• Deprecated Database Engine Features in SQL Server 2008 R2 ("Features Not Supported in a Future Version of SQL Server" section, "Transact-SQL" area)
• SQL Server 2008 R2中的废弃数据库引擎特性(“SQL Server未来版本不支持的特性”小节“Transact-SQL”区域)

#4

If I read this correctly, it will be a requirement to use semicolons to end TSQL statements. http://msdn.microsoft.com/en-us/library/ms143729%28v=sql.120%29.aspx

如果我没看错，就需要使用分号来结束TSQL语句。http://msdn.microsoft.com/en-us/library/ms143729%28v=sql.120%29.aspx

EDIT: I found a plug-in for SSMS 2008R2 that will format your script and add the semicolons. I think it is still in beta though...

编辑:我为SSMS 2008R2找到了一个插件，它将格式化您的脚本并添加分号。我认为它还在测试中…

http://www.tsqltidy.com/tsqltidySSMSAddin.aspx

http://www.tsqltidy.com/tsqltidySSMSAddin.aspx

EDIT: I found an even better free tool/plugin called ApexSQL... http://www.apexsql.com/

编辑:我发现了一个更好的免费工具/插件ApexSQL…http://www.apexsql.com/

#5

You must use it.

你必须使用它。

The practice of using a semicolon to terminate statements is standard and in fact is a requirement in several other database platforms. SQL Server requires the semicolon only in particular cases—but in cases where a semicolon is not required, using one doesn’t cause problems. I strongly recommend that you adopt the practice of terminating all statements with a semicolon. Not only will doing this improve the readability of your code, but in some cases it can save you some grief. (When a semicolon is required and is not specified, the error message SQL Server produces is not always very clear.)

使用分号终止语句的实践是标准的，实际上是其他一些数据库平台的需求。SQL Server只在特定情况下需要分号，但在不需要分号的情况下，使用分号不会导致问题。我强烈建议您采用使用分号终止所有语句的做法。这样做不仅可以提高代码的可读性，而且在某些情况下还可以避免一些麻烦。(当需要分号且未指定分号时，SQL Server生成的错误消息并不总是非常清晰。)

And most important:

最重要的是:

The SQL Server documentation indicates that not terminating T-SQL statements with a semicolon is a deprecated feature. This means that the long-term goal is to enforce use of the semicolon in a future version of the product. That’s one more reason to get into the habit of terminating all of your statements, even where it’s currently not required.

SQL Server文档表明，不使用分号终止T-SQL语句是不可取的特性。这意味着长期目标是在产品的未来版本中强制使用分号。这是养成习惯终止所有语句的另一个原因，即使是在当前不需要的地方。

Source: Microsoft SQL Server 2012 T-SQL Fundamentals by Itzik Ben-Gan.

资料来源:微软SQL Server 2012 T-SQL basic。

---

An example of why you always must use ; are the following two queries (copied from this post):

为什么你总是必须使用一个例子;以下是两个查询(转载自本文):

BEGIN TRY
    BEGIN TRAN
    SELECT 1/0 AS CauseAnException
    COMMIT
END TRY
BEGIN CATCH
    SELECT ERROR_MESSAGE()
    THROW
END CATCH

BEGIN TRY
    BEGIN TRAN
    SELECT 1/0 AS CauseAnException;
    COMMIT
END TRY
BEGIN CATCH
    SELECT ERROR_MESSAGE();
    THROW
END CATCH

#6

Personal opinion: Use them only where they are required. (See TheTXI's answer above for the required list.)

个人意见:只在需要的地方使用。(请参阅上文TheTXI的答案。)

Since the compiler doesn't require them, you can put them all over, but why? The compiler won't tell you where you forgot one, so you'll end up with inconsistent use.

既然编译器不需要它们，你可以把它们都放在一起，但是为什么呢?编译器不会告诉你你在哪里忘记了一个，所以你最终会得到不一致的使用。

[This opinion is specific to SQL Server. Other databases may have more-stringent requirements. If you're writing SQL to run on multiple databases, your requirements may vary.]

这个观点是针对SQL Server的。其他数据库可能有更严格的要求。如果要在多个数据库上编写SQL，您的需求可能会有所不同。

tpdi stated above, "in a script, as you're sending more than one statement, you need it." That's actually not correct. You don't need them.

tpdi上面说过，“在脚本中，当您发送多个语句时，您需要它。”这是不正确的。你不需要它们。

PRINT 'Semicolons are optional'
PRINT 'Semicolons are optional'
PRINT 'Semicolons are optional';
PRINT 'Semicolons are optional';

Output:

输出:

Semicolons are optional
Semicolons are optional
Semicolons are optional
Semicolons are optional

#7

I still have a lot to learn about T-SQL, but in working up some code for a transaction (and basing code on examples from * and other sites) I found a case where it seems a semicolon is required and if it is missing, the statement does not seem to execute at all and no error is raised. This doesn't seem to be covered in any of the above answers. (This was using MS SQL Server 2012.)

我还有很多要学习t - sql,但是在一些代码工作事务(和基于代码示例从*和其他网站)我发现情况似乎需要一个分号,如果丢失,声明似乎并没有执行,没有错误。上面的答案似乎没有涉及到这一点。(使用的是MS SQL Server 2012。)

Once I had the transaction working the way I wanted, I decided to put a try-catch around it so if there are any errors it gets rolled back. Only after doing this, the transaction was not committed (SSMS confirms this when trying to close the window with a nice message alerting you to the fact that there is an uncommitted transaction.

一旦我按照我想要的方式进行事务处理，我决定在它周围设置一个try-catch，这样如果有任何错误，它就会回滚。只有在这样做之后，事务才会被提交(SSMS在试图关闭窗口时确认这一点，并发出一条漂亮的消息，提醒您有一个未提交的事务。

So this

所以这

COMMIT TRANSACTION 

outside a BEGIN TRY/END TRY block worked fine to commit the transaction, but inside the block it had to be

在BEGIN TRY/END TRY块之外提交事务是可行的，但在block中必须如此

COMMIT TRANSACTION;

Note there is no error or warning provided and no indication that the transaction is still uncommitted until attempting to close the query tab.

注意，在试图关闭查询选项卡之前，没有提供错误或警告，也没有指示事务仍然未提交。

Fortunately this causes such a huge problem that it is immediately obvious that there is a problem. Unfortunately since no error (syntax or otherwise) is reported it was not immediately obvious what the problem was.

幸运的是，这导致了如此巨大的问题，以至于很明显存在问题。不幸的是，由于没有错误(语法或其他)被报告，所以问题并不明显。

Contrary-wise, ROLLBACK TRANSACTION seems to work equally well in the BEGIN CATCH block with or without a semicolon.

相反地，回滚事务在具有或不具有分号的BEGIN CATCH块中似乎同样有效。

There may be some logic to this but it feels arbitrary and Alice-in-Wonderland-ish.

这可能有一些逻辑，但它让人觉得是任意的，而且是仙境般的。

#8

According to Transact-SQL Syntax Conventions (Transact-SQL) (MSDN)

根据Transact-SQL语法约定(Transact-SQL) (MSDN)

Transact-SQL statement terminator. Although the semicolon is not required for most statements in this version of SQL Server, it will be required in a future version.

transact - sql语句结束符。虽然在这个版本的SQL Server中，大多数语句都不需要分号，但是在将来的版本中会需要分号。

(also see @gerryLowry 's comment)

(见@gerryLowry的评论)

#9

It appears that semicolons should not be used in conjunction with cursor operations: OPEN, FETCH, CLOSE and DEALLOCATE. I just wasted a couple of hours with this. I had a close look at the BOL and noticed that [;] is not shown in the syntax for these cursor statements!!

看起来分号不应该与游标操作一起使用:OPEN、FETCH、CLOSE和DEALLOCATE。我只是浪费了几个小时。我仔细看了一下BOL，注意到这些游标语句的语法没有显示出来!!

So I had: OPEN mycursor; and this gave me error 16916.

打开mycursor;结果是16916。

But: OPEN mycursor worked.

但是:打开mycursor工作。

#10

When using either a DISABLE or ENABLE TRIGGER statement in a batch that has other statements in it, the statement just before it must end with a semicolon. Otherwise, you'll get a syntax error. I tore my hair out with this one... And afterwards, I stumbled on this MS Connect item about the same thing. It is closed as won't fix.

当在具有其他语句的批处理中使用DISABLE或ENABLE触发器语句时，语句必须以分号结束。否则，会出现语法错误。我用这个把我的头发扯下来了……后来，我偶然发现了这个MS Connect项目，关于同样的事情。它是关闭的，不会修复。

see here

在这里看到的

#11

Semicolons do not always work in compound SELECT statements.

分号并不总是在复合选择语句中工作。

Compare these two different versions of a trivial compound SELECT statement.

比较普通复合选择语句的这两个不同版本。

The code

的代码

DECLARE @Test varchar(35); 
SELECT @Test=
    (SELECT 
        (SELECT 
            (SELECT 'Semicolons do not always work fine.';););); 
SELECT @Test Test;

returns

返回

Msg 102, Level 15, State 1, Line 5
Incorrect syntax near ';'.

However, the code

然而,代码

DECLARE @Test varchar(35)
SELECT @Test=
    (SELECT 
        (SELECT 
            (SELECT 'Semicolons do not always work fine.'))) 
SELECT @Test Test

returns

返回

Test
-----------------------------------
Semicolons do not always work fine.

(1 row(s) affected)

#12

Note: This answers the question as written, but not the problem as stated. Adding it here, since people will be searching for it

注意:这回答的是书面问题，而不是陈述的问题。在这里加上它，因为人们将会搜索它

Semicolon is also used before WITH in recursive CTE statements:

在递归CTE语句中也使用分号:

;WITH Numbers AS
(
    SELECT n = 1
    UNION ALL
    SELECT n + 1
    FROM Numbers
    WHERE n+1 <= 10
)
SELECT n
FROM Numbers

This query will generate a CTE called Numbers that consists of integers [1..10]. It is done by creating a table with the value 1 only, and then recursing until you reach 10.

这个查询将生成一个名为number的CTE，它由整数组成[1..10]。它通过只创建值1的表来完成，然后递归到10。

#13

If you like getting random Command Timeout errors in SQLServer then leave off the semi-colon at the end of your CommandText strings.

如果您希望在SQLServer中获得随机命令超时错误，那么请在CommandText字符串的末尾删除分号。

I don't know if this is documented anywhere or if it is a bug, but it does happen and I have learnt this from bitter experience.

我不知道这是否被记录在任何地方或者它是否是一个bug，但它确实发生了，我从痛苦的经历中学到了这一点。

I have verifiable and reproducible examples using SQLServer 2008.

我有使用SQLServer 2008的可验证和可再现的示例。

aka -> In practice, always include the terminator even if you're just sending one statement to the database.

实际上，即使只向数据库发送一条语句，也要包含终止符。