December 24, 2015 at 12:52 am
All
Id just like peoples views on the use of CTEs.
I work contracts and move from job to job, 1 or 2 jobs a year. I've worked with a lot of developers.
about a year ago I came across a developer who littered his t sql with ctes, cte after cte with his query at then end.
some of his CTEs were really unneccessary I thought, a whole block of code just to rename or alias a column name on a table.
then since then i've had 2 jobs where i've come across more and more people who code like this and now in my current job there are only 2 other developers who both do it.
I personally find the code ugly and difficult to maintain.
it seems to be common though so maybe i'm missing a trick?
thanks
December 24, 2015 at 2:01 am
It's partly about preference, and partly about using the right tool for the right job. If a CTE is unnecessary, don't use it. If you prefer subqueries, don't use CTEs. There are some occasions where a CTE will do what a subquery can't, such as when you need to reference it more than once.
John
December 24, 2015 at 2:21 am
thanks for the reply
ill be honest I often didn't see their use as even necessary as a sub query in what I saw
but even if they were there only to replace what would be sub queries what is your preference?
and yes, I use CTEs only at times when a query/sub query is required more than once in a query or for recursive reasons
December 24, 2015 at 2:41 am
I prefer CTEs to subqueries because you can get all the definitions out of the way at the front, and then the main query looks more neat and compact.
John
December 24, 2015 at 2:48 am
They're very useful when writing complex queries. Write a basic query, turn it into a CTE, write the next query off that, etc. Easier debugging too, as at any point the following queries can be commented out replaced with SELECT * FROM LastCTE
Most of my more complex queries lately have had at least one and up to four CTEs.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 24, 2015 at 3:26 am
John Mitchell-245523 (12/24/2015)
I prefer CTEs to subqueries because you can get all the definitions out of the way at the front, and then the main query looks more neat and compact.John
urgh
I find myself shifting up and down through the code
but yes if complex enough I can see the benefit
December 24, 2015 at 3:39 am
Readability.
Sometimes a query may perform better if it uses sub-queries instead of CTEs, but if it isn't a major issue I'll use a CTE because the code is easier to read and therefore easier to maintain. And as Gail has said, it is also to build a complex query up in stages with CTEs.
Somebody using CTEs everywhere, regardless of suitability, hasn't yet got bored of the shiny new toy that they've found.
December 24, 2015 at 3:47 am
thanks all
CTEs seem to be a lot more popular than id expected them to be
I'm not sure I agree with the readability of CTEs, I prefer the code in the place it is doing its job
again I know complexity has to be considered, as in a particularly complex query might be better broken down into CTEs
December 24, 2015 at 6:50 am
Unless you're using recursion, a CTE can be rewritten as a derived table... Considering that derived tables (and CTEs) are processed before the outer query, CTEs can allow the code to laid out in an order that more closely resembles the actual processing order.
That said, if there isn't a logical reason to use a CTE, don't...
December 24, 2015 at 7:36 am
BrainDonor (12/24/2015)
Sometimes a query may perform better if it uses sub-queries instead of CTEs
I hope you have some references to back this up. To the best of my knowledge, non-recursive CTEs and sub-queries are parsed to exactly the same structure, so there should be no performance difference between the two.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 4, 2016 at 10:35 am
One of my more common uses of CTEs is to pre-assign row-ordered values like ROW_NUMBER(), NTILE(), etc., which I can then aggregate on in a final select.
Any kind of grouping on an obnoxiously long CASE statement (or other logic) is a candidate in my book for a CTE, because you don't have to duplicate the CASE logic in the SELECT and GROUP BY statements: put the CASE in the CTE, then GROUP BY the derived column in the final SELECT. (I would love it if the query engine could recognize the alias in the SELECT and allow using the alias in the GROUP BY, but that's probably not gonna happen in MSSQL; this link suggests it is possible in PostgreSQL).
YMMV
Rich
January 4, 2016 at 10:51 am
Rich Mechaber (1/4/2016)
One of my more common uses of CTEs is to pre-assign row-ordered values like ROW_NUMBER(), NTILE(), etc., which I can then aggregate on in a final select.Any kind of grouping on an obnoxiously long CASE statement (or other logic) is a candidate in my book for a CTE, because you don't have to duplicate the CASE logic in the SELECT and GROUP BY statements: put the CASE in the CTE, then GROUP BY the derived column in the final SELECT. (I would love it if the query engine could recognize the alias in the SELECT and allow using the alias in the GROUP BY, but that's probably not gonna happen in MSSQL; this link suggests it is possible in PostgreSQL).
YMMV
Rich
You could do that the same in a sub query couldn't you?
January 4, 2016 at 12:05 pm
Except for recursive CTEs, CTEs and Subqueries are basically the same thing. I prefer CTEs for a couple things:
First, for nested queries. CTEs make nested queries much more readable and easier to troubleshoot.
The other is for modifying data. You can't do this with a subquery:
-- Sample data
DECLARE @a TABLE(c1 int);
INSERT @a SELECT TOP 100 CHECKSUM(newid())%10 FROM sys.all_columns;
-- results
SELECT * FROM @a;
-- I want to set C1 to it's Absolute (ABS) value:
WITH a AS
(
SELECT c1
FROM @a
WHERE c1 < 0
)
UPDATE a
SET c1 = ABS(c1)
-- new values
SELECT * FROM @a;
-- Itzik Ben-Gan 2001
January 4, 2016 at 12:21 pm
Alan.B (1/4/2016)
Except for recursive CTEs, CTEs and Subqueries are basically the same thing. I prefer CTEs for a couple things:First, for nested queries. CTEs make nested queries much more readable and easier to troubleshoot.
The other is for modifying data. You can't do this with a subquery:
-- Sample data
DECLARE @a TABLE(c1 int);
INSERT @a SELECT TOP 100 CHECKSUM(newid())%10 FROM sys.all_columns;
-- results
SELECT * FROM @a;
-- I want to set C1 to it's Absolute (ABS) value:
WITH a AS
(
SELECT c1
FROM @a
WHERE c1 < 0
)
UPDATE a
SET c1 = ABS(c1)
-- new values
SELECT * FROM @a;
You don't need a sub query or a cte to modify data
I personally prefer a sub query for readability but different styles is good for variety eh 🙂
January 4, 2016 at 12:39 pm
erics44 (1/4/2016)
Alan.B (1/4/2016)
Except for recursive CTEs, CTEs and Subqueries are basically the same thing. I prefer CTEs for a couple things:First, for nested queries. CTEs make nested queries much more readable and easier to troubleshoot.
The other is for modifying data. You can't do this with a subquery:
-- Sample data
DECLARE @a TABLE(c1 int);
INSERT @a SELECT TOP 100 CHECKSUM(newid())%10 FROM sys.all_columns;
-- results
SELECT * FROM @a;
-- I want to set C1 to it's Absolute (ABS) value:
WITH a AS
(
SELECT c1
FROM @a
WHERE c1 < 0
)
UPDATE a
SET c1 = ABS(c1)
-- new values
SELECT * FROM @a;
You don't need a sub query or a cte to modify data
I personally prefer a sub query for readability but different styles is good for variety eh 🙂
I'm not saying that you need a CTE to modify data; I was just demonstrating one difference between subqueries and CTEs (you can't do what I did with a subquery). The example I posted is
helpful for development and troubleshooting. You can highlight just the SELECT part of that query to see what would be updated. This is helpful when the data modification query is based on complex logic.
-- Itzik Ben-Gan 2001
Viewing 15 posts - 1 through 15 (of 161 total)
You must be logged in to reply to this topic. Login to reply