SQL commands in Microsoft SQL Server can be broadly categorized into four types: DDL (Data Definition Language), DML (Data Manipulation Language), DCL (Data Control Language), and TCL (Transactional Control Language). In this article, we will focus on SQL DDL commands and explore their usage with simple examples.

SQL DDL Commands

SQL DDL commands are used to create and define the structure of a database and its objects. They include the following major categories:

• CREATE
• ALTER
• DROP
• TRUNCATE

CREATE

The CREATE command is used to create a new database or objects within a database, such as tables, views, stored procedures, and more. Let’s take a look at a couple of examples:

To create a new database:

CREATE DATABASE LibraryDB;

This command creates a database named “LibraryDB” in Microsoft SQL Server.

To create a new table within an existing database:

USE LibraryDB;

CREATE TABLE Books (
  Id INT PRIMARY KEY IDENTITY(1, 1),
  Name VARCHAR(50) NOT NULL,
  Price INT
);

This command creates a table named “Books” in the “LibraryDB” database. The table has three columns: Id, Name, and Price. The Id column is the primary key column with an IDENTITY property, which automatically increments its value for each new record added to the table.

ALTER

The ALTER command is used to modify the structure of an existing table. It allows you to add or modify columns within a table. Here are a couple of examples:

To add a new column to an existing table:

USE LibraryDB;

ALTER TABLE Books
ADD ISBN INT NOT NULL;

This command adds a new column named “ISBN” of type INT to the “Books” table.

To modify an existing column within a table:

USE LibraryDB;

ALTER TABLE Books
ALTER COLUMN ISBN VARCHAR(50);

This command changes the data type of the “ISBN” column from INT to VARCHAR(50) within the “Books” table.

DROP

The DROP command is used to delete an existing database or objects within a database. Here are a couple of examples:

To delete a database:

DROP DATABASE LibraryDB;

This command deletes the “LibraryDB” database.

To delete a table:

DROP TABLE Books;

This command deletes the “Books” table.

TRUNCATE

The TRUNCATE command is used to remove all the records from a table. Here’s an example:

To remove all records from a table:

TRUNCATE TABLE Books;

This command removes all the records from the “Books” table.

Conclusion

In this article, we have explored SQL DDL commands in Microsoft SQL Server. We have learned how to create databases and tables, modify their structure, and delete them if needed. Understanding these commands is essential for managing the schema and structure of your database effectively.

Do you have any questions or comments about SQL DDL commands? Feel free to share your thoughts below!