General – Dave + SQL Server = Ingenious SQL….

Data Sets with JSON (SQL 2016)

With the release of SQL Server 2016 JSON objects support is now available. With the ever growing popularity and versatility of JSON, including JSON support into SQL Server is good for both database and application developers. In a previous post we looked at how to output a JSON object in a dataset using the FOR XML PATH clause and some string concatenation. This process can be rather difficult to read and updating can be a challenge. In this blog post we are going to use the FOR JSON PATH clause to simplify the JSON building process.

A brief background regarding JSON (JavaScript Object Notation). JSON is a lightweight data interchange format similar to XML, however it is based on specific programing languages such as C and Java. It is a data format that is easily understandable for both humans and machines. JSON is machine and language independent, which makes it a great language for data interchange between different systems. For more information regarding JSON please see the following URL https://www.json.org/json-en.html..

In the previous post our goal was to output the student information with a JSON object that described the courses and grades the student received.

{
  "Grades": [{
    "CourseCode": "[Course Code]",
    "CourseName": "[Course Name]",
    "Grade": "[Course Grade]"
  }]
}

In the previous blog post we used some crazy string concatenation to build out the JSON Object. Here is that query, with the string concatenation and using the SUBSTRING function to clean up the excess commas and whitespace.

SELECT s.StudentID
	 , s.FirstName
	 , s.LastName
	 , GradesJSON = '{"Grades":[' + SUBSTRING((
			SELECT ', {"CourseCode":"'+ c.CourseCode + '", "CourseName":"' + c.CourseName + '", "Grade":"' + sc.Grade + '"}' 
			FROM #StudentCourse sc JOIN #Courses c ON c.CourseID = sc.CourseID
			WHERE s.StudentID = sc.StudentID
			FOR XML PATH('')),3,1000) + ']}'
FROM #Students s;

Using the FOR JSON PATH clause instead of FOR XML PATH clause will simplify our syntax and make our code easier to read and understand. We will still need to output the same columns and use the correlated sub query idea to map the student course/grade data to the proper student in the outer query. The names of the columns will become the data elements in JSON object.

SELECT s.StudentID
     , s.FirstName
     , s.LastName
     , GradesJSON = (
           SELECT CourseCode = c.CourseCode
                , CourseName = c.CourseName
                , Grade = sc.Grade
           FROM #StudentCourse sc
           JOIN #Courses c ON c.CourseID = sc.CourseID
           WHERE s.StudentID = sc.StudentID
           FOR JSON PATH
       )
FROM #Students s
WHERE StudentID = 10005;

StudentID	FirstName	LastName	Courses
10005	Will	Williams	[{“CourseCode”:”ICS140″, “CourseName”:”Introduction to Programming”, “Grade”:”A”}]

The only thing that is missing in the root grades element, so lets add that in.

SELECT s.StudentID
     , s.FirstName
     , s.LastName
     , GradesJSON = (
           SELECT CourseCode = c.CourseCode
                , CourseName = c.CourseName
                , Grade = sc.Grade
           FROM #StudentCourse sc
           JOIN #Courses c ON c.CourseID = sc.CourseID
           WHERE s.StudentID = sc.StudentID
           FOR JSON PATH, ROOT('Grades')
       )
FROM #Students s
WHERE StudentID = 10005;

StudentID	FirstName	LastName	Courses
10005	Will	Williams	{“Grades”:[{“CourseCode”:”ICS140″, “CourseName”:”Introduction to Programming”, “Grade”:”A”}]}

There we have it, the same dataset structure including the nested JSON objects without all of the crazy string concatenation and SUBSTRING Function usage. Here is the full script:

DROP TABLE IF EXISTS #Students
GO
 
DROP TABLE IF EXISTS #Courses
GO
 
DROP TABLE IF EXISTS #StudentCourse
GO
 
CREATE TABLE #Students (
   StudentID INT PRIMARY KEY NOT NULL
 , FirstName NVARCHAR(50) NOT NULL
 , LastName NVARCHAR(50) NOT NULL
 )
GO
 
CREATE TABLE #Courses (
	CourseID INT PRIMARY KEY NOT NULL
  , CourseCode NVARCHAR(50) NOT NULL
  , CourseName NVARCHAR(50) NOT NULL
)
 
