Animating a Magic 8 Ball in SQL Server Management Studio

Published on: 2019-05-14

This post is a response to this month’s T-SQL Tuesday #114 prompt by Matthew McGiffen.  T-SQL Tuesday is a way for the SQL Server community to share ideas about different database and professional topics every month. This month Matthew asks us to write about puzzles, so I decided to recreate a childhood favorite in SQL Server.


Watch this week’s episode on YouTube.

As a kid, I found Magic 8 Balls alluring. There is something appealing about a who-knows-how-many-sides die emerging from the depths of a mysterious inky blue fluid to help answers life’s most difficult questions.

I never ended up buying a magic eight ball of my own though, so today I’m going to build and animate one in SQL Server Management Studio.

Fun and Valuable? Signs point to yes.

While building a magic eight ball in SQL Server is not the most useful project in the world it is:

  1. Fun
  2. A great way to learn lots of cool SSMS and SQL tips and tricks to use in more useful situations.

Here’s an example of the finished project followed by all of the components that make this project work. The full code for this solution can be found at the bottom of this post.

SSMS Magic 8 Ball

VALUES()

I needed a way to store all of the Magic 8 Ball messages. Some days I like UNIONing together a bunch of SELECT statements, but for these “larger” static datasets I like the syntax of VALUES().

SELECT * FROM 
(VALUES  
    ('It is certain.'), 
    ('It is decidedly so.'), 
    ('Without a doubt.'), 
    ('Yes - definitely.'), 
    ('You may rely on it.'), 
    ('As I see it, yes.'), 
    ('Most likely.'), 
    ('Outlook good.'), 
    ('Yes.'), 
    ('Signs point to yes.'), 

    ('Reply hazy, try again.'), 
    ('Ask again later.'), 
    ('Better not tell you now.'), 
    ('Cannot predict now.'), 
    ('Concentrate and ask again.'), 

    ('Don''t count on it.'), 
    ('My reply is no.'), 
    ('My sources say no.'), 
    ('Outlook not so good.'), 
    ('Very doubtful.') 
) T(Response) 

ORDER BY NEWID()

After we create our data set of static messages, we need to randomly return 1 message for every shake of the eight ball. My favorite way to return one random record is to order the data by NEWID() (creating a random order for values) and then using TOP 1 to return only the first random record:

DECLARE @Message varchar(100) = '';
 
WITH MagicResponses AS ( 
    ...<VALUES() query from above>...
)

SELECT TOP 1 @Message = Response FROM MagicResponses ORDER BY NEWID();

Table Driven Animation

While I never have used this technique for animating an image before, I have used a control table to drive what data should get processed in an ETL.

In today’s case, instead of saving the values of what data was last manipulated in an SSIS package, I’ll be storing what each action each frame of animation should display, as well as how much delay to put in between each frame:

CREATE TABLE dbo.AnimationControl
(
    Id int IDENTITY PRIMARY KEY,
    ActionToTake varchar(20),
    DelayToTake varchar(20),
    ActionTakenDate datetime2

);

INSERT INTO dbo.AnimationControl (ActionToTake,DelayToTake) VALUES ('ShakeLeft','00:00:00.100');
INSERT INTO dbo.AnimationControl (ActionToTake,DelayToTake) VALUES ('ShakeRight','00:00:00.100');
INSERT INTO dbo.AnimationControl (ActionToTake,DelayToTake) VALUES ('ShakeLeft','00:00:00.100');
INSERT INTO dbo.AnimationControl (ActionToTake,DelayToTake) VALUES ('ShakeRight','00:00:00.100');
INSERT INTO dbo.AnimationControl (ActionToTake,DelayToTake) VALUES ('ShakeLeft','00:00:00.100');
INSERT INTO dbo.AnimationControl (ActionToTake,DelayToTake) VALUES ('ShakeRight','00:00:00.100');
INSERT INTO dbo.AnimationControl (ActionToTake,DelayToTake) VALUES ('Reveal','00:00:00.500');

