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:

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.

A great day at SQL In The City by Redgate

Friday I had a great day at Redgate’s SQL In The City presentation by Steve Jones (@way0utwest). There were three sessions presented which are summarized below.

Session 1 – Team-based Database Development with Version Control

I thought that this session was going to mostly review for me since I am already familiar with the Redgate SQL Source control product, and use it most every day, however there were a few things that I learned that will help me going forward. One of the key points that I had not considered before with SQL Source Control is:

Adding lookup data to source control. After the session and after thinking about it a bit, this now seems obvious, but it is something I had not considered before.  I have always though of source control as a way of storing all the schema, stored procedures, functions, and other types of code that are in the database, but now I am going to look at adding lookup tables to the database.  Consider tables like a zip code table, a states table, a country table, or other tables that don’t change very often, but are used by your database application all the time. In the past when changing this type of tables, I have written a migration script to manually add the new rows, for instance an insert statement to add a new zip code.  But instead if I was to just insert the new zip code to the table that has source control in place for the data, I could then have that data stored in a consistent state that I could always get back to if it is needed. What I love about this is that I already have all the software licenses to do this, I just need to do it.

Session 2 – Automated Build and Test

This was my favorite session of the afternoon, with a task on my to do list to build a Continuous Integration (CI) server, Steve was able to provide all the solutions needed to accomplish my task.

tSQLt for Test Driven Development

Having recently started with TDD in the SQL Server environment I have recently created my own test harness, however this is not something that I want to be responsible for maintaining over time.  tSQLt is a database unit testing framework that allows you to do TDD with SQL Server, as well as allowing you to do continuous testing in the CI environment.

Once tSQLt has been installed, when your run the tests the first time you get results showing that 10 tests succeeded and 1 failed.

tSQLtInitialFailingTests

Next the tSQLt walks you through how to find the code causing the failure and shows you how to fix it. Once you have fixed the code that is failing, You then run the tests again and get a passing score as shown here:

tSQLtPassingTests

I can’t wait to replace the simple TDD system that I built. Redgate offers an integration tool so that all of the test cases can be shown in SSMS. I haven’t had a chance to try that part out yet.

Team City Continuous Integration

There is a free download of Team City for small environments.  This should be enough for me to try it out and determine if it is doing to do what I need. I will post a follow up article after I give Team City a try.

Session 3 – Best Practices for Database Deployment

The main focus of this session was on Continuous Deployment. It was a good overall course, but not something that I am ready for yet. I need to get the Continuous Integration working first.

A couple quotes from Steve Jones on Continuous development were:

You only have two known states, Production and Source Control.

and

ABCD stands for Always Be Continuously Deploying.

These may not be exact quotes, but it is as close as I could get while taking notes.

The concept of two known states, Production and Source Control is interesting.  Everything else can be moving or changing without everyone involved knowing about it.

Summary:

This was a great session, and I would like to thank Redgate and Steve Jones for providing me the information that I need to move forward to build a Continuous Integration system. If you have a chance to visit a SQL In The City session by Redgate, it is well worth time time.

Links:

SSMS Results In A Separate Tab

At the end of my SQL Saturday presentation today on Advanced Common Table Expressions, which is about the 12th time I have presented on CTEs, I was asked a question that I have been asked many times after I present.  I am blogging about this because I have been asked about it so many times. This one one of those hidden gems in SSMS that just makes working in SSMS that much easier.

The question was “I have my results and messages showing up below my T-SQL editor in SQL Server Management studio, how do you get it to show up as 3 tabs, one for the editor, one for results and oen for messages.

The default configuration of SSMS your results and messages windows are shown below the query window as shown below:

ResultsBelowQuery

This configuration is just fine when you are running at a higher resolution, but when you are running at a lower resolution, like most projectors require, it is easier to see what is going on when the Results and Mesages are displayed on separate tabs at the top level as shown in the next two screen shots.

CodeTab

ResultsTab

Based on the default configuration, it is not exactly obvious how to do this.

Here is how you do it.  From the Tools menu in SSMS choose Options. The following dialog will be shown.  Expand the Query Results setting, then SQL Server, then click on the Results to Grid (or Results to Text) depending on your preference.  There are two checkboxes that you should check to get the desired behavior.  These are highlighed in the image below.  “Display results in a separate tab”, and “Switch to results tab after the query executes”.  Click both of these then click the OK button.  Depending your your version of SSMS you may need to restart it for the changes to start working.

OptionsDialog

