This article explains SQL DDL commands in Microsoft SQL Server using a few simple examples.

SQL commands broadly fit into four categories:

• DDL (Data Definition Language)
• DML (Data Manipulation Language)
• DCL (Data Control Language)
• TCL (Transactional Control Language)

This article only covers the SQL DDL commands.

SQL DDL commands

The DDL commands in SQL are used to create database schema and to define the type and structure of the data that will be stored in a database. SQL DDL commands are further divided into the following major categories:

• CREATE
• ALTER
• DROP
• TRUNCATE

CREATE

The CREATE query is used to create a database or objects such as tables, views, stored procedures, etc.

Creating a database

The following example demonstrates how the CREATE query can be used to create a database in MS SQL Server:

The script above creates a database named “LibraryDB” in MS SQL Server.

Creating a table

The CREATE query is also used to add tables in an existing database as shown in the following script:

The above script creates a table named “Books” in the “LibraryDB” database that we created earlier.

The “Books” table contains three columns: Id, Name, and Price. The Id column is the primary key column and it cannot be NULL. A column with a PRIMARY KEY constraint must contain unique values. However, since we have set the IDENTITY property for the Id column, every time a new record is added in the Books table, the value of the Id column will be incremented by 1, starting from 1. You need to specify the values for the Name column as well as it cannot have NULL. Finally, the Price column can have NULL values.

To view all the tables in the LibraryDB, execute the following QL DDL script:

You should see the following output:

Similarly, to see all the columns in the Books table, run the following script:

Here is the output:

You can see how the CREATE query can be used to define the structure of a table and the type of data that will be stored in a table. Note, we have not added any record to the Books table yet as SQL DDL commands are only concerned with the structure of the database and not with the database records. The SQL DML commands are used for inserting, modifying and deleting database records.

ALTER

The ALTER command in SQL DDL is used to modify the structure of an already existing table.

Adding a new column

For example, if we want to add a new column e.g. ISBN to the existing Books table in the LibraryDB database, the ALTER command can be used as follows:

The syntax of the ALTER command is straightforward. The ALTER statement is used followed by the object type and the name of the object, which in this case are TABLE and Books, respectively.

Next, you need to specify the operation that you need to perform, which is ADD in our case. Let’s now again SELECT the columns from the Books table and see if the ISBN column has been added to the Books table:

Here is the result set:

In the output, you can see the newly added ISBN column.

Modifying an existing column

Let’s see another case where ALTER command can be useful. Suppose, instead of adding a new column to a table, you want to modify an existing column. For example, you want to change the data type of the ISBN column from INT to VARCHAR (50). The ALTER query can be used as follows:

You can see that to modify an existing column within a table, we first have to use the ALTER command with the table name and then another ALTER command with the name of the column that is to be modified.

If you again select the column names, you will see the updated data type (VARCHAR) for the ISBN column.

DROP

The DROP command is a type of SQL DDL command, that is used to delete an existing database or an object within a database.

Deleting a database

The following DROP command deletes the LibraryDB database that we created earlier:

Note: If you execute the above command, the LibraryDB database will be deleted. To execute the rest of the queries in this article, you will again need to CREATE the LibraryDB database, along with the Books table.

Deleting a table

The DROP command is a type of SQL DDL command that is used to delete an existing table. For instance, the following command will delete the Books table:

Deleting a column

To delete a column within a database, the DROP query is used in combination with the ALTER query. The ALTER query specifies the table that you want to delete whereas the DROP query specifies the column to delete within the table specified by the ALTER query. Let’s drop the ISBN column from the Books:

TRUNCATE

The TRUNCATE command in SQL DDL is used to remove all the records from a table. Let’s insert a few records in the Books table:

Let’s see if the records have been actually inserted:

Here is the result set:

You can see the three records that we inserted in the Books table.

The TRUNCATE command will remove all the records from the Books table as shown below:

If you again select all the records from the Books table, you will see that the table is empty.

Conclusion

In this article, you saw how to use SQL DDL commands to create a database schema and to define and modify the structure of your database. You saw how to execute SQL DDL commands in MS SQL Server with the help of different examples. Have anything to say about the article? Please feel free to comment.

• Author
• Recent Posts

Ben Richardson

Ben Richardson runs Acuity Training a leading provider of SQL training the UK. It offers a full range of SQL training from introductory courses through to advanced administration and data warehouse training – see here for more details. Acuity has offices in London and Guildford, Surrey. He also blogs occasionally on Acuity’s blog

View all posts by Ben Richardson

Latest posts by Ben Richardson (see all)

• Working with the SQL MIN function in SQL Server - May 12, 2022
• SQL percentage calculation examples in SQL Server - January 19, 2022
• Working with Power BI report themes - February 25, 2021