SQL Sunday Fun – Word Search with New T-SQL 2012 Functionality

 

 

new_sql2012_word_jumbleClick on the grid to enlarge, then print it.

Find the following words in the grid up, down, left, right, horizontal, vertical, and diagonal.

ROWS  RANGE  PRECEDING 
FOLLOWING  UNBOUNDED  IIF 
CHOOSE  OFFSET  FETCH 
FORMAT  CONCAT  SEQUENCE 
PARSE  TRYPARSE  TRYCONVERT 
THROW  LEAD  LAG 
FIRSTVALUE  LASTVALUE  PERCENTRANK 
CUMEDIST  PERCENTILEDISC  PERCENTILECONT 

NOTE:  some of these terms normally have an underscore in them, but for the purpose of the word scramble the underscores have been omitted.

 

Related Links:

SEQUENCE object

CHOOSE function

Analytics – Analytic Functions – PERCENT_RANK

Analytics PERCENTILE_DISC and PERCENTILE_CONT

IIF function in TSQL.

TSQL Analytic Functions LEAD and LAG

OVER clause with ROWS PRECEDING and FOLLOWING in TSQL 2012

OFFSET / FETCH in a SELECT statement

Cumulative Distribution Function (CDF) or as it is known in TSQL CUME_DIST()

 

 

CTE Data Paging

Day 14 of Common Table Expression Month (June) at SteveStedman.com, today we will be taking a look at data paging with CTE’s and how it compares to the offset and fetch in T-SQL 2012.

These queries will be using that database that was set up in a previous posting on the CTE_DEMO Sample Database, if you haven’t set up the sample database, download it and set it up now.

The concept of data paging is taking a large set of results and breaking it up into smaller pages of result sets. For instance if you search your favorite internet search engine for a term, you are usually shown results 1 to 20 of X where X is a very large number.  For instance page 1 is results 1 to 20 of 652,443 results.

Data Paging – Before SQL 2012

First lets take a look at a query with no data paging Shows all tables on this database, and all columns, and returns many rows.


SELECT OBJECT_NAME(sc.object_id) as TableName,
       name as ColumnName
  FROM sys.columns sc
ORDER BY OBJECT_NAME(sc.object_id);

When this is run we get 702 rows in the result set:
DataPagingCTE1

Now to introduce data paging with a CTE,  Here we have a CTE example to get data paging. This example assumes that the page size is 10 the first page would display the first 10 rows, the the second page would display rows 11 to 20, and the third page would display rows 21 to 30. Here we are using the ROW_NUMBER() function with the OVER clause for the windowing functionality to get the row number from the output. In a non-CTE query you can’t use ROW_NUMBER() in the where statement, but if it is wrapped in a CTE, and given a column alias we can then use it in the WHERE clause selecting from the CTE.


declare @pageNum as int;
declare @pageSize as int;
set @pageNum = 2;
set @pageSize = 10;

;WITH TablesAndColumns AS
(
SELECT OBJECT_NAME(sc.object_id) AS TableName,
 name AS ColumnName,
 ROW_NUMBER() OVER (ORDER BY OBJECT_NAME(sc.object_id)) AS RowNum
 FROM sys.columns sc
)
SELECT *
 FROM TablesAndColumns
 WHERE RowNum BETWEEN (@pageNum - 1) * @pageSize + 1
 AND @pageNum * @pageSize ;

When we run this we get the following results for page 2:

DataPagingCTE2

Now if we change the @pageNum variable to be 3 we get the following:

DataPagingCTE3

SQL 2012 Data Paging

If you are using SQL Server 2012, there is a new option added to the SELECT statement, this is called OFFSET and FETCH. You have an option to do data paging without the CTE, the OFFSET and FETCH option allows you to do the following:

</pre>
declare @pageNum as int;
declare @pageSize as int;
set @pageNum = 2;
set @pageSize = 10;

SELECT OBJECT_NAME(sc.object_id) AS TableName,
name AS ColumnName
 FROM sys.columns sc
 ORDER BY TableName
OFFSET (@pageNum - 1) * @pageSize ROWS FETCH NEXT @pageSize ROWS ONLY;
<pre>

Which produces similar results for page 2 as the CTE version as shown here:

DataPagingCTE4

Which is Better?

