SQL Server2012 T-SQL基础教程--读书笔记（8 - 10章）

SQL Server2012 T-SQL基础教程--读书笔记（8 - 10章）

示例数据库：点我

 

 

CHAPTER 08 数据修改

DML(Data Manipulation Language) 称为数据操作语言，除涉及数据修改语句之外，还包括数据检索。DML主要包含SELECT,INSERT,UPDATE,DELETE,TRUNCATE和MERGE语句。

8.1 插入数据

T-SQL 提供了多个将数据插入到表中的语句：INSERT VALUES,INSERT SELECT,INSERT EXEC,SELECT INTO,BULK INSERT

8.1.1 INSERT VALUES 语句

除标准SQL的特性外，SQL SERVER 2008开始支持增强的VALUES子句，即允许用,分隔多行

INSERT INTO table_name (col1,col2)
VALUES
(val1,val2), (val1,val2), (val1,val2)

该语句被作为一个原子性操作。对于增强VALUES子句，还可以将其作为表值构造函数以标准方式构建一个派生表。

  1. SELECT * 
  2. FROM (VALUES (1,2),(3,4),(5,6)) num(odd,even) 

8.1.2 INSERT SELECT 语句

  1. --1.一般形式 
  2. INSERT INTO table_name (col1,col2) 
  3. SELECT col1,col2 FROM table_name1 
  4. --2.增强VALUES子句，此处SELECT没有FROM子句，不是标准语法，建议使用VALUES子句的表值构造函数来构造结果值 
  5. INSERT INTO table_name (col1,col2) 
  6. SELECT val1,val2 UNION ALL 
  7. SELECT val3,val4 UNION ALL 
  8. SELECT val5,val6 UNION ALL 
  9. SELECT val7,val8  

INSERT SELECT也是作为原子性操作执行的。

8.1.3 INSERT EXEC 语句

使用INSERT EXEC语句可以将存储过程或动态SQL批处理返回的结果集插入到目标表中。INSERT EXEC非常类似INSERT SELECT的语法和概念，只是将SELECT语句替代为EXEC语句。

--创建一个过程，创建过程和执行动态语句将在CHPATER 10中介绍
CREATE PROC Sales.usp_getorders
  @country AS NVARCHAR(40)
AS
BEGIN
    SELECT orderid, orderdate, empid, custid
    FROM Sales.Orders 
    WHERE shipcountry = @country
END
--------------------------
INSERT INTO dbo.Orders(orderid, orderdate, empid, custid)
  EXEC Sales.usp_getorders @country = 'France';

8.1.4 SELECT INTO 语句

SELECT INTO是一个非标准的T-SQL语句，它使用查询的结果创建并填充目标表。“非标准”即不是ISO和ANSI SQL标准的一部分，不能使用此语句将数据插入到现有表中。

--比如备份Sales.Orders表中 France 的记录
SELECT orderid, orderdate, empid, custid
INTO Sales.FanceOrders
FROM Sales.Orders 
WHERE shipcountry = 'France'

SELECT INTO好处之一就是只要数据库的“恢复模式”属性未设置成完整，SELECT INTO就会以最小日志记录模式执行，意味着相对于完整日志记录这是一个非常快速的操作。更多详细住处请点击这里

8.1.5 BULK INSERT 语句

可以使用BULK INSERT语句将来自文件的数据插入到一个现在表中。在语句中指定目标表、源文件和选项。可以指定多个选项，包括数据文件类型（如：CHAR和NATIVE）、字段终止符、行终止符和其他所有文件选项。

--建表
IF OBJECT_ID('dbo.Orders', 'U') IS NOT NULL DROP TABLE dbo.Orders;

CREATE TABLE dbo.Orders
(
  orderid   INT         NOT NULL
    CONSTRAINT PK_Orders PRIMARY KEY,
  orderdate DATE        NOT NULL
    CONSTRAINT DFT_orderdate DEFAULT(SYSDATETIME()),
  empid     INT         NOT NULL,
  custid    VARCHAR(10) NOT NULL
);
-------------------------------
BULK INSERT dbo.Orders FROM 'd:\orders.txt'
  WITH 
    (
       DATAFILETYPE    = 'char',
       FIELDTERMINATOR = ',',
       ROWTERMINATOR   = '\n'
    );
GO
-------------------------------
/*
orders.txt 数据
10001,2009-02-12,3,1
10002,2009-02-12,5,2
10003,2009-02-13,4,2
10004,2009-02-14,1,1
10005,2009-02-13,1,3
10006,2009-02-15,3,3
10007,2009-02-15,2,2
10008,2009-02-15,1,3
10009,2009-02-16,2,3
10010,2009-02-16,3,1
*/

8.1.6 标识列属性和序列对象

SQL SERVER 支持两种自动生成键的内置解决方案：标识列属性和序列对象（SQL SERVER 2012）。

8.1.6.1 标识列属性

SQL SERVER 允许无小数的任意数值类型的列定义了IDENTITY的属性，此属性根据提供的种子值（第一参数）和增量值（步长值）自动生成值。通常情况下是使用此属性来生成SURROGATE键（系统生成）。

CREATE  table dbo.T1 
(
    keycol INT IDENTITY(1,1)    CONSTRAINT PK_T1 PRIMARY KEY,
    datacol VARCHAR(20) NOT NULL CONSTRAINT CHK_T1_datacol CHECK(datacol like '[A-Za-z]%')
)
--插入数据
INSERT INTO dbo.T1 VALUES ('AAA'),('BBB'),('ccc')
-- 使用$identity 结果是1，2，3
SELECT $IDENTITY FROM dbo.t1

-- 显式值插入到dbo.T1里
SET IDENTITY_INSERT dbo.T1 ON
INSERT INTO dbo.T1(keycol,datacol) VALUES(4,'dddd')
SET IDENTITY_INSERT dbo.T1 OFF

--结果是4，返回当前值，那么下一个值就是5
SELECT IDENT_CURRENT('dbo.T1')

 

enter description here

1460043669645.jpg

查询表时，除可以使用列来引用IDENTITY的值，还可以使用SQL SERVER提供的标识符$identity。
标识列的一个重要属性就是用户不能将其添加到现有列或从现有列删除它。如果要手动插入数据到现有列，只需对表设置IDENTITY_INSERT的会话选项即可。

 

8.1.6.2 序列对象

序列对象是作为一种替代标识列的键生成机制添加到SQL SERVER 2012 的，这在其他数据库中已经实现的标准功能。
与标识列不同，序列对象的优点之一是它不是绑定到特定表中的特定列，而是作为数据库中的一个独立对象。当需要生成新值时，调用一个针对对象的函数，然后可以在任何地方使用返回值。
与标识列属性不同点：

  • 序列对象可以指定任意整数数值类型（默认为BIGINT）

  • 序列对象支持指定数据类型内的最小值和最大值，如果不指定则为数据类型的最大或最小值。

  • 序列对象可以循环（默认为不循环）。

/*
创建一个订单ID的序列对象，数据类型为INT，最小值为1，最大值为INT最大值，从1开始，步长为1.
*/
--创建
CREATE SEQUENCE dbo.SeqOrderIDs AS INT MINVALUE 1 CYCLE
--修改
ALTER SEQUENCE dbo.SeqOrderIDs NO CYCLE
--查询
SELECT (NEXT VALUE FOR dbo.SeqOrderIDs)

IF OBJECT_ID('dbo.T1', 'U') IS NOT NULL DROP TABLE dbo.T1;
CREATE TABLE dbo.T1
(
  keycol  INT         NOT NULL
    CONSTRAINT PK_T1 PRIMARY KEY,
  datacol VARCHAR(10) NOT NULL
);
--插入到T1
INSERT INTO dbo.T1 VALUES(NEXT VALUE FOR dbo.SeqOrderIDs, 'b')

--获取序列对象的相关住处，可以查询sys.sequences的视图
SELECT current_value FROM sys.sequences WHERE name = 'SeqOrderIDs'

SQL SERVER扩展了对序列选项的支持，一个是类似于开窗函数的OVER子句，在多行插入时来控制分配序列值的顺序。

INSERT INTO dbo.T1(keycol, datacol)
SELECT 
    NEXT VALUE FOR dbo.SeqOrderIds OVER (ORDER BY hiredate)
    ,LEFT(firstname,1) + left(lastname,1)
FROM HR.Employees

 

enter description here

1460043700745.jpg

另一个是允许在默认约束中使用NEXT VALUE FOR函数。这是超越了标识列属性的一个显著优势。

 

ALTER TABLE dbo.T1 ADD CONSTRAINT DF_T1_keycol 
  DEFAULT(NEXT VALUE FOR dbo.SeqOrderIDs)
  FOR keycol

INSERT INTO dbo.T1(datacol) VALUES ('TEST')

最后一个扩展是允许使用一个名为sp_sequence_get_range的存储过程一次分配整个序列值范围。即如果你需要某个范围的序列值，最简单的方式就是仅更新序列一次，按照范围的大小递增。调用此过程时，设置相应的参数即可。

DECLARE @first SQL_VARIANT;
EXEC sys.sp_sequence_get_range 
  @sequence_name = 'dbo.SeqOrderIDs', 
  @range_size = 1000, 
  @range_first_value = @firsts

8.2 删除数据

T-SQL提供个删除表中的行的语句：DELETE和TRUNCATE。
准备表：

CREATE TABLE dbo.Customers
(
  custid       INT          NOT NULL,
  companyname  NVARCHAR(40) NOT NULL,
  contactname  NVARCHAR(30) NOT NULL,
  contacttitle NVARCHAR(30) NOT NULL,
  address      NVARCHAR(60) NOT NULL,
  city         NVARCHAR(15) NOT NULL,
  region       NVARCHAR(15) NULL,
  postalcode   NVARCHAR(10) NULL,
  country      NVARCHAR(15) NOT NULL,
  phone        NVARCHAR(24) NOT NULL,
  fax          NVARCHAR(24) NULL,
  CONSTRAINT PK_Customers PRIMARY KEY(custid)
);

