Chapter 5.第5章. Table expressions表表达式

A table expression is a named query expression that represents a valid relational table. 表表达式是表示有效关系表的命名查询表达式。You can use table expressions in data-manipulation statements much like you use other tables. 可以在数据操作语句中使用表表达式，就像使用其他表一样。T-SQL supports four types of table expressions: derived tables, common table expressions (CTEs), views, and inline table-valued functions (inline TVFs). T-SQL支持四种类型的表表达式：派生表、公共表表达式（CTE）、视图和内联表值函数（内联TVF）。The focus of this chapter is on using SELECT queries against table expressions; Chapter 8, “Data modification,” covers modifications against table expressions.本章的重点是对表表达式使用SELECT查询；第8章“数据修改”介绍了对表表达式的修改。

Table expressions are not physically materialized anywhere—they are virtual. When you query a table expression, the inner query gets unnested. 表表达式在任何虚拟的地方都不会物理化。查询表表达式时，内部查询将被取消。In other words, the outer query and the inner query are merged into one query directly against the underlying objects. 换句话说，外部查询和内部查询被合并成一个直接针对底层对象的查询。The benefits of using table expressions are typically related to logical aspects of your code and not to performance. 使用表表达式的好处通常与代码的逻辑方面有关，而与性能无关。For example, you can use table expressions to simplify your solutions by using a modular approach. 例如，通过使用模块化方法，可以使用表表达式简化解决方案。Table expressions also help you circumvent certain restrictions in the language, such as the inability to refer to column aliases assigned in the SELECT clause in query clauses that are logically processed before the SELECT clause.表表达式还可以帮助您规避语言中的某些限制，例如无法在查询子句中引用SELECT子句中指定的列别名，这些查询子句在SELECT子句之前经过逻辑处理。

This chapter also introduces the APPLY table operator as it is used in conjunction with a table expression. 本章还介绍了与表表达式结合使用的APPLY表运算符。I explain how to use this operator to apply a table expression to each row of another table.我将解释如何使用此运算符将表表达式应用于另一个表的每一行。

You specify the query that defines the derived table within parentheses, followed by the AS clause and the derived table name. 指定在括号内定义派生表的查询，后跟AS子句和派生表名称。For example, the following code defines a derived table called USACusts based on a query that returns all customers from the United States, and the outer query selects all rows from the derived table:例如，以下代码基于返回所有来自美国的客户的查询定义了一个名为USACusts的派生表，外部查询从派生表中选择所有行：

Click here to view code image

USE TSQLV4;

SELECT *
FROM (SELECT custid, companyname
      FROM Sales.Customers
      WHERE country = N'USA') AS USACusts;

In this particular case, which is a simple example of the basic syntax, a derived table is not needed because the outer query doesn't apply any manipulation.在这种特殊情况下，这是基本语法的一个简单示例，不需要派生表，因为外部查询不应用任何操作。

The code in this basic example returns the following output:此基本示例中的代码返回以下输出：

custid      companyname
----------- ---------------
32          Customer YSIQX
36          Customer LVJSO
43          Customer UISOJ
45          Customer QXPPT
48          Customer DVFMB
55          Customer KZQZT
65          Customer NYUHS
71          Customer LCOUJ
75          Customer XOJYP
77          Customer LCYBZ
78          Customer NLTYP
82          Customer EYHKM
89          Customer YBQTI

With all types of table expressions, a query must meet three requirements to be a valid inner query in a table-expression definition:对于所有类型的表表达式，查询必须满足三个要求才能成为表表达式定义中的有效内部查询：

Order is not guaranteed.秩序不能保证。 A table expression is supposed to represent a relational table, and the rows in a relational table have no guaranteed order. 表表达式应该表示关系表，而关系表中的行没有保证的顺序。Recall that this aspect of a relation stems from set theory. 回想一下，关系的这一方面源于集合论。For this reason, standard SQL disallows an ORDER BY clause in queries that are used to define table expressions, unless the ORDER BY serves a purpose other than presentation. 因此，标准SQL不允许在用于定义表表达式的查询中使用ORDER BY子句，除非ORDER BY用于表示以外的用途。An example for such an exception is when the query uses the OFFSET-FETCH filter. 这种例外的一个例子是当查询使用OFFSET-FETCH筛选器时。T-SQL enforces similar restrictions, with similar exceptions—when TOP or OFFSET-FETCH is also specified. T-SQL执行类似的限制，当还指定了TOP或OFFSET-FETCH时也有类似的例外。In the context of a query with the TOP or OFFSET-FETCH filter, the ORDER BY clause serves as part of the specification of the filter. 在使用TOP或OFFSET-FETCH筛选器的查询上下文中，ORDER BY子句作为筛选器规范的一部分。If you use a query with TOP or OFFSET-FETCH and ORDER BY to define a table expression, ORDER BY is guaranteed to serve only the filtering-related purpose and not the usual presentation purpose. 如果使用带有TOP或OFFSET-FETCH和ORDER-BY的查询来定义表表达式，则ORDER-BY保证只用于与筛选相关的目的，而不用于通常的表示目的。If the outer query against the table expression does not have a presentation ORDER BY, the output is not guaranteed to be returned in any particular order. 如果表表达式没有外观ORDER BY,，则不能保证以任何特定顺序返回输出。See the “Views and the ORDER BY clause” section later in this chapter for more detail on this item (which applies to all types of table expressions).有关此项（适用于所有类型的表表达式）的更多详细信息，请参阅本章后面的“视图和ORDER BY子句”部分。

All columns must have names.所有列都必须有名称。 All columns in a table must have names; therefore, you must assign column aliases to all expressions in the SELECT list of the query that is used to define a table expression.表中的所有列都必须有名称；因此，必须为用于定义表表达式的查询的SELECT列表中的所有表达式指定列别名。

All column names must be unique.所有列名都必须是唯一的。 All column names in a table must be unique; therefore, a table expression that has multiple columns with the same name is invalid. 表中的所有列名必须是唯一的；因此，具有多个同名列的表表达式无效。Having multiple columns with the same name might happen when the query defining the table expression joins two tables that have a column with the same name. 当定义表表达式的查询连接两个具有相同名称的列的表时，可能会出现具有相同名称的多个列。If you need to incorporate both columns in your table expression, they must have different column names. 如果需要在表表达式中合并这两列，它们必须具有不同的列名。You can resolve this issue by assigning different column aliases to the two columns.可以通过为两列指定不同的列别名来解决此问题。

All three requirements are related to the fact that the table expression is supposed to represent a relation. 这三个要求都与表表达式应该表示关系这一事实有关。All relation attributes must have names; all attribute names must be unique; and, because the relation's body is a set of tuples, there's no order.所有关系属性都必须有名称；所有属性名称必须是唯一的；因为关系的主体是一组元组，所以没有顺序。

For example, suppose you need to write a query against the Sales.Orders table and return the number of distinct customers handled in each order year. 例如，假设您需要对Sales.Orders表编写一个查询，并返回每个订单年处理的不同客户数。The following attempt is invalid because the GROUP BY clause refers to a column alias that was assigned in the SELECT clause, and the GROUP BY clause is logically processed prior to the SELECT clause:以下尝试无效，因为GROUP BY子句引用了SELECT子句中分配的列别名，并且GROUP BY子句在逻辑上是在SELECT子句之前处理的：

Click here to view code image

SELECT
  YEAR(orderdate) AS orderyear,
  COUNT(DISTINCT custid) AS numcusts
FROM Sales.Orders
GROUP BY orderyear;

If you try running this query, you get the following error:如果尝试运行此查询，会出现以下错误：

Click here to view code image

Msg 207, Level 16, State 1, Line 5
Invalid column name 'orderyear'.

