T-SQL with keywords

T-SQL with keywords

The SELECT statement is the last logical step for SQL statement processing. Therefore, the following query may cause an error:

SELECT YEAR(OrderDate) AS OrderYear, COUNT(DISTINCT CustomerID) AS NumCustsFROM dbo.OrdersGROUP BY OrderYear;

Because group by is performed before Select, The orderyear column is not formed at that time.

If the query is successful, modify it as follows:

SELECT OrderYear, COUNT(DISTINCT CustomerID) AS NumCustsFROM (SELECT YEAR(OrderDate) AS OrderYear, CustomerID   FROM dbo.Orders) AS DGROUP BY OrderYear;

There is also a special Syntax:

SELECT OrderYear, COUNT(DISTINCT CustomerID) AS NumCustsFROM (SELECT YEAR(OrderDate), CustomerID   FROM dbo.Orders) AS D(OrderYear, CustomerID)GROUP BY OrderYear;

In the eyes of the author, he is very fond of this writing, because it is clearer, clearer, and easier to maintain.

Using Parameter targeting to generate a batch of results in a query is nothing to say.

Nested queries are logically executed from the inside out.

Multiple references. It is possible that your SQL statement contains a join combination after multiple queries from a table. For example, if you want to compare the number of customers each year with the number of customers in the previous year, you must join two instances of the same table. This is inevitable.

Common table expressions (CTE)

CTE is a new table type added in sql2005.

It is defined as follows:

With cte_name

As

(

Cte_query

)

Outer_query_refferring to_cte_name;

Note: Because the With keyword is already included in the standard T-SQL language, for distinction, the CTE adds ';' At the end of the statement as the stop character.

CTE instance 1 (result set alias)

WITH C AS( SELECT YEAR(OrderDate) AS OrderYear, CustomerID FROM dbo.Orders)SELECT OrderYear, COUNT(DISTINCT CustomerID) AS NumCustsFROM CGROUP BY OrderYear;

Of course, I personally recommend the following statement:

WITH C(OrderYear, CustomerID) AS( SELECT YEAR(OrderDate), CustomerID FROM dbo.Orders)SELECT OrderYear, COUNT(DISTINCT CustomerID) AS NumCustsFROM CGROUP BY OrderYear;

CTE instance 2 (multiple ctes)

WITH C1 AS( SELECT YEAR(OrderDate) AS OrderYear, CustomerID FROM dbo.Orders),C2 AS( SELECT OrderYear, COUNT(DISTINCT CustomerID) AS NumCusts FROM C1 GROUP BY OrderYear)SELECT OrderYear, NumCustsFROM C2WHERE NumCusts > 70;

CTE instance 3 (multiple references)

WITH YearlyCount AS( SELECT YEAR(OrderDate) AS OrderYear,  COUNT(DISTINCT CustomerID) AS NumCusts FROM dbo.Orders GROUP BY YEAR(OrderDate))SELECT Cur.OrderYear, Cur.NumCusts AS CurNumCusts, Prv.NumCusts AS PrvNumCusts, Cur.NumCusts - Prv.NumCusts AS GrowthFROM YearlyCount AS Cur LEFT OUTER JOIN YearlyCount AS Prv  ON Cur.OrderYear = Prv.OrderYear + 1;

CTE instance 4 (modify data)

1. dynamically assemble the query results from the customer table into the customersdups table:

IF OBJECT_ID('dbo.CustomersDups') IS NOT NULL DROP TABLE dbo.CustomersDups;GOWITH CrossCustomers AS( SELECT 1 AS c, C1.* FROM dbo.Customers AS C1, dbo.Customers AS C2)SELECT ROW_NUMBER() OVER(ORDER BY c) AS KeyCol, CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, FaxINTO dbo.CustomersDupsFROM CrossCustomers;

2. Use CTE to remove data. Only the records with the maximum keycol in the same mermerdups table are retained.

WITH JustDups AS( SELECT * FROM dbo.CustomersDups AS C1 WHERE KeyCol <  (SELECT MAX(KeyCol) FROM dbo.CustomersDups AS C2   WHERE C2.CustomerID = C1.CustomerID))DELETE FROM JustDups;

CTE instance 5 (object container)

It provides encapsulation capabilities and facilitates componentized programming. The author reminds me that the CTE cannot be directly embedded, but can be embedded by encapsulating the CTE into an object container and querying the container data from an external CTE.

The author also explains that using ctes has no value in view and udfs.

