01 — DDL Syntax Reference: CREATE, ALTER, DROP

-- Minimal table
CREATE TABLE dbo.Orders (
    OrderID   INT           NOT NULL,
    CustomerID INT          NOT NULL,
    OrderDate  DATETIME2(3) NOT NULL CONSTRAINT DF_Orders_OrderDate DEFAULT SYSUTCDATETIME(),
    Status     NVARCHAR(20) NOT NULL,
    TotalAmount DECIMAL(18,4) NULL,
    CONSTRAINT PK_Orders PRIMARY KEY CLUSTERED (OrderID ASC)
);

-- Table with explicit filegroup, compression, and ROWGUIDCOL
CREATE TABLE dbo.Documents (
    DocumentID  UNIQUEIDENTIFIER NOT NULL ROWGUIDCOL
                    CONSTRAINT DF_Documents_ID DEFAULT NEWSEQUENTIALID(),
    Title       NVARCHAR(400)    NOT NULL,
    Body        NVARCHAR(MAX)    NULL,
    CreatedAt   DATETIME2(0)     NOT NULL CONSTRAINT DF_Documents_Created DEFAULT SYSUTCDATETIME(),
    RowVersion  ROWVERSION       NOT NULL,   -- auto-updated 8-byte binary; NOT NULL but never specified on INSERT
    CONSTRAINT PK_Documents PRIMARY KEY CLUSTERED (DocumentID)
) ON [PRIMARY]
  WITH (DATA_COMPRESSION = PAGE);

CREATE TABLE dbo.OrderItems (
    OrderID    INT NOT NULL,
    LineNumber  INT NOT NULL,
    ProductID   INT NOT NULL,
    Qty         INT NOT NULL CONSTRAINT CK_OrderItems_Qty CHECK (Qty > 0),
    UnitPrice   DECIMAL(18,4) NOT NULL,
    CONSTRAINT PK_OrderItems PRIMARY KEY CLUSTERED (OrderID, LineNumber),
    CONSTRAINT FK_OrderItems_Orders  FOREIGN KEY (OrderID)   REFERENCES dbo.Orders(OrderID)   ON DELETE CASCADE,
    CONSTRAINT FK_OrderItems_Product FOREIGN KEY (ProductID) REFERENCES dbo.Products(ProductID) ON DELETE NO ACTION
);

-- Add a column (nullable columns are instantaneous on 2012+; NOT NULL with default also fast via metadata-only)
ALTER TABLE dbo.Orders ADD ShipDate DATETIME2(0) NULL;

-- Add NOT NULL column with a default — metadata-only since SQL Server 2012
ALTER TABLE dbo.Orders
    ADD IsArchived BIT NOT NULL CONSTRAINT DF_Orders_IsArchived DEFAULT 0;

-- Add a constraint
ALTER TABLE dbo.Orders
    ADD CONSTRAINT CK_Orders_Status CHECK (Status IN ('New','Processing','Shipped','Cancelled'));

-- Drop a constraint (must name it — always name constraints!)
ALTER TABLE dbo.Orders DROP CONSTRAINT CK_Orders_Status;

-- Alter a column type — rewrites the column; can be slow on large tables
ALTER TABLE dbo.Orders ALTER COLUMN Status NVARCHAR(30) NOT NULL;

-- Drop a column — marks column as dropped; space only reclaimed on heap rebuild or REORGANIZE
ALTER TABLE dbo.Orders DROP COLUMN ShipDate;

-- Enable / disable a constraint (NOCHECK skips existing rows; CHECK WITH CHECK validates all rows)
ALTER TABLE dbo.Orders NOCHECK CONSTRAINT FK_Orders_Cust;
ALTER TABLE dbo.Orders WITH CHECK CHECK CONSTRAINT FK_Orders_Cust;

-- Safe drop pattern
IF OBJECT_ID(N'dbo.Orders', N'U') IS NOT NULL
    DROP TABLE dbo.Orders;

-- SQL Server 2016+ preferred syntax
DROP TABLE IF EXISTS dbo.Orders;

-- Drop multiple tables in one statement (2016+)
DROP TABLE IF EXISTS dbo.OrderItems, dbo.Orders;   -- drop child before parent (FK order matters)

-- Schema ownership defaults to the issuing user or can be specified
CREATE SCHEMA sales AUTHORIZATION dbo;
CREATE SCHEMA reporting AUTHORIZATION dbo;

-- Create table in that schema immediately inside the same batch
-- (CREATE SCHEMA must be the only statement in the batch or use a workaround)
-- Pattern: create schema first, then create objects in subsequent batches.

-- Move a table from dbo to reporting schema
ALTER SCHEMA reporting TRANSFER dbo.SalesSummary;

-- Move a procedure
ALTER SCHEMA sales TRANSFER dbo.usp_PlaceOrder;

DROP SCHEMA IF EXISTS reporting;   -- fails if schema still contains objects

-- Basic integer sequence
CREATE SEQUENCE dbo.OrderNumberSeq
    AS INT
    START WITH 1
    INCREMENT BY 1
    MINVALUE 1
    MAXVALUE 2147483647
    CYCLE        -- wraps around; omit NO CYCLE for strict monotonic
    CACHE 50;    -- pre-allocates 50 values in memory; survives restarts with a gap of up to 50