CREATE TABLE dbo.Orders
(
  orderid        INT          NOT NULL,
  custid         INT          NULL,
  empid          INT          NOT NULL,
  orderdate      DATETIME     NOT NULL,
  requireddate   DATETIME     NOT NULL,
  shippeddate    DATETIME     NULL,
  shipperid      INT          NOT NULL,
  freight        MONEY        NOT NULL
    CONSTRAINT DFT_Orders_freight DEFAULT(0),
  shipname       NVARCHAR(40) NOT NULL,
  shipaddress    NVARCHAR(60) NOT NULL,
  shipcity       NVARCHAR(15) NOT NULL,
  shipregion     NVARCHAR(15) NULL,
  shippostalcode NVARCHAR(10) NULL,
  shipcountry    NVARCHAR(15) NOT NULL,
  CONSTRAINT PK_Orders PRIMARY KEY(orderid),
  CONSTRAINT FK_Orders_Customers FOREIGN KEY(custid)
    REFERENCES dbo.Customers(custid)
);

8.2.1 DELETE 语句

DELETE是一个标准语句，只包含有FROM和WHERE两个子句。
DELETE语句是完全日志记录的，所以，当删除大量的行时，运行时间可以会比较长一点。

8.2.2 TRUNCATE 语句

TRUNCATE 语句删除表中的所有行，与DELETE 不同，TRUNCATE 不进行筛选的。TRUNCATE 是最小日志方式，所以性能较DELETE 要好得多。TRUNCATE 完全是事务性的（常见谅解），并且在ROLLBACK时，SQL SERVER 可以撤销删除操作。
当表中含有标识列时，TRUNCATE 会重置标识值到原始的种子值，而DELETE 不会。
当目标表被外键约束引用时，即使父表为空，甚至外键是禁用的，都不允许使用TRUNCATE 语句。唯一解决方法就是删除该表的所有外键。
TRUNCATE 和 DROP 语句的执行速度都是非常快，为防止生产环境上发生误操作，可以创建一个虚拟表，带有指向生产表的外键，这样就可以防止用户从被引用表中删除数据或是删除表实体。

8.2.3 基于联接的DELETE

T-SQL 支持一个联接的非标准的DELETE 语法，联接本身是有筛选作用的。

DELETE FROM o
FROM dbo.Orders o INNER JOIN dbo.Customers c ON o.custid =c.custid 
WHERE c.country = 'USA'

其实这个语句和SELECT语句样的。你把DELETE替换成SELECT *毫无专程感。
如果想要使用标准语句来实现这个功能，可以这样

  1. DELETE FROM dbo.Orders  
  2. WHERE EXISTS ( 
  3. SELECT * FROM Sales.Customers c 
  4. WHERE c.custid = dbo.Orders.custid AND c.country = 'USA' 
  5. ) 

SQL SERVER 很有可能以相同的方式处理这两个查询，因此，不用考虑两者之间的性能差异。但是，建议尽可能的坚持标准。

8.3 更新数据

8.3.1 UPDATE 语句

8.3.2 基于联接的 UPDATE

T-SQL 也支持基于联接的UPDATE 非标准语句。
与DELETE 和 SELECT 差别不大

UPDATE od SET od.discount += 0.05
FROM Sales.OrderDetails od
    INNER JOIN dbo.Orders o ON o.orderid = od.orderid
WHERE o.custid = 1

当然也也可以使用标准SQL，通过子查询来进行UPDATE。
在某些情况下，联接版本比子查询版本会有性能优势。除了筛选之外，联接也为用户提供了访问其他表属性的权限，用户可以在SET子句的列赋值中使用这些属性。
下面是非标准UPDATE语句：

UPDATE T1
  SET col1 = T2.col1,
      col2 = T2.col2,
      col3 = T2.col3
FROM dbo.T1 JOIN dbo.T2
  ON T2.keycol = T1.keycol
WHERE T2.col4 = 'ABC';

需要UPDATE的T1表可以直接引用联接查询的T2表的列。如果我们使用标准SQL，可能需要使用的子查询就会比较多，造成SQL比较繁杂：

UPDATE dbo.T1
  SET col1 = (SELECT col1
              FROM dbo.T2
              WHERE T2.keycol = T1.keycol),
      col2 = (SELECT col2
              FROM dbo.T2
              WHERE T2.keycol = T1.keycol),
      col3 = (SELECT col3
              FROM dbo.T2
              WHERE T2.keycol = T1.keycol)
WHERE EXISTS
  (SELECT *
   FROM dbo.T2
   WHERE T2.keycol = T1.keycol
     AND T2.col4 = 'ABC');

标准SQL支持行构造函数（也称为矢量表达式），在SQL SERVER 2012中只实现了部分功能，行构造函数的许多方面SQL SERVER还未实现。包括下面的UPDATE中的SET子句：

UPDATE dbo.T1
  SET (col1, col2, col3) =
      (SELECT col1, col2, col3
       FROM dbo.T2
       WHERE T2.keycol = T1.keycol)
WHERE EXISTS
  (SELECT *
   FROM dbo.T2
   WHERE T2.keycol = T1.keycol
     AND T2.col4 = 'ABC');

PS: 虽然本书作者说要使用标准SQL来进行UPDATE或DELETE，但是实际使用中笔者认为若是对生产环境中的表操作时还是使用T-SQL的联接查询好一点。
理由如下：

DELETE FROM Sales.Customers  
SELECT * 
FROM Sales.Customers 
WHERE custid = 28

上述语句本来想删除custid = 28的这个用户，但是删除前一般都会确认条件是否正确，所以可能会使用SELECT进行查询，但是有时手滑或者不注意直接运行上述语句，Sales.Customers的数据会被全部删掉。如果使用T-SQL的联接来进行删除可以这样

DELETE FROM c  
SELECT * 
FROM Sales.Customers 
WHERE custid = 28

 

enter description here

1460043754972.jpg

PS:
在删除前对要删除的表不要起别名，即使上述SQL全部运行也不会进行任何删除，通过SELECT查询后再对Sales.Customers起别名c，再注释掉SELECT，就不会有误操作了。
UPDATE同理。

 

8.3.3 赋值 UPDATE

这没什么写的，就是使用变量来更新数据。

8.4 合并数据(MERGE)

SQL SERVER 2008 和 2012 都支持一个名为MERGE的修改数据语句，可以基于条件逻辑应用不同的操作（INSERT,UPDATE和DELETE）。MERGE 语句是SQL 标准的一部分，但是T-SQL向语句中添加了一些非标准扩展。
使用MERGE比传统方式的好处是它允许你以更少的代码表达请求，并且由于它要求更少地访问相关表，所以运行更为有效。
数据准备：

IF OBJECT_ID('dbo.Customers', 'U') IS NOT NULL DROP TABLE dbo.Customers;
GO

CREATE TABLE dbo.Customers
(
  custid      INT         NOT NULL,
  companyname VARCHAR(25) NOT NULL,
  phone       VARCHAR(20) NOT NULL,
  address     VARCHAR(50) NOT NULL,
  CONSTRAINT PK_Customers PRIMARY KEY(custid)
);

INSERT INTO dbo.Customers(custid, companyname, phone, address)
VALUES
  (1, 'cust 1', '(111) 111-1111', 'address 1'),
  (2, 'cust 2', '(222) 222-2222', 'address 2'),
  (3, 'cust 3', '(333) 333-3333', 'address 3'),
  (4, 'cust 4', '(444) 444-4444', 'address 4'),
  (5, 'cust 5', '(555) 555-5555', 'address 5');

IF OBJECT_ID('dbo.CustomersStage', 'U') IS NOT NULL DROP TABLE dbo.CustomersStage;
GO

CREATE TABLE dbo.CustomersStage
(
  custid      INT         NOT NULL,
  companyname VARCHAR(25) NOT NULL,
  phone       VARCHAR(20) NOT NULL,
  address     VARCHAR(50) NOT NULL,
  CONSTRAINT PK_CustomersStage PRIMARY KEY(custid)
);

INSERT INTO dbo.CustomersStage(custid, companyname, phone, address)
VALUES
  (2, 'AAAAA', '(222) 222-2222', 'address 2'),
  (3, 'cust 3', '(333) 333-3333', 'address 3'),
  (5, 'BBBBB', 'CCCCC', 'DDDDD'),
  (6, 'cust 6 (new)', '(666) 666-6666', 'address 6'),
  (7, 'cust 7 (new)', '(777) 777-7777', 'address 7');

-- Query tables
SELECT * FROM dbo.Customers;

SELECT * FROM dbo.CustomersStage;

 

enter description here

1460043797649.jpg

下面演示将源表CustomersStage表的内容合并到目标表Customers表中。即将添加不存在的客户，并更新也存在客户的属性。

 

MERGE INTO dbo.Customers TGT
USING dbo.CustomersStage SRC
    ON TGT.custid = SRC.custid
WHEN MATCHED THEN --匹配的客户更新属性
    UPDATE SET 
       TGT.companyname = SRC.companyname,
       TGT.phone = SRC.phone,
       TGT.address = SRC.address
WHEN NOT MATCHED THEN --不匹配则添加
    INSERT (custid, companyname, phone, address)
    VALUES (SRC.custid, SRC.companyname, SRC.phone, SRC.address);

SELECT * FROM dbo.Customers;

 

enter description here

1460043821757.jpg

WHEN MATCHED子句定义源表行被目标表行匹配采取的操作，WHEN NOT MATCHED则是不匹配时的操作。T-SQL还支持第3个子句，该子句定义了目标表行不被源表行匹配时采取何种操作，此子句叫做WHEN NOT MATCHED BY SOURCE。
比如，希望MERGE示例添加一个逻辑操作，当目标表行不被源表行匹配时从目标表删除此行。

 

MERGE INTO dbo.Customers TGT
USING dbo.CustomersStage SRC
    ON TGT.custid = SRC.custid
WHEN MATCHED THEN
    UPDATE SET 
       TGT.companyname = SRC.companyname,
       TGT.phone = SRC.phone,
       TGT.address = SRC.address
WHEN NOT MATCHED THEN
    INSERT (custid, companyname, phone, address)
    VALUES (SRC.custid, SRC.companyname, SRC.phone, SRC.address)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

SELECT * FROM dbo.Customers

 

enter description here

1460043842861.jpg

与前面的结果比对可以发现custid=1的行已经被删除了。
如果想添加多个条件，使得匹配结果更加精准。可以通过AND选项添加谓词来选择不同的操作子句来增加过滤条件。如下：

 

MERGE dbo.Customers AS TGT
USING dbo.CustomersStage AS SRC
  ON TGT.custid = SRC.custid -- AND TGT.companyname = SRC.companyname 这里也可以添加条件
WHEN MATCHED AND 
       (   TGT.companyname <> SRC.companyname
        OR TGT.phone       <> SRC.phone
        OR TGT.address     <> SRC.address) THEN
  UPDATE SET
    TGT.companyname = SRC.companyname,
    TGT.phone = SRC.phone,
    TGT.address = SRC.address
