1. SET DEADLOCK_PRIORITY2. SET LOCK_TIMEOUT3.@@LOCK_TIMEOUT4.SET IDENTITY_INSERT5.SET IMPLICIT_TRANSACTIONS6.SET NOCOUNT7.@@ROWCOUNT8.SET ROWCOUNT9.SET TRANSACTION ISOLATION LEVEL10.SET XACT_ABORT 1. SET DEADLOCK_PRIORITY

說(shuō)明：控制在發(fā)生死鎖情況時(shí)會(huì)話的反應(yīng)方式。如果兩個(gè)進(jìn)程都鎖定數(shù)據(jù)，并且直到其它進(jìn)程釋放自己的鎖時(shí)，每個(gè)進(jìn)程才能釋放自己的鎖，即發(fā)生死鎖情況。

語(yǔ)法：SET DEADLOCK_PRIORITY { LOW | NORMAL | @deadlock_var }

參數(shù)：LOW; 指定當(dāng)前會(huì)話為首選死鎖犧牲品。Microsoft® SQL Server™ 自動(dòng)回滾死鎖犧牲品的事務(wù)，并給客戶端應(yīng)用程序返回 1205 號(hào)死鎖錯(cuò)誤信息。NORMAL; 指定會(huì)話返回到默認(rèn)的死鎖處理方法。@deadlock_var 是指定死鎖處理方法的字符變量。如果指定 LOW，則 @deadlock_var 為 3；如果指定 NORMAL，則 @deadlock_var 為 6。

注釋?zhuān)篠ET DEADLOCK_PRIORITY 的設(shè)置是在執(zhí)行或運(yùn)行時(shí)設(shè)置，而不是在分析時(shí)設(shè)置。

權(quán)限：SET DEADLOCK_PRIORITY 權(quán)限默認(rèn)授予所有用戶。

2. SET LOCK_TIMEOUT

說(shuō)明：指定語(yǔ)句等待鎖釋放的毫秒數(shù)。

語(yǔ)法：SET LOCK_TIMEOUT timeout_period

參數(shù)：timeout_period 是在 Microsoft® SQL Server™ 返回鎖定錯(cuò)誤前經(jīng)過(guò)的毫秒數(shù)。值為 -1（默認(rèn)值）時(shí)表示沒(méi)有超時(shí)期限（即無(wú)限期等待）。當(dāng)鎖等待超過(guò)超時(shí)值時(shí)，將返回錯(cuò)誤。值為 0 時(shí)表示根本不等待，并且一遇到鎖就返回信息。

注釋?zhuān)涸谶B接開(kāi)始時(shí)，該設(shè)置的值為 -1。設(shè)置更改后，新設(shè)置在其余的連接時(shí)間里一直有效。SET LOCK_TIMEOUT 的設(shè)置是在執(zhí)行或運(yùn)行時(shí)設(shè)置，而不是在分析時(shí)設(shè)置。READPAST 鎖定提示為該 SET 選項(xiàng)提供了另一種方式。

權(quán)限：SET LOCK_TIMEOUT 權(quán)限默認(rèn)授予所有用戶。

示例：下例將鎖超時(shí)期限設(shè)置為 1,800 毫秒。

SET LOCK_TIMEOUT 1800GO

3. @@LOCK_TIMEOUT

說(shuō)明：返回當(dāng)前會(huì)話的當(dāng)前鎖超時(shí)設(shè)置，單位為毫秒。

語(yǔ)法：@@LOCK_TIMEOUT

返回類(lèi)型：integer

注釋?zhuān)篠ET LOCK_TIMEOUT 允許應(yīng)用程序設(shè)置語(yǔ)句等待阻塞資源的最長(zhǎng)時(shí)間。當(dāng)一條語(yǔ)句已等待超過(guò) LOCK_TIMEOUT 所設(shè)置的時(shí)間，則被鎖住的語(yǔ)句將自動(dòng)取消，并給應(yīng)用程序返回一條錯(cuò)誤信息。在一個(gè)連接的開(kāi)始，@@LOCK_TIMEOUT 返回一個(gè) –1值。

示例：下面的示例顯示當(dāng)一個(gè) LOCK_TIMEOUT 值未被設(shè)置時(shí)的結(jié)果集。

SELECT @@LOCK_TIMEOUT下面是結(jié)果集：-----------------1

下面的示例設(shè)置 LOCK_TIMEOUT 為 1800 毫秒，然后調(diào)用 @@LOCK_TIMEOUT。

SET LOCK_TIMEOUT 1800SELECT @@LOCK_TIMEOUT

下面是結(jié)果集：------------------------------1800

