T-SQL

005. T-SQL DDL (Data Definition Language) Commands

This guide includes all DDL commands with executable examples to fully build and manage the OrderDB database.

1. Database Object Creation & Modification

CREATE (All Object Types)

-- Database
CREATE DATABASE OrderDB;
GO
-- Schema
CREATE SCHEMA Sales;
GO
-- Table
CREATE TABLE Sales.Customers (
    CustomerID INT PRIMARY KEY IDENTITY(1,1),
    FirstName NVARCHAR(50) NOT NULL,
    LastName NVARCHAR(50) NOT NULL,
    Email NVARCHAR(100) UNIQUE,
    RegistrationDate DATETIME DEFAULT GETDATE()
);
GO
-- View
CREATE VIEW Sales.ActiveCustomers AS
SELECT CustomerID, FirstName, LastName, Email
FROM Sales.Customers
WHERE RegistrationDate > DATEADD(YEAR, -1, GETDATE());
GO
-- Index
CREATE UNIQUE INDEX IX_Customers_Email ON Sales.Customers(Email);
GO
-- Stored Procedure
CREATE PROCEDURE Sales.AddCustomer
    @FirstName NVARCHAR(50),
    @LastName NVARCHAR(50),
    @Email NVARCHAR(100) = NULL
AS
BEGIN
    INSERT INTO Sales.Customers (FirstName, LastName, Email)
    VALUES (@FirstName, @LastName, @Email);
END;
GO
-- Function
CREATE FUNCTION Sales.GetOrderCount(@CustomerID INT)
RETURNS INT
AS
BEGIN
    RETURN (SELECT COUNT(*) FROM Sales.Orders WHERE CustomerID = @CustomerID);
END;
GO
-- Trigger
CREATE TRIGGER Sales.trg_CustomerUpdate
ON Sales.Customers
AFTER UPDATE
AS
BEGIN
    INSERT INTO Sales.CustomerAudit(CustomerID, ChangeDate)
    SELECT CustomerID, GETDATE() FROM inserted;
END;
GO
-- User-Defined Type
CREATE TYPE dbo.PhoneNumber FROM NVARCHAR(20) NULL;
GO

2. ALTER (Modify Existing Objects)

-- Table (Add column)
ALTER TABLE Sales.Customers ADD PhoneNumber dbo.PhoneNumber;
GO
-- Table (Modify column)
ALTER TABLE Sales.Customers ALTER COLUMN PhoneNumber NVARCHAR(25);
GO
-- View
ALTER VIEW Sales.ActiveCustomers AS
SELECT CustomerID, FirstName, LastName, Email, PhoneNumber
FROM Sales.Customers
WHERE RegistrationDate > DATEADD(MONTH, -6, GETDATE());
GO
-- Procedure
ALTER PROCEDURE Sales.AddCustomer
    @FirstName NVARCHAR(50),
    @LastName NVARCHAR(50),
    @Email NVARCHAR(100) = NULL,
    @PhoneNumber dbo.PhoneNumber = NULL
AS
BEGIN
    INSERT INTO Sales.Customers (FirstName, LastName, Email, PhoneNumber)
    VALUES (@FirstName, @LastName, @Email, @PhoneNumber);
END;
GO
-- Function
ALTER FUNCTION Sales.GetOrderCount(@CustomerID INT)
RETURNS INT
AS
BEGIN
    RETURN (SELECT COUNT(*) FROM Sales.Orders
            WHERE CustomerID = @CustomerID AND TotalAmount > 100);
END;
GO

3. DROP (Remove Objects)

-- Trigger
DROP TRIGGER IF EXISTS Sales.trg_CustomerUpdate;
GO
-- Function
DROP FUNCTION IF EXISTS Sales.GetOrderCount;
GO
-- Procedure
DROP PROCEDURE IF EXISTS Sales.AddCustomer;
GO
-- View
DROP VIEW IF EXISTS Sales.ActiveCustomers;
GO
-- Index
DROP INDEX IF EXISTS IX_Customers_Email ON Sales.Customers;
GO
-- Table
DROP TABLE IF EXISTS Sales.Customers;
GO
-- Schema
DROP SCHEMA IF EXISTS Sales;
GO
-- Type
DROP TYPE IF EXISTS dbo.PhoneNumber;
GO
-- Database
USE master;
GO
DROP DATABASE IF EXISTS OrderDB;
GO