WHEN NOT MATCHED THEN 
  INSERT (custid, companyname, phone, address)
  VALUES (SRC.custid, SRC.companyname, SRC.phone, SRC.address);

当custid匹配时，但是属性不同时才更新目标表。

8.5 通过表表达式修改数据

SQL SERVER 对表表达式（派生表、CTEs、视图和内嵌表值用户定义函数[UDF]）不是仅限制于SELECT，也允许其他的DML语句。
对于某些问题使用表表达式是唯一选择。下面建T1来进行演示

IF OBJECT_ID('dbo.T1', 'U') IS NOT NULL DROP TABLE dbo.T1;
CREATE TABLE dbo.T1(id INT NOT NULL IDENTITY PRIMARY KEY, col1 INT, col2 INT);
GO
INSERT INTO dbo.T1(col1) VALUES(10),(20),(30);
SELECT * FROM dbo.T1;

假设拟要更新T1表，设置col2 为一个具有ROW_NUMBER函数的表达式结果。直接使用UPDATE语句的SET子句不允许使用ROW_NUMBER函数。此时可以使用CTE来进行进行设置

WITH C AS 
(SELECT * ,ROW_NUMBER()OVER(ORDER BY col1) AS rownum FROM dbo.T1 t
)
UPDATE C SET C.col2 = C.rownum

SELECT * FROM dbo.T1 t

 

enter description here

1460200936033.jpg

 

8.6 使用TOP和OFFSET-FETCH修改

SQL SERVER 支持在 INSERT,UPDATE,DELETE和MERGE 语句中直接使用TOP选项。当使用TOP选项时，只要指定的行数或百分比处理完成，SQL SERVER 就会停止处理修改语句。但是，不能为TOP选项指定一个ORDER BY 子句。从根本上讲，即SQL SERVER 首先访问到的语句将会受到修改影响的行。
一个TOP修改的典型使用场景是，当具有一个大型的修改操作时，如大量的删除操作，并且希望拆分成多个较小的块进行操作。
在T-SQL中 OFFSET-FETCH被视为ORDER BY子句的一部分。由于修改语句不支持ORDER BY子句，所以也不直接支持OFFSET-FETCH

IF OBJECT_ID('dbo.OrderDetails', 'U') IS NOT NULL DROP TABLE dbo.OrderDetails;
IF OBJECT_ID('dbo.Orders', 'U') IS NOT NULL DROP TABLE dbo.Orders;

CREATE TABLE dbo.Orders
(
  orderid        INT          NOT NULL,
  custid         INT          NULL,
  empid          INT          NOT NULL,
  orderdate      DATETIME     NOT NULL,
  requireddate   DATETIME     NOT NULL,
  shippeddate    DATETIME     NULL,
  shipperid      INT          NOT NULL,
  freight        MONEY        NOT NULL
    CONSTRAINT DFT_Orders_freight DEFAULT(0),
  shipname       NVARCHAR(40) NOT NULL,
  shipaddress    NVARCHAR(60) NOT NULL,
  shipcity       NVARCHAR(15) NOT NULL,
  shipregion     NVARCHAR(15) NULL,
  shippostalcode NVARCHAR(10) NULL,
  shipcountry    NVARCHAR(15) NOT NULL,
  CONSTRAINT PK_Orders PRIMARY KEY(orderid)
);

INSERT INTO dbo.Orders SELECT * FROM Sales.Orders;

DELETE TOP(50) FROM dbo.Orders

UPDATE TOP(50) dbo.Orders SET freight += 10.0

实际应用中，我们通常需要关心哪些行会受到DML语句的影响，不会希望任意选择的。为了避免这个问题，我们可以实际通过表表达式来修改数据。

;WITH C AS
(
    SELECT TOP(50) * FROM dbo.Orders o ORDER BY o.orderid
)
DELETE FROM C

;WITH C AS
(
    SELECT TOP(50) * FROM dbo.Orders o ORDER BY o.orderid DESC
)
UPDATE TOP(50) dbo.Orders SET freight += 10.0

在SQL SERVER 2012中，可以使用OFFSET-FETCH代替内部SELECT查询中的TOP选项。

;WITH C AS
(
    SELECT * FROM dbo.Orders o 
    ORDER BY o.orderid 
    OFFSET 0 ROWS FETCH FIRST 50 ROWS ONLY
)
DELETE FROM C

;WITH C AS
(
    SELECT * FROM dbo.Orders o 
    ORDER BY o.orderid 
    OFFSET 0 ROWS FETCH FIRST 50 ROWS ONLY
)
UPDATE TOP(50) dbo.Orders SET freight += 10.0

8.7 OUTPUT 子句

通常情况下，我们并不希望修改语句执行修改之外 的操作。但是，在某些情况下，能够从修改行获取返回数据可能会非常有用。例如，要示UPDATE语句不仅是修改数据而且返回更新列的旧值和新值优势，这可用于故障诊断、数据审核和其他用途。
通过向修改语句中添加OUTPUT子句，可以指定希望从修改行返回的属性和表达式。
OUTPUT 子句语法方面的特别之处在于，需要以inserted或deleted作为前缀。
OUTPUT 子句将从修改后的行返回所请求的属性来作为一个结果集，这非常类似于一个SELECT语句操作。如果希望直接将结果集输入到一个表中，那么可以添加一个带有目标表名称的INTO子句。如果希望返回修改后的行给调用方，并且还需要输出一个副本到表中，就需要指定两个OUTPUT子句——一个带有INTO子句，另一个没有INTO子句

8.7.1 INSERT与OUTPUT

IF OBJECT_ID('dbo.T1', 'U') IS NOT NULL DROP TABLE dbo.T1;
GO

CREATE TABLE dbo.T1
(
  keycol  INT          NOT NULL IDENTITY(1, 1) CONSTRAINT PK_T1 PRIMARY KEY,
  datacol NVARCHAR(40) NOT NULL
);
--
INSERT INTO dbo.T1(datacol)
  OUTPUT inserted.keycol, inserted.datacol
    SELECT lastname
    FROM HR.Employees
    WHERE country = N'USA'

 

enter description here

1460043891836.jpg

正如之前所说，也可以将结果集直接输入到一个表中（可以是真实表、临时表或表变量）。

 

DECLARE @newRows TABLE(keycol int, datacol varchar(40))
INSERT INTO dbo.T1(datacol)
 OUTPUT INSERTED.keycol, INSERTED.datacol
 INTO @newRows
   SELECT lastname FROM HR.Employees AS e 
   WHERE e.country = 'uk'

SELECT * FROM @newRows AS nr

 

enter description here

1460043900505.jpg

 

8.7.2 DELETE 与 OUTPUT

DELETE FROM dbo.T1
    OUTPUT DELETED.keycol, DELETED.datacol
WHERE keycol > 5 AND keycol < 10

 

enter description here

1460043914681.jpg

 

8.7.3 UPDATE 与 OUTPUT

通过UPDATE语句使用OUTPUT语句，可以引用被修改前的镜像（通过deleted）和修改后的镜像（通过inserted）。
enter description here
UPDATE前的T1表数据

UPDATE dbo.T1
    SET datacol += 'FISTNAME '
OUTPUT 
     INSERTED.datacol,
     DELETED.datacol AS old

 

enter description here

1460043951369.jpg

 

8.7.4 MERGE 与 OUTPUT

单个MERGE语句可以基于条件逻辑调用多个不同的DML操作。这意味着单个MERGE语句可以返回不同的DML操作产生的OUTPUT子句行。要确定是哪个DML产生的，可以在OUTPUT子句中调用一个名为$action函数，此函数返回一个代表操作的字符串（INSERT,UPDATE,DELETE）。
先运行8.4的代码，重新创建dbo.Customers和dbo.CustomersStage

MERGE INTO dbo.Customers AS TGT
USING dbo.CustomersStage AS SRC
  ON TGT.custid = SRC.custid
WHEN MATCHED THEN
  UPDATE SET
    TGT.companyname = SRC.companyname,
    TGT.phone = SRC.phone,
    TGT.address = SRC.address
WHEN NOT MATCHED THEN 
  INSERT (custid, companyname, phone, address)
  VALUES (SRC.custid, SRC.companyname, SRC.phone, SRC.address)
OUTPUT $action AS theaction, inserted.custid,
  deleted.companyname AS oldcompanyname,
  inserted.companyname AS newcompanyname,
  deleted.phone AS oldphone,
  inserted.phone AS newphone,
  deleted.address AS oldaddress,
  inserted.address AS newaddress;

 

enter description here

1460043977885.jpg

 

8.7.5 可组合的DML

OUTPUT子句为每个修改行返回一个输出行，但是，如果只需要一个修改行的子集，那该怎么做？SQL SERVER 支持从修改行的完全集合中将所需的行子集直接插入到最终的目标表中。

--创建Production.Products的副本到dbo中
IF OBJECT_ID('dbo.ProductsAudit', 'U') IS NOT NULL DROP TABLE dbo.ProductsAudit;
IF OBJECT_ID('dbo.Products', 'U') IS NOT NULL DROP TABLE dbo.Products;

CREATE TABLE dbo.Products
(
  productid    INT          NOT NULL,
  productname  NVARCHAR(40) NOT NULL,
  supplierid   INT          NOT NULL,
  categoryid   INT          NOT NULL,
  unitprice    MONEY        NOT NULL
    CONSTRAINT DFT_Products_unitprice DEFAULT(0),
  discontinued BIT          NOT NULL 
    CONSTRAINT DFT_Products_discontinued DEFAULT(0),
  CONSTRAINT PK_Products PRIMARY KEY(productid),
  CONSTRAINT CHK_Products_unitprice CHECK(unitprice >= 0)
);

INSERT INTO dbo.Products SELECT * FROM Production.Products;
--保存目标数据
CREATE TABLE dbo.ProductsAudit
(
  LSN INT NOT NULL IDENTITY PRIMARY KEY,
  TS  DATETIME NOT NULL DEFAULT(CURRENT_TIMESTAMP),
  productid INT NOT NULL,
  colname SYSNAME NOT NULL,
  oldval SQL_VARIANT NOT NULL,
  newval SQL_VARIANT NOT NULL
);

现在假设需要更新供应商1提供的所有产品，价格增长15%。同时还需要审核更新产品的旧值和新值，但是只有那些旧价格小于20和新价格大于或等于20的产品。

