使用 SQL Server 时需要经常用到的几个设置选项！

答案：
1. SET DEADLOCK_PRIORITY

说明：控制在发生死锁情况时会话的反应方式。如果两个进程都锁定数据，并且直到其它进程释放自己的锁时，每个进程才能释放自己的锁，即发生死锁情况。

语法：SET DEADLOCK_PRIORITY { LOW | NORMAL | @deadlock_var }

参数：LOW  指定当前会话为首选死锁牺牲品。Microsoft® SQL Server™ 自动回滚死锁牺牲品的事务，并给客户端应用程序返回 1205 号死锁错误信息。
　　　NORMAL  指定会话返回到默认的死锁处理方法。
　　　@deadlock_var 是指定死锁处理方法的字符变量。如果指定 LOW，则 @deadlock_var 为 3；如果指定 NORMAL，则 @deadlock_var 为 6。

注释：SET DEADLOCK_PRIORITY 的设置是在执行或运行时设置，而不是在分析时设置。

权限：SET DEADLOCK_PRIORITY 权限默认授予所有用户。

 

2. SET LOCK_TIMEOUT

说明：指定语句等待锁释放的毫秒数。

语法：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
　　　GO

 

3. @@LOCK_TIMEOUT

说明：返回当前会话的当前锁超时设置，单位为毫秒。

语法：@@LOCK_TIMEOUT

返回类型：integer

注释：SET LOCK_TIMEOUT 允许应用程序设置语句等待阻塞资源的最长时间。当一条语句已等待超过 LOCK_TIMEOUT 所设置的时间，则被锁住的语句将自动取消，并给应用程序返回一条错误信息。
　　　在一个连接的开始，@@LOCK_TIMEOUT 返回一个 –1值。

示例：下面的示例显示当一个 LOCK_TIMEOUT 值未被设置时的结果集。

　　　SELECT @@LOCK_TIMEOUT
　　　下面是结果集：
　　　----------------
　　　-1

　　　下面的示例设置 LOCK_TIMEOUT 为 1800 毫秒，然后调用 @@LOCK_TIMEOUT。

　　　SET LOCK_TIMEOUT 1800
　　　SELECT @@LOCK_TIMEOUT

　　　下面是结果集：
　　　------------------------------
　　　1800

 

4. SET IDENTITY_INSERT

说明：允许将显式值插入表的标识列中。

语法：SET IDENTITY_INSERT [ database.[ owner.] ] { table } { ON | OFF }

参数：database 是指定的表所驻留的数据库名称。
　　　owner 是表所有者的名称。
　　　table 是含有标识列的表名。

注释：任何时候，会话中只有一个表的 IDENTITY_INSERT 属性可以设置为 ON。如果某个表已将此属性设置为 ON，并且为另一个表发出了 SET IDENTITY_INSERT ON 语句，则 Microsoft® SQL Server™ 返回一个错误信息，指出 SET IDENTITY_INSERT 已设置为 ON 并报告此属性已设置为 ON 的表。
　　　如果插入值大于表的当前标识值，则 SQL Server 自动将新插入值作为当前标识值使用。
　　　SET IDENTITY_INSERT 的设置是在执行或运行时设置，而不是在分析时设置。

权限：执行权限默认授予 sysadmin 固定服务器角色和 db_owner 及 db_ddladmin 固定数据库角色以及对象所有者。

示例：下例创建一个含有标识列的表，并显示如何使用 SET IDENTITY_INSERT 设置填充由 DELETE 语句导致的标识值中的空隙。

　　　-- Create products table.
　　　CREATE TABLE products (id int IDENTITY PRIMARY KEY, product varchar(40))
　　　GO
　　　-- Inserting values into products table.
　　　INSERT INTO products (product) VALUES ('screwdriver')
　　　INSERT INTO products (product) VALUES ('hammer')
　　　INSERT INTO products (product) VALUES ('saw')
　　　INSERT INTO products (product) VALUES ('shovel')
　　　GO

　　　-- Create a gap in the identity values.
　　　DELETE products 
　　　WHERE product = 'saw'
　　　GO

　　　SELECT * 
　　　FROM products
　　　GO

　　　-- Attempt to insert an explicit ID value of 3;
　　　-- should return a warning.
　　　INSERT INTO products (id, product) VALUES(3, 'garden shovel')
　　　GO

　　　-- SET IDENTITY_INSERT to ON.
　　　SET IDENTITY_INSERT products ON
　　　GO

　　　-- Attempt to insert an explicit ID value of 3
　　　INSERT INTO products (id, product) VALUES(3, 'garden shovel')
　　　GO

　　　SELECT * 
　　　FROM products
　　　GO

　　　-- Drop products table.
　　　DROP TABLE products
　　　GO

 

5. SET IMPLICIT_TRANSACTIONS

说明：为连接设置隐性事务模式。