4. SET IDENTITY_INSERT

說(shuō)明：允許將顯式值插入表的標(biāo)識(shí)列中。

語(yǔ)法：SET IDENTITY_INSERT [ database.[ owner.] ] { table } { ON | OFF }

參數(shù)：database 是指定的表所駐留的數(shù)據(jù)庫(kù)名稱(chēng)。owner 是表所有者的名稱(chēng)。table 是含有標(biāo)識(shí)列的表名。

注釋?zhuān)喝魏螘r(shí)候，會(huì)話中只有一個(gè)表的 IDENTITY_INSERT 屬性可以設(shè)置為 ON。如果某個(gè)表已將此屬性設(shè)置為 ON，并且為另一個(gè)表發(fā)出了 SET IDENTITY_INSERT ON 語(yǔ)句，則 Microsoft® SQL Server™ 返回一個(gè)錯(cuò)誤信息，指出 SET IDENTITY_INSERT 已設(shè)置為 ON 并報(bào)告此屬性已設(shè)置為 ON 的表。如果插入值大于表的當(dāng)前標(biāo)識(shí)值，則 SQL Server 自動(dòng)將新插入值作為當(dāng)前標(biāo)識(shí)值使用。SET IDENTITY_INSERT 的設(shè)置是在執(zhí)行或運(yùn)行時(shí)設(shè)置，而不是在分析時(shí)設(shè)置。

權(quán)限：執(zhí)行權(quán)限默認(rèn)授予 sysadmin 固定服務(wù)器角色和 db_owner 及 db_ddladmin 固定數(shù)據(jù)庫(kù)角色以及對(duì)象所有者。

示例：下例創(chuàng)建一個(gè)含有標(biāo)識(shí)列的表，并顯示如何使用 SET IDENTITY_INSERT 設(shè)置填充由 DELETE 語(yǔ)句導(dǎo)致的標(biāo)識(shí)值中的空隙。

-- 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 productsGO

-- 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 ONGO

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

SELECT * FROM productsGO

-- Drop products table.DROP TABLE productsGO

5. SET IMPLICIT_TRANSACTIONS

說(shuō)明：為連接設(shè)置隱性事務(wù)模式。

語(yǔ)法：SET IMPLICIT_TRANSACTIONS { ON | OFF }

注釋?zhuān)寒?dāng)設(shè)置為 ON 時(shí)，SET IMPLICIT_TRANSACTIONS 將連接設(shè)置為隱性事務(wù)模式。當(dāng)設(shè)置為 OFF 時(shí)，則使連接返回到自動(dòng)提交事務(wù)模式。當(dāng)連接是隱性事務(wù)模式且當(dāng)前不在事務(wù)中時(shí)，執(zhí)行下列語(yǔ)句將啟動(dòng)事務(wù)：

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

如果連接已經(jīng)在打開(kāi)的事務(wù)中，則上述語(yǔ)句不啟動(dòng)新事務(wù)。對(duì)于因?yàn)樵撛O(shè)置為 ON 而自動(dòng)打開(kāi)的事務(wù)，用戶必須在該事務(wù)結(jié)束時(shí)將其顯式提交或回滾。否則當(dāng)用戶斷開(kāi)連接時(shí)，事務(wù)及其所包含的所有數(shù)據(jù)更改將回滾。在事務(wù)提交后，執(zhí)行上述任一語(yǔ)句即可啟動(dòng)新事務(wù)。隱性事務(wù)模式將保持有效，直到連接執(zhí)行 SET IMPLICIT_TRANSACTIONS OFF 語(yǔ)句使連接返回到自動(dòng)提交模式。在自動(dòng)提交模式下，如果各個(gè)語(yǔ)句成功完成則提交。在進(jìn)行連接時(shí)，SQL Server ODBC 驅(qū)動(dòng)程序和用于 SQL Server 的 Microsoft OLE DB 提供程序自動(dòng)將 IMPLICIT_TRANSACTIONS 設(shè)置為 OFF。對(duì)來(lái)自 DB-Library 應(yīng)用程序的連接，SET IMPLICIT_TRANSACTIONS 默認(rèn)為 OFF。當(dāng) SET ANSI_DEFAULTS 為 ON 時(shí)，將啟用 SET IMPLICIT_TRANSACTIONS。SET IMPLICIT_TRANSACTIONS 的設(shè)置是在執(zhí)行或運(yùn)行時(shí)設(shè)置，而不是在分析時(shí)設(shè)置。

示例：下例演示在將 IMPLICIT_TRANSACTIONS 設(shè)置為 ON 時(shí)顯式或隱式啟動(dòng)事務(wù)。它使用 @@TRANCOUNT 函數(shù)演示打開(kāi)的事務(wù)和關(guān)閉的事務(wù)。

