January 4, 2016 at 1:31 pm
Alan.B (1/4/2016)
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.
Actually, you can use a derived table to accomplish what you did using a CTE. The following worked just fine for me.
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:
UPDATE a SET
c1 = ABS(c1)
from
(
SELECT c1
FROM @a
WHERE c1 < 0
) a;
-- new values
SELECT * FROM @a;
Of course for such a simple update, all you really need is this.
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:
UPDATE @a
SET c1 = ABS(c1)
where c1 < 0;
-- new values
SELECT * FROM @a;
January 4, 2016 at 1:42 pm
Yeah I agree, you don't need a cte
And I can't see why you would use a cte in that situation, just adds a level of complexity
January 4, 2016 at 2:08 pm
The interesting part, all three updates resolve to the same execution plan. Three different ways to tell SQL Server the WHAT you want but it all comes down to SQL Server doing the same way.
January 4, 2016 at 3:15 pm
erics44 (1/4/2016)
Yeah I agree, you don't need a cteAnd I can't see why you would use a cte in that situation, just adds a level of complexity
Nobody said that you need a CTE for that and, in production, I never do that. But in a development environment, where you sometimes need to troubleshoot why your update/insert/delete is jacking up the data, I like having my data wrapped in a CTE so I run the SELECT statement and see if my update/insert/delete will be correct without actually doing an insert/update/delete. My example was simple but in the real world the queries are more complex. What I presented was a trick I learned from Itzik Ben-Gan in Microsoft SQL Server 2012 T-SQL Fundamentals which has served me well. I'm not the only one doing this. Remember, the OP wanted to understand why some people use CTEs.
... and, as Lynn mentioned, there is no additional cost in doing so.
-- Itzik Ben-Gan 2001
January 4, 2016 at 5:07 pm
It's just a matter of personal preference...
For simple queries, with a single, simple derived table, I tend not to care one way or another. They're both easy to read.
When you start getting into the situation where derived tables are nested inside each other, several levels deep... I, personally, find it easier to read the CTE formatted code... Simply because each section of the CTE is a single, concise, query... Rather than a query crammed in the middle of another query, crammed in the middle of another query... and so on.
As a side note... There is a difference between sub-queries and derived tables.
Derived tables and non-recursive CTEs are interchangeable... So so with sub-queries.
Hint... Sub-queries primarily exist in either the SELECT list or the WHERE clause and you can't join to them in the FROM clause without using an APPLY operator.
January 5, 2016 at 2:10 am
Jason A. Long (1/4/2016)
It's just a matter of personal preference...For simple queries, with a single, simple derived table, I tend not to care one way or another. They're both easy to read.
When you start getting into the situation where derived tables are nested inside each other, several levels deep... I, personally, find it easier to read the CTE formatted code... Simply because each section of the CTE is a single, concise, query... Rather than a query crammed in the middle of another query, crammed in the middle of another query... and so on.
As a side note... There is a difference between sub-queries and derived tables.
Derived tables and non-recursive CTEs are interchangeable... So so with sub-queries.
Hint... Sub-queries primarily exist in either the SELECT list or the WHERE clause and you can't join to them in the FROM clause without using an APPLY operator.
you're absolutely right it is a matter of personal preference, so this isn't a who's right or whose wrong thread, just a conversation about personal preference 🙂
but here my personal preference differs from yours
if with have code something like
CTEa, CTEb, CTEc, CTEd
select * from CTEa join CTEb join CTEc Join CTEd
then you are right your CTEs are in one concise block but to read what your main query is doing you need to keep shifting up and down the code which makes it less readable than having the code in the place where it is used
and if you need several levels of nested sub queries then you would still need them in CTEs wouldn't you? and if you put all of your several of nested queries in separate CTEs then you would have loads of CTEs which would make your code unreadable
which is the situation I came across which prompted me to create this thread
just to note, the queries I came across was badly written for many reasons not just the many CTEs and I don't expect anyone to write queries in that way
January 5, 2016 at 2:15 am
ill be honest tho
since I started this thread I have started using CTEs a more and not just for recursive requirements or when I need the code more than once in a single query 🙂
January 5, 2016 at 6:29 am
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;
Alan, I'm not sure I follow your point about a subquery. Removing the CTE in this case doesn't necessitate a subquery; a direct UPDATE works (I commented out the CTE and changed the UPDATE target to the the table variable):
-- 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)
WHERE c1 < 0;
-- new values
SELECT * FROM @a;
Rich
January 5, 2016 at 9:03 am
Rich Mechaber (1/5/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;
Alan, I'm not sure I follow your point about a subquery. Removing the CTE in this case doesn't necessitate a subquery; a direct UPDATE works (I commented out the CTE and changed the UPDATE target to the the table variable):
-- Sample dataDECLARE @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)
WHERE c1 < 0;
-- new values
SELECT * FROM @a;
Rich
A direct update absolutely works, Lynn even coded your example earlier. Many people including myself where updating SQL data long before CTEs where introduced. Nobody is saying that you need a derived table (subquery or CTE) to do data modification.
What I was demonstrating was in response to the OP's original question and is why someone would use a CTE instead of doing a direct update... especially when the data modification logic is extremely complex (I did a simple example so that more people would understand my point). See my previous comment.
-- Itzik Ben-Gan 2001
January 5, 2016 at 11:23 am
I prefer CTEs when one recordset depends on the prior. This could be potentially quite confusing with multiple subqueries
With Cte0 as
(
SELECT /* some record set */
),
Cte1 as
(
SELECT /* ..*/
FROM Cte0
),
Cte2 as
(
SELECT /*...*/
FROM Cte1
)
SELECT /* */
FROM Cte2
When it comes to something like
With Cte0 as
(
SELECT /* some record set */
FROM /* some source */
),
Cte1 as
(
SELECT /* ..*/
FROM /* another source */
)
SELECT /* */
FROM Cte0 JOIN Cte1 ON /* join columns */
I then prefer subqueries or temp tables if the sets are large.
Plus CTE allow you to easily perform CRUD operations possibly on the base set.
WITH MyCTE as (
SELECTrow_number() over (partition by /* columns */ order by /* columns*/ ) as ItemNum,
*
From/* table */
)
Delete From MyCte Where ItemNum>1 /* to remove certain column duplicates */
These are my preferences. I've seen others that use them for almost everything as you say. As long as it is commented well, it should help any confusion if you are not used to seeing code this way.
----------------------------------------------------
January 5, 2016 at 4:40 pm
GilaMonster (12/24/2015)
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
Yeah, right!
Especially when you debug a procedure with several consecutive INSERT.
CTE places an INSERT part in the middle of the query.
Every time you need to see what's being inserted you comment INSERT part, highlight the query, run it, and do not forget to uncomment when done.
Repeat all the steps when you need to re-test on another data set.
Certainly it's way more convenient comparing to subqueries/derived tables when you simply highlight the whole query except INSERT part and run it! :hehe:
_____________
Code for TallyGenerator
January 5, 2016 at 4:58 pm
Alan.B (1/5/2016)[hrWhat I was demonstrating was in response to the OP's original question and is why someone would use a CTE instead of doing a direct update... especially when the data modification logic is extremely complex (I did a simple example so that more people would understand my point). See my previous comment.
When data modification logic is extremely complex I would not come anywhere near a CTE.
It's like updating a view - it might work or might not work, but some times you wished it did not work.
To test extremely complex updates I use this simple trick:
UPDATE a
SET c1 = ComplexCalculation(c1),
c2 = AnotherComplexalculation (c3,c4)
/* SELECT a.c1 C1OldValue, ComplexCalculation(c1) C1NewValue,
a.c2 C2OldValue, AnotherComplexalculation (c3,c4) C2NewValue
-- */
FROM Table1 a
Join ... Whatever complex join you need to do ...
WHERE Very complex filtering goes here
Highlight the query starting from SELECT and run it - see what do you update, with what, and how many rows are gonna be updated.
Best part - you see what goes to the specified table, not to the view implemented on-fly by the CTE.
_____________
Code for TallyGenerator
January 5, 2016 at 5:30 pm
Sergiy (1/5/2016)
When data modification logic is extremely complex I would not come anywhere near a CTE.It's like updating a view - it might work or might not work, but some times you wished it did not work.
To test extremely complex updates I use this simple trick:
UPDATE a
SET c1 = ComplexCalculation(c1),
c2 = AnotherComplexalculation (c3,c4)
/* SELECT a.c1 C1OldValue, ComplexCalculation(c1) C1NewValue,
a.c2 C2OldValue, AnotherComplexalculation (c3,c4) C2NewValue
-- */
FROM Table1 a
Join ... Whatever complex join you need to do ...
WHERE Very complex filtering goes here
Highlight the query starting from SELECT and run it - see what do you update, with what, and how many rows are gonna be updated.
Best part - you see what goes to the specified table, not to the view implemented on-fly by the CTE.
Its those "whatever complex join" you have that we want better visibility into sometimes, where it can encompass many many tables. If you have a condition that , among plenty of others, is only supposed to get last names >'m' and that is all your table has, then your results will appear okay (even if you missed this in the complex join condition). The moment a person with a last name of Higgins gets added to the table and shows up in the results, you'll have to debug that complex join .
----------------------------------------------------
January 5, 2016 at 7:59 pm
Martin,
I honestly cannot see how a CTE can provide any better visibility than the verification SELECT I use.
Not to forget - updating CTE is not the same as updating table.
The more complex CTE you have - the less certain is the outcome of the UPDATE.
And if you failed to create a proper test case for your code - no CTE on Earth can help you.
_____________
Code for TallyGenerator
January 5, 2016 at 8:28 pm
The fact is - CTE is a foreign construction in SQL.
It was copied to T-SQL from front end languages to create more comfortable environment for those ".Net developers with SQL skills". So they do not need to learn how to do in SQL what they use to do using property grouping for objects/lists.
Before you start arguing that - try to explain why CTE construction must be prefixed with ";" - there is no such requirement for any other language construction.
Probably because the keyword "WITH" has a totally different meaning in SQL, so there must be a way to distinguish a "WITH" as a query option from a "WITH" as a CTE definition.
There are no tasks which could be done with CTE but not with pre-CTE language features.
Funny, that in Oracle it's named as "subquery factoring" - pretty self-confessing.
Some might see a benefit in "doing everything in one query", but I don't see it as a benefit at all. I still appreciate structured programming, you know. 😛
_____________
Code for TallyGenerator
Viewing 15 posts - 16 through 30 (of 161 total)
You must be logged in to reply to this topic. Login to reply