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!

Temporary Staging Tables

Published on: 2019-05-07

Watch this week’s episode on YouTube.

SQL Server Spool operators are a mixed bag. On one hand, they can negatively impact performance when writing data to disk in tempdb. On the other hand, they allow filtered and transformed result sets to be temporarily staged, making it easier for that data to be reused again during that query execution.

The problem with the latter scenario is that SQL Server doesn’t always decide to use a spool; often it’s happy to re-read (and re-process) the same data repeatedly. When this happens, one option you have is to explicitly create your own temporary staging table that will help SQL Server cache data it needs to reuse.

This post is a continuation of my series to document ways of refactoring queries for improved performance. I’ll be using the StackOverflow 2014 data dump for these examples if you want to play along at home.

No spools

Let’s start by looking at the following query:

WITH January2010Badges AS ( 
	SELECT 
		UserId,
		Name,
		Date
	FROM 
		dbo.Badges 
	WHERE 
		Date >= '2010-01-01' 
		AND Date <= '2010-02-01' 
), Next10PopularQuestions AS ( 
	SELECT TOP 10 * FROM (SELECT UserId, Name, Date FROM January2010Badges WHERE Name = 'Popular Question' ORDER BY Date OFFSET 10 ROWS) t
), Next10NotableQuestions AS ( 
	SELECT TOP 10 * FROM (SELECT UserId, Name, Date FROM January2010Badges WHERE Name = 'Notable Question' ORDER BY Date OFFSET 10 ROWS) t
), Next10StellarQuestions AS ( 
	SELECT TOP 10 * FROM (SELECT UserId, Name, Date FROM January2010Badges WHERE Name = 'Stellar Question' ORDER BY Date OFFSET 10 ROWS) t
)
SELECT UserId, Name FROM Next10PopularQuestions 
UNION ALL 
SELECT UserId, Name FROM Next10NotableQuestions
UNION ALL 
SELECT UserId, Name FROM Next10StellarQuestions 

Note: This is not necessarily the most efficient way to write this query, but it makes for a good demo.

This query is returning offset results for different badges from one month of data in the dbo.Badges table. While the query is using a CTE to make the logic easy to understand (i.e. filter the data to just January 2010 results and then calculate our offsets based on those results), SQL Server isn’t actually saving the results of our January2010Badges expression in tempdb to get reused. If we view the execution plan, we’ll see it reading from our dbo.Badges clustered index three times:

Three clustered index scans
Table 'Badges'. Scan count 27, logical reads 151137, ...

That means every time SQL Server needs to run our offset logic in each “Next10…” expression, it needs to rescan the entire clustered index to first filter on the Date column and then the Name column. This results in about 150,000 logical reads.

Divide and Conquer

One potential solution would be to add a nonclustered index that would allow SQL Server to avoid scanning the entire clustered index three times. But since this series is about improving performance without adding permanent indexes (since sometimes you are stuck in scenarios where you can’t easily add or modify an index), we’ll look at mimicking a spool operation ourselves.

We’ll use a temporary table to stage our filtered January 2010 results so SQL Server doesn’t have to scan the clustered index each time it needs to perform logic on that subset of data. For years I’ve referred to this technique as “temporary staging tables” or “faking spools”, but at a recent SQL Saturday Jeff Moden told me he refers to it as “Divide and Conquer”. I think that’s a great name, so I’ll use it going forward. Thanks Jeff!

First let’s divide our query so that we insert our January 2010 data into its own temporary table:

DROP TABLE IF EXISTS #January2010Badges;
CREATE TABLE #January2010Badges
(
	UserId int,
	Name nvarchar(40),
	Date datetime
	CONSTRAINT PK_NameDateUserId PRIMARY KEY CLUSTERED (Name,Date,UserId)
);

INSERT INTO #January2010Badges
SELECT
	UserId,
	Name,
	Date
FROM 
	dbo.Badges
WHERE 
	Date >= '2010-01-01' 
	AND Date <= '2010-02-01'; 

You’ll notice I added a clustered primary key which will index the data in an order that will make filtering easier.

Next, we conquer by changing the rest of our query to read from our newly created temp table:

WITH Next10PopularQuestions AS ( 
	SELECT TOP 10 * FROM (SELECT UserId, Name, Date FROM #January2010Badges WHERE Name = 'Popular Question' ORDER BY Date OFFSET 10 ROWS) t
), Next10NotableQuestions AS ( 
	SELECT TOP 10 * FROM (SELECT UserId, Name, Date FROM #January2010Badges WHERE Name = 'Notable Question' ORDER BY Date OFFSET 10 ROWS) t
), Next10StellarQuestions AS ( 
	SELECT TOP 10 * FROM (SELECT UserId, Name, Date FROM #January2010Badges WHERE Name = 'Stellar Question' ORDER BY Date OFFSET 10 ROWS) t
)
SELECT UserId, Name FROM Next10PopularQuestions 
UNION ALL 
SELECT UserId, Name FROM Next10NotableQuestions 
UNION ALL 
SELECT UserId, Name FROM Next10StellarQuestions 

Running this all together, we get the following plans and logical read counts:

Clustered index seeks
Table 'Badges'. Scan count 9, logical reads 50379, ...

(42317 rows affected)

(20 rows affected)
Table '#January2010Badges______________________________00000000003B'. Scan count 3, logical reads 12, ...

In this version of the query, SQL Server scans the clustered index a single time and saves that data to a temporary table. In the subsequent SELECTs, it seeks from this much smaller temporary table instead of going back to the clustered index, reducing the total amount of reads to 50379 + 12 = 50392: about a third of what the original query was doing.

Temporary Staged Data

At the end of day, you can hope that SQL Server creates a spool to temporarily stage or data, or you can be explicit about it and do it yourself. Either option is going to increase usage on your tempdb database, but at least by defining the temporary table yourself you can customize and index it to achieve maximum reuse and performance for your queries.

It’s important to note that this is not a technique you want to abuse: writing and reading too much data from tempdb can cause contention problems that can make you worse off than having allowed SQL Server to scan your clustered index three times. However, when implemented sparingly and for good reasons, this technique can greatly improve the performance of certain queries.

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!

IN vs UNION ALL

Published on: 2019-04-30

Watch this week’s episode on YouTube.

When you need to filter query results on multiple values, you probably use an IN() statement or multiple predicates separated by ORs:

WHERE Col1 IN ('A','B','C')

or

WHERE Col1 = 'A' OR Col1 = 'B' OR Col1 = 'C'

While SQL Server will generate the same query plan for either syntax, there is another technique you can try that can sometimes can improve performance under certain conditions: UNION ALL.

This post is a continuation of my series to document ways of refactoring queries for improved performance. I’ll be using the StackOverflow 2014 data dump for these examples if you want to play along at home.

Lookups and Scans

Let’s say we have the following index on our dbo.Badges table:

CREATE NONCLUSTERED INDEX [IX_Badges] ON [dbo].[Badges] ([Name]) INCLUDE ([UserId]);

Next let’s run these two separate queries:

/* Query 1 */
SELECT 
    Name, UserId, Date 
FROM 
    dbo.Badges 
WHERE 
    Name = 'Benefactor'
OPTION(MAXDOP 1)

/* Query 2 */
SELECT 
    Name, UserId, Date 
FROM 
    dbo.Badges 
WHERE 
    Name = 'Research Assistant'
OPTION(MAXDOP 1)

Note I’m enforcing MAXDOP 1 here to remove any performance differences due to parallelism in these demos.

The nonclustered index doesn’t cover these queries – while SQL Server can seek the index for the Name predicate in the WHERE clause, it can’t retrieve all the columns in the SELECT from the index alone. This leaves SQL Server with a tough choice to make:

  1. Does it scan the whole clustered index to return all the required columns for the rows requested?
  2. Does it seek to the matching records in the nonclustered index and then perform a key lookup to retrieve the remaining data?

So, what does SQL Server decide to do?

Execution plans

For Query 1, SQL Server thinks that reading the entire clustered index and returning only the rows where Name = 'Benefactor' is the best option.

SQL Server takes a different approach for Query 2 however, using the non-covering nonclustered indexes to find the records with Name = 'Research Assistant' and then going to look up the Date values in the clustered index via a Key Lookup

The reason SQL server chooses these two different plans is because it thinks it will be faster to return smaller number of records with a Seek + Key Lookup approach (“Research Assistant”, 127 rows), but faster to return a larger number of records with a Scan (“Benefactor”, 17935 rows).

Kimberly Tripp has an excellent post that defines where this “tipping point” from a key lookup to a clustered index scan typically occurs, but the important thing to keep in mind for this post is that we can sometimes use SQL Server’s ability to switch between these two approaches to our advantage.

Combining Queries with IN

So, what plan does SQL Server generate when we combine our two queries into one?

SELECT 
    Name, UserId, Date 
FROM 
    dbo.Badges 
WHERE 
    Name IN ('Benefactor','Research Assistant')
OPTION(MAXDOP 1)
Key Lookup

Interestingly enough SQL Server decides to retrieve the requested rows from the nonclustered index and then go lookup the remaining Date column in the clustered index.

If we look at the page reads (SET STATISTICS IO ON;) we’ll see SQL Server had to read 85500 pages to return the data requested:

(18062 rows affected)
Table 'Badges'. Scan count 2, logical reads 85500, physical reads 20, read-ahead reads 33103, ...

Without correcting our index to include the Date column, is there some way we can achieve the same results with better performance?

UNION ALL

In this case it’s possible to rewrite our query logic to use UNION ALL instead of IN/ORs:

SELECT 
    Name,UserId,Date 
FROM 
    dbo.Badges 
WHERE 
    Name = 'Benefactor' 
UNION ALL
SELECT 
    Name,UserId,Date 
FROM 
    dbo.Badges 
WHERE 
    Name = 'Research Assistant'
OPTION(MAXDOP 1)
UNION ALL execution plan

We get the same exact results through a hybrid execution plan.

In this case, our plan mirrors what SQL Server did when running our original two queries separately:

  • The rows where Name = 'Benefactor' are returned by scanning the clustered index.
  • The nonclustered index is seeked with clustered index lookups for the Name = 'Research Assistant' records.

Looking at the IO statistics for this UNION ALL query:

(18062 rows affected)
Table 'Badges'. Scan count 2, logical reads 50120, physical reads 6, read-ahead reads 49649, ...

Even though this query reads the whole clustered index to get the Benefactor rows, the total number of logical reads is still smaller than the seek/key lookup pattern seen in the combined query with IN(). This UNION ALL version gives SQL Server the ability to build a hybrid execution plan, combining two different techniques to generate a plan with fewer overall reads.

IN or UNION ALL?

There’s no way to know for sure without trying each variation.

But if you have a slow performing query that is filtering on multiple values within a column, it might be worth trying to get SQL Server to use a different plan by rewriting the query.

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!