SQL DDL (Data Definition Language)

Data Definition Language (DDL) in Transact-SQL

Data Definition Language (DDL) statements are used to define, alter, and remove database objects such as tables, indexes, and views. In Transact-SQL (T-SQL), these commands provide the structure for your database.

Core DDL Statements

1. CREATE

The CREATE statement is used to create new database objects.

Creating a Table

This is one of the most fundamental DDL operations, defining the structure of your data.


CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY IDENTITY(1,1),
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    Email VARCHAR(100) UNIQUE,
    RegistrationDate DATETIME DEFAULT GETDATE()
);

Creating an Index

Indexes improve the performance of data retrieval operations.


CREATE INDEX IX_CustomerEmail
ON Customers (Email);

Creating a View

Views are virtual tables based on the result set of a stored query.


CREATE VIEW ActiveCustomers AS
SELECT CustomerID, FirstName, LastName
FROM Customers
WHERE RegistrationDate > DATEADD(year, -1, GETDATE());

2. ALTER

The ALTER statement is used to modify existing database objects.

Altering a Table (Adding a Column)


ALTER TABLE Customers
ADD PhoneNumber VARCHAR(20);

Altering a Table (Modifying a Column)


ALTER TABLE Customers
ALTER COLUMN Email VARCHAR(150);

3. DROP

The DROP statement is used to delete database objects.

Dropping a Table

-- Warning: This will permanently delete the table and all its data.


DROP TABLE Customers;

Dropping an Index


DROP INDEX IX_CustomerEmail ON Customers;

Dropping a View


DROP VIEW ActiveCustomers;

Other DDL Commands

TRUNCATE TABLE: Removes all rows from a table, but the table structure remains. It's generally faster than DELETE for large tables.
RENAME OBJECT: Renames an existing database object (available in newer SQL Server versions).

Key Concepts

Schema: A collection of database objects owned by a user.
Data Types: Define the type of data a column can hold (e.g., INT, VARCHAR, DATETIME).
Constraints: Rules enforced on data in tables to ensure accuracy and integrity (e.g., PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK).

Understanding and effectively using DDL statements is crucial for database administrators and developers to manage the structure and integrity of SQL Server databases.

For more in-depth information, please refer to the official Microsoft SQL Server documentation.