ansi_nulls_影响查询结果SQL Server SET选项-SET ANSI_NULLS，SET ANSI_PADDING，SET ANSI

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

阅读量：2520 次

发布时间：2019-05-11

本文共 28654 字，大约阅读时间需要 95 分钟。

ansi_nulls

SQL Server provides us with a number of options to control SQL Server behavior on the connection level. These session-level options are configured using the SET T-SQL command that change the option value for the session on which the SET command is executed. Changing the default value of these session-level configuration affects how the session queries will be executed affecting the query result. The performed change on a session-level option will be applied to the current session until its value is reset or until the current user’s session is terminated.

SQL Server为我们提供了许多选项来控制SQL Server在连接级别上的行为。这些会话级别的选项是使用SET T-SQL命令配置的，该命令会更改执行SET命令的会话的选项值。更改这些会话级配置的默认值会影响会话查询的执行方式，从而影响查询结果。在会话级别选项上执行的更改将应用于当前会话，直到重置其值或终止当前用户的会话为止。

In this article series, we will list nine most common and heavily used session-level SET options and how SQL Server behaves before and after changing its default configuration. In this article, the first in a two-part series, we will describe the first four options.

在本系列文章中，我们将列出九个最常用和最常用的会话级SET选项，以及更改默认配置前后SQL Server的行为。本文是由两部分组成的系列文章的第一篇，我们将介绍前四个选项。

设置ANSI_NULLS (SET ANSI_NULLS)

The ANSI_NULLS session-level option, as the name indicates, controls if the SQL Server Database Engine will follow the ANSI SQL Server standard in prohibiting the usage of the equality operators to compare with the NULL value, as the NULL is never equal to anything. Instead of the equality operators, the IS NULL and IS NOT NULL can be used to compare the current value with the NULL value regardless of the current ANSI_NULLS option value.

顾名思义，ANSI_NULLS会话级选项控制SQL Server数据库引擎在禁止使用相等运算符与NULL值进行比较时是否遵循ANSI SQL Server标准，因为NULL从不等于任何值。可以使用IS NULL和IS NOT NULL代替相等运算符，以将当前值与NULL值进行比较，而不考虑当前的ANSI_NULLS选项值。

The T-SQL syntax that is used to control the ANSI_NULLS option is shown below:

下面显示了用于控制ANSI_NULLS选项的T-SQL语法：

SET ANSI_NULLS { ON | OFF }

SET ANSI_NULLS {开| 关闭}

When setting ANSI_NULLS to ON, all comparisons against the NULL value will evaluate UNKNOWN; the (column_name=NULL) and (column_name<>NULL) in the WHERE clause will return no rows even there are NULL and non-NULL values in that column, with no mean of using the equality operators to compare with the NULL value.

将ANSI_NULLS设置为ON时，所有对NULL值的比较都会得出UNKNOWN；即使该列中有NULL和非NULL值，WHERE子句中的（column_name = NULL）和（column_name <> NULL）也将不返回任何行，而不必使用相等运算符与NULL值进行比较。

Setting the ANSI_NULLS to OFF, the Equals (=) and Not Equal To (<>) comparison operators will not follow the ISO standard for the current session and can be used to compare with the NULL value. In this case, the (column_name=NULL) in the WHERE statement will return all rows with NULL value in that column, and the (column_name<>NULL) will exclude the rows that have NULL value in that column, considering the NULL as a valid value for comparison.

将ANSI_NULLS设置为OFF，等于（=）和不等于（<>）比较运算符将不遵循当前会话的ISO标准，并且可以用于与NULL值进行比较。在这种情况下，WHERE语句中的（column_name = NULL）将返回该列中所有具有NULL值的行，并且（column_name <> NULL）将排除该列中具有NULL值的行，并将NULL视为比较的有效值。

Take into consideration that, for executing distributed queries or creating or changing indexes on computed columns or indexed views, the SET ANSI_NULLS 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 ANSI_NULLS setting will be set at run time and not at parse time.

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

Let us see how the ANSI_NULLS setting work. We will create a simple table and insert four records into that table:

让我们看看ANSI_NULLS设置如何工作。我们将创建一个简单的表并将四个记录插入到该表中：

 CREATE TABLE ANSI_NULLS_Test(ID INT IDENTITY (1,1), NAME VARCHAR(10) NULL)GOINSERT INTO ANSI_NULLS_Test VALUES ('John'), (NULL), ('Jenny'), (NULL)