INSERT INTO dbo.ProductsAudit(productid,colname,oldval,newval)
SELECT productid,'unitprice',oldPrice,newPrice 
FROM(UPDATE dbo.Products
       SET unitprice *= 1.15
    OUTPUT 
       INSERTED.productid,
       DELETED.unitprice AS oldPrice,
       INSERTED.unitprice AS newPrice
    WHERE supplierid = 1) AS d
WHERE oldPrice < 20 AND newPrice >= 20

 

enter description here

1460043991896.jpg

 

-- 清理数据
IF OBJECT_ID('dbo.OrderDetails', 'U') IS NOT NULL DROP TABLE dbo.OrderDetails;
IF OBJECT_ID('dbo.ProductsAudit', 'U') IS NOT NULL DROP TABLE dbo.ProductsAudit;
IF OBJECT_ID('dbo.Products', 'U') IS NOT NULL DROP TABLE dbo.Products;
IF OBJECT_ID('dbo.Orders', 'U') IS NOT NULL DROP TABLE dbo.Orders;
IF OBJECT_ID('dbo.Customers', 'U') IS NOT NULL DROP TABLE dbo.Customers;
IF OBJECT_ID('dbo.T1', 'U') IS NOT NULL DROP TABLE dbo.T1;
IF OBJECT_ID('dbo.Sequences', 'U') IS NOT NULL DROP TABLE dbo.Sequences;
IF OBJECT_ID('dbo.CustomersStage', 'U') IS NOT NULL DROP TABLE dbo.CustomersStage;

练习

--1.创建dbo.Customers表
IF OBJECT_ID('dbo.Customers', 'U') IS NOT NULL DROP TABLE dbo.Customers;

CREATE TABLE dbo.Customers
(
  custid      INT          NOT NULL PRIMARY KEY,
  companyname NVARCHAR(40) NOT NULL,
  country     NVARCHAR(15) NOT NULL,
  region      NVARCHAR(15) NULL,
  city        NVARCHAR(15) NOT NULL  
);

--2.插入数据
INSERT INTO dbo.Customers(custid, companyname, country, region, city)
  VALUES(100, N'Coho Winery', N'USA', N'WA', N'Redmond');

--3.从Sales.Customers表插入所有具有订单的客户到dbo.Cusomters，相关表：Sales.Orders
INSERT INTO dbo.Customers(custid,companyname,country,region,city)
SELECT c.custid,c.companyname,c.country,c.region,c.city FROM Sales.Customers AS c
WHERE EXISTS(
SELECT 1 FROM Sales.Orders AS o WHERE c.custid = o.custid)

/*
4.使用SELECT INTO 语句创建 并填充dbo.Orders表，数据来自Sales.Orders表2006到2008年度的订单。ps:
*/
SELECT * INTO dbo.Orders 
FROM Sales.Orders AS o 
WHERE o.orderdate >= '20060101' AND o.orderdate < '20090101'

--5.从dbo.Orders表中删除2006年8月以前的订单，使用OUTPUT子句返回被订单删除订单的orderid和orderdate
DELETE FROM dbo.Orders 
OUTPUT DELETED.orderid, DELETED.orderdate
WHERE orderdate < '20060801'

--6. 从dbo.Orders表删除巴西(Brazil)客户订单，相关表:dbo.Customers
DELETE FROM dbo.Orders
WHERE EXISTS
  (SELECT * FROM dbo.Customers AS C
   WHERE dbo.Orders.custid = C.custid AND C.country = N'Brazil');

--7.更新dbo.Customers表中region列为NULL的值为<None>,使用OUTPUT子句显示 custid,oldregion和newregion
UPDATE c SET region = '<None>'
OUTPUT DELETED.custid, DELETED.region AS oldregion, INSERTED.region AS newregion
FROM dbo.Customers AS c 
WHERE region IS NULL

--8.更新dbo.Orders表中所有英国(UK)客户的订单，设置shipcountry,shipregion,shipcity为对应客户的country,region和city，相关表：dbo.Customers
--8.1 使用联接
UPDATE o SET o.shipregion = c.region, o.shipcountry = c.country, o.shipcity = c.city
FROM dbo.Customers AS c
    INNER JOIN dbo.Orders AS o ON c.custid = o.custid
WHERE country = 'UK'

--8.2 使用CTE
WITH CTE_UPD AS
(
    SELECT 
       o.shipcountry AS oCountry, o.shipregion AS oRegion, o.shipcity AS oCitry
       ,c.country AS cCountry, c.region AS cRegion, c.city AS cCity
    FROM dbo.Orders AS o
       INNER JOIN dbo.Customers AS c ON c.custid = o.custid
    WHERE c.country = 'UK'
)
UPDATE CTE_UPD SET CTE_UPD.oCountry = CTE_UPD.cCountry, CTE_UPD.oRegion = CTE_UPD.cRegion, CTE_UPD.oCitry = CTE_UPD.cCity;

--8.3 使用MERGE
MERGE INTO dbo.Orders AS o 
USING dbo.Customers AS c 
    ON c.custid = o.custid AND c.country = 'UK'
WHEN MATCHED THEN
    UPDATE SET o.shipcountry = c.country, o.shipregion = c.region, o.shipcity = c.city;

--清除数据
IF OBJECT_ID('dbo.Orders', 'U') IS NOT NULL DROP TABLE dbo.Orders;
IF OBJECT_ID('dbo.Customers', 'U') IS NOT NULL DROP TABLE dbo.Customers ;

  1.  

    enter description here

    1460044013659.jpg

  2.  

    enter description here

    1460044021511.jpg

Chapter 09 事务和并发处理

此章将解释SQL SERVER 如何使用锁定来隔离不一致数据，如何排除阻塞，以及使用隔离级别查询数据时如何控制一致性的级别。还介绍死锁和减少死锁发生的方法。

9.1 事务

事务是一个工作单元，可能 包含查询和修改数据以及修改数据定义等多个活动。
可以显式或隐式定义事务边界。可以使用BEGIN TRAN（或BEGIN TRANSACTION） 语句显式地定义事务的开始 。使用COMMIT TRAN 提交事务，显式地定义事务结束。回滚则是ROLLBACK TRAN。
如果不显式地标记事务的边界，默认情况下，SQL SERVER 将把每个单独语句作为一个事务。 可以通过一个IMPLICIT_TRANSACTIONS 的会话选项修改SQL SERVER 处理隐式事务的方式， 默认为OFF。当值为ON时，不需要指定BEGIN TRAN 语句标记事务的开始，但是必须以COMMIT TRAN或ROLLBACK TRAN语句标记事务的结束。
事务有4个属性——原子性（Atomicity），一致性（Consistency），隔离性（Isolation）和持续性（Durability），缩写为ACID

9.2 锁和阻塞

9.2.1 锁

锁是事务保护数据资源而获得的控制资源，防止其他事务的冲突或不兼容访问。
1. 锁的模式和兼容性
锁的模式主要为——排他锁和共享锁
当试图修改数据时，事务会请求数据资源的一个排他锁，而不管其隔离级别。如果授予了锁，排他锁直到事务结束才会解除。可使用COMMIT TRAN或ROLLBACK TRAN命令结束事务。
排他锁是如果一个事务持有资源的任何锁模式，你就不能在资源上获得排他锁；反之，如果一个事务持有资源的排他锁，就不能获得资源上的任何其他锁模式。这是默认的修改行为方式，然而，另一事务能不能读取修改的行，取决于它的隔离级别。
在SQL SERVER中，读取数据的默认级别是READ COMMITED。在这种隔离模式下，当尝试读取数据时，事务默认请求数据资源的共享锁，并且一旦语句完成资源的读取，就会立即释放锁。
事务之间锁的相互影响称为锁的兼容性。
2. 可锁定的资源类型
SQL SERVER 可以锁定不同类型的资源，包括RID或键（行）、页、对象（例如表）、数据库及其他。行驻留在页内，并且页是包含表或索引数据的物理数据块。
要获取一个特定的资源类型的锁，事务必须首先获取在更高粒度级别上的相同模式的意向锁。例如，要获取一个行上的排他锁，事务必须首先获取一个行所在页的意向排他锁和一个拥有该页对象的意向排他锁。

9.2.2 排除阻塞

在事务持有一个数据资源的锁，并且另一个事务请求同一资源的不兼容锁时，请求被阻塞并且请求者进入等待状态。在默认情况下，直到锁定者释放干扰锁，锁请求会一直等待。
只要在一个合理的一时间内满足请求，系统中的阻塞都是正常的。如果请求等待太久，则需要排除阻塞。
下面演示一个阻塞情况，并引导排除。SQL SERVER默认使用READ COMMITTED隔离级别。
打开3个独立的查询窗口。

  1. 查询1
    要更新行的话，会话必须获取一个排他锁，并且如果更新成功，SQL SERVER 会将锁授予会话。由于事务一直保持打开状态，所以一直会持有锁。

SELECT p.productid,
       p.unitprice
FROM Production.Products p --WITH (READCOMMITTEDLOCK)
WHERE p.productid = 2;

  1. 查询2
    尝试查询同一行，查询需要一个共享锁来读取数据，但是由于该行被其他会话以排他方式锁定了，并且共享锁和排他锁不兼容，所以会话会阻塞。

SELECT p.productid, p.unitprice FROM Production.Products p WHERE p.productid = 2

假设阻塞情况发生在你的数据库中，可以使用下面的查询来获取锁信息，包括当前授予会话的锁和会话等待的锁。

  1. 查询3

SELECT dtl.request_session_id AS spid, --进程id
       dtl.resource_type AS restype, --锁类型
       dtl.resource_database_id AS dbid, --数据库id
       DB_NAME(dtl.resource_database_id) AS dbname, 
       dtl.resource_description AS res, --资源
       dtl.resource_associated_entity_id AS resid, --资源i
       dtl.request_mode AS mode --锁模式
FROM sys.dm_tran_locks dtl;

 

enter description here

1460044052712.jpg

从图中可以看到，当前持有4个会话，其中57是查询2的进程id，57这个进程的状态为WAIT，一直在等待数据库中一个行上的共享锁。进程58在同一行上持有一个排他锁（res和resid与57的相同）。可以使用OBJECT_NAME函数转换resid得到对象名称。
在SMS在查询窗口底部可以查看spid。

 

 

enter description here

1460044065992.jpg

 

sys.dm_tran_locks视图仅提供了阻塞链中有关进程ID，要了解更多相关连接信息，可以查询sys.dm_exec_connections视图。