CREATE TABLE #StudentCourse(
	StudentID INT NOT NULL
  , CourseID INT NOT NULL
  , Grade VARCHAR(5) NULL
)
 
INSERT   INTO #Students
         (StudentID, FirstName, LastName)
VALUES   (10001, N'Bob',N'Roberts')
	   , (10002, N'John',N'Johnson')
	   , (10003, N'Tom',N'Tompson')
	   , (10004, N'Rich',N'Richardson')
	   , (10005, N'Will',N'Williams');
GO
 
INSERT INTO #Courses (
    CourseID
  , CourseCode
  , CourseName
)
VALUES (140, N'ICS140', N'Introduction to Programming')
     , (225, N'ICS225', N'Web Programming')
     , (240, N'ICS240', N'Advanced Programming')
     , (310, N'ICS310', N'Database Management');
 
INSERT INTO #StudentCourse (StudentID, CourseID, Grade)
VALUES (10001, 140, 'A')
     , (10001, 225, 'B+')
     , (10001, 240, 'B-')
     , (10001, 310, 'B')
     , (10002, 240, 'C+')
     , (10002, 310, 'A-')
     , (10004, 140, 'A')
     , (10004, 225, 'B')
     , (10005, 140, 'A');
 
 
SELECT s.StudentID
     , s.FirstName
     , s.LastName
     , GradesJSON = (
           SELECT CourseCode = c.CourseCode
                , CourseName = c.CourseName
                , Grade = sc.Grade
           FROM #StudentCourse sc
           JOIN #Courses c ON c.CourseID = sc.CourseID
           WHERE s.StudentID = sc.StudentID
           FOR JSON PATH, ROOT('Grades')
       )
FROM #Students s;

Data Sets with JSON

Prior to SQL Server 2016, SQL Server did not provide many options to work with JSON documents. However, JSON has become a favored format for developers to pass data between applications and application layers. This blog post we will take a look at how to build a JSON document and return it in a result set. We are going to build on a previous post where we built a comma separated list of values and returned them in a data set. Here is the result of the previous blog post

StudentID	FirstName	LastName	Courses
10001	Bob	Roberts	ICS140, ICS225, ICS240, ICS310
10002	John	Johnson	ICS240, ICS310
10003	Tom	Tompson
10004	Rich	Richardson	ICS140, ICS225
10005	Will	Williams	ICS140

This time we are going to change the output to return a JSON object that will contain the course code, course name and grade the student received. The JSON document structure should look like this:

{
  "Grades": [{
    "CourseCode": "[Course Code]",
    "CourseName": "[Course Name]",
    "Grade": "[Course Grade]"
  }]
}

Before we dig into this blog a brief background regarding JSON (JavaScript Object Notation) may help. JSON is a lightweight data interchange format similar to XML, however it is based on programing languages constructs derived from languages such as C and Java. It is a data format that is easily understandable for both humans and machines, making it ideal for transferring data between applications. JSON is machine and language independent, which makes it a great language for data interchange between different systems. For more information regarding JSON please see the following URL https://www.json.org/json-en.html.

We have one minor tweak to the table structure from the previous blog post where we add the grade to the Student Course weak entity table

CREATE TABLE #StudentCourse(
    StudentID INT NOT NULL
  , CourseID INT NOT NULL
  , Grade VARCHAR(5) NULL
);
GO
 
INSERT INTO #StudentCourse (StudentID, CourseID, Grade)
VALUES (10001, 140, 'A')
     , (10001, 225, 'B+')
     , (10001, 240, 'B-')
     , (10001, 310, 'B')
     , (10002, 240, 'C+')
     , (10002, 310, 'A-')
     , (10004, 140, 'A')
     , (10004, 225, 'B')
     , (10005, 140, 'A');

What we want the script to return is a result set with student details including a JSON document with course and grade information which will look like this:

StudentID	FirstName	LastName	Courses
10001	Bob	Roberts	{“Grades”:[{“CourseCode”:”ICS140″, “CourseName”:”Introduction to Programming”, “Grade”:”A”}, {“CourseCode”:”ICS225″, “CourseName”:”Web Programming”, “Grade”:”B+”}, {“CourseCode”:”ICS240″, “CourseName”:”Advanced Programming”, “Grade”:”B-“}, {“CourseCode”:”ICS310″, “CourseName”:”Database Management”, “Grade”:”B”}]}
10002	John	Johnson	{“Grades”:[{“CourseCode”:”ICS240″, “CourseName”:”Advanced Programming”, “Grade”:”C+”}, {“CourseCode”:”ICS310″, “CourseName”:”Database Management”, “Grade”:”A-“}]}
10003	Tom	Tompson
10004	Rich	Richardson	{“Grades”:[{“CourseCode”:”ICS140″, “CourseName”:”Introduction to Programming”, “Grade”:”A”}, {“CourseCode”:”ICS225″, “CourseName”:”Web Programming”, “Grade”:”B”}]}
10005	Will	Williams	{“Grades”:[{“CourseCode”:”ICS140″, “CourseName”:”Introduction to Programming”, “Grade”:”A”}]}

We will reuse most of the query and simply add some additional string concatenation. The previous sub query just concatenated a comma to the course code, this time we are going to build repeating JSON objects within the array.

{"CourseCode": "[Course Code]","CourseName": "[Course Name]","Grade": "[Course Grade]"}

-- CSV Version
SELECT ', ' + sc.Grade 
FROM #StudentCourse sc JOIN #Courses c ON c.CourseID = sc.CourseID
WHERE s.StudentID = sc.StudentID
FOR XML PATH('')
 
-- New JSON Version
SELECT ', {"CourseCode":"'+ c.CourseCode + '", "CourseName":"' + c.CourseName + '", "Grade":"' + sc.Grade + '"}' 
FROM #StudentCourse sc JOIN #Courses c ON c.CourseID = sc.CourseID
WHERE s.StudentID = sc.StudentID
FOR XML PATH('')

Now that we have the repeating JSON objects within the array, we need to build out the top object in the document and the array structure. We can accomplish this by adding additional string concatenation in the outer portion of the query.

SELECT s.StudentID
	 , s.FirstName
	 , s.LastName
	 , GradesJSON = '{"Grades":[' + SUBSTRING((
			SELECT ', {"CourseCode":"'+ c.CourseCode + '", "CourseName":"' + c.CourseName + '", "Grade":"' + sc.Grade + '"}' 
			FROM #StudentCourse sc JOIN #Courses c ON c.CourseID = sc.CourseID
			WHERE s.StudentID = sc.StudentID
			FOR XML PATH('')),3,1000) + ']}'
FROM #Students s;

Here is the full script:

DROP TABLE IF EXISTS #Students
GO
 
DROP TABLE IF EXISTS #Courses
GO
 
DROP TABLE IF EXISTS #StudentCourse
GO
 
CREATE TABLE #Students (
   StudentID INT PRIMARY KEY NOT NULL
 , FirstName NVARCHAR(50) NOT NULL
 , LastName NVARCHAR(50) NOT NULL
 )
GO
 
CREATE TABLE #Courses (
	CourseID INT PRIMARY KEY NOT NULL
  , CourseCode NVARCHAR(50) NOT NULL
  , CourseName NVARCHAR(50) NOT NULL
)
 
CREATE TABLE #StudentCourse(
	StudentID INT NOT NULL
  , CourseID INT NOT NULL
  , Grade VARCHAR(5) NULL
)
 
INSERT   INTO #Students
         (StudentID, FirstName, LastName)
VALUES   (10001, N'Bob',N'Roberts')
	   , (10002, N'John',N'Johnson')
	   , (10003, N'Tom',N'Tompson')
	   , (10004, N'Rich',N'Richardson')
	   , (10005, N'Will',N'Williams');
GO
 
INSERT INTO #Courses (
    CourseID
  , CourseCode
  , CourseName
)
VALUES (140, N'ICS140', N'Introduction to Programming')
     , (225, N'ICS225', N'Web Programming')
     , (240, N'ICS240', N'Advanced Programming')
     , (310, N'ICS310', N'Database Management');
 
INSERT INTO #StudentCourse (StudentID, CourseID, Grade)
VALUES (10001, 140, 'A')
     , (10001, 225, 'B+')
     , (10001, 240, 'B-')
     , (10001, 310, 'B')
     , (10002, 240, 'C+')
     , (10002, 310, 'A-')
     , (10004, 140, 'A')
     , (10004, 225, 'B')
     , (10005, 140, 'A');
 
 