USE pubsGO

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

PRINT 'Use explicit transaction'BEGIN TRANINSERT INTO t1 VALUES (2)SELECT 'Tran count in transaction'= @@TRANCOUNTCOMMIT TRANSELECT 'Tran count outside transaction'= @@TRANCOUNTGO

PRINT 'Setting IMPLICIT_TRANSACTIONS ON'GOSET IMPLICIT_TRANSACTIONS ONGO

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

PRINT 'Use explicit transactions with IMPLICIT_TRANSACTIONS ON'GOBEGIN TRANINSERT INTO t1 VALUES (5)SELECT 'Tran count in transaction'= @@TRANCOUNTCOMMIT TRANSELECT 'Tran count outside transaction'= @@TRANCOUNTGO

SELECT * FROM t1GO

-- Need to commit this tran too!DROP TABLE t1COMMIT TRANGO

6. SET NOCOUNT說(shuō)明：使返回的結(jié)果中不包含有關(guān)受 Transact-SQL 語(yǔ)句影響的行數(shù)的信息。語(yǔ)法：SET NOCOUNT { ON | OFF }

注釋?zhuān)寒?dāng) SET NOCOUNT 為 ON 時(shí)，不返回計(jì)數(shù)（表示受 Transact-SQL 語(yǔ)句影響的行數(shù)）。當(dāng) SET NOCOUNT 為 OFF 時(shí)，返回計(jì)數(shù)。即使當(dāng) SET NOCOUNT 為 ON 時(shí)，也更新 @@ROWCOUNT 函數(shù)。當(dāng) SET NOCOUNT 為 ON 時(shí)，將不給客戶端發(fā)送存儲(chǔ)過(guò)程中的每個(gè)語(yǔ)句的 DONE_IN_PROC 信息。當(dāng)使用 Microsoft® SQL Server™ 提供的實(shí)用工具執(zhí)行查詢時(shí)，在 Transact-SQL 語(yǔ)句（如 SELECT、INSERT、UPDATE 和 DELETE）結(jié)束時(shí)將不會(huì)在查詢結(jié)果中顯示'nn rows affected'。如果存儲(chǔ)過(guò)程中包含的一些語(yǔ)句并不返回許多實(shí)際的數(shù)據(jù)，則該設(shè)置由于大量減少了網(wǎng)絡(luò)流量，因此可顯著提高性能。SET NOCOUNT 設(shè)置是在執(zhí)行或運(yùn)行時(shí)設(shè)置，而不是在分析時(shí)設(shè)置。

權(quán)限：SET NOCOUNT 權(quán)限默認(rèn)授予所有用戶。

示例：下例在 osql 實(shí)用工具或 SQL Server 查詢分析器中執(zhí)行時(shí)，可防止顯示有關(guān)受影響的行數(shù)的信息。

USE pubsGO-- Display the count message.SELECT au_lname FROM authorsGOUSE pubsGO-- SET NOCOUNT to ON and no longer display the count message.SET NOCOUNT ONGOSELECT au_lname FROM authorsGO-- Reset SET NOCOUNT to OFF.SET NOCOUNT OFFGO

7. @@ROWCOUNT說(shuō)明：返回受上一語(yǔ)句影響的行數(shù)。

語(yǔ)法：@@ROWCOUNT

返回類(lèi)型：integer

注釋?zhuān)喝魏尾环祷匦械恼Z(yǔ)句將這一變量設(shè)置為 0 ，如 IF 語(yǔ)句。

示例：下面的示例執(zhí)行 UPDATE 語(yǔ)句并用 @@ROWCOUNT 來(lái)檢測(cè)是否有發(fā)生更改的行。

UPDATE authors SET au_lname = 'Jones'WHERE au_id = '999-888-7777'IF @@ROWCOUNT = 0print 'Warning: No rows were updated'

8. SET ROWCOUNT

說(shuō)明：使 Microsoft® SQL Server™ 在返回指定的行數(shù)之后停止處理查詢。

語(yǔ)法：SET ROWCOUNT { number | @number_var }

參數(shù)：number | @number_var 是在停止給定查詢之前要處理的行數(shù)（整數(shù)）。

