Many developers and database administrators create databases using GUI tools such as SQL Server Management Studio (SSMS), Azure Data Studio, etc.
But it can be much quicker to create databases using SQL. This is especially true if you have various environments that you need to recreate the same database on. You can run the same script against each environment, and the database will be created within seconds each time.
In SQL Server, we do this with T-SQL. T-SQL stands for Transact-SQL, and it is SQL Server’s extension to SQL.
Below is an example of using T-SQL to create a database in SQL Server.
Basic Code
CREATE DATABASE Music;This is all the code you need to create a database. In this case, it creates a database called Music.
However, that’s all it creates. The database doesn’t contain any tables or other objects. To do that, you must use further statements to create those objects as required.
Also, the above CREATE DATABASE statement uses the most basic syntax which doesn’t include any arguments. There are many options that can be included with this statement.
For example, you can specify your own name for the files and their filegroups. You can also create a database snapshot, or attach database files to create a database from the detached files of another database.
See the Microsoft documentation for the full syntax for this statement.
Create Tables
Here’s an example of creating three tables for the above database.
USE Music;
CREATE TABLE Artists (
ArtistId int IDENTITY(1,1) NOT NULL PRIMARY KEY,
ArtistName nvarchar(255) NOT NULL,
ActiveFrom date
);
CREATE TABLE Genres (
GenreId int IDENTITY(1,1) NOT NULL PRIMARY KEY,
Genre nvarchar(50) NOT NULL
);
CREATE TABLE Albums (
AlbumId int IDENTITY(1,1) NOT NULL PRIMARY KEY,
AlbumName nvarchar(255) NOT NULL,
ReleaseDate date NOT NULL,
ArtistId int NOT NULL,
GenreId int NOT NULL
CONSTRAINT FK_Albums_Artists FOREIGN KEY (ArtistId)
REFERENCES dbo.Artists (ArtistId)
ON DELETE NO ACTION
ON UPDATE NO ACTION
);The first step is to switch to the correct database (in this case, the Music database). To do this, we use USE MUSIC.
Once you’re using the correct database, you can create your tables and other objects. In this case I created three tables. Each time I use CREATE TABLE, followed by the table name that I want to create. This is followed by that table’s definition.
The definition includes the table’s columns and their definitions. For example, ArtistId is a column, it uses an int data type, and I’ve set it as the primary key for the table. Being the primary key means that it uniquely identifies each row in the database.
I’ve also set this column to be an IDENTITY column, which means that each row will use an automatically generated value that increments with each row. In this case it will start at 1 and increment by 1 (that’s because I’ve specified IDENTITY(1,1).
Finally, I’ve also set this column to NOT NULL. This means that it must contain a value. It cannot be null. This is actually a requirement before you set the column as a primary key, but you can also set other columns to NOT NULL as required (which I have done in this example).
Create Relationships
In the above example, I created a relationship between the Albums and Artists tables.
A relationship is a kind of link between tables with related data. Relationships help enforce data integrity, because they allow you to specify that a column can only contain values that match the values in a specific column in another table.
Here’s the specific section of code from the above example that creates the relationship:
CREATE TABLE Albums (
AlbumId int IDENTITY(1,1) NOT NULL PRIMARY KEY,
AlbumName nvarchar(255) NOT NULL,
ReleaseDate date NOT NULL,
ArtistId int NOT NULL,
GenreId int NOT NULL
CONSTRAINT FK_Albums_Artists FOREIGN KEY (ArtistId)
REFERENCES dbo.Artists (ArtistId)
ON DELETE NO ACTION
ON UPDATE NO ACTION
);I created the relationship at the same time I created the Albums table. I was able to do that because I had already created the Artists table (which is the other table in the relationship).
I created the relationship by creating a foreign key constraint (using the CONSTRAINT argument), and specifying FOREIGN KEY along with the details of the relationship. The REFERENCES keyword specifies which table and column the foreign key references.
I named the relationship FK_Albums_Artists.
The ON DELETE and ON UPDATE parts are optional. They specify what to do in the event someone deletes or updates a row from the parent/primary key column. The default value is NO ACTION, which means that the Database Engine raises an error, and the update action on the row in the parent table is rolled back.
My article on How to Create a Relationship in SQL lists the other options you can provide here, as well as what each option does.
Create Relationships Later
You can also create a relationship on an existing table.
To do this using T-SQL, use the ALTER TABLE statement.
So I can run the following code after running the previous code.
ALTER TABLE Albums
ADD CONSTRAINT FK_Albums_Genres FOREIGN KEY (GenreId)
REFERENCES dbo.Genres (GenreId)
ON DELETE NO ACTION
ON UPDATE NO ACTION
;This creates another relationship, this time between the Albums and Genres tables.
You can see that this code does pretty much the same thing as the previous relationship. The only difference is that the relationship is between Artists and Genres instead of Albums and Artists.
In this case I named the relationship FK_Albums_Genres.
Insert Data
The above code has created a database that is fit for data. We can now add data.
In SQL Server, you can add data to a database by using the INSERT statement. When using this statement, you need to provide the name of the table, as well as the columns you’d like to insert data into.
Actually, providing the columns is optional if you’re inserting data into all columns, but for the sake of clarity, here’s an example that includes the column names.
INSERT INTO Artists (ArtistName, ActiveFrom)
VALUES
('Iron Maiden','1975-12-25'),
('AC/DC','1973-01-11'),
('Allan Holdsworth','1969-01-01'),
('Buddy Rich','1919-01-01'),
('Devin Townsend','1993-01-01'),
('Jim Reeves','1948-01-01'),
('Tom Jones','1963-01-01'),
('Maroon 5','1994-01-01'),
('The Script','2001-01-01'),
('Lit','1988-06-26'),
('Black Sabbath','1968-01-01'),
('Michael Learns to Rock','1988-03-15'),
('Carabao','1981-01-01'),
('Karnivool','1997-01-01'),
('Birds of Tokyo','2004-01-01'),
('Bodyjar','1990-01-01');
INSERT INTO Genres (Genre)
VALUES
('Rock'),
('Jazz'),
('Country'),
('Pop'),
('Blues'),
('Hip Hop'),
('Rap'),
('Punk');
INSERT INTO Albums (AlbumName, ReleaseDate, ArtistId, GenreId)
VALUES
('Powerslave', '1984-09-03', 1, 1),
('Powerage', '1978-05-05', 2, 1),
('Singing Down the Lane', '1956-01-01', 6, 3),
('Ziltoid the Omniscient', '2007-05-21', 5, 1),
('Casualties of Cool', '2014-05-14', 5, 1),
('Epicloud', '2012-09-18', 5, 1),
('Somewhere in Time', '1986-09-29', 1, 1),
('Piece of Mind', '1983-05-16', 1, 1),
('Killers', '1981-02-02', 1, 1),
('No Prayer for the Dying', '1990-10-01', 1, 1),
('No Sound Without Silence', '2014-09-12', 9, 4),
('Big Swing Face', '1967-06-01', 4, 2),
('Blue Night', '2000-11-01', 12, 4),
('Eternity', '2008-10-27', 12, 4),
('Scandinavia', '2012-06-11', 12, 4),
('Long Lost Suitcase', '2015-10-09', 7, 4),
('Praise and Blame', '2010-06-26', 7, 4),
('Along Came Jones', '1965-05-21', 7, 4),
('All Night Wrong', '2002-05-05', 3, 2),
('The Sixteen Men of Tain', '2000-03-20', 3, 2);As you can see, each row has its own line. We simply add one line per row with each column separated by a comma and surrounded by parentheses. A comma also separates each row (after the parentheses).
Running the above code against our newly created database results in the following output:
(16 rows affected) (8 rows affected) (20 rows affected)
This tells us that the data was successfully inserted.
Check the Database
We can run a quick test against the database to verify that it was created and our data was inserted.
For example, we can run the following simple query.
SELECT * FROM Artists;Result:
+------------+------------------------+--------------+ | ArtistId | ArtistName | ActiveFrom | |------------+------------------------+--------------| | 1 | Iron Maiden | 1975-12-25 | | 2 | AC/DC | 1973-01-11 | | 3 | Allan Holdsworth | 1969-01-01 | | 4 | Buddy Rich | 1919-01-01 | | 5 | Devin Townsend | 1993-01-01 | | 6 | Jim Reeves | 1948-01-01 | | 7 | Tom Jones | 1963-01-01 | | 8 | Maroon 5 | 1994-01-01 | | 9 | The Script | 2001-01-01 | | 10 | Lit | 1988-06-26 | | 11 | Black Sabbath | 1968-01-01 | | 12 | Michael Learns to Rock | 1988-03-15 | | 13 | Carabao | 1981-01-01 | | 14 | Karnivool | 1997-01-01 | | 15 | Birds of Tokyo | 2004-01-01 | | 16 | Bodyjar | 1990-01-01 | +------------+------------------------+--------------+
And let’s run another query that uses data from all three tables.
SELECT
ArtistName,
AlbumName,
ReleaseDate
FROM Artists ar
INNER JOIN Albums al
ON ar.ArtistId = al.ArtistId
INNER JOIN Genres g
ON al.GenreId = g.GenreId
WHERE g.Genre = 'Rock';Result:
+----------------+-------------------------+---------------+ | ArtistName | AlbumName | ReleaseDate | |----------------+-------------------------+---------------| | Iron Maiden | Powerslave | 1984-09-03 | | AC/DC | Powerage | 1978-05-05 | | Devin Townsend | Ziltoid the Omniscient | 2007-05-21 | | Devin Townsend | Casualties of Cool | 2014-05-14 | | Devin Townsend | Epicloud | 2012-09-18 | | Iron Maiden | Somewhere in Time | 1986-09-29 | | Iron Maiden | Piece of Mind | 1983-05-16 | | Iron Maiden | Killers | 1981-02-02 | | Iron Maiden | No Prayer for the Dying | 1990-10-01 | +----------------+-------------------------+---------------+
This query demonstrates the importance of creating relationships between tables with related data. When we run queries like this we really need the data to be consistent between the tables.
The database is now ready for use. We can run queries against it. We can insert more data. We can add more tables, relationships, views, stored procedures, user-defined functions, and much more.