SELECT dec.session_id AS spid,
       dec.connect_time,
       dec.last_read,
       dec.last_write,
      dec.most_recent_sql_handle
FROM sys.dm_exec_connections dec
WHERE dec.session_id IN(57, 58);

 

enter description here

1460044080976.jpg

此查询提供的相关信息有：

 

  • 连接时间

  • 上次读取或写入时间

  • 连接中最近运行的SQL批处理所持有的句柄的二进制值。可以将该句柄作为输入参数提供给sys.dm_exec_sql_test表函数。

SELECT dec.session_id,
       dest.text
FROM sys.dm_exec_connections dec
     CROSS APPLY sys.dm_exec_sql_text(dec.most_recent_sql_handle) dest
WHERE dec.session_id IN(57, 58);

 

enter description here

1460044092813.jpg

 

还可以在DMV为sys.dm_exec_sessions中找到阻塞情况中涉及会话的更多有用信息。下面查询仅返回有关这些会话的可以属性。

SELECT des.session_id AS spid,
       des.login_time,
       des.host_name,
       des.program_name,
       des.login_name,
       des.nt_user_name,
       des.last_request_start_time,
       des.last_request_end_time
FROM sys.dm_exec_sessions des
WHERE des.session_id IN(57, 58);

 

enter description here

1460044106962.jpg

 

另一个DMV是sys.dm_exec_requests

SELECT der.session_id,
       der.blocking_session_id,
       der.command,
       der.sql_handle,
       der.database_id,
       der.wait_type,
       der.wait_time,
       der.wait_resource
FROM sys.dm_exec_requests der
WHERE der.blocking_session_id > 0;

 

enter description here

1460044133068.jpg

可以轻松得到阻止链中的会话、资源纠纷、阻塞的会话等。
如果需要终止阻塞者，可以使用KILL<spid>命令终止进程。
在默认情况下，会话没有锁超时的设置，如果要限制会话等待锁的时间，可以设置一个叫LOCK_TIMEOUT的会话选项。单位为毫秒，0为立即超时，-1为默认值。

 

SET LOCK_TIMEOUT 5000;
SELECT p.productid,
       p.unitprice
FROM Production.Products p --WITH (READCOMMITTEDLOCK)
WHERE p.productid = 2;

如果超时报的错

Msg 1222, Level 16, State 51, Line 2
Lock request time out period exceeded.

终止查询1

KILL 58;

此语句会导致查询1中的事务回滚，所以查询2查询的还是修改前的数据。

9.3 隔离级别

隔离级别确定了并发用户读取或写入的行为。
SWL SERVER 支持4个基于悲观并发控制（锁定）的传统隔离级别：READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ和SERIALIZABLE。隔离级别越高，读取者请求的锁就越强，并且持续时间就越长。所以，隔离级别越高，一致性越高并且并发性越低。
还支持2种基于乐观并发控制（行版本控制）的隔离级别：SHAPSHOT和READ COMMITTED SNAPSHOT，在某种意义上，这2个乐观锁分别是READ COMMITTED和SERIALIZABLE的乐观并发对应方式。

--设置整个会话的隔离级别
SET TRANSACTION ISOLATION LEVEL <isolation name>;--隔离名称在多个单词时，单词之间有空格

--可以使用一个表提示来设置查询的隔离级别
SELECT ... FROM <table> WITH (<isolationname>);--单词之间无空格

9.3.1 READ UNCOMMITTED 隔离级别

READ UNCOMMITTED是可用的最低隔离级别。读取者不需要请求共享锁，也不会与持有排他锁的写入者发生冲突，这意味着读取都可以读取未提交的更改（也称为脏读）。
下面做个脏读的示例：

--查询1
--修改前的unitprice为19.0
BEGIN TRAN;
    UPDATE Production.Products
    SET Production.Products.unitprice += 2 -- money
    WHERE Production.Products.productid = 2;

SELECT * FROM Production.Products p WHERE p.productid = 2

事务保持打开状态。查询1返回的结果如下图：

 

enter description here

1460044159916.jpg

在查询2中：

 

--查询2
--设置隔离级别READ UNCOMMITTED，得到的结果如上图一样。
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT * FROM Production.Products p WHERE p.productid = 2;

--在查询1中运行，价格半会改回19.0。
ROLLBACK TRAN;

9.3.2 READ COMMITTED 隔离级别

防止脏读的最低隔离级别是READ COMMITTED，这是企业版的默认级别。
将9.3.1的查询1直接运行，再将查询2的级别设置为READ COMMITTED，发现SELECT查询一直在阻塞中。我们可以在查询1中将事务提交COMMIT TRAN，返回到查询2中就直接看到了查询结果。
在锁的持续时间方面，READ COMMITTED隔离级别中，直到完成，读取者仅持有共享锁，它不会到事务一直持有锁，事实上，它甚至不会到语句结束。这意味着，在同一事务中的两次数据资源的读取之间，不会持有该资源的锁，因此，其他事务可以在这两次读取的间隙修改资源，并且读取者每次读取可能会获得不同的值，这种现象被称为不可重复读取或不一致解析。对于许多应用程序，这种现象是可以接受的，但是有些应用程序却不是这样。
完成后，将值设置回 19.0。

9.3.2 REPEATABLE READ 隔离级别

如果希望确保一同事务中的多次读取之间没有其他事务能够修改值，需要提升隔离级别到REPEATABLE READ。 在该隔离级别中，读取者不仅需要一个共享锁才能够进行读取，而且直到事务结束都持有锁。意味着只要读取者获取数据资源上的共享锁，直到读取者结束事务，都没有其他事务可以获取一个排他锁来修改该资源。这样，就能够确保得到的是可重复读取或是一致的解析。
下面在查询1 中设置会话的隔离级别为REPEATABLE READ，打开一个事务，并读取产品2的行。

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRAN;    
SELECT * FROM Production.Products p WHERE p.productid = 2

 

enter description here

1460044186000.jpg

由于查询1扔持有产品2行上的共享锁，共享锁会一直保持到事务结束。在查询2中尝试修改此行：

 

UPDATE Production.Products
SET Production.Products.unitprice += 2 -- money
WHERE Production.Products.productid = 2;

修改尝试发生阻塞。返回到查询1中，再次读取此行，并提交事务。

SELECT * FROM Production.Products p WHERE p.productid = 2
COMMIT TRAN;

 

enter description here

1460044195755.jpg

此次的查询结果与第一次读取的值是相同的。现在，读取者的事务已经提交并且释放了共享锁，在查询2中的修改者可以获得资源的排他锁并进行修改。
另一个防止的REPEATABLE READ的现象叫做丢失更新。丢失更新发生在两个事务读取同一个值时，基于它们读取的值进行计算，然后更新该值。在低于该级别的隔离级别中，在读取后不会在该资源上持有锁，两个事务都可以更新该值，并且是最后更新该值的事务覆盖另一个事务的更新。在REPEATABLE READ中，在第一次读取后双方会保持它们的共享锁，因此对于稍后的更新都不会获得一个排他锁，这样的情况就会导致死锁，并且阻止更新冲突。
完成后，还原值为 19.0。

 

9.3.4 SERIALIZABLE 隔离级别

在REPEATABLE READ该级别下运行，直到事务结束，读取者才会保持共享锁。因此，可以确保在事务中第一次读取的行能够重复读取。事务锁定的资源是第一次运行时发现的，在查询运行时那里并没有行，因此，同一事务中的第二次读取可能会返回新行，这些新行被称为幻影，这种读取被称为幻读。如果在读取之间，另一个事务添加了读取者查询筛选限定的新行，就会发生这种情况。
为了防止幻读，需要将隔离级别提升至SERIALIZABLE。最重要的部分是，SERIALIZABLE隔离级别的行为类似于REPEABLE READ，即：它要求读取者共聚一个共享锁来进行读取，并持有锁到事务结束。但是SERIALIZABLE隔离级别添加了另一个方面——在逻辑上，该隔离级别要求读取者锁定查询筛选所限定的键的整个范围。这意味着读取者会阻止其他事务尝试添加读取者查询筛选限定的行。
示例：
查询1

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRAN;
    SELECT p.productid,
         p.productname,
         p.categoryid,
         p.unitprice
    FROM Production.Products p
    WHERE p.categoryid = 1;

查询2

INSERT INTO Production.Products
(productname,
 supplierid,
 categoryid,
 unitprice,
 discontinued
)
VALUES(N'PRODUCT TEST', -- productname - nvarchar
       1, -- supplierid - int
       1, -- categoryid - int
       20.0, -- unitprice - money
       0 -- discontinued - bit
);

在低于SERIALIZABLE的所有级别中，上述语句是可以运行成功的。
回到查询1中，运行下面代码：

    SELECT p.productid,
         p.productname,
         p.categoryid,
         p.unitprice
    FROM Production.Products p
    WHERE p.categoryid = 1;
COMMIT TRAN;

 

enter description here

1460044211491.jpg

会得到数据插入前的结果。再一次运行会发现数据已经插入成功
enter description here

 

9.3.5 基于版本的隔离级别SNAPSHOT和READ COMMITTED SNAPSHOT

对于SQL SERVER，可以在tmpdb中存储已提交行的之前版本。在可以或是不可以发生的一致性问题方面，SNAPSHOT隔离级别在逻辑上类似于SERIALIZABLE；后者类似于READ COMMITTED SNAPSHOT。但是，读取者使用基于行版本控制的隔离级别不会发出共享锁，所以在请求的数据以排他方式锁定时它们不会等待，读取者仍旧会获得类似于SERIAZABLE和READ COMMITTED的一致性级别。
如果启用了任何基于快照的隔离级别，在修改tmpdb之前，DELETE和UPDATE语句需要复制行的版本。对于INSERT语句则不需要在tmpdb中版本化，因为它不存在早期的行版本。

9.3.5.1 SHAPSHOT 隔离级别

在此级别下，读取者在读取数据时，它是确保获得事务启动时最近提交的可用行版本。这就保证获得的是提交后的读取并且可以重复获取，以及确保获得的不是幻读。快照隔离级别是以发生为代价的，主要表现在更新和删除数据时。要想使用此隔离级别，可以执行以下代码，修改数据库选项：

ALTER DATABASE TSQL2012 SET ALLOW_SNAPSHOT_ISOLATION ON;

示例：
查询1：

BEGIN TRAN;
UPDATE Production.Products
  SET
      Production.Products.unitprice+=2 -- money 
