Using T-SQL with Magic Squares

Sharpen your T-SQL skills by filling a magic square

Editor's Note: Send your experts-only T-SQL tips to SQL Server MVP Itzik Ben-Gan at blackbelt @sqlmag.com. If we use your submission, you'll receive $100 and an exclusive T-SQL Black Belt shirt.

Puzzles attract both mathematicians and SQL programmers, even if the puzzle doesn't have a practical implementation in the real world. By learning how to use T-SQL scripts to populate magic squares, which are the source of many mathematical puzzles, you can sharpen your programming skills for more practical tasks.

A magic square is a square matrix—a matrix with the same number of rows and columns. The sum of the values in each row, column, and diagonal row is the same. Figure 1 is an example of a magic square. Notice that if you sum the values in each row, column, and diagonal, you always get 15. My father, who is a mathematician, showed me a simple method to fill an odd-sized magic square on paper. In this article, I show you how to use that method to fill a magic square's data in a table with T-SQL.

The rules to fill an odd-sized magic square are simple. First, write the first number in the middle cell of the first row. Second, write the next number in the cell that is one cell to the right and one cell above the current cell. If this cell is occupied, go to the cell beneath the current cell. If this cell lies outside the magic square, place the number all the way around the magic square. (Think of the square as a ball in which all of the edges of the rows, columns, and diagonals connect with one another.) Third, repeat the second step until the magic square is full. Figure 2 shows the steps involved in filling the magic square in Figure 1.

Representing a Magic Square in a Table

Now let's represent the magic square in a normalized table, in which each row in the table represents a cell. For each cell, you retain its row and column coordinates and its value. This table structure lets us represent a magic square of any size. Use the following T-SQL script to create the table:

CREATE TABLE MagicSquare(row int NOT NULL,col int NOT NULL,value int NOT NULL,CONSTRAINT PK_MagicSquare_row_col PRIMARY KEY(row, col),CONSTRAINT UNQ_MagicSquare_value UNIQUE(value))

Next, write a stored procedure that accepts the magic square's size as a parameter and fills the table with the magic square's data. First, you need to clear all existing rows in the table. Second, insert the first number in the middle cell of the first row. Last, insert the rest of the numbers in a WHILE loop. To make this puzzle a real challenge, let's limit the T-SQL script to a non-blocked WHILE loop with one INSERT statement. Listing 1, page 24, shows a stored procedure, FillMagicSquare, which populates the magic square.

Calculating the column number of the middle cell in the first row is easy. You simply divide the integer of @size by two and add one. The tough problem is calculating the coordinates of the other cells. This WHILE loop doesn't use a counter because we imposed the limit of one statement inside the loop. To overcome this limitation, the loop condition determines whether a row with a value of @size * @size exists in the table. This value is the largest number in the magic square, so if it exists in the table, you have inserted all rows.

To understand how the script calculates the destination cell coordinates, let's start with the result of the query marked as Input 2 in the comments in Listing 1. This query generates one row with the coordinates of the cell that is above and to the right of the last inserted cell. Join 1 performs a RIGHT OUTER JOIN between the base table (Input 1) and Input 2. A matching row exists in the base table only if the cell is already occupied in the magic square. For example, look at Step 2 in Figure 2. Join 1 will produce the result in Table 1 for Step 2 because the destination cell isn't occupied. In Step 4, the destination cell is occupied, as the result in Table 2 demonstrates.

Next, let's examine the result of the query marked as Input 3 in the comments in Listing 1. This query generates one row with the coordinates of the cell that is beneath the last inserted cell. Join 2 simply concatenates the row that Input 3 generates with the result of Join 1 by using a CROSS JOIN. Table 3 shows the result of Join 2 for Step 2; Table 4 shows the result of Join 2 for Step 4.

You can easily determine which coordinates of the destination cell are correct. If you have NULLs in the base table, you use the coordinates from O1, because this means that the above and right cell isn't occupied. If you don't have NULLs in the base table, this means that the cell is occupied, and you use the coordinates of the cell beneath, represented by O2.

To try this stored procedure, specify a table size, for example:

EXEC FillMagicSquare 3

Let's see whether the stored procedure populated the table with the correct values by running this query:

SELECT * FROM MagicSquare ORDER BY row, col

Table 5 shows the result of this query—a correctly populated magic square. These examples demonstrate T-SQL's power and flexibility—proof that you can use T-SQL to make magic.