The only correct answer is “It Depends”.  It depends on a number of things. First if you are writing code that needs to run on an older version of SQL Server than 2012, then you can’t use the  OFFSET and FETCH way 0f doing it, your best option is the CTE data paging. If you are running on only SQL Server 2012 or newer, then you have a choice. The size of your result set, the design of your tables, the number of JOINs in the query will all impact the overall performance. I would suggest if you are using SQL 2012 and you have a choice, code it both ways, and performance test it. See which runs best base on your specific query.

Related Links:

Common Table Expressions Book

If you enjoyed this posting, and want to learn more about common table expressions, please take a look at my book on CTE’s at Amazon.com. The book is titled Common Table Expressions – Joes 2 Pros® – A CTE Tutorial on Performance, Stored Procedures, Recursion, Nesting and the use of Multiple CTEs.

Multiple CTEs in a Query

Day 10 of Common Table Expression Month (June) at SteveStedman.com, today we will be taking a look at how to use multiple CTEs in a query.

These queries will be using that database that was set up in a previous posting on the CTE_DEMO Sample Database, if you haven’t set up the sample database, download it and set it up now.

 

One of the questions that comes up when discussing CTE Scope is can I do multiple CTEs. The answer is yes you can do multiple CTEs but the scope is is still constrained to a single SQL Statement.

Multiple CTEs in a Single Statement

Lets take a look when multiple CTEs in a single statement might be useful. Take the example of trying to generate random names in order to fill up tables in a test database to simulate a full database during the development phase of a new project. This example is explained in much further detail in chapter 6 in my Book on Common Table Expressions. Lets take a look here at this example from my SQL Saturday presentation. The following is a CTE that returns a list of first names.


;WITH Fnames (Name) AS
(

SELECT 'John'
 UNION
 SELECT 'Mary'
 UNION
 SELECT 'Bill'

)
SELECT F.Name FirstName
 FROM Fnames F;

When run we see the following:

MultipleCTEs1

 

Now if we want to expand on that we can add a second CTE for last names and CROSS JOIN the results of the two CTEs. A refresher on CROSS JOIN, the CROSS JOIN takes every row from one side and matches it up with every rows for the other side, with no ON clause. This effectively creates a Cartesian product of the two tables, or joins up every possible combination.


;WITH Fnames (Name) AS
(
SELECT 'John'
 UNION
 SELECT 'Mary'
 UNION
 SELECT 'Bill'
 ),

Lnames (Name) AS
(
SELECT 'Smith'
 UNION
 SELECT 'Gibb'
 UNION
 SELECT 'Jones'
 )

SELECT F.Name AS FirstName, L.Name AS LastName
 FROM Fnames AS F
 CROSS JOIN Lnames AS L;

MultipleCTEs2

Here you will notice that the initial CTE is separated from the new CTE with a comma, and that the second CTE doesn’t start with the WITH keyword. Then in the final SELECT statement it just references both of the CTE statements declared above. When run we see the following results.

MultipleCTEs3

 

Here you can see that every first name is joined up with every last name, with two input tables of 3 rows each the result set has 3×3 or 9 rows of output.

You could also add a third CTE with middle names, and possibly a 4th with login names, and very quickly end up with a very large result set multiplying each time there is another cross join added.

 

Related Links:

Common Table Expressions Book

If you enjoyed this posting, and want to learn more about common table expressions, please take a look at my book on CTE’s at Amazon.com. The book is titled Common Table Expressions – Joes 2 Pros® – A CTE Tutorial on Performance, Stored Procedures, Recursion, Nesting and the use of Multiple CTEs.

Recursive CTE for Dates In A Year

Day 8 of Common Table Expression Month (June) at SteveStedman.com, today I will be building on the intro to recursive CTEs from yesterday and showing how a recursive CTE can be used to calculate information about dates of the year. This would be useful if you were trying to build a calendar.

These queries will be using that database that was set up in a previous posting on the CTE_DEMO Sample Database, if you haven’t set up the sample database, download it and set it up now.

Recursive Review

Yesterday’s topic was the introduction to recursive CTE’s.

RecursiveRevew

In the introduction to recursive CTE’s we covered the declaration of the CTE, the Anchor Query which starts the recursive process, the Recursive Query which continues the recursion, and the Query that calls the CTE.

Recursive CTE for dates in a Year