In addition, we will see how the SQL Server behaves by default, when using the equality operators to compare the table values with the NULL value, and how it differs from using the IS NULL and IS NOT NULL operators:

此外，我们将了解SQL Server在默认情况下的行为，使用相等运算符将表值与NULL值进行比较时的行为，以及与使用IS NULL和IS NOT NULL运算符的区别：

 SELECT 'Default Setting in SQL Server 2016'SELECT * FROM ANSI_NULLS_Test WHERE [NAME] = NULLGOSELECT * FROM ANSI_NULLS_Test WHERE [NAME] <> NULLGOSELECT * FROM ANSI_NULLS_Test WHERE [NAME] IS NULLGOSELECT * FROM ANSI_NULLS_Test WHERE [NAME] IS NOT NULL

The result will show us that, no result will be returned when using the equality operators in evaluating the NULL values, which means that, by default, SQL Server evaluates the NULL value to UNKNOWN and will not consider it as a normal value that can be compared using the equality operators. This is not the case for the result returned from SELECT queries that use IS NULL and IS NOT NULL to evaluate the NULL values, which return correct values, as shown in the result below:

结果将向我们表明，使用相等运算符评估NULL值时不会返回任何结果，这意味着，默认情况下，SQL Server会将NULL值评估为UNKNOWN，并且不会将其视为可以使用相等运算符进行比较。从使用IS NULL和IS NOT NULL评估NULL值的SELECT查询返回的结果不是这种情况，返回正确的值，如下所示：

Setting the ANSI_NULL option to ON then executing the same SELECT statements:

将ANSI_NULL选项设置为ON，然后执行相同的SELECT语句：

 SELECT 'ANSI_NULLS ON'SET ANSI_NULLS ONGOSELECT * FROM ANSI_NULLS_Test WHERE [NAME] = NULLGOSELECT * FROM ANSI_NULLS_Test WHERE [NAME] <> NULLGOSELECT * FROM ANSI_NULLS_Test WHERE [NAME] IS NULLGOSELECT * FROM ANSI_NULLS_Test WHERE [NAME] IS NOT NULL

The same previous result will be returned from the query. Which means that by default, the ANSI_NULLS option is ON:

相同的先前结果将从查询中返回。这意味着默认情况下，ANSI_NULLS选项为ON：

If we set the ANSI_NULLS option to OFF and try the same SELECT statements:

如果我们将ANSI_NULLS选项设置为OFF并尝试相同的SELECT语句：

 SELECT 'ANSI_NULLS OFF'SET ANSI_NULLS OFFGOSELECT * FROM ANSI_NULLS_Test WHERE [NAME] = NULLGOSELECT * FROM ANSI_NULLS_Test WHERE [NAME] <> NULLGOSELECT * FROM ANSI_NULLS_Test WHERE [NAME] IS NULLGOSELECT * FROM ANSI_NULLS_Test WHERE [NAME] IS NOT NULL

You will see clearly that the NULL value is comparable now using equality operators; where the Equal to (=) operator works same as IS NULL operator and returns all rows with name column values equal to NULL.

您将清楚地看到，现在使用相等运算符可以比较NULL值；其中等于（=）运算符的工作原理与IS NULL运算符相同，并返回名称列值等于NULL的所有行。

On the other hand, the Not Equal to (<>) operators works same as IS NOT NULL operator and returns all rows with name column values not equal to NULL, evaluating the NULL value as any other normal value, as shown in the result below:

另一方面，不等于（<>）运算符的工作方式与IS NOT NULL运算符相同，并返回名称列值不等于NULL的所有行，并将NULL值评估为其他任何正常值，如以下结果所示：

It is better to use the IS NULL and IS NOT NULL operators for the NULL value comparison that will work in all cases regardless of the ANSI_NULLS option setting.

最好使用IS NULL和IS NOT NULL运算符进行NULL值比较，该值在所有情况下均适用，而与ANSI_NULLS选项设置无关。

设置ANSI_PADDING (SET ANSI_PADDING)

The ANSI_PADDING setting controls how trailing spaces are handled in columns with CHAR and VARCHAR data types, and trailing zeroes in columns with BINARY and VARBINARY data types. In other words, it specifies how the column stores the values shorter than the column defined size for that data types.

