This article focuses on several novel and interrelated concepts enhancements, and through practical example demonstrates these features.This article does not discuss all the new Transact-SQL functions
Introduction and scope
The paper describes the Microsoft SQL Server 2005 Beta 2 Transact-SQL in several new enhancements.These new features can improve your skills, query performance and error management.This article focuses on several novel and interrelated concepts enhancements, and through practical example demonstrates these features.This article does not discuss all the new Transact-SQL functions.
Prerequisites: The target audience should be able to skillfully use the Transact-SQL for certain queries and as Microsoft SQL Server 2000 components in the application.
Improve the query expression and DRI support
This section describes the relationship between the following new features and enhancements:
• Add sorting function
• New common table expression based on (CTE) of the recursive query
• the new PIVOT and APPLY relational operators
• Declarative referential integrity (DRI) Enhanced
Sort function
SQL Server 2005 introduces four new ranking functions: ROW_NUMBER, RANK, DENSE_RANK and NTILE.These new functions enable you to effectively analyze data and to sort the query result rows to provide value.You may find these useful new functions typically include: the results of consecutive integers assigned to the line for that page, scoring and drawing a histogram.
Speaker Statistics program
The following programs Speaker Statistics will be used to discuss and demonstrate different functions and their clauses.Including the calculation of the three major topics: database, development, and system management.Eleven speakers delivered a speech at the meeting and speak for their access to the range of 1 to 9 score.The results are summarized and stored in the table below SpeakerStats:
USE tempdb - or your own test database
CREATE TABLE SpeakerStats
(
speaker VARCHAR (10) NOT NULL PRIMARY KEY,
track VARCHAR (10) NOT NULL,
score INT NOT NULL,
pctfilledevals INT NOT NULL,
numsessions INT NOT NULL
)
SET NOCOUNT ON
INSERT INTO SpeakerStats VALUES (Dan, Sys, 3, 22, 4)
INSERT INTO SpeakerStats VALUES (Ron, Dev, 9, 30, 3)
INSERT INTO SpeakerStats VALUES (Kathy, Sys, 8, 27, 2)
INSERT INTO SpeakerStats VALUES (Suzanne, DB, 9, 30, 3)
INSERT INTO SpeakerStats VALUES (Joe, Dev, 6, 20, 2)
INSERT INTO SpeakerStats VALUES (Robert, Dev, 6, 28, 2)
INSERT INTO SpeakerStats VALUES (Mike, DB, 8, 20, 3)
INSERT INTO SpeakerStats VALUES (Michele, Sys, 8, 31, 4)
INSERT INTO SpeakerStats VALUES (Jessica, Dev, 9, 19, 1)
INSERT INTO SpeakerStats VALUES (Brian, Sys, 7, 22, 3)
INSERT INTO SpeakerStats VALUES (Kevin, DB, 7, 25, 4)
Each speaker in the table has a row containing the names of the speakers, topics, the average score, complete evaluation of the meeting participants relative to the percentage of the number of participants and speakers from the number of times.This section demonstrates how to use new sort function analysis of statistical data on the speaker to generate useful information.
Semantic
All four ranking functions all follow a similar syntax pattern:
Sort function
() OVER (
[PARTITION BY]
ORDER BY)
This function can only be two clauses in the query specified in the - in the SELECT clause or the ORDER BY clause.The following sections discuss in detail different functions.
ROW_NUMBER
ROW_NUMBER function allows you to query result rows to provide continuous integer value.For example, suppose you want to return all the speakers speaker, track, and score, score in descending order according to the results at the same time allocation to the continuous value from 1.The following query by using the ROW_NUMBER function and specify the OVER (ORDER BY score DESC) produce the desired result:
SELECT ROW_NUMBER () OVER (ORDER BY score DESC) AS rownum,
speaker, track, score
FROM SpeakerStats
ORDER BY score DESC
The following is the result set:
rownum speaker track score
------ ---------- ---------- -----------
1 Jessica Dev 9
2 Ron Dev 9
3 Suzanne DB 9
4 Kathy Sys 8
5 Michele Sys 8
6 Mike DB 8
7 Kevin DB 7
8 Brian Sys 7
9 Joe Dev 6
10 Robert Dev 6
11 Dan Sys 3
The speaker received the highest score line number 1, the speaker received the lowest score line number 11.ROW_NUMBER is always sorted according to the request line to generate different for different line number.Please note that if the OVER () option is specified in the ORDER BY list is not the only, the result is uncertain.This means that the query has more than one correct result; different calls in the query, you may get different results.For example, in our example, there are three different speakers receive the same highest score (9): Jessica, Ron, and Suzanne.Because SQL Server must be assigned to different speakers of different line number, so you should assume that allocated to Jessica, Ron, and Suzanne value of 1,2 and 3 are assigned in any order to these speakers.If the value of 1,2 and 3 are allocated to Ron, Suzanne, and Jessica, then the results should be equally correct.
If you specify a unique ORDER BY list, the result is always determined.For example, suppose the speaker scores between the same situation, you want to use the highest value of the separation has pctfilledevals.If the value remains the same, the value is used to separate the numsessions the highest priority.Finally, if the value is still the same, the name of the lowest lexicographic order to separate the speaker has.The ORDER BY list - score, pctfilledevals, numsessions and speaker - is unique, so the result is certain:
SELECT ROW_NUMBER () OVER (ORDER BY score DESC, pctfilledevals DESC,
numsessions DESC, speaker) AS rownum,
speaker, track, score, pctfilledevals, numsessions
FROM SpeakerStats
ORDER BY score DESC, pctfilledevals DESC, numsessions DESC, speaker
The following is the result set:
rownum speaker track score pctfilledevals numsessions
------ ---------- ---------- ----------- --------------------------
1 Ron Dev 9 30 3
2 Suzanne DB 9 30 3
3 Jessica Dev 9 19 1
4 Michele Sys 8 31 4
5 Kathy Sys 8 27 2
6 Mike DB 8 20 3
7 Kevin DB 7 25 4
8 Brian Sys 7 22 3
9 Robert Dev 6 28 2
10 Joe Dev 6 20 2
11 Dan Sys 3 22 4
New ranking function is one of the important benefits of their efficiency.SQL Server's optimizer scans the data only once, in order to calculate value.It is the completion of the work is: to use the column in the sort order placed on the index scan, or, if not create the appropriate index, scan and sort the data first.
Another advantage is that syntax is simple.To give you a feel by using the lower version of SQL Server-based set of methods used to calculate the order value is how difficult and inefficient, consider the following SQL Server 2000 query, it returns the same results as the previous query:
SELECT
(SELECT COUNT (*)
FROM SpeakerStats AS S2
WHERE S2.score> S1.score
OR (S2.score = S1.score
AND S2.pctfilledevals> S1.pctfilledevals)
OR (S2.score = S1.score
AND S2.pctfilledevals = S1.pctfilledevals
AND S2.numsessions> S1.numsessions)
OR (S2.score = S1.score
AND S2.pctfilledevals = S1.pctfilledevals
AND S2.numsessions = S1.numsessions
AND S2.speaker
FROM SpeakerStats AS S1
ORDER BY score DESC, pctfilledevals DESC, numsessions DESC, speaker
Obviously the query SQL Server 2005 queries than the much more complex.In addition, each base SpeakerStats line of the table, SQL Server must scan the table in another instance of all matching rows.For each row in the table based on the average about half of the table scan (at least) line.SQL Server 2005 query performance degradation is linear, and the performance of SQL Server 2000 queries are exponentially worse.Even in very small table, the performance difference is significant.For example, test the performance of the following query, which query SalesOrderHeader AdventureWorks database table to calculate the sales orders according to the order SalesOrderID the number of rows.SalesOrderHeader table has 31,465 rows.The first query uses the SQL Server 2005 ROW_NUMBER function, while the second query uses subqueries SQL Server 2000 technology:
- SQL Server 2005 query
SELECT SalesOrderID,
ROW_NUMBER () OVER (ORDER BY SalesOrderID) AS rownum
FROM Sales.SalesOrderHeader
- SQL Server 2000 query
SELECT SalesOrderID,
(SELECT COUNT (*)
FROM Sales.SalesOrderHeader AS S2
WHERE S2.SalesOrderID <= S1.SalesOrderID) AS rownum
FROM Sales.SalesOrderHeader AS S1
I'm on my laptop (Compaq Presario X1020U, CPU: Centrino 1.4 GH, RAM: 1GB, local HD) running on the test.SQL Server 2005 query just 1 second to complete, and SQL Server 2000 query takes about 12 minutes to complete.
Line number of a typical application is paging through query results.A given page size (number of lines as a unit) and the page number, need to return to the line belong to a given page.For example, suppose you want in accordance with the "score DESC, speaker" in order to return from the second page SpeakerStats table row, and assuming page size is three lines.The following query first sorted according to calculations derived table specified number of rows in D, then only the selected line number 4 to 6 lines (they belong to the second page):
SELECT backup bin conf config data eshow_sitemap.html generate.sh log maint sitemap.html svn tmp
FROM (SELECT ROW_NUMBER () OVER (ORDER BY score DESC, speaker) AS rownum,
speaker, track, score
FROM SpeakerStats) AS D
WHERE rownum BETWEEN 4 AND 6
ORDER BY score DESC, speaker
The following is the result set:
rownum speaker track score
------ ---------- ---------- -----------
4 Kathy Sys 8
5 Michele Sys 8
6 Mike DB 8
Expressed in more general terms is that, given the @ pagenum variable in the page number and the @ pagesize variable page size, the following query returns the expected page is the line:
DECLARE @ pagenum AS INT, @ pagesize AS INT
SET @ pagenum = 2
SET @ pagesize = 3
SELECT backup bin conf config data eshow_sitemap.html generate.sh log maint sitemap.html svn tmp
FROM (SELECT ROW_NUMBER () OVER (ORDER BY score DESC, speaker) AS rownum,
speaker, track, score
FROM SpeakerStats) AS D
WHERE rownum BETWEEN (@ pagenum-1) * @ pagesize +1 AND @ pagenum * @ pagesize
ORDER BY score DESC, speaker
Above the line for you only interested in a particular page in terms of a specific request is sufficient.However, when users issue multiple requests, the method can not meet the need, because each call to the query requires a complete scan of your table to calculate the line number.When the user might repeatedly request a different page, the page to more effectively, first with all the basic table rows (including the line number calculated) populate a temporary table, and the line number of the column containing the indexed:
SELECT ROW_NUMBER () OVER (ORDER BY score DESC, speaker) AS rownum, backup bin conf config data eshow_sitemap.html generate.sh log maint sitemap.html svn tmp
INTO # SpeakerStatsRN
FROM SpeakerStats
CREATE UNIQUE CLUSTERED INDEX idx_uc_rownum ON # SpeakerStatsRN (rownum)
Then, for each page requested, issue the following query:
SELECT rownum, speaker, track, score
FROM # SpeakerStatsRN
WHERE rownum BETWEEN (@ pagenum-1) * @ pagesize +1 AND @ pagenum * @ pagesize
ORDER BY score DESC, speaker
Only pages are expected to line will be scanned.
Section
Line group can be calculated within the independent sort values, rather than as a group sort of all calculated values of table rows.To do this, use the PARTITION BY clause, and specify a list of expressions to identify the sort value should be calculated independently for the line group.For example, the following query in accordance with the "score DESC, speaker" a separate allocation of the order of line numbers within each track:
SELECT track,
ROW_NUMBER () OVER (
PARTITION BY track
ORDER BY score DESC, speaker) AS pos,
speaker, score
FROM SpeakerStats
ORDER BY track, score DESC, speaker
The following is the result set:
track pos speaker score
---------- --- ---------- -----------
DB 1 Suzanne 9
DB 2 Mike 8
DB 3 Kevin 7
Dev 1 Jessica 9
Dev 2 Ron 9
Dev 3 Joe 6
Dev 4 Robert 6
Sys 1 Kathy 8
Sys 2 Michele 8
Sys 3 Brian 7
Sys 4 Dan 3
PARTITION BY clause is specified in the track list makes the track with the same group calculated separately for each line line number.
RANK, DENSE_RANK
RANK and ROW_NUMBER function DENSE_RANK function is very similar, because they provide the specified sort order value, and can require the row groups (segmented) provided internally.However, the difference is with the ROW_NUMBER, RANK, and DENSE_RANK to sort the column in the row with the same value of the distribution of the same order.When the ORDER BY list is not unique, and you do not want to in the ORDER BY list of rows with the same value to assign different sort, RANK and DENSE_RANK useful.RANK and DENSE_RANK the uses and differences between the two can be best explained with an example.Order by score DESC following query calculate the line number of different speakers, sort and close order value:
SELECT speaker, track, score,
ROW_NUMBER () OVER (ORDER BY score DESC) AS rownum,
RANK () OVER (ORDER BY score DESC) AS rnk,
DENSE_RANK () OVER (ORDER BY score DESC) AS drnk
FROM SpeakerStats
ORDER BY score DESC
The following is the result set:
speaker track score rownum rnk drnk
---------- ---------- ----------- ------ --- ----
Jessica Dev 9 1 1 1
Ron Dev 9 2 1 1
Suzanne DB 9 3 1 1
Kathy Sys 8 4 4 2
Michele Sys 8 5 4 2
Mike DB 8 6 4 2
Kevin DB 7 7 7 3
Brian Sys 7 8 7 3
Joe Dev 6 9 9 4
Robert Dev 6 10 9 4
Dan Sys 3 11 11 5
As discussed earlier, score column is not unique, so different speakers may have the same score.Line numbers down the score does represent the order, but the speaker has the same score line still get different numbers.Note, however, in the results, all the speakers with the same score are given the same sort order and close values.In other words, when the ORDER BY list is not the only, ROW_NUMBER is uncertain, and RANK and DENSE_RANK always determined.Order value and the difference between the value of close is that sort, sort Representative: line number with high scores plus 1, and close sequencing representative: significantly higher scores with the line number plus 1.You already know from the content so far, you can deduce the only time when the ORDER BY list, ROW_NUMBER, RANK, and DENSE_RANK produce exactly the same value.
NTILE
NTILE allows you to specify the order in accordance with the query result rows to a specified number of groups spread (tile) in the.Each row group are given different numbers: the first group 1, the second group 2, and so on.You can function in parentheses after the name of the requested group number specified in the ORDER BY clause of OVER option to specify the requested order.Group was calculated as the number of rows total_num_rows / num_groups.If more than the number of n, then the front of n groups obtained an additional line.Therefore, it may not all groups are given an equal number of rows, but the group could only difference between the maximum row size.For example, the following query in descending order according to score three group number will be assigned to a different speaker lines:
SELECT speaker, track, score,
ROW_NUMBER () OVER (ORDER BY score DESC) AS rownum,
NTILE (3) OVER (ORDER BY score DESC) AS tile
FROM SpeakerStats
ORDER BY score DESC
The following is the result set:
speaker track score rownum tile
---------- ---------- ----------- ------ ----
Jessica Dev 9 1 1
Ron Dev 9 2 1
Suzanne DB 9 3 1
Kathy Sys 8 4 1
Michele Sys 8 5 2
Mike DB 8 6 2
Kevin DB 7 7 2
Brian Sys 7 8 2
Joe Dev 6 9 3
Robert Dev 6 10 3
Dan Sys 3 11 3
In SpeakerStats table has 11 speakers.11 divided by 3 will be more than the number of group size 3 and 2, which means that the previous two groups will get an additional line (4 lines in each group), and the third group will not get the additional line (in the groupThere are 3 lines).Group number (tile) 1 is assigned to line 1 to 4, group number 2 is assigned to line 5 to 8, the group number 3 is assigned to line 9 to 11.This information can be generated by the histogram and uniform distribution of the project to each step.In our example, the first step, said the speaker with the highest scores, the second step, said the speaker with moderate scores, and the third step that has the lowest score of the speaker.Can use the CASE expression is a meaningful set of numbers to provide descriptive alternative meanings:
SELECT speaker, track, score,
CASE NTILE (3) OVER (ORDER BY score DESC)
WHEN 1 THEN High
WHEN 2 THEN Medium
WHEN 3 THEN Low
END AS scorecategory
FROM SpeakerStats
ORDER BY track, speaker
The following is the result set:
speaker track score scorecategory
---------- ---------- ----------- -------------
Kevin DB 7 Medium
Mike DB 8 Medium
Suzanne DB 9 High
Jessica Dev 9 High
Joe Dev 6 Low
Robert Dev 6 Low
Ron Dev 9 High
Brian Sys 7 Medium
Dan Sys 3 Low
Kathy Sys 8 High
Michele Sys 8 Medium
Recursive queries and common table expressions
This section explores the details of the recursive CTE expressions, and their common solution to the problem as to be applied to greatly simplify the traditional methods.
Common Table Expressions
Common table expression (CTE) is a reference to statements by definition a temporary named result set.In their simplest form, you can be more similar to the CTE as a type of non-continuous view of the improved version of derived tables.FROM clause in the query references a manner similar CTE derived tables and views referenced manner.CTE only once defined, can refer to it several times in the query.The definition of the CTE can be referenced in the same batch variables defined.You can even INSERT, UPDATE, DELETE, and CREATE VIEW statement in order to view a similar way with the use of use of CTE.However, CTE's real power lies in their recursive function, that CTE can contain their own references.In this paper, first described in a simple form of CTE, later described their recursive form.This article discusses conducted by CTE SELECT query.
When you want to reference the same table as the reference results, but do not want to create a persistent view in the database, you can use a derived table.However, the derived table does not have a CTE in the limit: you can not only define a derived table in the query once and then use it many times.Instead, you must define the same query multiple derived tables.However, you can define the CTE once and use it several times in the query, instead of continuing to save it in the database.
CTE in the provision of practical examples, first the basic syntax of the CTE and derived tables and views for comparison.The following is a view, derived tables, and CTE query within the general form:
View
CREATE VIEW
AS
GO
SELECT backup bin conf config data eshow_sitemap.html generate.sh log maint sitemap.html svn tmp
FROM
Derived table
SELECT backup bin conf config data eshow_sitemap.html generate.sh log maint sitemap.html svn tmp
FROM (
CTE
WITH
AS
(
)
SELECT backup bin conf config data eshow_sitemap.html generate.sh log maint sitemap.html svn tmp
FROM
After the keyword WITH, provide an alias for the CTE, and the results it provides an optional column alias list; prepared CTE of the subject; then reference it from the outside query.
Please note that if the CTE of the WITH clause is not the first statement in the batch, you should be in front of it by placing a semicolon (;) to be separated from the previous statement.WITH clause, the semicolon is used to avoid other use (for example, for table hints) confusion.Although you may find that not in all cases need to include the semicolon, but still recommended that you use it consistently.
As a practical example, consider the AdventureWorks database HumanResources.Employee and Purchasing.PurchaseOrderHeader table.Each employee in the specified column to the ManagerID manager.Each employee in the Employee table may be relevant in the PurchaseOrderHeader table orders.Suppose you want to return the number of orders each employee and the last order date, and returned in the same line manager similar to the detailed information.The following example shows how to use views, derived tables and CTE to achieve solutions:
View
CREATE VIEW VEmpOrders (EmployeeID, NumOrders, MaxDate)
AS
SELECT EmployeeID, COUNT (*), MAX (OrderDate)
FROM Purchasing.PurchaseOrderHeader
GROUP BY EmployeeID
GO
SELECT E. EmployeeID, OE.NumOrders, OE.MaxDate,
E. ManagerID, OM.NumOrders, OM.MaxDate
FROM HumanResources.Employee AS E
JOIN VEmpOrders AS OE
ON E. EmployeeID = OE.EmployeeID
LEFT OUTER JOIN VEmpOrders AS OM
ON E. ManagerID = OM.EmployeeID
Derived table
SELECT E. EmployeeID, OE.NumOrders, OE.MaxDate,
E. ManagerID, OM.NumOrders, OM.MaxDate
FROM HumanResources.Employee AS E
JOIN (SELECT EmployeeID, COUNT (*), MAX (OrderDate)
FROM Purchasing.PurchaseOrderHeader
GROUP BY EmployeeID) AS OE (EmployeeID, NumOrders, MaxDate)
ON E. EmployeeID = OE.EmployeeID
LEFT OUTER JOIN
(SELECT EmployeeID, COUNT (*), MAX (OrderDate)
FROM Purchasing.PurchaseOrderHeader
GROUP BY EmployeeID) AS OM (EmployeeID, NumOrders, MaxDate)
ON E. ManagerID = OM.EmployeeID
CTE
WITH EmpOrdersCTE (EmployeeID, NumOrders, MaxDate)
AS
(
SELECT EmployeeID, COUNT (*), MAX (OrderDate)
FROM Purchasing.PurchaseOrderHeader
GROUP BY EmployeeID
)
SELECT E. EmployeeID, OE.NumOrders, OE.MaxDate,
E. ManagerID, OM.NumOrders, OM.MaxDate
FROM HumanResources.Employee AS E
JOIN EmpOrdersCTE AS OE
ON E. EmployeeID = OE.EmployeeID
LEFT OUTER JOIN EmpOrdersCTE AS OM
ON E. ManagerID = OM.EmployeeID
The CTEs definition must be followed by an outer query, which may or may not refer to it.
You cannot refer to the CTE later in the batch after other intervening statements.
WITH clause, you can define the same number of CTE, each of which refer to previously defined CTE.Comma is used to separate the CTE.For example, suppose you want to count the number of employees in order the minimum, maximum, and the difference between:
WITH
EmpOrdersCTE (EmployeeID, Cnt)
AS
(
SELECT EmployeeID, COUNT (*)
FROM Purchasing.PurchaseOrderHeader
GROUP BY EmployeeID
),
MinMaxCTE (MN, MX, Diff)
AS
(
SELECT MIN (Cnt), MAX (Cnt), MAX (Cnt)-MIN (Cnt)
FROM EmpOrdersCTE
)
SELECT backup bin conf config data eshow_sitemap.html generate.sh log maint sitemap.html svn tmp FROM MinMaxCTE
The following is the result set:
MN MX Diff
----------- ----------- -----------
160,400,240
In EmpOrdersCTE, the calculated number of orders each employee.In MinMaxCTE, reference EmpOrdersCTE to calculate the number of employees in order the minimum, maximum, and the difference between the two.
Note in the CTE internal, you do not only refer to just in front of it defined CTE; Instead, you can refer to all previously defined CTE.Please note does not allow forward references: CTE can refer to the definition of the CTE in front of it and itself (see later in the recursive query), but not defined later reference in its CTE.For example, if you are in the same WITH statement defines a CTE C1, C2, C3, you can refer to C1 and C2 C2, but can not refer to C3.
In another example, the following code to generate a histogram to calculate the minimum and maximum values in the range of four orders, the number of employees.If these calculations it seems very complicated to you, please do not spend time trying to get to know them.This example is intended to demonstrate how to use the actual program statement in the same WITH statement multiple CTE (each of which may refer to the previous CTE).
WITH
EmpOrdersCTE (EmployeeID, Cnt)
AS
(
SELECT EmployeeID, COUNT (*)
FROM Purchasing.PurchaseOrderHeader
GROUP BY EmployeeID
),
MinMaxCTE (MN, MX, Diff)
AS
(
SELECT MIN (Cnt), MAX (Cnt), MAX (Cnt)-MIN (Cnt)
FROM EmpOrdersCTE
),
NumsCTE (Num)
AS
(
SELECT 1 AS Num
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
),
StepsCTE (Step, Fromval, Toval)
AS
(
SELECT
Num,
CAST (MN + ROUND ((Num-1) * ((Diff +1) / 4.), 0) AS INT),
CAST (MN + ROUND ((Num) * ((Diff +1) / 4.), 0) - 1 AS INT)
FROM MinMaxCTE CROSS JOIN NumsCTE
),
HistogramCTE (Step, Fromval, Toval, Samples)
AS
(
SELECT S. Step, S. Fromval, S. Toval, COUNT (EmployeeID)
FROM StepsCTE AS S
LEFT OUTER JOIN EmpOrdersCTE AS OE
ON OE.Cnt BETWEEN S. Fromval AND S. Toval
GROUP BY S. Step, S. Fromval, S. Toval
)
SELECT backup bin conf config data eshow_sitemap.html generate.sh log maint sitemap.html svn tmp FROM HistogramCTE
The following is the result set:
Step Fromval Toval Samples
----------- ----------- ----------- -----------
11,602,192
22,202,800
32,813,400
434,140,010
Please note that the second CTE (MinMaxCTE) refers to the first (EmpOrdersCTE); third (NumsCTE) does not refer to any CTE.The fourth (StepsCTE) refers to the second and third CTE, while the fifth (HistogramCTE) refers to the first and fourth CTE.
Recursive query
Non-recursive CTE to improve your skills.But for every piece of code using non-recursive CTE, you can usually Transact-SQL by using the other structure (for example, derived tables) to write to achieve the same result a short code.For the recursive CTE, the situation is different.This section describes the semantics of recursive queries, and for employees of the organization chart hierarchy and bill of materials (BOM) program provides the actual implementation.
Semantic
When the CTE reference itself, it is considered recursive.Recursive CTE is based on at least two queries (or, to use recursive queries to say, as a member) built.A non-recursive query, also known as the anchor member (AM).The other is the recursive query, also known as the recursive member (RM).Query separated by the UNION ALL operator.The following example shows a recursive CTE to simplify the general form:
WITH RecursiveCTE ()
AS
(
- Anchor Member:
- SELECT query that does not refer to RecursiveCTE
SELECT ...
FROM
...
UNION ALL
- Recursive Member
- SELECT query that refers to RecursiveCTE
SELECT ...
FROM
JOIN RecursiveCTE
...
)
- Outer Query
SELECT ...
FROM RecursiveCTE
...
Logically, you can implement the algorithm as a recursive CTE:
1.
Anchor member is activated.Set R0 (R, said, "Results") is generated.
2.
Recursive member is activated when the reference RecursiveCTE be set Ri (i = step number) as input.Set Ri + 1 is generated.
3.
The logic is repeated in step 2 run (incremental steps at each iteration number), until you return the empty set.
4.
External query execution, in reference to RecursiveCTE obtained when the accumulation of all previous steps (UNION ALL) results.
In the CTE has two or more members, but members and other members of the recursive (recursive or non-recursive) can have only one between the UNION ALL operator.Other operators (eg, UNION) can only be used between members of non-recursive.And support the conventional implicit conversion operators UNION and UNION ALL different, requiring all members of the recursive CTE exactly match the columns, including those with the same data type, length and accuracy.
In a recursive routine recursive CTE and traditional (not necessarily specific to SQL Server) the similarity between.Recursive routine usually consists of three important elements - the first call of the routine, the same recursive routine inspection and termination of the recursive calls.Recursive CTE anchor member in the traditional recursive routine that corresponds to the first call of the routine.Members of the corresponding recursive recursive call to the routine.Termination of inspections in the recursive routine is usually explicit (for example, by means of IF statements), but in a recursive CTE is implicit - when there is no return call from any of the previous row, recursion stops.
The following section describes the recursive CTE in a single parent node and multi-parent environment, the actual examples and usage.
Single-parent environment: Employees Organization Chart
Single-parent hierarchy for the program, the use of employee organization chart.
Note the example in this section uses a table named Employees, the table has a table with the AdventureWorks HumanResources.Employee in different structures.You should test in your own database or tempdb to run the code, rather than run the code in the AdventureWorks.
Employees table and the following code to generate data to populate it with an example:
USE tempdb - or your own test database
CREATE TABLE Employees
(
empid int NOT NULL,
mgrid int NULL,
empname varchar (25) NOT NULL,
salary money NOT NULL,
CONSTRAINT PK_Employees PRIMARY KEY (empid),
CONSTRAINT FK_Employees_mgrid_empid
FOREIGN KEY (mgrid)
REFERENCES Employees (empid)
)
CREATE INDEX idx_nci_mgrid ON Employees (mgrid)
SET NOCOUNT ON
INSERT INTO Employees VALUES (1, NULL, Nancy, $ 10000.00)
INSERT INTO Employees VALUES (2, 1, Andrew, $ 5000.00)
INSERT INTO Employees VALUES (3, 1, Janet, $ 5000.00)
INSERT INTO Employees VALUES (4, 1, Margaret, $ 5000.00)
INSERT INTO Employees VALUES (5, 2, Steven, $ 2500.00)
INSERT INTO Employees VALUES (6, 2, Michael, $ 2500.00)
INSERT INTO Employees VALUES (7, 3, Robert, $ 2500.00)
INSERT INTO Employees VALUES (8, 3, Laura, $ 2500.00)
INSERT INTO Employees VALUES (9, 3, Ann, $ 2500.00)
INSERT INTO Employees VALUES (10, 4, Ina, $ 2500.00)
INSERT INTO Employees VALUES (11, 7, David, $ 2000.00)
INSERT INTO Employees VALUES (12, 7, Ron, $ 2000.00)
INSERT INTO Employees VALUES (13, 7, Dan, $ 2000.00)
AS
(
)
1.
2.
AS
(
)
AS
(
)
AS
(
)
AS
(
)
AS
(
)
AS
(
0