In the following picture, the CTE is named Dates, the anchor query start out by just selecting January 1st of 2013. Next the recursive part selects CalendarDate from the Dates CTE and it adds a single day to it. This all continues recursively as long as the date is less than January 1st 2014.

RecursiveDates1

There is an additional setting. The OPTION (MAXRECURSION 366) has been added to go past the default 100 levels of recursion.

When we run the query we get the following results:

RecursiveDates2

Which continues all the way to December 31st 2013.

</span>
 ;WITH Dates as
 (
 SELECT cast('2013-01-01' as date) as CalendarDate

UNION ALL

SELECT dateadd(day , 1, CalendarDate) AS CalendarDate
 FROM Dates
 WHERE dateadd (day, 1, CalendarDate) < '2014-01-01'
 )

SELECT *
 FROM Dates
 OPTION (MAXRECURSION 366);
<pre>

 

How Would This Be Useful:

Chapter 10 of the CTE book has a section on finding holes in patterns. Basically you want to query for things that you have it is generally straightforward, but if you want to query for things that you don’t have it is not as easy. Lets say you are working on a scheduling application that needs to look at a list of dates and find the dates that a venue may be available. You can probably easily query the dates the venue is in use, and with the Dates CTE you could then do a left join with exclusions where you left join the Dates CTE to the dates that a venue is in use, then only select the results where the venue date is null. This would return the dates that the venue is available.

Related Links:

Common Table Expressions Book

If you enjoyed this posting, and want to learn more about common table expressions, please take a look at my book on CTE’s at Amazon.com. The book is titled Common Table Expressions – Joes 2 Pros® – A CTE Tutorial on Performance, Stored Procedures, Recursion, Nesting and the use of Multiple CTEs.

Introduction to Recursive CTEs

Day 7 of Common Table Expression Month (June) at SteveStedman.com, today we will be taking a look at the introduction to recursive CTEs.

These queries will be using that database that was set up in a previous posting on the CTE_DEMO Sample Database, if you haven’t set up the sample database, download it and set it up now.

The recursive feature of CTEs is perhaps one of the most powerful things you can do with a Common Table Expression

What is Recursion

Recursion is a concept in programming where a function calls itself. Many of the typical C programming interview questions around data structures like trees, linked lists, and other structures often times use recursion. 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

Recursion With a CTE

Doing recursion as shown above with a function could be considered the brute force way of doing it. The common table expression way of doing recursion is much more elegant, and you can use it even if you don’t have the database permissions needed to create stored procs or functions.

Here is an example of a recursive CTE that queries a heirarchy of store departments:


use [cte_demo];
-- Recursive CTE
;WITH DepartmentCTE(id, Department, Parent, Level) AS
( SELECT id, department, parent, 0 as Level
 FROM Departments
 WHERE parent is NULL
 UNION ALL -- and now for the recursive part
 SELECT d.id, d.department, d.parent,
 DepartmentCTE.Level + 1 as Level
 FROM Departments d
 INNER JOIN DepartmentCTE
 ON DepartmentCTE.id = d.parent)
SELECT *
 FROM DepartmentCTE
 ORDER BY Parent;

The first time I looked at a recursive CTE, I was just confused by the syntax. Lets walk through the different parts and it may make more sense, to start with I will add some white space to the query to split it up a bit:

RecursiveCTE1

Anchor Query

The anchor query is the part of the CTE that starts the recursion. This is the part of the query that is run first that the recursive part will build from.

RecursiveCTE2

Recursive Query

The UNION ALL keyword is used to separate the anchor query from the recursive part.

RecursiveCTE3

After the UNION ALL comes the Recursive Query

RecursiveCTE4

What makes the recursive part of the CTE query recursive is the way that it references itself

Executing the Recursive CTE

Once the recursive CTE has been written, it can be executed just like any other CTE, SELECT everything FROM the CTE name, in this case we are also using ORDER BY to sort the results.

RecursiveCTE5

With the CTE complete, lets take a look at the results.

RecursiveCTE6

Now lets break the results up into what was generated by the anchor and what was generated by the recursive query.

RecursiveCTE7

Whats Next

Now that you understand recursive CTE’s we can get into some really interesting recursive options over the rest of CTE month at SteveStedman.com. Stick around something new most every day.

Related Links:

Common Table Expressions Book

If you enjoyed this posting, and want to learn more about common table expressions, please take a look at my book on CTE’s at Amazon.com. The book is titled Common Table Expressions – Joes 2 Pros® – A CTE Tutorial on Performance, Stored Procedures, Recursion, Nesting and the use of Multiple CTEs.

