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 go -- prevent 'Cannot drop database' error if exists(select name from sysdatabases where name='dbEmployees') begin alter database dbEmployees set single_user with rollback immediate drop database dbEmployees end go create database dbEmployees go
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 go create table tblEmployees ( [empID] int primary key, [lastName] varchar(80) not null, [firstName] varchar(30) null, [birthDate] datetime null ) go
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) go delete from tblEmployees where empID=111 go insert into tblEmployees values(111, 'Adams', 'Adam', '01/15/1985') go select * from tblEmployees go
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.