正文

SQL Server选项综述

IP云
IP云V管理员/6 阅读
08/08
提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

I. 基本概念

SQL Server中的选项根据其作用范围分为如下几类：

  • 实例选项 —— 在数据库实例范围内有效，通过 sp_configure 存储过程进行配置。
  • 数据库选项 —— 在数据库范围内有效，通过 ALTER DATABASE SET 语句进行配置。
  • 批处理选项 —— 批处理选项又称SET选项，其有效域需根据具体情况确定，它通过SET语句来修改。批处理选项在会话建立时根据用户选项或连接选项初始化。其中，用户选项在用户登录时通过实例选项中的“user options” 选项获得；连接选项通过 ODBC 或 OLE DB 连接属性获得。
  • 语句选项 —— 查询语句中通过提示关键字（如查询提示，表提示，联接提示等）设置的选项或策略。详见 提示 (Transact-SQL)。

II. 选项作用层次结构

当某一选项在多个级别受到支持时，将按如下层次发生作用：

1. 数据库选项替代实例选项。

2. 批处理（SET）选项代替数据库选项。

3. 语句选项（提示）代替批处理选项。

III. 各类选项的配置及查看

1. 实例选项

1）查看实例选项

SELECT * FROM sys.configurations ORDER BY name
--或
sp_configure

可用的实例选项及定义详见：http://technet.microsoft.com/zh-cn/library/ms189631(v=sql.105).aspx

2）设置实例选项

sp_configure 'fill factor', 100;
GO
RECONFIGURE;
GO

注意：在通过sp_configure配置实例选项后，此选项不会立即生效，除非重启服务或执行RECONFIGURE语句。

详见：http://technet.microsoft.com/zh-cn/library/ms188787(v=sql.105).aspx

2. 数据库选项

1）查看数据库选项

SELECT is_ansi_nulls_on FROM sys.databases WHERE name = 'DBName'
--或
SELECT * FROM sys.databases WHERE name = 'DBName'

注意：上面SQL语句中'DBName'不要加'[]'，否则查询不出结果。

数据库选项的缺省值在model数据库中定义：http://technet.microsoft.com/zh-cn/library/ms186388(v=sql.105).aspx

可用的数据库选项及定义详见：http://technet.microsoft.com/zh-cn/library/ms190249(v=sql.105).aspx

2）设置数据库选项

ALTER DATABASE DBName SET RECOVERY FULL, PAGE_VERIFY CHECKSUM

详见：http://technet.microsoft.com/zh-cn/library/bb522682(v=sql.105).aspx

3. 批处理选项

1）查看批处理选项

批处理选项的具体取值由两部分因素决定，一是会话的初始值（由用户选项或连接选项决定），二是批处理语句所处的上下文及SET语句的使用情况。也就是说，如果上下文或批处理语句中都没有执行SET语句，则批处理选项的取值为会话的初始值，否则其取值由上下文及批处理中SET语句的设置值决定。关于上下文有如下情况：

  • 由用户设置的 SET 选项在会话存在期间有效。
  • 在存储过程或触发器内设置的 SET 选项在该存储过程或触发器语句块内有效。
  • 除非进行显式重置，否则来自所有更高级别的代码中的 SET 选项值在存储过程或触发器内有效。
  • 动态 SQL 批处理内由用户设置的 SET 选项仅在该批处理块内有效。
  • 除非进行重置，否则为连接设置的 SET 选项在连接到其他数据库之后有效。

对于批处理执行时的SET选项可用如下语句查看：

SELECT 'DISABLE_DEF_CNST_CHK', CASE WHEN (1 & @@OPTIONS) = 1 THEN 'ON' ELSE 'OFF' END
UNION
SELECT 'IMPLICIT_TRANSACTIONS', CASE WHEN (2 & @@OPTIONS) = 2 THEN 'ON' ELSE 'OFF' END
UNION
SELECT 'CURSOR_CLOSE_ON_COMMIT', CASE WHEN (4 & @@OPTIONS) = 4 THEN 'ON' ELSE 'OFF' END
UNION
SELECT 'ANSI_WARNINGS', CASE WHEN (8 & @@OPTIONS) = 8 THEN 'ON' ELSE 'OFF' END
UNION
SELECT 'ANSI_PADDING', CASE WHEN (16 & @@OPTIONS) = 16 THEN 'ON' ELSE 'OFF' END
UNION
SELECT 'ANSI_NULLS', CASE WHEN (32 & @@OPTIONS) = 32 THEN 'ON' ELSE 'OFF' END
UNION
SELECT 'ARITHABORT', CASE WHEN (64 & @@OPTIONS) = 64 THEN 'ON' ELSE 'OFF' END
UNION
SELECT 'ARITHIGNORE', CASE WHEN (128 & @@OPTIONS) = 128 THEN 'ON' ELSE 'OFF' END
UNION
SELECT 'QUOTED_IDENTIFIER', CASE WHEN (256 & @@OPTIONS) = 256 THEN 'ON' ELSE 'OFF' END
UNION
SELECT 'NOCOUNT', CASE WHEN (512 & @@OPTIONS) = 512 THEN 'ON' ELSE 'OFF' END
UNION
SELECT 'ANSI_NULL_DFLT_ON', CASE WHEN (1024 & @@OPTIONS) = 1024 THEN 'ON' ELSE 'OFF' END
UNION
SELECT 'ANSI_NULL_DFLT_OFF', CASE WHEN (2048 & @@OPTIONS) = 2048 THEN 'ON' ELSE 'OFF' END
UNION
SELECT 'CONCAT_NULL_YIELDS_NULL', CASE WHEN (4096 & @@OPTIONS) = 4096 THEN 'ON' ELSE 'OFF' END
UNION
SELECT 'NUMERIC_ROUNDABORT', CASE WHEN (8192 & @@OPTIONS) = 8192 THEN 'ON' ELSE 'OFF' END
UNION
SELECT 'XACT_ABORT', CASE WHEN (16384 & @@OPTIONS) = 16384 THEN 'ON' ELSE 'OFF' END