-- Globally unique order number with prefix room (use BIGINT for safety)
CREATE SEQUENCE dbo.InvoiceSeq
    AS BIGINT
    START WITH 100000
    INCREMENT BY 1
    NO CYCLE
    CACHE 20;

-- Get next value
DECLARE @next BIGINT = NEXT VALUE FOR dbo.InvoiceSeq;

-- Use in INSERT
INSERT INTO dbo.Invoices (InvoiceID, CustomerID, InvoiceDate)
VALUES (NEXT VALUE FOR dbo.InvoiceSeq, 42, SYSUTCDATETIME());

-- Use in DEFAULT constraint
ALTER TABLE dbo.Invoices
    ADD CONSTRAINT DF_Invoices_ID DEFAULT (NEXT VALUE FOR dbo.InvoiceSeq) FOR InvoiceID;

-- Generate a range atomically (sp_sequence_get_range)
DECLARE @first SQL_VARIANT, @last SQL_VARIANT, @count BIGINT = 100;
EXEC sys.sp_sequence_get_range
    @sequence_name     = N'dbo.InvoiceSeq',
    @range_size        = @count,
    @range_first_value = @first OUTPUT,
    @range_last_value  = @last  OUTPUT;
-- Use @first through @last for batch inserts

-- Restart sequence (useful in test environments)
ALTER SEQUENCE dbo.OrderNumberSeq RESTART WITH 1;

-- Change cache size
ALTER SEQUENCE dbo.OrderNumberSeq CACHE 100;

-- Extend max value
ALTER SEQUENCE dbo.OrderNumberSeq MAXVALUE 9999999999;

-- Alias a table in another database (cross-database without four-part names in queries)
CREATE SYNONYM dbo.ArchivedOrders FOR ArchiveDB.dbo.Orders;

-- Alias a linked-server table
CREATE SYNONYM dbo.RemoteInventory FOR [LinkedSrv].[WarehouseDB].[dbo].[Inventory];

-- Alias a procedure to hide the schema
CREATE SYNONYM dbo.GetCustomer FOR dbo.usp_GetCustomerByID;

DROP SYNONYM IF EXISTS dbo.ArchivedOrders;

-- Minimal
CREATE DATABASE SalesApp;

-- Full specification
CREATE DATABASE SalesApp
ON PRIMARY (
    NAME = SalesApp_data,
    FILENAME = 'D:\MSSQL\DATA\SalesApp.mdf',
    SIZE = 256MB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 64MB
),
FILEGROUP SalesApp_readonly READ_ONLY (
    NAME = SalesApp_ro,
    FILENAME = 'E:\MSSQL\RO\SalesApp_ro.ndf',
    SIZE = 64MB
)
LOG ON (
    NAME = SalesApp_log,
    FILENAME = 'L:\MSSQL\LOG\SalesApp.ldf',
    SIZE = 64MB,
    MAXSIZE = 4GB,
    FILEGROWTH = 64MB
)
COLLATE Latin1_General_CI_AS;

-- Change recovery model
ALTER DATABASE SalesApp SET RECOVERY FULL;

-- Enable Read-Committed Snapshot Isolation (RCSI) — reduces blocking
-- Requires brief SCH-M lock; users are briefly disconnected on busy systems
ALTER DATABASE SalesApp SET READ_COMMITTED_SNAPSHOT ON;

-- Set compatibility level
ALTER DATABASE SalesApp SET COMPATIBILITY_LEVEL = 160;  -- SQL Server 2022

-- Enable Query Store
ALTER DATABASE SalesApp SET QUERY_STORE = ON (
    OPERATION_MODE = READ_WRITE,
    MAX_STORAGE_SIZE_MB = 1000,
    QUERY_CAPTURE_MODE = AUTO
);

-- Add a secondary filegroup and file
ALTER DATABASE SalesApp
ADD FILEGROUP SalesApp_archive;

ALTER DATABASE SalesApp
ADD FILE (
    NAME = SalesApp_archive_1,
    FILENAME = 'D:\MSSQL\ARCHIVE\SalesApp_ar1.ndf',
    SIZE = 512MB,
    FILEGROWTH = 256MB
) TO FILEGROUP SalesApp_archive;

-- Rename database (requires exclusive access)
ALTER DATABASE SalesApp MODIFY NAME = SalesApp_v2;

-- BAD — system-generated name
ALTER TABLE dbo.Orders ADD TaxRate DECIMAL(5,2) NOT NULL DEFAULT 0;

-- GOOD — named
ALTER TABLE dbo.Orders ADD TaxRate DECIMAL(5,2) NOT NULL
    CONSTRAINT DF_Orders_TaxRate DEFAULT 0;

-- BAD
ID UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY CLUSTERED

-- GOOD (GUID as clustered PK)
ID UNIQUEIDENTIFIER NOT NULL ROWGUIDCOL DEFAULT NEWSEQUENTIALID() PRIMARY KEY CLUSTERED

-- BEST (separate clustered PK + nonclustered GUID — still use NEWSEQUENTIALID)
OrderID   INT               NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED,
PublicRef UNIQUEIDENTIFIER  NOT NULL DEFAULT NEWSEQUENTIALID() UNIQUE NONCLUSTERED

-- Dev: clear data fast
TRUNCATE TABLE dbo.StagingLoad;

-- Dev: rebuild schema from scratch
DROP TABLE IF EXISTS dbo.StagingLoad;
CREATE TABLE dbo.StagingLoad ( ... );