This chapter provides a brief overview of programmable objects to familiarize you with the capabilities of T-SQL in this area and with the concepts involved. 本章简要概述了可编程对象，以使您熟悉T-SQL在这一领域的功能以及所涉及的概念。The chapter covers variables; batches; flow elements; cursors; temporary tables; routines such as user-defined functions, stored procedures, and triggers; and dynamic SQL.本章涵盖变量；批次；流量元件；游标；临时表格；例程，如用户定义的函数、存储过程和触发器；和动态SQL。
You use variables to temporarily store data values for later use in the same batch in which they were declared. 您可以使用变量临时存储数据值，以便以后在声明它们的同一批中使用。I describe batches later in this chapter, but for now, the important thing for you to know is that a batch is one T-SQL statement or more sent to Microsoft SQL Server for execution as a single unit.我将在本章后面介绍批处理，但就目前而言，您需要知道的重要一点是，批处理是将一条或多条T-SQL语句作为单个单元发送到Microsoft SQL Server以供执行。
Use a 使用DECLARE statement to declare one or more variables, and use a SET statement to assign a value to a single variable. DECLARE语句声明一个或多个变量，并使用SET语句为单个变量赋值。For example, the following code declares a variable called 例如，以下代码声明了一个@i of an INT data type and assigns it the value 10:INT数据类型的名为@i的变量，并为其赋值10：
DECLARE @i AS INT;
SET @i = 10;
Alternatively, you can declare and initialize a variable in the same statement, like this:或者，可以在同一语句中声明和初始化变量，如下所示：
DECLARE @i AS INT = 10;
When you assign a value to a scalar variable, the value must be the result of a scalar expression. 为标量变量赋值时，该值必须是标量表达式的结果。The expression can be a scalar subquery. 表达式可以是标量子查询。For example, the following code declares a variable called 例如，以下代码声明了一个名为@empname and assigns it the result of a scalar subquery that returns the full name of the employee with an ID of 3:@empname的变量，并将标量子查询的结果赋给它，该子查询返回ID为3的员工的全名：
USE TSQLV4;
DECLARE @empname AS NVARCHAR(61);
SET @empname = (SELECT firstname + N' ' + lastname
FROM HR.Employees
WHERE empid = 3);
SELECT @empname AS empname;
This code returns the following output:此代码返回以下输出：
empname
----------
Judy Lew
The SET statement can operate on only one variable at a time, so if you need to assign values to multiple variables, you need to use multiple SET statements. SET语句一次只能对一个变量进行操作，因此如果需要为多个变量赋值，则需要使用多个SET语句。This approach can involve unnecessary overhead when you need to pull multiple attribute values from the same row. 当需要从同一行提取多个属性值时，这种方法可能会带来不必要的开销。For example, the following code uses two separate 例如，以下代码使用两个单独的SET statements to pull both the first and last names of the employee with the ID of 3 to two separate variables:SET语句将ID为3的员工的名字和姓氏拉入两个单独的变量：
DECLARE @firstname AS NVARCHAR(20), @lastname AS NVARCHAR(40);
SET @firstname = (SELECT firstname
FROM HR.Employees
WHERE empid = 3);
SET @lastname = (SELECT lastname
FROM HR.Employees
WHERE empid = 3);
SELECT @firstname AS firstname, @lastname AS lastname;
This code returns the following output:此代码返回以下输出：
firstname lastname
---------- ---------
Judy Lew
T-SQL also supports a nonstandard assignment T-SQL还支持非标准的赋值SELECT statement, which you use to query data and assign multiple values obtained from the same row to multiple variables by using a single statement. SELECT语句，您可以使用该语句查询数据，并使用单个语句将从同一行获得的多个值赋给多个变量。Here's an example:下面是一个示例：
DECLARE @firstname AS NVARCHAR(20), @lastname AS NVARCHAR(40);
SELECT
@firstname = firstname,
@lastname = lastname
FROM HR.Employees
WHERE empid = 3;
SELECT @firstname AS firstname, @lastname AS lastname;
The assignment 只有一行符合条件时，赋值SELECT has predictable behavior when exactly one row qualifies. SELECT具有可预测的行为。However, note that if the query has more than one qualifying row, the code doesn't fail. 但是，请注意，如果查询有多个符合条件的行，则代码不会失败。The assignments take place per qualifying row, and with each row accessed, the values from the current row overwrite the existing values in the variables. 赋值按符合条件的行进行，当访问每一行时，当前行中的值将覆盖变量中的现有值。When the assignment 当赋值SELECT finishes, the values in the variables are those from the last row that SQL Server happened to access. SELECT完成时，变量中的值是SQL Server碰巧访问的最后一行中的值。For example, the following assignment 例如，以下赋值SELECT has two qualifying rows:SELECT有两个符合条件的行：
DECLARE @empname AS NVARCHAR(61);
SELECT @empname = firstname + N' ' + lastname
FROM HR.Employees
WHERE mgrid = 2;
SELECT @empname AS empname;
The employee information that ends up in the variable after the assignment 赋值SELECT finishes depends on the order in which SQL Server happens to access those rows—and you have no control over this order. SELECT完成后，变量中最终显示的员工信息取决于SQL Server访问这些行的顺序，您无法控制此顺序。When I ran this code, I got the following output:运行此代码时，我得到以下输出：
empname
---------------
Sven Mortensen
The SET statement is safer than the assignment SELECT because it requires you to use a scalar subquery to pull data from a table. SET语句比赋值SELECT更安全，因为它要求您使用标量子查询从表中提取数据。Remember that a scalar subquery fails at run time if it returns more than one value. 请记住，如果标量子查询返回多个值，则在运行时将失败。For example, the following code fails:例如，以下代码失败：
DECLARE @empname AS NVARCHAR(61);
SET @empname = (SELECT firstname + N' ' + lastname
FROM HR.Employees
WHERE mgrid = 2);
SELECT @empname AS empname;
Because the variable was not assigned a value, it remains 因为变量没有赋值，所以它保持NULL, which is the default for variables that were not initialized. NULL，这是未初始化变量的默认值。This code returns the following output:此代码返回以下输出：
Msg 512, Level 16, State 1, Line 71
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <,
<= , >, >= or when the subquery is used as an expression.
empname
--------
NULL
A batch is one or more T-SQL statements sent by a client application to SQL Server for execution as a single unit. 批处理是客户端应用程序发送到SQL Server的一个或多个T-SQL语句，以作为单个单元执行。The batch undergoes parsing (syntax checking), resolution/binding (checking the existence of referenced objects and columns, permissions checking), and optimization as a unit.批处理作为一个单元进行解析（语法检查）、解析/绑定（检查引用对象和列的存在、权限检查）和优化。
Don't confuse 不要混淆“事务”和“批处理”。transactions and batches. A transaction is an atomic unit of work. 事务是一个原子工作单元。A batch can have multiple transactions, and a transaction can be submitted in parts as multiple batches. 一个批次可以有多个事务，一个事务可以作为多个批次部分提交。When a transaction is canceled or rolled back, SQL Server undoes the partial activity that has taken place since the beginning of the transaction, regardless of where the batch began.当事务被取消或回滚时，SQL Server将撤消自事务开始以来发生的部分活动，无论批处理从何处开始。
Client application programming interfaces (APIs) such as ADO.NET provide you with methods for submitting a batch of code to SQL Server for execution. ADONET等客户端应用程序编程接口（API）为您提供了向SQL Server提交一批代码以供执行的方法。SQL Server utilities such as SQL Server Management Studio (SSMS), SQLCMD, and OSQL provide a client tool command called SQL Server实用程序（如SQL Server Management Studio(SSMS)、SQLCMD和OSQL）提供了一个名为GO that signals the end of a batch. GO的客户端工具命令，该命令指示批处理的结束。Note that the 请注意，GO command is a client tool command and not a T-SQL server command.GO命令是客户端工具命令，而不是T-SQL server命令。
A batch is a set of commands that are parsed and executed as a unit. 批处理是作为一个单元进行分析和执行的一组命令。If the parsing is successful, SQL Server then attempts to execute the batch. 如果解析成功，SQL Server将尝试执行批处理。In the event of a syntax error in the batch, the whole batch is not submitted to SQL Server for execution. 如果批处理中出现语法错误，则不会将整个批处理提交给SQL Server执行。For example, the following code has three batches, the second of which has a syntax error (例如，以下代码有三个批次，其中第二个批次有语法错误（在第二个查询中是FOM instead of FROM in the second query):FOM而不是FROM）：
-- Valid batch
PRINT 'First batch';
USE TSQLV4;
GO
-- Invalid batch
PRINT 'Second batch';
SELECT custid FROM Sales.Customers;
SELECT orderid FOM Sales.Orders;
GO
-- Valid batch
PRINT 'Third batch';
SELECT empid FROM HR.Employees;
Because the second batch has a syntax error, the whole batch is not submitted to SQL Server for execution. 因为第二个批处理有语法错误，所以整个批处理不会提交给SQL Server执行。The first and third batches pass syntax validation and therefore are submitted for execution. 第一批和第三批通过语法验证，因此提交执行。This code produces the following output showing that the whole second batch was not executed:此代码生成以下输出，表明整个第二批未执行：
First batch
Msg 102, Level 15, State 1, Line 91
Incorrect syntax near 'Sales'.
Third batch
empid
-----------
2
7
1
5
6
8
3
9
4
(9 row(s) affected)
A variable is local to the batch in which it's defined. 变量是定义它的批次的局部变量。If you refer to a variable that was defined in another batch, you'll get an error saying that the variable was not defined. 如果引用的变量是在另一批中定义的，则会出现一个错误，说明未定义该变量。For example, the following code declares a variable and prints its content in one batch, and then it tries to print its content from another batch:例如，以下代码声明一个变量并在一个批中打印其内容，然后尝试从另一个批中打印其内容：
DECLARE @i AS INT;
SET @i = 10;
-- Succeeds
PRINT @i;
GO
-- Fails
PRINT @i;
The reference to the variable in the first 第一个PRINT statement is valid because it appears in the same batch where the variable was declared, but the second reference is invalid. PRINT语句中对变量的引用是有效的，因为它出现在声明变量的同一批中，但第二个引用无效。Therefore, the first 因此，第一个PRINT statement returns the variable's value (10), whereas the second fails. Here's the output returned from this code:PRINT语句返回变量的值（10），而第二个语句失败。以下是此代码返回的输出：
10
Msg 137, Level 15, State 2, Line 106
Must declare the scalar variable "@i".
The following statements cannot be combined with other statements in the same batch: 以下语句不能与同一批中的其他语句组合：CREATE DEFAULT, CREATE FUNCTION, CREATE PROCEDURE, CREATE RULE, CREATE SCHEMA, CREATE TRIGGER, and CREATE VIEW. CREATE DEFAULT、CREATE FUNCTION、CREATE PROCEDURE、CREATE RULE、CREATE SCHEMA、CREATE TRIGGER和CREATE VIEW。For example, the following code has a 例如，以下代码在同一批中具有DROP statement followed by a CREATE VIEW statement in the same batch and therefore is invalid:DROP语句，后跟CREATE VIEW语句，因此无效：
DROP VIEW IF EXISTS Sales.MyView;
CREATE VIEW Sales.MyView
AS
SELECT YEAR(orderdate) AS orderyear, COUNT(*) AS numorders
FROM Sales.Orders
GROUP BY YEAR(orderdate);
GO
An attempt to run this code generates the following error:尝试运行此代码会生成以下错误：
Msg 111, Level 15, State 1, Line 113
'CREATE VIEW' must be the first statement in a query batch.
To get around the problem, separate the 要解决此问题，请通过在DROP VIEW and CREATE VIEW statements into different batches by adding a GO command after the DROP VIEW statement.DROP VIEW语句之后添加GO命令，将DROP VIEW和CREATE VIEW语句分隔为不同的批。
A batch is a unit of resolution (also known as 批次是一个分辨率单位（也称为“绑定”）。binding). This means that checking the existence of objects and columns happens at the batch level. 这意味着在批处理级别检查对象和列的存在。Keep this fact in mind when you're designing batch boundaries. 在设计批边界时，请记住这一事实。When you apply schema changes to an object and try to manipulate the object data in the same batch, SQL Server might not be aware of the schema changes yet and fail the data-manipulation statement with a resolution error. 当您将架构更改应用于对象并尝试在同一批处理中操作对象数据时，SQL Server可能尚未意识到架构更改，并导致数据操作语句失败，并出现解决错误。I'll demonstrate the problem through an example and then recommend best practices.我将通过一个示例演示这个问题，然后推荐最佳实践。
Run the following code to create a table called 运行以下代码在当前数据库中创建一个名为T1 in the current database, with one column called col1:T1的表，其中一列名为col1：
DROP TABLE IF EXISTS dbo.T1;
CREATE TABLE dbo.T1(col1 INT);
Next, try to add a column called 接下来，尝试向col2 to T1 and query the new column in the same batch:T1添加一个名为col2的列，并在同一批中查询新列：
ALTER TABLE dbo.T1 ADD col2 INT;
SELECT col1, col2 FROM dbo.T1;
Even though the code might seem to be perfectly valid, the batch fails during the resolution phase with the following error:尽管代码似乎完全有效，但批处理在解决阶段失败，并出现以下错误：
Msg 207, Level 16, State 1, Line 130
Invalid column name 'col2'.
At the time the 解析SELECT statement was resolved, T1 had only one column, and the reference to the col2 column caused the error. SELECT语句时，T1只有一列，对col2列的引用导致了错误。One best practice you can follow to avoid such problems is to separate data-definition language (DDL) and Data-Manipulation Language (DML) statements into different batches, as in the following example:为避免此类问题，您可以遵循的一种最佳做法是将数据定义语言（DDL）和数据操作语言（DML）语句分为不同的批，如下例所示：
ALTER TABLE dbo.T1 ADD col2 INT;
GO
SELECT col1, col2 FROM dbo.T1;
GO n optionGO n选项The GO command is not really a T-SQL command; it's actually a command used by SQL Server's client tools, such as SSMS, to denote the end of a batch. GO命令不是真正的T-SQL命令；它实际上是SQL Server的客户端工具（如SSMS）用来表示批处理结束的命令。This command supports an argument indicating how many times you want to execute the batch. 此命令支持一个参数，该参数指示要执行批处理的次数。To see how the 要查看带有参数的GO command with the argument works, first create the table T1 by using the following code:GO命令的工作方式，请首先使用以下代码创建表T1：
DROP TABLE IF EXISTS dbo.T1;
CREATE TABLE dbo.T1(col1 INT IDENTITY);
The col1 column gets its values automatically from an identity property. col1列自动从identity属性获取其值。Note that the demo works just as well if you use a default constraint to generate values from a sequence object. 请注意，如果使用默认约束从序列对象生成值，演示也可以正常工作。Next, run the following code to suppress the default output produced by DML statements that indicates how many rows were affected:接下来，运行以下代码以抑制DML语句生成的默认输出，该语句指示有多少行受到影响：
SET NOCOUNT ON;
Finally, run the following code to define a batch with an 最后，运行以下代码以使用INSERT DEFAULT VALUES statement and to execute the batch 100 times:INSERT DEFAULT VALUES语句定义一个批，并执行该批100次：
INSERT INTO dbo.T1 DEFAULT VALUES;
GO 100
SELECT * FROM dbo.T1;
The query returns 100 rows with the values 查询返回1 through 100 in col1.col1中值为1到100的100行。
You use flow elements to control the flow of your code. 您可以使用流元素来控制代码的流。T-SQL provides basic forms of control with flow elements, including the T-SQL提供了流元素的基本控制形式，包括IF . . . ELSE element and the WHILE element.IF . . . ELSE元素和WHILE元素。
IF . . . ELSE flow elementIF . . . ELSE流元素You use the 使用IF . . . ELSE element to control the flow of your code based on the result of a predicate. IF . . . ELSE元素来控制基于谓词结果的代码流。You specify a statement or statement block that is executed if the predicate is 可以指定在谓词为TRUE, and optionally a statement or statement block that is executed if the predicate is FALSE or UNKNOWN.TRUE时执行的语句或语句块，也可以指定在谓词为FALSE或UNNKOWN时执行的语句或语句块。
For example, the following code checks whether today is the last day of the year (in other words, whether today's year is different than tomorrow's year). 例如，以下代码检查今天是否是一年中的最后一天（换句话说，今天的一年是否与明天的一年不同）。If this is true, the code prints a message saying that today is the last day of the year; if it's not true (“else”), the code prints a message saying that today is not the last day of the year:如果这是真的，代码将打印一条消息，说明今天是一年中的最后一天；如果不是真的（“else”），代码将打印一条消息，说明今天不是一年中的最后一天：
IF YEAR(SYSDATETIME()) <> YEAR(DATEADD(day, 1, SYSDATETIME()))
PRINT 'Today is the last day of the year.';
ELSE
PRINT 'Today is not the last day of the year.';
In this example, I use 在本例中，我使用PRINT statements to demonstrate which parts of the code were executed and which weren't, but of course you can specify other statements as well.PRINT语句来演示代码的哪些部分已执行，哪些部分未执行，但您当然也可以指定其他语句。
Keep in mind that T-SQL uses three-valued logic and that the 请记住，T-SQL使用三值逻辑，并且当谓词为ELSE block is activated when the predicate is either FALSE or UNKNOWN. FALSE或UNKNOWN时，会激活ELSE块。In cases for which both 如果FALSE and UNKNOWN are possible outcomes of the predicate (for example, when NULLs are involved) and you need different treatment for each case, make sure you have an explicit test for NULLs with the IS NULL predicate.FALSE和UNKNOWN都是谓词的可能结果（例如，当涉及NULL时），并且需要对每种情况进行不同的处理，请确保使用IS NULL谓词对NULL进行显式测试。
If the flow you need to control involves more than two cases, you can nest 如果需要控制的流涉及两种以上的情况，则可以嵌套IF . . . ELSE elements. IF . . . ELSE元素。For example, the next code I'll show you handles the following three cases differently:例如，我将向您展示的下一段代码以不同的方式处理以下三种情况：
Today is the last day of the year.今天是一年的最后一天。
Today is the last day of the month but not the last day of the year.今天是一个月的最后一天，但不是一年的最后一天。
Today is not the last day of the month.今天不是一个月的最后一天。
IF YEAR(SYSDATETIME()) <> YEAR(DATEADD(day, 1, SYSDATETIME()))
PRINT 'Today is the last day of the year.';
ELSE
IF MONTH(SYSDATETIME()) <> MONTH(DATEADD(day, 1, SYSDATETIME()))
PRINT 'Today is the last day of the month but not the last day of the year.';
ELSE
PRINT 'Today is not the last day of the month.';
If you need to run more than one statement in the 如果需要在IF or ELSE sections, you need to use a statement block. IF或ELSE部分中运行多条语句，则需要使用语句块。You mark the boundaries of a statement block with the 使用BEGIN and END keywords. BEGIN和END关键字标记语句块的边界。For example, the following code shows how to run one type of process if it's the first day of the month and another type of process if it isn't:例如，以下代码显示了如何在一个月的第一天运行一种类型的流程，如果不是，则运行另一种类型的流程：
IF DAY(SYSDATETIME()) = 1
BEGIN
PRINT 'Today is the first day of the month.';
PRINT 'Starting first-of-month-day process.';
/* ... process code goes here ... */
PRINT 'Finished first-of-month-day database process.';
END;
ELSE
BEGIN
PRINT 'Today is not the first day of the month.';
PRINT 'Starting non-first-of-month-day process.';
/* ... process code goes here ... */
PRINT 'Finished non-first-of-month-day process.';
END;
WHILE flow elementWHILE流元素T-SQL provides the T-SQL提供WHILE element, which you can use to execute code in a loop. WHILE元素，您可以使用它在循环中执行代码。The WHILE element executes a statement or statement block repeatedly while the predicate you specify after the WHILE keyword is TRUE. WHILE元素在WHILE关键字后指定的谓词为TRUE时重复执行语句或语句块。When the predicate is 当谓词为FALSE or UNKNOWN, the loop terminates.FALSE或UNKNOWN时，循环终止。
T-SQL doesn't provide a built-in looping element that executes a predetermined number of times, but it's easy to mimic such an element with a T-SQL不提供执行预定次数的内置循环元素，但很容易用WHILE loop and a variable. WHILE循环和变量来模拟这样的元素。For example, the following code demonstrates how to write a loop that iterates 10 times:例如，以下代码演示如何编写迭代10次的循环：
DECLARE @i AS INT = 1;
WHILE @i <= 10
BEGIN
PRINT @i;
SET @i = @i + 1;
END;
The code declares an integer variable called 代码声明了一个名为@i that serves as the loop counter and initializes it with the value 1. @i的整数变量，该变量用作循环计数器，并用值1对其进行初始化。The code then enters a loop that iterates while the variable is smaller than or equal to 然后，代码进入一个循环，当变量小于或等于10. 10时循环。In each iteration, the code in the loop's body prints the current value of 在每次迭代中，循环体中的代码打印当前值@i and then increments it by 1. @i，然后将其递增1。This code returns the following output showing that the loop iterated 10 times:此代码返回以下输出，显示循环迭代了10次：
1
2
3
4
5
6
7
8
9
10
If at some point in the loop's body you want to break out of the current loop and proceed to execute the statement that appears after the loop's body, use the 如果在循环体中的某个点，您希望中断当前循环并继续执行循环体后面出现的语句，请使用BREAK command. BREAK命令。For example, the following code breaks from the loop if the value of 例如，如果@i is equal to 6:@i的值等于6，则以下代码将从循环中断：
DECLARE @i AS INT = 1;
WHILE @i <= 10
BEGIN
IF @i = 6 BREAK;
PRINT @i;
SET @i = @i + 1;
END;
This code produces the following output showing that the loop iterated five times and terminated at the beginning of the sixth iteration:此代码生成以下输出，显示循环迭代了五次，并在第六次迭代开始时终止：
1
2
3
4
5
Of course, this code is not very sensible; if you want the loop to iterate only five times, you should simply specify the predicate 当然，这个代码不是很明智；如果希望循环只迭代五次，只需指定谓词@i <= 5. @i<=5即可。Here I just wanted to demonstrate the use of the 这里我只想用一个简单的例子来演示BREAK command with a simple example.BREAK命令的使用。
If at some point in the loop's body you want to skip the rest of the activity in the current iteration and evaluate the loop's predicate again, use the 如果在循环体的某个点上，希望跳过当前迭代中的其余活动并再次计算循环的谓词，请使用CONTINUE command. CONTINUE命令。For example, the following code demonstrates how to skip the activity of the sixth iteration of the loop from the point where the 例如，以下代码演示了如何从IF statement appears and until the end of the loop's body:IF语句出现的点到循环体的末尾跳过循环第六次迭代的活动：
DECLARE @i AS INT = 0;
WHILE @i < 10
BEGIN
SET @i = @i + 1;
IF @i = 6 CONTINUE;
PRINT @i;
END;
The output of this code shows that the value of 此代码的输出显示，除第六次迭代外，所有迭代都打印了@i was printed in all iterations except the sixth:@i的值：
1
2
3
4
5
7
8
9
10
As another example of using a 作为使用WHILE loop, the following code creates a table called dbo.Numbers and populates it with 1,000 rows with the values 1 through 1,000 in the column n:WHILE循环的另一个示例，以下代码创建了一个名为dbo.Numbers的表，并用n列中的1000行值1到1000填充该表：
SET NOCOUNT ON;
DROP TABLE IF EXISTS dbo.Numbers;
CREATE TABLE dbo.Numbers(n INT NOT NULL PRIMARY KEY);
GO
DECLARE @i AS INT = 1;
WHILE @i <= 1000
BEGIN
INSERT INTO dbo.Numbers(n) VALUES(@i);
SET @i = @i + 1;
END;
In Chapter 2, “Single-table queries,” I explained that a query without an 在第2章“单表查询”中，我解释了没有ORDER BY clause returns a set (or a multiset), whereas a query with an ORDER BY clause returns what standard SQL calls a cursor—a nonrelational result with order guaranteed among rows. ORDER BY子句的查询将返回一个集合（或多集合），而带有ORDER BY子句的查询将返回标准SQL所称的“游标”，这是一个行之间有顺序保证的非关系结果。In the context of the discussion in Chapter 2, the use of the term “cursor” was conceptual. 在第2章的讨论中，“游标”一词的使用是概念性的。SQL and T-SQL also supports an object called SQL和T-SQL还支持一个名为cursor you can use to process rows from a result of a query one at a time and in a requested order. cursor的对象，您可以使用该对象按请求的顺序逐个处理查询结果中的行。This is in contrast to using set-based queries—normal queries without a cursor for which you manipulate the set or multiset as a whole and cannot rely on order.这与使用基于集的查询形成对比。对于没有游标的普通查询，可以将集合或多集合作为一个整体进行操作，并且不能依赖于顺序。
I want to stress that your default choice should be to use set-based queries; only when you have a compelling reason to do otherwise should you consider using cursors. 我想强调的是，您的默认选择应该是使用基于集合的查询；只有当您有强烈的理由不这样做时，才应该考虑使用游标。This recommendation is based on several factors, including the following:本建议基于以下几个因素：
First and foremost, when you use cursors you pretty much go against the relational model, which is based on set theory.首先也是最重要的一点，当您使用游标时，您几乎违背了基于集合论的关系模型。
The record-by-record manipulation done by the cursor has overhead. 游标执行的逐记录操作有开销。A certain extra cost is associated with each record manipulation by the cursor compared to set-based manipulation. 与基于集合的操作相比，游标对每个记录的操作都会产生一定的额外成本。Given a set-based query and cursor code that do similar physical processing behind the scenes, the cursor code is usually many times slower than the set-based code.给定一个在后台执行类似物理处理的基于集合的查询和游标代码，游标代码通常比基于集合的代码慢很多倍。
With cursors, you write imperative solutions—in other words, you're responsible for defining how to process the data (declaring the cursor, opening it, looping through the cursor records, closing the cursor, and deallocating the cursor). 使用游标，您可以编写命令式解决方案——换句话说，您负责定义如何处理数据（声明游标、打开游标、循环遍历游标记录、关闭游标和取消分配游标）。With set-based solutions, you write declarative code where you mainly focus on the logical aspects of the solution—in other words, on what to get instead of on how to get it. 使用基于集合的解决方案，您可以编写声明性代码，其中您主要关注解决方案的逻辑方面，换句话说，关注获得什么，而不是如何获得它。Therefore, cursor solutions tend to be longer, less readable, and harder to maintain than set-based solutions.因此，与基于集合的解决方案相比，游标解决方案往往更长，可读性更低，更难维护。
For most people, it's not simple to think in relational terms immediately when they start learning SQL. 对于大多数人来说，当他们开始学习SQL时，立即用关系术语进行思考并不简单。It's more intuitive for most people to think in terms of cursors—processing one record at a time in a certain order. 对于大多数人来说，更直观的想法是，游标按照一定的顺序一次处理一条记录。As a result, cursors are widely used, and in most cases they are misused; that is, they are used even when much better set-based solutions exist. 因此，游标被广泛使用，并且在大多数情况下被滥用；也就是说，即使存在更好的基于集合的解决方案，也会使用它们。Make a conscious effort to adopt the set-based state of mind and to truly think in terms of sets. 有意识地努力采用以集合为基础的心态，并真正按照集合进行思考。It can take time—in some cases years—but as long as you're working with a language that is based on the relational model, that's the right way to think.在某些情况下可能需要数年的时间，但只要您使用的是基于关系模型的语言，这就是正确的思考方式。
Every rule has exceptions. 每一条规则都有例外。One example is when you need to apply a certain task to each row from some table or view. 例如，当您需要将某个任务应用于某个表或视图中的每一行时。For example, you might need to execute some administrative task for each index or table in your database. 例如，您可能需要为数据库中的每个索引或表执行一些管理任务。In such a case, it makes sense to use a cursor to iterate through the index or table names one at a time and execute the relevant task for each of those.在这种情况下，可以使用游标一次遍历一个索引或表名，并为每个索引或表名执行相关任务。
Another example of when you should consider cursors is when your set-based solution performs badly and you exhaust your tuning efforts using the set-based approach. 另一个应该考虑使用游标的例子是，基于集合的解决方案性能不佳，并且使用基于集合的方法耗尽了调优工作。As mentioned, set-based solutions tend to be much faster, but in some exceptional cases the cursor solution is faster. 如前所述，基于集合的解决方案往往要快得多，但在某些特殊情况下，游标解决方案要快得多。One such example is computing running aggregates using T-SQL code that is compatible with legacy versions of SQL Server that don't support the frame option in window functions. 其中一个示例是使用T-SQL代码计算运行聚合，该代码与SQL Server的旧版本兼容，这些旧版本不支持窗口函数中的框架选项。Relational solutions to running aggregates using joins or subqueries are extremely slow. 使用联接或子查询运行聚合的关系解决方案非常慢。An iterative solution, such as one based on a cursor, is usually the optimal one. 迭代解决方案（例如基于游标的解决方案）通常是最佳解决方案。If there are no compatibility restrictions, using a relational solution with window functions is the optimal way to compute running totals.如果没有兼容性限制，那么使用带有窗口函数的关系解决方案是计算运行总数的最佳方法。
Working with a cursor generally involves the following steps:使用游标通常包括以下步骤：
1. Declare the cursor based on a query.基于查询声明游标。
2. Open the cursor.打开游标。
3. Fetch attribute values from the first cursor record into variables.将属性值从第一个游标记录提取到变量中。
4. As long as you haven't reached the end of the cursor (while the value of a function called只要您还没有到达游标的末尾（当一个名为@@FETCH_STATUS is 0), loop through the cursor records; in each iteration of the loop, perform the processing needed for the current row, and then fetch the attribute values from the next row into the variables.@@FETCH_STATUS的函数的值为0时），就可以循环遍历游标记录；在循环的每次迭代中，执行当前行所需的处理，然后将下一行的属性值提取到变量中。
5. Close the cursor.关闭游标。
6. Deallocate the cursor.取消分配游标。
The following example with cursor code calculates the running total quantity for each customer and month from the 以下带有游标代码的示例从Sales.CustOrders view:Sales.CustOrders视图计算每个客户和月份的运行总数量：
SET NOCOUNT ON;
DECLARE @Result AS TABLE
(
custid INT,
ordermonth DATE,
qty INT,
runqty INT,
PRIMARY KEY(custid, ordermonth)
);
DECLARE
@custid AS INT,
@prvcustid AS INT,
@ordermonth AS DATE,
@qty AS INT,
@runqty AS INT;
DECLARE C CURSOR FAST_FORWARD /* read only, forward only */ FOR
SELECT custid, ordermonth, qty
FROM Sales.CustOrders
ORDER BY custid, ordermonth;
OPEN C;
FETCH NEXT FROM C INTO @custid, @ordermonth, @qty;
SELECT @prvcustid = @custid, @runqty = 0;
WHILE @@FETCH_STATUS = 0
BEGIN
IF @custid <> @prvcustid
SELECT @prvcustid = @custid, @runqty = 0;
SET @runqty = @runqty + @qty;
INSERT INTO @Result VALUES(@custid, @ordermonth, @qty, @runqty);
FETCH NEXT FROM C INTO @custid, @ordermonth, @qty;
END;
CLOSE C;
DEALLOCATE C;
SELECT
custid,
CONVERT(VARCHAR(7), ordermonth, 121) AS ordermonth,
qty,
runqty
FROM @Result
ORDER BY custid, ordermonth;
The code declares a cursor based on a query that returns the rows from the 代码基于一个查询声明一个游标，该查询返回按客户ID和订单月份排序的CustOrders view ordered by customer ID and order month, and it iterates through the records one at a time. CustOrders视图中的行，并一次遍历一条记录。The code keeps track of the current running-total quantity in a variable called 代码在一个名为@runqty that is reset every time a new customer is found. @runqty的变量中跟踪当前运行的总数量，该变量在每次找到新客户时都会重置。For each row, the code calculates the current running total by adding the current month's quantity (对于每一行，代码通过将当前月份的数量（@qty) to @runqty, and it inserts a row with the customer ID, order month, current month's quantity, and running quantity into a table variable called @Result. @qty）添加到@runqty来计算当前的运行总数，并将包含客户ID、订单月份、当前月份的数量和运行数量的行插入到名为@Result的表变量中。When the code is done processing all cursor records, it queries the table variable to present the running aggregates.当代码处理完所有游标记录后，它将查询表变量以显示正在运行的聚合。
Here's the output returned by this code, shown in abbreviated form:以下是此代码返回的输出，以缩写形式显示：
custid ordermonth qty runqty
----------- ---------- ----------- -----------
1 2015-08 38 38
1 2015-10 41 79
1 2016-01 17 96
1 2016-03 18 114
1 2016-04 60 174
2 2014-09 6 6
2 2015-08 18 24
2 2015-11 10 34
2 2016-03 29 63
3 2014-11 24 24
3 2015-04 30 54
3 2015-05 80 134
3 2015-06 83 217
3 2015-09 102 319
3 2016-01 40 359
...
89 2014-07 80 80
89 2014-11 105 185
89 2015-03 142 327
89 2015-04 59 386
89 2015-07 59 445
89 2015-10 164 609
89 2015-11 94 703
89 2016-01 140 843
89 2016-02 50 893
89 2016-04 90 983
89 2016-05 80 1063
90 2015-07 5 5
90 2015-09 15 20
90 2015-10 34 54
90 2016-02 82 136
90 2016-04 12 148
91 2014-12 45 45
91 2015-07 31 76
91 2015-12 28 104
91 2016-02 20 124
91 2016-04 81 205
(636 row(s) affected)
As explained in Chapter 7, “Beyond the fundamentals of querying,” T-SQL supports window functions you can use to provide elegant and highly efficient solutions to running aggregates, freeing you from needing to use cursors. 正如第7章“超越查询基础知识”中所述，T-SQL支持窗口函数，您可以使用这些函数为运行聚合提供优雅而高效的解决方案，使您无需使用游标。Here's how you address the same task with a window function:下面是如何使用窗口函数处理相同的任务：
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;
When you need to temporarily store data in tables, in certain cases you might prefer not to work with permanent tables. 当您需要在表中临时存储数据时，在某些情况下，您可能不希望使用永久表。Suppose you need the data to be visible only to the current session, or even only to the current batch. 假设您需要数据只对当前会话可见，甚至只对当前批可见。As an example, suppose you need to store temporary data during data processing, as in the cursor example in the previous section. 例如，假设您需要在数据处理期间存储临时数据，如前一节中的游标示例所示。Another case where people use temporary tables is when they don't have permissions to create permanent tables in a user database.人们使用临时表的另一种情况是，他们没有在用户数据库中创建永久表的权限。
SQL Server supports three kinds of temporary tables you might find more convenient to work with than permanent tables in such cases: local temporary tables, global temporary tables, and table variables. SQL Server支持三种临时表，在这种情况下，您可能会发现使用它们比使用永久表更方便：本地临时表、全局临时表和表变量。The following sections describe the three kinds and demonstrate their use with code samples.以下部分描述了这三种类型，并通过代码示例演示了它们的用法。
You create a local temporary table by naming it with a single pound sign as a prefix, such as 您可以通过使用一个磅符号作为前缀来命名本地临时表，例如#T1. #T1。All three kinds of temporary tables are created in the 这三种临时表都是在tempdb database.tempdb数据库中创建的。
A local temporary table is visible only to the session that created it, in the creating level and all inner levels in the call stack (inner procedures, triggers, and dynamic batches). 本地临时表仅对创建它的会话可见，在创建级别和调用堆栈中的所有内部级别（内部过程、触发器和动态批处理）。A local temporary table is destroyed automatically by SQL Server when the creating level in the call stack goes out of scope. 当调用堆栈中的创建级别超出范围时，SQL Server会自动销毁本地临时表。For example, suppose a stored procedure called 例如，假设一个名为Proc1 calls a procedure called Proc2, which in turn calls a procedure called Proc3, which in turn calls a procedure called Proc4. Proc1的存储过程调用一个名为Proc2的过程，该过程又调用一个名为Proc3的过程，该过程又调用一个名为Proc4的过程。Proc2 creates a temporary table called #T1 before calling Proc3. Proc2在调用Proc3之前创建一个名为#T1的临时表。The table 表#T1 is visible to Proc2, Proc3, and Proc4 but not to Proc1, and it's destroyed automatically by SQL Server when Proc2 finishes. #T1对Proc2、Proc3和Proc4可见，但对Proc1不可见，当Proc2完成时，SQL Server会自动销毁该表。If the temporary table is created in an ad-hoc batch in the outermost nesting level of the session (in other words, when the value of the 如果临时表是在会话最外层嵌套级别的临时批中创建的（换句话说，当@@NESTLEVEL function is 0), it's visible to all subsequent batches as well and is destroyed by SQL Server automatically only when the creating session disconnects.@NESTLEVEL函数的值为0时），则所有后续批也可以看到该临时表，并且只有在创建会话断开连接时，SQL Server才会自动销毁该临时表。
You might wonder how SQL Server prevents name conflicts when two sessions create local temporary tables with the same name. 您可能想知道，当两个会话创建具有相同名称的本地临时表时，SQL Server如何防止名称冲突。SQL Server internally adds a suffix to the table name that makes it unique in SQL Server在内部为表名添加后缀，使其在tempdb. tempdb中唯一。As a developer, you shouldn't care—you refer to the table using the name you provided without the internal suffix, and only your session has access to your table.作为一名开发人员，您不应该介意使用您提供的名称引用表，而不使用内部后缀，并且只有您的会话可以访问您的表。
One obvious scenario for which local temporary tables are useful is when you have a process that needs to store intermediate results temporarily—such as during a loop—and later query the data.本地临时表很有用的一个明显的场景是，当您有一个进程需要临时存储中间结果时，例如在循环期间，然后查询数据。
Another scenario is when you need to access the result of some expensive processing multiple times. 另一种情况是，您需要多次访问一些昂贵处理的结果。For example, suppose you need to join the 例如，假设您需要联接Sales.Orders and Sales.OrderDetails tables, aggregate order quantities by order year, and join two instances of the aggregated data to compare each year's total quantity with the previous year. Sales.Orders和Sales.OrderDetails表，按订单年份聚合订单数量，并联接聚合数据的两个实例，以将每年的总数量与前一年进行比较。The 示例数据库中的Orders and OrderDetails tables in the sample database are very small, but in real-life situations such tables can have millions of rows. Orders和OrderDetails表非常小，但在实际情况中，此类表可能有数百万行。One option is to use table expressions, but remember that table expressions are virtual. 一种选择是使用表表达式，但请记住，表表达式是虚拟的。The expensive work involving scanning all the data, joining the 涉及扫描所有数据、连接Orders and OrderDetails tables, and aggregating the data would have to happen twice with table expressions. Orders和OrderDetails表以及聚合数据的昂贵工作必须使用表表达式进行两次。Instead, it makes sense to do all the expensive work only once—storing the result in a local temporary table—and then join two instances of the temporary table, especially because the result of the expensive work is a tiny set with only one row per order year.相反，只需在本地临时表中存储一次结果，然后连接临时表的两个实例，就可以完成所有昂贵的工作，特别是因为昂贵工作的结果是一个很小的集合，每个订单年只有一行。
The following code illustrates this scenario using a local temporary table:以下代码使用本地临时表演示了此场景：
DROP TABLE IF EXISTS #MyOrderTotalsByYear;
GO
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
INNER 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 #MyOrderTotalsByYear AS Cur
LEFT OUTER JOIN #MyOrderTotalsByYear AS Prv
ON Cur.orderyear = Prv.orderyear + 1;
This code produces the following output:此代码生成以下输出：
orderyear curyearqty prvyearqty
----------- ----------- -----------
2014 9581 NULL
2015 25489 9581
2016 16247 25489
To verify that the local temporary table is visible only to the creating session, try accessing it from another session:要验证本地临时表是否仅对创建会话可见，请尝试从其他会话访问它：
SELECT orderyear, qty FROM #MyOrderTotalsByYear;
You get the following error:出现以下错误：
Msg 208, Level 16, State 0, Line 1
Invalid object name '#MyOrderTotalsByYear'.
When you're done, go back to the original session and drop the temporary table:完成后，返回原始会话并删除临时表：
DROP TABLE IF EXISTS #MyOrderTotalsByYear;
It's generally recommended that you clean up resources as soon as you're done working with them.通常建议您在使用完资源后立即清理它们。
Note
At the date of this writing, global temporary tables are not supported by Azure SQL Database. 在撰写本文之日，Azure SQL数据库不支持全局临时表。If you want to run the code samples from this section, you need to connect to a SQL Server box product.如果要运行本节中的代码示例，需要连接到SQL Server box产品。
When you create a global temporary table, it's visible to all other sessions. 创建全局临时表时，它对所有其他会话都可见。Global temporary tables are destroyed automatically by SQL Server when the creating session disconnects and there are no active references to the table. 当创建会话断开连接并且没有对表的活动引用时，SQL Server会自动销毁全局临时表。You create a global temporary table by naming it with two pound signs as a prefix, such as 您可以通过使用两个磅符号作为前缀来命名全局临时表，例如##T1.##T1。
Global temporary tables are useful when you want to share temporary data with everyone. 当您希望与所有人共享临时数据时，全局临时表非常有用。No special permissions are required, and everyone has full DDL and DML access. 不需要特殊权限，每个人都有完全的DDL和DML访问权限。Of course, the fact that everyone has full access means that anyone can change or even drop the table, so consider the alternatives carefully.当然，每个人都有完全访问权限这一事实意味着任何人都可以更改甚至放弃该表，因此请仔细考虑其他选择。
For example, the following code creates a global temporary table called 例如，以下代码创建了一个名为##Globals with columns called id and val:##Globals的全局临时表，其列名为id和val：
CREATE TABLE ##Globals
(
id sysname NOT NULL PRIMARY KEY,
val SQL_VARIANT NOT NULL
);
The table in this example is intended to mimic global variables, which are not supported in T-SQL. 本例中的表旨在模拟T-SQL中不支持的全局变量。The id column is of a sysname data type (the type that SQL Server uses internally to represent identifiers), and the val column is of a SQL_VARIANT data type (a generic type that can store within it a value of almost any base type).id列是sysname数据类型（SQL Server内部用于表示标识符的类型），val列是SQL_VARIANT数据类型（可以在其中存储几乎任何基类型的值的通用类型）。
Anyone can insert rows into the table. 任何人都可以在表中插入行。For example, run the following code to insert a row representing a variable called 例如，运行以下代码插入一行，该行表示一个名为i and initialize it with the integer value 10:i的变量，并使用整数值10对其进行初始化：
INSERT INTO ##Globals(id, val) VALUES(N'i', CAST(10 AS INT));
Anyone can modify and retrieve data from the table. 任何人都可以修改和检索表中的数据。For example, run the following code from any session to query the current value of the variable 例如，从任何会话运行以下代码以查询变量i:i的当前值：
SELECT val FROM ##Globals WHERE id = N'i';
This code returns the following output:此代码返回以下输出：
val
-----------
10
Note
Keep in mind that as soon as the session that created the global temporary table disconnects and there are no active references to the table, SQL Server automatically destroys the table.请记住，一旦创建全局临时表的会话断开连接并且没有对该表的活动引用，SQL Server就会自动销毁该表。
If you want a global temporary table to be created every time SQL Server starts, and you don't want SQL Server to try to destroy it automatically, you need to create the table from a stored procedure that is marked as a startup procedure. 如果希望在每次SQL Server启动时创建全局临时表，并且不希望SQL Server尝试自动销毁它，则需要从标记为启动过程的存储过程创建表。(For details, see “sp_procoption” in SQL Server Books Online at the following URL: （有关详细信息，请参阅SQL Server联机丛书中的“sp_procoption”，网址如下：http://msdn.microsoft.com/en-us/library/ms181720.aspx.)http://msdn.microsoft.com/en-us/library/ms181720.aspx。）
Run the following code from any session to explicitly destroy the global temporary table:从任何会话运行以下代码以显式销毁全局临时表：
DROP TABLE IF EXISTS ##Globals;
Table variables are similar to local temporary tables in some ways and different in others. 表变量在某些方面类似于本地临时表，但在其他方面有所不同。You declare table variables much like you declare other variables, by using the 通过使用DECLARE statement.DECLARE语句，可以像声明其他变量一样声明表变量。
As with local temporary tables, table variables have a physical presence as a table in the 与本地临时表一样，表变量在tempdb database, contrary to the common misconception that they exist only in memory. tempdb数据库中以表的形式存在，这与通常的误解相反，即它们只存在于内存中。Like local temporary tables, table variables are visible only to the creating session, but because they are variables they have a more limited scope: only the current batch. 与本地临时表一样，表变量仅对创建会话可见，但由于它们是变量，所以它们的作用域更为有限：只有当前批。Table variables are visible neither to inner batches in the call stack nor to subsequent batches in the session.表变量对调用堆栈中的内部批和会话中的后续批都不可见。
If an explicit transaction is rolled back, changes made to temporary tables in that transaction are rolled back as well; however, changes made to table variables by statements that completed in the transaction aren't rolled back. 如果回滚显式事务，则也会回滚对该事务中的临时表所做的更改；但是，事务中完成的语句对表变量所做的更改不会回滚。Only changes made by the active statement that failed or that was terminated before completion are undone.只有失败或在完成之前终止的活动语句所做的更改才会撤消。
Temporary tables and table variables also have optimization differences, but those topics are outside the scope of this book. 临时表和表变量也有优化方面的差异，但这些主题超出了本书的范围。For now, I'll just say that in terms of performance, usually it makes more sense to use table variables with small volumes of data (only a few rows) and to use local temporary tables otherwise.现在，我只想说，在性能方面，通常使用具有少量数据（只有几行）的表变量更有意义，否则使用本地临时表更有意义。
For example, the following code uses a table variable instead of a local temporary table to compare total order quantities of each order year with the year before:例如，以下代码使用表变量而不是本地临时表来比较每个订单年与前一年的订单总量：
DECLARE @MyOrderTotalsByYear TABLE
(
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
INNER 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 @MyOrderTotalsByYear AS Cur
LEFT OUTER JOIN @MyOrderTotalsByYear AS Prv
ON Cur.orderyear = Prv.orderyear + 1;
This code returns the following output:此代码返回以下输出：
orderyear curyearqty prvyearqty
----------- ----------- -----------
2014 9581 NULL
2015 25489 9581
2016 16247 25489
Note that instead of using a table variable or a temporary table and a self-join here, this particular task can be handled alternatively with the 请注意，这里不使用表变量或临时表和自联接，而是可以使用LAG function, like this:LAG函数交替处理此特定任务，如下所示：
SELECT
YEAR(O.orderdate) AS orderyear,
SUM(OD.qty) AS curyearqty,
LAG(SUM(OD.qty)) OVER(ORDER BY YEAR(orderdate)) AS prvyearqty
FROM Sales.Orders AS O
INNER JOIN Sales.OrderDetails AS OD
ON OD.orderid = O.orderid
GROUP BY YEAR(orderdate);
You can use a table type to preserve a table definition as an object in the database. 可以使用表类型将表定义保留为数据库中的对象。Later you can reuse it as the table definition of table variables and input parameters of stored procedures and user-defined functions. 以后可以将其重用为表变量的表定义以及存储过程和用户定义函数的输入参数。Table types are required for table-valued parameters (TVPs).表值参数（TVP）需要表类型。
For example, the following code creates a table type called 例如，以下代码在当前数据库中创建一个名为dbo.OrderTotalsByYear in the current database:dbo.OrderTotalsByYear的表类型：
DROP TYPE IF EXISTS dbo.OrderTotalsByYear;
CREATE TYPE dbo.OrderTotalsByYear AS TABLE
(
orderyear INT NOT NULL PRIMARY KEY,
qty INT NOT NULL
);
After the table type is created, whenever you need to declare a table variable based on the table type's definition, you won't need to repeat the code—instead, you can simply specify 创建表类型后，每当需要根据表类型的定义声明表变量时，无需重复代码，只需指定dbo.OrderTotalsByYear as the variable's type, like this:dbo.OrderTotalsByYear作为变量的类型，如下所示：
DECLARE @MyOrderTotalsByYear AS dbo.OrderTotalsByYear;
As a more complete example, the following code declares a variable called 作为一个更完整的示例，以下代码声明了一个名为@MyOrderTotalsByYear of the new table type, queries the Orders and OrderDetails tables to calculate total order quantities by order year, stores the result of the query in the table variable, and queries the variable to present its contents:@MyOrderTotalsByYear的新表类型变量，查询Orders和OrderDetails表以按订单年份计算总订单量，将查询结果存储在表变量中，并查询变量以显示其内容：
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
INNER JOIN Sales.OrderDetails AS OD
ON OD.orderid = O.orderid
GROUP BY YEAR(orderdate);
SELECT orderyear, qty FROM @MyOrderTotalsByYear;
This code returns the following output:此代码返回以下输出：
orderyear qty
----------- -----------
2014 9581
2015 25489
2016 16247
The benefit of the table type feature extends beyond just helping you shorten your code. 表类型特性的好处不仅仅是帮助您缩短代码。As I mentioned, you can use it as the type of input parameters of stored procedures and functions, which is a useful capability.如前所述，您可以将其用作存储过程和函数的输入参数类型，这是一种有用的功能。
With SQL Server, you can construct a batch of T-SQL code as a character string and then execute that batch. 使用SQL Server，您可以将一批T-SQL代码构造为字符串，然后执行该批。This capability is called 此功能称为“动态SQL”。dynamic SQL. SQL Server provides two ways of executing dynamic SQL: using the SQL Server提供了两种执行动态SQL的方法：使用EXEC (short for EXECUTE) command, and using the sp_executesql stored procedure. EXEC（EXECUTE的缩写）命令和使用sp_executesql存储过程。I will explain the difference between the two and provide examples for using each.我将解释两者之间的区别，并提供使用它们的示例。
Dynamic SQL is useful for several purposes, including the following ones:动态SQL有多种用途，包括以下用途：
Automating administrative tasks自动化管理任务 For example, querying metadata and constructing and executing a 例如，查询元数据并为实例中的每个数据库构造和执行BACKUP DATABASE statement for each database in the instanceBACKUP DATABASE语句
Improving performance of certain tasks提高某些任务的性能 For example, constructing parameterized ad-hoc queries that can reuse previously cached execution plans (more on this later)例如，构造可以重用以前缓存的执行计划的参数化即席查询（稍后将对此进行详细介绍）
Constructing elements of the code based on querying the actual data基于查询实际数据构建代码元素 For example, constructing a 例如，当您提前不知道哪些元素应该出现在PIVOT query dynamically when you don't know ahead of time which elements should appear in the IN clause of the PIVOT operatorPIVOT运算符的IN子句中时，动态构建PIVOT查询
Note
Be extremely careful when concatenating user input as part of your code. 在将用户输入作为代码的一部分进行连接时，要格外小心。Hackers can attempt to inject code you did not intend to run. 黑客可以尝试注入您不打算运行的代码。The best measure you can take against SQL injection is to avoid concatenating user input as part of your code (for example, by using parameters). 针对SQL注入可以采取的最佳措施是避免将用户输入连接为代码的一部分（例如，通过使用参数）。If you do concatenate user input as part of your code, make sure you thoroughly inspect the input and look for SQL injection attempts. 如果确实将用户输入作为代码的一部分进行串联，请确保彻底检查输入并查找SQL注入尝试。You can find an article on the subject in SQL Server Books Online using the following URL: 您可以使用以下URL在SQL Server联机丛书中找到有关此主题的文章：https://technet.microsoft.com/en-us/library/ms161953(v=sql.105).aspx.。
EXEC commandEXEC命令The EXEC command accepts a character string in parentheses as input and executes the batch of code within the character string. EXEC命令接受括号中的字符串作为输入，并执行字符串中的一批代码。EXEC supports both regular and Unicode character strings as input. EXEC支持常规字符串和Unicode字符串作为输入。This command can also be used to execute a stored procedure, as I will demonstrate later in the chapter.这个命令还可以用于执行存储过程，我将在本章后面部分演示。
The following example stores a character string with a 以下示例将带有PRINT statement in the variable @sql and then uses the EXEC command to invoke the batch of code stored within the variable:PRINT语句的字符串存储在变量@sql中，然后使用EXEC命令调用存储在变量中的一批代码：
DECLARE @sql AS VARCHAR(100);
SET @sql = 'PRINT ''This message was printed by a dynamic SQL batch.'';';
EXEC(@sql);
Notice the use of two single quotes to represent one single quote in a string within a string. This code returns the following output:请注意，使用两个单引号表示字符串中的一个单引号。此代码返回以下输出：
This message was printed by a dynamic SQL batch.
sp_executesql stored proceduresp_executesql存储过程The sp_executesql stored procedure is an alternative tool to the EXEC command for executing dynamic SQL code. sp_executesql存储过程是EXEC命令的替代工具，用于执行动态SQL代码。It's more secure and more flexible in the sense that it has an interface; that is, it supports input and output parameters. 它更安全，更灵活，因为它有一个接口；也就是说，它支持输入和输出参数。Note that unlike 请注意，与EXEC, sp_executesql supports only Unicode character strings as the input batch of code.EXEC不同，sp_executesql只支持Unicode字符串作为输入批代码。
The fact that you can use input and output parameters in your dynamic SQL code can help you write more secure and more efficient code. 您可以在动态SQL代码中使用输入和输出参数，这可以帮助您编写更安全、更高效的代码。In terms of security, parameters that appear in the code cannot be considered part of the code—they can only be considered operands in expressions. 就安全性而言，代码中出现的参数不能被视为代码的一部分，它们只能被视为表达式中的操作数。So, by using parameters, you can eliminate your exposure to SQL injection.因此，通过使用参数，您可以消除对SQL注入的暴露。
The sp_executesql stored procedure can perform better than EXEC because its parameterization aids in reusing cached execution plans. sp_executesql存储过程的性能优于EXEC，因为它的参数化有助于重用缓存的执行计划。An execution plan is the physical processing plan SQL Server produces for a query, with the set of instructions describing which objects to access, in what order, which indexes to use, how to access them, which join algorithms to use, and so on. 执行计划是SQL Server为查询生成的物理处理计划，其中的一组指令描述了要访问的对象、顺序、要使用的索引、如何访问它们、要使用的连接算法等。One of the requirements for reusing a previously cached plan is that the query string be the same as the one for which the cached plan was created. 重用以前缓存的计划的要求之一是，查询字符串必须与为其创建缓存计划的字符串相同。The best way to efficiently reuse query execution plans is to use stored procedures with parameters. 高效重用查询执行计划的最佳方法是使用带参数的存储过程。This way, even when parameter values change, the query string remains the same. 这样，即使参数值更改，查询字符串也保持不变。But if you decide to use ad-hoc code instead of stored procedures, at least you can still work with parameters if you use 但是，如果您决定使用特殊代码而不是存储过程，那么如果您使用sp_executesql and therefore increase the chances for plan reuse.sp_executesql，至少您仍然可以使用参数，从而增加计划重用的机会。
The sp_executesql procedure has two input parameters and an assignments section. sp_executesql过程有两个输入参数和一个赋值部分。You specify the Unicode character string holding the batch of code you want to run in the first parameter, which is called 您可以在第一个参数中指定包含要运行的代码批的Unicode字符串，该参数称为@stmt. @stmt。You provide a Unicode character string holding the declarations of input and output parameters in the second input parameter, which is called 您提供了一个Unicode字符串，该字符串在第二个输入参数中保存输入和输出参数的声明，该参数称为@params. @params。Then you specify the assignments of input and output parameters separated by commas.然后指定由逗号分隔的输入和输出参数的赋值。
The following example constructs a batch of code with a query against the 下面的示例构造了一批代码，并对Sales.Orders table. Sales.Orders表进行了查询。The example uses an input parameter called 该示例在查询的筛选器中使用了一个名为@orderid in the query's filter:@orderid的输入参数：
DECLARE @sql AS NVARCHAR(100);
SET @sql = N'SELECT orderid, custid, empid, orderdate
FROM Sales.Orders
WHERE orderid = @orderid;';
EXEC sp_executesql
@stmt = @sql,
@params = N'@orderid AS INT',
@orderid = 10248;
This code generates the following output:此代码生成以下输出：
orderid custid empid orderdate
----------- ----------- ----------- -----------
10248 85 5 2014-07-04
This code assigns the value 此代码将值10248 to the input parameter, but even if you run it again with a different value, the code string remains the same. 10248指定给输入参数，但即使使用不同的值再次运行它，代码字符串仍保持不变。This way, you increase the chances for reusing a previously cached plan.这样，您就增加了重用以前缓存的计划的机会。
PIVOT with Dynamic SQLPIVOT与动态SQL结合使用This section is advanced and optional, and it's intended for readers who feel very comfortable with pivoting techniques and dynamic SQL. 本节是高级和可选的，面向那些对数据透视技术和动态SQL非常熟悉的读者。In Chapter 7, I explained how to use the 在第7章中，我解释了如何使用PIVOT operator to pivot data. PIVOT运算符来透视数据。I mentioned that in a static query, you have to know ahead of time which values to specify in the 我提到，在静态查询中，您必须提前知道要在IN clause of the PIVOT operator. PIVOT运算符的IN子句中指定哪些值。Following is an example of a static query with the 以下是使用PIVOT operator:PIVOT运算符的静态查询示例：
SELECT *
FROM (SELECT shipperid, YEAR(orderdate) AS orderyear, freight
FROM Sales.Orders) AS D
PIVOT(SUM(freight) FOR orderyear IN([2014],[2015],[2016])) AS P;
This example queries the 此示例查询Sales.Orders table and pivots the data so that it returns shipper IDs in the rows, order years in the columns, and the total freight in the intersection of each shipper and order year. Sales.Orders表并旋转数据，以便在行中返回发货人ID，在列中返回订单年份，以及在每个发货人和订单年份的交叉处返回总运费。This code returns the following output:此代码返回以下输出：
shipperid 2014 2015 2016
----------- ------------ ------------- -------------
3 4233.78 11413.35 4865.38
1 2297.42 8681.38 5206.53
2 3748.67 12374.04 12122.14
With the static query, you have to know ahead of time which values (order years in this case) to specify in the 使用静态查询，您必须提前知道要在IN clause of the PIVOT operator. PIVOT运算符的IN子句中指定哪些值（本例中为订单年份）。This means you need to revise the code every year. 这意味着您需要每年修订代码。Instead, you can query the distinct order years from the data, construct a batch of dynamic SQL code based on the years you queried, and execute the dynamic SQL batch like this:相反，您可以从数据中查询不同的订单年份，根据查询的年份构造一批动态SQL代码，并执行如下动态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 += N','
ELSE
SET @first = 0;
SET @sql += QUOTENAME(@orderyear);
FETCH NEXT FROM C INTO @orderyear;
END;
CLOSE C;
DEALLOCATE C;
SET @sql += N')) AS P;';
EXEC sp_executesql @stmt = @sql;
Note
There are more efficient ways to concatenate strings than using a cursor, such as using Common Language Runtime (CLR) aggregates and the 有比使用游标更有效的连接字符串的方法，例如使用公共语言运行时（CLR）聚合和FOR XML PATH option, but they are more advanced and are beyond the scope of this book.FOR XML PATH选项，但它们更高级，超出了本书的范围。
Routines are programmable objects that encapsulate code to calculate a result or to execute activity. 例程是可编程对象，封装代码以计算结果或执行活动。SQL Server supports three types of routines: user-defined functions, stored procedures, and triggers.SQL Server支持三种类型的例程：用户定义函数、存储过程和触发器。
With SQL Server, you can choose whether to develop a routine with T-SQL or with Microsoft .NET code based on the CLR integration in the product. 使用SQL Server，您可以根据产品中的CLR集成选择是使用T-SQL还是使用Microsoft .NET代码开发例程。Because this book's focus is T-SQL, the examples here use T-SQL. 因为本书的重点是T-SQL，所以这里的示例使用T-SQL。When the task at hand mainly involves data manipulation, T-SQL is usually a better choice. 当手头的任务主要涉及数据操作时，T-SQL通常是更好的选择。When the task is more about iterative logic, string manipulation, or computationally intensive operations, .NET code is usually a better choice.当任务更多地涉及迭代逻辑、字符串操作或计算密集型操作时，.NET代码通常是更好的选择。
The purpose of a user-defined function (UDF) is to encapsulate logic that calculates something, possibly based on input parameters, and return a result.用户定义函数（UDF）的目的是封装逻辑，该逻辑可能基于输入参数计算某些内容，并返回结果。
SQL Server supports scalar and table-valued UDFs. SQL Server支持标量和表值UDF。Scalar UDFs return a single value; table-valued UDFs return a table. One benefit of using UDFs is that you can incorporate them into queries. 使用UDF的一个好处是可以将它们合并到查询中。Scalar UDFs can appear anywhere in the query where an expression that returns a single value can appear (for example, in the 标量UDF可以出现在查询中返回单个值的表达式可以出现的任何位置（例如，在SELECT list). SELECT列表中）。Table UDFs can appear in the 表UDF可以出现在查询的FROM clause of a query. FROM子句中。The example in this section is a scalar UDF.本节中的示例是标量UDF。
UDFs are not allowed to have any side effects. UDF不允许有任何副作用。This obviously means UDFs are not allowed to apply any schema or data changes in the database. 这显然意味着不允许UDF在数据库中应用任何模式或数据更改。But other ways of causing side effects are less obvious.但其他引起副作用的方式则不太明显。
For example, invoking the 例如，调用RAND function to return a random value or the NEWID function to return a globally unique identifier (GUID) have side effects. RAND函数返回随机值或调用NEWID函数返回全局唯一标识符（GUID）会产生副作用。Whenever you invoke the 每当您在不指定种子的情况下调用RAND function without specifying a seed, SQL Server generates a random seed that is based on the previous invocation of RAND. RAND函数时，SQL Server都会根据之前的RAND调用生成一个随机种子。For this reason, SQL Server needs to store information internally whenever you invoke the 因此，每当您调用RAND function. RAND函数时，SQL Server都需要在内部存储信息。Similarly, whenever you invoke the 类似地，无论何时调用NEWID function, the system needs to set some information aside to be taken into consideration in the next invocation of NEWID. NEWID函数，系统都需要留出一些信息，以便在下次调用NEWID时加以考虑。Because 因为RAND and NEWID have side effects, you're not allowed to use them in your UDFs.RAND和NEWID有副作用，所以不允许在UDF中使用它们。
For example, the following code creates a UDF called 例如，以下代码创建了一个名为dbo.GetAge that returns the age of a person with a specified birth date (@birthdate argument) at a specified event date (@eventdate argument):dbo.GetAge的UDF，该UDF返回具有指定出生日期（@birthdate参数）的人在指定事件日期（@eventdate参数）的年龄：
DROP FUNCTION IF EXISTS dbo.GetAge;
GO
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;
GO
The function calculates the age as the difference, in terms of years, between the birth year and the event year, minus 1 year in cases where the event month and day are smaller than the birth month and day. 该函数将年龄计算为出生年份和事件年份之间的年差，如果事件月份和日期小于出生月份和日期，则减去1年。The expression 表达式100 * month + day is simply a trick to concatenate the month and day. 100 * month + day只是将月和日连接起来的一个技巧。For example, for the twelfth day in the month of February, the expression yields the integer 212.例如，对于2月的第12天，表达式生成整数212。
Note that a function can have more than just a 请注意，函数体中可以有不止一个RETURN子句。RETURN clause in its body. It can have code with flow elements, calculations, and more. 它可以包含包含流元素、计算等的代码。But the function must have a 但函数必须有一个返回值的RETURN clause that returns a value.RETURN子句。
To demonstrate using a UDF in a query, the following code queries the 为了演示在查询中使用UDF，以下代码查询HR.Employees table and invokes the GetAge function in the SELECT list to calculate the age of each employee today:HR.Eemployees表，并调用SELECT列表中的GetAge函数来计算每个员工今天的年龄：
SELECT
empid, firstname, lastname, birthdate,
dbo.GetAge(birthdate, SYSDATETIME()) AS age
FROM HR.Employees;
For example, if you were to run this query on February 12, 2016, you would get the following output:例如，如果您要在2016年2月12日运行此查询，您将获得以下输出：
empid firstname lastname birthdate age
----------- ---------- -------------------- ---------- -----------
1 Sara Davis 1968-12-08 47
2 Don Funk 1972-02-19 43
3 Judy Lew 1983-08-30 32
4 Yael Peled 1957-09-19 58
5 Sven Mortensen 1975-03-04 40
6 Paul Suurs 1983-07-02 32
7 Russell King 1980-05-29 35
8 Maria Cameron 1978-01-09 38
9 Patricia Doyle 1986-01-27 30
(9 row(s) affected)
Note that if you run the query in your system, the values you get in the age column depend on the date on which you run the query.请注意，如果在系统中运行查询，则年龄列中的值取决于运行查询的日期。
Stored procedures are routines that encapsulate code. 存储过程是封装代码的例程。They can have input and output parameters, they can return result sets of queries, and they are allowed to have side effects. 它们可以有输入和输出参数，可以返回查询的结果集，并且允许产生副作用。Not only can you modify data through stored procedures, you can also apply schema changes through them.您不仅可以通过存储过程修改数据，还可以通过存储过程应用架构更改。
Compared to using ad-hoc code, the use of stored procedures gives you many benefits:与使用ad-hoc代码相比，使用存储过程有许多好处：
Stored procedures encapsulate logic.存储过程封装逻辑。 If you need to change the implementation of a stored procedure, you apply the change in one place using the 如果需要更改存储过程的实现，可以使用ALTER PROC command, and all users of the procedure will use the new version from that point.ALTER PROC命令在一个地方应用更改，并且该过程的所有用户都将从此处使用新版本。
Stored procedures give you better control of security.存储过程可以更好地控制安全性。 You can grant a user permissions to execute the procedure without granting the user direct permissions to perform the underlying activities. 您可以授予用户执行过程的权限，而无需授予用户执行基础活动的直接权限。For example, suppose you want to allow certain users to delete a customer from the database, but you don't want to grant them direct permissions to delete rows from the 例如，假设您希望允许某些用户从数据库中删除客户，但不希望授予他们从Customers table. Customers表中删除行的直接权限。You want to ensure that requests to delete a customer are validated—for example, by checking whether the customer has open orders or open debts—and you might also want to audit the requests. 您希望确保删除客户的请求得到验证，例如，通过检查客户是否有未结订单或未结债务，您可能还希望审核这些请求。By not granting direct permissions to delete rows from the 通过不授予从Customers table but instead granting permissions to execute a procedure that handles the task, you ensure that all the required validations and auditing always take place. Customers表中删除行的直接权限，而是授予执行处理任务的过程的权限，可以确保始终进行所有必需的验证和审核。In addition, stored procedures with parameters can help prevent SQL injection, especially when they replace ad-hoc SQL submitted from the client application.此外，带有参数的存储过程有助于防止SQL注入，尤其是当它们替换从客户端应用程序提交的即席SQL时。
You can incorporate all error-handling code within a procedure, silently taking corrective action where relevant.您可以将所有错误处理代码合并到一个过程中，在相关的地方默默地采取纠正措施。 I discuss error handling later in this chapter.我将在本章后面讨论错误处理。
Stored procedures give you performance benefits.存储过程为您提供了性能优势。 Earlier I talked about reuse of previously cached execution plans. 前面我谈到了重用以前缓存的执行计划。Queries in stored procedure are usually parameterized and therefore have a high likelihood to reuse previously cached plans. 存储过程中的查询通常是参数化的，因此很可能重用以前缓存的计划。Another performance benefit of using stored procedures is a reduction in network traffic. 使用存储过程的另一个性能好处是减少了网络流量。The client application needs to submit only the procedure name and its arguments to SQL Server. 客户端应用程序只需向SQL Server提交过程名称及其参数。The server processes all the procedure's code and returns only the output back to the caller. 服务器处理所有过程的代码，只将输出返回给调用者。No back-and-forth traffic is associated with intermediate steps of the procedure.没有来回通信与过程的中间步骤相关联。
As a simple example, the following code creates a stored procedure called 作为一个简单的示例，以下代码创建了一个名为Sales.GetCustomerOrders. Sales.GetCustomerOrders的存储过程。The procedure accepts a customer ID (该过程接受客户ID（@custid) and a date range (@fromdate and @todate) as inputs. @custid）和日期范围（@fromdate和@todate）作为输入。The procedure returns rows from the 该过程将Sales.Orders table representing orders placed by the requested customer in the requested date range as a result set, and the number of affected rows as an output parameter (@numrows):Sales.Orders表中的行作为结果集返回，这些行表示请求的客户在请求的日期范围内下的订单，并将受影响的行数作为输出参数（@numrows）：
DROP PROC IF EXISTS Sales.GetCustomerOrders;
GO
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
When executing the procedure, if you don't specify a value in the 执行过程时，如果未在@fromdate parameter, the procedure will use the default 19000101, and if you don't specify a value in the @todate parameter, the procedure will use the default 99991231. @fromdate参数中指定值，则过程将使用默认的19000101；如果未在@todate@todate公司参数中指定值，则过程将使用默认的99991231。Notice the use of the keyword 请注意，使用关键字OUTPUT to indicate that the parameter @numrows is an output parameter. OUTPUT表示参数@numrows是一个输出参数。The SET NOCOUNT ON command is used to suppress messages indicating how many rows were affected by DML statements, such as the SELECT statement within the procedure.SET NOCOUNT ON命令用于抑制指示有多少行受DML语句（如过程中的SELECT语句）影响的消息。
Here's an example of executing the procedure, requesting information about orders placed by the customer with the ID of 1 in the year 2015. 以下是执行此过程的示例，请求ID为1的客户在2015年下的订单信息。The code absorbs the value of the output parameter 代码吸收了局部变量@numrows in the local variable @rc and returns it to show how many rows were affected by the query:@rc中输出参数@numrows的值，并返回该值以显示受查询影响的行数：
DECLARE @rc AS INT;
EXEC Sales.GetCustomerOrders
@custid = 1,
@fromdate = '20150101',
@todate = '20160101',
@numrows = @rc OUTPUT;
SELECT @rc AS numrows;
The code returns the following output showing three qualifying orders:代码返回以下输出，显示三个符合条件的订单：
orderid custid empid orderdate
----------- ----------- ----------- -----------
10643 1 6 2015-08-25
10692 1 4 2015-10-03
10702 1 4 2015-10-13
numrows
-----------
3
Run the code again, providing a customer ID that doesn't exist in the 再次运行代码，提供Orders table (for example, customer ID 100). Orders表中不存在的客户ID（例如，客户ID 100）。You get the following output indicating that there are zero qualifying orders:您将获得以下输出，表明没有符合条件的订单：
orderid custid empid orderdate
----------- ----------- ----------- -----------------------
numrows
-----------
0
A “触发器”是一种特殊的存储过程，不能显式执行。trigger is a special kind of stored procedure—one that cannot be executed explicitly. Instead, it's attached to an event. 相反，它附加到一个事件。Whenever the event takes place, the trigger fires and the trigger's code runs. 每当事件发生时，触发器就会触发，触发器的代码就会运行。SQL Server supports the association of triggers with two kinds of events: data manipulation events (DML triggers) such as SQL Server支持触发器与两种类型的事件的关联：数据操作事件（DML触发器），如INSERT, and data definition events (DDL triggers) such as CREATE TABLE.INSERT；数据定义事件（DDL触发器），如CREATE TABLE。
You can use triggers for many purposes, including auditing, enforcing integrity rules that cannot be enforced with constraints, and enforcing policies.您可以将触发器用于多种目的，包括审核、强制执行不能用约束强制执行的完整性规则，以及强制执行策略。
A trigger is considered part of the transaction that includes the event that caused the trigger to fire. 触发器被视为事务的一部分，其中包括导致触发器触发的事件。Issuing a 在触发器的代码中发出ROLLBACK TRAN command within the trigger's code causes a rollback of all changes that took place in the trigger, and also of all changes that took place in the transaction associated with the trigger.ROLLBACK TRAN命令会导致回滚触发器中发生的所有更改，以及与触发器关联的事务中发生的所有更改。
Triggers in SQL Server fire per statement and not per modified row.SQL Server中的触发器按语句激发，而不是按修改后的行激发。
SQL Server supports two kinds of DML triggers: SQL Server支持两种DML触发器：after and instead of. after和instead of。An after trigger fires after the event it's associated with finishes and can be defined only on permanent tables. after触发器在事件发生后激发，它与finishs关联，只能在永久表上定义。An instead of trigger fires instead of the event it's associated with and can be defined on permanent tables and views.instead of触发器激发而不是它关联的事件，并且可以在永久表和视图上定义。
In the trigger's code, you can access pseudo tables called 在触发器的代码中，可以访问名为inserted and deleted that contain the rows that were affected by the modification that caused the trigger to fire. inserted和deleted的伪表，这些表包含受触发触发器的修改影响的行。The inserted table holds the new image of the affected rows in the case of 在执行INSERT and UPDATE actions. INSERT和UPDATE操作时，插入的表保存受影响行的新图像。The deleted table holds the old image of the affected rows in the case of 在执行DELETE and UPDATE actions. DELETE和UPDATE操作时，已删除表保留受影响行的旧映像。Remember that 请记住，INSERT, UPDATE, and DELETE actions can be invoked by the INSERT, UPDATE, and DELETE statements, as well as by the MERGE statement. INSERT、UPDATE和DELETE语句以及MERGE语句都可以调用INSERT、UPDATE和DELETE操作。In the case of 在instead of triggers, the inserted and deleted tables contain the rows that were supposed to be affected by the modification that caused the trigger to fire.instead of触发器的情况下，插入和删除的表包含应该受触发触发器的修改影响的行。
The following simple example of an 下面是一个简单的after trigger audits inserts to a table. after触发器审计示例，它将插入到表中。Run the following code to create a table called 运行以下代码在当前数据库中创建一个名为dbo.T1 in the current database, and another table called dbo.T1_Audit that holds audit information for insertions to T1:dbo.T1的表，以及另一个名为dbo.T1_Audit的表，该表保存T1插入的审核信息：
DROP TABLE IF EXISTS dbo.T1_Audit, dbo.T1;
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 DATETIME2(3) NOT NULL DEFAULT(SYSDATETIME()),
login_name sysname NOT NULL DEFAULT(ORIGINAL_LOGIN()),
keycol INT NOT NULL,
datacol VARCHAR(10) NOT NULL
);
In the audit table, the 在audit表中，audit_lsn column has an identity property and represents an audit log serial number. audit_lsn列具有identity属性，并表示审核日志序列号。The dt列使用默认表达式dt column represents the date and time of the insertion, using the default expression SYSDATETIME(). SYSDATETIME()表示插入的日期和时间。The login_name column represents the name of the login that performed the insertion, using the default expression ORIGINAL_LOGIN().login_name列使用默认表达式ORIGINAL_LOGIN()表示执行插入的登录名。
Next, run the following code to create the 接下来，运行以下代码在AFTER INSERT trigger trg_T1_insert_audit on the T1 table to audit insertions:T1表上创建插入后触发器trg_T1_INSERT_audit以审核插入：
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
As you can see, the trigger simply inserts into the audit table the result of a query against the inserted table. 如您所见，触发器只是将针对插入表的查询结果插入到审计表中。The values of the columns in the audit table that are not listed explicitly in the 在INSERT statement are generated by the default expressions described earlier. INSERT语句中未显式列出的审核表中的列的值由前面描述的默认表达式生成。To test the trigger, run the following code:要测试触发器，请运行以下代码：
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');
The trigger fires after each statement. Next, query the audit table:触发器在每条语句后激发。接下来，查询审计表：
SELECT audit_lsn, dt, login_name, keycol, datacol
FROM dbo.T1_Audit;
You get the following output, only with 您只会得到以下输出，其中dt and login_name values that reflect the date and time when you ran the inserts, and the login you used to connect to SQL Server:dt和login_name值反映了运行插入的日期和时间，以及用于连接到SQL Server的登录名：
audit_lsn dt login_name keycol datacol
----------- ----------------------- ---------------- ----------- ----------
1 2016-02-12 09:04:27.713 K2\Gandalf 10 a
2 2016-02-12 09:04:27.733 K2\Gandalf 30 x
3 2016-02-12 09:04:27.733 K2\Gandalf 20 g
When you're done, run the following code for cleanup:完成后，运行以下代码进行清理：
DROP TABLE dbo.T1_Audit, dbo.T1;
SQL Server supports DDL triggers, which can be used for purposes such as auditing, policy enforcement, and change management. SQL Server支持DDL触发器，可用于审核、策略实施和更改管理等目的。SQL Server box product supports the creation of DDL triggers at two scopes, the database scope and the server scope, depending on the scope of the event. SQL Server box产品支持在两个作用域（数据库作用域和服务器作用域）创建DDL触发器，具体取决于事件的作用域。Azure SQL Database currently supports only database triggers.Azure SQL数据库当前仅支持数据库触发器。
You create a 您可以为具有数据库作用域的事件创建database trigger for events with a database scope, such as CREATE TABLE. database触发器，例如CREATE TABLE。You create an 您可以为具有服务器作用域的事件创建一个all server trigger for events with a server scope, such as CREATE DATABASE. all server触发器，例如CREATE DATABASE。SQL Server supports only SQL Server仅支持after DDL triggers; it doesn't support instead of DDL triggers.afterDDL触发器；它不支持instead ofDDL触发器。
Within the trigger, you obtain information about the event that caused the trigger to fire by querying a function called 在触发器中，您可以通过查询名为EVENTDATA, which returns the event information as an XML instance. EVENTDATA的函数来获取导致触发器触发的事件的信息，该函数将事件信息作为XML实例返回。You can use XQuery expressions to extract event attributes such as post time, event type, and login name from the XML instance.您可以使用XQuery表达式从XML实例中提取事件属性，如发布时间、事件类型和登录名。
The following code creates the 以下代码创建dbo.AuditDDLEvents table, which holds the audit information:dbo.AuditDDLEvents表，该表保存审核信息：
DROP TABLE IF EXISTS dbo.AuditDDLEvents;
CREATE TABLE dbo.AuditDDLEvents
(
audit_lsn INT NOT NULL IDENTITY,
posttime DATETIME2(3) 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)
);
Notice that the table has a column called 请注意，该表有一个名为eventdata that has an XML data type. eventdata的列，该列具有XML数据类型。In addition to the individual attributes that the trigger extracts from the event information and stores in individual attributes, it also stores the full event information in the 除了触发器从事件信息中提取并存储在各个属性中的各个属性外，它还将完整的事件信息存储在eventdata column.eventdata列中。
Run the following code to create the 运行以下代码，使用事件组trg_audit_ddl_events audit trigger on the database by using the event group DDL_DATABASE_LEVEL_EVENTS, which represents all DDL events at the database level:DDL_DATABASE_LEVEL_EVENTS（表示数据库级别的所有ddl事件），在数据库上创建trg_audit_ddl_events审核触发器：
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);
GO
The trigger's code first stores the event information obtained from the 触发器的代码首先将从EVENTDATA function in the @eventdata variable. EVENTDATA函数获得的事件信息存储在@eventdata变量中。The code then inserts a row into the audit table with the attributes extracted by using XQuery expressions by the 然后，代码向审计表中插入一行，其中包含通过.value method from the event information, plus the XML instance with the full event information. .value方法使用XQuery表达式从事件信息中提取的属性，以及包含完整事件信息的XML实例。(For details about the XQuery language, see the following Wikipedia article: （有关XQuery语言的详细信息，请参阅以下维基百科文章：https://en.wikipedia.org/wiki/XQuery.)https://en.wikipedia.org/wiki/XQuery。）
To test the trigger, run the following code, which contains a few DDL statements:要测试触发器，请运行以下代码，其中包含一些DDL语句：
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);
Next, run the following code to query the audit table:接下来，运行以下代码查询审核表：
SELECT * FROM dbo.AuditDDLEvents;
You get the following output (split here into two sections for display purposes), but with values in the 您将获得以下输出（为了便于显示，此处分为两个部分），但posttime and loginname attributes that reflect the post time and logon name in your environment:posttime和loginname属性中的值反映了您环境中的发布时间和登录名称：
audit_lsn posttime eventtype loginname
--------- ------------------------- -------------- ----------------
1 2016-02-12 09:06:18.293 CREATE_TABLE K2\Gandalf
2 2016-02-12 09:06:18.413 ALTER_TABLE K2\Gandalf
3 2016-02-12 09:06:18.423 ALTER_TABLE K2\Gandalf
4 2016-02-12 09:06:18.423 CREATE_INDEX K2\Gandalf
audit_lsn schemaname objectname targetobjectname eventdata
----------- ------------- ------------- ----------------- -------------------
1 dbo T1 NULL <EVENT_INSTANCE>...
2 dbo T1 NULL <EVENT_INSTANCE>...
3 dbo T1 NULL <EVENT_INSTANCE>...
4 dbo idx1 T1 <EVENT_INSTANCE>...
When you're done, run the following code for cleanup:完成后，运行以下代码进行清理：
DROP TRIGGER IF EXISTS trg_audit_ddl_events ON DATABASE;
DROP TABLE IF EXISTS dbo.AuditDDLEvents;
SQL Server provides you with tools to handle errors in your T-SQL code. SQL Server为您提供了处理T-SQL代码中错误的工具。The main tool used for error handling is a construct called 用于错误处理的主要工具是名为TRY. . .CATCH. TRY. . .CATCH的构造。SQL Server also provides a set of functions you can invoke to get information about the error. SQL Server还提供了一组函数，您可以调用这些函数来获取有关错误的信息。I'll start with a basic example demonstrating the use of 我将从一个演示TRY. . .CATCH, followed by a more detailed example demonstrating the use of the error functions.TRY. . .CATCH用法的基本示例开始，然后是一个更详细的示例，演示了错误函数的使用。
You work with the 你和TRY. . .CATCH construct by placing the usual T-SQL code in a TRY block (between the BEGIN TRY and END TRY keywords) and placing all the error-handling code in the adjacent CATCH block (between the BEGIN CATCH and END CATCH keywords). TRY. . .CATCH构造一起工作，方法是将常用的T-SQL代码放在一个TRY块中（在BEGIN TRY和END TRY关键字之间），并将所有错误处理代码放在相邻的CATCH捕获块中（在BEGIN-CATCH和END-CATCH关键字之间）。If the 如果TRY block has no error, the CATCH block is simply skipped. TRY块没有错误，则跳过CATCH块。If the 如果TRY block has an error, control is passed to the corresponding CATCH block. TRY块有错误，控制权将传递给相应的CATCH块。Note that if a 请注意，如果TRY. . .CATCH block captures and handles an error, as far as the caller is concerned, there was no error.TRY. . .CATCH块捕获并处理错误，就调用方而言，没有错误。
Run the following code to demonstrate a case with no error in the 运行以下代码以演示TRY block:TRY块中没有错误的情况：
BEGIN TRY
PRINT 10/2;
PRINT 'No error';
END TRY
BEGIN CATCH
PRINT 'Error';
END CATCH;
All code in the TRY block completed successfully; therefore, the CATCH block was skipped. TRY块中的所有代码成功完成；因此，跳过了CATCH块。This code generates the following output:此代码生成以下输出：
5
No error
Next, run similar code, but this time divide by zero. An error occurs:接下来，运行类似的代码，但这次是除以零。出现错误：
BEGIN TRY
PRINT 10/0;
PRINT 'No error';
END TRY
BEGIN CATCH
PRINT 'Error';
END CATCH;
When the 当divide by zero error happened in the first PRINT statement in the TRY block, control was passed to the corresponding CATCH block. TRY块中的第一条PRINT语句中发生“除以零”错误时，控制权被传递给相应的CATCH块。The second PRINT statement in the TRY block was not executed. TRY块中的第二条PRINT语句未执行。Therefore, this code generates the following output:因此，此代码生成以下输出：
Error
Typically, error handling involves some work in the 通常，错误处理涉及CATCH block investigating the cause of the error and taking a course of action. CATCH块中的一些工作，以调查错误的原因并采取行动。SQL Server gives you information about the error via a set of functions. SQL Server通过一组函数为您提供有关错误的信息。The ERROR_NUMBER function returns an integer with the number of the error. ERROR_NUMBER函数返回一个包含错误数的整数。The CATCH block usually includes flow code that inspects the error number to determine what course of action to take. CATCH块通常包括检查错误号以确定要采取的操作过程的流代码。The ERROR_MESSAGE function returns error-message text. ERROR_MESSAGE函数返回错误消息文本。To get the list of error numbers and messages, query the 要获取错误号和消息的列表，请查询sys.messages catalog view. sys.messages目录视图。The ERROR_SEVERITY and ERROR_STATE functions return the error severity and state. ERROR_SEVERITY和ERROR_STATE函数返回错误严重性和状态。The ERROR_LINE function returns the line number in the code where the error happened. ERROR_LINE函数返回发生错误的代码中的行号。Finally, the 最后，ERROR_PROCEDURE function returns the name of the procedure in which the error happened and returns NULL if the error did not happen within a procedure.ERROR_PROCEDURE函数返回发生错误的过程的名称，如果错误未在过程中发生，则返回NULL。
To demonstrate a more detailed error-handling example including the use of the error functions, first run the following code, which creates a table called 要演示更详细的错误处理示例，包括错误函数的使用，请首先运行以下代码，在当前数据库中创建一个名为dbo.Employees in the current database:dbo.Employees的表：
DROP TABLE IF EXISTS 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)
);
The following code inserts a new row into the 下面的代码在TRY块的Employees table in a TRY block, and if an error occurs, shows how to identify the error by inspecting the ERROR_NUMBER function in the CATCH block. Employees表中插入新行，如果发生错误，则说明如何通过检查CATCH块中的ERROR_NUMBER函数来识别错误。The code uses flow control to identify and handle errors you want to deal with in the 代码使用流控制来识别和处理要在CATCH block, and it re-throws the error otherwise.CATCH块中处理的错误，否则会重新抛出错误。
The code also prints the values of the other error functions simply to show what information is available to you when an error occurs:代码还打印其他错误函数的值，以显示发生错误时可用的信息：
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;
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;
When you run this code for the first time, the new row is inserted into the 第一次运行此代码时，新行将成功插入Employees table successfully, and therefore the CATCH block is skipped. Employees表，因此跳过CATCH块。You get the following output:您将获得以下输出：
(1 row(s) affected)
When you run the same code a second time, the 第二次运行同一代码时，INSERT statement fails, control is passed to the CATCH block, and a primary-key-violation error is identified. INSERT语句失败，控制权传递给CATCH块，并识别出主键冲突错误。You get the following output:您将获得以下输出：
Handling PK violation...
Error Number : 2627
Error Message : Violation of PRIMARY KEY constraint 'PK_Employees'. Cannot insert duplicate key
in object 'dbo.Employees'.
Error Severity: 14
Error State : 1
Error Line : 3
Error Proc : Not within proc
To see other errors, run the code with the values 要查看其他错误，请使用值0, 'A', and NULL as the employee ID.0、'A'和NULL作为员工ID运行代码。
Here, for demonstration purposes, I used 这里，出于演示目的，我使用PRINT statements as the actions when an error was identified. PRINT语句作为识别错误时的操作。Of course, error handling usually involves more than just printing a message indicating that the error was identified.当然，错误处理通常不只是打印一条表明已识别错误的消息。
Note that you can create a stored procedure that encapsulates reusable error-handling code like this:请注意，您可以创建一个存储过程来封装可重用的错误处理代码，如下所示：
DROP PROC IF EXISTS dbo.ErrInsertHandler;
GO
CREATE PROC dbo.ErrInsertHandler
AS
SET NOCOUNT ON;
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;
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');
GO
In your 在CATCH block, you check whether the error number is one of those you want to deal with locally. CATCH块中，检查错误号是否是要在本地处理的错误号之一。If it is, you simply execute the stored procedure; otherwise, you re-throw the error:如果是，只需执行存储过程；否则，您将重新抛出错误：
BEGIN TRY
INSERT INTO dbo.Employees(empid, empname, mgrid)
VALUES(1, 'Emp1', NULL);
END TRY
BEGIN CATCH
IF ERROR_NUMBER() IN (2627, 547, 515, 245)
EXEC dbo.ErrInsertHandler;
ELSE
THROW;
END CATCH;
This way, you can maintain the reusable error-handling code in one place.这样，您就可以在一个地方维护可重用的错误处理代码。
This chapter provided a high-level overview of programmable objects and, as such, doesn't include an exercises section. 本章提供了可编程对象的高级概述，因此不包括练习部分。Its goal is to make you aware of SQL Server's programmability capabilities. 它的目标是让您了解SQL Server的可编程能力。This chapter covered variables, batches, flow elements, cursors, temporary tables, dynamic SQL, user-defined functions, stored procedures, triggers, and error handling—quite a few subjects. 本章涵盖了变量、批次、流元素、游标、临时表、动态SQL、用户定义函数、存储过程、触发器和错误处理等许多主题。This chapter also concludes the book. When you're ready to tackle more advanced T-SQL topics including query-tuning coverage, the next natural step is to read my book 本章也是本书的结尾。当您准备好处理更高级的T-SQL主题（包括查询调优覆盖范围）时，下一步自然是阅读我的书《T-SQL查询》（微软出版社，2015）.T-SQL Querying (Microsoft Press, 2015)