SQL Server: create a database with queries (the full life cycle of a database)

Tweet

When we think about a SQL Server database, we usually consider databases and tables as static objects while records contained as dynamic objects. What I mean is that we are inclined to build the structure of our database (the db itself, the tables, index and so forth), and then manipulate (insert, update, delete) the data contained in the tables. Some times we use temporary tables.
In this post we will see how to:

1. create a database
2. create a table
3. create an index
4. drop the index
5. drop or truncate the table
6. drop the database

The full life cycle of a database: from creation to destruction!

Create the database

It's very simple and the syntax of our query will be:

CREATE DATABASE databaseName

Create a table

The creation of a table is a little bit more complex:

CREATE TABLE tableName (
columnName1 dataType,
columnName2 dataType,
columnName3 dataType)

The columnName will be the name of the column and dataType is the data type of the column (I bet you thought differently...). Now... do you already know the list of available data types? Well, just in case, here's the list.
After the dataType we can set constraints. Those constrains are:
NOT NULL
The column will not accept NULL values.
UNIQUE
It is normally used in ID columns for uniquely identify records. To be used together with NOT NULL.
PRIMARY KEY
Identifies the primary key of the table.
FOREIGN KEY
The foreign key points to a primary key in another table. The syntax is a little bit different:

CREATE TABLE tableName (
tableId int NOT NULL PRIMARY KEY,
columnName1 dataType,
... ...
tableFk int FOREIGN KEY REFERENCES otherTable(otherTableId)
)

CHECK
It is used to set the value range of a column:

CREATE TABLE tableName (
tableId int NOT NULL CHECK (tableId>1),
columnName1 dataType,
... ...
)

DEFAULT
It determines the default value for a column

CREATE TABLE tableName (
columnName1 dataType DEFAULT defaultValue,
... ...
)

Create an index

In order to create an index we can use the following syntax:

CREATE INDEX indexName
ON tableName (columnName)

To create a unique index just add UNIQUE between CREATE and INDEX.

Drop the index

Now, we need to drop an index... we have just created it, but never mind... we want to drop it anyway:

DROP INDEX tableName.indexName

Simple as that. As you will see the DROP statement works similarly for everything.

Drop or truncate a table

We can drop an entire table or just empty it (with truncate). Let's start with dropping the table:

DROP TABLE tableName

If we need to delete all the data contained in a table, we can truncate it:

TRUNCATE TABLE tableName

Drop a database

To drop a database we use:

DROP DATABASE databaseName

If you have followed every command from the beginning of this post, you have created a database, a table inside it (with columns and constrains) and an index. Then you have removed the index, the table (or just the data contained in it) and deleted the database.

The birth, the life and the death of a database!

Enjoy and please share your thoughts in the comment section below.