You can solve the problem by referring to the expression YEAR(orderdate) in both the GROUP BY and SELECT clauses, but this is an example with a short expression. 您可以通过引用GROUP by和SELECT子句中的表达式YEAR(orderdate)来解决这个问题，但这是一个简短表达式的示例。What if the expression was much longer and you wanted to avoid the repetition of the code? 如果表达式要长得多，并且希望避免代码重复，该怎么办？You can achieve this with a table expression like the one shown in Listing 5-1.可以使用Listing 5-1所示的表表达式来实现这一点。

LISTING 5-1 Query with a derived table using inline aliasing form使用内联别名形式使用派生表进行查询

This query returns the following output:此查询返回以下输出：

orderyear   numcusts
----------- -----------
2014        67
2015        86
2016        81

This code defines a derived table called D based on a query against the Orders table that returns the order year and customer ID from all rows. 这段代码基于对Orders表的查询定义了一个名为D的派生表，该表从所有行返回订单年份和客户ID。The SELECT list of the inner query uses the inline aliasing form to assign the alias orderyear to the expression YEAR(orderdate). 内部查询的选择列表使用内联别名表单将别名orderyear分配给表达式YEAR(orderdate)。The outer query can refer to the orderyear column alias in both the GROUP BY and SELECT clauses, because as far as the outer query is concerned, it queries a table called D with columns called orderyear and custid.外部查询可以在GROUP BY和SELECT子句中引用orderyear列别名，因为就外部查询而言，它查询一个名为D的表，其中包含名为orderyear和custid的列。

As I mentioned earlier, Microsoft SQL Server expands the definition of the table expression and accesses the underlying objects directly. 如前所述，Microsoft SQL Server扩展了表表达式的定义，并直接访问底层对象。After expansion, the query in Listing 5-1 looks like the following:扩展后，Listing 5-1中的查询如下所示：

Click here to view code image

SELECT YEAR(orderdate) AS orderyear, COUNT(DISTINCT custid) AS numcusts
FROM Sales.Orders
GROUP BY YEAR(orderdate);

I present this example just to emphasize that you usually use table expressions for logical (not performance-related) reasons. 我给出这个示例只是为了强调，出于逻辑（与性能无关）原因，通常使用表表达式。Generally speaking, table expressions have neither a positive nor negative impact on performance when compared to the expanded query without the table expression.一般来说，与没有表表达式的扩展查询相比，表表达式对性能既没有正面影响，也没有负面影响。

Listing 5-1 uses the inline aliasing form to assign column aliases to expressions. Listing 5-1使用内联别名表单为表达式指定列别名。The syntax for inline aliasing is <expression> [AS] <alias>. 内联别名的语法是<expression> [AS] <alias>。Note that the word AS is optional in the syntax for inline aliasing; however, I find that it helps the readability of the code and recommend using it.注意，在内联别名的语法中，AS是可选的；然而，我发现它有助于代码的可读性，并建议使用它。

In some cases, you might prefer to use a second aliasing form, which you can think of as external aliasing. 在某些情况下，您可能更喜欢使用第二种别名形式，可以将其视为外部别名。With this form, you do not assign column aliases following the expressions in the SELECT list—you specify all target column names in parentheses following the table expression's name, like so:使用此表单，您不会在SELECT列表中的表达式后面指定列别名，而是在表表达式名称后面的括号中指定所有目标列名，如下所示：

Click here to view code image

SELECT orderyear, COUNT(DISTINCT custid) AS numcusts
FROM (SELECT YEAR(orderdate), custid
      FROM Sales.Orders) AS D(orderyear, custid)
GROUP BY orderyear;

Each form has its advantages. 每种形式都有其优点。I'll start with the advantages of inline aliasing. 我将从内联别名的优点开始。If you need to debug the code when using the inline form, when you highlight the query defining the table expression and run it, the columns in the result appear with the aliases you assigned. 如果在使用内联表单时需要调试代码，那么当突出显示定义表表达式的查询并运行它时，结果中的列将显示您指定的别名。With the external form, you cannot include the target column names when you highlight the table expression query, so the result appears with no column names in the case of the unnamed expressions. 对于外部表单，在突出显示表表达式查询时不能包含目标列名，因此对于未命名的表达式，结果显示时没有列名。Also, when the table expression query is lengthy, using the external form can make it quite difficult to figure out which column alias belongs to which expression.此外，当表表达式查询很长时，使用外部表单可能会很难确定哪个列别名属于哪个表达式。

Then again, the external aliasing form has its advantages—for example, when the query defining the table expression won't undergo any further revisions and you want to treat it like a “black box.” 此外，外部别名表单也有它的优点，例如，当定义表表达式的查询不需要进行任何进一步的修改，并且希望将其视为“黑盒”时。You want to focus your attention on the table-expression name followed by the target-column list when you look at the outer query. 在查看外部查询时，您希望将注意力集中在表表达式名称后面的目标列列表上。To use terminology from traditional programming, you can use external aliasing to specify a contract interface between the outer query and the table expression.要使用传统编程中的术语，可以使用外部别名来指定外部查询和表表达式之间的约定接口。

Click here to view code image

DECLARE @empid AS INT = 3;

SELECT orderyear, COUNT(DISTINCT custid) AS numcusts
FROM (SELECT YEAR(orderdate) AS orderyear, custid
      FROM Sales.Orders
      WHERE empid = @empid) AS D
GROUP BY orderyear;

This query returns the number of distinct customers per year whose orders were handled by the input employee (the employee whose ID is stored in the variable @empid). 此查询返回每年由输入员工（ID存储在变量@empid中的员工）处理订单的不同客户数。Here's the output of this query:以下是此查询的输出：

orderyear   numcusts
----------- -----------
2014        16
2015        46
2016        30

As an example, Listing 5-2 returns order years and the number of customers handled in each year only for years in which more than 70 customers were handled.例如，Listing 5-2只返回订单年数和每年处理的客户数，这些年处理的客户数超过70个。

LISTING 5-2 Query with nested derived tables使用嵌套派生表进行查询

This code returns the following output:此代码返回以下输出：

orderyear   numcusts
----------- -----------
2015        86
2016        81

The purpose of the innermost derived table, D1, is to assign the column alias orderyear to the expression YEAR(orderdate). 最里面的派生表D1用于将列别名orderyear分配给表达式YEAR(orderdate)。The query against D1 refers to orderyear in both the GROUP BY and SELECT clauses and assigns the column alias numcusts to the expression COUNT(DISTINCT custid). 对D1的查询引用GROUP BY和SELECT子句中的orderyear，并将列别名numcusts分配给表达式COUNT(DISTINCT custid)。The query against D1 is used to define the derived table D2. 对D1的查询用于定义派生表D2。The query against D2 refers to numcusts in the WHERE clause to filter order years in which more than 70 customers were handled.针对D2的查询引用WHERE子句中的numcusts来筛选处理了70多个客户的订单年份。

The whole purpose of using table expressions here is to simplify the code by reusing column aliases. 这里使用表表达式的全部目的是通过重用列别名来简化代码。However, with the complexity added by the nesting, I'm not sure this solution is really simpler than the alternative without table expressions:然而，由于嵌套增加了复杂性，我不确定这个解决方案是否真的比没有表表达式的替代方案更简单：

Click here to view code image

SELECT YEAR(orderdate) AS orderyear, COUNT(DISTINCT custid) AS numcusts
FROM Sales.Orders
GROUP BY YEAR(orderdate)
HAVING COUNT(DISTINCT custid) > 70;

LISTING 5-3 Multiple derived tables based on the same query基于同一查询的多个派生表

