SQL Server is an ocean of information. I believe if one starts learning today, after 60 years he/she may still be learning the subject (there are always a few exceptions)! Recently, I published the SQL Server Questions and Answers video tutorial, and since the course came out, I have been receiving lots of request to share SQL Tips which are small and easy to digest.
While writing the SQL books with my co-authors Vinod Kumar and Rick Morelan, we often came across very interesting and useful tips which we believe would be helpful to readers. Sometimes the tips are so small that we could not find an appropriate place for them in the book. We call them Sixty Seconds Tips. The tips are so small that sometimes sixty seconds are too long for them. Keeping the notion of quick and easy tips – we have decided to come up with new series called “SQL in Sixty Seconds.” Every week we will post one small but useful video tip. We believe that Sixty Seconds is metaphor for quick and easy tip.
I have seen many developers staying away from CTE (Common Table Expression), their excuse is that they do not know how to convert subqueries to CTE or they think it is very complicated to do so. In the very first episode of SQL in Sixty Seconds we will see how easily one can convert Subqueries to CTE and CTE to Subqueries.
[youtube=http://www.youtube.com/watch?v=Z7VuYBXX2dg]
More on CTE:
Simple Example of Recursive CTE
Multiple CTE in One SELECT Statement Query
Common Table Expression (CTE) and Few Observation
Delete Duplicate Rows
Simple Example of Recursive CTE – Part 2 – MAXRECURSION – Prevent CTE Infinite Loop
T-SQL Paging Query Technique Comparison (OVER and ROW_NUMBER()) – CTE vs. Derived Table
I encourage you to submit your ideas for SQL in Sixty Seconds. We will try to accommodate as many as we can.
Reference: Pinal Dave (https://blog.sqlauthority.com)
16 Comments. Leave new
It says, Video is private!!
Pinal,
what is the benefits of using a CTE vs a temp table?
super “LIKE”. I am ready to watch this kind of 60 videos a day :).
Hi Pinal sir,
I will definitely go for this.
Regard$
Chirag Satasiya
nice!!!!
Could you provide a script of the video?
Or better, loose the video and just write about the topic?
I’m at work and cannot view videos.
Dear Dave. The idea is great & I’m looking forward to every topic in this series.
But not to use video – in this case – seems to be more effiecient for your readers and less laborious for you :-)
Hi Pinal,
This will help for us….! we are very much interested to participate in this..!!!
We can share below topics with developers and DBAs
1. simple query tuning tricks
2. choosing indexes wisely
3. useful and less used DBCC’s, XP_’s and SP_’s
I think We can make most in 60 seconds
Hi,
this is great!
Although i’m using CTE for quite a while, this is the most simple way to use it and to show how it works.
I really enjoyed that video and hope to see more of those :-)
o
Peter
Nice demo of CTE, thank you
Hi pinal sir,
Nice demo and easy explanation.
Regard$
Chirag Satasiya
@Nghia Tran
Where I work, I don’t get access to the DB for stored code. In our case, everything has to be done IN sql alone and my use of even T-SQL is limited — no procedural code.
Luckily, pretty everything I’ve tried can be done w/ the right SQL. CTEs have let me whip up some awesome SQL — the main benefit being sql clarity, ability to change and maintain portions more easily, and the ability for clear incremental development and testing.
Nice Post Dave. :)
Quickest way to write CTE’s.