This is my post for Adam Machanic's T-SQL Tuesday #002. Thanks for reading! Feedback is appreciated.

Can you create a game in SQL Server?

A few years back at the Austin Game Developers Conference in Texas I got to meet and talk with the CEO and the lead database developer of the Icelandic company (CCP Games) that created EVE Online - a scifi MMORPG. Some nice and very sharp guys. They had been eCommerce web/database developers plus gamers. They looked at MMORPGs and said to themselves, "these are just huge/high transaction database applications - we can do this!" And that is what they did. They designed and built the database in SQL Server and wrote tons of stored procedures. They made it so you could "play" the game by calling the stored procedures. And then they got a few artists and a couple of programmers to build a beautiful front end for their database application <grin>. The rest is history - EVE Online exploded in popularity and the company became very successful. They proved you can write a game in SQL Server.

Now back to the original question: Can you create a game in SQL Server?

That is The Challenge: Write a game in SQL Server. Not just any game but an Unbeatable Tic-Tac-Toe Game in SQL Server using Transact-SQL.

I have done this in C, C++ and Visual Basic, but never in Transact-SQL. It should be possible! I am accepting this challenge too!

Starting out there is a problem with SQL Server in that the primary UI for manipulating the database and writing T-SQL (SSMS) don't really support the requirements for a computer game.

Following is a basic description (highly over-simplified) of the steps of a computer game:

Infinite Loop (aka Game Loop) that does following over and over and over:

• User Input: Polls for user input (keyboard, mouse, joystick). User moves, fires gun, buys sword, saves girl, etc.
• AI Actions: Makes "choices" using some kind of AI for non-player characters (NPCs) - movement, firing gun, etc.
• Resolve Game World: Determines results of player and AI actions (collisions, hits/misses, increase score, affect of "gravity", decrease health of player/NPCs, etc).
• Draw Game World: Computer redraws world and presents to player. Likely includes music/sounds.
  

SSMS and the SQL Server database engine support the required functionality of a computer game when examined individually but not when mashed together. SSMS doesn't support the polling/acceptance of user input when running a T-SQL script - so no Game Loop combined with the other 4 steps of the Game Loop in SSMS. There is a workaround if you want to "play" your game within SSMS - you as the user of SSMS can be responsible for the Game Loop and make the calls for User Input, AI Actions, Resolve Game World, and Draw Game World.

Let's get to the rules!

1. Follows standard Tic-Tac-Toe rules. X goes first.
2. Computer can either be Xs or Os. Set when you start new game.
3. Game continues until one of the following occurs:
• Player or computer wins.
• No more open slots and no winner - this is a tie.
• If you wish, you can short-circuit game and declare a tie if win is not possible. [Bonus points if you implement this.]
4. Computer must always win if it can.
5. Computer must tie if win not possible.
6. Computer must never lose.
7. Game "state" must be stored in the database (table, temporary table, TVP, variable, whatever).
8. Input validation, in particular cheating prevention, must be done in the T-SQL.
9. Determination of game board status must be down in database/T-SQL (that is, win state, tie state, game over state, etc.)
10. Determination of computer's next move must be done in database/T-SQL.

If you wish you can implement the Game Looping, User Polling, World Redraw functionality in Powershell or in a .NET application or something similar but the core of the game must be a T-SQL game.

Post your setup and sample gameplay code here or on your own blog and a link here. Would love to see some collaboration going on!

Personal Aside: As I said before, I am accepting this challenge too. Following are some quick and dirty ideas that I might test out. I am not sure if the following ideas are a reasonable start of a solution or a dead end (likely a dead end!). T-SQL doesn't support arrays. I am not sure how I will represent the Tic-Tac-Toe board with nine slots (3 X 3). Maybe as 9 separate variables each representing a different slot on the board or maybe a 3 column 3 rows temporary table? But I think maybe a series of stored procedures to "play" a game. Maybe something like the following (somewhat mapped to the steps of the Game Loop)?

• usp_StartGame(@ComputerPlays char(1), @GameID int OUTPUT) - This stored procedure starts a new game. The @ComputerPlays input is either 'X' or 'O' to set what the computer is playing. The OUTPUT parameter @GameID returns a new unique value representing the identifier for the game.
• usp_ShowStatus(@GameID int) - This shows current state of the @GameID game. Redraws board possibly as formatted ASCII. Shows who is playing Xs and Os. Shows who's turn it is. Shows if Win/Loss/Tie/Game Over.
• usp_PlayerTurn(@GameID int, @Position int) - Represents player move. @Position would represent one of the nine positions on the board. If the player attempted to cheat this would be caught here.
• usp_ComputerTurn(@GameID int) - Represents computer move. This represents the meat of the coding. How do you code this so that the computer never loses. Hmmm. There are several ways to do this - recursively, brute force, data-driven, others? After the choice is made, this procedure possibly calls usp_ShowStatus to display board back to player showing computer's choice and results.

Please let me know if I need to add or clarify anything. Please let me know if you have any ideas or other feedback. Thanks for reading! Looking forward to seeing people's solutions and ideas!