ANSI_PADDING设置控制如何使用CHAR和VARCHAR数据类型的列处理尾随空格，以及使用BINARY和VARBINARY数据类型的列处理尾随零。换句话说，它指定列如何存储比该数据类型的列定义大小短的值。

The T-SQL syntax that is used to control the ANSI_ PADDING option is shown below:

下面显示了用于控制ANSI_ PADDING选项的T-SQL语法：

SET ANSI_PADDING { ON | OFF }

设置ANSI_PADDING {开| 关闭}

When the ANSI_ PADDING option is set to ON, which is the default setting:

当ANSI_ PADDING选项设置为ON时，这是默认设置：

char(n) column with trailing blanks will be padded with spaces to the length of the column. The spaces will be retrieved with the column value. char（n）列的原始值将用空格填充到该列的长度。空格将与列值一起检索。

The original value of the binary(n) column with trailing zeroes will be padded with zeroes to the length of the column. The zeroes will be retrieved with the column value.

带有尾随零的binary（n）列的原始值将以零填充到列的长度。零将与列值一起检索 。

varchar(n) column will not be trimmed and the column original value will not be padded with spaces to the length of the column. varchar（n）列中的尾随空白将不会被修剪，并且该列的原始值将不会用空格填充到列的长度。

varbinary(n) column will not be trimmed and the column original value will not be padded with zeroes to the length of the column. varbinary（n）列中的尾随零将不会被修剪，并且列的原始值将不会用零填充到列的长度。

Setting the ANSI_ PADDING option is set to OFF:

将ANSI_ PADDING选项设置为OFF ：

char(n) NOT NULL column with trailing blanks will be padded with spaces to the length of the column. The spaces will not be retrieved with the column value. char（n）NOT NULL列的原始值将用空格填充到该列的长度。空格将不会与列值一起检索。

binary(n) NOT NULL column with trailing zeroes will be padded with zeroes to the length of the column. The zeroes will not be retrieved with the column value. binary（n）NOT NULL列的原始值将以零填充到该列的长度。零将不会与列值一起检索。

varchar(n) column will be trimmed and the column original value will not be padded with spaces to the length of the column. varchar（n）列中的尾随空白将被剪裁，并且该列的原始值将不会用该列的长度填充空格。

varbinary(n) column will be trimmed and the column original value will not be padded with zeroes to the length of the column. varbinary（n）列中的尾随零将被修剪，并且该列的原始值将不会用零填充到列的长度。

Null-able Null- able char(n) and char（n）和Null-able Null- able binary(n) columns with trailing blanks and zeroes will not be padded, and these spaces and zeroes will be trimmed from the original column value. binary（n）列的原始值将不会被填充，并且将从原始列值中修剪掉这些空格和零。

The SET ANSI_PADDING option is always ON for the columns with nchar, nvarchar, ntext, text, image data types. Which means that trailing spaces and zeros are not trimmed from the original value.

对于具有nchar，nvarchar，ntext，text，image数据类型的列，SET ANSI_PADDING选项始终为ON。这意味着尾随空格和零不会从原始值修剪掉。

The SET ANSI_PADDING option affects only newly created columns. Once created, the value will be stored in the column based on the setting configured when that column was created, and this column will not be affected by any new change performed after its creation.

SET ANSI_PADDING选项仅影响新创建的列。一旦创建，该值将根据创建该列时配置的设置存储在该列中，并且此列将不受其创建后执行的任何新更改的影响。

Again, taking into consideration that, for creating or changing indexes on computed columns or indexed views, the SET ANSI_ PADDING should be set to ON. The SET ANSI_ PADDING setting will be set at run time and not at parse time.

再次考虑到要在计算列或索引视图上创建或更改索引，应将SET ANSI_ PADDING设置为ON。 SET ANSI_ PADDING设置将在运行时而不是在分析时设置。

Let us see now, how the SQL Server will act with different ANSI_ PADDING settings. We will create a simple table that contains the targeted data types and insert two records into that table; the first record without spaces and zeroes and the second one with spaces and zeroes. As the ANSI_ PADDING setting affects only the newly created columns, we will create new table each time the ANSI_ PADDING option setting is changed. We will start with setting the ANSI_ PADDING to ON:

现在让我们来看一下SQL Server如何在不同的ANSI_ PADDING设置下运行。我们将创建一个包含目标数据类型的简单表，并将两个记录插入该表中。第一个记录不带空格和零，第二个记录不带空格和零。由于ANSI_ PADDING设置仅影响新创建的列，因此每次ANSI_ PADDING选项设置更改时，我们都会创建新表。我们将从将ANSI_ PADDING设置为ON开始：

 SET ANSI_PADDING ON; CREATE TABLE Test_ANSI_PADDING_ON (    Nullable_char char(15) NULL,    NotNull_char char(15) NOT NULL,    Nullable_varbinary varbinary(15) NULL,    Nullable_varchar varchar(15) NULL,    Nullable_Nvarchar nvarchar(15) NULL       ) INSERT INTO Test_ANSI_PADDING_ON VALUES ('Jack','Jack',0x00ee,'Jack','Jack'),            ('Jack  ','Jack  ',0x00ee00,'Jack  ','Jack  ');

Checking the length of each column data inserted into the table, with and without spaces using the DATALENGTH function:

使用DATALENGTH函数检查插入到表中的每个列数据的长度（带空格和不带空格）：

 SELECT DATALENGTH(Nullable_char) AS Nullable_char,       DATALENGTH(NotNull_char) AS NotNull_char,       DATALENGTH(Nullable_varbinary) AS Nullable_varbinary,       DATALENGTH(Nullable_varchar) AS Nullable_varchar,       DATALENGTH(Nullable_Nvarchar) AS Nullable_NvarcharFROM Test_ANSI_PADDING_ON;

The result will show that, the char data type will consume all the column size, which is 15. The case is different with the varchar, varbinary and nvarchar data types, in which the space will be calculated in the column length, but the rest of the column size that is not consumed will not be padded, as shown in the result below:

结果将显示，char数据类型将消耗所有列大小，即15。这种情况与varchar，varbinary和nvarchar数据类型不同，其中将在列长度中计算空间，但其余部分不会填充未使用的列大小，如下结果所示：

Retrieving the data inserted in each column, using the “<” character to specify the end of the column value for string data types:

检索插入到每一列中的数据，使用“ <”字符为字符串数据类型指定列值的结尾：

 SELECT  Nullable_char + '<'     AS Nullable_char       , NotNull_char + '<'      AS NotNull_char 	   , Nullable_varbinary      AS Nullable_varbinary	   , Nullable_varchar +'<'   AS Nullable_varchar	   , Nullable_Nvarchar +'<'  AS Nullable_NvarcharFROM Test_ANSI_PADDING_ON

It is clear from the result below that, for the char data type, the original value will be padded with spaces to the size of the column, which is 15 in our case. This is why we see the spaces in both records, although we did not inset spaces to that column in the first record.

从下面的结果可以明显看出，对于char数据类型，原始值将用空格填充到列的大小，在本例中为15。这就是为什么我们在两个记录中都看到空格的原因，尽管我们没有在第一条记录中为该列插入空格。

For the varchar, varbinary and nvarchar, the case is different, where the spaces and zeroes that are inserted will be retrieved with the column value, but the rest of the column size will not be padded with spaces and zeroes, and will be empty. This is why we see the spaces and zeroes in the second record that is not appeared in the first record:

对于varchar，varbinary和nvarchar，情况有所不同，其中将使用列值检索插入的空格和零，但是其余列大小将不填充空格和零，并且将为空。这就是为什么我们在第二条记录中看到的空格和零没有出现在第一条记录中的原因：

Let us see if it will behave in a different way when setting the ANSI_PADDING to OFF. We will create a new table with the same data types and sizes, and insert the same two records again:

让我们看看将ANSI_PADDING设置为OFF时它是否会以不同的方式运行。我们将创建一个具有相同数据类型和大小的新表，并再次插入相同的两条记录：

 SET ANSI_PADDING OFF; CREATE TABLE Test_ANSI_PADDING_OFF (    Nullable_char char(15) NULL,    NotNull_char char(15) NOT NULL,    Nullable_varbinary varbinary(15) NULL,    Nullable_varchar varchar(15) NULL,    Nullable_Nvarchar nvarchar(15) NULL       ) INSERT INTO Test_ANSI_PADDING_OFF VALUES ('Jack','Jack',0x00ee,'Jack','Jack'),            ('Jack  ','Jack  ',0x00ee00,'Jack  ','Jack  ');