WHERE Production.Products.productid = 2;

SELECT *
FROM Production.Products p
WHERE p.productid = 2;

 

enter description here

1460044230938.jpg

值得注意的是，即使查询1中的事务在READ COMMITTED下运行，SQL SERVER 也会复制更新到tempdb之前的行版本（19.00），这是因为SNAPSHOT已经启用。如果有人使用SNAPSHOT开始事务，那么他们可以更新之前的版本。
查询2：

 

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

BEGIN TRAN;

SELECT *
FROM Production.Products p
WHERE p.productid = 2;

 

enter description here

1460044244854.jpg

如果此查询在SERIALIZABLE下运行的，此查询会发生阻塞。
回到查询1中，提交修改事务COMMIT TRAN，再返回查询2中运行下述代码：

 

SELECT *
FROM Production.Products p
WHERE p.productid = 2;

COMMIT TRAN;

 

enter description here

1460044258098.jpg

不出所料，结果还是19.00。
在查询2中运行下述代码：

 

BEGIN TRAN;

SELECT *
FROM Production.Products p
WHERE p.productid = 2;

COMMIT TRAN;

这一次，事务启动时该行可用的是最后提交版本是 21.00 的版本，所以结果如下：
enter description here
现在，没有事务显示价格为 19.00 的版本的，每分钟运行一次的清理线程可以在下次运行时从tempdb中删除该版本了。
完成后设置价格回 19.00。

9.3.5.2 冲突检测

SNAPSHOT可以防止更新冲突，但不会像REPEATABLE READ和SERIALIAZABLE那样产生死锁，如果SNAPSHOT的事务失败，说明检测到了更新冲突。SNAPSHOT通过检查存储的版本来检测更新冲突，它可以发现在事务的读取和写入之间是否有另一个事务修改数据。
以下示例演示了一个水岸有更新冲突的情况，紧接着下一个示例是具有更新冲突的情况。
在连接1中运行下面的代码，设置事务的隔离级别为SNAPSHOT，找开一个事务并读取产品2的行。

-- 查询 1, Step 1
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRAN;
  SELECT productid, unitprice
  FROM Production.Products
  WHERE productid = 2;

 

enter description here

1460269746441.jpg

假设已经做过计算，继续在查询1运行以下代码，更新产品价格。

 

-- 查询 1, Step 2
-- Connection 1, Step 2
  UPDATE Production.Products
    SET unitprice = 20.00
  WHERE productid = 2;
COMMIT TRAN;

在读取、计算和写入之间没有其他事务修改行，因此没有冲突。
接下来，在查询1中运行下面代码，再次打开事务，并读取产品2的数据。

-- 查询 1, Step 1
BEGIN TRAN;
  SELECT productid, unitprice
  FROM Production.Products
  WHERE productid = 2;

 

enter description here

1460270046916.jpg

这一次，在查询2中运行下面代码，将价格设置为25.00

 

-- 查询 2, Step 1
UPDATE Production.Products
  SET unitprice = 25.00
WHERE productid = 2;

返回查询1中将价格更新回19.00

-- 查询 2, Step 1
UPDATE Production.Products
  SET unitprice = 25.00
WHERE productid = 2;

这次SQL SERVER 检测到在读取和写入之间另一个事务修改了数据，因此事务会失败，错误信息如下：

Msg 3960, Level 16, State 2, Line 1
Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation to access table 'Production.Products' directly or indirectly in database 'TSQL2012' to update, delete, or insert the row that has been modified or deleted by another transaction. Retry the transaction or change the isolation level for the update/delete statement.

9.3.5.3 READ COMMITTED SNAPSHOT隔离级别

该级别也是基于行版本控制的，与SNAPSHOT不同的是，读取者获得是“语句”启动时可用的最后提交的行版本，而不是“事务”，启动时可用的最后提交的行版本。

--在数据库中启用READ cOMMITTED SNAPSHOT
ALTER DATABASE TSQL2012 SET READ_COMMITTED_SNAPSHOT ON;

值得注意的是，要使此代码运行成功，此代码的连接必须是TSQL2012数据库的唯一连接。好吧，我即使这么做了，还是像下图一样，代码一直在查询，如下图。
enter description here

9.4 死锁

死锁是两个或多个进程互相阻塞的情况。在任何情况下，SQL SERVER 检测到死锁，都会通过终止其中一个事务进行干预，如果不干预，涉及的进程会永远陷于死锁状态。
除非另外指定，否则SQL SERVER 会选择终止工作最少的事务，因为这便于事务进行回滚。但是，SQL SERVER 允许用户设置DEADLOCK_PRIOPRITY选项，可以在范围内（-10~10）进行选择，死锁优先级最低的进程将被作为“牺牲对象”，而不管其做了多少工作。在平级的情况下，将使用工作数量作为决胜属性（tiebreaker）。
示例：
查询1：

BEGIN TRAN; 
UPDATE Production.Products  
    SET unitprice += 2.00 
WHERE productid = 2;

查询2：

BEGIN TRAN; 
UPDATE Sales.OrderDetails  
    SET unitprice += 2.00 
WHERE productid = 2;

再在查询1中运行下列代码，尝试查询 Sales.OrderDetails 表中的 productid = 2的行，并提交事务。

SELECT * FROM Sales.OrderDetails od WHERE od.productid = 2; 
COMMIT TRAN;

查询1的事务需要一个共享锁执行资源的读取，由于查询2中的事务持有一个排他锁，所以查询1的事务被阻塞。此时具有了一个阻塞情况，但还没有死锁。
接下来，在查询2中运行以下代码，尝试查询 Production.Products 中产品2的行并提交事务。

SELECT * FROM Production.Products p WHERE p.productid = 2; 
COMMIT TRAN;

为了能够执行读取，查询2中事务需要 Production.Products 表中产品2的共享锁，所以此请求现在与持有相同资源的排他锁的查询1发生冲突。两个进程都相互阻塞了另一个线程——这就具有了一个死锁。SQL SERVER检测到死锁（通常在几秒内），会选择两个进程之一作为死锁牺牲品，并终止其事务，出现以下错误。
enter description here
显然，事务时间越长，锁保持的时间越长，增加了死锁的可能性，你应该尽可能地缩短事务时间，剔除逻辑上不应是同一工作单元的事务。
并不存在真正的逻辑冲突，如果经常发生死锁，原因是缺乏良好的索引来支持查询筛选。所以，良好的索引设计可以减少发生真正逻辑冲突的死锁。
减少死锁发生时需要考虑的另一个选项是选择隔离级别。在此示例中，运行在READ COMMITTED下，它们需要共享锁。如果你使用了READ COMMITTED SNAPSHOT，则读取者不需要共享锁，可以消除此类涉及共享锁的死锁。
完成后清理代码。

练习

CHAPTER 10 可编程对象

10.1 变量

使用DECLARE来进行变量声明，然后使用SET给单个变量赋值，也可以使用SELECT来给多个变量来进行赋值。
SQL SERVER 2008以上版本支持变量的初始化。

DECLARE @i AS INT = 10;
SET @i = 11

10.2 批

批是由客户端应用程序作为一个单元发送给SQL SERVER执行的一条或多条T-SQL语句。
不要混淆事务和批。事务是工作的原子单元，一个批可以有多个事务，一个事务可以分成多个批提交。当事务中途被取消或回滚时，SQL SERVER 会撤销自事务开始后发生的活动部分，而与批开始无关。
SMS、SQLCMD和OSQL提供了一个叫做GO的命令平提示结束一个批。注意，GO是一个客户端命令，而不是一个T-SQL服务器命令。

10.2.1 以批为单元的语法分析

批是作为一个单元分析和执行的命令级。如果分析成功，然后SQL SERVER 将尝试执行该批。

--有效批
PRINT 'FIRST BLOCK';
USE TSQL2012;
GO
--无效批
PRINT 'SECOND BLOCK';
SELECT c.custid FROM Sales.Customers c;
SELECT o.orderid1 FROM Sales.Orders o;
GO
--有效批
PRINT 'THIRD BLOCK';
SELECT e.empid FROM HR.Employees e;

由于第1和第3个批通过了语法验证，因此提交执行了。
enter description here

10.2.2 批和变量

变量对于定义它的批而言是局部化的，如果尝试引用在另一个批中定义的变量，就会收到一条错误说该变量未定义。

DECLARE @i INT; 
SET @i = 10; 
--success 
PRINT @i; 
GO 
--error 
PRINT @i;

 

enter description here

1460205532886.jpg

 

10.2.3 不能组合在同一个批中的语句

以下不能与其他语句组合在同一个批中：CREATE DEFAULT、CREATE FUNCTION、CREATE PROCEDURE、CREATE RULE、CREATE SCHEMA、CREATE TRIGGER和CREATE VIEW。
例如，下面同一个批中具有一个IF语句，后面跟着CREATE VIEW语句，所以是无效的。

IF OBJECT_ID('Sales.MyView','V') IS NOT NULL DROP VIEW Sales.MyView; 
CREATE VIEW Sales.MyView 
AS 
SELECT YEAR(orderdate) FROM Sales.Orders ;

 

enter description here

1460205600466.jpg

为了避免此类问题，可以在IF语句后面添加GO命令，将IF和CREATE VIEW语句分隔到不同的批中。

 

10.2.4 以批为单元的解析

批是一个解析单元，这意味着检查对象和列是否存在发生在批级。在设计批的边界时，就牢记这个事实。当用户硐一批中将架构更改应用到对象并尝试操作对象数据时，SQL SERVER 可能不知道它们的架构更改，并且因解析错误导致数据操作语句失败。

--先运行下面代码，创建一个表 
IF OBJECT_ID('dbo.T1','U') IS NOT NULL DROP TABLE dbo.T1; 
CREATE TABLE dbo.T1(col1 INT); 
--再运行下面代码，添加一列 
ALTER TABLE dbo.T1 ADD col2 INT; 
SELECT t.* FROM dbo.T1 t

上面代码看起来是有效的，但是解析时会出现下面的错误。
enter description here
在SELECT语句解析的时点，T1只有col1 列，引用clo2 会导致错误。避免此类问题的做法就是将DDL和DML语句分隔到不同批次中。如：

ALTER TABLE dbo.T1 ADD col2 INT; 
GO 
SELECT col1,col2 FROM dbo.T1 t

在实际操作中，自己发现了下面一个方法 ，也可以查询出col2，就是用来查询所有行。这样能够成功的原因可能是用绕过了SSMS客户端的检查，所以才能运行成功。

