CTE Data Paging in a Procedure

Day 15 of Common Table Expression Month (June) at SteveStedman.com, today we will be taking a look at data paging with CTE’s in a function. Yesterday we took a look at CTEs for Data Paging, today we will build on what we used yesterday.

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.

CTE Data Paging


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 ;

Now lets wrap it in a procedure to simplify things.


CREATE PROCEDURE TablesAndColumnsPager @pageNum int, @pageSize int
AS
BEGIN
SET NOCOUNT ON;

;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 ;
END

Then to call the paging function it is a bit cleaner.


exec TablesAndColumnsPager 1, 10;
exec TablesAndColumnsPager 2, 10;
exec TablesAndColumnsPager 3, 10;

Which produces the following output.
DataPagingSproc1

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.

T-SQL: A Simple Example Using a Cursor

In SQL Server the cursor is a tool that is used to iterate over a result set, or to loop through each row of a result set one row at a time. It may not be the best way to work with a set of data, but if you need to loop row by agonizing row (RBAR) in a T-SQL script then a cursor is one way of doing it.

Note: If you are new to SQL Server and come from an Oracle background, you should know that cursors on SQL Server are different from those on Oracle.

Before creating the cursor, we will just start with a simple query that will end up being used in the cursor.


USE AdventureWorks2008;
GO

SELECT BusinessEntityID, Name
 FROM Sales.Store;

Which looks something like this:

SimpleCursor1

Now to convert it to a cursor, instead of just a select statement.

Step 1: Declare variables to hold the output from the cursor.

</p>
DECLARE @BusinessEntityID as INT;
DECLARE @BusinessName as NVARCHAR(50);

Step 2: Declare the cursor object;


DECLARE @BusinessCursor as CURSOR;

Step 3: Assign the query to the cursor.


SET @BusinessCursor = CURSOR FOR
SELECT BusinessEntityID, Name
 FROM Sales.Store;

Step 4: Open the cursor.


OPEN @BusinessCursor;

Step 5: Fetch the first row.


FETCH NEXT FROM @BusinessCursor INTO @BusinessEntityID, @BusinessName;

Step 5: Loop until there are no more results.  In the loop print out the ID and the name from the result set and fetch the net row.


WHILE @@FETCH_STATUS = 0
BEGIN
 PRINT cast(@BusinessEntityID as VARCHAR (50)) + ' ' + @BusinessName;
 FETCH NEXT FROM @BusinessCursor INTO @BusinessEntityID, @BusinessName;
END

Step 6: Close the cursor.


CLOSE @BusinessCursor;

Step 7: Deallocate the cursor to free up any memory or open result sets.


DEALLOCATE @BusinessCursor;

Now putting it all together:


DECLARE @BusinessEntityID as INT;
DECLARE @BusinessName as NVARCHAR(50);

DECLARE @BusinessCursor as CURSOR;

SET @BusinessCursor = CURSOR FOR
SELECT BusinessEntityID, Name
 FROM Sales.Store;

OPEN @BusinessCursor;
FETCH NEXT FROM @BusinessCursor INTO @BusinessEntityID, @BusinessName;

WHILE @@FETCH_STATUS = 0
BEGIN
 PRINT cast(@BusinessEntityID as VARCHAR (50)) + ' ' + @BusinessName;
 FETCH NEXT FROM @BusinessCursor INTO @BusinessEntityID, @BusinessName;
END

CLOSE @BusinessCursor;
DEALLOCATE @BusinessCursor;

SimpleCursor2

This should give you a quick overview of how to quickly build and use a cursor on Microsoft SQL Server. The example shown was run on SQL Server 2008, and works the same on SQL Server 2005 or 2012.

Enjoy!

Related Links

 


 

Recursive Scalar Function in T-SQL

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!

SQL Server 2012 IIF Statement

SQL Server 2012 introduces the IIF statement. It is very similar to the IIF statement in MS Access.

I have extracted the IIF part of my presentation on Whats new in SQL Server 2012, and turned it into a video with a demo of how to use the IIF statement. The IIF statement is a quick shortcut to simple CASE statements.

Here is the T-SQL references in the video:

USE [Master];
CREATE DATABASE [tsql2012];
GO

USE [tsql2012];

-- Table to be used by Over Clause Rows/Range
CREATE TABLE REVENUE
(
[DepartmentID] int,
[Revenue] int,
[Year] int
);

insert into REVENUE
values (1,10030,1998),(2,20000,1998),(3,40000,1998),
(1,20000,1999),(2,60000,1999),(3,50000,1999),
(1,40000,2000),(2,40000,2000),(3,60000,2000),
(1,30000,2001),(2,30000,2001),(3,70000,2001),
(1,90000,2002),(2,20000,2002),(3,80000,2002),
(1,10300,2003),(2,1000,2003), (3,90000,2003),
(1,10000,2004),(2,10000,2004),(3,10000,2004),
(1,20000,2005),(2,20000,2005),(3,20000,2005),
(1,40000,2006),(2,30000,2006),(3,30000,2006),
(1,70000,2007),(2,40000,2007),(3,40000,2007),
(1,50000,2008),(2,50000,2008),(3,50000,2008),
(1,20000,2009),(2,60000,2009),(3,60000,2009),
(1,30000,2010),(2,70000,2010),(3,70000,2010),
(1,80000,2011),(2,80000,2011),(3,80000,2011),
(1,10000,2012),(2,90000,2012),(3,90000,2012);