Checking the length of each column data inserted into the table again, with and without spaces using the DATALENGTH function:

使用DATALENGTH函数再次检查插入表中的每个列数据的长度，是否带空格：

 SELECT DATALENGTH(Nullable_char) AS Nullable_char,       DATALENGTH(NotNull_char) AS NotNull_char,       DATALENGTH(Nullable_varbinary) AS Nullable_varbinary,       DATALENGTH(Nullable_varchar) AS Nullable_varchar,       DATALENGTH(Nullable_Nvarchar) AS Nullable_NvarcharFROM Test_ANSI_PADDING_OFF;

The result will show that, the NOT NULL char data type will act in the same way by consuming all the column size, which is 15.

结果将显示，NOT NULL char数据类型将通过消耗所有列大小（即15）以相同的方式起作用。

The case is different with the Null-able char varchar and varbinary data types, in which the space will not be calculated in the column length, and the rest of the column size that is not consumed will not be padded. The nvarchar data type will act in the same previous way, in which the spaces will be calculated in the column length but the rest of the column size will not be padded, as shown in the result below:

Null-able char varchar和varbinary数据类型的情况有所不同，在这些类型中，将不会在列长度中计算空格，并且不会填充未消耗的其余列大小。 nvarchar数据类型将以与以前相同的方式起作用，其中将在列长度中计算空格，但不填充其余列大小，如以下结果所示：

If we Retrieve the data inserted in each column, using the “<” character to specify the end of the column value for string data types:

如果我们检索插入到每一列中的数据，请使用“ <”字符为字符串数据类型指定列值的结尾：

 SELECT  Nullable_char + '<'     AS Nullable_char       , NotNull_char + '<'      AS NotNull_char 	   , Nullable_varbinary      AS Nullable_varbinary	   , Nullable_varchar +'<'   AS Nullable_varchar	   , Nullable_Nvarchar +'<'  AS Nullable_NvarcharFROM Test_ANSI_PADDING_OFF

You will see clearly from the result below that, the NOT NULL char data type will act same as previously, where the original value will be padded with spaces to the size of the column, which is 15 in this case. That is why you can see the spaces in both records, although we did not inset spaces to that column in the first record.

从下面的结果中您将清楚地看到，NOT NULL char数据类型将与以前的行为相同，在原始值中将用空格填充列的大小，在本例中为15。这就是为什么您可以看到两个记录中的空格的原因，尽管我们没有在第一条记录中的该列中插入空格。

For the Null-able char, varchar and varbinary data types, the spaces and zeroes that are inserted will not be retrieved with the column value, and the rest of the column size will not be padded with spaces and zeroes, and will be empty. This is why we cannot see the spaces and zeroes in the second record although we inserted these spaces and zeroes in the original value.

对于Null-able char，varchar和varbinary数据类型，将不使用列值检索插入的空格和零，并且其余列大小不会用空格和零填充，并且为空。这就是为什么尽管我们在原始值中插入了这些空格和零，但是却看不到第二个记录中的空格和零。

Nothing will be changed for the nvarchar data type, that will work always and ANSI_PADDING ON, the spaces will be retrieved but the rest of the column size will not be padded:

对于nvarchar数据类型，将不进行任何更改，该数据类型将始终有效并且为ANSI_PADDING ON，将检索空格，但不会填充其余的列大小：

Be careful when changing the ANSI_PADDING setting and create new column, as that setting will not be changed for that column until you drop it and create it again.

更改ANSI_PADDING设置并创建新列时要小心，因为在删除该列并再次创建之前，该设置不会更改。

设置ANSI_WARNINGS (SET ANSI_WARNINGS)

The ANSI_WARNING controls the ISO standard behavior of the SQL Server Database Engine for several error conditions. The T-SQL syntax that is used to control the ANSI_ WARNING option is shown below:

对于几种错误情况，ANSI_WARNING控制SQL Server数据库引擎的ISO标准行为。下面显示了用于控制ANSI_ WARNING选项的T-SQL语法：

SET ANSI_WARNINGS { ON | OFF }

SET ANSI_WARNINGS {开| 关闭}

Setting the ANSI_WARNING option to ON, the SQL Server Database Engine will follow the ISO standard in:

将ANSI_WARNING选项设置为ON，SQL Server数据库引擎将在以下方面遵循ISO标准：

A warning message is generated when null values appear in aggregate functions, such as SUM, AVG, MAX, MIN or COUNT.
当汇总函数（例如SUM，AVG，MAX，MIN或COUNT）中出现空值时，将生成警告消息。

The T-SQL statement is rolled back and error message is generated when the divide-by-zero and arithmetic overflow errors detected.
当检测到除以零和算术溢出错误时，将回滚T-SQL语句并生成错误消息。

The INSERT or UPDATE T-SQL statement is canceled and an error message is generated if the length of a new value specified in that statement for the string column exceeds the maximum size of the column.
如果在该语句中为字符串列指定的新值的长度超过该列的最大大小，则INSERT或UPDATE T-SQL语句将被取消并生成错误消息。

Setting the ANSI_WARNING option to OFF, the SQL Server Database Engine will follow a non-standard behavior, in which:

将ANSI_WARNING选项设置为OFF，SQL Server数据库引擎将遵循非标准行为，其中：

No warning is issued when null values appear in aggregate functions, such as SUM, AVG, MAX, MIN or COUNT.
当空值出现在聚合函数（例如SUM，AVG，MAX，MIN或COUNT）中时，不会发出警告。

A Warning message is generated when the divide-by-zero and arithmetic overflow errors detected and NULL values will be returned.
当检测到除以零和算术溢出错误并且将返回NULL值时，将生成警告消息。

The INSERT or UPDATE T-SQL statement will succeed if the length of a new value specified in that statement for the string column exceeds the maximum size of the column, and the data inserted will be truncated to the size of that column.
如果在该语句中为字符串列指定的新值的长度超过该列的最大大小，则INSERT或UPDATE T-SQL语句将成功执行，并且插入的数据将被截断为该列的大小。

Take into consideration that, for executing the distributed queries or creating or changing indexes on computed columns or indexed views, the SET ANSI_ WARNINGS 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 ANSI_ WARNINGS setting will be set at run time and not at parse time.

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

Let us see how the ANSI_ WARNINGS setting work. We will create a simple table and insert three records into that table:

让我们看看ANSI_ WARNINGS设置如何工作。我们将创建一个简单的表并将三个记录插入到该表中：

 CREATE TABLE ANSI_WARNINGS_Test (   ID INT IDENTITY(1,1),   Num INT NULL,   EmpName VARCHAR(15))GO INSERT INTO ANSI_WARNINGS_Test  VALUES (NULL, NULL), (1,NULL),(2,NULL)

The first case that we will check is the NULL values that appear in the aggregate functions, which is the SUM function in our demo below. If we try to get the SUM of the NUM column values, with the ANSI_ WARNINGS set to ON:

我们将检查的第一种情况是聚合函数中出现的NULL值，这是下面的演示中的SUM函数。如果我们尝试获取NUM列值的SUM，并将ANSI_ WARNINGS设置为ON：

 SET ANSI_WARNINGS ON;GOSELECT ID,SUM(Num) AS TotalIDsFROM ANSI_WARNINGS_Test GROUP BY IDGO

The query will succeed, but a warning message will be displayed, showing that the detected NULL values will be eliminated from the aggregate function result as shown below:

查询将成功，但是将显示警告消息，表明将从聚合函数结果中消除检测到的NULL值，如下所示：

Setting the ANSI_ WARNINGS option to OFF and executing the same query again:

将ANSI_ WARNINGS选项设置为OFF并再次执行相同的查询：

 SET ANSI_WARNINGS OFF;GOSELECT ID,SUM(Num) AS TotalIDsFROM ANSI_WARNINGS_Test GROUP BY IDGO

The query will succeed and no warning message will be displayed:

查询将成功，并且不会显示警告消息：

Now we will test how the SQL Server Database Engine will act when inserting a long string to a column, exceeding the column’s defined size. If we try to insert the below long string to the EmpName with size 15, with the ANSI_WARNINGS option set to ON in the first case:

现在，我们将测试在将长字符串插入超出列的定义大小的列时，SQL Server数据库引擎将如何操作。如果我们尝试将下面的长字符串插入大小为15的EmpName中，并且在第一种情况下将ANSI_WARNINGS选项设置为ON：

 SET ANSI_WARNINGS ON;INSERT INTO ANSI_WARNINGS_TestVALUES (5, 'Hi From SQL Shack Site Where You Can Always Learn New');GO

