Using a Common Table Expression Instead of a Derived Table

Here is a short video that shows how to convert those ugly derived table (SubQuery) queries into a common table expression to help clean up your TSQL code. This applies to Common Table Expressions on Microsoft SQL Server, and the recording comes from my Free SQL Query Training course.

 

 

Other CTE Related Posts:

Next Week’s Free SQL Training: MERGE Statement and Recursive Queries

Next week, on Thursday Feb 26th Aaron Buma and I, as part of the Free SQL Training provided by Emergency Reporting, will be presenting on the use of the MERGE Statement and how to do Recursive Queries. This is intended to help prepare TSQL Developers for the 70-461 certification exam.

Here is the basic abstract for the presentation:

This will be a free training provided by Steve Stedman and Aaron Buma at Emergency Reporting to prepare for the Microsoft 70-461 SQL Queries exam. This week we will be two topics, first on the MERGE Statement, then Recursive Queries.  This is provided free of charge to give back to the SQL community.

You can watch via Google On Air Hangouts at this link: https://plus.google.com/u/0/events/cma01e76ll4kfsaopdj6jue1gno

A week ago we touched slightly on recursive queries in the CTE presentation, in this session I will go into much more detail on the recursive queries.

 

For more information:

 

What is a Common Table Expression

I just a added a video to YouTube titled “What is a Common Table Expression”.  This video covers the extreme basics of what a CTE is on Microsoft SQL Server, and how to write that CTE in TSQL. Here is the video:


It’s a very short video with the basics of what a CTE is.  Take a look.

 

Other CTE Related Posts:

Introduction to CTEs Slides and Sample Queries

Today I am at SQL Saturday Portland Oregon, and at 9:00am I am presenting the Introduction To Common Table Expressions session:

Introduction

Here is the abstract:

Have you ever wanted to create a recursive query, but didn’t see how to do it. With the Common Table Expressions session you will learn everything needed to start using CTE’s for recursive queries, as temporary views, and to use the result set multiple times in the same query. Learn how simplify query syntax using CTE’s. One of the most overlooked features of SQL Server is the CTE which not only simplifies the query, but gives you the ability to do things that would otherwise be impossible (or at least very challenging) with SQL Server. The class is designed for people who haven’t used CTE’s before, or for those who want to learn the basics of CTEs including data paging. This session pairs well with the Advanced Common Table Expressions session.

This session will include the following topics:

  • Introduction to Memory Tables and CTEs
  • Simple CTE
  • CTE Instead of a Derived Table
  • Multiple CTE in a Query
  • Data Paging
  • CTEs in Stored Procedures, Functions and Views
  • Introduction To Recursive CTEs

At 10:45 I will be giving the Advanced Common Table Expressions Session.

Download the presentation here:  Introduction to CTEs.zip

Related Posts:

SQL Saturday #265 in Portland Oregon

I will be attending SQL Saturday #265 in Portland Oregon on November 16th 2013.

This will be my second time attending SQL Saturday in Portland, and it is looking to be a great event. There are some great speakers on the line up that I am looking forward to learning from.

I will be presenting 2 sessions on Common Table Expressions.

Common Table Expressions – Introduction

Have you ever wanted to create a recursive query, but didn’t see how to do it. With the Common Table Expressions session you will learn everything needed to start using CTE’s for recursive queries, as temporary views, and to use the result set multiple times in the same query. Learn how simplify query syntax using CTE’s. One of the most overlooked features of SQL Server is the CTE which not only simplifies the query, but gives you the ability to do things that would otherwise be impossible (or at least very challenging) with SQL Server. The class is designed for people who haven’t used CTE’s before, or for those who want to learn the basics of CTEs including data paging. This session pairs well with the Advanced Common Table Expressions

Common Table Expressions – Advanced

You have been introduced to Common Table Expression, you understand the ;WITH syntax, but you want to know more. Learn how to recursive queries work with CTEs and how to display hierarchical data. Did you know that you can INSERT, UPDATE and DELETE data from CTEs. Some of the common use cases for CTEs will be covered including finding holes in patterns, finding and removing duplicate data, string parsing, and more. See how CTEs compare to SQL Server 2012 offset and fetch paging techniques. Get an in depth understanding of the performance behind a common table expression. Understand when the CTE is the right solution, and the wrong solution to use. Finally we will take a look at some classic recursive algorithms and how they can be implemented with CTEs. This session pairs well with the Introduction to Common Table Expressions session.

A great day at PASS Summit today.

I started out the day with the keynote presentation with some great info on PASS and the PASS volunteers, followed by Microsoft presenting  on in memory tables, and native compilation for stored procedures, described and demonstrated as “Wicked Fast”.

Next I attended a fabulous presentation from Erin Stellato on Extended Events, an improved replacement for the SQL profiler. I learned a few things that I will use regularly to troubleshoot performance issues.

After that Glenn Berry presented on using DMVs to diagnose performance problems. This was one of my favorite sessions for the day. Glenn did a great job presenting about 60 queries that can be used to track different performance and configuration problems on SQL Server.

During break I then had a chance to talk to the Microsoft SQL Server team to get a better understanding of the execution plans associated with recursive CTEs.  I will have to blog about this one later, but after talking with 4 different people from Microsoft, I have a better understanding of exactly what the execution plan shows for the recursive CTE.

The next session was on using table and row compression to speed up SQL Server. Using compression you can reduce the number of logical reads because the data is compressed and less pages are hit when running a query. This does however increase CPU load, but it looks to me like the small increase in CPU load would be worth it.

At the end of the day during the Exhibitor Reception I had my first book signing on the CTE book at the Joes2Pros booth. We had 30 books to give away, and when the book signing started, there were already 30 people in line to get a book. It was great to talk with people about what they are using CTEs for.

CTE_UK

I also had a chance to meet Pinal Dave and talk with him for a bit.

Another great day at PASS Summit 13.