How to generate test data with SQL Server: step by step
Creating test datasets is essential to validate queries, perform load tests, test indexes and develop features without affecting real data. This guide shows how to generate test data in SQL Server in a practical and reproducible way, using set-based techniques and functions like RAND, NEWID and CHECKSUM to produce records with a realistic appearance. We explain the rationale for each technique and provide concrete examples.
Prerequisites
- Accessible SQL Server instance (local or remote).
- SQL Server Management Studio (SSMS) or another SQL tool.
- Permissions to create databases and tables (CREATE DATABASE, CREATE TABLE, INSERT).
Step 1: Create a database and a sample table
Start by isolating test data in a dedicated database to avoid impacts. The sample table mimics a common scenario: customers with name, email, creation date and active flag. We use IDENTITY to generate unique keys automatically and types like NVARCHAR and DATETIME2 for compatibility and precision.
CREATE DATABASE TestDataDB;
GO
USE TestDataDB;
GO
CREATE TABLE Customers (
CustomerID INT IDENTITY(1,1) PRIMARY KEY,
FirstName NVARCHAR(50),
LastName NVARCHAR(50),
Email NVARCHAR(100),
CreatedDate DATETIME2,
IsActive BIT
);
Why: having a simple table allows testing various queries (joins, date filters, aggregations). Identity makes referencing and deduplication easier afterwards.
Step 2: Generate names and emails using simple combinations
An efficient technique is to create small lists of first names and last names and use CROSS JOIN to get all combinations. With 5 first names and 5 last names we obtain 25 unique combinations — fast and deterministic. To make emails distinct we add a number generated by CHECKSUM(NEWID()) mod 100.
-- temporary tables with short lists
CREATE TABLE #FirstNames (Name NVARCHAR(50));
CREATE TABLE #LastNames (Name NVARCHAR(50));
INSERT INTO #FirstNames VALUES ('Ana'),('João'),('Miguel'),('Rita'),('Pedro');
INSERT INTO #LastNames VALUES ('Silva'),('Santos'),('Ferreira'),('Costa'),('Pereira');
-- use CROSS JOIN to create combinations and insert
INSERT INTO Customers (FirstName, LastName, Email, CreatedDate, IsActive)
SELECT f.Name, l.Name,
LOWER(f.Name + '.' + l.Name + CAST(ABS(CHECKSUM(NEWID()))%100 AS VARCHAR(3)) + '@example.com'),
DATEADD(day, -ABS(CHECKSUM(NEWID()))%365, GETDATE()),
CASE WHEN ABS(CHECKSUM(NEWID()))%2 = 0 THEN 1 ELSE 0 END
FROM #FirstNames f
CROSS JOIN #LastNames l;
Concrete example: with 5×5 = 25 rows generated, each Email will have a suffix 0–99 to reduce collisions. This approach avoids loops and is much faster for small/medium volumes.
Step 3: Generate many records (example 10000) with set-based techniques
To create thousands of records, use a numbers (tally) table. It's a set-based technique that avoids cursors and is scalable. In the example we create up to 10000 entries; by adjusting TOP() or the multiplication calculation of the lists you will get exactly the desired number.
-- create a temporary tally table with N numbers
WITH Tally AS (
SELECT TOP (10000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
)
INSERT INTO Customers (FirstName, LastName, Email, CreatedDate, IsActive)
SELECT
-- choose randomly among lists using modulo
fn.Name,
ln.Name,
LOWER(fn.Name + '.' + ln.Name + CAST(n%100 AS VARCHAR(3)) + '@example.com'),
DATEADD(second, -(ABS(CHECKSUM(NEWID())) % 31536000), GETDATE()), -- up to 1 year ago
CASE WHEN n%3 = 0 THEN 0 ELSE 1 END
FROM Tally t
CROSS JOIN (SELECT Name FROM #FirstNames) fn
CROSS JOIN (SELECT Name FROM #LastNames) ln
WHERE t.n <= 10000;
Practical note: depending on how many lists you cross, the multiplication can produce more rows than expected (for example 10000×5×5 = 250000). If you want exactly 10000 rows, use SELECT TOP (10000) ... or limit with WHERE/ROW_NUMBER(). This technique is fast on modern machines: inserting 100k–1M records can be done in seconds to minutes, depending on I/O.
Step 4: Introduce diversity with NEWID and RAND
RAND() returns the same value per batch in a SELECT without variable seed; therefore, for row-by-row variability prefer CHECKSUM(NEWID()) or NEWID() combined with functions. Use them to generate ages, balances and dispersed dates. Examples of distribution: Age between 18 and 77, Balance between 0 and 1000.
-- add Age and Balance column with pseudo-random values
ALTER TABLE Customers ADD Age INT NULL, Balance DECIMAL(10,2) NULL;
UPDATE Customers
SET Age = 18 + ABS(CHECKSUM(NEWID()))%60,
Balance = CAST((ABS(CHECKSUM(NEWID()))%100000) / 100.0 AS DECIMAL(10,2));
Explanation: ABS(CHECKSUM(NEWID()))%60 generates 0–59. By adding 18 we get the range 18–77. For Balance we use cents; %100000 produces 0–99999 which we then divide by 100.
Step 5: Remove duplicates and validate integrity
When generating large amounts of data it's common for duplicates to appear. Detect by email (or by the FirstName+LastName combination) and remove keeping the desired row, typically the one with the lowest CustomerID. Cleaning prevents errors in unique indexes and reports.
-- find duplicate emails
SELECT Email, COUNT(*) AS Qty
FROM Customers
GROUP BY Email
HAVING COUNT(*) > 1;
-- remove duplicates keeping the lowest CustomerID
;WITH Dups AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY Email ORDER BY CustomerID) AS rn
FROM Customers
)
DELETE FROM Dups WHERE rn > 1;
Validating integrity also includes checking ranges (dates in the past), percentage of IsActive (e.g.: ~66% active if you used n%3 above) and distribution of Age/Balance with COUNT/AVG/MIN/MAX.
Verify the result
Use simple queries to confirm volume, samples and statistics. These queries help validate whether the generated data follow the expected rules.
-- total count
SELECT COUNT(*) AS TotalCustomers FROM Customers;
-- sample of 10 random records
SELECT TOP (10) * FROM Customers ORDER BY NEWID();
-- basic statistics
SELECT MIN(CreatedDate) AS Oldest, MAX(CreatedDate) AS Newest, AVG(Age) AS AvgAge FROM Customers;
Conclusion
Generating test data in SQL Server using set-based techniques and functions like NEWID and CHECKSUM avoids loops and improves performance. Start with small lists and combine them, then scale with a tally table. Recommended next steps: turn these scripts into parameterizable stored procedures (number of records, percentage active), use temporary tables for isolation and version control the .sql files for reproducibility. With these patterns you can create realistic scenarios for development, performance testing and query validation.