语法：SET IMPLICIT_TRANSACTIONS { ON | OFF }

注释：当设置为 ON 时，SET IMPLICIT_TRANSACTIONS 将连接设置为隐性事务模式。当设置为 OFF 时，则使连接返回到自动提交事务模式。
　　　当连接是隐性事务模式且当前不在事务中时，执行下列语句将启动事务：

　　　ALTER TABLE FETCH REVOKE 
　　　CREATE  GRANT SELECT 
　　　DELETE INSERT TRUNCATE TABLE 
　　　DROP OPEN UPDATE 

　　　如果连接已经在打开的事务中，则上述语句不启动新事务。
　　　对于因为该设置为 ON 而自动打开的事务，用户必须在该事务结束时将其显式提交或回滚。否则当用户断开连接时，事务及其所包含的所有数据更改将回滚。在事务提交后，执行上述任一语句即可启动新事务。
　　　隐性事务模式将保持有效，直到连接执行 SET IMPLICIT_TRANSACTIONS OFF 语句使连接返回到自动提交模式。在自动提交模式下，如果各个语句成功完成则提交。
　　　在进行连接时，SQL Server ODBC 驱动程序和用于 SQL Server 的 Microsoft OLE DB 提供程序自动将 IMPLICIT_TRANSACTIONS 设置为 OFF。对来自 DB-Library 应用程序的连接，SET IMPLICIT_TRANSACTIONS 默认为 OFF。
　　　当 SET ANSI_DEFAULTS 为 ON 时，将启用 SET IMPLICIT_TRANSACTIONS。
　　　SET IMPLICIT_TRANSACTIONS 的设置是在执行或运行时设置，而不是在分析时设置。

示例：下例演示在将 IMPLICIT_TRANSACTIONS 设置为 ON 时显式或隐式启动事务。它使用 @@TRANCOUNT 函数演示打开的事务和关闭的事务。

　　　USE pubs
　　　GO

　　　CREATE table t1 (a int)
　　　GO
　　　INSERT INTO t1 VALUES (1)
　　　GO

　　　PRINT 'Use explicit transaction'
　　　BEGIN TRAN
　　　INSERT INTO t1 VALUES (2)
　　　SELECT 'Tran count in transaction'= @@TRANCOUNT
　　　COMMIT TRAN
　　　SELECT 'Tran count outside transaction'= @@TRANCOUNT
　　　GO

　　　PRINT 'Setting IMPLICIT_TRANSACTIONS ON'
　　　GO
　　　SET IMPLICIT_TRANSACTIONS ON
　　　GO

　　　PRINT 'Use implicit transactions'
　　　GO
　　　-- No BEGIN TRAN needed here.
　　　INSERT INTO t1 VALUES (4)
　　　SELECT 'Tran count in transaction'= @@TRANCOUNT
　　　COMMIT TRAN
　　　SELECT 'Tran count outside transaction'= @@TRANCOUNT
　　　GO

　　　PRINT 'Use explicit transactions with IMPLICIT_TRANSACTIONS ON'
　　　GO
　　　BEGIN TRAN
　　　INSERT INTO t1 VALUES (5)
　　　SELECT 'Tran count in transaction'= @@TRANCOUNT
　　　COMMIT TRAN
　　　SELECT 'Tran count outside transaction'= @@TRANCOUNT
　　　GO

　　　SELECT * FROM t1
　　　GO

　　　-- Need to commit this tran too!
　　　DROP TABLE t1
　　　COMMIT TRAN
　　　GO

 

6. SET NOCOUNT
说明：使返回的结果中不包含有关受 Transact-SQL 语句影响的行数的信息。

语法：SET NOCOUNT { ON | OFF } 

注释：当 SET NOCOUNT 为 ON 时，不返回计数（表示受 Transact-SQL 语句影响的行数）。当 SET NOCOUNT 为 OFF 时，返回计数。
　　　即使当 SET NOCOUNT 为 ON 时，也更新 @@ROWCOUNT 函数。
　　　当 SET NOCOUNT 为 ON 时，将不给客户端发送存储过程中的每个语句的 DONE_IN_PROC 信息。当使用 Microsoft® SQL Server™ 提供的实用工具执行查询时，在 Transact-SQL 语句（如 SELECT、INSERT、UPDATE 和 DELETE）结束时将不会在查询结果中显示"nn rows affected"。
　　　如果存储过程中包含的一些语句并不返回许多实际的数据，则该设置由于大量减少了网络流量，因此可显著提高性能。
　　　SET NOCOUNT 设置是在执行或运行时设置，而不是在分析时设置。

权限：SET NOCOUNT 权限默认授予所有用户。

示例：下例在 osql 实用工具或 SQL Server 查询分析器中执行时，可防止显示有关受影响的行数的信

上一个：自建数据库首部曲常用参数功能汇总
下一个：数据库概论笔记（第三天）