Creating a Simple SQL Server Database

Most of the programming I do is written in a procedural language like C# or Python. Whenever I have to work with Microsoft SQL Server, I always have to spend an hour or two reviewing SQL syntax to remind myself how that sometimes-annoying language works.

A very common SQL task for me is to create a simple SQL database for testing purposes. By that I mean I create a dummy database because I’m going to write C# code that connects to a real database, so I start by working with a simple DB because database connectivity is almost always a nightmare.


I create databases using a script, rather than using the SQL Server Management Studio GUI, so I have clear documentation of what I did, and so I can recreate my databases easily.

My generic SQL script usually starts with:

use master

-- prevent 'Cannot drop database' error
if exists(select name from sysdatabases where name='dbEmployees')
  alter database dbEmployees
  set single_user with rollback immediate
  drop database dbEmployees

create database dbEmployees

The set single_user statement disconnects any other processes that are attached to the database, so you wouldn’t want to do this in a production environment.

Next I typically create a simple table:

use dbEmployees

create table tblEmployees
  [empID] int primary key,
  [lastName] varchar(80) not null,
  [firstName] varchar(30) null,
  [birthDate] datetime null

It’s important to “use” the new database otherwise the new dummy table will be added into the Master database. Finally, I verify everything is working:

-- bad statement, need quotes on DOB
insert into tblEmployees
  values(111, 'Adams', 'Adam', 01/15/1985)

delete from tblEmployees where empID=111

insert into tblEmployees
  values(111, 'Adams', 'Adam', '01/15/1985')

select * from tblEmployees

After I write C# code that connects to the dummy database, I’ll drop the database and start working with my real database. Unlike when using the GUI to drop a database, the SQL drop statement actually deletes the associated .mdf data and .ldf log files.

This entry was posted in Machine Learning, Miscellaneous, Software Test Automation. Bookmark the permalink.