T-SQL中常用的set语句

1、SET ANSI_DEFAULTS { ON | OFF }
示例：

SET ANSI_DEFAULTS ON;  DBCC USEROPTIONS;  SET ANSI_DEFAULTS OFF;  

设置 SET ANSI_DEFAULTS ON 并使用 DBCC USEROPTIONS 语句显示受影响的设置。

2、SET ANSI_NULLS { ON | OFF }
T-SQL 支持在与空值进行比较时，允许比较运算符返回 TRUE 或 FALSE。
ANSI_NULLS 设置不影响包含 NULL 的联接列。联接列中包含 NULL 的行不是结果集的一部分。
当 SET ANSI_NULLS 为 ON 时，即使 column_name 中包含空值，使用 WHERE column_name = NULL 的 SELECT 语句仍返回零行。 即使 column_name 中包含非空值，使用 WHERE column_name <> NULL 的 SELECT 语句仍会返回零行。

当 SET ANSI_NULLS 为 OFF 时，等于 (=) 和不等于 (<>) 比较运算符不遵守 ISO 标准。 使用 WHERE column_name = NULL 的 SELECT 语句返回 column_name 中包含空值的行。 使用 WHERE column_name <> NULL 的 SELECT 语句返回列中包含非空值的行。 此外，使用 WHERE column_name <> XYZ_value 的 SELECT 语句返回所有不为 XYZ_value 也不为 NULL 的行。