The INSERT statement will fail and an error message will be displayed, showing that the string should be truncated as it exceeds the column size:

INSERT语句将失败，并且将显示一条错误消息，显示该字符串超过列大小时应被截断：

Trying to insert the same long string to the EmpName column, with the ANSI_WARNINGS option set to OFF this time:

尝试将相同的长字符串插入EmpName列，此时ANSI_WARNINGS选项设置为OFF：

 SET ANSI_WARNINGS OFF;INSERT INTO ANSI_WARNINGS_TestVALUES (6, 'Hi From SQL Shack Site Where You Can Always Learn New');GO

The insert statement will succeed with no error message displayed:

insert语句将成功，并且不会显示错误消息：

What happened to the inserted long string? Executing the below SELECT statement that retrieve the inserted record:

插入的长字符串发生了什么？执行以下SELECT语句以检索插入的记录：

 SELECT * FROM ANSI_WARNINGS_Test WHERE Num=6

You will see that the insert succeeded but the string will be truncated automatically to the column size. Which means that only 15 characters of the inserted string will be saved to the column as shown below:

您会看到插入成功，但是字符串将被自动截断为列大小。这意味着插入的字符串中只有15个字符将被保存到该列，如下所示：

The last test for the ANSI_WARNINGS option is the divide by zero case. We will drop the existing table and create new one with three decimal columns, and insert three new records to that table:

ANSI_WARNINGS选项的最后一个测试是被零除的情况。我们将删除现有表，并创建一个包含三个小数列的新表，并在该表中插入三个新记录：

 DROP TABLE ANSI_WARNINGS_TestCREATE TABLE ANSI_WARNINGS_Test (   ID DECIMAL (5,2),   Num DECIMAL (5,2),   DivRest DECIMAL (5,2),)GO INSERT INTO ANSI_WARNINGS_Test  VALUES (1, 0,NULL),(2, 1,NULL),(2, 2,NULL)

Then we will update that table to fill the DivRest column with the result generated by dividing the ID column by the Num column value, after setting the ANSI_WARNINGS option to ON in the first case, with the ARITHABORT option set to OFF in all cases, that will be described in details later within this article:

然后，在第一种情况下将ANSI_WARNINGS选项设置为ON，并且在所有情况下将ARITHABORT选项设置为OFF后，我们将更新该表以将ID列除以Num列值生成的结果填充到DivRest列中，稍后将在本文中详细描述：

 SET ARITHABORT OFF GOSET ANSI_WARNINGS ONGOUPDATE ANSI_WARNINGS_TestSET DivRest= ID / NUM ;GOSELECT * FROM ANSI_WARNINGS_TestGO

An error message will be displayed indicating that you are trying to divide by zero, and the update statement will be rolled back as shown below:

将显示一条错误消息，指示您试图除以零，并且更新语句将回滚，如下所示：

The select statement result will show that the update statement fail with no change performed on the DivRest column:

select语句结果将显示update语句失败，并且对DivRest列未执行任何更改：

Setting the ANSI_WARNINGS option to OFF, and execute the same UPDATE statement:

将ANSI_WARNINGS选项设置为OFF，并执行相同的UPDATE语句：

 SET ARITHABORT OFF GOSET ANSI_WARNINGS OFFGOUPDATE ANSI_WARNINGS_TestSET DivRest= ID / NUM ;GOSELECT * FROM ANSI_WARNINGS_TestGO

The update statement will succeed and a warning message will be displayed showing that division by zero is caught:

更新语句将成功执行，并且将显示警告消息，显示捕获到零除：

Moreover, the select statement will show that the DivRest column is updated for all values except for the one with divide by zero issue, which returns NULL value:

此外，select语句将显示DivRest列已更新为所有值，但除以零的问题除外，该值将返回NULL值：

SET ARITHABORT (SET ARITHABORT)

The ARITHABORT option terminates the query when an overflow or divide-by-zero error occurs during the execution of the query. The T-SQL syntax that is used to control the ARITHABORT option is shown below:

如果在执行查询期间发生溢出或被零除错误，则ARITHABORT选项将终止查询。下面显示了用于控制ARITHABORT选项的T-SQL语法：

SET ARITHABORT { ON | OFF }