可用的SET选项及定义详见：http://technet.microsoft.com/zh-cn/library/ms190707(v=sql.105).aspx

也可通过如下语句查看用户选项缺省值：

DECLARE @UserOptions INT
SELECT @UserOptions=CONVERT(INT,value_in_use) FROM sys.configurations WHERE name='user options'SELECT 'DISABLE_DEF_CNST_CHK', CASE WHEN (1 & @UserOptions) = 1 THEN 'ON' ELSE 'OFF' END
UNION
SELECT 'IMPLICIT_TRANSACTIONS', CASE WHEN (2 & @UserOptions) = 2 THEN 'ON' ELSE 'OFF' END
UNION
SELECT 'CURSOR_CLOSE_ON_COMMIT', CASE WHEN (4 & @UserOptions) = 4 THEN 'ON' ELSE 'OFF' END
UNION
SELECT 'ANSI_WARNINGS', CASE WHEN (8 & @UserOptions) = 8 THEN 'ON' ELSE 'OFF' END
UNION
SELECT 'ANSI_PADDING', CASE WHEN (16 & @UserOptions) = 16 THEN 'ON' ELSE 'OFF' END
UNION
SELECT 'ANSI_NULLS', CASE WHEN (32 & @UserOptions) = 32 THEN 'ON' ELSE 'OFF' END
UNION
SELECT 'ARITHABORT', CASE WHEN (64 & @UserOptions) = 64 THEN 'ON' ELSE 'OFF' END
UNION
SELECT 'ARITHIGNORE', CASE WHEN (128 & @UserOptions) = 128 THEN 'ON' ELSE 'OFF' END
UNION
SELECT 'QUOTED_IDENTIFIER', CASE WHEN (256 & @UserOptions) = 256 THEN 'ON' ELSE 'OFF' END
UNION
SELECT 'NOCOUNT', CASE WHEN (512 & @UserOptions) = 512 THEN 'ON' ELSE 'OFF' END
UNION
SELECT 'ANSI_NULL_DFLT_ON', CASE WHEN (1024 & @UserOptions) = 1024 THEN 'ON' ELSE 'OFF' END
UNION
SELECT 'ANSI_NULL_DFLT_OFF', CASE WHEN (2048 & @UserOptions) = 2048 THEN 'ON' ELSE 'OFF' END
UNION
SELECT 'CONCAT_NULL_YIELDS_NULL', CASE WHEN (4096 & @UserOptions) = 4096 THEN 'ON' ELSE 'OFF' END
UNION
SELECT 'NUMERIC_ROUNDABORT', CASE WHEN (8192 & @UserOptions) = 8192 THEN 'ON' ELSE 'OFF' END
UNION
SELECT 'XACT_ABORT', CASE WHEN (16384 & @UserOptions) = 16384 THEN 'ON' ELSE 'OFF' END

可用的用户选项及定义详见：http://technet.microsoft.com/zh-cn/library/ms176031(v=sql.105).aspx

2）设置批处理（SET）选项

SET QUOTED_IDENTIFIER, ANSI_NULLS ON

详见：http://technet.microsoft.com/zh-cn/library/ms190356(v=sql.105).aspx

另外，Transact-SQL 提供 SET ANSI_DEFAULTS 语句作为设置下列 ISO 标准选项的快捷方式：

  • SET ANSI_NULLS
  • SET CURSOR_CLOSE_ON_COMMIT
  • SET ANSI_NULL_DFLT_ON
  • SET IMPLICIT_TRANSACTIONS
  • SET ANSI_PADDING
  • SET QUOTED_IDENTIFIER
  • SET ANSI_WARNINGS

详见：http://technet.microsoft.com/zh-cn/library/ms190707(v=sql.105).aspx

可通过如下语句设置用户选项缺省值：

sp_configure 'user options', 5496
GO
RECONFIGURE
GO

参考：
http://technet.microsoft.com/zh-cn/library/ms191203(v=sql.105).aspx
http://www.mssqltips.com/sqlservertip/1415/determining-set-options-for-a-current-session-in-sql-server/