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.

CTE book signing at PASS SUMMIT 2013

image

image

image

A great turnout for the CTE book signing tonight.  Enjoy the books.

In Denver for SQL Saturday

I will be speaking at SQL Saturday Denver, presenting on Advanced Common Table Expressions.  This presentation should be a lot of fun.  I will be covering all the advanced and fun CTE features that I haven’t had time to fit into my introduction to CTE session in the past.

sqlsat190_speaking

Here is the outline of the session:

1.Recursive CTEs.
2.Hierarchical CTEs.
3.Manipulating Data.
4.Common Use Cases.
5.CTE Performance Considerations.
I will also be giving away a couple signed copies of my book on Common Table Expressions book.
Paperback Kindle
See you there!

I’m Speaking at SQL Saturday #190 in Denver

sqlsat190_speaking

 

One of my sessions was accepted for SQL Saturday #190 in Denver this year.

Please come see my CTE presentation.

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