WAITFOR DELAY

I wanted there to be a different delay between certain animation frames (I believe the final message reveal deserves a slightly more dramatic pause), so I’m using WAITFOR DELAY to achieve that.

WAITFOR DELAY @DelayToTake;

PRINT

The goal here is to print this ascii 8 ball shaking left and right before displaying the message. We do this using good old fashioned PRINT(). After printing a particular frame we update our control table to indicate that particular frame has been drawn.

IF @CurrentActionType = 'ShakeLeft'
BEGIN 
    PRINT(' 
                _.a$$$$$a._ 
              ,$$$$$$$$$$$$$. 
            ,$$$$$$$$$$$$$$$$$. 
           d$$$$$$$$$$$$$$$$$$$b 
          d$$$$$$$$~`"`~$$$$$$$$b 
         ($$$$$$$p   _   q$$$$$$$) 
         $$$$$$$$   (_)   $$$$$$$$ 
         $$$$$$$$   (_)   $$$$$$$$ 
         ($$$$$$$b       d$$$$$$$) 
          q$$$$$$$$a._.a$$$$$$$$p 
           q$$$$$$$$$$$$$$$$$$$p 
            `$$$$$$$$$$$$$$$$$` 
              `$$$$$$$$$$$$$` 
                `~$$$$$$$~` 
    ') 
END 

GO

You might be wondering why I decided to use a control table to dictate what images to animate. The trouble was that in order to get the PRINT to actually display our ascii images on screen in SSMS, the batch needed to finish submitting. So each frame we print needs to be part of its own batch.

Since we have 7 frames in our animation, we need to execute our procedure 7 times.

Alternatively we can use GO 7, but then we get that ugly batch execution completed message which I don’t think there is anyway to hide:

EXEC dbo.USP_ShakeThe8Ball;
GO 7

-- OR
EXEC dbo.USP_ShakeThe8Ball;
GO
EXEC dbo.USP_ShakeThe8Ball;
GO
EXEC dbo.USP_ShakeThe8Ball;
GO
EXEC dbo.USP_ShakeThe8Ball;
GO
EXEC dbo.USP_ShakeThe8Ball;
GO
EXEC dbo.USP_ShakeThe8Ball;
GO
EXEC dbo.USP_ShakeThe8Ball;
GO

Completely Useless? I guess not

I’ve always been a fan of occasionally taking a break to build things for pure fun. It’s a good way to apply lesser known features to your code, stretch your creativity for solving problems, and of course push software functionality to their limits through feature abuse.

Here is the full set of code if you want to run it for yourself (note, this works on a 1920×1080 resolution monitor with SSMS at full screen…your results may vary):

CREATE OR ALTER PROCEDURE dbo.USP_ShakeThe8Ball
AS
BEGIN
    /* Hide extra output to the messages window that will ruin our animation */
    SET NOCOUNT ON;
    SET ANSI_WARNINGS OFF;

	/* Set up a table to keep track of our animation frames and insert into it */
	IF OBJECT_ID('dbo.AnimationControl') IS NULL
	BEGIN
		CREATE TABLE dbo.AnimationControl
		(
			Id int IDENTITY PRIMARY KEY,
			ActionToTake varchar(20),
			DelayToTake varchar(20),
			ActionTakenDate datetime2
    
		);
	END;

	INSERT INTO dbo.AnimationControl (ActionToTake,DelayToTake) VALUES ('ShakeLeft','00:00:00.100');
	INSERT INTO dbo.AnimationControl (ActionToTake,DelayToTake) VALUES ('ShakeRight','00:00:00.100');
	INSERT INTO dbo.AnimationControl (ActionToTake,DelayToTake) VALUES ('ShakeLeft','00:00:00.100');
	INSERT INTO dbo.AnimationControl (ActionToTake,DelayToTake) VALUES ('ShakeRight','00:00:00.100');
	INSERT INTO dbo.AnimationControl (ActionToTake,DelayToTake) VALUES ('ShakeLeft','00:00:00.100');
	INSERT INTO dbo.AnimationControl (ActionToTake,DelayToTake) VALUES ('ShakeRight','00:00:00.100');
	INSERT INTO dbo.AnimationControl (ActionToTake,DelayToTake) VALUES ('Reveal','00:00:00.500');

 
    DECLARE @CurrentActionId int = 1;
    DECLARE @CurrentActionType varchar(20) = 'ShakeLeft';
    DECLARE @DelayToTake varchar(20) = '00:00:00.100';
 
    /* If more than 1 second elapsed, clear the control table */
    DECLARE @LastRunDate datetime2;
    SELECT @LastRunDate = MAX(ActionTakenDate) FROM dbo.AnimationControl;
    
    IF DATEDIFF(millisecond,@LastRunDate,GETDATE()) > 1000
    BEGIN
        UPDATE dbo.AnimationControl SET ActionTakenDate = NULL;
    END
    
    /* Which action/frame are we currently on? */
 
    SELECT @CurrentActionId = MIN(Id) FROM dbo.AnimationControl WHERE ActionTakenDate IS NULL;
    SELECT @CurrentActionType = ActionToTake,
            @DelayToTake = DelayToTake
        FROM dbo.AnimationControl WHERE Id = @CurrentActionId
    
	    
    WAITFOR DELAY @DelayToTake;
    /* Since we can't clear the Messages window, we need to fill it with
    blank space between animation frames to achieve the desired effect */
 
  
    PRINT(' 
 
  

 
        '); 
 
 
    IF @CurrentActionType = 'ShakeLeft'
    BEGIN 
 
        PRINT(' 
                  _.a$$$$$a._ 
                 ,$$$$$$$$$$$$$. 
               ,$$$$$$$$$$$$$$$$$. 
              d$$$$$$$$$$$$$$$$$$$b 
             d$$$$$$$$~`"`~$$$$$$$$b 
            ($$$$$$$p   _   q$$$$$$$) 
            $$$$$$$$   (_)   $$$$$$$$ 
            $$$$$$$$   (_)   $$$$$$$$ 
            ($$$$$$$b       d$$$$$$$) 
             q$$$$$$$$a._.a$$$$$$$$p 
              q$$$$$$$$$$$$$$$$$$$p 
               `$$$$$$$$$$$$$$$$$` 
                 `$$$$$$$$$$$$$` 
                   `~$$$$$$$~` 
        ') 
 
    END 
 
    If @CurrentActionType = 'ShakeRight' 
 
    BEGIN 
 
        PRINT(' 
                      _.a$$$$$a._ 
                     ,$$$$$$$$$$$$$. 
                   ,$$$$$$$$$$$$$$$$$. 
                  d$$$$$$$$$$$$$$$$$$$b 
                 d$$$$$$$$~`"`~$$$$$$$$b 
                ($$$$$$$p   _   q$$$$$$$) 
                $$$$$$$$   (_)   $$$$$$$$ 
                $$$$$$$$   (_)   $$$$$$$$ 
                ($$$$$$$b       d$$$$$$$) 
                 q$$$$$$$$a._.a$$$$$$$$p 
                  q$$$$$$$$$$$$$$$$$$$p 
                   `$$$$$$$$$$$$$$$$$` 
                     `$$$$$$$$$$$$$` 
                       `~$$$$$$$~` ') 
 
    END 
 
    IF @CurrentActionType = 'Reveal'
    BEGIN
 
        DECLARE @Message varchar(100) = '';
 
        WITH MagicResponses AS ( 
        SELECT * FROM 
        (VALUES  
            ('It is certain.'), 
            ('It is decidedly so.'), 
            ('Without a doubt.'), 
            ('Yes - definitely.'), 
            ('You may rely on it.'), 
            ('As I see it, yes.'), 
            ('Most likely.'), 
            ('Outlook good.'), 
            ('Yes.'), 
            ('Signs point to yes.'), 
 
            ('Reply hazy, try again.'), 
            ('Ask again later.'), 
            ('Better not tell you now.'), 
            ('Cannot predict now.'), 
            ('Concentrate and ask again.'), 
 
            ('Don''t count on it.'), 
            ('My reply is no.'), 
            ('My sources say no.'), 
            ('Outlook not so good.'), 
            ('Very doubtful.') 
        ) T(Response) 
        )
 
        SELECT TOP 1 @Message = Response FROM MagicResponses ORDER BY NEWID();

		BEGIN 
 
        PRINT(' 
                      _.a$$$$$a._ 
                     ,$$$$$$$$$$$$$. 
                   ,$$$$$$$$$$$$$$$$$. 
                  d$$$$$$$$$$$$$$$$$$$b 
                 d$$$$$$$$~`"`~$$$$$$$$b 
                ($$$$$$$p   _   q$$$$$$$) 
                $$$$$$$$   (_)   $$$$$$$$           ' + @Message + '
                $$$$$$$$   (_)   $$$$$$$$ 
                ($$$$$$$b       d$$$$$$$) 
                 q$$$$$$$$a._.a$$$$$$$$p 
                  q$$$$$$$$$$$$$$$$$$$p 
                   `$$$$$$$$$$$$$$$$$` 
                     `$$$$$$$$$$$$$` 
                       `~$$$$$$$~` ') 
 
		END 
    END
 
	    PRINT(' 
 
  

 
        '); 
    
    UPDATE dbo.AnimationControl SET ActionTakenDate = GETDATE() WHERE Id = @CurrentActionId;

END;
GO
 


/*
CTRL+T first to show Results as Text

Then highlight and execute the following:

EXEC dbo.USP_ShakeThe8Ball;
GO 7

-- OR
EXEC dbo.USP_ShakeThe8Ball;
GO
EXEC dbo.USP_ShakeThe8Ball;
GO
EXEC dbo.USP_ShakeThe8Ball;
GO
EXEC dbo.USP_ShakeThe8Ball;
GO
EXEC dbo.USP_ShakeThe8Ball;
GO
EXEC dbo.USP_ShakeThe8Ball;
GO
EXEC dbo.USP_ShakeThe8Ball;
GO

*/

Thanks for reading. You might also enjoy following me on Twitter.

Want to learn even more SQL?

Sign up for my newsletter to receive weekly SQL tips!

4 Ways To Define Lookup Values In A Query

Published on: 2018-06-05

Watch this week’s episode on YouTube.

Occasionally you may need to create a lookup table of values for a single query.  Building a permanent table of these values is a good option if you need to reuse the values frequently, but for one-off, ad-hoc queries you may want to hard-code the lookup table of values directly in your query.

We can hard-code these values using several different techniques.  Below are the techniques I’ve seen used before, going in order from my least to most favorite.

Table Variables

DECLARE @T TABLE 
(
    ColorName varchar(20), 
    HexCode char(7)
);

INSERT INTO @T VALUES 
        ('FireBrick','#B22222'),
        ('HotPink','#FF69B4'),
        ('Tomato','#FF6347'),
        ('PapayaWhip','#FFEFD5'),
        ('RebeccaPurple','#663399'),
        ('LawnGreen','#7CFC00'),
        ('MidnightBlue','#191970');

SELECT * FROM @T;

Table variables get a bad rap in SQL Server because they don’t provide accurate row estimates to the query optimizer.  This can cause SQL Server to come up with some really terrible execution plans that will kill your query’s performance.

However, if your use case is a single small lookup table of less than 100 records, table variables might actually be a viable option.  They are quick and easy to setup and can be added to the top of your query.

With that said, I don’t think I’ve ever used a table variable in this type of scenario (or any scenario really).  I know some people love using them and I think that’s fine as long as you are keeping track of your query performance.  For me though, there are so many better options available…

Temporary Tables

CREATE TABLE #T 
(
    ColorName varchar(20), 
    HexCode char(7)
);

INSERT INTO #T VALUES 
        ('FireBrick','#B22222'),
        ('HotPink','#FF69B4'),
        ('Tomato','#FF6347'),
        ('PapayaWhip','#FFEFD5'),
        ('RebeccaPurple','#663399'),
        ('LawnGreen','#7CFC00'),
        ('MidnightBlue','#191970');

SELECT * FROM #T;

Temp tables are the answer to many of the table variable’s shortcomings.

Temp tables can perform well with larger amounts of data because they can be indexed and can have statistics generated on them.  Both of these features typically help SQL Server generate better execution plans.

There is some overhead in coding a temp table though: just like a table variable, a temp table needs to be created and inserted into before being able to use it in your query.  While normally not a huge deal, this is not something I want to have to do in those instances where I want to define some lookup values quickly…

SELECT with UNION ALL

SELECT
    *
FROM
    (
        SELECT 'FireBrick','#B22222'     UNION ALL
        SELECT 'HotPink','#FF69B4'   UNION ALL
        SELECT 'Tomato','#FF6347'    UNION ALL
        SELECT 'PapayaWhip','#FFEFD5'    UNION ALL
        SELECT 'RebeccaPurple','#663399' UNION ALL
        SELECT 'LawnGreen','#7CFC00'     UNION ALL
        SELECT 'MidnightBlue','#191970'
    ) T(ColorName,HexCode);

The next option is hard-coding values in SELECT statements and then joining them together with UNION ALLs.

This is probably the most common technique I see, and for good reason: the syntax is straight forward and doesn’t require any special setup; perfect for the one-time use ad-hoc scenario.

Its format also makes it easy to use the ALT + highlight shortcut to quickly create a derived table lookup from the results of another query or values copied from elsewhere.

I do like this method a lot, but there is one method that I like slightly more…

The VALUES() Constructor

SELECT
    *
FROM
    (VALUES 
        ('FireBrick','#B22222'),
        ('HotPink','#FF69B4'),
        ('Tomato','#FF6347'),
        ('PapayaWhip','#FFEFD5'),
        ('RebeccaPurple','#663399'),
        ('LawnGreen','#7CFC00'),
        ('MidnightBlue','#191970')
        ) T(ColorName,HexCode);

You’ve probably use the VALUES constructor in an INSERT INTO statement, but did you know you can also use it in a FROM clause?

This syntax is similar to our SELECT + UNION ALL technique above, except we are swapping in single quotes and parentheses for SELECTs and UNION ALLs.  I find this slightly easier to write, if only because it requires typing out fewer characters and feels more programmatic.

One Last Tip: CTE

WITH HtmlColors AS (
        SELECT 'FireBrick' AS ColorName,'#B22222' AS HexCode     UNION ALL
        SELECT 'HotPink','#FF69B4'   UNION ALL
        SELECT 'Tomato','#FF6347'    UNION ALL
        SELECT 'PapayaWhip','#FFEFD5'    UNION ALL
        SELECT 'RebeccaPurple','#663399' UNION ALL
        SELECT 'LawnGreen','#7CFC00'     UNION ALL
        SELECT 'MidnightBlue','#191970'
)

SELECT * FROM HtmlColors

This isn’t really an additional technique, but something related that I use often and feels appropriate to mention.

If using either the SELECT + UNION ALL or VALUES techniques, you can put those lookup queries into a common table expression for easier referencing.

This doesn’t give any performance advantage, but it does help keep your code clean by putting your lookup logic right at the top of your file.  This becomes particularly useful when using those hard-coded lookup values as parameters, allowing all changes to your query to be made right at the top of your file during subsequent runs.

Thanks for reading. You might also enjoy following me on Twitter.

Want to learn even more SQL?

Sign up for my newsletter to receive weekly SQL tips!