This query joins two derived tables that are based on the same query. 此查询连接基于同一查询的两个派生表。The first derived table, Cur, represents current years, and the second derived table, Prv, represents previous years. 第一个派生表Cur表示当前年份，第二个派生表Prv表示以前年份。The join condition Cur.orderyear = Prv.orderyear + 1 ensures that each year from the first derived table matches the previous year of the second. 联接条件Cur.orderyear = Prv.orderyear + 1确保第一个派生表中的每一年与第二个派生表中的前一年相匹配。Because the code uses a left outer join, all left years are preserved, including the first, which has no previous year. 因为代码使用左外连接，所以所有的左年份都会被保留，包括第一个没有上一年的年份。The SELECT clause of the outer query calculates the difference between the number of customers handled in the current and previous years.外部查询的SELECT子句计算当前和以前年份处理的客户数量之间的差异。

Listing 5-3 produces the following output:Listing 5-3生成以下输出：

Click here to view code image

orderyear   curnumcusts prvnumcusts growth
----------- ----------- ----------- -----------
2014        67          NULL        NULL
2015        86          67          19
2016        81          86          –5

The fact that you cannot refer to multiple instances of the same derived table in the same join forces you to maintain multiple copies of the same query definition. 不能在同一个联接中引用同一派生表的多个实例，这迫使您维护同一查询定义的多个副本。This leads to lengthy code that is hard to maintain and prone to errors.这会导致代码冗长，难以维护，并且容易出错。

CTEs are defined by using a WITH statement and have the following general form:CTE使用WITH语句定义，具有以下一般形式：

Click here to view code image

WITH <CTE_Name>[(<target_column_list>)]
AS
(
  <inner_query_defining_CTE>
)
<outer_query_against_CTE>;

The inner query defining the CTE must follow all requirements mentioned earlier to be valid to define a table expression. 定义CTE的内部查询必须遵循前面提到的所有要求，才能有效地定义表表达式。As a simple example, the following code defines a CTE called USACusts based on a query that returns all customers from the United States, and the outer query selects all rows from the CTE:作为一个简单的示例，以下代码基于一个返回所有来自美国的客户的查询定义了一个名为USACusts的CTE，外部查询从CTE中选择所有行：

WITH USACusts AS
(
  SELECT custid, companyname
  FROM Sales.Customers
  WHERE country = N'USA'
)
SELECT * FROM USACusts;

As with derived tables, as soon as the outer query finishes, the CTE goes out of scope.与派生表一样，外部查询一完成，CTE就超出范围。

Here's an example of the inline form:下面是一个内联表单的示例：

Click here to view code image

WITH C AS
(
  SELECT YEAR(orderdate) AS orderyear, custid
  FROM Sales.Orders
)
SELECT orderyear, COUNT(DISTINCT custid) AS numcusts
FROM C
GROUP BY orderyear;

And here's an example of the external form:下面是一个外部形式的例子：

Click here to view code image

WITH C(orderyear, custid) AS
(
  SELECT YEAR(orderdate), custid
  FROM Sales.Orders
)
SELECT orderyear, COUNT(DISTINCT custid) AS numcusts
FROM C
GROUP BY orderyear;

The motivations for using one form or the other are similar to those described for derived tables.使用一种形式或另一种形式的动机与为派生表描述的动机类似。

Click here to view code image

DECLARE @empid AS INT = 3;

WITH C AS
(
  SELECT YEAR(orderdate) AS orderyear, custid
  FROM Sales.Orders
  WHERE empid = @empid
)
SELECT orderyear, COUNT(DISTINCT custid) AS numcusts
FROM C
GROUP BY orderyear;

Click here to view code image

WITH C1 AS
(
  SELECT YEAR(orderdate) AS orderyear, custid
  FROM Sales.Orders
),
C2 AS
(
  SELECT orderyear, COUNT(DISTINCT custid) AS numcusts
  FROM C1
  GROUP BY orderyear
)
SELECT orderyear, numcusts
FROM C2
WHERE numcusts > 70;

This modular approach substantially improves the readability and maintainability of the code compared to the nested derived-table approach.与嵌套派生表方法相比，这种模块化方法大大提高了代码的可读性和可维护性。

Note that even if you want to, you cannot nest CTEs, nor can you define a CTE within the parentheses of a derived table. 请注意，即使您愿意，也不能嵌套CTE，也不能在派生表的括号内定义CTE。I think of this restriction as a good thing.我认为这种限制是一件好事。

Click here to view code image

WITH YearlyCount AS
(
  SELECT YEAR(orderdate) AS orderyear,
    COUNT(DISTINCT custid) AS numcusts
  FROM Sales.Orders
  GROUP BY YEAR(orderdate)
)
SELECT Cur.orderyear,
  Cur.numcusts AS curnumcusts, Prv.numcusts AS prvnumcusts,
  Cur.numcusts - Prv.numcusts AS growth
FROM YearlyCount AS Cur
  LEFT OUTER JOIN YearlyCount AS Prv
    ON Cur.orderyear = Prv.orderyear + 1;

As you can see, the CTE YearlyCount is defined only once and accessed twice in the FROM clause of the outer query—once as Cur and once as Prv. 如您所见，CTE YearlyCount在外部查询的FROM子句中只定义了一次，访问了两次，一次是Cur，一次是Prv。You need to maintain only one copy of the inner query (the code inside the CTE). 您只需要维护内部查询的一个副本（CTE中的代码）。The solution is clearer and less prone to errors.解决方案更清晰，更不容易出错。

If you're curious about performance, recall that earlier I mentioned that table expressions typically have no impact on performance because they're not physically materialized anywhere. 如果你对性能感兴趣，记得我之前提到过，表表达式通常不会对性能产生影响，因为它们在任何地方都不是物理物化的。Both references to the CTE in the previous query are going to be expanded. 上一个查询中对CTE的两个引用都将被扩展。Internally, this query has a self join between two instances of the Orders table, each of which involves scanning the table data and aggregating it before the join—the same physical processing that takes place with the derived-table approach. 在内部，这个查询在Orders表的两个实例之间有一个自联接，每个实例都涉及扫描表数据并在联接之前对其进行聚合，这与使用派生表方法进行的物理处理相同。If you want to avoid the repetition of the work done here, you should persist the inner query's result in a temporary table or a table variable. 如果希望避免重复此处所做的工作，应该将内部查询的结果持久化到临时表或表变量中。My focus in this discussion is on coding aspects and not performance, and clearly the ability to specify the inner query only once is a great benefit.我在本次讨论中的重点是编码方面，而不是性能，显然，只指定一次内部查询的能力是一个很大的好处。

CTEs are unique among table expressions in the sense that they support recursion. CTE在表表达式中是独一无二的，因为它们支持递归。Recursive CTEs, like nonrecursive ones, are defined by the SQL standard. 与非递归CTE一样，递归CTE是由SQL标准定义的。A recursive CTE is defined by at least two queries (more are possible)—at least one query known as the anchor member and at least one query known as the recursive member. 递归CTE由至少两个查询定义（可能有更多查询）——至少一个称为锚定成员的查询和至少一个称为递归成员的查询。The general form of a basic recursive CTE looks like the following:基本递归CTE的一般形式如下所示：

Click here to view code image

WITH <CTE_Name>[(<target_column_list>)]
AS
(
  <anchor_member>
  UNION ALL
  <recursive_member>
)
<outer_query_against_CTE>;

The anchor member is a query that returns a valid relational result table—like a query that is used to define a nonrecursive table expression. 锚定成员是一个查询，它返回一个有效的关系结果表，就像用于定义非递归表表达式的查询一样。The anchor member query is invoked only once.锚定成员查询只被调用一次。

