Over the years, I found that having a small sample database is very helpful to test out my queries. Most of the times, the real database is too big and complex to use it. And, it is not easy to duplicate it. Here is my sample database.

Database design

Create database

-- DROP DATABASE Test;
CREATE DATABASE Test;

Create tables

USE Test;
CREATE TABLE Emails(
        Contact_id INT NOT NULL,
        Email NCHAR(50) NOT NULL,
        Label NCHAR(50) NOT NULL,
        CONSTRAINT PK_Emails PRIMARY KEY (Contact_id, Email)
    );
 
CREATE TABLE Contacts(
        id INT IDENTITY(2,1) PRIMARY KEY, -- Auto increment by 1 starting from 2.
        Firstname NCHAR(50) NOT NULL, 
        Lastname NCHAR(50) NOT NULL
    );

Add foreign keys

ALTER TABLE Emails WITH CHECK ADD CONSTRAINT FK_Emails_Contacts 
    FOREIGN KEY(Contact_id) REFERENCES Contacts (id);

Insert data

There is a foreign key between Contacts and Emails tables. In order to add an email, a contact must exists.

USE Test;
INSERT INTO Contacts(Firstname, Lastname) VALUES('Joe', 'Smith');
    INSERT INTO Emails(Contact_id, Email, Label) 
        VALUES(SCOPE_IDENTITY(), 'joe.smith@OpenWritings.net', 'personal');  
 
INSERT INTO Contacts(Firstname, Lastname) VALUES('Anne', 'Bond');
    INSERT INTO Emails(Contact_id, Email, Label) 
        VALUES(SCOPE_IDENTITY(), 'anne.bond@OpenWritings.net', 'work');  
 
INSERT INTO Contacts(Firstname, Lastname) VALUES('James', 'Bond');
    INSERT INTO Emails(Contact_id, Email, Label) 
        VALUES(SCOPE_IDENTITY(), 'james.bond@OpenWritings.net', 'work');

Display tables content

SELECT * FROM Contacts;
SELECT * FROM Emails;

Output

Github

Complete SQL script can be found at https://github.com/xuanngo2001/ms-sql/blob/master/sample-db/ms-sql-sample-db.sql

Here is how you can run it from command line.

# Adapt parameter values to your setup.
sqlcmd -S MyServer\Name -E -i ms-sql-sample-db.sql

MS SQL Server

About the author

Xuan Ngo is the founder of OpenWritings.net. He currently lives in Montreal, Canada. He loves to write about programming and open source subjects.

MS SQL - Sample database