T-SQL Data Definition Language (DDL)

Data Definition Language (DDL) statements are used to define, modify, and delete database objects such as tables, indexes, views, and more. These statements interact with the database's schema.

1. CREATE

The CREATE statement is used to create new database objects.

CREATE DATABASE

Creates a new database.

CREATE DATABASE database_name;

CREATE TABLE

Creates a new table with specified columns and their data types.

CREATE TABLE TableName (
Column1 DataType [constraints],
Column2 DataType [constraints],
Column3 DataType [constraints]
-- ... more columns
);

Example:

CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
FirstName VARCHAR(50) NOT NULL,
LastName VARCHAR(50) NOT NULL,
HireDate DATE,
Salary DECIMAL(10, 2) DEFAULT 0.00
);

CREATE INDEX

Creates an index on a table to improve query performance.

CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED] INDEX IndexName
ON TableName (Column1 [ASC|DESC], Column2 [ASC|DESC], ...);

Example:

CREATE NONCLUSTERED INDEX IX_Employees_LastName
ON Employees (LastName ASC);

CREATE VIEW

Creates a virtual table based on the result-set of an SQL statement.

CREATE VIEW ViewName AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

Example:

CREATE VIEW ActiveEmployees AS
SELECT EmployeeID, FirstName, LastName, HireDate
FROM Employees
WHERE HireDate > '2020-01-01';

CREATE PROCEDURE

Creates a stored procedure (a set of T-SQL statements precompiled and stored on the server).

CREATE PROCEDURE ProcedureName
@parameter1 datatype,
@parameter2 datatype
AS
BEGIN
-- T-SQL statements
END;

2. ALTER

The ALTER statement is used to modify existing database objects.

ALTER TABLE

Modifies a table by adding, deleting, or modifying columns, or by adding/dropping constraints.

-- Add a column
ALTER TABLE TableName
ADD NewColumn DataType [constraints];

-- Drop a column
ALTER TABLE TableName
DROP COLUMN ColumnName;

-- Modify a column (e.g., change data type or size)
ALTER TABLE TableName
ALTER COLUMN ColumnName NewDataType;

Example: Add a 'Department' column to the Employees table

ALTER TABLE Employees
ADD Department VARCHAR(100) NULL;

ALTER INDEX

Rebuilds or reorganizes an index.

ALTER INDEX IndexName ON TableName REBUILD;
ALTER INDEX IndexName ON TableName REORGANIZE;

3. DROP

The DROP statement is used to delete existing database objects.

DROP DATABASE

Deletes an entire database.

DROP DATABASE database_name;

DROP TABLE

Deletes a table and all its data.

DROP TABLE TableName;

Example: Drop the Employees table

DROP TABLE Employees;

DROP INDEX

Deletes an index.

DROP INDEX IndexName ON TableName;

Example: Drop the index on LastName

DROP INDEX IX_Employees_LastName ON Employees;

DROP VIEW

Deletes a view.

DROP VIEW ViewName;

DROP PROCEDURE

Deletes a stored procedure.

DROP PROCEDURE ProcedureName;

4. TRUNCATE TABLE

The TRUNCATE TABLE statement removes all rows from a table but keeps the table structure intact. It is generally faster than DELETE without a WHERE clause for large tables.

TRUNCATE TABLE TableName;

Example: Remove all records from the Employees table

TRUNCATE TABLE Employees;

5. RENAME (using sp_rename)

T-SQL does not have a direct RENAME statement for most objects. Instead, the system stored procedure sp_rename is used.

sp_rename

Renames a database object.

EXEC sp_rename 'OldName', 'NewName', 'ObjectType';

Example: Rename the Employees table to Staff

EXEC sp_rename 'Employees', 'Staff', 'OBJECT';

Example: Rename the IX_Employees_LastName index to IX_Staff_LastName

EXEC sp_rename 'Staff.IX_Employees_LastName', 'IX_Staff_LastName', 'INDEX';

Common Constraints

When creating or altering tables, you can define constraints to enforce data integrity:

Constraint Description Example Usage
PRIMARY KEY Uniquely identifies each record in a table. Cannot contain NULL values. EmployeeID INT PRIMARY KEY
FOREIGN KEY A field that links to the PRIMARY KEY in another table. Enforces referential integrity. DepartmentID INT FOREIGN KEY REFERENCES Departments(DepartmentID)
UNIQUE Ensures all values in a column are unique. Can contain one NULL value. Email VARCHAR(255) UNIQUE
NOT NULL Ensures a column cannot have a NULL value. FirstName VARCHAR(50) NOT NULL
DEFAULT Sets a default value for a column when no value is specified. IsActive BIT DEFAULT 1
CHECK Ensures that all values in a column satisfy a specific condition. Salary DECIMAL(10, 2) CHECK (Salary >= 0)