SQL SERVER – CTE can be Updated

Today I have received a fantastic email from Matthew Spieth. SQL Server expert from Ohio. He recently had a great conversation with his colleagues in the office and wanted to make sure that everybody who reads this blog knows about this little feature which is commonly confused.

Here is his statement and we will start our story with Matthew’s own statement: “Users often confuse CTE with Temp Table but technically they both are different, CTE are like Views and they can be updated just like views.

Very true statement from Matthew. I totally agree with what he is saying. Just like him, I have enough, time came across a situation when developers think CTE is like temp table. When you update temp table, it remains in the scope of the temp table and it does not propagate it to the table based on which temp table is built. However, this is not the case when it is about CTE, when you update CTE, it updates underlying table just like view does.

Here is the working example of the same built by Matthew to illustrate this behavior.

Check the value in the base table first.

USE AdventureWorks2012;
-- Check - The value in the base table is updated
SELECT Color
FROM [Production].[Product]
WHERE ProductNumber = 'CA-6738';

Now let us build CTE with the same data.

;WITH CTEUpd(ProductID, Name, ProductNumber, Color)
AS(
SELECT ProductID, Name, ProductNumber, Color
FROM [Production].[Product]
WHERE ProductNumber = 'CA-6738')

Now let us update CTE with following code.

-- Update CTE
UPDATE CTEUpd SET Color = 'Rainbow';

Now let us check the BASE table based on which the CTE was built.

-- Check - The value in the base table is updated
SELECT Color
FROM [Production].[Product]
WHERE ProductNumber = 'CA-6738';

That’s it! You can update CTE and it will update the base table.

Here is the script which you should execute all together.

USE AdventureWorks2012;
-- Check - The value in the base table is updated
SELECT Color
FROM [Production].[Product]
WHERE ProductNumber = 'CA-6738';
-- Build CTE
;WITH CTEUpd(ProductID, Name, ProductNumber, Color)
AS(
SELECT ProductID, Name, ProductNumber, Color
FROM [Production].[Product]
WHERE ProductNumber = 'CA-6738')
-- Update CTE
UPDATE CTEUpd SET Color = 'Rainbow';
-- Check - The value in the base table is updated
SELECT Color
FROM [Production].[Product]
WHERE ProductNumber = 'CA-6738';

If you are aware of such scenario, do let me know and I will post this on my blog with due credit to you.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Does Use of CTE Change the Order of Join in Inner Join

I just had an interesting conversation the day before when I was discussing about Join Order in one of my recent presentations.

The comment which triggered all the conversation was “If I want to change the order of how tables are joined in SQL Server, I prefer to use CTE instead of Join Orders”. 

During the conversation user was suggesting that he wanted his tables to be joined in certain ways in SQL Server but SQL Server Engine Optimizer was re-organizing everything to give the most optimal performance. His needs were to join tables in certain ways and did not care about the performance. To join tables as per his need he had to use a FORCE ORDER hint of the table. However, he recently learned about CTE and believed that CTE will help him to achieve his tasks without the help of FORCE ORDER hint.

He believed that as CTE syntax is constructed before the SELECT statement SQL Server will build a result set instead first and will use the same resultset to join the SELECT statement following the CTE. Well, the casual conversation converted to debate and it was getting very difficult as everybody started to express their opinion with very loud voice. Finally, we decided to run an example on my machine.

NOTE: The example uses INNER JOIN only and the conclusion applies to only INNER JOIN. In the case of OUTER JOIN there is an entire different story, we will cover that in future blog posts.

We created two examples and executed them one by one. Once we executed them, we compared their resultant – they were identical. Right after that we compared the order of the join for both the example.

Example 1: Without CTE

USE AdventureWorks2012
GO
SELECT c.CustomerID, p.BusinessEntityID
FROM Person.Person p
INNER JOIN Person.EmailAddress ea ON ea.BusinessEntityID = p.BusinessEntityID
INNER JOIN Person.PersonPhone ph ON ph.BusinessEntityID = p.BusinessEntityID
INNER JOIN Sales.PersonCreditCard pc ON pc.BusinessEntityID = p.BusinessEntityID
INNER JOIN Sales.Customer c ON c.CustomerID = p.BusinessEntityID;

Execution Plan:

Example 2: With CTE

USE AdventureWorks2012
GO
WITH MyCTE AS
(SELECT c.CustomerID
FROM Sales.PersonCreditCard pc
INNER JOIN Sales.Customer c ON c.CustomerID = pc.BusinessEntityID)
SELECT cte.CustomerID, p.BusinessEntityID
FROM Person.Person p
INNER JOIN Person.EmailAddress ea ON ea.BusinessEntityID = p.BusinessEntityID
INNER JOIN MyCTE cte ON cte.CustomerID = p.BusinessEntityID
INNER JOIN Person.PersonPhone ph ON ph.BusinessEntityID = p.BusinessEntityID;