SELECT s.StudentID
	 , s.FirstName
	 , s.LastName
	 , GradesJSON = '{"Grades":[' + SUBSTRING((
			SELECT ', {"CourseCode":"'+ c.CourseCode + '", "CourseName":"' + c.CourseName + '", "Grade":"' + sc.Grade + '"}' 
			FROM #StudentCourse sc JOIN #Courses c ON c.CourseID = sc.CourseID
			WHERE s.StudentID = sc.StudentID
			FOR XML PATH('')),3,1000) + ']}'
FROM #Students s;

In a future post I will show how to build this Data Set using JSON functions in SQL 2016.

Comma Separated Values using the FOR XML PATH clause

In a previous post we looked at how we can build out a delimited list using the ISNULL and COALESCE functions. This functionality is great if you need to return the delimited list through a function or persist into a variable, however what if you need to return a comma separated list in a result set? This blog post will look at, and describe, how to build out a delimited or comma separated list using FOR XML PATH clause. Using tricks with the FOR XML PATH clause, a correlated subqueries, and un-named columns will give us all the tools needed to return a delimited list within a result set.

So the idea for this blog post is that we take a list of student’s details and include a comma separated list of the course codes that they have registered for. Here is a data diagram that represents our sample dataset.

CSV FOR XML PATH

Here are the populated tables.

StudentID	FirstName	LastName
10001	Bob	Roberts
10002	John	Johnson
10003	Tom	Thompson
10004	Rich	Richardson
10005	Will	Williams

310ICS310Database Management

CourseID	CourseCode	CourseName
140	ICS140	Introduction to Programming
225	ICS225	Web Programming
240	ICS240	Advanced Programming

StudentID	CourseID
10001	140
10001	225
10001	240
10001	310
10002	240
10002	310
10004	140
10004	225
10005	140

What we would expect the script to return is a result set with student details including a comma separated list of courses that looks like this:

StudentID	FirstName	LastName	Courses
10001	Bob	Roberts	ICS140, ICS225, ICS240, ICS310
10002	John	Johnson	ICS240, ICS310
10003	Tom	Tompson
10004	Rich	Richardson	ICS140, ICS225
10005	Will	Williams	ICS140

Using the following temp table structure and sampling of student and course data gives us the data that we will need for this example.

DROP TABLE IF EXISTS #Students
GO
DROP TABLE IF EXISTS #Courses
GO
DROP TABLE IF EXISTS #StudentCourse
GO
 
CREATE TABLE #Students (
   StudentID INT PRIMARY KEY NOT NULL
 , FirstName NVARCHAR(50) NOT NULL
 , LastName NVARCHAR(50) NOT NULL
 )
GO
 
CREATE TABLE #Courses (
    CourseID INT PRIMARY KEY NOT NULL
  , CourseCode NVARCHAR(50) NOT NULL
  , CourseName NVARCHAR(50) NOT NULL
)
 
CREATE TABLE #StudentCourse(
    StudentID INT NOT NULL
  , CourseID INT NOT NULL
)
 
INSERT   INTO #Students (StudentID, FirstName, LastName)
VALUES   (10001, N'Bob',N'Roberts')
       , (10002, N'John',N'Johnson')
       , (10003, N'Tom',N'Tompson')
       , (10004, N'Rich',N'Richardson')
       , (10005, N'Will',N'Williams');
GO
 
INSERT INTO #Courses (
    CourseID
  , CourseCode
  , CourseName
)
VALUES (140, N'ICS140', N'Introduction to Programming')
     , (225, N'ICS225', N'Web Programming')
     , (240, N'ICS240', N'Advanced Programming')
     , (310, N'ICS310', N'Database Management');
 
INSERT INTO #StudentCourse (StudentID, CourseID)
VALUES (10001, 140)
     , (10001, 225)
     , (10001, 240)
     , (10001, 310)
     , (10002, 240)
     , (10002, 310)
     , (10004, 140)
     , (10004, 225)
     , (10005, 140);

We are going to start out by creating a simple query that returns all of the data we need, to generate the output. This will include all data from the student table, all data from the student course weak entity table, and only course code data from the course table.