An example is as follows:

CREATE VIEW dbo.VYearCntASWITH YearCnt AS( SELECT YEAR(OrderDate) AS OrderYear,  COUNT(DISTINCT CustomerID) AS NumCusts FROM dbo.Orders GROUP BY YEAR(OrderDate))SELECT * FROM YearCnt;

CTE instance 6 (recursive ctes)

The author gave an example to describe the new content in sql2005, the recursion of ctes.

The employee information is returned based on the employee ID and contains information of all lower-level employees. The returned results contain the following fields: employeeid, reportsto, firstname, and lastname.

Here, the author gives an optimal indexing method:

CREATE UNIQUE INDEX idx_mgr_emp_ifname_ilname ON dbo.Employees(ReportsTo, EmployeeID) INCLUDE(FirstName, LastName);

The author's explanation: This index uses a separate query (local scan) to obtain the direct subordinates of each manager. Here, include (fristname, lastname) overwrites the column.

TIPS: What include indexes?

Include index is a new function of sql2005. The columns with the include index do not affect the physical storage order of the index rows. They are mounted on the 'index row' as a pendant. The purpose of hanging these 'pendants 'is to scan an index to obtain the additional data.

Return to the author's example. The following is the recursive code:

WITH EmpsCTE AS( SELECT EmployeeID, ReportsTo, FirstName, LastName FROM dbo.Employees WHERE EmployeeID = 5 UNION ALL SELECT EMP.EmployeeID, EMP.ReportsTo, EMP.FirstName, EMP.LastName FROM EmpsCTE AS MGR  JOIN dbo.Employees AS EMP   ON EMP.ReportsTo = MGR.EmployeeID)SELECT * FROM EmpsCTE;

Understanding: a recursive CTE contains at least two queries. The first query is similar to an anchor in the CTE body. This anchor only returns a valid table and serves as an anchor for recursion. As shown in the preceding example, the anchor returns only one row with an employee ID = 5. The Second 2nd queries are used as recursive members. This recursion ends when the query result of a subordinate member is null.

If you are worried that recursion will cause a permanent loop, you can use the following expression:

With cte_name as (cte_body) outer_query option (maxrecursion N );

The default N value is 100. When n = 0, there is no limit.

The SELECT statement is the last logical step for SQL statement processing. Therefore, the following query may cause an error:

SELECT YEAR(OrderDate) AS OrderYear, COUNT(DISTINCT CustomerID) AS NumCustsFROM dbo.OrdersGROUP BY OrderYear;

Because group by is performed before Select, The orderyear column is not formed at that time.

If the query is successful, modify it as follows:

SELECT OrderYear, COUNT(DISTINCT CustomerID) AS NumCustsFROM (SELECT YEAR(OrderDate) AS OrderYear, CustomerID   FROM dbo.Orders) AS DGROUP BY OrderYear;

There is also a special Syntax:

SELECT OrderYear, COUNT(DISTINCT CustomerID) AS NumCustsFROM (SELECT YEAR(OrderDate), CustomerID   FROM dbo.Orders) AS D(OrderYear, CustomerID)GROUP BY OrderYear;

In the eyes of the author, he is very fond of this writing, because it is clearer, clearer, and easier to maintain.

Using Parameter targeting to generate a batch of results in a query is nothing to say.

Nested queries are logically executed from the inside out.

Multiple references. It is possible that your SQL statement contains a join combination after multiple queries from a table. For example, if you want to compare the number of customers each year with the number of customers in the previous year, you must join two instances of the same table. This is inevitable.

Common table expressions (CTE)

CTE is a new table type added in sql2005.

It is defined as follows:

With cte_name

As

(

Cte_query

)

Outer_query_refferring to_cte_name;

Note: Because the With keyword is already included in the standard T-SQL language, for distinction, the CTE adds ';' At the end of the statement as the stop character.

CTE instance 1 (result set alias)

WITH C AS( SELECT YEAR(OrderDate) AS OrderYear, CustomerID FROM dbo.Orders)SELECT OrderYear, COUNT(DISTINCT CustomerID) AS NumCustsFROM CGROUP BY OrderYear;

Of course, I personally recommend the following statement:

WITH C(OrderYear, CustomerID) AS( SELECT YEAR(OrderDate), CustomerID FROM dbo.Orders)SELECT OrderYear, COUNT(DISTINCT CustomerID) AS NumCustsFROM CGROUP BY OrderYear;