The recursive member is a query that has a reference to the CTE name and is invoked repeatedly until it returns an empty set. 递归成员是一个查询，它引用了CTE名称，并被反复调用，直到返回一个空集。The reference to the CTE name represents the previous result set. 对CTE名称的引用代表以前的结果集。The first time that the recursive member is invoked, the previous result set represents whatever the anchor member returned. 第一次调用递归成员时，前一个结果集表示锚成员返回的内容。In each subsequent invocation of the recursive member, the reference to the CTE name represents the result set returned by the previous invocation of the recursive member. 在递归成员的每个后续调用中，对CTE名称的引用表示递归成员的上一次调用返回的结果集。Both queries must be compatible in terms of the number of columns they return and the data types of the corresponding columns. 这两个查询必须在返回的列数和相应列的数据类型方面兼容。The reference to the CTE name in the outer query represents the unified result sets of the invocation of the anchor member and all invocations of the recursive member.外部查询中对CTE名称的引用表示锚定成员调用和递归成员所有调用的统一结果集。

If this is your first encounter with recursive CTEs, you might find this explanation hard to understand. 如果这是你第一次遇到递归CTE，你可能会发现这个解释很难理解。They are best explained with an example. 最好用一个例子来解释它们。The following code demonstrates how to return information about an employee (Don Funk, employee ID 2) and all the employee's subordinates at all levels (direct or indirect):下面的代码演示了如何返回有关员工（Don Funk，员工ID 2）和所有级别（直接或间接）的员工下属的信息：

Click here to view code image

WITH EmpsCTE AS
(
  SELECT empid, mgrid, firstname, lastname
  FROM HR.Employees
  WHERE empid = 2

  UNION ALL

  SELECT C.empid, C.mgrid, C.firstname, C.lastname
  FROM EmpsCTE AS P
    INNER JOIN HR.Employees AS C
      ON C.mgrid = P.empid
)
SELECT empid, mgrid, firstname, lastname
FROM EmpsCTE;

The anchor member queries the HR.Employees table and simply returns the row for employee 2:锚定成员查询HR.Employees表，并简单地返回employees 2的行：

Click here to view code image

  SELECT empid, mgrid, firstname, lastname
  FROM HR.Employees
  WHERE empid = 2

The recursive member joins the CTE—representing the previous result set—with the Employees table to return the direct subordinates of the employees returned in the previous result set:递归成员将表示上一个结果集的CTE与Employees表联接，以返回上一个结果集中返回的员工的直接下属：

Click here to view code image

  SELECT C.empid, C.mgrid, C.firstname, C.lastname
  FROM EmpsCTE AS P
    INNER JOIN HR.Employees AS C
      ON C.mgrid = P.empid

In other words, the recursive member is invoked repeatedly, and in each invocation it returns the next level of subordinates. 换句话说，递归成员被反复调用，并且在每次调用中返回下一级别的下级。The first time the recursive member is invoked, it returns the direct subordinates of employee 2—employees 3 and 5. 第一次调用递归成员时，它返回employee 2-employee 3和employee 5的直接下属。The second time the recursive member is invoked, it returns the direct subordinates of employees 3 and 5—employees 4, 6, 7, 8, and 9. 第二次调用递归成员时，它返回雇员3和5的直接下属，即雇员4、6、7、8和9。The third time the recursive member is invoked, there are no more subordinates; the recursive member returns an empty set, and therefore recursion stops.第三次调用递归成员时，不再有下级；递归成员返回一个空集，因此递归停止。

The reference to the CTE name in the outer query represents the unioned result sets—in other words, employee 2 and all the employee's subordinates.外部查询中对CTE名称的引用代表联合结果集，换句话说，即员工2和员工的所有下属。

Here's the output of this code:下面是这段代码的输出：

Click here to view code image

empid       mgrid       firstname  lastname
----------- ----------- ---------- --------------------
2           1           Don        Funk
3           2           Judy       Lew
5           2           Sven       Mortensen
6           5           Paul       Suurs
7           5           Russell    King
9           5           Patricia   Doyle
4           3           Yael       Peled
8           3           Maria      Cameron

In the event of a logical error in the join predicate in the recursive member, or if there are problems with the data that result in cycles, the recursive member potentially can be invoked an infinite number of times. 如果递归成员中的连接谓词出现逻辑错误，或者如果数据存在导致循环的问题，递归成员可能会被无限次调用。As a safety measure, SQL Server restricts the number of times the recursive member can be invoked to 100 by default. 作为一种安全措施，SQL Server将递归成员的调用次数默认限制为100次。The code will fail if the recursive member is invoked more than 100 times. 如果递归成员被调用超过100次，代码将失败。You can change the default maximum recursion limit (that is, the number of times the recursive member can be invoked) by specifying the hint OPTION(MAXRECURSION n) at the end of the outer query, where n is an integer in the range 0 through 32,767. 通过在外部查询的末尾指定提示OPTION(MAXRECURSION n)，可以更改默认的最大递归限制（即可以调用递归成员的次数），其中n是0到32767范围内的整数。If you want to remove the restriction altogether, specify MAXRECURSION 0. 如果要完全删除该限制，请指定MAXRECURSION 0。Note that SQL Server stores the intermediate result sets returned by the anchor and recursive members in a work table in tempdb; if you remove the restriction and have a runaway query, the work table will quickly get very large, and the query will never finish.请注意，SQL Server将锚和递归成员返回的中间结果集存储在tempdb的工作表中；如果删除限制并进行失控查询，工作表将很快变得非常大，查询将永远无法完成。

As an example, the following code creates a view called USACusts in the Sales schema in the TSQLV4 database, representing all customers from the United States:例如，以下代码在TSQLV4数据库的销售模式中创建了一个名为USACusts的视图，代表来自美国的所有客户：

Click here to view code image

DROP VIEW IF EXISTS Sales.USACusts;
GO
CREATE VIEW Sales.USACusts
AS

SELECT
  custid, companyname, contactname, contacttitle, address,
  city, region, postalcode, country, phone, fax
FROM Sales.Customers
WHERE country = N'USA';
GO

Note that just as with derived tables and CTEs, instead of using inline column aliasing as shown in the preceding code, you can use external column aliasing by specifying the target column names in parentheses immediately after the view name.请注意，与派生表和CTE一样，您可以使用外部列别名，而不是使用前面代码中所示的内联列别名，方法是在视图名称后面的括号中指定目标列名。

After you create this view, you can query it much like you query other tables in the database:创建此视图后，可以像查询数据库中的其他表一样对其进行查询：

SELECT custid, companyname
FROM Sales.USACusts;

Because a view is an object in the database, you can manage access permissions similar to the way you do for tables. 因为视图是数据库中的一个对象，所以可以像管理表一样管理访问权限。(These permissions include SELECT, INSERT, UPDATE, and DELETE.) （这些权限包括SELECT、INSERT、UPDATE和DELETE。）You can even deny direct access to the underlying objects while granting access to the view.在授予对视图的访问权限时，甚至可以拒绝对底层对象的直接访问。

Note that the general recommendation to avoid using SELECT * has specific relevance in the context of views. 请注意，避免使用SELECT *的一般性建议在视图上下文中具有特定的相关性。The columns are enumerated in the compiled form of the view, and new table columns will not be automatically added to the view. 这些列以视图的编译形式枚举，新的表列不会自动添加到视图中。For example, suppose you define a view based on the query SELECT * FROM dbo.T1, and at the view creation time the table T1 has the columns col1 and col2. 例如，假设您基于查询SELECT * FROM dbo.T1定义了一个视图，并且在视图创建时，表T1具有col1和col2列。SQL Server stores information only on those two columns in the view's metadata. SQL Server只存储视图元数据中这两列的信息。If you alter the definition of the table to add new columns, those new columns will not be added to the view. 如果更改表的定义以添加新列，这些新列将不会添加到视图中。You can refresh the view's metadata by using the stored procedure sp_refreshview or sp_refreshsqlmodule, but to avoid confusion, the best practice is to explicitly list the column names you need in the definition of the view. 可以使用存储过程sp_refreshview或sp_refreshsqlmodule刷新视图的元数据，但为了避免混淆，最佳做法是在视图定义中显式列出所需的列名。If columns are added to the underlying tables and you need them in the view, use the ALTER VIEW statement to revise the view definition accordingly.如果向基础表中添加了列，并且视图中需要这些列，请使用ALTER VIEW语句相应地修改视图定义。