ALTER TABLE dbo.T1 ADD col2 INT; 
SELECT * FROM dbo.T1 t

10.2.5 GO n 选项

GO命令不是真正的T-SQ命令，它实际上是一个用于SQL SERVER 客户端工具的命令。此命令支持一个打比赛要执行批多少次的参数。

SET NOCOUNT ON; 
--执行insert语句100次 
INSERT dbo.T1 DEFAULT VALUES; 
GO 100 
SELECT * FROM dbo.T1 t 
查询返回1~100.

10.3 流元素

10.3.1 IF...ELSE

T-SQL使用三值逻辑，所以ELSE块的执行条件其实是FALSE或UNKONW。IF或ELSE运行不止一条SQL，那么需要使用BEGIN...END来作为语句块边界。

10.3.2 WHILE

WHILE后面的执行条件是TRUE。WHILE也支持BREAK和CONTINUE命令跳出循环或继续下一循环

10.4 游标（CURSOR）

没有ORDER BY子句的查询语句返回的是一个集合（或多重集合），而具有此子句返回的是标准SQL，称之为“游标”——行间具有确定顺序的非关系型结果。
一般地，应该默认使用基于集合的查询，只有迫不得已时，否则不应该考虑使用游标，原因如下：

  1. 首先并且是重要的原因是当使用游标时，几乎违背了基于集合理论的关系模型。

  2. 游标所做的按记录的逐个操作需要耗费一定的开销。所以，才能会差一些。

  3. 如果使用游标，解决方案的物理方面会花费大量的代码。造成代码的可读性较差，维护较为困难等。

使用游标就像使用鱼竿钓鱼，一次钓一条鱼。而使用集合就像用网捕鱼，一次捕捞一群鱼。考虑使用游标的情况应该是需要应用一个特定的任务到表或视图的每一行时，或者是基于集合的解决方案不是很理想时。
游标的使用步骤：

  1. 基于查询声明游标。

  2. 打开游标。

  3. 从第一个游标记录中提取属性值给变量。

  4. 遍历游标记录，直到到达游标的末尾（全局变量==@@FETCH_STATUS==为0时）；在循环中的每次迭代中，从游标的当前记录中提取属性值给变量，并为当前行执行所需的处理。

  5. 关闭游标。

  6. 释放游标。

示例：

SET NOCOUNT ON;
DECLARE @Result TABLE
(
    custid INT, ordermonth DATETIME, qty INT, runqty INT, PRIMARY KEY(custid, ordermonth)
);

DECLARE @custid AS INT, @prvcustid AS INT, @ordermonth DATETIME, @qty AS INT, @runqty AS INT;
--1.声明
DECLARE C CURSOR FAST_FORWARD /* read only, forward only */
FOR SELECT custid, ordermonth, qty
    FROM   Sales.CustOrders
    ORDER BY custid, ordermonth;
--2.打开游标
OPEN C;
--3.第一次赋值给游标
FETCH NEXT FROM C INTO @custid, @ordermonth, @qty;
SELECT @prvcustid = @custid, @runqty = 0;
--4.遍历游标
WHILE @@FETCH_STATUS = 0
BEGIN
    IF @custid <> @prvcustid
    BEGIN
        SELECT @prvcustid = @custid, @runqty = 0
    END;
    SET @runqty = @runqty + @qty;
    INSERT INTO @Result VALUES  ( @custid, @ordermonth, @qty, @runqty );
    --每次遍历赋值
    FETCH NEXT FROM C INTO @custid, @ordermonth, @qty;
END;
--5.关闭游标
CLOSE C;
--6.释放游标
DEALLOCATE C;

SELECT custid, CONVERT(VARCHAR(7), ordermonth, 121) AS ordermonth, qty, runqty
FROM   @Result
ORDER BY custid, ordermonth;

 

enter description here

1460207472690.jpg

如第7章所述，使用增加开窗函数，可以使用户可以提供更高效的解决方案。

 

SELECT custid, ordermonth, qty, SUM(qty) OVER(PARTITION BY custid ORDER BY ordermonth ROWS UNBOUNDED PRECEDING) AS runqty
FROM   Sales.CustOrders
ORDER BY custid, ordermonth;

10.5 临时表

SQL SERVER支持3种临时表：本地临时表、全局临时表和表变量

10.5.1 本地临时表

本地临时表像创建一般的表一样，只是表名使用#来作为前缀。本地临时表只对创建它的会话可见。
使用本地临时表的一种情况是，当有一个需要存储中间临时结果的过程时，并且在后面还要查询该数据。另一种情况是当需要多次访问某些昂贵的处理的结果时。如，需要使用两个表的联合查询结果，但是如果每次使用都去做联接查询所耗费的资源是昂贵的，所以我们可以直接把结果保存起来，这样只需要作一次联接查询就可以了。

10.5.2 全局临时表

全局临时表对所有的会话可见。但是当创建临时表的会话连接断开了并且没有对该表的活动引用时，SQL SERVER 自动销毁该表。可以使用##来创建。

10.5.1 表变量

表变量在某些方面类似于本地临时表，但是表变量的创建是使用DECLARE来声明的。
就像本地临时表一样，表变量在tmpdb数据库中是一个物理存在的表，而不是觉的误解认为的仅存在于内存中。表变量仅对创建会话可见，但它们的范围更有限：只是对当前批可见。表变量不对调用堆栈内的内部批可见，也不对会话中后续批可见。
如果一个显式事务进行了回滚，那么事务中对临时表的修改也会回滚。但是，事务中由语句对表变量已完成的修改不会回滚。只有失败的活动语句所做或是语句完成前被终止的，表变量才会被撤销。
一般地，对于少量的数据（几行）使用表变量更有意义，否则应当使用本地临时表。


CREATE TABLE #MyOrderTotalsByYear
(
  orderyear INT NOT NULL PRIMARY KEY,
  qty       INT NOT NULL
);

INSERT INTO #MyOrderTotalsByYear(orderyear, qty)
  SELECT
    YEAR(O.orderdate) AS orderyear,
    SUM(OD.qty) AS qty
  FROM Sales.Orders AS O
    JOIN Sales.OrderDetails AS OD
      ON OD.orderid = O.orderid
  GROUP BY YEAR(orderdate);

SELECT Cur.orderyear, Cur.qty AS curyearqty, Prv.qty AS prvyearqty
FROM dbo.#MyOrderTotalsByYear AS Cur
  LEFT OUTER JOIN dbo.#MyOrderTotalsByYear AS Prv
    ON Cur.orderyear = Prv.orderyear + 1;

 

enter description here

1460208988543.jpg

 

10.5.4 表类型

SQL SERVER 2008支持表类型，当创建了表类型，就会在数据库中保留表的定义，可以在以后重新使用它作为表变量的表定义，以及作为存储过程和用户定义函数的输入参数。
例如，下面代码在当前数据库中创建了一个名为dbo.OrderTotalsByYear的表类型

CREATE TYPE dbo.OrderTotalsByYear AS TABLE
(
  orderyear INT NOT NULL PRIMARY KEY,
  qty       INT NOT NULL
);
GO

-- Use table type
DECLARE @MyOrderTotalsByYear AS dbo.OrderTotalsByYear;

INSERT INTO @MyOrderTotalsByYear(orderyear, qty)
  SELECT
    YEAR(O.orderdate) AS orderyear,
    SUM(OD.qty) AS qty
  FROM Sales.Orders AS O
    JOIN Sales.OrderDetails AS OD
      ON OD.orderid = O.orderid
  GROUP BY YEAR(orderdate);

SELECT orderyear, qty FROM @MyOrderTotalsByYear;
GO

 

enter description here

1460209712947.jpg

 

10.6 动态SQL

SQL SERVER提供了两种执行动态SQL:使用EXEC命令和sp_executesql存储。过程

10.6.1 EXEC

EXEC是T-SQL提供的执行动态SQL原始技术。EXEC()括号内可以使用varhcar或nvarchar类型的字符串。支持正则和Uunicode字符串输入。

DECLARE @sql AS VARCHAR(100);
SET @sql = 'PRINT ''This message was printed by a dynamic SQL batch.'';';
EXEC(@sql);

10.6.1 sp_executesql 存储过程

sp_executesql仅支持Unicode（nvarchar）作为输入代码批。

DECLARE @sql AS NVARCHAR(100);

SET @sql = N'SELECT orderid, custid, empid, orderdate
FROM Sales.Orders
WHERE orderid = @orderid;';

EXEC sys.sp_executesql
  @stmt = @sql,
  @params = N'@orderid AS INT',
  @orderid = 10248;

10.6.3 使用动态SQL的PIVOT

静态PIVOT

SELECT *
FROM (SELECT shipperid, YEAR(orderdate) AS orderyear, freight
      FROM Sales.Orders) AS D
  PIVOT(SUM(freight) FOR orderyear IN([2006],[2007],[2008])) AS P;

即当PIVOT运算符的IN子句中指定哪些值不确定时可以使用动态SQL来构造。如：

DECLARE
  @sql       AS NVARCHAR(1000),
  @orderyear AS INT,
  @first     AS INT;

DECLARE C CURSOR FAST_FORWARD FOR
  SELECT DISTINCT(YEAR(orderdate)) AS orderyear
  FROM Sales.Orders
  ORDER BY orderyear;

SET @first = 1;

