set =concat_影响查询结果SQL Server SET选项– SET CONCAT_NULL_YIELDS_NULL，SET NUMERIC_ROUNDABORT，SET QUOTED_IDENTIFIER，SET NOCOUNT，SET XAC

set =concat

In the previous article of this two-part series SQL Server SET Options that Affect the Query Result – SET ANSI_NULLS, SET ANSI_PADDING, SET ANSI_WARNINGS and SET ARITHABORT, we described the first four SQL Server SET options and showed practically how setting these options ON and OFF affects the SQL Server Database Engine behavior and the query result. To recall, SQL Server SET options are a group of session-level options that control how the SQL Server behaves on the database session level, and the option value can be changed using the SET T-SQL command for the current session that you execute the SET command on.

在这个分为两部分的系列的上一篇文章中，影响查询结果SQL Server SET选项– SET ANSI_NULLS，SET ANSI_PADDING，SET ANSI_WARNINGS和SET ARITHABORT ，我们描述了前四个SQL Server SET选项，并实际展示了如何将这些选项设置为ON和OFF影响SQL Server数据库引擎的行为和查询结果。 回想一下，SQL Server SET选项是一组会话级选项，它们控制SQL Server在数据库会话级上的行为，并且可以使用SET T-SQL命令为您执行当前会话的当前会话更改选项值。 SET命令打开。

In this article, we will describe another five SET options and see how turning it ON and OFF will change the SQL Server behavior and the query result.

在本文中，我们将介绍另外五个SET选项，并了解如何将其打开和关闭将如何更改SQL Server的行为和查询结果。

设置CONCAT_NULL_YIELDS_NULL (SET CONCAT_NULL_YIELDS_NULL)

The SET CONCAT_NULL_YIELDS_NULL option controls if the string concatenation results with the NULL value are treated as null or empty string values. The T-SQL statement that can be used to control the CONCAT_NULL_YIELDS_NULL option is:

SET CONCAT_NULL_YIELDS_NULL选项控制是否将具有NULL值的字符串连接结果视为空值或空字符串值。 可用于控制CONCAT_NULL_YIELDS_NULL选项的T-SQL语句为：

SET CONCAT_NULL_YIELDS_NULL { ON | OFF }

SET CONCAT_NULL_YIELDS_NULL {ON | 关闭}

When you set the CONCAT_NULL_YIELDS_NULL option to ON, the result of concatenating a null value with a string is the NULL value. If you set the CONCAT_NULL_YIELDS_NULL option to OFF, the result of concatenating a null value with a string will be the string itself, treating the null value as an empty string.

当您将CONCAT_NULL_YIELDS_NULL选项设置为ON时，将空值与字符串连接起来的结果就是NULL值。 如果将CONCAT_NULL_YIELDS_NULL选项设置为OFF，则将空值与字符串连接的结果将是字符串本身，将空值视为空字符串。

In order to execute a distributed query or to create or change indexes on computed columns or indexed views, the SET CONCAT_NULL_YIELDS_NULL option should be set to ON. Otherwise, the operation will fail and the SQL Server will return an error that lists all SET options that violate the required values. The SET CONCAT_NULL_YIELDS_NULL setting is set at run time, not at the parse time.

为了执行分布式查询或在计算的列或索引视图上创建或更改索引，应将SET CONCAT_NULL_YIELDS_NULL选项设置为ON。 否则，该操作将失败，并且SQL Server将返回错误，列出所有违反所需值的SET选项。 SET CONCAT_NULL_YIELDS_NULL设置是在运行时而不是在解析时设置的。

Let us see how the CONCAT_NULL_YIELDS_NULL setting work. If we try to concatenate the below string with NULL value after setting the CONCAT_NULL_YIELDS_NULL option to ON:

让我们看看CONCAT_NULL_YIELDS_NULL设置是如何工作的。 如果在将CONCAT_NULL_YIELDS_NULL选项设置为ON之后尝试将以下字符串与NULL值连接：

 
SET CONCAT_NULL_YIELDS_NULL ON;
GO
SELECT 'Hello From SQLShack' + NULL As ConcatString;
GO
 