4. TRUNCATE (Remove All Table Data)

-- Much faster than DELETE for large tables
TRUNCATE TABLE Sales.OrderDetails;
GO

5. sp_rename (Rename Objects)

-- Rename table
EXEC sp_rename 'Sales.Customers', 'Clients';
GO
-- Rename column
EXEC sp_rename 'Sales.Clients.PhoneNumber', 'Phone', 'COLUMN';
GO
-- Rename index
EXEC sp_rename 'Sales.Clients.IX_Customers_Email', 'IX_Clients_Email', 'INDEX';
GO

6. sp_addextendedproperty (Add Metadata)

-- Add table description
EXEC sp_addextendedproperty
    @name = N'MS_Description',
    @value = N'Stores customer information',
    @level0type = N'SCHEMA', @level0name = 'Sales',
    @level1type = N'TABLE', @level1name = 'Clients';
GO
-- Add column description
EXEC sp_addextendedproperty
    @name = N'MS_Description',
    @value = N'Customer contact phone number',
    @level0type = N'SCHEMA', @level0name = 'Sales',
    @level1type = N'TABLE', @level1name = 'Clients',
    @level2type = N'COLUMN', @level2name = 'Phone';
GO

7. Schema Management

-- Create schema with authorization
CREATE SCHEMA Inventory AUTHORIZATION dbo;
GO
-- Transfer table to new schema
ALTER SCHEMA Inventory TRANSFER Sales.Products;
GO
-- Drop schema (must be empty)
DROP SCHEMA IF EXISTS Archive;
GO

8. Complete Database Example

-- Full database setup
CREATE DATABASE OrderDB;
GO
USE OrderDB;
GO
-- Schemas
CREATE SCHEMA Sales;
CREATE SCHEMA Inventory;
GO
-- Tables
CREATE TABLE Sales.Customers (
    CustomerID INT PRIMARY KEY IDENTITY(1,1),
    Name NVARCHAR(100) NOT NULL,
    Email NVARCHAR(100) UNIQUE,
    Phone NVARCHAR(20)
);
GO
CREATE TABLE Inventory.Products (
    ProductID INT PRIMARY KEY IDENTITY(1,1),
    Name NVARCHAR(100) NOT NULL,
    Price DECIMAL(10,2) CHECK (Price > 0)
);
GO
CREATE TABLE Sales.Orders (
    OrderID INT PRIMARY KEY IDENTITY(1,1),
    CustomerID INT REFERENCES Sales.Customers(CustomerID),
    OrderDate DATETIME DEFAULT GETDATE()
);
GO
-- Indexes
CREATE INDEX IX_Orders_Customer ON Sales.Orders(CustomerID);
GO
-- Views
CREATE VIEW Sales.CustomerOrders AS
SELECT c.CustomerID, c.Name, COUNT(o.OrderID) AS OrderCount
FROM Sales.Customers c
LEFT JOIN Sales.Orders o ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerID, c.Name;
GO
-- Procedures
CREATE PROCEDURE Sales.PlaceOrder
    @CustomerID INT,
    @ProductID INT
AS
BEGIN
    DECLARE @OrderID INT;

    INSERT INTO Sales.Orders (CustomerID)
    VALUES (@CustomerID);

    SET @OrderID = SCOPE_IDENTITY();

    INSERT INTO Sales.OrderDetails (OrderID, ProductID)
    VALUES (@OrderID, @ProductID);
END;
GO

Execution Order Cheat Sheet

1. Create: Database → Schemas → Tables → Indexes/Views → Procedures/Functions → Triggers
2. Modify: Use ALTER after creation
3. Drop: Reverse order (Triggers → Functions → Procedures → Views → Indexes → Tables → Schemas → Database)

This site is open source. Improve this page.