In SQL for Smarties , Joe Celko provides an ANSI SQL definition for the Series table (elsewhere called Tally or Numbers). Defining it ensures that the values in the column are unique, positive, and adjacent from 1 to the maximum value:
CREATE TABLE Series ( seq INTEGER NOT NULL PRIMARY KEY, CONSTRAINT non_negative_nbr CHECK (seq > 0), CONSTRAINT numbers_are_complete CHECK ((SELECT COUNT(*) FROM Series) = (SELECT MAX(seq) FROM Series)) );
Uniqueness is ensured by the PRIMARY KEY declaration. Positivity is provided by the non_negative_nbr constraint. With these two constraints, adjacency is ensured by the numbers_are_complete constraint.
SQL Server does not support subqueries in control constraints. When I try to create a Series table, I get this error:
Msg 1046, Level 15, State 1, Line 4 Subqueries are not allowed in this context. Only scalar expressions are allowed. Msg 102, Level 15, State 1, Line 4 Incorrect syntax near ')'.
If I remove the unsupported numbers_are_complete restriction, I will leave this definition:
CREATE TABLE Series ( seq INTEGER NOT NULL PRIMARY KEY, CONSTRAINT non_negative_nbr CHECK (seq > 0) );
When I try to create this version of Series, it succeeds:
Command(s) completed successfully.
This version of Series is weaker because it does not provide the adjacency of numbers in the table.
To demonstrate this, I first need to fill out a table. I applied the method described by Itzik Ben-Gan in his article “ Virtual Auxiliary Number Table” to do this effectively for 65,536 rows:
WITH N0(_) AS (SELECT NULL UNION ALL SELECT NULL), N1(_) AS (SELECT NULL FROM N0 AS L CROSS JOIN N0 AS R), N2(_) AS (SELECT NULL FROM N1 AS L CROSS JOIN N1 AS R), N3(_) AS (SELECT NULL FROM N2 AS L CROSS JOIN N2 AS R), N4(_) AS (SELECT NULL FROM N3 AS L CROSS JOIN N3 AS R) INSERT INTO Series ( seq ) SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n FROM N4;
The query produces the output as follows:
(65536 row(s) affected)
Now I can choose from a table like this to create 65,536 rows:
SELECT seq FROM Series;
I trimmed the result set, but it looks like this:
seq 1 2 ... 65535 65536
Check it yourself, and you will see that each number in the interval [1, 65536] is in the result set. The row is adjacent.
But I can break the contact by deleting any line that is not the endpoint of the range:
DELETE FROM Series WHERE seq = 25788;
If the matching has been completed, this statement raises an error, but instead succeeds:
(1 row(s) affected)
It would be difficult for a person to find the missing value through visual inspection. They would have to suspect that value was lost in the first place before going on occasion. For these reasons, Series data spoofing is an easy way to introduce subtle errors into a SQL Server application based on Series table continuity.
Suppose a user writes a query that reads from Sequence to list strings from another source. After my intervention, this query will now produce incorrect results around a certain value - on the 25,788th line, everything is disabled by one.
You can write a query to detect missing values in the Series table, but how to limit the table so that missing values are impossible?