SQL Server Technology Insider's 5 table expression

A table expression is a named query expression that represents a valid relational table. You can use a table expression in a data-processing statement just like any other table. SQL Server supports 4 types of table expressions: Derived tables (derived table), common table expressions (cte,common table expression), views, and inline table-valued functions (inline tvf,inline table-valued function).

Table expressions are not objects that are physically real, they are virtual. Queries for table expressions are converted inside the database engine to queries against the underlying objects. The benefits of using table expressions are often reflected in the logical aspects of the code, not the performance aspects.

1. Derived tables

A derived table (also known as a table subquery) is defined in the FROM clause of an external query, and the derived table exists in the outer query that defines it, as long as the outer query ends and the derived table does not exist. The query statement that defines the derived table is written in a pair of parentheses followed by the name of the AS clause and the derived table. For example, the following code defines a derived table named Usacusts, which is a query that returns all U.S. customers:

SELECT *from (select CustID, CompanyName      from sales.customers      = N'USA' As usacusts;

To effectively define any type of table expression, a query statement must meet three requirements:

(1). There is no guarantee of a certain order. A table expression represents a table, and rows in a relational table are not in a fixed order. ANSI SQL does not allow an ORDER BY clause in the query statement that defines the table expression table, and T-SQL generally adheres to this limitation, with only one exception-when top is specified in the statement. In a query statement with the top option, the ORDER BY clause has only one logical purpose: Define which rows to filter out for the top option.

(2). All columns must have a name.

(3). All column names must be unique.

1.1 Assigning column Aliases

(1) Query the derived table in the form of an inline alias

Year (OrderDate) as OrderYear, CustID from      sales.orders) as DGROUP by OrderYear;

(2) querying a derived table with an external naming format

Select OrderYear, COUNT (DISTINCT CustID) as Numcustsfrom (select Year (OrderDate), CustID from      sales.orders) as D ( OrderYear, CustID) GROUP byOrderYear;

2. Common table Expressions (CTE)

The following code defines a CTE named Usacusts, whose internal query returns all customers from the United States, and the external query selects all the rows in the CTE, as shown in the following example:

With usacusts as (  SELECT CustID, CompanyName from  sales.customers  = N'USA' )usacusts;

2.1 Assigning column Aliases

The CTE also supports the naming of column aliases in two formats-inline and external formats. For inline format, to specify <expression> as <column_alias>; for external formats, specify a list of target columns in a pair of parentheses following the CTE name. Examples are as follows:

-- Inline column Aliasingwith C  as (SELECT year (OrderDate) as OrderYear, CustID from  sales.orders)-- External column Aliasingwith C (OrderYear, CustID) as (  SELECT Year (OrderDate), CustID from  sales.orders) 

2.2 Defining multiple CTE

On the surface, the difference between a derived table and a CTE may only be semantic. However, the CTE has several important advantages. One advantage is that if you need to reference another CTE in one CTE, you do not need to nest as a derived table, instead, simply define multiple CTE in the same with clause and separate them with commas. Each CTE can refer to all the CTE defined before it, while an external query can reference all the CTE.

