行使 SQL Server 时必要常常用到的几个配置选项！

发布时间：2018-08-15 09:03:27 所属栏目：电商来源：站长网

导读：1. SET DEADLOCK_PRIORITY 声名：节制在发存亡锁环境时会话的回响方法。假如两个历程都锁定命据，而且直到其余历程开释本身的锁时，每个历程才气开释本身的锁，即发存亡锁环境。语法：SET DEADLOCK_PRIORITY { LOW | NORMAL | @deadlock_var } 参数：LOW

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

（编辑：湖南网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!

户外搭讪式直播是不是	微软公布每年只提供一
IDC调查只有9%的企业	设施管理的五大应用趋