-

COMMON TABLE EXPRESSIONS(cte):

It is used best used to extract the hierarchical data  and it is best explained with an example:

Problem
Many organizations have some type of hierarchy for business processes. When it comes to large organizations, the hierarchy can get very complex and large, so building a hierarchy in a RDBMS is a tedious task. We have to create views, cursors and so on, but using a CTE in SQL Server is a better solution to retrieve hierarchy-based data.
Solution
Common Table Expressions (CTE) have two types, recursive and non-recursive. We will see how the recursive CTE works with examples.
A recursive CTE can be explained in three parts:
  • Anchor Query: This is the first statement which is executed. This query will give the base data for the CTE.
  • Separator: This is the middle part where in we generally use a UNION ALL and few more operators.
  • Recursive Query: This is the main part, this is the CTE query which refers to the same CTE by recursion.

Let’s us create an example of CTE

Let’s take a scenario of an organization (org) chart. In this example the organization chart would start from "CEO" and end up at the “Purchase Department”.  Each department/person is linked to the predecessor as nodes. Let's see how a CTE can be used to achieve this in SQL Server. We will also touch base on how to use MAXRECURSION when using a CTE.
Orginisation Chart - Description: Example for CTE. This chart has a sample orginisation chart for our aritcle.
Let's create a sample table.
IF OBJECT_ID ('MyDepartment','U') IS NOT NULL
    DROP TABLE MyDepartment;
GO 
 
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE TABLE [dbo].[MyDepartment]
(
   [DepartmentID] [smallint] NOT NULL,
   [DepartmentName] [nvarchar](30) NOT NULL,
   [ParentID] [nvarchar](40) NULL,
   CONSTRAINT [PK_DepartmentID] PRIMARY KEY CLUSTERED ( [DepartmentID] ASC )
   WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] 
GO
Now the table is created and we can populate the table with values the table “MyDepartment”. The below insert statements can be executed to insert the data into “MyDepartment” table.
INSERT INTO [dbo].[MyDepartment] ([DepartmentID],[DepartmentName],[ParentID])
VALUES
   ('1','CEO',null),
   ('2','President','1'),
   ('3','Chairman','1'),
   ('4','Vice President','2'),
   ('5','Associate Vice President','4'),
   ('6','Senior Manager','4'),
   ('7','Delivery Manager','4'),
   ('8','Program Manager','4'),
   ('9','Project Manager','5'),
   ('10','Planning Manager','5'),
   ('11','Execution Manager','5'),
   ('12','Project Leader','6'),
   ('13','Project Planner','6'),
   ('14','Senior Project Lead','12'),
   ('15','Team Lead','12'),
   ('16','Sprint Lead','12'),
   ('17','Statistics Department','6'),
   ('18','Logistics Department','6'),
   ('19','Sales Account','7'),
   ('20','Customer Service','7'),
   ('21','Product Support B','8'),
   ('22','Sales Department','21'),
   ('23','Purchase Department','21'),
   ('24','Group Manager','8'),
   ('25','Overseas Department','24'),
   ('26','Domestic Department','24');
go
The ParentID for the top-most tree is set to NULL indicating that there is no parent for this row. Now that we have loaded the data we can code a query to walk the hierarchy using a Common Table Expression.
We need to report on the entire organizational structure under the CEO.  The following recursive code using a CTE will get us the output:
WITH OrgTree (DepartmentID, DepartmentName, ParentID, Tree)
AS
(
   SELECT DepartmentID, DepartmentName, ParentID , 0 AS Tree 
   FROM MyDepartment
   WHERE ParentID IS NULL
   UNION ALL
   SELECT MyDepartment.DepartmentID, MyDepartment.DepartmentName, MyDepartment.ParentID , OrgTree.Tree + 1
   FROM MyDepartment
   JOIN OrgTree ON MyDepartment.ParentID = OrgTree.DepartmentID
)
 
SELECT * FROM OrgTree ORDER BY Tree
Below is the output from the above query execution.
DepartmentIDDepartmentNameParentIDTree
1CEONULL0
2President11
3Chairman11
4Vice President22
5Associate Vice President43
6Senior Manager43
7Delivery Manager43
8Program Manager43
21Product Support B84
24Group Manager84
19Sales Account74
20Customer Service74
12Project Leader64
13Project Planner64
17Statistics Department64
18Logistics Department64
9Project Manager54
10Planning Manager54
11Execution Manager54
14Senior Project Lead125
15Team Lead125
16Sprint Lead125
25Overseas Department245
26Domestic Department245
22Sales Department215
23Purchase Department215