注釋?zhuān)航ㄗh將當(dāng)前使用 SET ROWCOUNT 的 DELETE、INSERT 和 UPDATE 語(yǔ)句重新編寫(xiě)為使用 TOP 語(yǔ)法。有關(guān)更多信息，請(qǐng)參見(jiàn) DELETE、INSERT 或 UPDATE。對(duì)于在遠(yuǎn)程表和本地及遠(yuǎn)程分區(qū)視圖上執(zhí)行的 INSERT、UPDATE 和 DELETE 語(yǔ)句，忽略 SET ROWCOUNT 選項(xiàng)設(shè)置。若要關(guān)閉該選項(xiàng)（以便返回所有的行），請(qǐng)將 SET ROWCOUNT 指定為 0。說(shuō)明; 設(shè)置 SET ROWCOUNT 選項(xiàng)將使大多數(shù) Transact-SQL 語(yǔ)句在已受指定數(shù)目的行影響后停止處理。這包括觸發(fā)器和 INSERT、UPDATE 及 DELETE 等數(shù)據(jù)修改語(yǔ)句。ROWCOUNT 選項(xiàng)對(duì)動(dòng)態(tài)游標(biāo)無(wú)效，但限制鍵集的行集和不感知游標(biāo)。使用該選項(xiàng)時(shí)應(yīng)謹(jǐn)慎，它主要與 SELECT 語(yǔ)句一起使用。如果行數(shù)的值較小，則 SET ROWCOUNT 替代 SELECT 語(yǔ)句 TOP 關(guān)鍵字。SET ROWCOUNT 的設(shè)置是在執(zhí)行或運(yùn)行時(shí)設(shè)置，而不是在分析時(shí)設(shè)置。

權(quán)限：SET ROWCOUNT 權(quán)限默認(rèn)授予所有用戶。

示例：SET ROWCOUNT 在指定的行數(shù)后停止處理。在下例中，注意有 x 行滿足預(yù)付款少于或等于 $5,000 的條件；但是，從更新所返回的行數(shù)中可以看出并非所有的行都得到處理。ROWCOUNT 影響所有的 Transact-SQL 語(yǔ)句。

USE pubsGOSELECT count(*) AS CntFROM titles WHERE advance >= 5000GO

下面是結(jié)果集：

Cnt;;;;----------- 11;

(1 row(s) affected)

現(xiàn)在，將 ROWCOUNT 設(shè)置為 4，并更新預(yù)付款等于或大于 $5,000 的所有行。

-- SET ROWCOUNT to 4.SET ROWCOUNT 4GOUPDATE titlesSET advance = 5000WHERE advance >= 5000GO

9. SET TRANSACTION ISOLATION LEVEL說(shuō)明：控制由連接發(fā)出的所有 Microsoft® SQL Server™ SELECT 語(yǔ)句的默認(rèn)事務(wù)鎖定行為。

語(yǔ)法：SET TRANSACTION ISOLATION LEVEL{ READ COMMITTED;| READ UNCOMMITTED;| REPEATABLE READ;| SERIALIZABLE }

參數(shù)：READ COMMITTED 指定在讀取數(shù)據(jù)時(shí)控制共享鎖以避免臟讀，但數(shù)據(jù)可在事務(wù)結(jié)束前更改，從而產(chǎn)生不可重復(fù)讀取或幻像數(shù)據(jù)。該選項(xiàng)是 SQL Server 的默認(rèn)值。READ UNCOMMITTED 執(zhí)行臟讀或 0 級(jí)隔離鎖定，這表示不發(fā)出共享鎖，也不接受排它鎖。當(dāng)設(shè)置該選項(xiàng)時(shí)，可以對(duì)數(shù)據(jù)執(zhí)行未提交讀或臟讀；在事務(wù)結(jié)束前可以更改數(shù)據(jù)內(nèi)的數(shù)值，行也可以出現(xiàn)在數(shù)據(jù)集中或從數(shù)據(jù)集消失。該選項(xiàng)的作用與在事務(wù)內(nèi)所有語(yǔ)句中的所有表上設(shè)置 NOLOCK 相同。這是四個(gè)隔離級(jí)別中限制最小的級(jí)別。REPEATABLE READ 鎖定查詢中使用的所有數(shù)據(jù)以防止其他用戶更新數(shù)據(jù)，但是其他用戶可以將新的幻像行插入數(shù)據(jù)集，且幻像行包括在當(dāng)前事務(wù)的后續(xù)讀取中。因?yàn)椴l(fā)低于默認(rèn)隔離級(jí)別，所以應(yīng)只在必要時(shí)才使用該選項(xiàng)。SERIALIZABLE 在數(shù)據(jù)集上放置一個(gè)范圍鎖，以防止其他用戶在事務(wù)完成之前更新數(shù)據(jù)集或?qū)⑿胁迦霐?shù)據(jù)集內(nèi)。這是四個(gè)隔離級(jí)別中限制最大的級(jí)別。因?yàn)椴l(fā)級(jí)別較低，所以應(yīng)只在必要時(shí)才使用該選項(xiàng)。該選項(xiàng)的作用與在事務(wù)內(nèi)所有 SELECT 語(yǔ)句中的所有表上設(shè)置 HOLDLOCK 相同。

