Friday, 29 July 2011

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


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 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:
The column will not accept NULL values.
It is normally used in ID columns for uniquely identify records. To be used together with NOT NULL.
Identifies the primary key of the table.
The foreign key points to a primary key in another table. The syntax is a little bit different:
CREATE TABLE tableName (
columnName1 dataType,
... ...
tableFk int FOREIGN KEY REFERENCES otherTable(otherTableId)
It is used to set the value range of a column:
CREATE TABLE tableName (
tableId int NOT NULL CHECK (tableId>1),
columnName1 dataType,
... ...
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:
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:

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.

0 thoughts:

Post a Comment

Comments are moderated. I apologize if I don't publish comments immediately.

However, I do answer to all the comments.