With C1 as (  Select Year (OrderDate) as OrderYear, CustID from  sales.orders), C2 as (  select OrderYear, COUNT (DISTINCT CustID) as numcusts from  C1  

Multiple references to 2.3 CTE

A CTE is a definition of a re-query, which is already present in the FROM clause of an external query, so that multiple instances of the same CTE can be referenced.

2.4 Recursive CTE

Defining a recursive CTE requires at least two queries, the first query is called an anchor member (anchor member), and the second query is called a recursive member (recursive member). The anchor member query is only called once. A recursive member is a query that references the name of a CTE. A reference to a CTE name represents a logical "previous result set" in an execution sequence. When a recursive member is called for the first time, the previous result set represents the result set returned by the locating member, and each time a recursive member is called, a reference to the CTE name represents the result set returned by the previous call to the recursive member. On the results returned by the query, two member queries must be compatible on the number of columns and the data type of the corresponding column. Examples are shown below:

With Empscte as (  SELECT empid, Mgrid, FirstName, LastName from  HR. Employees  2(anchor member)    UNION all    SELECT c.empid, C.mgrid, C.firstname, C.lastname  empscte as P    JOIN HR. Employees as C      = p.empid ( recursive member )) SELECT Empid, Mgrid, FirstName, Lastnamefrom empscte;

The anchor member queries the Hr.employees table, returning only record rows that are equal to 2 by the employee. The first time a recursive member is called, returns the direct subordinates of employee 2, such as 3 and 5. The second call to the recursive member returns the direct subordinates for employees 3 and 5, and so on until the recursive member returns an empty result set.

3. View

So far, we've discussed two types of table expressions (derived tables and CTE) that are very limited in scope and are limited to being used within the scope of a single statement. As long as external queries that contain these table expressions complete, they disappear. The View and inline table-valued functions (inline TVF) are two reusable table expressions whose definitions are stored in a database object, and once created, the object is the permanent part of the database. Examples are shown below:

Use tsqlfundamentals2008;if object_id ('sales.usacusts') is not NULL  DROP VIEW sales.usacusts; Gocreate VIEW sales.usacustsasselect  CustID, CompanyName, ContactName, ContactTitle, address,  = N'  USA';

Generally recommended to avoid using the SELECT * statement in the context of the and view-closed application, columns are enumerated when the view is compiled, and new columns may not be added to the view automatically. If you add columns to the underlying table, and you want these new columns in the view, you can modify the views with the ALTER VIEW statement accordingly.

3.1 View Options

(1) Encryption options

You can use the encryption option when you create or modify a view, stored procedure, or trigger with a user-defined function. If you specify the encryption option, SQL server internally confuses the text that defines the object, and ordinary users cannot see this obfuscated text directly through any directory object, and only privileged users can access the text of the created object through special means. The following code can return the creation text of an object.

SELECT object_definition (object_id ('sales.usacusts')); GO

However, when you modify the view definition and specify the encryption option, the text of the view definition returns null

ALTER VIEW sales.usacusts with Encryptionas

(2) Schemabinding options

Once this option is specified, the referenced object cannot be deleted, and the referenced column cannot be deleted or modified.

ALTER VIEW sales.usacusts with SCHEMABINDING

(3) CHECK OPTION

The purpose of the option is to prevent data modifications that are performed through the view from conflicting with the filtering conditions set in the view, and if you do not set the check option, you can insert a UK customer through the usacusts view, and such updates are passed to the underlying data table. However, the modified customer will not be able to display in the view because it no longer satisfies the query filter criteria for the view. If you want to prevent this modification from conflicting with the view's query filtering criteria, simply add with CHECK option at the end of the query statement that defines the view.

-- Add CHECK OPTION to the Viewalter VIEW sales.usacusts with SCHEMABINDINGasselect  CustID, CompanyName, ContactName, ContactTitle, address,  = N'USA'withCHECK OPTION; GO

4. Inline table-valued functions

An inline table-valued function is an important table expression that supports input parameters, and in addition to input parameters, inline table-valued functions are similar to views in other ways. Examples are shown below:

-- Creating fn_getcustorders functionuse tsqlfundamentals2008;if object_id (   " dbo.fn_getcustorders   '   ' is not NULL DROP FUNCTION dbo.fn_getcustorders; Gocreate FUNCTION dbo.fn_getcustorders (@cid as INT) RETURNS TABLE  asreturn  SELECT OrderID, CustID, Empid, OrderDate, RequiredDate, ShippedDate, ShipperID, freight, shipname, ship Address, ShipCity, ShipRegion, Shippostalcode, ShipCountry from sales.orders where Cust  ID   =   GO  

-- Test functionselect OrderID, Custidfrom dbo.fn_getcustorders (1) as CO;

5.APPLY operator

The Apply operator supports two forms: cross apply and outer apply. The Apply operator operates on two input tables, the second of which can be a table expression, which is called the left table and the right table, respectively. Cross apply implements a logical processing step: the right table expression is applied to each row in the left table, and the result set is combined to produce a uniform result table. Examples are shown below:

The following code uses cross apply to return the latest three orders for each customer:

Cross APPLY    (SELECT TOP (3) OrderID, Empid, OrderDate, RequiredDate      From Sales.orders as O     =   c.custid     ORDER by OrderDate DESC, OrderID desc) as A;

If the right table expression returns an empty set, cross apply does not return the corresponding left data row. If you want to return rows from the corresponding left table when the right table expression returns an empty set, you can use the outer apply operator instead of cross apply.

Summarize

Table expressions can simplify code, improve code maintainability, and encapsulate query logic. You can use a derived table or a CTE when you need to use table expressions, and you do not plan to reuse their definitions. The CTE has two advantages over derived tables: The CTE is not used as nested as a derived table, and multiple instances of the same CTE can also be referenced, and derived tables cannot be used in this way.

When you need to define reusable table expressions, you can use views and inline table-valued functions. If you do not need to support input parameters, use the view instead of the inline table-valued function

SQL Server Technology Insider's 5 table expression