Remember that a presentation ORDER BY clause is not allowed in the query defining a table expression because a relation isn't ordered. 请记住，在定义表表达式的查询中不允许使用外观ORDER BY子句，因为关系没有顺序。If you need to return rows from a view sorted for presentation purposes, you should specify a presentation ORDER BY clause in the outer query against the view, like this:如果需要从出于演示目的排序的视图中返回行，则应在针对该视图的外部查询中指定一个外观ORDER BY子句，如下所示：

Click here to view code image

SELECT custid, companyname, region
FROM Sales.USACusts
ORDER BY region;

Try running the following code to create a view with a presentation ORDER BY clause:尝试运行以下代码，创建一个带有外观ORDER BY子句的视图：

Click here to view code image

ALTER VIEW Sales.USACusts
AS

SELECT
  custid, companyname, contactname, contacttitle, address,
  city, region, postalcode, country, phone, fax
FROM Sales.Customers
WHERE country = N'USA'
ORDER BY region;
GO

This attempt fails, and you get the following error:此尝试失败，您将收到以下错误：

Click here to view code image

Msg 1033, Level 15, State 1, Procedure USACusts, Line 249
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and
common table expressions, unless TOP, OFFSET or FOR XML is also specified.

The error message indicates that T-SQL allows the ORDER BY clause only in exceptional cases—when the TOP, OFFSET-FETCH, or FOR XML option is used. 错误消息表明，T-SQL仅在使用TOP、OFFSET-FETCH或FOR XML选项的例外情况下才允许ORDER BY子句。In those cases, the ORDER BY clause serves a purpose other than its usual presentation purpose. 在这些情况下，ORDER BY子句的作用不是其通常的表达目的。Even standard SQL has a similar restriction, with a similar exception when the OFFSET-FETCH option is used.即使是标准SQL也有类似的限制，使用OFFSET-FETCH选项时也有类似的例外。

Because T-SQL allows an ORDER BY clause in a view when TOP or OFFSET-FETCH is also specified, some people think they can create “ordered views.” 由于T-SQL允许在还指定了TOP或OFFSET-FETCH时在视图中使用ORDER BY子句，因此有些人认为他们可以创建“有序视图”。One of the ways people try to achieve this is by using TOP (100) PERCENT, like the following:人们尝试实现这一目标的方法之一是使用TOP (100) PERCENT，如下所示：

Click here to view code image

ALTER VIEW Sales.USACusts
AS

SELECT TOP (100) PERCENT
  custid, companyname, contactname, contacttitle, address,
  city, region, postalcode, country, phone, fax
FROM Sales.Customers
WHERE country = N'USA'
ORDER BY region;
GO

Even though the code is technically valid and the view is created, you should be aware that if an outer query against the view doesn't have an ORDER BY clause, presentation order is not guaranteed. 尽管代码在技术上是有效的，并且视图是创建的，但您应该知道，如果针对视图的外部查询没有ORDER BY子句，则不能保证呈现顺序。For example, run the following query against the view:例如，对视图运行以下查询：

Click here to view code image

SELECT custid, companyname, region
FROM Sales.USACusts;

Here's the output from one of my executions, showing that the rows are not sorted by region:下面是我的一次执行的输出，显示行不是按区域排序的：

Click here to view code image

custid      companyname             region
----------- ----------------------- ---------------
32          Customer YSIQX          OR
36          Customer LVJSO          OR
43          Customer UISOJ          WA
45          Customer QXPPT          CA
48          Customer DVFMB          OR
55          Customer KZQZT          AK
65          Customer NYUHS          NM
71          Customer LCOUJ          ID
75          Customer XOJYP          WY
77          Customer LCYBZ          OR
78          Customer NLTYP          MT
82          Customer EYHKM          WA
89          Customer YBQTI          WA

If the outer query doesn't have an ORDER BY clause but the result seems to be ordered, it could be because of certain physical conditions and optimization choices, but those things are not guaranteed to be repeatable. 如果外部查询没有ORDER BY子句，但结果似乎是有序的，这可能是因为某些物理条件和优化选择，但这些事情不能保证是可重复的。The only way to guarantee presentation order is to have an ORDER BY clause in the outer query. Nothing else counts.保证呈现顺序的唯一方法是在外部查询中使用ORDER BY子句。其他都不重要。

In old versions of SQL Server when the inner query had the combination of TOP (100) PERCENT and ORDER BY and the outer query didn't have an ORDER BY clause, you got the rows ordered. 在旧版本的SQL Server中，当内部查询包含TOP (100) PERCENT和ORDER BY组合，而外部查询没有ORDER BY子句时，行被排序。It wasn't a guaranteed behavior, but it happened to be the result of the way the optimizer handled things. 这不是一种保证的行为，但它碰巧是优化器处理事情的方式的结果。At some point, Microsoft added smarter optimization that optimizes out this meaningless combination. 在某种程度上，微软增加了更智能的优化，优化了这个毫无意义的组合。Unfortunately, the optimizer doesn't yet optimize out the combination when the inner query uses the OFFSET clause with 0 ROWS, and without a FETCH clause, like the following:不幸的是，当内部查询使用带有0 ROWS的OFFSET子句且没有FETCH子句时，优化器尚未优化组合，如下所示：

Click here to view code image

ALTER VIEW Sales.USACusts
AS

SELECT
  custid, companyname, contactname, contacttitle, address,
  city, region, postalcode, country, phone, fax
FROM Sales.Customers
WHERE country = N'USA'
ORDER BY region
OFFSET 0 ROWS;
GO

At the moment, when I query the view and don't indicate an ORDER BY clause in the outer query, the result rows happen to be sorted by region. 目前，当我查询视图时，在外部查询中没有指示ORDER BY子句，结果行恰好是按区域排序的。But I stress—do not assume that's guaranteed. 但我强调，这并不能保证。It happens to be the case because of the current optimization. 由于当前的优化，情况恰好如此。If you need a guarantee that the rows will be returned sorted, you need an ORDER BY clause in the outer query.如果需要保证返回的行是经过排序的，则需要在外部查询中使用ORDER BY子句。

Do not confuse the behavior of a query that is used to define a table expression with an outer query. 不要将用于定义表表达式的查询行为与外部查询混淆。An outer query with an ORDER BY clause and a TOP or OFFSET-FETCH option does guarantee presentation order. 带有ORDER BY子句和TOP或OFFSET-FETCH选项的外部查询可以保证呈现顺序。The simple rule is that if the outer query has an ORDER BY clause, you have a presentation ordering guarantee, regardless of whether that ORDER BY clause also serves another purpose.简单的规则是，如果外部查询有一个ORDER BY子句，那么不管ORDER BY子句是否还有其他用途，都有一个表示顺序保证。

Before you look at the ENCRYPTION option, run the following code to alter the definition of the USACusts view to its original version:在查看ENCRYPTION选项之前，请运行以下代码，将USACusts视图的定义更改为其原始版本：

Click here to view code image

ALTER VIEW Sales.USACusts
AS

SELECT
  custid, companyname, contactname, contacttitle, address,
  city, region, postalcode, country, phone, fax
FROM Sales.Customers
WHERE country = N'USA';
GO

To get the definition of the view, invoke the OBJECT_DEFINITION function like this:要获取视图的定义，请如下调用OBJECT_DEFINITION函数：

Click here to view code image

SELECT OBJECT_DEFINITION(OBJECT_ID('Sales.USACusts'));

The text with the definition of the view is available because the view was created without the ENCRYPTION option. 具有视图定义的文本可用，因为创建视图时未使用ENCRYPTION选项。You get the following output:您将获得以下输出：

Click here to view code image

CREATE VIEW Sales.USACusts
AS

