Using the TSQL CHOOSE Function

Here is a short video tutorial that shows how to use the CHOOSE function in T-SQL on SQL Server 2012, SQL Server 2014 or Newer. This was originally part of my free SQL query training for the 70-461 certification exam.

Here is the sample code that goes along with the CHOOSE function training. This sample code has been tested and confirmed working on SQL Server 2012 and SQL Server 2014.

 

CREATE DATABASE [QueryTraining];
GO
USE [QueryTraining];
GO
------------------------------------------------
-- CHOOSE (2012, 2014 or newer)
-- returns the item at a specific index

declare @corners as int = 6;
-- the old way using case.
SELECT CASE @corners
		WHEN 1 THEN 'point'
		WHEN 2 THEN 'line'
		WHEN 3 THEN 'triangle'
		WHEN 4 THEN 'square'
		WHEN 5 THEN 'pentagon'
		WHEN 6 THEN 'hexagon'
		WHEN 7 THEN 'heptagon'
		WHEN 8 THEN 'octagon'
		ELSE NULL
	   END;

-- now with choose
declare @corners as int = 6;
SELECT choose(@corners, 'point', 'line', 'triangle', 'square', 'pentagon',
                        'hexagon', 'heptagon', 'octagon')

-- CHOOSE day of week example
DECLARE @day as int=4
SELECT CHOOSE(@day,'Sunday','Monday', 'Tuesday', 'Wednesday',
                   'Thursday', 'Friday', 'Saturday');

-- Pick 5 people at random from a list of 10
GO
DECLARE @myRandonNum INTEGER;
SET @myRandonNum = CAST(RAND() * 10 AS INTEGER) + 1;
SELECT @myRandonNum as Num, CHOOSE(@myRandonNum, 'Steve', 'Aaron', 'Pete', 'Cody',
                     'Gabe', 'Bill', 'Aaron', 'Jordan', 'Darren', 'Mark', 'Aaron') as Name;
GO 5

Notice on the last line of the sample, the GO command has the number 5 right after it. This tells SSMS to run the batch 5 times. See my blog post on GO for more details.

More Info:

Using the TSQL COALESCE Function

Here is a short video tutorial that shows how to use the COALESCE function in T-SQL on SQL Server.

This was originally part of my free SQL query training for the 70-461 certification exam.

Here is the first sample code from the video:

-- COALESCE

USE [QueryTraining];

CREATE TABLE [dbo].[Departments](
	[ID] [INTEGER] IDENTITY(1,1) PRIMARY KEY NOT NULL,
	[Department] [VARCHAR](200) NOT NULL,
	[Details] [VARCHAR](4000) NULL,
	[Parent] [INT] NULL,
	[Archived] [BIT] NOT NULL DEFAULT(0)
);

INSERT INTO [dbo].[Departments]
           ([Department])
     VALUES
           ('Camping'),
           ('Cycle'),
           ('Snowsports'),
           ('Fitness');

DECLARE @campingDepartmentId as INT;
SELECT @campingDepartmentId = ID
  FROM [dbo].[Departments]
  WHERE [Department] = 'Camping';

INSERT INTO [dbo].[Departments]
           ([Department], [Parent])
     VALUES
           ('Tents', @campingDepartmentId),
           ('Backpacks', @campingDepartmentId),
           ('Sleeping Bags', @campingDepartmentId),
           ('Cooking', @campingDepartmentId);

-- examine the table
SELECT [ID],
	   [Department],
       [Details],
       [Parent],
       [Archived]
  FROM [dbo].[Departments];

-- clean up some NULLs with COALESCE
SELECT [ID],
	   [Department],
       [Details],
       COALESCE([Parent], 0) as Parent,
       [Archived]
  FROM [dbo].[Departments];

And then the code that shows the performance differences:

-- COALESCE compared to ISNULL
-- turn on actual execution plan CTRL+M
set statistics io on;
SELECT [ID],
	   [Department],
       [Details],
	   [Parent],
       COALESCE((SELECT MAX(Parent) FROM [dbo].[Departments]), 0) as MaxParent,
       [Archived]
  FROM [dbo].[Departments];

SELECT [ID],
	   [Department],
       [Details],
	   [Parent],
       ISNULL((SELECT MAX(Parent) FROM [dbo].[Departments]), 0) as MaxParent,
       [Archived]
  FROM [dbo].[Departments];

More Info:

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

 

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.