Common Table Expressions Presentations Updated.

May 20, 2013 by Leave a Comment

I used to be able to do the Common Table Expression in a single 1 hour session, but after writing the book on Common Table Expressions, I discovered that I just can’t fit it into an hour with the detail that Common Table Expressions deserve. I would rather go into depth so that everyone can master CTEs rather than just breezing over the highlights and leaving everyone wondering.

For future SQL Saturdays and other presentations the sessions have been broken out as follows. Currently I have proposed the following 2 sessions for SQL Saturday Denver.

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 Expression session you will learn everything needed to start using CTEs for recursive queries, as temporary views, and to use the result set multiple times in the same query. Learn how simplify query syntax using CTEs. 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 CTEs before, or for those who want to learn the basics of CTEs including data paging. This session pairs well with the Advanced Common Table Expression session.

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

Filed under CTE, SQL Saturday Tagged with , , , , , ,

A Great Day At SQL Saturday Redmond

May 18, 2013 by Leave a Comment

It was a great day at SQL Saturday today. I was able to preview my Common Table Expressions Book at my Common Table Expressions presentation.  The presentation was great, the room was packed, and there were lots of great questions.

sqlsat212_web

Here is the lineup for the room that I presented in.  Just before me was Rick Morelan presenting on SQL 2012 Error handling, which was a spectacular presentation.

One presentation that I wish I had attended was the Machine Learning for Mere Mortals from Dev Nambi. Although the presentation that I went to in the same time slot was great, I wish I had been able to go to both.

image

The slides are available for download from the presentation here:  Steve Stedman CTE Presentation.

One thing that I had a hard time with during this presentation was being able to get all of the information covered in a hour.  After writing my Common Table Expressions Book I had so many things I my mind that I wanted to cover, but the time just didn’t allow for it.  I think that I will split the CTE presentation into two 1 hour sessions, first the introduction to CTEs for those who have not used CTEs in the past, and then the advanced CTEs for the really fun stuff.

Filed under SQL Saturday, Uncategorized Tagged with , , ,

Presenting at SQL Saturday #212 in Redmond Tomorrow

May 17, 2013 by Leave a Comment

I will be presenting at SQL Saturday #212 in Redmond tomorrow.

sqlsat212_web

My presentation will be on Common Table Expressions, and it is the first CTE presentation since my Common Table Expressions book released.  I have updated the presentation to include a number of new things relating to CTE’s that I figured out while writing the book.

This will be my second SQL Saturday in Redmond, the first one was a year ago.  SQL Saturdays are a great learning experience, and I am looking forward to a great day of free learning.

Also, if you are at SQL Saturday, drop by the Joes2Pros table/booth and take a look at my Common Table Expressions book.  Let me know what you think.  See you in Redmond.

Filed under SQL Saturday Tagged with ,

Recursive Scalar Function in T-SQL

April 22, 2013 by Leave a Comment

In my Common Table Expressions presentation the topic of recursion often comes up, but for scalar functions in T-SQL, it might not be as common.
This article has been written to show how a scalar function in SQL Server can call itself, thus being considered recursive.

What is the Fibonacci Sequence

The Fibonacci Sequence is a classic example that is used to demonstrate recursion.

By definition, the first two numbers in the Fibonacci sequence are 0 and 1, and each subsequent number is the sum of the previous two.

For instance, the following are Fibonacci Numbers:
0, 1, 1, 2, 3, 5, 8, 13, 21, 34, 55, 89, 144, 233, 377, 610, 987, 1597, 2584, 4181, 6765, 10946, 17711, 28657, 46368, 75025, 121393, 196418, 317811, 514229, 832040, 1346269, 2178309, 3524578, 5702887, 9227465, 14930352, 24157817, 39088169, 63245986, 102334155, 165580141, 267914296, 433494437, 701408733 …

Fibonacci Sequence as a Computer Science Challenge

Often times calculating the Fibonacci Sequence is used as a computer science puzzle, or programming interview question to see if you understand recursion.  It is very simple to do in any programming language that supports recursion.

What is Recursion

Recursion is a programming concept where a function, procedure or section of code calls itself. For instance in the following T-SQL example, the scalar function Fibonacci calculates the Fibonacci sequence using recursion, by calling itself.  This is accomplished by calling the function name inside of the body of the function.


