SQL Data Definition Language (DDL)

MSDN Home SQL Docs

Overview

The Data Definition Language (DDL) in SQL is used to define and modify database structures such as tables, indexes, and schemas. DDL statements are essential for creating, altering, and removing database objects.

CREATE TABLE

Creates a new table in the database.

CREATE TABLE dbo.Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName NVARCHAR(50) NOT NULL,
    LastName NVARCHAR(50) NOT NULL,
    HireDate DATE,
    Salary MONEY
);

Key options:

  • PRIMARY KEY – defines the primary key constraint.
  • FOREIGN KEY – creates a relationship to another table.
  • CHECK – enforces custom validation rules.
  • DEFAULT – supplies a default value for a column.

ALTER TABLE

Modifies an existing table's definition.

ALTER TABLE dbo.Employees
ADD DepartmentID INT;

ALTER TABLE dbo.Employees
DROP COLUMN Salary;

ALTER TABLE dbo.Employees
ADD CONSTRAINT FK_Dept
FOREIGN KEY (DepartmentID)
REFERENCES dbo.Departments(DepartmentID);

DROP TABLE

Deletes a table and all its data.

DROP TABLE dbo.Employees;

Use IF EXISTS to avoid errors if the table does not exist.

DROP TABLE IF EXISTS dbo.Employees;

CREATE INDEX

Improves query performance by creating an index on one or more columns.

CREATE INDEX IX_Employees_LastName
ON dbo.Employees (LastName);

For unique indexes, add the UNIQUE keyword.

ALTER INDEX

Rebuilds or reorganizes an existing index.

ALTER INDEX IX_Employees_LastName
ON dbo.Employees
REBUILD WITH (FILLFACTOR = 80);

DROP INDEX

Removes an index from a table.

DROP INDEX IX_Employees_LastName
ON dbo.Employees;

CREATE SCHEMA

Creates a new schema to group related objects.

CREATE SCHEMA Sales AUTHORIZATION dbo;

ALTER SCHEMA

Transfers an object from one schema to another.

ALTER SCHEMA Sales
TRANSFER dbo.Employees;

DROP SCHEMA

Deletes a schema, provided it has no objects.

DROP SCHEMA Sales;