Shulou(Shulou.com)06/01 Report--

Table expression

Table expressions do not have any physical instantiation, they are virtual when querying table expressions, and internal queries are not nested, in other words, external and internal queries are merged directly into a query of an underlying object, and the benefits of using table expressions are usually related to the logical aspects of the code, not to the performance of the code-excerpt from the SQL Server 2012 basic tutorial. When using table expressions, we must meet the following three requirements, otherwise an error will be reported. Let's briefly introduce the four types of expressions in the following table.

(1) the sequence cannot be guaranteed.

(2) all columns must have names.

(3) all column names must be unique.

Derivative table

Derived tables (also known as subquery tables) are defined in the FROM clause of external queries, and their scope is external queries. Once the external query is complete, the derived table disappears. Let's look at an example of a simple derivation table.

USE TSQL2012GOSELECT * FROM (SELECT * FROM Sales.Customers WHERE country = Native USA') AS USACusts

Let's take a look at the conditions that the table expression query meets as specified above, and then we make the following query:

USE TSQL2012GOSELECT * FROM (SELECT * FROM Sales.Customers WHERE country = Native USA' ORDER BY custid) AS USACusts

When we add ORDER BY in the subquery after the above error occurred, that is to say, the above table expression requirements of the first point, table expression as a relational table, because the relationship is derived from the set theory, so can not guarantee the order of output data, see SQL Server 2012 basic tutorials say so, I also understand, as for the real reason still can not understand, anyway, do not carry out ORDER BY in the table expression. There is no need to say much about the second and third points of the requirement, for example, if you do not have an alias for the table at this time, you will definitely report an error, and when you join multiple tables, the column fields in the table must be the same, in order to ensure uniqueness, we must alias the column name to solve the problem of non-uniqueness. One of the advantages of using table expressions is that in any clause of an external query, the column aliases assigned in the SELECT clause of the internal query can be referenced, which can help us circumvent the practical problem that the column aliases assigned in the SELECT clause cannot be referenced in the query clause before the logical processing of the select clause (such as WHERE, GROUP BY). What does it mean? we know when doing regular queries. At this point, for example, WHERE and GROUP BY are carried out before SELECT, so it will cause us to fail to reference columns in SELECT through WHERE and GROUP BY. Let's take a look at the following example.

USE TSQL2012GOSELECT YEAR (orderdate) AS orderyear, COUNT (DISTINCT custid) AS custidsFROM Sales.OrdersGROUP BY orderyear

As shown above, we group the orderyear in SELECT by GROUP BY, but the GROUP BY operation comes before SELECT, so it results in the following error.

To solve this problem, we can query through the derived table in the table expression

USE TSQL2012GOSELECT orderyear, COUNT (DISTINCT custid) AS custidsFROM (SELECT YEAR (orderdate) AS orderyear, custid FROM Sales.Orders) AS SOGROUP BY orderyear

For derived tables, parameters can be referenced for variables such as stored procedures or functions or input parameters, and derived tables can be nested as follows:

USE TSQL2012GOSELECT orderyear, numcustsFROM (SELECT orderyear, COUNT (DISTINCT custid) AS numcustsFROM (SELECT YEAR (orderdate) AS orderyear, custid FROM Sales.Orders) AS D1 GROUP BY orderyear) AS D2WHERE numcusts > 70

When there are multiple tables nesting in this way, the code will become more and more complex, lengthy code is not conducive to maintenance, easily lead to errors, but also reduce the readability of the code. At this point, we can use the second form of the table expression, CTE.

Common table expression (CTE)

CTE is defined by a WITH statement and has the following common forms.

WITH [)] AS ()

Let's look at a simple example of CTE.

USE TSQL2012GOWITH USACusts AS (SELECT custid, companyname FROM Sales.Customers WHERE country = Native USA') SELECT * FROM USACusts

As with derived tables, CTE disappears as soon as the external query is completed. We can also use parameters in CTE, as follows:

USE TSQL2012GODECLARE @ 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 numcustsFROM CGROUP BY orderyear

We can also implement the same nesting of derived tables as follows:

USE TSQL2012GOWITH C1 AS (SELECT YEAR (orderdate) AS orderyear, custid FROM Sales.Orders), C2 AS (SELECT orderyear,COUNT (DISTINCT custid) AS numcustsFROM C1 GROUP BY orderyear) SELECT orderyear, numcustsFROM C2WHERE numcusts > 70

Here we use CTE to achieve the same results as derived tables, which are only semantically different from CTE, but the main advantage over derived tables is that they do not require multiple nesting like derived tables, while CTE does not need to be nested as long as it is defined, and each CTE appears separately in the code in a modular manner. Compared with the nested derived table approach, the modular approach greatly improves the readability and maintainability of the code. If there are multiple tables that need to be nested using CTE to achieve more crisp and contribute to the clarity of the code. Another advantage for derived tables is that CTE already exists in terms of the FROM clause of an external query, so you can reference multiple instances of the same CTE.

View (VIEW)

Views and embedded table-valued functions are two reusable table representation types whose definitions are stored as database objects that, after creation, are permanent parts of the database and can be deleted from the database only if they are explicitly deleted. Let's look at how to create a view and use it.

USE TSQL2012GOIF OBJECT_ID ('Sales.USACusts') IS NOT NULL DROP VIEW Sales.USACusts;GOCREATE VIEW Sales.USACustsASSELECT custid, companyname, contactname, contacttitle, [address] FROM Sales.CustomersWHERE country = N'USA'GO

After the view is created, the view object already exists in the database. At this point, we will query the view.

USE TSQL2012GOSELECT * FROM Sales.USACusts

Embedded table-valued function (TVF)

Embedded table-valued functions are reusable table expressions that support input parameters. All aspects are similar to views except that input parameters are supported. Let's take a look at how to create embedded table-valued functions.

USE TSQL2012GOIF OBJECT_ID ('dbo.GetCustOrders') IS NOT NULL DROP FUNCTION dbo.GetCustOrders;GOCREATE FUNCTION dbo.GetCustOrders (@ cid AS INT) RETURNS TABLEAS RETURN SELECT orderid, custid, empid, orderdate, requireddate, shippeddate, shipperid, shipcity, shipaddress, shipregion, freight FROM Sales.Orders WHERE custid = @ cidGO

Now that we have created the TVF, let's call the custom TVF

USE TSQL2012GOSELECT orderid, custidFROM dbo.GetCustOrders (1) AS O

Above we provide an alias for the table expression, which is not necessary, but is recommended because it makes the code more readable and error-free. In this section, we review the four ways of table expressions, and let's draw a conclusion for these four forms of table expressions.

(1) Table expression can simplify the code, improve the maintainability of the code and encapsulate the query logic.

(2) when you need to use table expressions and do not intend to reuse their definitions, you can use derived tables or CTE, while CTE has more advantages on derived tables without nesting CTE like derived tables, using CTE to make the code more modular and easy to maintain, in addition, multiple instances of the same CTE can be referenced, which can not be achieved by derived tables.

(3) when you need to use table expressions and define reusable table expressions, you can use views or embedded table-valued functions, and when you don't need to support input parameters, you can use views, otherwise, you should use embedded table-valued functions (TVF).

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.