SQL Server 常用设置选项

喜欢ヅ旅行 2022-06-14 06:12 377阅读 0赞

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 查询分析器中执行时，可防止显示有关受影响的行数的信息。  
USE pubs  
GO  
\-- Display the count message.  
SELECT au\_lname  
FROM authors  
GO  
USE pubs  
GO  
\-- SET NOCOUNT to ON and no longer display the count message.  
SET NOCOUNT ON  
GO  
SELECT au\_lname  
FROM authors  
GO  
\-- Reset SET NOCOUNT to OFF.  
SET NOCOUNT OFF  
GO  
　　7. @@ROWCOUNT  
　　说明：返回受上一语句影响的行数。  
　　语法：  
@@ROWCOUNT  
　　返回类型：integer  
　　注释：任何不返回行的语句将这一变量设置为 0 ，如 IF 语句。  
　　示例：下面的示例执行 UPDATE 语句并用 @@ROWCOUNT 来检测是否有发生更改的行。  
UPDATE authors SET au\_lname = 'Jones'  
WHERE au\_id = '999-888-7777'  
IF @@ROWCOUNT = 0  
print 'Warning: No rows were updated'  
　　8. SET ROWCOUNT  
　　说明：使 Microsoft® SQL Server™ 在返回指定的行数之后停止处理查询。  
　　语法：  
SET ROWCOUNT \{ number | @number\_var \}  
　　参数：number | @number\_var是在停止给定查询之前要处理的行数（整数）。  
　　注释：建议将当前使用 SET ROWCOUNT 的 DELETE、INSERT 和 UPDATE 语句重新编写为使用 TOP 语法。有关更多信息，请参见 DELETE、INSERT 或 UPDATE。  
　　对于在远程表和本地及远程分区视图上执行的 INSERT、UPDATE 和 DELETE 语句，忽略 SET ROWCOUNT 选项设置。  
　　若要关闭该选项（以便返回所有的行），请将 SET ROWCOUNT 指定为 0。  
　　说明 设置 SET ROWCOUNT 选项将使大多数 Transact-SQL 语句在已受指定数目的行影响后停止处理。这包括触发器和 INSERT、UPDATE 及 DELETE 等数据修改语句。ROWCOUNT 选项对动态游标无效，但限制键集的行集和不感知游标。使用该选项时应谨慎，它主要与 SELECT 语句一起使用。  
　　如果行数的值较小，则 SET ROWCOUNT 替代 SELECT 语句 TOP 关键字。  
　　SET ROWCOUNT 的设置是在执行或运行时设置，而不是在分析时设置。  
　　权限：SET ROWCOUNT 权限默认授予所有用户。  
　　示例：SET ROWCOUNT 在指定的行数后停止处理。在下例中，注意有 x 行满足预付款少于或等于 $5,000 的条件；但是，从更新所返回的行数中可以看出并非所有的行都得到处理。ROWCOUNT 影响所有的 Transact-SQL 语句。  
USE pubs  
GO  
SELECT count(\*) AS Cnt  
FROM titles  
WHERE advance >= 5000  
GO  
　　下面是结果集：Cnt  11 (1 row(s) affected)  
　　现在，将 ROWCOUNT 设置为 4，并更新预付款等于或大于 $5,000 的所有行。  
\-- SET ROWCOUNT to 4.  
SET ROWCOUNT 4  
GO  
UPDATE titles  
SET advance = 5000  
WHERE advance >= 5000  
GO  
　　9. SET TRANSACTION ISOLATION LEVEL  
　　说明：控制由连接发出的所有 Microsoft® SQL Server™ SELECT 语句的默认事务锁定行为。  
　　语法：  
SET TRANSACTION ISOLATION LEVEL  
\{ READ COMMITTED  
| READ UNCOMMITTED  
| REPEATABLE READ  
| SERIALIZABLE  
\}