The concatenation result is NULL as shown below:

串联结果为NULL，如下所示：

Concatenating the same string with the NULL value, but this time after setting the CONCAT_NULL_YIELDS_NULL option to OFF:

将相同的字符串与NULL值连接，但这一次是在将CONCAT_NULL_YIELDS_NULL选项设置为OFF之后：

 
SET CONCAT_NULL_YIELDS_NULL OFF;
GO
SELECT 'Hello From SQLShack' + NULL As ConcatString;
GO
 

The concatenation result is the same string followed by blank, treating the NULL value as empty string as shown below:

串联结果是相同的字符串，后跟空白，将NULL值视为空字符串，如下所示：

设置NUMERIC_ROUNDABORT (SET NUMERIC_ROUNDABORT)

The NUMERIC_ROUNDABORT option identifies the level of error reporting that is generated when rounding in an expression causes a loss of precision. The T-SQL syntax used to control the NUMERIC_ROUNDABORT option is like below:

NUMERIC_ROUNDABORT选项标识在四舍五入导致精度损失时生成的错误报告的级别。 用于控制NUMERIC_ROUNDABORT选项的T-SQL语法如下：

SET NUMERIC_ROUNDABORT { ON | OFF }

SET NUMERIC_ROUNDABORT {开| 关闭}

When setting the NUMERIC_ROUNDABORT option to ON, an error will be generated if a loss of precision occurs in an expression. If you set the NUMERIC_ROUNDABORT option to OFF, losses of precision will not generate error messages and the result will be rounded to fit the precision of the column or the variable that will store the result. The precision loss can occur when you try to store a value with a specific precision in a column or variable with a precision less than this value. The NUMERIC_ROUNDABORT option specifies how the SQL Server will react in this situation.

将NUMERIC_ROUNDABORT选项设置为ON时，如果表达式中出现精度损失，则会生成错误。 如果将NUMERIC_ROUNDABORT选项设置为OFF，则精度损失将不会生成错误消息，并且结果将舍入以适合列或存储结果的变量的精度。 当您尝试将具有特定精度的值存储在列或精度小于该值的变量中时，可能会发生精度损失。 NUMERIC_ROUNDABORT选项指定SQL Server在这种情况下的React方式。

Recall the SET ARITHABORT option described in the previous article that terminates the query when an overflow or divide-by-zero error occurs during the execution of the query. If you set the ARITHABORT option to ON:

回忆上一篇文章中描述的SET ARITHABORT选项，该选项在查询执行期间发生溢出或被零除错误时终止查询。 如果将ARITHABORT选项设置为ON：

• Setting the NUMERIC_ROUNDABORT to ON, an error message will be generated and no result will be returned from the query.
  将NUMERIC_ROUNDABORT设置为ON，将生成一条错误消息，并且查询不会返回任何结果。
• Setting the NUMERIC_ROUNDABORT to OFF, no error or warning messages will be generated and the query will succeed returning a rounded result that fits the destination column or variable precision.
  将NUMERIC_ROUNDABORT设置为OFF，将不会生成任何错误或警告消息，并且查询将成功返回适合目标列或可变精度的舍入结果。

If you set the ARITHABORT option to OFF:

如果将ARITHABORT选项设置为OFF：

• Setting the NUMERIC_ROUNDABORT to ON, a warning message will be generated and NULL value will be returned from the query.
  将NUMERIC_ROUNDABORT设置为ON，将生成警告消息，并从查询中返回NULL值。
• Setting the NUMERIC_ROUNDABORT to OFF, no error or warning messages will be generated and the query will succeed returning a rounded result that fits the destination column or variable precision.
  将NUMERIC_ROUNDABORT设置为OFF，将不会生成任何错误或警告消息，并且查询将成功返回适合目标列或可变精度的舍入结果。