也就是说，设置为on是你只能这样写：where xx is null 或者 where xx is not null，而设置为off时可以写成这样：where xx = null 或者 where xx <> null
如果 SET ANSI_NULLS ON 那么 SQL 语句中， 判断 非空的写法， 要修改为：WHERE 字段 IS NULL。SET ANSI_NULLS ON 以后， NULL = NULL 将 返回 false.
3、SET ANSI_PADDING { ON | OFF }
当设置为ON时，不剪裁字符值中插入到varchar列的尾随空格和二进制值中插入到varbinary列的尾随零。不将值按列的长度进行填充。当设置为OFF时，剪裁varchar列的尾随空格和varbinary列的尾随零。该设置只影响新列的定义。
ANSI_PADDING为ON时，将允许空值的Char(n)和binary(n)列填充到列长；而当ANSI_PADDING为OFF时，将剪裁尾随空格和零，始终将不允许空值的Char(n)和binary(n)列填充到列长。
4、SET ANSI_WARNINGS { ON | OFF }
ANSI_WARNINGS 可以影响下列情况：
设置为 ON 时，如果聚合函数（如 SUM、AVG、MAX、MIN、STDEV、STDEVP、VAR、VARP 或 COUNT）中出现空值，将生成警告消息。 设置为 OFF 时，不发出警告。
设置为 ON 时，被零除错误和算术溢出错误将导致回滚语句，并生成错误消息。 设置为 OFF 时，被零除错误和算术溢出错误将导致返回空值。 如果在 character、Unicode 或 binary 列上尝试执行 INSERT 或 UPDATE 操作，而这些列中的新值长度超出最大列大小，则将出现被零除错误和算术溢出错误导致返回空值的行为。
如果 SET ANSI_WARNINGS 为 ON，则根据 ISO 标准，将取消 INSERT 或 UPDATE 操作。 字符列的尾随空格和二进制列的尾随零都将被忽略。 设置为 OFF 时，数据将剪裁为列的大小，并且语句执行成功。
5、SET ARITHABORT { ON | OFF }
在查询执行过程中发生溢出或被零除错误时终止查询。
如果 SET ARITHABORT 为 ON，则这些错误情况将导致查询或批处理终止。如果在事务内发生错误，则回滚事务。如果 SET ARITHABORT 为 OFF 并且发生了这些错误之一，则显示一条警告消息，并给算术运算的结果赋以空值。
说明 如果 SET ARITHABORT 和 SET ARITHIGNORE 都没有设置，则 Microsoft® SQL Server™ 将在执行查询后返回 NULL 和一条警告信息。
如果 SET ARITHABORT 为 OFF 并且在对表达式的求值过程中 INSERT、DELETE 或 UPDATE 语句遇到算术错误（溢出、被零除或域错误），SQL Server 将插入或更新 NULL 值。如果目标列不可为空，则插入或更新操作失败并且用户收到错误信息。
如果 SET ARITHABORT 或 SET ARITHIGNORE 为 OFF，并且 SET ANSI_WARNINGS 为 ON，则当遇到被零除或溢出错误时，SQL Server 仍返回错误信息。
SET ARITHABORT 的设置是在执行或运行时设置，而不是在分析时设置。
在计算列或索引视图上创建或操作索引时，SET ARITHABORT 必须为 ON。如果 SET ARITHABORT 为 OFF，计算列或索引视图上带索引的表上的 CREATE、UPDATE、INSERT 和 DELETE 语句将失败。有关计算列上的索引视图和索引所必需的 SET 选项设置的更多信息，请参见 SET 中的"使用 SET 语句时的注意事项"。
权限:
SET ARITHABORT 权限默认授予所有用户。
6、SET ARITHIGNORE { ON | OFF }
控制在查询执行过程中是否从溢出或被零除错误返回错误信息。
SET ARITHIGNORE 设置仅控制是否返回错误信息。不论该设置是什么，Microsoft® SQL Server™ 都将在包含溢出或被零除错误的计算中返回 NULL。可以使用 SET ARITHABORT 设置确定是否终止查询。该设置不影响在 INSERT、UPDATE 和 DELETE 语句执行过程中发生的错误。
如果 SET ARITHABORT 或 SET ARITHIGNORE 为 OFF，并且 SET ANSI_WARNINGS 为 ON，则当遇到被零除或溢出错误时，SQL Server 仍返回错误信息。
SET ARITHIGNORE 的设置是在执行或运行时设置，而不是在分析时设置。
权限
SET ARITHIGNORE 权限默认授予所有用户。
7、SET CONCAT_NULL_YIELDS_NULL { ON | OFF }
将串联结果视为 Null 还是空字符串值。
當是NULL或空字符串時控制是否連接字符串值。
例如：
将 SET 选项 CONCAT_NULL_YIELDS_NULL 设置为 ON 之后，表达式 'abc' + NULL 返回的值是 NULL。
而将 CONCAT_NULL_YIEDS_NULL 设置为 OFF 之后，该表达式得出的结果却是 'abc'。
8、SET CURSOR_CLOSE_ON_COMMIT { ON | OFF }
表示提交事务时服务器将不会关闭游标。
When SET CURSOR_CLOSE_ON_COMMIT is ON, this setting closes any open cursors on commit or rollback in compliance with ISO.如果 SET CURSOR_CLOSE_ON_COMMIT 为 ON，此设置将遵从 ISO 标准，在提交或回滚时关闭所有打开的游标。 如果 SET CURSOR_CLOSE_ON_COMMIT 为 OFF，则在提交事务时将不关闭游标。
9、SET DATEFIRST { number | @number_var }
number | @number_var 是一个整数，表示一周的第一天，可以是下列值中的一个：

注释：
使用 @@DATEFIRST 函数检查 SET DATEFIRST 的当前设置。
SET DATEFIRST 的设置是在执行或运行时设置，而不是在分析时设置。
权限:SET DATEFIRST 权限默认授予所有用户。
10、SET DEADLOCK_PRIORITY { LOW | NORMAL | HIGH | | @deadlock_var | @deadlock_intvar }
::= { -10 | -9 | -8 | … | 0 | … | 8 | 9 | 10 }
控制在发生死锁情况时会话的反应方式。如果两个进程都锁定数据，并且直到其它进程释放自己的锁时，每个进程才能释放自己的锁，即发生死锁情况。
参数：
LOW
指定当前会话为首选死锁牺牲品。Microsoft® SQL Server™ 自动回滚死锁牺牲品的事务，并给客户端应用程序返回 1205 号死锁错误信息。NORMAL
指定会话返回到默认的死锁处理方法。
@deadlock_var
br/>NORMAL
指定会话返回到默认的死锁处理方法。
@deadlock_var