SELECT
  custid, companyname, contactname, contacttitle, address,
  city, region, postalcode, country, phone, fax
FROM Sales.Customers
WHERE country = N'USA';

Next, alter the view definition—only this time, include the ENCRYPTION option:接下来，这次仅更改视图定义，包括ENCRYPTION选项：

Click here to view code image

ALTER VIEW Sales.USACusts WITH ENCRYPTION
AS

SELECT
  custid, companyname, contactname, contacttitle, address,
  city, region, postalcode, country, phone, fax
FROM Sales.Customers
WHERE country = N'USA';
GO

Try again to get the text with the definition of the view:请再次尝试获取包含视图定义的文本：

Click here to view code image

SELECT OBJECT_DEFINITION(OBJECT_ID('Sales.USACusts'));

This time you get a NULL back.这一次你得到了一个NULL值。

As an alternative to the OBJECT_DEFINITION function, you can use the sp_helptext stored procedure to get object definitions. 作为OBJECT_DEFINITION函数的替代方法，您可以使用sp_helptext存储过程来获取对象定义。For example, the following code requests the object definition of the USACusts view:例如，以下代码请求USACusts视图的对象定义：

Click here to view code image

EXEC sp_helptext 'Sales.USACusts';

Because in our case the view was created with the ENCRYPTION option, you will not get the object definition back; instead, you'll get the following message:因为在例子中，视图是使用ENCRYPTION选项创建的，您将无法获得对象定义；相反，您将收到以下消息：

Click here to view code image

The text for object 'Sales.USACusts' is encrypted.

For example, alter the USACusts view with the SCHEMABINDING option:

Click here to view code image

ALTER VIEW Sales.USACusts WITH SCHEMABINDING
AS

SELECT
  custid, companyname, contactname, contacttitle, address,
  city, region, postalcode, country, phone, fax
FROM Sales.Customers
WHERE country = N'USA';
GO

Now try to drop the address column from the Customers table:现在尝试从Customers表中删除address列：

Click here to view code image

ALTER TABLE Sales.Customers DROP COLUMN address;

You get the following error:出现以下错误：

Click here to view code image

Msg 5074, Level 16, State 1, Line 346
The object 'USACusts' is dependent on column 'address'.
Msg 4922, Level 16, State 9, Line 346
ALTER TABLE DROP COLUMN address failed because one or more objects access this column.

Without the SCHEMABINDING option, you would have been allowed to make such a schema change, as well as drop the Customers table altogether. This can lead to errors at run time when you try to query the view and referenced objects or columns do not exist. If you create the view with the SCHEMABINDING option, you can avoid these errors.

To support the SCHEMABINDING option, the object definition must meet a couple of requirements. The query is not allowed to use * in the SELECT clause; instead, you have to explicitly list column names. Also, you must use schema-qualified two-part names when referring to objects. Both requirements are actually good practices in general.

As you can imagine, creating your objects with the SCHEMABINDING option is a good practice.可以想象，使用SCHEMABINDING选项创建对象是一个很好的实践。

The query defining the view USACusts filters customers from the United States. The view is currently defined without CHECK OPTION. This means you can currently insert through the view customers from other countries, and you can update the country of existing customers through the view to one other than the United States. For example, the following code successfully inserts a customer from the United Kingdom through the view:

Click here to view code image

INSERT INTO Sales.USACusts(
  companyname, contactname, contacttitle, address,
  city, region, postalcode, country, phone, fax)
VALUES(
  N'Customer ABCDE', N'Contact ABCDE', N'Title ABCDE', N'Address ABCDE',
  N'London', NULL, N'12345', N'UK', N'012-3456789', N'012-3456789');

The row was inserted through the view into the Customers table. However, because the view filters only customers from the United States, if you query the view looking for the new customer, you get an empty set back:

Click here to view code image

SELECT custid, companyname, country
FROM Sales.USACusts
WHERE companyname = N'Customer ABCDE';

Query the Customers table directly to look for the new customer:

Click here to view code image

SELECT custid, companyname, country
FROM Sales.Customers
WHERE companyname = N'Customer ABCDE';

You get the customer in the output, because the new row made it to the Customers table:

Click here to view code image

custid      companyname        country
----------- ------------------ ---------------
92          Customer ABCDE     UK

Similarly, if you update a customer row through the view, changing the country attribute to a country other than the United States, the update succeeds. But that customer information doesn't show up anymore in the view because it doesn't satisfy the view's query filter.

If you want to prevent modifications that conflict with the view's filter, add WITH CHECK OPTION at the end of the query defining the view:

Click here to view code image

ALTER VIEW Sales.USACusts WITH SCHEMABINDING
AS

SELECT
  custid, companyname, contactname, contacttitle, address,
  city, region, postalcode, country, phone, fax
FROM Sales.Customers
WHERE country = N'USA'
WITH CHECK OPTION;
GO

Now try to insert a row that conflicts with the view's filter:

Click here to view code image

INSERT INTO Sales.USACusts(
  companyname, contactname, contacttitle, address,
  city, region, postalcode, country, phone, fax)
VALUES(
  N'Customer FGHIJ', N'Contact FGHIJ', N'Title FGHIJ', N'Address FGHIJ',
  N'London', NULL, N'12345', N'UK', N'012-3456789', N'012-3456789');

You get the following error:

Click here to view code image

Msg 550, Level 16, State 1, Line 387
The attempted insert or update failed because the target view either specifies WITH CHECK
OPTION or spans a view that specifies WITH CHECK OPTION and one or more rows resulting from the
operation did not qualify under the CHECK OPTION constraint.
The statement has been terminated.

When you're done, run the following code for cleanup:

Click here to view code image

DELETE FROM Sales.Customers
WHERE custid > 91;

DROP VIEW IF EXISTS Sales.USACusts;

T-SQL supports another type of table function called multi-statement TVF, which populates and returns a table variable. This type isn't considered a table expression because it's not based on a query.

For example, the following code creates an inline TVF called GetCustOrders in the TSQLV4 database:

Click here to view code image

USE TSQLV4;
DROP FUNCTION IF EXISTS dbo.GetCustOrders;
GO
CREATE FUNCTION dbo.GetCustOrders
  (@cid AS INT) RETURNS TABLE
AS
RETURN
  SELECT orderid, custid, empid, orderdate, requireddate,
    shippeddate, shipperid, freight, shipname, shipaddress, shipcity,
    shipregion, shippostalcode, shipcountry
  FROM Sales.Orders
  WHERE custid = @cid;
GO

This inline TVF accepts an input parameter called @cid, representing a customer ID, and returns all orders placed by the input customer. You query inline TVFs by using DML statements, which is the same way you query other tables. If the function accepts input parameters, you specify those in parentheses following the function's name. Also, make sure you provide an alias for the table expression. Providing a table expression with an alias is not always a requirement, but it is a good practice because it makes your code more readable and less prone to errors. For example, the following code queries the function to request all orders that were placed by customer 1:

Click here to view code image

SELECT orderid, custid
FROM dbo.GetCustOrders(1) AS O;

This code returns the following output:

orderid     custid
----------- -----------
10643       1
10692       1
10702       1
10835       1
10952       1
11011       1

As with tables, you can refer to an inline TVF as part of a join. For example, the following query joins the inline TVF returning customer 1's orders with the Sales.OrderDetails table, matching the orders with their respective order lines:

Click here to view code image

SELECT O.orderid, O.custid, OD.productid, OD.qty
FROM dbo.GetCustOrders(1) AS O
  INNER JOIN Sales.OrderDetails AS OD
    ON O.orderid = OD.orderid;

This code returns the following output:

Click here to view code image

