SQL SERVER – Convert Subquery to CTE – SQL in Sixty Seconds #001 – Video

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.

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 (http://blog.sqlauthority.com)

About these ads

19 thoughts on “SQL SERVER – Convert Subquery to CTE – SQL in Sixty Seconds #001 – Video

  1. 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.

  2. 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 :-)

  3. 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

  4. 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

  5. @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.

  6. Pingback: SQL SERVER – T-SQL Errors and Reactions – SQL in Sixty Seconds #002 – Video « SQL Server Journey with SQL Authority

  7. Pingback: SQL SERVER – T-SQL Constructs – Declaration and Initialization – SQL in Sixty Seconds #003 – Video « SQL Server Journey with SQL Authority

  8. Pingback: SQL SERVER – Weekly Series – Memory Lane – #015 « SQL Server Journey with SQL Authority

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s