Again, to be able to execute the distributed queries or to be able to create or change indexes on computed columns or indexed views, the SET NUMERIC_ROUNDABORT option must be set to ON. Otherwise, the operation will fail and the SQL Server will return an error that lists all SET options that violate the required values. The SET NUMERIC_ROUNDABORT setting is also set at run time, not at parse time.

同样，为了能够执行分布式查询，或者能够在计算列或索引视图上创建或更改索引，必须将SET NUMERIC_ROUNDABORT选项设置为ON。 否则，该操作将失败，并且SQL Server将返回错误，列出所有违反所需值的SET选项。 SET NUMERIC_ROUNDABORT设置也是在运行时而不是在分析时设置的。

Let us see how the NUMERIC_ROUNDABORT setting work. If we try to add two decimal values with precision equal to 3 and store the result to a decimal variable with precision equal to 2, after setting both NUMERIC_ROUNDABORT and ARITHABORT options to ON:

让我们看看NUMERIC_ROUNDABORT设置是如何工作的。 如果我们尝试将两个精度等于3的十进制值相加并将结果存储到精度等于2的十进制变量中，然后将NUMERIC_ROUNDABORT和ARITHABORT选项都设置为ON：

 
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT ON
DECLARE @FinalResult DECIMAL(5, 2)
SELECT @FinalResult = 3.175 + 1.443
SELECT @FinalResult AS FinalResult 
GO
 

The query will fail and an error message will be generated indicating that an overflow issue is detected as shown below:

查询将失败，并且将生成一条错误消息，指示检测到溢出问题，如下所示：

Trying to add the same two decimal values with precision equal to 3 and store the result to a decimal variable with precision equal to 2, after setting the NUMERIC_ROUNDABORT option to OFF and the ARITHABORT options to ON:

在将NUMERIC_ROUNDABORT选项设置为OFF并将ARITHABORT选项设置为ON之后，尝试将精度等于3的两个相同的十进制值相加并将结果存储到精度等于2的十进制变量中：

 
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
DECLARE @FinalResult DECIMAL(5, 2)
SELECT @FinalResult = 3.175 + 1.443
SELECT @FinalResult AS FinalResult 
GO
 

No error or waning message will be generated:

不会产生错误或减弱消息：

And the query will succeed, with the result rounded to fit the destination variable precision size, which is 2 in our case, as shown below:

查询将成功，结果将四舍五入以适合目标变量的精度大小，在我们的示例中为2，如下所示：

Again, if we try to add the same two decimal values with precision equal to 3 and store the result to a decimal variable with precision equal to 2, after setting the NUMERIC_ROUNDABORT option to ON and the ARITHABORT options to OFF:

同样，如果将NUMERIC_ROUNDABORT选项设置为ON并将ARITHABORT选项设置为OFF，如果我们尝试将精度等于3的两个相同的十进制值相加并将结果存储到精度等于2的十进制变量中：

 
SET ARITHABORT OFF
SET NUMERIC_ROUNDABORT ON
DECLARE @FinalResult DECIMAL(5, 2)
SELECT @FinalResult = 3.175 + 1.443
SELECT @FinalResult AS FinalResult 
GO
 

The query will not fail, and a warning message will be generated indicating that an arithmetic overflow issue detected:

查询不会失败，并且将生成警告消息，指示检测到算术溢出问题：

And the result will be a NULL value returned from the query:

结果将是查询返回的NULL值：

设置QUOTED_IDENTIFIER (SET QUOTED_IDENTIFIER)

The QUOTED_IDENTIFIER option controls if the SQL Server will follow the ISO rules regarding the quotation mark delimiting identifiers and literal strings. The Identifiers that are delimited by double quotation marks can be either Transact-SQL reserved keywords or can contain characters that are not generally allowed by the Transact-SQL syntax rules for the identifiers. The T-SQL syntax used to control the QUOTED_IDENTIFIER option is like the below:

QUOTED_IDENTIFIER选项控制SQL Server是否遵循有关引号分隔标识符和文字字符串的ISO规则。 用双引号定界的标识符可以是Transact-SQL保留关键字，也可以包含Transact-SQL语法规则通常不允许使用的标识符标识符。 用于控制QUOTED_IDENTIFIER选项的T-SQL语法如下：

