Do you need to quickly add a numbers table to your SSDT Database Project? Here’s how.

Add a new table object to your database project, like so:

CREATE TABLE [dbo].[Numbers]
(
	Number INT NOT NULL,
	CONSTRAINT PK_Numbers
		PRIMARY KEY CLUSTERED (Number)
		WITH (FILLFACTOR = 100, ALLOW_ROW_LOCKS = OFF, ALLOW_PAGE_LOCKS = OFF, DATA_COMPRESSION = ROW)
)

Note the details: FILLFACTOR = 100: This table will be effectively read-only, so we can make use of all the pages to minimize reads. ALLOW_ROW_LOCKS = OFF, ALLOW_PAGE_LOCKS = OFF: With a read-only table there is no need for the overhead of row and page level locking, so we can evade these altogether. Queries will then escalate to table locking without additional help. DATA_COMPRESSION = ROW: This helps a bit in minimizing the footprint of the table on disk. ROW level is used instead of PAGE due to all the values in a page being unique, so little benefit to be had from dictionary compression algorithms.

Create a SQLCMD variable in your project named $(NumbersTableRowCount), or something else of your choosing, and assign it the count of numbers you want your table to have. Be sensible with this, you can always double up the rows in your queries. I tend to use 1M in order to make spoofing data easier, but this will be overkill for most systems.

Add the code below to your Post-Deployment Script:

PRINT 'Populating The Numbers Table';
GO
DECLARE @Count INT = CAST('$(NumbersTableRowCount)' AS INT);
IF (SELECT COUNT(*) FROM [dbo].[Numbers])  @Count
BEGIN
	WITH Numbers AS
	(
		SELECT
			1 AS Number
		WHERE
			1 < @Count
		UNION ALL
		SELECT
			Number + 1 AS Number
		FROM
			Numbers
		WHERE
			Number < @Count
	)
	MERGE INTO [dbo].[Numbers] AS T
	USING Numbers AS S
	ON S.Number = T.Number
	WHEN NOT MATCHED BY TARGET THEN
		INSERT (Number)
		VALUES (Number)
	WHEN NOT MATCHED BY SOURCE THEN
		DELETE
	OPTION
		(MAXRECURSION 0)
	;
END
GO
ALTER INDEX PK_Numbers ON [dbo].[Numbers] REBUILD;
GO

Granted, the use of MERGE and a Recursive CTE there might be far from being the most efficient way to populate the table, but they do demonstrate two important principles in a database project.

• No dependencies (if possible). Using a CTE means we’re not depending on other tables that might or might not be there in the future (like the commonly used spt_values), so the code becomes more portable between different versions of SQL Server.
• Using a MERGE statement instead of an assortment of other statements abides by the principle of declarative programming. The Numbers CTE block defines “how” you want your table contents to look like, and then the MERGE merely enforces this against whatever is the current status of the database.

And you’re good to go! A Numbers Table is especially useful for running algorithms or spoofing data, like so:

WITH Source AS
(
	SELECT
		ROW_KEY = N.Number,
		ROW_NAME = 'Spoofed Row ' + CAST(N.Number AS VARCHAR(100))
	FROM
		dbo.Numbers AS N
	WHERE
		N.Number < 100000
)
MERGE INTO dbo.TARGET_TABLE AS T
USING Source AS S
	ON S.ROW_KEY = T.ROW_KEY
WHEN NOT MATCHED BY TARGET THEN
	INSERT (ROW_KEY, ROW_NAME)
	VALUES (ROW_KEY, ROW_NAME)
;