注释：SET DEADLOCK_PRIORITY 的设置是在执行或运行时设置，而不是在分析时设置。
权限：SET DEADLOCK_PRIORITY 权限默认授予所有用户。
11、SET LOCK_TIMEOUT timeout_period
指定语句等待锁释放的毫秒数。
参数：
timeout_period
是在 Microsoft® SQL Server™ 返回锁定错误前经过的毫秒数。值为 -1（默认值）时表示没有超时期限（即无限期等待）。
当锁等待超过超时值时，将返回错误。值为 0 时表示根本不等待，并且一遇到锁就返回信息。
注释：
在连接开始时，该设置的值为 -1。设置更改后，新设置在其余的连接时间里一直有效。
SET LOCK_TIMEOUT 的设置是在执行或运行时设置，而不是在分析时设置。
READPAST 锁定提示为该 SET 选项提供了另一种方式。
权限:SET LOCK_TIMEOUT 权限默认授予所有用户。
示例：
下例将锁超时期限设置为 1,800 毫秒。
SET LOCK_TIMEOUT 1800
12、SET NOCOUNT { ON | OFF }
阻止在结果集中返回显示受 Transact-SQL 语句或存储过程影响的行计数的消息。
注释：
当 SET NOCOUNT 为 ON 时，不返回计数。 当 SET NOCOUNT 为 OFF 时，返回计数。
The @@ROWCOUNT function is updated even when SET NOCOUNT is ON.@@ROWCOUNT函数将更新，即使在 SET NOCOUNT ON。
当 SET NOCOUNT 为 ON 时，将不向客户端发送存储过程中每个语句的 DONE_IN_PROC 消息。 如果存储过程中包含一些并不返回许多实际数据的语句，或者如果过程包含 Transact-SQL 循环，网络通信流量便会大量减少，因此，将 SET NOCOUNT 设置为 ON 可显著提高性能。
SET NOCOUNT 指定的设置是在执行或运行时生效，而不是在分析时生效。
13、SET NOEXEC { ON | OFF }
编译每个查询但不执行。
注释：
当 SET NOEXEC 为 ON 时， SQL Server 将编译每一批 Transact-SQL 语句但并不执行它们。 当 SET NOEXEC 设置为 OFF 时，所有批处理将在编译后执行。
SQL Server 中的语句执行包含两个阶段：编译和执行。 该设置可用于让 SQL Server 在执行 Transact-SQL 代码时，验证代码中的语法和对象名。 它也可以用于调试通常是较大的批处理中的部分语句。
SET NOEXEC 设置是在执行或运行时设置的，而不是在分析时设置的。
权限:要求具有 public 角色的成员身份。
14、SET PARSEONLY { ON | OFF }
检查每个 Transact-SQL 语句的语法并返回任何错误消息，但不编译和执行语句。
注释:
当 SET PARSEONLY 为 ON 时， SQL Server 只分析语句。 当 SET PARSEONLY 为 OFF 时， SQL Server 编译并执行语句。
SET PARSEONLY 的设置是在分析时设置，而不是在执行或运行时设置。
在存储过程或触发器中不要使用 PARSEONLY。 如果 OFFSETS 选项为 ON 而且没有出现错误，则 SET PARSEONLY 返回偏移量。
权限：Requires membership in the public role.要求 公共 角色具有成员身份。
15、SET SHOWPLAN_ALL { ON | OFF }
Causes Microsoft SQL ServerSQL Server not to execute Transact-SQLTransact-SQL statements.使 Microsoft SQL ServerSQL Server 不执行 Transact-SQL 语句。 SQL Server 返回有关语句执行情况的详细信息，并估计语句对资源的需求。
注释：
SET SHOWPLAN_ALL 的设置是在执行或运行时设置，而不是在分析时设置。
如果 SET SHOWPLAN_ALL 为 ON，则 SQL Server 将返回每个语句的执行信息但不执行语句。 Transact-SQL 语句不会被执行。 在将此选项设置为 ON 后，将始终返回有关所有后续 Transact-SQL 语句的信息，直到将该选项设置为 OFF 为止。 例如，如果在 SET SHOWPLAN_ALL 为 ON 时执行 CREATE TABLE 语句，则 SQL Server 将从涉及同一个表的后续 SELECT 语句返回错误信息，告知用户指定的表不存在。 因此，对此表的后续引用将失败。 如果 SET SHOWPLAN_ALL 为 OFF，则 SQL Server 将执行语句，但不生成报表。
SET SHOWPLAN_ALL 是供为处理其输出而编写的应用程序使用的。 使用 SET SHOWPLAN_TEXT 返回对于 Microsoft Win32 命令提示符下应用程序，可读的输出，例如osql实用程序。
不能在存储过程内指定 SET SHOWPLAN_TEXT 和 SET SHOWPLAN_ALL，它们必须是批处理中的唯一语句。
16、SET SHOWPLAN_TEXT { ON | OFF }
Causes Microsoft SQL ServerSQL Server not to execute Transact-SQLTransact-SQL statements.使 Microsoft SQL Server 不执行 Transact-SQL 语句。 而是由 SQL Server 返回有关如何执行语句的详细信息。
注释：
SET SHOWPLAN_TEXT 的设置是在执行或运行时设置的，而不是在分析时设置的。
When SET SHOWPLAN_TEXT is ON, SQL ServerSQL Server returns execution information for each Transact-SQLTransact-SQL statement without executing it.当 SET SHOWPLAN_TEXT 为 ON 时， SQL ServerSQL Server 将返回每个 Transact-SQL 语句的执行信息，但不执行语句。 将该选项设置为 ON 以后，将返回有关所有后续 SQL Server 语句的执行计划信息，直到将该选项设置为 OFF 为止。 例如，如果在 SET SHOWPLAN_TEXT 为 ON 时执行 CREATE TABLE 语句， SQL Server 将从涉及同一个表的后续 SELECT 语句返回错误信息，以便通知用户：指定的表不存在。 因此，对此表的后续引用将失败。 如果 SET SHOWPLAN_TEXT 是 OFF， SQL Server 将执行语句，但不生成包含执行计划信息的报表。
设置 set SHOWPLAN_TEXT 旨在返回 Microsoft Win32 命令提示符下应用程序的可读的输出，例如osql实用程序。 SET SHOWPLAN_ALL 则返回更详细的输出，以使专门处理其输出的程序进行处理。
17、SET STATISTICS IO { ON | OFF }
注释：
如果 STATISTICS IO 为 ON，则显示统计信息。 如果为 OFF，则不显示统计信息。
After this option is set ON, all subsequent Transact-SQLTransact-SQL statements return the statistical information until the option is set to OFF.如果将此选项设置为 ON，则所有后续的 Transact-SQL 语句将返回统计信息，直到将该选项设置为 OFF 为止。
18、SET STATISTICS TIME { ON | OFF }
显示分析、编译和执行各语句所需的毫秒数。
注释：
当 SET STATISTICS TIME 为 ON 时，会显示语句的时间统计信息。 为 OFF 时，不显示时间统计信息。
SET STATISTICS TIME 的设置是在执行或运行时设置，而不是在分析时设置。
Microsoft SQL Server无法提供精确的统计信息在纤程模式下，当你启用激活轻型池配置选项。
Cpu中的列sysprocesses查询执行使用 SET STATISTICS TIME ON 时，才会更新表。 OFF，SET STATISTICS TIME 时0返回。
ON 和 OFF 设置还影响 SQL Server Management Studio 内的"当前活动的进程信息视图"中的 CPU 列。
19、SET XACT_ABORT { ON | OFF }
注释：
当 SET XACT_ABORT 为 ON 时，如果执行 Transact-SQL 语句产生运行时错误，则整个事务将终止并回滚。
当 SET XACT_ABORT 为 OFF 时，有时只回滚产生错误的 Transact-SQL 语句，而事务将继续进行处理。 如果错误很严重，那么即使 SET XACT_ABORT 为 OFF，也可能回滚整个事务。 OFF 是默认设置。
编译错误（如语法错误）不受 SET XACT_ABORT 的影响。
对于大多数 OLE DB 访问接口（包括 SQL Server），必须将隐式或显示事务中的数据修改语句中的 XACT_ABORT 设置为 ON。 唯一不需要该选项的情况是在提供程序支持嵌套事务时。
当 ANSI_WARNINGS=OFF 时，违反权限的行为导致事务中止。
SET XACT_ABORT 的设置是在执行或运行时设置，而不是在分析时设置。
要查看此设置的当前设置，请运行以下查询。

DECLARE @XACT_ABORT VARCHAR(3) = 'OFF';  IF ( (16384 & @@OPTIONS) = 16384 ) SET @XACT_ABORT = 'ON';  SELECT @XACT_ABORT AS XACT_ABORT;