SET QUOTED_IDENTIFIER { ON | OFF }

SET QUOTED_IDENTIFIER {ON | 关闭}

If you set the QUOTED_IDENTIFIER option to ON, which is the default setting, any set of characters that are delimited by double quotation marks will be treated as T-SQL identifiers, such as table name, column name or stored procedure name, and any set of characters that are delimited by single quotation marks will be treated as literals. The single quotation mark can be represented by two single quotation marks (“), if that single quotation mark is part of the literal string. To be able to use the reserved keywords for object names in the database, you should set the QUOTED_IDENTIFIER option to ON.

如果将QUOTED_IDENTIFIER选项设置为ON（这是默认设置），则任何用双引号引起来的字符集都将被视为T-SQL标识符，例如表名，列名或存储过程名，以及任何由单引号引起来的字符集将被视为文字。 如果单引号是文字字符串的一部分，则可以用两个单引号（ “” ）表示。 为了能够将保留关键字用于数据库中的对象名称，应将QUOTED_IDENTIFIER选项设置为ON。

Setting the QUOTED_IDENTIFIER option to OFF, any set of characters that are delimited by double quotation marks or delimited by single quotation marks will be treated as literal. Moreover, the identifiers cannot be quoted and must follow all Transact-SQL rules for identifiers. If the literal string is delimited by double quotation marks, the string can contain single quotation marks within the string.

将QUOTED_IDENTIFIER选项设置为OFF，用双引号定界或用单引号定界的任何字符集都将被视为文字。 此外，标识符不能用引号引起来，并且必须遵循所有Transact-SQL标识符规则。 如果文字字符串由双引号分隔，则字符串可以在字符串内包含单引号。

To be able to Create or change indexes on computed columns or indexed views, the SET QUOTED_IDENTIFIER option must be set to ON. Otherwise, the operation will fail and the SQL Server will return an error with all SET options that violate the required values. The SET NUMERIC_ROUNDABORT setting is set at parse time, which means that the SET QUOTED_IDENTIFIER statement will take effect before any statements are executed.

为了能够在计算列或索引视图上创建或更改索引，必须将SET QUOTED_IDENTIFIER选项设置为ON。 否则，该操作将失败，并且SQL Server将返回一个错误，其中包含所有违反必需值的SET选项。 SET NUMERIC_ROUNDABORT设置是在分析时设置的，这意味着SET QUOTED_IDENTIFIER语句将在执行任何语句之前生效。

Now, we will see how the QUOTED_IDENTIFIER setting work. If you try to create the below table, using the UPDATE reserved keyword as a table name and the UNIQUE and CREATE reserved keywords as column names, after setting the QUOTED_IDENTIFIER option to OFF:

现在，我们将了解QUOTED_IDENTIFIER设置如何工作。 如果您尝试创建下表，请在将QUOTED_IDENTIFIER选项设置为OFF后，​​使用UPDATE保留关键字作为表名，并使用UNIQUE和CREATE保留关键字作为列名：

 
SET QUOTED_IDENTIFIER OFF
GO
CREATE TABLE "UPDATE" ("UNIQUE" INT  NULL, "CREATE" INT  NULL);
GO
 

The result will show that the query is failed, due to using reserved keywords delimited by double quotations as identifier names, as shown below:

结果将显示查询失败，这是因为使用了以双引号分隔的保留关键字作为标识符名称，如下所示：

Trying to create the same table, using the UPDATE reserved keyword as a table name and the UNIQUE and CREATE reserved keywords as column names, after setting the QUOTED_IDENTIFIER option to ON:

在将QUOTED_IDENTIFIER选项设置为ON之后，尝试使用UPDATE保留关键字作为表名，并使用UNIQUE和CREATE保留关键字作为列名来创建同一表：

 
SET QUOTED_IDENTIFIER ON;
GO
CREATE TABLE "UPDATE" ("UNIQUE" INT  NULL, "CREATE" INT  NULL);
GO
 