Anchor Query

Creating tables is one piece of it, inserting data is another group in the example. The important part is implementing the CTE using the WITH clause. For our example the name of the CTE is named as “OrgTree”. The first select in the CTE is used to extract the first node of the Tree which is “CEO” and the Parent ID is set as NULL. The below query will get the first node in our example.
SELECT DepartmentID, DepartmentName, ParentID , 0 AS Tree 
FROM MyDepartment
WHERE ParentID IS NULL
DepartmentIDDepartmentNameParentIDTree
1CEONULL0
Below I explain the data.  The left side has the parent data and the right side has the child data. If you notice DepartmentID 1 (left side) is the parent for DepartmentID (right side) 2 and 3. If you look further each DepartmentID is connected with the ParentID in the child table. Below is the image representation of the query that was generated above. The arrow connects the DepartmentID and ParentID for our reference.
CTE Image represenation - Description: This chart explains the relationship between parent and child in CTE

Separator and Recursive Query

The next section is the INNER JOIN combining the CTE where in recursion comes into picture, which intern refers to itself. The INNER JOIN retrieves the data by splitting the “MyDepartment” table into two parts using the OrgTree CTE and does a join and creates the CTE for us to query the CTE.
SELECT MyDepartment.DepartmentID, MyDepartment.DepartmentName, MyDepartment.ParentID , OrgTree.Tree + 1
FROM MyDepartment
JOIN OrgTree ON MyDepartment.ParentID = OrgTree.DepartmentID

Query Designer

The below Query Designer screen print is available for us to see the query in the Designer. The right-side is the CTE - “OrgTree”, since CTE will be created after execution the Query Designer does not show the columns, if you notice the “MyDepartment” table has the column and the INNER JOIN reference.
Query Designer - Description: Query Designer for CTE

MAXRECURSION

When it comes to using a CTE one of the problems faced is an infinite loop while forming the CTE. In general, when the parent and child query returns the same or equal value, the CTE may go into an infinite loop and the transaction may go into an infinite loop. To avoid this there is an option clause which can be used at the end of CTE SELECT command with the key word MAXRECURSION and the row count. Using 0 has no restriction, but in our example I have used a value of 10.
WITH OrgTree (DepartmentID, DepartmentName, ParentID, Tree)
AS
(
   SELECT DepartmentID, DepartmentName, ParentID, 0 AS Tree 
   FROM MyDepartment
   WHERE ParentID IS NULL
   UNION ALL
   SELECT MyDepartment.DepartmentID, MyDepartment.DepartmentName, MyDepartment.ParentID, OrgTree.Tree + 1
   FROM MyDepartment
   JOIN OrgTree ON MyDepartment.ParentID = OrgTree.DepartmentID
)
SELECT * FROM OrgTree OPTION (MAXRECURSION 10)
Example Queries
Try these example queries to find the data and see if you can come up with other scenarios and how to query the data.
-- return everyone under Program Manager (ParentID = 8)
WITH OrgTree (DepartmentID, DepartmentName, ParentID, Tree)
AS
(
   SELECT DepartmentID, DepartmentName, ParentID , 0 AS Tree 
   FROM MyDepartment
   WHERE ParentID = 8
   UNION ALL
   SELECT MyDepartment.DepartmentID, MyDepartment.DepartmentName, MyDepartment.ParentID , OrgTree.Tree + 1
   FROM MyDepartment
   JOIN OrgTree ON MyDepartment.ParentID = OrgTree.DepartmentID
)
SELECT * FROM OrgTree;