SELECT s.StudentID, s.FirstName, s.LastName, c.CourseCode
FROM #Students s
LEFT JOIN #StudentCourse sc ON sc.StudentID = s.StudentID
LEFT JOIN #Courses c ON c.CourseID = sc.CourseID

StudentID	FirstName	LastName	CourseCode
10001	Bob	Roberts	ICS140
10001	Bob	Roberts	ICS225
10001	Bob	Roberts	ICS240
10001	Bob	Roberts	ICS310
10002	John	Johnson	ICS240
10002	John	Johnson	ICS310
10003	Tom	Tompson
10004	Rich	Richardson	ICS140
10004	Rich	Richardson	ICS225
10005	Will	Williams	ICS140

Now that we have our dataset, let’s turn our focus on creating the comma separated list of course codes. Here we will implement our firsts bit of trickery. We are going to use the FOR XML clause, however we are going to pass an empty string to the PATH function. This will rename the XML root node as an empty string, thus not returning a root node. You can test this by running both queries, the first query will have a root node of <row> whereas the second query only returns the <CourseCode> nodes.

SELECT c.CourseCode
FROM #Students s
LEFT JOIN #StudentCourse sc ON sc.StudentID = s.StudentID
LEFT JOIN #Courses c ON c.CourseID = sc.CourseID
FOR XML PATH;
 
SELECT c.CourseCode
FROM #Students s
LEFT JOIN #StudentCourse sc ON sc.StudentID = s.StudentID
LEFT JOIN #Courses c ON c.CourseID = sc.CourseID
FOR XML PATH('');

Being that we were able to remove the root node by simply renaming the node, we need to do the same for the column name CourseCode. Here comes some more trickery, if we concatenate a string and do not provide an alias, the column name is undefined and thus is empty. In Management Studio you should see something like this “(No column name)”. This trickery actually works out to have an advantage because we need to concatenate our strings with a comma to create a comma separated list of values. So running the following query will give us all of the values in a comma separated list.

SELECT ', ' + c.CourseCode
FROM #Students s
LEFT JOIN #StudentCourse sc ON sc.StudentID = s.StudentID
LEFT JOIN #Courses c ON c.CourseID = sc.CourseID
FOR XML PATH('');

, ICS140, ICS225, ICS240, ICS310, ICS240, ICS310, ICS140, ICS225, ICS140

So now we have all of the course codes in one big comma separated list, we need to get them into sub lists for each student. If we nest the query with the FOR XML clause in a sub query and reference the student id from the outer query, this will limit the comma separated lists to only the course codes that are linked to the students.

SELECT s.StudentID
	 , s.FirstName
	 , s.LastName
	 , Courses = (
			SELECT ', ' + c.CourseCode
			FROM #StudentCourse sc JOIN #Courses c ON c.CourseID = sc.CourseID
			WHERE s.StudentID = sc.StudentID
			FOR XML PATH(''))
FROM #Students s;

The last piece is a bit of data clean up, using a STUFF or SUBSTRING we can remove the extra comma and white space from the comma separated list. Here is the final query that will return the result with a comma separated list in the result set.

SELECT s.StudentID
	 , s.FirstName
	 , s.LastName
	 , Courses = SUBSTRING((
			SELECT ', ' + c.CourseCode
			FROM #StudentCourse sc JOIN #Courses c ON c.CourseID = sc.CourseID
			WHERE s.StudentID = sc.StudentID
			FOR XML PATH('')),3,1000)
FROM #Students s;

Comma Separated Values with NULL Functions

Comma Separated Values, or values that are delimited by a specific character, are an easy way to pass a list of values from a function or in a dataset. There are a couple ways to create comma separated values within SQL Server, as a single set which could be useful in a scalar function, or in a dataset which could allow passing additional values in a flat data set. This blog post is going to focus on building a scalar delimited list using a NULL value function such as ISNULL and COALESCE. There is a misconception that COALESCE has a mystical power which is needed to build a delimited list, we will take a look at this mystical power and show that the NULL value functions are technically not needed.

