SQL Server Innovators Solve Business Problems with Style

SQL Server Calculates Video Poker Payouts After more than 30 years as a programmer, Joe Krynicky was ready to retire from his job, but not from solving programming challenges. He and some colleagues had an idea for a video poker game that incorporated a few twists on the arcade classic and formed a company, Kirei Technology, to develop and market the product. They found a company that would buy their idea if Kirei could program the math for the game: the poker-hand simulations needed to create pay tables. (A pay table is the amount that the game returns to a player for each winning hand.)

Joe talked to gaming-industry math experts, but none of them would take on the problem since the game had more cards (six instead of five) and winning hands than the traditional video poker game. “With [our game], it would take weeks of computer time for the evaluation of each pay table using the traditional simulation methods, because of the millions of possible combinations of hands,” Joe said.

Joe thought that SQL Server might offer a way to perform the needed calculations in a reasonable amount of time. Typically in video poker, the best way to play each hand is determined by calculating the expected value for each combination of cards kept and discarded from the dealt hand and selecting the best value for the player. “But we said, these are the winning hands; what are the different ways to get there? And we went through all the different strategies. For example, if you’re dealt a full house, you’re going to keep it. But if you’re dealt three cards in an inside straight, and a low pair in the same hand, what do you do?”

The solution that Joe devised uses about 200 SQL Server tables to contain the various hands and player strategies. “We had a SQL table containing a row for each possible hand of dealt cards, 27 tables of all the winning combinations in the game and how much was paid for each winning hand, and then we had about 100 tables containing rows for each hand of cards fitting specific starting card-playing strategies,” Joe says.

Although there are more than 120 million possible hands, they break down into certain similar groupings, and Joe used these as the basis for the tables. “For instance, we have a table containing all hands that contained two jacks or higher and a table containing all hands that contained three of five cards that could be used for a straight,” Joe says. “These tables were created once at the beginning of the process, so that much of the repetitive processing wouldn’t need to be performed multiple times later in the iterative portions of the processing.”

After the tables were created, the next task was to write the program that calculates the pay table. The potential game buyer wanted a 98 percent return— that is, the player would get 98 cents back on every dollar bet on the game. As Joe explains, his team wrote a two-step T-SQL stored procedure that calculates the return to a player by joining the table of all possible hands against tables containing all hands that match each hand-playing strategy, in descending order of percentage return (e.g., a dealt royal flush has the highest-percentage return for any hands), one strategy at a time. “For each strategy, the percentage return of the strategy is multiplied by the number of records returned from the join, and this amount is added to a running total of money won by the player. The records from the join with each strategy table are then removed from the remaining hands table, and the next highest strategy is joined to the remaining card table after the previously played strategies were removed. These steps are repeated until all possible hands have been evaluated and the table of remaining cards contains no rows. The running total of amount won is divided by the total number of hands evaluated, and the resulting amount is the percentage of money bet returned to the players. The percentage of money not returned to the players is the hold percentage for the machine manufacturer.”

Innovation is at the essence of the solution that Joe and his colleagues devised. They took a seemingly complex math-calculation problem and reformulated it into a series of set operations that can calculate a new pay table in about two hours—far more quickly than a simulation program that processes each of the millions of records individually multiple times. “This shows that the SQL language isn’t just useful for storing and analyzing data, but it can solve math problems as well,” Joe said. “Without this solution, we wouldn’t be able to sell our company’s video poker game.”

Streamlining Data Flow Among Applications
For all the automation time savings that software packages provide, ironically, IT may spend a great deal of time on keeping data for those applications updated. “Like many organizations, my agency had collected an overabundance of software packages that were put into action to streamline and consolidate our data flow, but as new programs were implemented, little thought was put into the integration of these systems,” says David Dye, a sergeant and DBA with the Cape Coral, Florida, police department. For example, IT staff for the department needed to manually maintain information for about 700 publicsafety– department employees in seven separate applications, including Active Directory (AD), a process that was prone to data entry error.

David’s involvement with IT for the police department started about five years ago, when he was assigned as the project lead to implement new computer-aided dispatch (CAD) and records management systems. Several years later, when David’s job duties had expanded to include SQL Server database reports and integration tasks, he and the network administrator, Matthew Vilord, were discussing the ongoing problem of having to manually update employee records in the various applications. “We decided we needed a starting point where [the information] could just trickle down [to all the applications]. AD seemed like the most logical place to start. We’ll go in and we’ll replace a user within AD, create a Windows account, then move down through the other applications.”

David’s first step in consolidating and automating application-data administration was to inventory the existing systems and data flow between them. Since most of the applications used SQL Server as their back-end database, David decided to use SQL Server as the basis of his solution. He created a chart of data-flow processes, beginning with adding a new user into AD, and charted the progression of adding, changing, or removing users throughout the agency and information that could be passed between systems, rather than entered manually. “After I created a comprehensive flow chart, I determined that it was feasible to create a setbased solution that used AD as the starting point and trickled the information down throughout our data infrastructure.”

Next, David identified additional data-flow functionality that the system needed. He opted to run a separate physical server—which he created from three computers seized from a drug dealer—running a separate instance of SQL Server. To get production-environment information onto the new server, David created a stored procedure that queried the maintenance plans of the production servers—which provided the most recent transaction-log backup—and restored that backup to the new server. “I owe much of the work [in creating the stored procedures for the solution] to Itzik Ben- Gan’s book, Inside Microsoft SQL Server 2005: T-SQL Querying,” David says. He then created a job that used this stored procedure, which ran hourly 20 minutes after the log backups. The new SQL Server instance now had databases that held information from the production environment, to be used for reporting and for centrally performing data-updating tasks across all the applications.

The existing system lacked a means for notifying users of certain transactions, such as the status of police reports. “Department personnel were required to log into numerous applications to find such notifications, if they even existed, David says.” To address this need, David wrote stored procedures that collected information from the backed-up transaction logs and put them in a temporary table. “Then, utilizing a cursor and the xp_smtpsendmail procedure, written by Gert Drapers, I [created a procedure that] emailed the notifications to employees, based on time limits denoted in IF BEGIN clauses. I placed these stored procedures into SQL jobs and scheduled them to run daily.”

Continue to next page