SET @sql = N'SELECT *
FROM (SELECT shipperid, YEAR(orderdate) AS orderyear, freight
      FROM Sales.Orders) AS D
  PIVOT(SUM(freight) FOR orderyear IN(';

OPEN C;

FETCH NEXT FROM C INTO @orderyear;

WHILE @@fetch_status = 0
BEGIN
  IF @first = 0
    SET @sql = @sql + N','
  ELSE
    SET @first = 0;

  SET @sql = @sql + QUOTENAME(@orderyear);

  FETCH NEXT FROM C INTO @orderyear;
END

CLOSE C;

DEALLOCATE C;

SET @sql = @sql + N')) AS P;';

EXEC sys.sp_executesql @stmt = @sql;

 

enter description here

1460210340840.jpg
`

 

10.7 例程

例程是封装了代码的可编程对象，用于计算结果或执行活动。SQL SERVER 支持3种例程：用户定义函数、存储过程和触发器。

10.7.1 用户字义函数（UDF）

用户字义函数（UDF）的目的是用于封装计算逻辑，基于所输入的参数并返回一个结果。
SQL SERVER支持标题UDF和表值UDF。标题UDF返回单个值，表值UDF返回一个表。
UDF不允许有任何副作用，这显然意味着UDF不允许应用于任何架构或是数据库中的数据修改，但是导致副作用的其他方法并不那么明显。例如，调用RAND函数返回一个随机值或是调用NEWID函数返回一个全局唯一标识符（GUID）具有副作用。由于RANK和NEWID有副作用，不允许把它们旋转在UDF中。

CREATE FUNCTION dbo.GetAge
(
  @birthdate AS DATE,
  @eventdate AS DATE
)
RETURNS INT
AS
BEGIN
  RETURN
    DATEDIFF(year, @birthdate, @eventdate)
    - CASE WHEN 100 * MONTH(@eventdate) + DAY(@eventdate)
              < 100 * MONTH(@birthdate) + DAY(@birthdate)
           THEN 1 ELSE 0
      END;
END;

--使用
SELECT
  empid, firstname, lastname, birthdate,
  dbo.GetAge(birthdate, SYSDATETIME()) AS age
FROM HR.Employees;

 

enter description here

1460210805458.jpg

 

10.7.2 存储过程

存储过程是封装T-SQL代码的服务器端例程。存储过程可以具有输入和输出参数，可以返回查询的结果集，并允许具有副作用的调用代码。

CREATE PROC Sales.GetCustomerOrders
  @custid   AS INT,
  @fromdate AS DATETIME = '19000101',
  @todate   AS DATETIME = '99991231',
  @numrows  AS INT OUTPUT
AS
SET NOCOUNT ON;

SELECT orderid, custid, empid, orderdate
FROM Sales.Orders
WHERE custid = @custid
  AND orderdate >= @fromdate
  AND orderdate < @todate;

SET @numrows = @@rowcount;
GO

DECLARE @rc AS INT;

EXEC Sales.GetCustomerOrders
  @custid   = 1, -- Also try with 100
  @fromdate = '20070101',
  @todate   = '20080101',
  @numrows  = @rc OUTPUT;

SELECT @rc AS numrows;

 

enter description here

1460211008300.jpg

 

10.7.3 触发器

触发器是一种特殊类型的存储过程——它无法显式执行，相反，它依附于一个事件。SQL SERVER支持与两类事件关联的触发器：一类是数据库操作事件（DML触发器），如INSERT；另一类是数据库定义事件（DDL触发器），如CREATE TABLE。
触发器被认为是事务的一部分，包括导致触发器触发事件。在触发器代码中发出ROLLBACK命令会导致事件触发器所有发生的修改回滚，并且还有与触发器相关联的事务内所生的修改回滚。
1.DML触发器
DML触发器有两种类型——after和instead of。after触发器在与相关联事件完成后触发，并且只能在永久表上定义。instead of触发器是在代替与其相关联的事件时触发，并且能够在永久表和视图上定义。
在触发器代码中，可以访问名为inserted和deleted的表，其中包含了导致触发器触发受修改影响的行。inserted表保存INSERT和UPDATE操作情况下受影响的行的新镜像，deleted表保存关DELETE和UPDATE操作下受影响行的旧镜像。
下面是一个after触发器示例，审核插入到表中的行。


CREATE TABLE dbo.T1
(
  keycol  INT         NOT NULL PRIMARY KEY,
  datacol VARCHAR(10) NOT NULL
);

CREATE TABLE dbo.T1_Audit
(
  audit_lsn  INT         NOT NULL IDENTITY PRIMARY KEY,
  dt         DATETIME    NOT NULL DEFAULT(SYSDATETIME()),
  login_name sysname     NOT NULL DEFAULT(ORIGINAL_LOGIN()),
  keycol     INT         NOT NULL,
  datacol    VARCHAR(10) NOT NULL
);
GO

CREATE TRIGGER trg_T1_insert_audit ON dbo.T1 AFTER INSERT
AS
SET NOCOUNT ON;

INSERT INTO dbo.T1_Audit(keycol, datacol)
  SELECT keycol, datacol FROM inserted;
GO

INSERT INTO dbo.T1(keycol, datacol) VALUES(10, 'a');
INSERT INTO dbo.T1(keycol, datacol) VALUES(30, 'x');
INSERT INTO dbo.T1(keycol, datacol) VALUES(20, 'g');

SELECT audit_lsn, dt, login_name, keycol, datacol
FROM dbo.T1_Audit;

 

enter description here

1460211736899.jpg

2.DDL触发器
DDL触发器支持创建数据库和服务器两个范围内的触发器。在触发器内，通过查询EVENTDATA函数返回一个XML值来得到导致触发器触发的事件信息，可以通过XQuery表达式从该XML值中提取事件属性。

 

IF OBJECT_ID('dbo.AuditDDLEvents', 'U') IS NOT NULL
  DROP TABLE dbo.AuditDDLEvents;

CREATE TABLE dbo.AuditDDLEvents
(
  audit_lsn        INT      NOT NULL IDENTITY,
  posttime         DATETIME NOT NULL,
  eventtype        sysname  NOT NULL,
  loginname        sysname  NOT NULL,
  schemaname       sysname  NOT NULL,
  objectname       sysname  NOT NULL,
  targetobjectname sysname  NULL,
  eventdata        XML      NOT NULL,
  CONSTRAINT PK_AuditDDLEvents PRIMARY KEY(audit_lsn)
);

--创建触发器
CREATE TRIGGER trg_audit_ddl_events
  ON DATABASE FOR DDL_DATABASE_LEVEL_EVENTS
AS
SET NOCOUNT ON;

DECLARE @eventdata AS XML = EVENTDATA();

INSERT INTO dbo.AuditDDLEvents(
  posttime, eventtype, loginname, schemaname, 
  objectname, targetobjectname, eventdata)
  VALUES(
    @eventdata.value('(/EVENT_INSTANCE/PostTime)[1]',         'VARCHAR(23)'),
    @eventdata.value('(/EVENT_INSTANCE/EventType)[1]',        'sysname'),
    @eventdata.value('(/EVENT_INSTANCE/LoginName)[1]',        'sysname'),
    @eventdata.value('(/EVENT_INSTANCE/SchemaName)[1]',       'sysname'),
    @eventdata.value('(/EVENT_INSTANCE/ObjectName)[1]',       'sysname'),
    @eventdata.value('(/EVENT_INSTANCE/TargetObjectName)[1]', 'sysname'),
    @eventdata);
    
CREATE TABLE dbo.T1(col1 INT NOT NULL PRIMARY KEY);
ALTER TABLE dbo.T1 ADD col2 INT NULL;
ALTER TABLE dbo.T1 ALTER COLUMN col2 INT NOT NULL;
CREATE NONCLUSTERED INDEX idx1 ON dbo.T1(col2);
GO

SELECT * FROM dbo.AuditDDLEvents;

 

enter description here

1460212102796.jpg

 

 

enter description here

1460212163630.jpg

 

10.8 错误处理

使用TRY...CATCH构造，应当将T-SQL代码放在TRY块中，所有错误处理代码旋转在紧邻的CATCH块中。如果TRY块中没有错误，就会跳过CATCH块；如果有错，控制就会被传递给相应的CATCH块。
通常情况下，CATCH块中的错误处理会涉及一些操作，包括调查错误的原因并相应地采取某种操作。如：ERROR_NUMBER函数返回一个代表错误编号的整数，并且这可能是最重要的错误函数。ERROR_MESSAGE函数返回错误消息文本，要获取错误编号和消息的列表，可以查询sys.messages目录视图。ERROR_SERVERITY和ERROR_STAGE函数返回错误的严重性和状态。ERROR_LINE返回发生错误的行号。最后ERROR_PROCEDURE返回发生错误的过程名称，并在过程没有发生错误时返回NULL。
示例：

IF OBJECT_ID('dbo.Employees') IS NOT NULL DROP TABLE dbo.Employees;
CREATE TABLE dbo.Employees
(
  empid   INT         NOT NULL,
  empname VARCHAR(25) NOT NULL,
  mgrid   INT         NULL,
  CONSTRAINT PK_Employees PRIMARY KEY(empid),
  CONSTRAINT CHK_Employees_empid CHECK(empid > 0),
  CONSTRAINT FK_Employees_Employees
    FOREIGN KEY(mgrid) REFERENCES dbo.Employees(empid)
);

--插入操作
BEGIN TRY

  INSERT INTO dbo.Employees(empid, empname, mgrid)
    VALUES(1, 'Emp1', NULL);
  -- Also try with empid = 0, 'A', NULL

END TRY
BEGIN CATCH

  IF ERROR_NUMBER() = 2627
  BEGIN
    PRINT 'Handling PK violation...';
  END
  ELSE IF ERROR_NUMBER() = 547
  BEGIN
    PRINT 'Handling CHECK/FK constraint violation...';
  END
  ELSE IF ERROR_NUMBER() = 515
  BEGIN
    PRINT 'Handling NULL violation...';
  END
  ELSE IF ERROR_NUMBER() = 245
  BEGIN
    PRINT 'Handling conversion error...';
  END
  ELSE
  BEGIN
    PRINT 'Re-throwing error...';
    THROW; -- SQL Server 2012 only
  END

  PRINT 'Error Number  : ' + CAST(ERROR_NUMBER() AS VARCHAR(10));
  PRINT 'Error Message : ' + ERROR_MESSAGE();
  PRINT 'Error Severity: ' + CAST(ERROR_SEVERITY() AS VARCHAR(10));
  PRINT 'Error State   : ' + CAST(ERROR_STATE() AS VARCHAR(10));
  PRINT 'Error Line    : ' + CAST(ERROR_LINE() AS VARCHAR(10));
  PRINT 'Error Proc    : ' + COALESCE(ERROR_PROCEDURE(), 'Not within proc');
 
END CATCH;

第一次运行插入代码时是成功的，因此会跳过CATCH块。
当第二次运行时，INSERT语句会失败，CATCH块就会捕捉到错误。根据输出得到下面输出：
enter description here

BEGIN TRY

  INSERT INTO dbo.Employees(empid, empname, mgrid)
    VALUES(1, 'Emp1', NULL);
END TRY
BEGIN CATCH

THROW

END CATCH;

抛出错误信息也可以直接使用THROW，这是SQL SERVER 2012版本开始支持的特性。
enter description here





test I want to try camp
返回顶部
posted @ 2016-04-10 20:36  无名的风吹草地  阅读(1795)  评论(0)  编辑  收藏  举报