Before we dive into building the delimited lists, a quick review of the NULL value functions will be helpful. Both the ISNULL and COALESCE statements can be used to replace NULL values with a different specified value. However the COALSCE statement can evaluate multiple NULL value expressions checks whereas the ISNULL statement has a single evaluation and replacement. Here are a couple links with additional details regarding the COALSCE and ISNULL functions.

So the idea for this blog post is that we take a list of first and last names and return a concatenated, comma separated list. Here is our sample dataset.

FirstName	LastName
Bob	Roberts
John	Johnson
Tom	Thompson
Rich	Richardson
Will	Williams

What we would expect the script to return is a comma separated list of values that looks like this:

Bob Roberts, John Johnson, Tom Thompson, Rich Richardson, Will Williams

Using the following temp table structure and sampling of first and last names gives us the data that we will need for this example.

DROP TABLE IF EXISTS #Person
GO
 
CREATE TABLE #Person (
   FirstName NVARCHAR(50)
 , LastName NVARCHAR(50))
GO
 
INSERT   INTO #Person
         (FirstName, LastName)
VALUES   (N'Bob',N'Roberts')
	   , (N'John',N'Johnson')
	   , (N'Tom',N'Tompson')
	   , (N'Rich',N'Richardson')
	   , (N'Will',N'Williams');
GO

The first step is to build out the string that we will put between the delimited values, in this case we will concatenate the first and last names. Instead of using the addition operator, we could have used the CONCAT function.

SELECT  p.FirstName + ' ' + p.LastName
FROM    #Person p;

(No column name)
Bob Roberts
John Johnson
Tom Thompson
Rich ichardson
Will Williams

As you can see from the result set, we have all of the names concatenated. Now we need to build all of the values into a single result. We can take and store all of the values into a variable, then return the result from the variable

DECLARE @FLNameCSV NVARCHAR(500);
 
SELECT  @FLNameCSV = p.FirstName + ' ' + p.LastName
FROM    #Person p;
 
SELECT  @FLNameCSV;

(No column name)
Will Williams

The interesting fact here is that only the last value was persisted in the variable. In truth each of the values that were returned in the result set were written to the variable, but each value was overwritten by the next value. We will use this concept by concatenating the variable value with the first and last name values.

DECLARE @FLNameCSV NVARCHAR(500);
 
SELECT  @FLNameCSV = @FLNameCSV + ', ' + p.FirstName + ' ' + p.LastName
FROM    #Person p;
 
SELECT  @FLNameCSV;

(No column name)
NULL

A NULL value was returned??? This is because the variable value was never initialized, therefore was set to NULL. When you concatenate a NULL value with any value you will end up with NULL. This is where the COALESCE function comes into play. If we use the COALSCE function to check for the NULL value and properly handle the NULL, we can properly concatenate our values and store them into the variable

DECLARE @FLNameCSV NVARCHAR(500);
 
SELECT  @FLNameCSV = COALESCE(@FLNameCSV + ', ', '') + p.FirstName + ' ' + p.LastName
FROM    #Person p;
 
SELECT  @FLNameCSV;

(No column name)
Bob Roberts, John Johnson, Tom Thompson, Rich Richardson, Will Williams

So as we can see, the magic is not within the COALSCE function it is actually the variable concatenation that is the magic behind building the list of values. Further proof??? Let’s remove the COALSCE and assign a default value to our variable.

DECLARE @FLNameCSV NVARCHAR(500) = '';
 
SELECT  @FLNameCSV = @FLNameCSV + ', ' + p.FirstName + ' ' + p.LastName
FROM    #Person p;
 
SELECT  @FLNameCSV;

(No column name)
, Bob Roberts, John Johnson, Tom Thompson, Rich Richardson, Will Williams

We have our delimited list with an extra comma in the front, we can simply use function like SUBSTRING to remove the first few characters.

DECLARE @FLNameCSV NVARCHAR(500) = '';
 
SELECT  @FLNameCSV = @FLNameCSV + ', ' + p.FirstName + ' ' + p.LastName
FROM    #Person p;
 
SELECT  SUBSTRING(@FLNameCSV, 3, LEN(@FLNameCSV));

(No column name)
Bob Roberts, John Johnson, Tom Thompson, Rich Richardson, Will Williams

There we have it, the truth behind comma separated lists and the COALSCE function. In a future blog post, we will take a look at building comma separated lists using the FOR XML clause.