CTE Scope

Day 5 of Common Table Expression Month (June) at SteveStedman.com, today I will cover concepts around the scope of a CTE.

The queries in the video will be using that database that was set up in a previous posting on the CTE_DEMO Sample Database, if you haven’t set up the sample database, download it and set it up now.

Where is live is not to far from the US/Canadian border. On most days I have no need to visit Canada, but occasionally I may got to Vancouver for a SQLSaturday, or just to go out for some sushi or a comedy club. As long as I have my passport with me, I have the ability to cross into Canada and return to the United States. If I don’t plan ahead and take my passport with me, then visiting Canada is not an option, it is just not allowed. When we talk about scope in any programming language it is similar to  not being able to get to that sushi restaurant inVancouver BC because I don’t have my passport and I can’t cross the border without it. That sushi restaurant would be considered out of scope. In programming languages, including T-SQL, scope is the concept of what you can or can’t access. Some things in T-SQL are accessible from anywhere on the SQL Server (assuming permissions have been granted), for instance a table has what I would call global scope in that it can be referenced from any query accessing the SQL Server (again assuming the correct permissions). If you look at something like a temporary table, it has the scope of the function or query batch that created it, there is also a concept of a global temporary table which has a greater scope.

One of the confusing things around CTE is the scope of a CTE. Given that a CTE is described as being similar to a temporary table, like a temporary named result set, or like a temporary view, all of these imply something that might be around for more than a single query. When using CTE’s, the CTE only exists for the single statement that is using it.

The scope of the CTE is only a single query. That means that the single select, insert or update statement that uses the CTE is the only thing that can access that CTE.

Lets take a look at scope using the following query:


USE [cte_demo];
 GO

SELECT *
FROM Departments
ORDER BY id;

;WITH departmentsCTE(id, department, parent) AS
 ( SELECT id, department, parent
 FROM Departments)
 SELECT * FROM departmentsCTE WHERE id = 1;

SELECT * FROM departmentsCTE WHERE id = 2;

SELECT department
 FROM Departments
 ORDER BY id DESC;

At first glance this batch of SQL may look fine, but when we run it it throws an error.

CTE_Scope1

Even thought we get two result sets.

CTE_Scope2

Lets take a look at the code again, so we can see what is in scope and out of scope. The green rectangle outlines a single query that is using a CTE, and that CTE is available only inside of that single statement.  The red rectangle is attempting to access the CTE from the previous query, and the CTE is not reachable, it is out of scope.

CTE_Scope3

If we take a look again at the original query, what can we do to achieve the results that were intended here, 4 result sets returned, and the 2nd and 3rd queries using the CTE.


USE [cte_demo];
GO

SELECT *
FROM Departments
ORDER BY id;

;WITH departmentsCTE(id, department, parent) AS
( SELECT id, department, parent
FROM Departments)
SELECT * FROM departmentsCTE WHERE id = 1;

SELECT * FROM departmentsCTE WHERE id = 2;

SELECT department
FROM Departments
ORDER BY id DESC;

To make it work we just need to copy the CTE and paste it in to the second query so that it looks like this:


USE [cte_demo];
GO

SELECT *
 FROM Departments
 ORDER BY id;

;WITH departmentsCTE(id, department, parent) AS
( SELECT id, department, parent
 FROM Departments)
SELECT * FROM departmentsCTE WHERE id = 1;

;WITH departmentsCTE(id, department, parent) AS
( SELECT id, department, parent
 FROM Departments)
SELECT * FROM departmentsCTE WHERE id = 2;

SELECT department
 FROM Departments
 ORDER BY id DESC;

Which produces the desired result sets:

CTE_Scope4

Here is how the scope of the CTE’s works. The first CTE query, the CTE is only available int the green rectangle, and the second CTE only has the scope of the blue rectangle.

CTE_Scope6

I hope this helps with the overall understanding of the scope of a common table expression.

Related Links:

Common Table Expressions Book

If you enjoyed this posting, and want to learn more about common table expressions, please take a look at my book on CTE’s at Amazon.com. The book is titled Common Table Expressions – Joes 2 Pros® – A CTE Tutorial on Performance, Stored Procedures, Recursion, Nesting and the use of Multiple CTEs.