注釋?zhuān)阂淮沃荒茉O(shè)置這些選項(xiàng)中的一個(gè)，而且設(shè)置的選項(xiàng)將一直對(duì)那個(gè)連接保持有效，直到顯式更改該選項(xiàng)為止。這是默認(rèn)行為，除非在語(yǔ)句的 FROM 子句中在表級(jí)上指定優(yōu)化選項(xiàng)。SET TRANSACTION ISOLATION LEVEL 的設(shè)置是在執(zhí)行或運(yùn)行時(shí)設(shè)置，而不是在分析時(shí)設(shè)置。

示例：下例為會(huì)話設(shè)置 TRANSACTION ISOLATION LEVEL。對(duì)于每個(gè)后續(xù) Transact-SQL 語(yǔ)句，SQL Server 將所有共享鎖一直控制到事務(wù)結(jié)束為止。

SET TRANSACTION ISOLATION LEVEL REPEATABLE READGOBEGIN TRANSACTIONSELECT * FROM publishersSELECT * FROM authors...COMMIT TRANSACTION

10. SET XACT_ABORT

說(shuō)明：指定當(dāng) Transact-SQL 語(yǔ)句產(chǎn)生運(yùn)行時(shí)錯(cuò)誤時(shí)，Microsoft® SQL Server™ 是否自動(dòng)回滾當(dāng)前事務(wù)。

語(yǔ)法：SET XACT_ABORT { ON | OFF }

注釋?zhuān)寒?dāng) SET XACT_ABORT 為 ON 時(shí)，如果 Transact-SQL 語(yǔ)句產(chǎn)生運(yùn)行時(shí)錯(cuò)誤，整個(gè)事務(wù)將終止并回滾。為 OFF 時(shí)，只回滾產(chǎn)生錯(cuò)誤的 Transact-SQL 語(yǔ)句，而事務(wù)將繼續(xù)進(jìn)行處理。編譯錯(cuò)誤（如語(yǔ)法錯(cuò)誤）不受 SET XACT_ABORT 的影響。對(duì)于大多數(shù) OLE DB 提供程序（包括 SQL Server），隱性或顯式事務(wù)中的數(shù)據(jù)修改語(yǔ)句必須將 XACT_ABORT 設(shè)置為 ON。唯一不需要該選項(xiàng)的情況是提供程序支持嵌套事務(wù)時(shí)。有關(guān)更多信息，請(qǐng)參見(jiàn)分布式查詢和分布式事務(wù)。 SET XACT_ABORT 的設(shè)置是在執(zhí)行或運(yùn)行時(shí)設(shè)置，而不是在分析時(shí)設(shè)置。

示例：下例導(dǎo)致在含有其它 Transact-SQL 語(yǔ)句的事務(wù)中發(fā)生違反外鍵錯(cuò)誤。在第一個(gè)語(yǔ)句集中產(chǎn)生錯(cuò)誤，但其它語(yǔ)句均成功執(zhí)行且事務(wù)成功提交。在第二個(gè)語(yǔ)句集中，SET XACT_ABORT 設(shè)置為 ON。這導(dǎo)致語(yǔ)句錯(cuò)誤使批處理終止，并使事務(wù)回滾。

CREATE TABLE t1 (a int PRIMARY KEY)CREATE TABLE t2 (a int REFERENCES t1(a))GOINSERT INTO t1 VALUES (1)INSERT INTO t1 VALUES (3)INSERT INTO t1 VALUES (4)INSERT INTO t1 VALUES (6)GOSET XACT_ABORT OFFGOBEGIN TRANINSERT INTO t2 VALUES (1)INSERT INTO t2 VALUES (2) /* Foreign key error */INSERT INTO t2 VALUES (3)COMMIT TRANGO

SET XACT_ABORT ONGO

BEGIN TRANINSERT INTO t2 VALUES (4)INSERT INTO t2 VALUES (5) /* Foreign key error */INSERT INTO t2 VALUES (6)COMMIT TRANGO

/* Select shows only keys 1 and 3 added. Key 2 insert failed and was rolled back, butXACT_ABORT was OFF and rest of transactionsucceeded.Key 5 insert error with XACT_ABORT ON causedall of the second transaction to roll back. */

SELECT * FROM t2GO

DROP TABLE t2DROP TABLE t1GO