orderid     custid      productid   qty
----------- ----------- ----------- ------
10643       1           28          15
10643       1           39          21
10643       1           46           2
10692       1           63          20
10702       1            3           6
10702       1           76          15
10835       1           59          15
10835       1           77           2
10952       1            6          16
10952       1           28           2
11011       1           58          40
11011       1           71          20

When you're done, run the following code for cleanup:

Click here to view code image

DROP FUNCTION IF EXISTS dbo.GetCustOrders;

The APPLY operator operates on two input tables; I'll refer to them as the “left” and “right” tables. The right table is typically a derived table or a TVF. The CROSS APPLY operator implements one logical-query processing phase—it applies the right table to each row from the left table and produces a result table with the unified result sets.

It might sound like the CROSS APPLY operator is similar to a cross join, and in a sense that's true. For example, the following two queries return the same result sets:

Click here to view code image

SELECT S.shipperid, E.empid
FROM Sales.Shippers AS S
  CROSS JOIN HR.Employees AS E;

SELECT S.shipperid, E.empid
FROM Sales.Shippers AS S
  CROSS APPLY HR.Employees AS E;

Remember that a join treats its two inputs as a set, and therefore there's no order between them. This means you cannot refer on one side to elements from the other. With APPLY, the left side is evaluated first, and the right side is evaluated per row from the left. So the right side can have references to elements from the left. For example, the following code uses the CROSS APPLY operator to return the three most recent orders for each customer:

Click here to view code image

SELECT C.custid, A.orderid, A.orderdate
FROM Sales.Customers AS C
  CROSS APPLY
    (SELECT TOP (3) orderid, empid, orderdate, requireddate
     FROM Sales.Orders AS O
     WHERE O.custid = C.custid
     ORDER BY orderdate DESC, orderid DESC) AS A;

You can think of the table expression A as a correlated derived table. In terms of logical-query processing, the right table expression (a derived table, in this case) is applied to each row from the Customers table. Notice in the inner query's filter the reference to the attribute C.custid from the left table. The derived table returns the three most recent orders for the current customer from the left row. Because the derived table is applied to each left row, the CROSS APPLY operator returns the three most recent orders for each customer.

Here's the output of this query, shown in abbreviated form:

Click here to view code image

custid      orderid     orderdate
----------- ----------- -----------
1           11011       2016-04-09
1           10952       2016-03-16
1           10835       2016-01-15
2           10926       2016-03-04
2           10759       2015-11-28
2           10625       2015-08-08
3           10856       2016-01-28
3           10682       2015-09-25
3           10677       2015-09-22
...

(263 row(s) affected)

Remember that you can use the standard OFFSET-FETCH option instead of TOP, like this:

Click here to view code image

SELECT C.custid, A.orderid, A.orderdate
FROM Sales.Customers AS C
  CROSS APPLY
    (SELECT orderid, empid, orderdate, requireddate
     FROM Sales.Orders AS O
     WHERE O.custid = C.custid
     ORDER BY orderdate DESC, orderid DESC
     OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY) AS A;

If the right table expression returns an empty set, the CROSS APPLY operator does not return the corresponding left row. For example, customers 22 and 57 did not place orders. In both cases, the derived table is an empty set; therefore, those customers are not returned in the output. If you want to return rows from the left side even if there are no matches on the right side, use OUTER APPLY. This operator has a second logical phase that preserves all left rows. It keeps the rows from the left side for which there are no matches on the right side, and it uses NULLs as placeholders on the right side. You probably noticed that, in the sense that OUTER APPLY preserves all left rows, it's similar to a LEFT OUTER JOIN. Because of the way APPLY works, there's no APPLY equivalent of a RIGHT OUTER JOIN.

For example, run the following code to return the three most recent orders for each customer and include in the output customers who did not place orders:

Click here to view code image

SELECT C.custid, A.orderid, A.orderdate
FROM Sales.Customers AS C
  OUTER APPLY
    (SELECT TOP (3) orderid, empid, orderdate, requireddate
     FROM Sales.Orders AS O
     WHERE O.custid = C.custid
     ORDER BY orderdate DESC, orderid DESC) AS A;

This time, customers 22 and 57 are included in the output, which is shown here in abbreviated form:

Click here to view code image

custid      orderid     orderdate
----------- ----------- -----------
1           11011       2016-04-09
1           10952       2016-03-16
1           10835       2016-01-15
2           10926       2016-03-04
2           10759       2015-11-28
2           10625       2015-08-08
3           10856       2016-01-28
3           10682       2015-09-25
3           10677       2015-09-22
...
22          NULL        NULL
...
57          NULL        NULL
...

(265 row(s) affected)

You might find it more convenient to work with inline TVFs instead of derived tables. This way, your code will be simpler to follow and maintain. For example, the following code creates an inline TVF called TopOrders that accepts as inputs a customer ID (@custid) and a number (@n), and returns the @n most recent orders for customer @custid:

Click here to view code image

DROP FUNCTION IF EXISTS dbo.TopOrders;
GO
CREATE FUNCTION dbo.TopOrders
  (@custid AS INT, @n AS INT)
  RETURNS TABLE
AS
RETURN
  SELECT TOP (@n) orderid, empid, orderdate, requireddate
  FROM Sales.Orders
  WHERE custid = @custid
  ORDER BY orderdate DESC, orderid DESC;
GO

You can now substitute the use of the derived table from the previous examples with the new function:

Click here to view code image

SELECT
  C.custid, C.companyname,
  A.orderid, A.empid, A.orderdate, A.requireddate
FROM Sales.Customers AS C
  CROSS APPLY dbo.TopOrders(C.custid, 3) AS A;

In terms of physical processing, nothing really changed because, as I stated earlier, the definition of table expressions is expanded, and SQL Server will in any case end up querying the underlying objects directly.

When you need to define reusable table expressions, use views or inline TVFs. When you do not need to support input parameters, use views; otherwise, use inline TVFs.

Use the APPLY operator when you want to apply a correlated table expression to each row from a source table and unify all result sets into one result table.

Click here to view code image

SELECT orderid, orderdate, custid, empid,
  DATEFROMPARTS(YEAR(orderdate), 12, 31) AS endofyear
FROM Sales.Orders
WHERE orderdate <> endofyear;

When you try to run this query, you get the following error:

Click here to view code image

Msg 207, Level 16, State 1, Line 233
Invalid column name 'endofyear'.

Explain what the problem is, and suggest a valid solution.

Table involved: TSQLV4 database, Sales.Orders table

Desired output:

empid       maxorderdate
----------- -------------
3           2016-04-30
6           2016-04-23
9           2016-04-29
7           2016-05-06
1           2016-05-06
4           2016-05-06
2           2016-05-05
5           2016-04-22
8           2016-05-06

(9 row(s) affected)

Table involved: Sales.Orders

Desired output:

Click here to view code image

empid       orderdate   orderid     custid
----------- ----------- ----------- -----------
9           2016-04-29  11058       6
8           2016-05-06  11075       68
7           2016-05-06  11074       73
6           2016-04-23  11045       10
5           2016-04-22  11043       74
4           2016-05-06  11076       9
3           2016-04-30  11063       37
2           2016-05-05  11073       58
2           2016-05-05  11070       44
1           2016-05-06  11077       65

(10 row(s) affected)

Table involved: Sales.Orders

Desired output (abbreviated):

Click here to view code image

orderid     orderdate   custid      empid       rownum
----------- ----------- ----------- ----------- -------
10248       2014-07-04  85          5           1
10249       2014-07-05  79          6           2
10250       2014-07-08  34          4           3
10251       2014-07-08  84          3           4
10252       2014-07-09  76          4           5
10253       2014-07-10  34          3           6
10254       2014-07-11  14          5           7
10255       2014-07-12  68          9           8
10256       2014-07-15  88          3           9
10257       2014-07-16  35          4           10
...

(830 row(s) affected)

Table involved: Sales.Orders

Desired output:

Click here to view code image