Execution Plan:

When we compare both the execution plan, it is very clear that there is no change in the order of the table orders. Both the statements have a very similar execution plan and tables were joined in exactly the same order. The matter of the fact in the case of CTE, it was interesting to see that SQL Server started to join very first one table from CTE and another from SELECT statements and continued to build the entire execution plan.

The reason for this is very simple – CTE is not temporary table kind of object or feature. It is just an expression to represent your SELECT statement in such a way that it increases your readability and usability. They do not execute before the regular SELECT statement or build a result set before hand. It actually executes just like regular SELECT statement all together.

In summary: CTE does not impact Table Join Order when all Joins in the query are INNER JOIN. 

Here is one of the my favorite videos on this subject:

http://www.youtube.com/watch?v=Z7VuYBXX2dg

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL Server – Multiple CTE in One SELECT Statement Query

I have previously written many articles on CTE. One question I get often is how to use multiple CTE in one query or multiple CTE in SELECT statement. Let us see quickly two examples for the same. I had done my best to take simplest examples in this subject.

Option 1 :

/* Method 1 */
;WITH CTE1 AS (SELECT 1 AS Col1),
CTE2 AS (SELECT 2 AS Col2)
SELECT CTE1.Col1,CTE2.Col2
FROM CTE1
CROSS JOIN CTE2
GO

Option 2:

/* Method 2 */
;WITH CTE1 AS (SELECT 1 AS Col1),
CTE2 AS (SELECT COL1+1 AS Col2 FROM CTE1)
SELECT CTE1.Col1,CTE2.Col2
FROM CTE1
CROSS JOIN CTE2
GO

Please note the difference between options. If there is any other option than above mentioned two options, please leave your comment here.

Reference : Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – SQL SERVER – Simple Example of Recursive CTE – Part 2 – MAXRECURSION – Prevent CTE Infinite Loop

Yesterday I wrote about SQL SERVER – SQL SERVER – Simple Example of Recursive CTE. I right away received email from regular reader John Mildred that if I can prevent infinite recursion of CTE.

Sure! recursion can be limited. Use the option of MAXRECURSION.

USE AdventureWorks
GO
WITH Emp_CTE AS (
SELECT EmployeeID, ContactID, LoginID, ManagerID, Title, BirthDate
FROM HumanResources.Employee
WHERE ManagerID IS NULL
UNION ALL
SELECT e.EmployeeID, e.ContactID, e.LoginID, e.ManagerID, e.Title, e.BirthDate
FROM HumanResources.Employee e
INNER JOIN Emp_CTE ecte ON ecte.EmployeeID = e.ManagerID
)
SELECT *
FROM Emp_CTE OPTION (MAXRECURSION 5)
GO

Now if your CTE goes beyond 5th recursion it will throw an error and stop executing. If you put MAXRECURSION value too low it may be possible before your desire result is accomplished and will throw an error.

For example if you change MAXRECURSION to value 3. It will throw following error.

Msg 530, Level 16, State 1, Line 1
The statement terminated. The maximum recursion 3 has been exhausted before statement completion.

In summary MAXRECUSION is good way to protect your CTE to go into infinite loop.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Simple Example of Recursive CTE

Recursive is the process in which the query executes itself. It is used to get results based on the output of base query. We can use CTE as Recursive CTE (Common Table Expression). You can read my previous articles about CTE by searching at http://search.SQLAuthority.com .

Here, the result of CTE is repeatedly used to get the final resultset. The following example will explain in detail where I am using AdventureWorks database and try to find hierarchy of Managers and Employees.

USE AdventureWorks
GO
WITH Emp_CTE AS (
SELECT EmployeeID, ContactID, LoginID, ManagerID, Title, BirthDate
FROM HumanResources.Employee
WHERE ManagerID IS NULL
UNION ALL
SELECT e.EmployeeID, e.ContactID, e.LoginID, e.ManagerID, e.Title, e.BirthDate
FROM HumanResources.Employee e
INNER JOIN Emp_CTE ecte ON ecte.EmployeeID = e.ManagerID
)
SELECT *
FROM Emp_CTE
GO

In the above example Emp_CTE is a Common Expression Table, the base record for the CTE is derived by the first sql query before UNION ALL. The result of the query gives you the EmployeeID which don’t have ManagerID.

Second query after UNION ALL is executed repeatedly to get results and it will continue until it returns no rows. For above e.g. Result will have EmployeeIDs which have ManagerID (ie, EmployeeID of the first result).  This is obtained by joining CTE result with Employee table on columns EmployeeID of CTE with ManagerID of table Employee.

This process is recursive and will continue till there is no ManagerID who doesn’t have EmployeeID.

Reference : Pinal Dave (http://blog.SQLAuthority.com), Special thanks to Jr. Developers at my organization who helped me to prepare example.