Today, we will be discussing an important concept in SQL Server called Common Table Expressions (CTE). This topic was brought to my attention by Matthew Spieth, a SQL Server expert from Ohio, who recently had a conversation with his colleagues about the confusion between CTE and Temp Tables.

Matthew made a valid point that many users often confuse CTE with Temp Tables. While they may seem similar, they are actually different. CTEs are more like Views and can be updated just like views.

Let’s dive deeper into this concept. When you update a Temp Table, the changes remain within the scope of the Temp Table and do not propagate to the underlying base table. However, this is not the case with CTEs. When you update a CTE, it updates the underlying base table, just like a view does.

Let’s take a look at an example to illustrate this behavior:

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';

In this example, we first check the value in the base table. Then, we create a CTE with the same data and update the CTE. Finally, we check the base table again and see that the value has been updated.

As you can see, you can update a CTE and it will update the base table. This is a powerful feature that can be used in various scenarios.

If you come across similar scenarios or have any other interesting SQL Server concepts to share, please let me know. I would be happy to give credit to you and share it on my blog.