Winner Winner Chicken Dinner

“Winner Winner Chicken Dinner”, and “Vegas, Vegas, Vegas” are quotes from two of my favorite Las Vegas movies. It’s no secret that my wife Kristin and I love Las Vegas, it’s also no secret that I love coming up with ingenious database solutions to random problems. So I was thinking, what it would take to create a simple 3 wheel slot machine in SQL Server. Is SQL Server the right place to put slot machine business logic? Ummmm, probably not, but that has never stopped me before. In this blog post I am going to build a 3 wheel slot simulation using small data set that represents the possible combinations that could appear and a random selection process to determine a pull outcome. I will also add a small table to track the results of each pull including a running balance in a players table.

Slot machine details

A standard 3 wheel slot with 20 stopping position yields 8000 possible result combinations (20³ = 8000). If there is only one jackpot symbol on each of the wheels, this means there is a 1 in 8000 chance of hitting the jackpot. If there are 5 empty spaces per wheel, there are 125 possibilities to hit all empty spaces. The simple math is multiply the count of a specific symbol on each wheel. Example, if there are five single bars on wheel number one, two 7’s on wheel number two, and one Wild on wheel three, thus 5 X 2 X 1 hence there are 10 opportunities for this (B-7-W) combination to appear. The total number of distinct combinations can be found by multiplying the count of different possible symbols per wheel. We have 6 different symbols on each wheel, thus 6 X 6 X 6 = 216 possibilities.

Slot machine data set

Armed with the combination logic we can build out a dataset for all 8000 possible combinations. Using an opportunity field, we can store the number of opportunities that each specific combination can appear. See the combinations table for full data set and opportunity count. Combining the opportunity field with a numbers (virtual-auxiliary-table-numbers) table, thanks @ItzikBenGan, we can build out all 8000 combinations. Once we have all 8000 possible combinations we can randomly select a single combination.

Slot machine SQL logic

For this simulation we will need a few tables to store the combinations, the results log for our players, and a table for our players. The combinations table will store symbol for each column, a winning amount for the combination, and the number of opportunities the combination will appear. The results log will store the resulting combination, the winning amount, the player, and a timestamp the slot machine was played. The players table will store the players name and the available funds for playing the slot machine.

Table definitions

Combinations Table

CREATE TABLE Combinations (
  CombinationID INT PRIMARY KEY IDENTITY(1,1) NOT NULL
, Reel1 VARCHAR(5) NOT NULL
, Reel2 VARCHAR(5) NOT NULL
, Reel3 VARCHAR(5) NOT NULL
, WinningAmount INT NOT NULL
, Opportunities INT NOT NULL
) ON  [PRIMARY];

ResultsLog Table

CREATE TABLE ResultsLog (
ResultsLogID INT PRIMARY KEY IDENTITY(1,1) NOT NULL
, PlayerID INT NOT NULL 
, Reel1 VARCHAR(5) NOT NULL
, Reel2 VARCHAR(5) NOT NULL
, Reel3 VARCHAR(5) NOT NULL
, WinningAmount INT NOT NULL
, ResultDate DATETIME NOT NULL
);

Player Table

CREATE TABLE Player (
PlayerID INT PRIMARY KEY IDENTITY(1,1) NOT NULL, 
, Name VARCHAR(100) NOT NULL
, AvailableFunds INT NOT NULL
);

Building the Combinations Dataset

Using a CROSS APPLY between the combinations table data and Itzik Ben-Gan’s numbers common table expression will yield 8000 total possible combinations.

WITH L0 AS (SELECT 1 AS C UNION ALL SELECT 1 AS O) , -- 2 rows
     L1 AS (SELECT 1 AS C FROM L0 AS A CROSS JOIN L0 AS B), --4 rows
     L2 AS (SELECT 1 AS C FROM L1 AS A CROSS JOIN L1 AS B),-- 16 rows
     L3 AS (SELECT 1 AS C FROM L2 AS A CROSS JOIN L2 AS B), -- 256 rows
     Nums AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS Number FROM L3)
SELECT  Reel1
        , Reel2
        , Reel3
        , WinningAmount
FROM    Combinations cbn
CROSS APPLY Nums nms
WHERE   nms.Number <= cbn.Opportunities;