orderid     orderdate   custid      empid       rownum
----------- ----------- ----------- ----------- -------
10258       2014-07-17  20          1           11
10259       2014-07-18  13          4           12
10260       2014-07-19  56          4           13
10261       2014-07-19  61          4           14
10262       2014-07-22  65          8           15
10263       2014-07-23  20          9           16
10264       2014-07-24  24          6           17
10265       2014-07-25  7           2           18
10266       2014-07-26  87          3           19
10267       2014-07-29  25          4           20

(10 row(s) affected)

Table involved: HR.Employees

Desired output:

Click here to view code image

empid       mgrid       firstname  lastname
----------- ----------- ---------- --------------------
9           5           Patricia   Doyle
5           2           Sven       Mortensen
2           1           Don        Funk
1           NULL        Sara       Davis

(4 row(s) affected)

Tables involved: Sales.Orders and Sales.OrderDetails

When running the following code:

Click here to view code image

SELECT * FROM Sales.VEmpOrders ORDER BY empid, orderyear;

Desired output:

Click here to view code image

empid       orderyear   qty
----------- ----------- -----------
1           2014        1620
1           2015        3877
1           2016        2315
2           2014        1085
2           2015        2604
2           2016        2366
3           2014        940
3           2015        4436
3           2016        2476
4           2014        2212
4           2015        5273
4           2016        2313
5           2014        778
5           2015        1471
5           2016        787
6           2014        963
6           2015        1738
6           2016        826
7           2014        485
7           2015        2292
7           2016        1877
8           2014        923
8           2015        2843
8           2016        2147
9           2014        575
9           2015        955
9           2016        1140

(27 row(s) affected)

Table involved: Sales.VEmpOrders view

Desired output:

Click here to view code image

empid       orderyear   qty         runqty
----------- ----------- ----------- -----------
1           2014        1620        1620
1           2015        3877        5497
1           2016        2315        7812
2           2014        1085        1085
2           2015        2604        3689
2           2016        2366        6055
3           2014        940         940
3           2015        4436        5376
3           2016        2476        7852
4           2014        2212        2212
4           2015        5273        7485
4           2016        2313        9798
5           2014        778         778
5           2015        1471        2249
5           2016        787         3036
6           2014        963         963
6           2015        1738        2701
6           2016        826         3527
7           2014        485         485
7           2015        2292        2777
7           2016        1877        4654
8           2014        923         923
8           2015        2843        3766
8           2016        2147        5913
9           2014        575         575
9           2015        955         1530
9           2016        1140        2670

(27 row(s) affected)

Table involved: Production.Products

When issuing the following query:

Click here to view code image

SELECT * FROM Production.TopProducts(5, 2);

Desired output:

Click here to view code image

productid   productname        unitprice
----------- ------------------ ---------------
12          Product OSFNS      38.00
11          Product QMVUN      21.00

(2 row(s) affected)

Table involved: Production.Suppliers

Desired output (shown here in abbreviated form):

Click here to view code image

supplierid  companyname       productid   productname     unitprice
----------- ----------------- ----------- --------------- ----------
8           Supplier BWGYE    20          Product QHFFP   81.00
8           Supplier BWGYE    68          Product TBTBL   12.50
20          Supplier CIYNM    43          Product ZZZHR   46.00
20          Supplier CIYNM    44          Product VJIEO   19.45
23          Supplier ELCRN    49          Product FPYPN   20.00
23          Supplier ELCRN    76          Product JYGFE   18.00
5           Supplier EQPNC    12          Product OSFNS   38.00
5           Supplier EQPNC    11          Product QMVUN   21.00
...

(55 row(s) affected)

When you're done, run the following code for cleanup:

Click here to view code image

DROP VIEW IF EXISTS Sales.VEmpOrders;
DROP FUNCTION IF EXISTS Production.TopProducts;

Click here to view code image

WITH C AS
(
  SELECT *,
    DATEFROMPARTS(YEAR(orderdate), 12, 31) AS endofyear
  FROM Sales.Orders
)
SELECT orderid, orderdate, custid, empid, endofyear
FROM C
WHERE orderdate <> endofyear;

Click here to view code image

USE TSQLV4;

SELECT empid, MAX(orderdate) AS maxorderdate
FROM Sales.Orders
GROUP BY empid;

Click here to view code image

SELECT O.empid, O.orderdate, O.orderid, O.custid
FROM Sales.Orders AS O
  INNER JOIN (SELECT empid, MAX(orderdate) AS maxorderdate
              FROM Sales.Orders
              GROUP BY empid) AS D
    ON O.empid = D.empid
    AND O.orderdate = D.maxorderdate;

Click here to view code image

SELECT orderid, orderdate, custid, empid,
  ROW_NUMBER() OVER(ORDER BY orderdate, orderid) AS rownum
FROM Sales.Orders;

Click here to view code image

WITH OrdersRN AS
(
  SELECT orderid, orderdate, custid, empid,
    ROW_NUMBER() OVER(ORDER BY orderdate, orderid) AS rownum
  FROM Sales.Orders
)
SELECT * FROM OrdersRN WHERE rownum BETWEEN 11 AND 20;

You might wonder why you need a table expression here. Window functions (such as the ROW_NUMBER function) are allowed only in the SELECT and ORDER BY clauses of a query, and not directly in the WHERE clause. By using a table expression, you can invoke the ROW_NUMBER function in the SELECT clause, assign an alias to the result column, and refer to that alias in the WHERE clause of the outer query.

Here's the complete solution query:

Click here to view code image

WITH EmpsCTE AS
(
  SELECT empid, mgrid, firstname, lastname
  FROM HR.Employees
  WHERE empid = 9

  UNION ALL

  SELECT P.empid, P.mgrid, P.firstname, P.lastname
  FROM EmpsCTE AS C
    INNER JOIN HR.Employees AS P
      ON C.mgrid = P.empid
)
SELECT empid, mgrid, firstname, lastname
FROM EmpsCTE;

Click here to view code image

USE TSQLV4;
DROP VIEW IF EXISTS Sales.VEmpOrders;
GO
CREATE VIEW  Sales.VEmpOrders
AS

SELECT
  empid,
  YEAR(orderdate) AS orderyear,
  SUM(qty) AS qty
FROM Sales.Orders AS O
  INNER JOIN Sales.OrderDetails AS OD
    ON O.orderid = OD.orderid
GROUP BY
  empid,
  YEAR(orderdate);
GO

Click here to view code image

SELECT empid, orderyear, qty,
  (SELECT SUM(qty)
   FROM  Sales.VEmpOrders AS V2
   WHERE V2.empid = V1.empid
     AND V2.orderyear <= V1.orderyear) AS runqty
FROM  Sales.VEmpOrders AS V1
ORDER BY empid, orderyear;

Note that in Chapter 7, “Beyond the fundamentals of querying,” you'll learn techniques to compute running totals by using window functions.

Click here to view code image

USE TSQLV4;
DROP FUNCTION IF EXISTS Production.TopProducts;
GO
CREATE FUNCTION Production.TopProducts
  (@supid AS INT, @n AS INT)
  RETURNS TABLE
AS
RETURN
  SELECT TOP (@n) productid, productname, unitprice
  FROM Production.Products
  WHERE supplierid = @supid
  ORDER BY unitprice DESC;
GO

Alternatively, you can use the OFFSET-FETCH filter. You replace the inner query in the function with the following one:

Click here to view code image

  SELECT productid, productname, unitprice
  FROM Production.Products
  WHERE supplierid = @supid
  ORDER BY unitprice DESC
  OFFSET 0 ROWS FETCH NEXT @n ROWS ONLY;

Click here to view code image

SELECT S.supplierid, S.companyname, P.productid, P.productname, P.unitprice
FROM Production.Suppliers AS S
  CROSS APPLY Production.TopProducts(S.supplierid, 2) AS P;