-- return Vice President (DepartmentID = 4) and direct reports (ParentID = 4)
WITH OrgTree (DepartmentID, DepartmentName, ParentID, Tree)
AS
(
   SELECT DepartmentID, DepartmentName, ParentID , 0 AS Tree 
   FROM MyDepartment
   WHERE DepartmentID = 4
   UNION ALL
   SELECT MyDepartment.DepartmentID, MyDepartment.DepartmentName, MyDepartment.ParentID , OrgTree.Tree + 1
   FROM MyDepartment
   JOIN OrgTree ON MyDepartment.ParentID = OrgTree.DepartmentID
   WHERE MyDepartment.ParentID = 4
)
SELECT * FROM OrgTree;

  
-- return everyone above Senior Manager (DepartmentID = 6)
WITH OrgTree(DepartmentName,ParentID,ReportsTo)AS
(
   SELECT T1.DepartmentName,T2.DepartmentID,T2.DepartmentName 
   FROM MyDepartment T1
   INNER JOIN MyDepartment T2 ON T1.ParentID=T2.DepartmentID 
   WHERE T1.DepartmentID=6
   UNION ALL
   SELECT OT.ReportsTo,T2.DepartmentID,T2.DepartmentName
   FROM OrgTree OT
   INNER JOIN MyDepartment T1 ON OT.ParentID=T1.DepartmentID
   INNER JOIN MyDepartment T2 ON T1.ParentID=T2.DepartmentID
)
SELECT * FROM OrgTree;


-- return list with of people with no direct reports
WITH OrgTree(ParentID, DepartmentID, DepartmentName, DepartmentLevel) AS 
(
    SELECT ParentID, DepartmentID, DepartmentName, 0 AS DepartmentLevell
    FROM MyDepartment 
    WHERE ParentID IS NULL
    UNION ALL
    SELECT e.ParentID, e.DepartmentID, e.DepartmentName,  DepartmentLevel + 1
    FROM MyDepartment AS e
    INNER JOIN OrgTree AS d ON e.ParentID = d.DepartmentID 
)

Comments

Post a Comment

Popular posts from this blog

-
Image
CLUSTERED INDEX VS NON CLUSTERED INDEX Clustered Index Only one clustered index can be there in a table because of the sorted order Sort the records and store them physically according to the order Data retrieval is faster than non-clustered indexes Do not need extra space to store logical structure A primary key by default creates a clustered index on it. Non Clustered Index There can be any number of non-clustered indexes in a table because of the number of unique keys we can create. Do not affect the physical order. Create a logical order for data rows and use pointers to physical data files Data insertion/update is faster than clustered index Use extra space to store logical structure A unique key/keys by dafault created an unclustered index on it. In Depth Explanation: Clustered Technically speaking, a clustered index is a B-Tree data structure where all the rows in a table are stored at the leaf level of the index. In other words, a clustered ind
Read more
-
Image
SQL JOINS Tutorial: INNER, OUTER, LEFT, RIGHT, CROSS What are JOINS? Joins help retrieving data from two or more database tables. The tables are mutually related using primary and foreign keys. id first_name last_name movie_id 1 Adam Smith 1 2 Ravi Kumar 2 3 Susan Davidson 5 4 Jenny Adrianna 8 6 Lee Pong 10 id title category 1 ASSASSIN'S CREED: EMBERS Animations 2 Real Steel(2012) Animations 3 Alvin and the Chipmunks Animations 4 The Adventures of Tin Tin Animations 5 Safe (2012) Action 6 Safe House(2012) Action 7 GIA 18+ 8 Deadline 2009 18+ 9 The Dirty Picture 18+ 10 Marley and me Romance   Types of joins Cross JOIN Cross JOIN is a simplest form of JOINs which matches each row from one database table to all rows of another. In other words it gives us combinations of each row of first table with all records in second table. Suppose we want to get all member records against all the movie records, we can use the script shown below to ge
Read more
-
Image
ANSI_NULL and QUOTED_IDENTIFIER: ANSI_NULL: When ever we are creat ing   or altering th e  SQL objects like the Stored Procedure s   and the User Defined Functions at t h at time   most of us use this.  Let us  understand this better with an example let us create a table and insert some records, CREATE   TABLE  #Demo ( FName  VARCHAR ( 200 ), LName  Varchar ( 200 ) ) Insert  #Demo  values ( 'Ravi' , 'Shekhar' ) Insert  #Demo  values ( 'Isha' ,NULL ) Insert  #Demo  values ( 'Santosh' , 'Thakur' )   SQL Server tend s to  behave differently when ever we use either  the  SET ANSI_NULL ON or the OFF . SET ANSI_NULL ON; If the ANSI_NULL is on then the comparison with NULL value with = or <> returns false. SET   ANSI_NULLS   ON SELECT   *   FROM  #Demo  WHERE  LName  =   NULL SELECT   *   FROM  #Demo  WHERE  LName  <>   NULL The  a bove query returns No result just  because  of the ANSI_NULL was ON. In such a
Read more