Selecting a winning combination

Using TSQL’s random function we can select a random winning combination. The RAND function takes a seed value to start the randomization process, if the same seed is provided, the same random value will be returned. So we will use the NEWID and CHECKSUM functions to gather a random seed value. To get a value between 0 – 7999, we can multiply the RAND function result with 8000 and cast as an integer. Adding one gives us a random value between 1 and 8000.

SELECT CAST(RAND(CHECKSUM(NEWID())) * 8000 AS INT) + 1

Adding a bit of SQL ingenuity we can order the combinations dataset by the random value function. This will completely randomize the combinations dataset. All that’s left to do is select a single record, say the first record.

WITH L0 AS (SELECT 1 AS C UNION ALL SELECT 1 AS O) , -- 2 rows
     L1 AS (SELECT 1 AS C FROM L0 AS A CROSS JOIN L0 AS B), --4 rows
     L2 AS (SELECT 1 AS C FROM L1 AS A CROSS JOIN L1 AS B),-- 16 rows
     L3 AS (SELECT 1 AS C FROM L2 AS A CROSS JOIN L2 AS B), -- 256 rows
     Nums AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS Number FROM L3)
SELECT TOP 1
        Reel1
      , Reel2
      , Reel3
      , WinningAmount
FROM    Combinations cbn
CROSS APPLY Nums nms
WHERE   nms.Number <= cbn.Opportunities
ORDER BY CAST(RAND(CHECKSUM(NEWID())) * 8000 AS INT) + 1;

Time to play the slot

Running the query 5 times yielded the following results.

Reel1	Reel2	Reel3
_	BB	B
BBB	7	BB
B	B	_
B	W	_
7	BBB	_

Hmmmm… I didn’t win a single time… I think I successfully created a slot machine.

Log the results

All that’s left is to save the results of each spin and update the players available funds. A simple insert into the Results log, including the player, reels combination, winning amount and time-stamp will handle the logging side.

DECLARE @Reel1 NVARCHAR(5);
DECLARE @Reel2 NVARCHAR(5);
DECLARE @Reel3 NVARCHAR(5);
DECLARE @WinningAmount INT;
 
WITH L0 AS (SELECT 1 AS C UNION ALL SELECT 1 AS O) , -- 2 rows
     L1 AS (SELECT 1 AS C FROM L0 AS A CROSS JOIN L0 AS B), --4 rows
     L2 AS (SELECT 1 AS C FROM L1 AS A CROSS JOIN L1 AS B),-- 16 rows
     L3 AS (SELECT 1 AS C FROM L2 AS A CROSS JOIN L2 AS B), -- 256 rows
     Nums AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS Number FROM L3)
SELECT TOP 1
        @Reel1 = Reel1
      , @Reel1 = Reel2
      , @Reel1 = Reel3
      , @WinningAmount = WinningAmount
FROM    Combinations cbn
CROSS APPLY Nums nms
WHERE   nms.Number <= cbn.Opportunities
ORDER BY CAST(RAND(CHECKSUM(NEWID())) * 8000 AS INT) + 1;
 
INSERT INTO dbo.ResultsLog (PlayerID, Reel1, Reel2, Reel3, WinningAmount, ResultDate)
VALUES  (
  	 529
	,@Reel1
	,@Reel2
	,@Reel3
	,@WinningAmount
	,GETDATE()
);

A simple update will handle the players available funds.

UPDATE  Player
SET     AvailableFunds = AvailableFunds + @WinningAmount - 1
WHERE   PlayerID = 529;

Summary

Obviously this is a simplified implementation of a 3 reel slot machine but it meets the statistical opportunities for all combinations based on the number of symbols per reel. So using some ingenious ideas, a numbers table from Itzik Ben-Gan, and standard T-SQL functionality, we have a functional SQL slot machine that logs its results and keeps players hard earned virtual money. All that’s left to do is book a trip to Las Vegas…

DONE! combacksoon

Dave + SQL Server = Ingenious SQL….

SQL Thoughts from @IngeniousSQL

Category: General

Data Sets with JSON (SQL 2016)

Data Sets with JSON

Comma Separated Values using the FOR XML PATH clause

Comma Separated Values with NULL Functions

Winner Winner Chicken Dinner