CTE instance 2 (multiple ctes)

WITH C1 AS( SELECT YEAR(OrderDate) AS OrderYear, CustomerID FROM dbo.Orders),C2 AS( SELECT OrderYear, COUNT(DISTINCT CustomerID) AS NumCusts FROM C1 GROUP BY OrderYear)SELECT OrderYear, NumCustsFROM C2WHERE NumCusts > 70;

CTE instance 3 (multiple references)

WITH YearlyCount AS( SELECT YEAR(OrderDate) AS OrderYear,  COUNT(DISTINCT CustomerID) AS NumCusts FROM dbo.Orders GROUP BY YEAR(OrderDate))SELECT Cur.OrderYear, Cur.NumCusts AS CurNumCusts, Prv.NumCusts AS PrvNumCusts, Cur.NumCusts - Prv.NumCusts AS GrowthFROM YearlyCount AS Cur LEFT OUTER JOIN YearlyCount AS Prv  ON Cur.OrderYear = Prv.OrderYear + 1;

CTE instance 4 (modify data)

1. dynamically assemble the query results from the customer table into the customersdups table:

IF OBJECT_ID('dbo.CustomersDups') IS NOT NULL DROP TABLE dbo.CustomersDups;GOWITH CrossCustomers AS( SELECT 1 AS c, C1.* FROM dbo.Customers AS C1, dbo.Customers AS C2)SELECT ROW_NUMBER() OVER(ORDER BY c) AS KeyCol, CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, FaxINTO dbo.CustomersDupsFROM CrossCustomers;

2. Use CTE to remove data. Only the records with the maximum keycol in the same mermerdups table are retained.

WITH JustDups AS( SELECT * FROM dbo.CustomersDups AS C1 WHERE KeyCol <  (SELECT MAX(KeyCol) FROM dbo.CustomersDups AS C2   WHERE C2.CustomerID = C1.CustomerID))DELETE FROM JustDups;

CTE instance 5 (object container)

It provides encapsulation capabilities and facilitates componentized programming. The author reminds me that the CTE cannot be directly embedded, but can be embedded by encapsulating the CTE into an object container and querying the container data from an external CTE.

The author also explains that using ctes has no value in view and udfs.

An example is as follows:

CREATE VIEW dbo.VYearCntASWITH YearCnt AS( SELECT YEAR(OrderDate) AS OrderYear,  COUNT(DISTINCT CustomerID) AS NumCusts FROM dbo.Orders GROUP BY YEAR(OrderDate))SELECT * FROM YearCnt;

CTE instance 6 (recursive ctes)

The author gave an example to describe the new content in sql2005, the recursion of ctes.

The employee information is returned based on the employee ID and contains information of all lower-level employees. The returned results contain the following fields: employeeid, reportsto, firstname, and lastname.

Here, the author gives an optimal indexing method:

CREATE UNIQUE INDEX idx_mgr_emp_ifname_ilname ON dbo.Employees(ReportsTo, EmployeeID) INCLUDE(FirstName, LastName);

The author's explanation: This index uses a separate query (local scan) to obtain the direct subordinates of each manager. Here, include (fristname, lastname) overwrites the column.

TIPS: What include indexes?

Include index is a new function of sql2005. The columns with the include index do not affect the physical storage order of the index rows. They are mounted on the 'index row' as a pendant. The purpose of hanging these 'pendants 'is to scan an index to obtain the additional data.

Return to the author's example. The following is the recursive code:

WITH EmpsCTE AS( SELECT EmployeeID, ReportsTo, FirstName, LastName FROM dbo.Employees WHERE EmployeeID = 5 UNION ALL SELECT EMP.EmployeeID, EMP.ReportsTo, EMP.FirstName, EMP.LastName FROM EmpsCTE AS MGR  JOIN dbo.Employees AS EMP   ON EMP.ReportsTo = MGR.EmployeeID)SELECT * FROM EmpsCTE;

Understanding: a recursive CTE contains at least two queries. The first query is similar to an anchor in the CTE body. This anchor only returns a valid table and serves as an anchor for recursion. As shown in the preceding example, the anchor returns only one row with an employee ID = 5. The Second 2nd queries are used as recursive members. This recursion ends when the query result of a subordinate member is null.

If you are worried that recursion will cause a permanent loop, you can use the following expression:

With cte_name as (cte_body) outer_query option (maxrecursion N );

The default N value is 100. When n = 0, there is no limit.