What is the Massive Deal With SQL CTE?
CTE is shorter for frequent desk expressions. And for a single thing, it was 1st released in SQL:1999 specs. So, it is fairly normal. Even a lot more, subqueries and momentary tables are its shut kinfolk.
But What Is SQL CTE? And How Do You Use It?
This posting will enable you with that. Alongside with effortless examples, you can master this in no time. It’s a pain-free working experience to understand this nowadays.
But there is much more.
There’s a software you can use to pace up your coding greatly. Well, it’s not that SQL CTE is tough to code. But the resource is just about the corner to kick items up.
So, why not start it now? Let’s go!
What Is SQL CTE?
Let’s describe it by telling you what it is and what it is not.
What It Is
Initial, CTE is a temporary named consequence set. So, it has a name, and it’s short-term, like a momentary desk. The result established of a CTE is derived from a Find question. That end result set exists inside of the execution scope of its outer question. And the outer question can be a Select, INSERT, UPDATE, or MERGE. When it is done, the CTE is also long gone. So, with that confined scope, you just can’t reuse a CTE.
A CTE can also reference itself. And when it does, it becomes a recursive prevalent table expression.
You also build a CTE using the WITH assertion, like this:
To illustrate, see the anatomy of a SQL CTE beneath:
So, Why Would You Use CTE in SQL?
When summarizing information or computing a complicated system, it is always superior to divide your queries into chunks.
It simplifies your code. That can make it much easier to go through and realize. And a CTE does that for you. See the higher than sample? It divides the career into 2: the internal question and the outer query. An internal query with 1 CTE is also the simplest.
So, in quick, CTE can support your code chunks be extra readable.
A further purpose to use CTE is when you have to have a hierarchical listing. A recursive CTE can help you with that. You are going to see an illustration of this in a later on part.
For the factors previously mentioned, you can say that a SQL CTE can be recursive or non-recursive.
What It is Not
Now, let’s demystify things about CTE. For the reason that you might have listened to hearsays about it.
First, a non-recursive CTE does not exchange subqueries, derived tables, or short term tables. If you observe the instance before, the purpose is identical to these query standards. But each and every has its put in your SQL scripts. For example, if you will need the momentary end result set in yet another question, a momentary desk can be a much better selection. Mainly because a momentary desk has a bigger scope in your script. You can reference it wherever inside of a series of instructions. It can also have a world-wide scope.
Then, a non-recursive CTE is not for blazing-fast queries. It is not constantly quicker than the alternate options or vice-versa. Check out performance comparisons in this in-depth report.
How to Use SQL CTE?
Now that you know what it is and what it is not, it is time to know how to use it. Let’s also divide it into 2: what will function and what will not operate.
8 Matters That Will Do the job in SQL CTE
1. Use an Inline or External Column Aliases
SQL CTE supports 2 types of column aliases. Underneath is the to start with that uses the inline kind:
USE WideWorldImporters GO -- Use an inline column alias WITH InvoiceCTE AS ( Pick Month(i.InvoiceDate) AS InvoiceMonth, SUM(il.ExtendedPrice) AS Sum FROM Profits.InvoiceLines il Inner Sign up for income.Invoices i ON i.InvoiceID = il.InvoiceID Exactly where i.InvoiceDate In between '1/1/2013' AND '12/31/2013' Group BY Month(i.InvoiceDate) ) Select InvoiceMonth, Quantity FROM InvoiceCTE Buy BY InvoiceMonth
Employing the AS keyword defines the column alias in a SQL question. In the previously mentioned code, InvoiceMonth and Amount are column aliases.
A further variety of column alias is the external sort. See a revision of the identical code employing it beneath:
USE WideWorldImporters GO -- Use an external column alias WITH InvoiceCTE(InvoiceMonth, Sum) AS ( Pick out Month(i.InvoiceDate), SUM(il.ExtendedPrice) FROM Income.InvoiceLines il Inner Be a part of sales.Invoices i ON i.InvoiceID = il.InvoiceID Where i.InvoiceDate Among '1/1/2013' AND '12/31/2013' Group BY Month(i.InvoiceDate) ) Find InvoiceMonth, Volume FROM InvoiceCTE Order BY InvoiceMonth
This time, the column aliases are defined immediately after the CTE title. The two queries will have the pursuing consequence set:
2. Select, INSERT, UPDATE, DELETE, or MERGE Follows a SQL CTE
The previous SQL CTE examples you observed stick to a Pick out statement. In addition to a Decide on assertion, you can also use INSERT, UPDATE, DELETE, or MERGE.
Here’s an illustration employing INSERT:
-- Get the most up-to-date product value and include a 2% improve in selling price in products expense record USE AdventureWorks GO DECLARE @productID INT = 703 WITH LatestProductCost AS ( Decide on Top 1 pch.ProductID, pch.StartDate, pch.EndDate, pch.StandardCost FROM Generation.ProductCostHistory pch In which pch.ProductID = @productID Purchase BY pch.StartDate DESC ) INSERT INTO Generation.ProductCostHistory (ProductID, StartDate, EndDate, StandardCost, ModifiedDate) Choose @productID ,DATEADD(d,1,lpc.EndDate) ,DATEADD(d,366,lpc.EndDate) ,(lpc.StandardCost * .02) + lpc.StandardCost ,GETDATE() FROM LatestProductCost lpc
3. Various CTEs in a Question
You can also outline more than 1 CTE in a query. Here’s an example:
-- Having the ahead of and just after merchandise typical charge change USE AdventureWorks GO DECLARE @productID INT = 711 WITH LatestProductCost AS ( Decide on Major 1 pch.ProductID, pch.StartDate, pch.StandardCost FROM Manufacturing.ProductCostHistory pch Where by pch.ProductID = @productID Buy BY pch.StartDate DESC ), PreviousProductCost AS ( Find Leading 1 pch.ProductID, pch.StartDate, pch.StandardCost FROM Production.ProductCostHistory pch Inner Be part of LatestProductCost lpc ON pch.ProductID = lpc.ProductID Exactly where pch.ProductID = @productID AND pch.StartDate < lpc.StartDate ORDER BY pch.StartDate DESC ) SELECT lpc.ProductID ,p.Name AS Product ,lpc.StandardCost AS LatestCost ,lpc.StartDate ,ppc.StandardCost AS PreviousCost FROM LatestProductCost lpc INNER JOIN PreviousProductCost ppc ON lpc.ProductID = ppc.ProductID INNER JOIN Production.Product p ON lpc.ProductID = p.ProductID WHERE lpc.ProductID = @productID
Multiple CTEs are separated by commas. You can see 2 CTEs in the above example. And it will have the following result set:
4. Reference a SQL CTE Several Times
But there’s more to the previous example. The PreviousProductCost CTE references the LatestProductCost CTE. Then, the outer query referred to LatestProductCost CTE again. You can reference a CTE like this several times as needed.
5. Use SQL CTE in a Stored Procedure and Pass Arguments to It
You can also use SQL CTE in a stored procedure. Then, passing stored procedure parameter values to it is possible.
Here’s an example:
USE AdventureWorks GO IF OBJECT_ID('dbo.uspInsertNewProductCost') IS NOT NULL SET NOEXEC ON GO CREATE PROCEDURE dbo.uspInsertNewProductCost ( @productID INT, @increase DECIMAL(3,2) ) AS SET NOCOUNT ON WITH LatestProductCost AS ( SELECT TOP 1 pch.ProductID, pch.StartDate, pch.EndDate, pch.StandardCost FROM Production.ProductCostHistory pch WHERE pch.ProductID = @productID ORDER BY pch.StartDate DESC ) INSERT INTO Production.ProductCostHistory (ProductID, StartDate, EndDate, StandardCost, ModifiedDate) SELECT @productID ,DATEADD(d,1,lpc.EndDate) ,DATEADD(d,366,lpc.EndDate) ,(lpc.StandardCost * @increase) + lpc.StandardCost ,GETDATE() FROM LatestProductCost lpc GO
In the above, a CTE is used to receive 2 stored procedure parameters , @productID, and @increase. This will add a new row in the ProductCostHistory table.
6. Use SQL CTE in a View
You can also use SQL CTE in a view. Here’s an example:
USE WideWorldImporters GO CREATE VIEW dbo.vwYearlyInvoiceTotalsPerProduct AS WITH InvoiceCTE(InvoiceYear, InvoiceMonth, StockItemID, Amount) AS ( SELECT YEAR(i.InvoiceDate), MONTH(i.InvoiceDate), il.StockItemID, SUM(il.ExtendedPrice) FROM Sales.InvoiceLines il INNER JOIN Sales.Invoices i ON i.InvoiceID = il.InvoiceID GROUP BY YEAR(i.InvoiceDate), MONTH(i.InvoiceDate), il.StockItemID ) SELECT i.InvoiceYear, i.InvoiceMonth, si.StockItemName, i.Amount FROM InvoiceCTE i INNER JOIN Warehouse.StockItems si ON i.StockItemID = si.StockItemID GO
7. Use SQL CTE in a Cursor
You can also use SQL CTE with a cursor. Then, loop through the results. Here’s an example:
USE WideWorldImporters GO DECLARE @invoiceMonth TINYINT DECLARE @amount MONEY DECLARE invoice_cursor CURSOR FOR WITH InvoiceCTE AS ( SELECT MONTH(i.InvoiceDate) AS InvoiceMonth, SUM(il.ExtendedPrice) AS Amount FROM Sales.InvoiceLines il INNER JOIN sales.Invoices i ON i.InvoiceID = il.InvoiceID WHERE i.InvoiceDate BETWEEN '1/1/2013' AND '12/31/2013' GROUP BY MONTH(i.InvoiceDate) ) SELECT InvoiceMonth, Amount FROM InvoiceCTE ORDER BY InvoiceMonth OPEN invoice_cursor FETCH NEXT FROM invoice_cursor INTO @invoiceMonth, @amount WHILE @@fetch_status = 0 BEGIN PRINT 'Invoice Month: ' + CAST(@invoiceMonth AS VARCHAR) PRINT 'Amount: ' + CAST(@amount AS VARCHAR) FETCH NEXT FROM invoice_cursor INTO @invoiceMonth, @amount END CLOSE invoice_cursor DEALLOCATE invoice_cursor
8. Use a Temporary Table in a Recursive CTE
A recursive CTE has an anchor member and a recursive member. You can use this to query hierarchical data. A family tree, for example, is hierarchical by nature.
It doesn’t matter if the CTE uses a usual table or a temporary table. See an example using a temporary table below:
-- British Royal family CREATE TABLE dbo.RoyalFamily ( ID INT NOT NULL, Name VARCHAR(60) NOT NULL, Father INT, Mother INT CONSTRAINT PK_RoyalFamily_ID PRIMARY KEY (ID) ) GO INSERT INTO dbo.RoyalFamily (ID, Name, Father, Mother) VALUES (1,'Philip',NULL,NULL), (2,'Queen Elizabeth II',NULL,NULL), (3,'Charles',1,2), (4,'Anne',2,1), (5,'Andrew',2,1), (6,'Edward',2,1), (7,'Diana',NULL,NULL), (8,'Camilla',NULL,NULL), (9,'Mark Philips',NULL,NULL), (10,'Timothy Laurence',NULL,NULL), (11,'Sarah',NULL,NULL), (12,'Sophie',NULL,NULL), (13,'William',3,7), (14,'Harry',3,7), (15,'Peter Philips',9,4), (16,'Zara Tindall',9,4), (17,'Beatrice',5,11), (18,'Eugenie',5,11), (19,'Louise',6,12), (20,'James',6,12), (21,'Catherine',NULL,NULL), (22,'Meghan',NULL,NULL), (23,'Autumn Philips',NULL,NULL), (24,'Mike Tindall',NULL,NULL), (25,'Jack Brooksbank',NULL,NULL), (26,'George',13,21), (27,'Charlotte',13,21), (28,'Louis',13,21), (29,'Archie Harrison Mountbatten-Windsor',14,22), (30,'Savannah',15,23), (31,'Isla',15,23), (32,'Mia Grace',24,16), (33,'Lena',24,16) DECLARE @id INT = 26 -- Prince George WITH Ancestor(ID) AS ( -- First anchor member returns the royal family member in question SELECT ID FROM dbo.RoyalFamily WHERE ID = @id UNION -- Second anchor member returns the father SELECT Father FROM dbo.RoyalFamily WHERE ID = @id UNION -- Third anchor member returns the mother SELECT Mother FROM dbo.RoyalFamily WHERE ID = @id UNION ALL -- First recursive member returns male ancestors of the previous generation SELECT rf.Father FROM RoyalFamily rf INNER JOIN Ancestor a ON rf.ID = a.ID UNION ALL -- Second recursive member returns female ancestors of the previous generation SELECT rf.Mother FROM RoyalFamily rf INNER JOIN Ancestor a ON rf.ID = a.ID ) SELECT DISTINCT rf.ID, rf.Name, rf.Mother, rf.Father FROM RoyalFamily rf INNER JOIN Ancestor a ON rf.ID = a.ID ORDER BY rf.ID DESC
Below is the output of the query above:
Here’s what happened in this query:
Mother and Father columns are IDs of a royal family member.
Prince George (ID = 26) showed on top. It is the first anchor member of the CTE.
His mother is Catherine (ID = 21), and his father is Prince William (ID = 13). These are also the second and third anchor members.
Then, Prince William’s parents are Princess Diana (ID = 7) and Prince Charles (ID = 3). This and the next bullet point are part of the recursive members of the CTE.
Meanwhile, Prince Charles’ parents are Queen Elizabeth (ID = 2) and Prince Philip (ID = 1).
But take note: an incorrectly written recursive CTE may cause an infinite loop. So, to protect you from that, you can add MAXRECURSION n, where n is the number of loops. Add this at the end of the query after the WHERE clause or the last JOIN.
Great! We used SQL CTE to get the hierarchical list of the British royal family.
4 Things That Won’t Work in SQL CTE
There are rules in SQL Server CTE. So, in this section, we’ll discuss the things that won’t work.
1. No Semicolon Before the WITH Clause
At times you will encounter a syntax error if the WITH clause of a CTE is not preceded by a semicolon. This happens when you run a batch of SQL statements. Here’s an example:
This happens because the WITH clause is used for other purposes like a table hint. Adding a semicolon in the preceding statement will fix the problem.
Depending on your editor, squiggly lines also appear under a CTE name like the one you see above. The error message is clear enough on how to fix it.
2. SQL CTE Column Conflicts
You will have problems if
the number of columns in both anchor and recursive members is different.
columns are not named
there are duplicate names
data types of columns are not the same for both anchor and recursive members.
Here’s an example of CTE syntax errors because of unnamed columns.
3. Reusing a SQL CTE Name Outside of the Outer Query
A SQL CTE is not reusable. I’ve mentioned this earlier, but I like to stress the point further. Based on the earlier sample, you can’t reference the InvoiceCTE in the next SQL command. It will trigger an error.
If you need the temporary result set in another query in a batch, there are a few options. One is a temporary table. Or use multiple non-recursive CTE if this is faster.
4. Nesting a SQL CTE
Nested CTEs will simply not work. Here’s an example that will cause several syntax errors:
WITH InvoiceCTE(InvoiceMonth, StockItemID, Amount) AS ( SELECT MONTH(i.InvoiceDate), il.StockItemID, SUM(il.ExtendedPrice) FROM Sales.InvoiceLines il INNER JOIN sales.Invoices i ON i.InvoiceID = il.InvoiceID WHERE i.InvoiceDate BETWEEN '1/1/2013' AND '12/31/2013' GROUP BY MONTH(i.InvoiceDate), il.StockItemID ), AverageAmountPerMonth AS ( SELECT InvoiceMonth, AVG(Amount) AS Average FROM (WITH InvoiceAmountPerMonth AS ( SELECT i.InvoiceMonth, si.StockItemName, i.Amount FROM InvoiceCTE i INNER JOIN Warehouse.StockItems si ON i.StockItemID = si.StockItemID ) ) ) SELECT * FROM AverageAmountPerMonth
Other Things Not Allowed in a SQL CTE
When these keywords are found in a recursive member:
Use of scalar aggregation
Use of SELECT INTO, OPTION clause with query hints, and FOR BROWSE.
ORDER BY without a TOP clause
Pro Coding Tips for SQL CTE
Typing all the codes above without IntelliSense can be difficult and error-prone. So, if you could minimize these and speed up coding by up to 4 times faster, why not take it? That’s why there’s SQL Complete by Devart. It’s a SQL Server Management Studio smart add-in. It delivers SQL IntelliSense, autocompletion, refactoring, formatting, and debugging.
Let’s see how it works with SQL CTE.
First, in the SSMS Query window, type cte and press Tab. This code snippet will give you a CTE template you can fill up. See below.
Then, rename the CTE.
Then, edit the CTE until the code is like this:
WITH InvoiceCTE(InvoiceMonth, Amount) AS ( SELECT MONTH(i.InvoiceDate), SUM(il.ExtendedPrice) FROM Sales.InvoiceLines il INNER JOIN sales.Invoices i ON i.InvoiceID = il.InvoiceID WHERE i.InvoiceDate BETWEEN '1/1/2013' AND '12/31/2013' GROUP BY MONTH(i.InvoiceDate) ) SELECT InvoiceMonth, Amount FROM InvoiceCTE ORDER BY InvoiceMonth
Make use of the table suggestions and use snippets like ij for INNER JOIN. SQL Complete will suggest the likely joined table and columns. So, make use of those.
See for yourself. Start with joining the 2 tables.
Then, add columns using the column picker.
SQL CTE is another way of expressing queries with a temporary result set. This standard improves the readability of code.
So, why not try SQL CTE today with SQL Complete?