From this point forward SSMS will default to showing the results in a separate tab.  For SQL Saturday or other presentations this is a great way to share more of the result set with the audience.

Common Table Expressions – Terminating the Previous Statement

Day 4 of Common Table Expression Month (June) at SteveStedman.com, today I will cover the topic of terminating the previous T-SQL statement.

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.

To start with having come from a background years ago of writing C programs, the concept of terminating statements is natural to me. I always feel a bit uncomfortable of a SQL Statement isn’t properly terminated with a semicolon. If I am doing a T-SQL code review for someone else, I usually will make at least one comment about terminating T-SQL statements, unless it has already been done. I know that is not a requirement to terminate every SQL Statement, but it does make it easier to read. However in most cases, other than readability the termination of the previous statement really doesn’t matter. . . In most cases.

For instance, the following two sets of queries work exactly the same with our without the semicolons.

Batch 1:


SELECT * FROM Departments
SELECT * FROM (SELECT id, department, parent FROM Departments) as Dept

Batch 2:


SELECT * FROM Departments;
SELECT * FROM (SELECT id, department, parent FROM Departments) as Dept;

In most cases, when writing multiple T-SQL statements in a batch, the semicolon to terminate the lines really doesn’t matter, but there are a few cases in SQL Server where it does matter, and common table expressions are one of them.

Improper Termination

To start with lets take a look at the sample code from our previous posting titled “Writing Your First Common Table Expression with SQL Server“. If we tried to run this SQL without any semicolons, it would run fine if it was run one line at a time, but if we run it as a batch, it throws an error.

USE cte_demo
GO

SELECT * FROM Departments

WITH departmentsCTE(id, department, parent) AS
(
 SELECT id, department, parent
 FROM Departments
)
SELECT *
 FROM departmentsCTE

When run produces the following results:

CTE_Termination

Msg 336, Level 15, State 1, Line 5
Incorrect syntax near 'departmentsCTE'. If this is intended to be a 
common table expression, you need to explicitly terminate the previous 
statement with a semi-colon.

Which is a descriptive enough message stating that we need to explicitly terminate the previous statement with a semi-colon.

Proper Termination

When there is another T-SQL Statement in a batch prior to a CTE, that statement must be terminated with a semi-colon like this:

USE cte_demo
GO

SELECT * FROM Departments;

WITH departmentsCTE(id, department, parent) AS
(
 SELECT id, department, parent
 FROM Departments
)
SELECT *
 FROM departmentsCTE

Note, the only required semi-colon is the one at the end of the SELECT * FROM Departments; query, and we get the desired results.
CTE_Termination2

GO Keyword in SSMS

When working in SQL Server Management Studio, an alternative is to terminate the batch, and the GO keyword is used to break a number of T-SQL statements into different batches, like this:


USE cte_demo;
GO

SELECT * FROM Departments

GO

WITH departmentsCTE(id, department, parent) AS
(
 SELECT id, department, parent
 FROM Departments
)
SELECT *
 FROM departmentsCTE

GO

CTE_Termination3

Clean Code

Being an advocate for clean code, and clearing specifying your intentions, the way that I would write is with a semi-colon at the end of every T-SQL statement, and an extra semi-colon at the beginning of any CTE just to be safe.


USE cte_demo;
GO

SELECT * FROM Departments;

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

CTE_Termination4

How you decide to do it is up to your own coding standards, and as long as it is clearly defined and supports clean code it is just fine.

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.

Sample Database for Common Table Expressions

As I have presented my Common Table Expressions presentation many times, and as part of writing the Common Table Expressions Book I have created some sample database scripts to use.

Once you load up the script into SSMS and refresh the databases tree item, you will see a new database called cte_demo that includes 2 tables.

CTE_DEMO_SCRIPT

dbo.Departments

The first table is called dbo.Departments and it contains 17 rows with a number of store departments with the top level departments being indicated by a parent of NULL, and subdepartments having actual parent ID’s.

CTE_DEMO_dbo_departments

dbo.Royalty

The dbo.Royalty table contains several generations of the British Royal Family family tree. We will use these for some interesting hierarchical queries.

CTE_DEMO_dbo_royalty

Stored Procedures

This database also includes 2 stored procedures to rebuild the Departments table as we will be changing data in that table as we test different scripts.

Downloadable Scripts

Download one of the following scripts and run it in SQL Server Management Studio to set up the CTE_DEMO database for use in several samples.

This script is intended to be run on a test or development server and is not intended to be run on a production system.

 

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