CREATE FUNCTION dbo.Fibonacci (@Num integer, @prev integer, @next integer)
RETURNS VARCHAR(4000)
AS
BEGIN
DECLARE @returnValue as VARCHAR (4000) = cast(@prev as varchar(4000));
IF (@Num > 0)
BEGIN
IF (LEN(@returnValue) > 0)
BEGIN
SET @returnValue = @returnValue + ',';
END
SET @returnValue = @returnValue + dbo.Fibonacci(@Num - 1, @next, @next + @prev) ;
END

RETURN @returnValue;
END
GO

To call the function you simply include in a select statement, with the first parameter being the number of Fibonacci numbers to calculate, and the second and third parameters are always 0 and 1. The second and third parameters are set to 0 and 1 to prime the recursive function, and are used internally to pass the recursive call the current and previous values.

select dbo.Fibonacci(10, 0, 1);

Which produces the following output using SSMS:
FIbonacci_Scalar

For more details on the CTE version of Fibonaci take a look at my earlier post.

Enjoy!

Filed under TSQL Tagged with , , , , , , , , ,

Common Table Expression accepted for SQL Saturday 212 in Redmond WA

April 13, 2013 by Leave a Comment

My Common Table Expression presentation was accepted for SQL Saturday 212 in Redmond WA.

This is the session that I am the most familiar with, and that I have presented the most times. I think this will be the 8th time I have presented this session, and after every previous presentation it has been improved.

Over the last several months I have been working with the Rick Morelan and the exceptional team at Joes2Pros to write a book on common table expressions.  This book is in the final cleanup stage, and I hope to have the a couple of the first printed copies of the book available at the session as a door prize or give-away.

Some of the things that will be covered in this session that I haven’t covered in the session before will be:

  • CTEs and the DELETE statement… Can I delete data from a CTE?
  • The UPDATE statement on a CTE… Will it update the base tables?
  • Interesting performance implications. What is safe and what is not safe with a CTE.

I will also be covering the usual topics:

If I had a little more time, I might be able to rename the presentation to “Everything you ever wanted to know about CTE’s, but were afraid to ask”.

This should be a fun presentation based on the amount of focus I have had on CTE’s over the last few months.

 

See you at Microsoft in Redmond on May 18th for SQL Saturday!

Filed under CTE, SQL Saturday, TSQL Tagged with , , , ,

Select Favorite SQL Server 2012 Articles

April 9, 2013 by Leave a Comment

The following articles are a collection of my favorite SQL Server related posts over the last 6 months.

3 Steps to Work More Efficiently in SSMS. Posted 2/17/2013

Recently the 3 Steps to Work More Efficiently in SSMS article was one of my favorites. The three steps were:

  1. Display results in a separate tab.
  2. Hotkey for SELECT TOP 100 * FROM …
  3. sp_help by using Alt+F1

The Display results in a separate tab is one of the first things I do when working in SSMS on a new computer.

VARCHAR diff function Posted 2/13/2013

What made the VARCHAR diff function posting one of my favorite was how it built on a number of previous articles to finally get to something that was much more useful, all based on common table expressions. The VARCHAR diff function used code from a previous posting called Using a CTE to Split a String Into Rows with Line Numbers. A couple weeks after writing the VARCHAR diff function with CTE’s I ended up using this on a project to create an automated testing script to compare the output from an outdated code project to the results from the new T-SQL implementation.  The use of the VARCHAR diff function allowed for a single tester to test the work of 3 developers in a time far less than what was anticipated on this project.

The VARCHAR diff function probably isn’t something that anyone would use on a daily basis, but it is a massive time saver when needed.

TSQL 2012 – Generating letters with a SEQUENCE object Posted 11/9/2012

This article was a lot of fun. Having learned about how to use a SQL Server 2012 sequence object to generate letters at a SQL Saturday presentation, I just had to blog about this one. It was a fun post.

Adventureworks2012 data to Google GeoChart Visualization Posted 10/19/2012

On this article I pulled address data from the AdventureWorks2012 database, and covered how to extract it and reformat it correctly to display on the Google GeoChart Visualizations.

Visualization1

Again another fun posting.

I hope everyone enjoys these as much as I did.

Filed under SQL 2012 Tagged with , , , , , ,

← Older posts