参数：READ COMMITTED指定在读取数据时控制共享锁以避免脏读，但数据可在事务结束前更改，从而产生不可重复读取或幻像数据。该选项是 SQL Server 的默认值。  
　　READ UNCOMMITTED执行脏读或 0 级隔离锁定，这表示不发出共享锁，也不接受排它锁。当设置该选项时，可以对数据执行未提交读或脏读；在事务结束前可以更改数据内的数值，行也可以出现在数据集中或从数据集消失。该选项的作用与在事务内所有语句中的所有表上设置 NOLOCK 相同。这是四个隔离级别中限制最小的级别。  
　　REPEATABLE READ锁定查询中使用的所有数据以防止其他用户更新数据，但是其他用户可以将新的幻像行插入数据集，且幻像行包括在当前事务的后续读取中。因为并发低于默认隔离级别，所以应只在必要时才使用该选项。  
　　SERIALIZABLE在数据集上放置一个范围锁，以防止其他用户在事务完成之前更新数据集或将行插入数据集内。这是四个隔　离级别中限制最大的级别。因为并发级别较低，所以应只在必要时才使用该选项。该选项的作用与在事务内所有 SELECT 语句中的所有表上设置 HOLDLOCK 相同。  
　　注释：一次只能设置这些选项中的一个，而且设置的选项将一直对那个连接保持有效，直到显式更改该选项为止。这是默认行为，除非在语句的 FROM 子句中在表级上指定优化选项。  
　　SET TRANSACTION ISOLATION LEVEL 的设置是在执行或运行时设置，而不是在分析时设置。  
　　示例：下例为会话设置 TRANSACTION ISOLATION LEVEL。对于每个后续 Transact-SQL 语句，SQL Server 将所有共享锁一直控制到事务结束为止。  
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ  
GO  
BEGIN TRANSACTION  
SELECT \* FROM publishers  
SELECT \* FROM authors  
...  
COMMIT TRANSACTION

10. SET XACT\_ABORT  
　　说明：指定当 Transact-SQL 语句产生运行时错误时，Microsoft® SQL Server™ 是否自动回滚当前事务。  
　　语法：  
SET XACT\_ABORT \{ ON | OFF \}  
　　注释：当 SET XACT\_ABORT 为 ON 时，如果 Transact-SQL 语句产生运行时错误，整个事务将终止并回滚。为 OFF 时，只回滚产生错误的 Transact-SQL 语句，而事务将继续进行处理。编译错误（如语法错误）不受 SET XACT\_ABORT 的影响。  
　　对于大多数 OLE DB 提供程序（包括 SQL Server），隐性或显式事务中的数据修改语句必须将 XACT\_ABORT 设置为 ON。唯一不需要该选项的情况是提供程序支持嵌套事务时。有关更多信息，请参见分布式查询和分布式事务。  
　　SET XACT\_ABORT 的设置是在执行或运行时设置，而不是在分析时设置。  
　　示例：下例导致在含有其它 Transact-SQL 语句的事务中发生违反外键错误。在第一个语句集中产生错误，但其它语句均成功执行且事务成功提交。在第二个语句集中，SET XACT\_ABORT 设置为 ON。这导致语句错误使批处理终止，并使事务回滚。  
CREATE TABLE t1 (a int PRIMARY KEY)  
CREATE TABLE t2 (a int REFERENCES t1(a))  
GO  
INSERT INTO t1 VALUES (1)  
INSERT INTO t1 VALUES (3)  
INSERT INTO t1 VALUES (4)  
INSERT INTO t1 VALUES (6)  
GO  
SET XACT\_ABORT OFF  
GO  
BEGIN TRAN  
INSERT INTO t2 VALUES (1)  
INSERT INTO t2 VALUES (2) /\* Foreign key error \*/  
INSERT INTO t2 VALUES (3)  
COMMIT TRAN  
GO  
SET XACT\_ABORT ON  
GO  
BEGIN TRAN  
INSERT INTO t2 VALUES (4)  
INSERT INTO t2 VALUES (5) /\* Foreign key error \*/  
INSERT INTO t2 VALUES (6)  
COMMIT TRAN  
GO  
/\* Select shows only keys 1 and 3 added.  
Key 2 insert failed and was rolled back, but  
XACT\_ABORT was OFF and rest of transaction  
succeeded.  
Key 5 insert error with XACT\_ABORT ON caused  
all of the second transaction to roll back. \*/  
SELECT \*  
FROM t2  
GO  
DROP TABLE t2  
DROP TABLE t1  
GO