USE [tsql2012];
-- first lets look at the REVENUE table
SELECT *
FROM Revenue;

-------------------------------------------------------------------

-- IIF
-- http://stevestedman.com/?p=1578
-- assume we want to display an indicator to see if we are above
-- or below average. First we start with the average over departmentID
SELECT Year, DepartmentID, Revenue,
avg(Revenue) OVER (PARTITION by DepartmentID) as AverageRevenue
FROM REVENUE
ORDER BY DepartmentID, year;
-- without IIF using the CASE statement we would get the following
-- http://stevestedman.com/?p=1578
SELECT Year, DepartmentID, Revenue, AverageRevenue,
CASE WHEN Revenue > AverageRevenue
THEN 'Better Than Average'
ELSE 'Not Better'
END as Ranking
FROM (SELECT Year, DepartmentID, Revenue,
avg(Revenue) OVER (PARTITION by DepartmentID) as AverageRevenue
FROM REVENUE ) as t
ORDER BY DepartmentID, year;

-- now the same functionality using IIF and simplifying the code
-- http://stevestedman.com/?p=1578
SELECT Year, DepartmentID, Revenue, AverageRevenue,
iif(Revenue > AverageRevenue, 'Better Than Average', 'Not Better') as Ranking
FROM (SELECT Year, DepartmentID, Revenue,
avg(Revenue) OVER (PARTITION by DepartmentID) as AverageRevenue
FROM REVENUE ) as t
ORDER BY DepartmentID, year;

----------------------------------
SELECT IIF(3=2,'true',NULL) -- ERROR ?

----------------------------------

SELECT IIF(1=7,NULL,NULL) -- ERROR !
--Do you think that IIF actually uses the CASE statement?

TSQL Pivot Table

Here is a quick sample of how to implement a pivot table in TSQL for SQL Server. The example below creates a database called pivot, you probably already have your own database to work in. Then it creates a table called REVENUE and fills it in with department revenue for just over a 10 year period. Then you see a couple simple select statements followed by a SELECT statement that pivots the data.


USE [Master];
set statistics io off;

IF EXISTS(SELECT name FROM sys.databases WHERE name = 'pivot')
BEGIN
ALTER DATABASE [pivot] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE [pivot];
END
CREATE DATABASE [pivot];
GO

USE [pivot];

-- Table to be used for demo
CREATE TABLE REVENUE
(
[DepartmentID] int,
[Revenue] int,
[Year] int
);

insert into REVENUE
values (1,10030,1998),(2,20000,1998),(3,40000,1998),
(1,20000,1999),(2,60000,1999),(3,50000,1999),
(1,40000,2000),(2,40000,2000),(3,60000,2000),
(1,30000,2001),(2,30000,2001),(3,70000,2001),
(1,90000,2002),(2,20000,2002),(3,80000,2002),
(1,10300,2003),(2,1000,2003), (3,90000,2003),
(1,10000,2004),(2,10000,2004),(3,10000,2004),
(1,20000,2005),(2,20000,2005),(3,20000,2005),
(1,40000,2006),(2,30000,2006),(3,30000,2006),
(1,70000,2007),(2,40000,2007),(3,40000,2007),
(1,50000,2008),(2,50000,2008),(3,50000,2008),
(1,20000,2009),(2,60000,2009),(3,60000,2009),
(1,30000,2010),(2,70000,2010),(3,70000,2010),
(1,80000,2011),(2,80000,2011),(3,80000,2011),
(1,10000,2012),(2,90000,2012),(3,90000,2012);
USE [pivot];
-- first lets look at the REVENUE table

SELECT *
FROM Revenue;
SELECT DepartmentId, Year, Revenue
FROM Revenue;

-- Simple Pivot
SELECT Year, [1], [2], [3]
FROM (SELECT Year, DepartmentId, Revenue FROM Revenue) as t
PIVOT
(
sum(Revenue)
FOR DepartmentId in ([1], [2], [3])
) as pivotTable;

Just to recap, here is the actual pivot code, and the output that it produces.

-- Simple Pivot
SELECT Year, [1], [2], [3]
FROM (SELECT Year, DepartmentId, Revenue FROM Revenue) as t
PIVOT
(
sum(Revenue)
FOR DepartmentId in ([1], [2], [3])
) as pivotTable;

I hope you find this useful as an example of working with PIVOT tables in SQL Server using the select statement in TSQL.