Using the TSQL IIF Function

Here is a quick video tutorial on how to use the IIF function on SQL Server 2012, SQL Server 2014 0r newer. This was originally part of my free SQL query training for the 70-461 certification exam.

Here is the sample code from the video.


USE Master;
GO

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

USE [QueryTraining];
GO

------------------------ EXAMPLES SETUP ------------------------
-- Table to be used for training
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);

------------------------------------------------
-- IIF (2012)
USE [QueryTraining];
GO

-- 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?

More Info:

Simple Cursor Example : FORWARD_ONLY vs FAST FORWARD

First lets take a look at two queries using CURSORS, the first one will use the FORWARD_ONLY type cursor, and the second will use the FAST_FORWARD type cursor. These two types sound very similar, but perform quite differently.

DECLARE @firstName as NVARCHAR(50);
DECLARE @middleName as NVARCHAR(50);
DECLARE @lastName as NVARCHAR(50);
DECLARE @phone as NVARCHAR(50);
DECLARE @PeoplePhoneCursor as CURSOR;

SET @PeoplePhoneCursor = CURSOR FORWARD_ONLY FOR
SELECT TOP 10 FirstName, MiddleName, LastName, PhoneNumber
  FROM person.Person p
 INNER JOIN person.personphone pp on p.BusinessEntityID = pp.BusinessEntityID;

OPEN @PeoplePhoneCursor;
FETCH NEXT FROM @PeoplePhoneCursor INTO @firstName, @middleName, @lastName, @phone;
 WHILE @@FETCH_STATUS = 0
BEGIN
	 PRINT ISNULL(@firstName, '') + ' ' +
	       ISNULL(@middleName, '') + ' ' +
	       ISNULL(@lastName, '')  +
	       ' Phone: ' + ISNULL(@phone, '') ;
	 FETCH NEXT FROM @PeoplePhoneCursor INTO @firstName, @middleName, @lastName, @phone;
END
CLOSE @PeoplePhoneCursor;
DEALLOCATE @PeoplePhoneCursor;

Now for the FAST_FORWARD CURSOR example. Notice only one line has changed, that’s the line that says “SET @PeoplePhoneCursor = CURSOR FAST_FORWARD FOR”.

DECLARE @firstName as NVARCHAR(50);
DECLARE @middleName as NVARCHAR(50);
DECLARE @lastName as NVARCHAR(50);
DECLARE @phone as NVARCHAR(50);
DECLARE @PeoplePhoneCursor as CURSOR;

-- HERE IS WHERE WE SET THE CURSOR TO BE FAST_FORWARD
SET @PeoplePhoneCursor = CURSOR FAST_FORWARD FOR
SELECT TOP 10 FirstName, MiddleName, LastName, PhoneNumber
  FROM person.Person p
 INNER JOIN person.personphone pp on p.BusinessEntityID = pp.BusinessEntityID;

OPEN @PeoplePhoneCursor;
FETCH NEXT FROM @PeoplePhoneCursor INTO @firstName, @middleName, @lastName, @phone;
 WHILE @@FETCH_STATUS = 0
BEGIN
	 PRINT ISNULL(@firstName, '') + ' ' +
	       ISNULL(@middleName, '') + ' ' +
	       ISNULL(@lastName, '')  +
	       ' Phone: ' + ISNULL(@phone, '') ;
	 FETCH NEXT FROM @PeoplePhoneCursor INTO @firstName, @middleName, @lastName, @phone;
END
CLOSE @PeoplePhoneCursor;
DEALLOCATE @PeoplePhoneCursor;

At this point the two queries aren’t that different. Let take a look at how they perform.

To do this, First I turn on Actual Execution Plans, then I highlight both queries in SSMS and run them. The results look something like this.
forward_only_cursor1
From which we can see that opening the cursor takes 8% of the cost of the entire batch, and each step through the cursor takes up another 6% of the entire batch. So overall the first example using the FORWARD_ONLY cursor takes 72% of the entire batch.

When we look at the plan associated with the second cursor loop, using the FAST FORWARD cursor option, we see different numbers:

fast_forward_cursor

Again, opening the CURSOR takes 8% of the batch, then each lop through takes 1% for a total of 18% of the cost of the entire batch. The missing 10% is accounted for in rounding errors since SQL Server Management Studio only shows whole numbers on the percentage of the batch.

With can see with this example the FORWARD_ONLY CURSOR takes 4 times the time as the FAST FORWARD CURSOR, and the number continues to widen as the number of times the cursor loops is executed.

FAST FORWARD CURSORS are usually the fastest option with SQL Server. There may be cases where another option may work better, but the FAST FORWARD CURSOR is a good place to start if you must use a CURSOR.

 

Related Links

 

T-SQL: A Listing Databases 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.

For more information on cursors, also take a look at the free SQL query training provided by Steve Stedman.

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

SELECT name, database_id
  FROM sys.databases ;

Now we can take that query and wrap it with a CURSOR:


DECLARE @DatabaseID as INT;
DECLARE @DatabaseName as NVARCHAR(50);

DECLARE @DatabaseCursor as CURSOR;

SET @DatabaseCursor = CURSOR FOR
SELECT name, database_id
  FROM sys.databases ;

OPEN @DatabaseCursor;
FETCH NEXT FROM @DatabaseCursor INTO @DatabaseName, @DatabaseID;

WHILE @@FETCH_STATUS = 0
BEGIN
 PRINT cast(@DatabaseID as VARCHAR (50)) + ' ' + @DatabaseName;
 FETCH NEXT FROM @DatabaseCursor INTO @DatabaseName, @DatabaseID;
END

CLOSE @DatabaseCursor;
DEALLOCATE @DatabaseCursor;

Now, inside of the WHILE loop you could replace the PRINT statement with some else that performs an action on each database. This works similar to how the undocumented sp_foreachdb stored procedure works.

 

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 , SQL Server 2008R2, SQL Server 2012 or SQL Server 2014.

Enjoy!

-Steve Stedman

Related Links

 

Executing a TSQL batch multiple times using GO

Using the GO commend in TSQL to run a batch of code multiple times is something that I commonly do in demo’s and training’s.  What amazes me is how many people after the training will tell me that they didn’t know about using the GO command to run a statement multiple times.

Here’s how it works. First lets create a table, then we will insert 100000 into the table using a while loop.

CREATE TABLE SampleTable(
	ID INT NOT NULL IDENTITY(1,1),
	SomeData uniqueidentifier NOT NULL DEFAULT(NEWID())
);

DECLARE @i INT; 
SET @i = 1;
WHILE @i <= 100000
BEGIN
	INSERT dbo.SampleTable DEFAULT VALUES ;
	SET @i = @i + 1;
END 

Sure that works, but it takes several lines of code to loop. There is an easier way to do it.

CREATE TABLE SampleTable(
	ID INT NOT NULL IDENTITY(1,1),
	SomeData uniqueidentifier NOT NULL DEFAULT(NEWID())
);

GO
	INSERT dbo.SampleTable DEFAULT VALUES; 
GO 100000

In this example, GO is the batch separator, SSMS breaks up the TSQL on GO statements, then runs each batch one at a time.
The first GO command is used to simply separate the CREATE statement from the INSERT statement. Then the second GO with followed by 100000 tells SSMS to run that batch 100000 times. If we didn’t have the first GO, the CREATE TABLE would have been attempted 100000 times, not just the INSERT STATEMENT.

GO IS NOT A TSQL KEYWORD.

GO is simply a SSMS batch separator word that can be modified to be something else in the SSMS settings if you want to change it. GO is not part of TSQL, and won’t work when you call TSQL from your own programs.

 

A quick tip that will hopefully save you some time.

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:

T-SQL: A Simple Example Using a Cursor

For more information on cursors, also take a look at the free SQL query training provided by Steve Stedman.

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 , SQL Server 2008R2, SQL Server 2012 or SQL Server 2014.

Here is a video showing a similar overview of using cursors in TSQL.

cursorVideoScreenshot

Enjoy!

-Steve Stedman

Related Links