SET ARITHABORT {开| 关闭}

If you set the ARITHABORT option to ON and the ANSI WARNINGS is set to ON, the query will terminate and error messages will be generated.

如果将ARITHABORT选项设置为ON并将ANSI WARNINGS设置为ON，则查询将终止并生成错误消息。

If you set the ARITHABORT option to ON and the ARITHABORT is set to OFF, all the batch will terminate.

如果将ARITHABORT选项设置为ON并将ARITHABORT设置为OFF，则所有批处理都将终止。

Setting the ARITHABORT option to OFF and overflow or divide-by-zero error occurs, a warning message is displayed, and NULL will be returned to the result of the arithmetic operation. If the target column does not allow NULL, the insert or update action on that column fails and the user will receive an error.

将ARITHABORT选项设置为OFF会发生溢出或除零错误，显示警告消息，并且算术运算的结果将返回NULL。如果目标列不允许NULL，则对该列的插入或更新操作将失败，并且用户将收到错误。

The ARITHABORT option should be set to ON when you are creating or changing indexes on computed columns or indexed views. 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 ARITHABORT setting will be set at run time and not at parse time.

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

Let us see how the ARITHABORT setting work. We will create a simple table and insert three records into that table after setting the ARITHABORT option to ON:

让我们看看ARITHABORT设置是如何工作的。将ARITHABORT选项设置为ON后，我们将创建一个简单的表并将三个记录插入到该表中：

 CREATE TABLE ARITHABORT_Test ( Num1 TINYINT,  Num2 TINYINT) SET ARITHABORT ON GOINSERT INTO ARITHABORT_Test VALUES (1,0),(2,10),(3,512)

The insert statement will fail, showing that you are inserting a value of 512 that exceeds that TINYINT column limit:

insert语句将失败，表明您要插入的值超过TINYINT列限制的512：

Trying the same INSERT statement after setting the ARITHABORT option to OFF:

将ARITHABORT选项设置为OFF后，尝试相同的INSERT语句：

 SET ARITHABORT OFFGOINSERT INTO ARITHABORT_Test VALUES (1,0),(2,10),(3,512)

A warning message will be displayed showing that one of the values, to be inserted, exceeds the column limit, but the INSERT statement succeeds:

将显示一条警告消息，显示要插入的值之一超出列数限制，但INSERT语句成功：

Retrieving the inserted values:

检索插入的值：

 SELECT * FROM ARITHABORT_Test

The result will show that, all values will be inserted successfully except for the one that exceeds the column limit, which will be replaced by NULL:

结果将显示，所有值将被成功插入，但超出列限制的值将被NULL取代：

If we try to perform a division operation that contains division by zero problem, after setting the ARITHABORT option to ON:

如果我们尝试执行包含零除问题的除法运算，请将ARITHABORT选项设置为ON后：

 SET ARITHABORT ONGOSELECT Num1/Num2 AS DivRes FROM ARITHABORT_Test

The statement will fail with error message showing that you are trying to divide by zero:

该语句将失败，并显示错误消息，提示您正在尝试除以零：

Trying the same division operation that contains division by zero problem, after setting the ARITHABORT option to OFF:

将ARITHABORT选项设置为OFF后，尝试进行包含零除问题的相同除法运算：

 SET ARITHABORT OFFGOSELECT Num1/Num2 AS DivRes FROM ARITHABORT_Test

The statement will succeed and the division by zero value will be returned as NULL as shown below:

该语句将成功执行，并且除以零的值将作为NULL返回，如下所示：

结论 (Conclusion)

Within this article, the first in a two-part series, we describe the first four SET options; SET ANSI_NULLS, SET ANSI_PADDING, SET ANSI_WARNINGS and SET ARITHABORT and show practically how setting these options ON and OFF affect the SQL Server Database Engine behavior and the query result.

在这篇由两部分组成的系列文章中的第一篇中，我们描述了前四个SET选项。 SET ANSI_NULLS，SET ANSI_PADDING，SET ANSI_WARNINGS和SET ARITHABORT，并实际显示将这些选项设置为ON和OFF如何影响SQL Server数据库引擎的行为和查询结果。

In the of this series, we will go through the five rest options in the same detailed way with clear practical demos.

在本系列的中，我们将以相同的详细方式，通过清晰的实际演示，来介绍五个rest选项。