This operation is completed successfully now, even though you are using reserved keywords delimited by double quotations as identifier names because the QUOTED_IDENTIFIER option is set to ON, as shown below:

现在，即使您使用由双引号分隔的保留关键字作为标识符名称，此操作也已成功完成，因为QUOTED_IDENTIFIER选项设置为ON，如下所示：

If you try to define the below literal strings using the combination of single and double quotation marks, after setting the QUOTED_IDENTIFIER option to OFF:

如果您尝试使用单引号和双引号的组合来定义以下文字字符串，请将QUOTED_IDENTIFIER选项设置为OFF后：

 
SET QUOTED_IDENTIFIER OFF;
GO
--String Value in single quotes
SELECT "'Hello From SQLSHACK'"
--String Value in single quotes
SELECT '''Hello From SQLSHACK'''
--String Value with two single quotes
SELECT 'Hello From '''' SQLSHACK'
--String Value in double quotes
SELECT '"Hello From SQLSHACK"'
--String Value in double quotes
SELECT """Hello From SQLSHACK"""
--String Value with two double quotes 
SELECT "Hello From """" SQLSHACK"
GO
 

The query will succeed, treating the outer double quotation and single quotation marks as literal string delimiters. Defining two single quotation marks in the literal string, the first one will be treated as one string delimiter and the second one will be treated as part of the literal string, and defining two double quotation marks in the literal string, the first one will be treated as one string delimiter and the second one will be treated as part of the literal string.

查询将成功，将外部双引号和单引号视为文字字符串定界符。 在文字字符串中定义两个单引号，第一个将被视为一个字符串定界符，第二个将被视为文字字符串的一部分，并在文字字符串中定义两个双引号，第一个为视为一个字符串定界符，第二个定界符将视为文字字符串的一部分。

The different cases are clear in the result shown below:

如下所示的结果清楚表明了不同的情况：

Trying to define the same literal strings using the combination of single and double quotation marks, after setting the QUOTED_IDENTIFIER option to ON:

在将QUOTED_IDENTIFIER选项设置为ON之后，尝试使用单引号和双引号的组合定义相同的文字字符串：

 
SET QUOTED_IDENTIFIER ON;
GO
--String Value in single quotes
SELECT "'Hello From SQLSHACK'"
--String Value in single quotes
SELECT '''Hello From SQLSHACK'''
--String Value with two single quotes
SELECT 'Hello From '''' SQLSHACK'
--String Value in double quotes
SELECT '"Hello From SQLSHACK"'
--String Value in double quotes
SELECT """Hello From SQLSHACK"""
--String Value with two double quotes 
SELECT "Hello From """" SQLSHACK"
GO
 

The first string and the last two strings that contain the double quotation marks as string delimiter will fail, as the double quotation delimiter can be used only to define the identifiers when setting the QUOTED_IDENTIFIER option to ON, as shown in the result below:

包含双引号作为字符串定界符的第一个字符串和最后两个字符串将失败，因为当将QUOTED_IDENTIFIER选项设置为ON时，双引号定界符只能用于定义标识符，如以下结果所示：

设置数量 (SET NOCOUNT)

The NOCOUNT option stops displaying the number of rows affected by a T-SQL statement or stored procedure from being returned as part of the result set in the Messages tab. The T-SQL syntax that is used to control the NOCOUNT option is shown below:

NOCOUNT选项停止显示受T-SQL语句或存储过程影响的行数作为“消息”选项卡中结果集的一部分返回。 下面显示了用于控制NOCOUNT选项的T-SQL语法：

SET NOCOUNT { ON | OFF }

设置NOCOUNT {开| 关闭}

If you set the NOCOUNT option to ON, the number of affected rows will not be returned. Setting the NOCOUNT option to OFF, the number of affected rows will be returned. The setting of the NOCOUNT option will not affect the @@ROWCOUNT function, that will be updated in all cases even if the NOCOUNT option is set to ON.

如果将NOCOUNT选项设置为ON，则不会返回受影响的行数。 将NOCOUNT选项设置为OFF，将返回受影响的行数。 NOCOUNT选项的设置不会影响@@ ROWCOUNT函数，即使NOCOUNT选项设置为ON，该函数在所有情况下都会更新。

Setting the NOCOUNT option to ON helps significantly in enhancing the performance of stored procedures that contain loops and many statements that don’t return too much actual data, by reducing the network traffic caused by writing such type of messages. The SET NOCOUNT option takes effect at a run time and not at a parse time.

将NOCOUNT选项设置为ON可以通过减少由于编写此类消息而引起的网络流量，来极大地提高包含循环和许多不返回太多实际数据的语句的存储过程的性能。 SET NOCOUNT选项在运行时而不是在解析时生效。

Let us see how the SET NOCOUNT option simply works. If we try to run the below simple SELECT statement after setting the NOCOUNT option to OFF:

让我们看看SET NOCOUNT选项是如何工作的。 如果我们在将NOCOUNT选项设置为OFF后尝试运行以下简单的SELECT语句：

 
SET NOCOUNT OFF;  
GO
SELECT * FROM ANSI_Test
 

The number of returned rows is displayed in the Messages tab as shown below:

返回的行数显示在“消息”选项卡中，如下所示：

Running the same SELECT statement after setting the NOCOUNT option to ON:

将NOCOUNT选项设置为ON后，运行相同的SELECT语句：

 
SET NOCOUNT ON;  
GO
SELECT * FROM ANSI_Test
 

The number of returned rows will not be displayed in the Messages tab, and only a simple message indicating that the command is executed successfully will be displayed, as shown below:

返回的行数将不会显示在“消息”选项卡中，而只会显示一条简单的消息，指示命令已成功执行，如下所示：

设定XACT_ABORT (SET XACT_ABORT)

By default, T-SQL statements that are executed within the BEGIN TRANSACTION and COMMIT TRANSACTION commands will not be rolled back automatically, as one batch, in the case of any failure. The XACT_ABORT option controls if the T-SQL statements inside the current transaction will be rolled back automatically if any of these T-SQL statements raises a run-time error. The T-SQL syntax used to control the XACT_ABORT option is like the below:

默认情况下，如果发生任何故障，在BEGIN TRANSACTION和COMMIT TRANSACTION命令中执行的T-SQL语句将不会作为一批自动回滚。 XACT_ABORT选项控制如果这些T-SQL语句中的任何一个引发运行时错误，当前事务中的T-SQL语句是否将自动回滚。 用于控制XACT_ABORT选项的T-SQL语法如下所示：

SET XACT_ABORT { ON | OFF }

SET XACT_ABORT {开| 关闭}

The XACT_ABORT option is OFF by default. If you set the XACT_ABORT option to ON, the entire transaction will be terminated and the changes will be rolled back if any T-SQL statement inside that transaction raises an exception, depending on the severity of the generated error. Compile errors, such as syntax errors, will not be affected by XACT_ABORT option value.

XACT_ABORT选项默认情况下为OFF。 如果将XACT_ABORT选项设置为ON，则根据所生成错误的严重性，如果该事务中的任何T-SQL语句引发异常，则整个事务将终止，并且更改将回滚。 编译错误（例如语法错误）将不受XACT_ABORT选项值的影响。

Setting the XACT_ABORT option to OFF, only the Transact-SQL statement that raised the error will be rolled back and the transaction will continue processing with no issue. Again, depending on the severity of the generated error, the transaction may be completely rolled back regardless of the XACT_ABORT option value. SET XACT_ABORT option takes effect at run time.

将XACT_ABORT选项设置为OFF，将仅回滚引发错误的Transact-SQL语句，并且事务将继续处理而不会出现问题。 同样，根据所产生错误的严重性，无论XACT_ABORT选项值如何，都可以完全回滚事务。 SET XACT_ABORT选项在运行时生效。

Let us see how the SET XACT_ABORT option works. A simple table will be created with one INT column and try to insert three values twice to that table within transactions, with the middle insertion process causing a divide-by-zero error on each transaction. The first three records will be inserted with the XACT_ABORT option set to OFF and the second three records will be inserted with the XACT_ABORT option set to ON:

让我们看看SET XACT_ABORT选项如何工作。 将创建一个带有一个INT列的简单表，并尝试在事务内向该表两次插入三个值，中间插入过程会在每个事务上导致被零除错误。 前三个记录将在XACT_ABORT选项设置为OFF的情况下插入，后三个记录将在XACT_ABORT选项设置为ON的情况下插入：

 
CREATE TABLE XACT_ABORT_Test (ID INT)
GO
SET XACT_ABORT OFF;  
GO  
BEGIN TRANSACTION;  
INSERT INTO XACT_ABORT_Test VALUES (1);  
INSERT INTO XACT_ABORT_Test VALUES (1/0);   
INSERT INTO XACT_ABORT_Test VALUES (3);  
COMMIT TRANSACTION;  
GO  
SET XACT_ABORT ON;  
GO  
BEGIN TRANSACTION;  
INSERT INTO XACT_ABORT_Test VALUES (4);  
INSERT INTO XACT_ABORT_Test VALUES (1/0);   
INSERT INTO XACT_ABORT_Test VALUES (6);  
COMMIT TRANSACTION;  
GO
 

The result below will show that both transactions returned an error message due to divide-by-zero problem detected:

下面的结果将显示由于检测到被零除问题，两个事务都返回了错误消息：

Trying to retrieve the inserted data to the table:

尝试将插入的数据检索到表中：

 
SELECT * FROM XACT_ABORT_TestGO  
 

The result will show that the first transaction, with the XACT_ABORT option set to OFF, the first and the last INSERT statements executed successfully, and only the middle INSERT statement with divide-by-zero error is terminated. The second transaction with the XACT_ABORT option set to ON, the whole transaction is rolled back with no value inserted to that table from that transaction, as shown clearly below:

结果将显示，将XACT_ABORT选项设置为OFF的第一个事务成功执行了第一个和最后一个INSERT语句，并且仅终止了除以零错误的中间INSERT语句。 XACT_ABORT选项设置为ON的第二个事务，将回滚整个事务，而该事务中没有任何值插入到该表中，如下所示：

SQL Server provides us with many built-in functions that help in getting the SQL Server metadata information. One of these built-in functions is the @@OPTIONS function, that helps in getting the current values of the SET options for the current session. It returns an integer value that contains the current value of 15 SET options on the current session, with each option value stored in one bit of that integer value.

SQL Server为我们提供了许多内置函数，这些函数有助于获取SQL Server元数据信息。 这些内置函数之一是@@ OPTIONS函数，该函数有助于获取当前会话的SET选项的当前值。 它返回一个整数值，其中包含当前会话中15个SET选项的当前值，每个选项值都存储在该整数值的一位中。

The default values of the SET options that is returned from the @@OPTIONS function is 5496, with no option value overridden yet:

从@@ OPTIONS函数返回的SET选项的默认值为5496，尚未覆盖任何选项值：

Where the 5496 value is equivalent to 1010101111000 in binary, with each bit reflects the value of a specific option. The @@Option function can display the values of the nine SET options described in this articles series in more readable way using the T-SQL script below:

其中5496值等效于二进制的1010101111000，每个位都反映特定选项的值。 @@ Option函数可以使用以下T-SQL脚本以更易读的方式显示本系列文章中描述的九个SET选项的值：

 
IF ( (8 & @@OPTIONS) = 8 ) PRINT 'ANSI_WARNINGS :ON'                  ELSE PRINT 'ANSI_WARNINGS :OFF'
IF ( (16 & @@OPTIONS) = 16 ) PRINT 'ANSI_PADDING :ON'                 ELSE PRINT 'ANSI_PADDING :OFF'
IF ( (32 & @@OPTIONS) = 32 ) PRINT 'ANSI_NULLS :ON'                   ELSE PRINT 'ANSI_NULLS :OFF'
IF ( (64 & @@OPTIONS) = 64 ) PRINT 'ARITHABORT :ON'                   ELSE PRINT 'ARITHABORT :OFF' 
IF ( (256 & @@OPTIONS) = 256 ) PRINT 'QUOTED_IDENTIFIER :ON'          ELSE PRINT 'QUOTED_IDENTIFIER :OFF' 
IF ( (512 & @@OPTIONS) = 512 ) PRINT 'NOCOUNT :ON'                    ELSE PRINT 'NOCOUNT :OFF'
IF ( (4096 & @@OPTIONS) = 4096 ) PRINT 'CONCAT_NULL_YIELDS_NULL :ON'  ELSE PRINT 'CONCAT_NULL_YIELDS_NULL :OFF'
IF ( (8192 & @@OPTIONS) = 8192 ) PRINT 'NUMERIC_ROUNDABORT :ON'       ELSE PRINT 'NUMERIC_ROUNDABORT :OFF' 
IF ( (16384 & @@OPTIONS) = 16384 ) PRINT 'XACT_ABORT :ON'             ELSE PRINT 'XACT_ABORT :OFF'
 

After running the previous T-SQL script within a new session in my local SQL Server 2016 instance, the returned result will be as shown below:

在本地SQL Server 2016实例的新会话中运行先前的T-SQL脚本后，返回的结果如下所示：

Some of the previously described SET options can be set globally at the SQL Server instance level to be applied automatically to all users’ sessions using the SQL Server Management Studio, from the Connections tab of the SQL Server Instance Properties window as shown below:

可以从SQL Server实例属性窗口的“连接”选项卡中，使用SQL Server Management Studio在SQL Server实例级别上全局设置一些先前描述的SET选项，以自动应用于所有用户的会话，如下所示：

Take into consideration that setting these options values, at the session level, using the previously listed SET options will override the server level values for the current session.

请注意，使用先前列出的SET选项在会话级别设置这些选项值将覆盖当前会话的服务器级别值。

结论 (Conclusion)

In this article, we described deeply the widely used nine SET options that control the way SQL Server works and returns data.

在本文中，我们深入介绍了广泛使用的9个SET选项，这些选项控制SQL Server的工作方式并返回数据。

We demonstrated, in practical terms, for each option how the SQL Server behavior differs when you set these options ON and OFF. Due to the critical importance of these settings, you should be careful when you change the default SET options value, as these SET options change the way the SQL Server Database Engine behaves and the query results. It is better to test the behavior in the DEV environment first before applying it to the production environment.

实际上，我们为每个选项演示了将这些选项设置为ON和OFF时，SQL Server行为有何不同。 由于这些设置至关重要，因此在更改默认SET选项值时应格外小心，因为这些SET选项会更改SQL Server数据库引擎的行为方式和查询结果。 最好先在DEV环境中测试行为，然后再将其应用于生产环境。

目录 (Table of contents)

SQL Server SET Options that Affect the Query Result – SET ANSI_NULLS, SET ANSI_PADDING, SET ANSI_WARNINGS and SET ARITHABORT

SQL Server SET Options that Affect the Query Result – SET CONCAT_NULL_YIELDS_NULL, SET NUMERIC_ROUNDABORT, SET QUOTED_IDENTIFIER, SET NOCOUNT, SET XACT_ABORT

影响查询结果SQL Server SET选项-SET ANSI_NULLS，SET ANSI_PADDING，SET ANSI_WARNINGS和SET ARITHABORT

影响查询结果SQL Server SET选项– SET CONCAT_NULL_YIELDS_NULL，SET NUMERIC_ROUNDABORT，SET QUOTED_IDENTIFIER，SET NOCOUNT，SET XACT_ABORT

翻译自: https://www.sqlshack.com/sql-server-set-options-affect-query-result-set-concat_null_yields_null-set-numeric_roundabort-set-quoted_identifier-set-nocount-set-xact_abort/

set =concat