Create a Test Database in SQL Server

Post Date:

January 13, 2024

,

As a SQL Server DBA and blogger, creating a test database with data often needed for preparing tests and demos. You might also need this for testing a SQL Server feature or script before rolling out to Production.

The SQL scripts below in this post will help you make a new test database with data (inserting with loops) in the table that is utilising various Data Types.

Creating a Test Database in SQL Server

The initial step involves creating a new test database named “Jupiter.” The script specifies the file locations for the data and log files and sets their respective sizes. The database is structured with a primary filegroup and a designated log file group.

See the CREATE DATABASE MS Docs for more information on parameters available, such as auto-growth settings.

USE [master]
GO

-- (Simple Method) Create Test Database
-- This relies on default MSSQL configuration settings
CREATE DATABASE Jupiter;

-- Create a test database in SQL Server (with options, tailor to your needs) 
CREATE DATABASE Jupiter
ON PRIMARY
	(NAME = 'jupiter_Data',
	FILENAME = 'D:\mssql_data\jupiter_Data.mdf',
	SIZE = 800MB,
	MAXSIZE = UNLIMITED,
	FILEGROWTH = 200MB)
LOG ON
	(NAME = 'jupiter_Log',
	FILENAME = 'D:\mssql_data\jupiter_Log.ldf',
	SIZE = 200MB,
	MAXSIZE = UNLIMITED,
	FILEGROWTH = 50MB);
GO

Create SQL Table & Insert Test Data

Once the database is in place, we proceed to create a table named “JupiterHarvest” and infuse it with a substantial amount of test data. The script utilizes a loop to insert rows into the table, generating various types of data.

USE Jupiter;
GO
-- Create JupiterHarvest table if not exists
    CREATE TABLE JupiterHarvest (
        HarvestID INT PRIMARY KEY,
        CropName NVARCHAR(50),
        HarvestDate DATE,
        Yield INT,
        SoilType NVARCHAR(20),
        PesticideUsed BIT,
        MarketPrice DECIMAL(10, 2)
    );

-- Create table and insert test data to the Jupiter-themed database
DECLARE @rowCount INT = 0, @batchSize INT = 1000;

WHILE @rowCount < 100000
BEGIN
    INSERT INTO JupiterHarvest (HarvestID, CropName, HarvestDate, Yield, SoilType, PesticideUsed, MarketPrice)
    SELECT 
        TOP (@batchSize)
        @rowCount + ROW_NUMBER() OVER (ORDER BY NEWID()),
        'JupiterCrop ' + CAST(@rowCount + ROW_NUMBER() OVER (ORDER BY NEWID()) AS NVARCHAR(10)),
        DATEADD(DAY, CAST(RAND() * 365 AS INT), '2022-01-01'),
        CAST(RAND() * 100 AS INT),
        CASE WHEN RAND() > 0.5 THEN 'Gas-Rich' ELSE 'Stormy Clay' END,
        1 AS BIT,
        CAST(RAND() * 50 AS DECIMAL(10, 2));
    --FROM Cosmos.dbo.GalacticValues;

    SET @rowCount += @batchSize;
END;

SELECT * FROM JupiterHarvest;

Feel free to customize this script to align with your specific testing needs. You can adjust the batch size, the number of loops, or introduce more columns with different data types to ensure it meets your unique testing requirements.

With the SQL scripts provided in this post, you can effortlessly create a diverse test database for all your SQL Server experiments. Enjoy!

Peter Whyte

This website is managed by Peter Whyte, an experienced SQL Database Administrator (DBA) & Blogger, working remotely from home in Edinburgh, Scotland.

DBASco