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) ;