SQL Server Cheatsheet

SELECT Basics

-- Basic SELECT
SELECT column1, column2 FROM TableName;
SELECT * FROM TableName;
SELECT DISTINCT column1 FROM TableName;

-- Aliases
SELECT FirstName AS Name, Salary * 12 AS AnnualSalary FROM Employees;

-- Filtering
SELECT * FROM Products WHERE Price > 100 AND Category = 'Electronics';
SELECT * FROM Products WHERE Price BETWEEN 10 AND 50;
SELECT * FROM Products WHERE Name LIKE '%phone%';
SELECT * FROM Products WHERE CategoryId IN (1, 2, 3);
SELECT * FROM Products WHERE DeletedAt IS NULL;

-- Sorting
SELECT * FROM Products ORDER BY Price DESC, Name ASC;

-- Limiting rows
SELECT TOP 10 * FROM Orders ORDER BY OrderDate DESC;
-- Or with OFFSET/FETCH (pagination):
SELECT * FROM Orders ORDER BY OrderDate DESC
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

JOINs

-- INNER JOIN (only matching rows)
SELECT o.OrderId, c.Name
FROM Orders o
INNER JOIN Customers c ON o.CustomerId = c.Id;

-- LEFT JOIN (all from left, matching from right)
SELECT c.Name, o.OrderId
FROM Customers c
LEFT JOIN Orders o ON c.Id = o.CustomerId;

-- RIGHT JOIN (all from right, matching from left)
SELECT c.Name, o.OrderId
FROM Orders o
RIGHT JOIN Customers c ON o.CustomerId = c.Id;

-- FULL OUTER JOIN
SELECT c.Name, o.OrderId
FROM Customers c
FULL OUTER JOIN Orders o ON c.Id = o.CustomerId;

-- SELF JOIN
SELECT a.Name AS Employee, b.Name AS Manager
FROM Employees a
LEFT JOIN Employees b ON a.ManagerId = b.Id;

-- CROSS JOIN (every combination)
SELECT c.Name, p.Name FROM Colors c CROSS JOIN Products p;

Aggregates & GROUP BY

SELECT
    Category,
    COUNT(*)           AS TotalProducts,
    SUM(Stock)         AS TotalStock,
    AVG(Price)         AS AvgPrice,
    MIN(Price)         AS MinPrice,
    MAX(Price)         AS MaxPrice
FROM Products
GROUP BY Category
HAVING COUNT(*) > 5        -- filter on aggregated result
ORDER BY TotalProducts DESC;

-- COUNT with DISTINCT
SELECT COUNT(DISTINCT CustomerId) FROM Orders;

-- Window functions
SELECT
    Name, Salary,
    ROW_NUMBER() OVER (ORDER BY Salary DESC)  AS Rank,
    RANK()       OVER (ORDER BY Salary DESC)  AS DenseRank,
    SUM(Salary)  OVER (PARTITION BY DeptId)   AS DeptTotal,
    LAG(Salary)  OVER (ORDER BY HireDate)     AS PrevSalary,
    LEAD(Salary) OVER (ORDER BY HireDate)     AS NextSalary
FROM Employees;

INSERT / UPDATE / DELETE

-- INSERT single row
INSERT INTO Products (Name, Price, Stock)
VALUES ('Keyboard', 49.99, 100);

-- INSERT multiple rows
INSERT INTO Products (Name, Price)
VALUES ('Mouse', 29.99), ('Monitor', 299.99);

-- INSERT from SELECT
INSERT INTO ArchivedOrders SELECT * FROM Orders WHERE Year(OrderDate) < 2020;

-- UPDATE
UPDATE Products SET Price = Price * 1.1 WHERE Category = 'Electronics';
UPDATE p SET p.Stock = i.Qty
FROM Products p JOIN Inventory i ON p.Id = i.ProductId;

-- DELETE
DELETE FROM Products WHERE Stock = 0;
DELETE TOP (100) FROM Logs WHERE CreatedAt < DATEADD(DAY, -30, GETDATE());

-- TRUNCATE (fast delete all rows, no log per row)
TRUNCATE TABLE TempData;

-- MERGE (upsert)
MERGE INTO Products AS target
USING Staging AS source ON target.Sku = source.Sku
WHEN MATCHED THEN UPDATE SET target.Price = source.Price
WHEN NOT MATCHED THEN INSERT (Sku, Price) VALUES (source.Sku, source.Price);

DDL — Tables & Indexes

-- Create table
CREATE TABLE Employees (
    Id         INT IDENTITY(1,1) PRIMARY KEY,
    Name       NVARCHAR(100) NOT NULL,
    Email      NVARCHAR(200) NOT NULL UNIQUE,
    Salary     DECIMAL(10,2) DEFAULT 0,
    HireDate   DATE          NOT NULL,
    DeptId     INT           REFERENCES Departments(Id),
    IsActive   BIT           DEFAULT 1,
    CreatedAt  DATETIME2     DEFAULT GETDATE()
);

-- Alter table
ALTER TABLE Employees ADD PhoneNumber NVARCHAR(20) NULL;
ALTER TABLE Employees ALTER COLUMN Name NVARCHAR(200) NOT NULL;
ALTER TABLE Employees DROP COLUMN OldColumn;

-- Indexes
CREATE INDEX IX_Employees_DeptId ON Employees(DeptId);
CREATE UNIQUE INDEX UX_Employees_Email ON Employees(Email);
CREATE INDEX IX_Cov ON Orders(CustomerId) INCLUDE (OrderDate, Total);

DROP INDEX IX_Employees_DeptId ON Employees;

String Functions

SELECT LEN('Hello');              -- 5
SELECT UPPER('hello');            -- HELLO
SELECT LOWER('HELLO');            -- hello
SELECT LTRIM(RTRIM('  hi  '));    -- hi
SELECT TRIM('  hi  ');            -- hi (SQL 2017+)
SELECT LEFT('Hello', 3);          -- Hel
SELECT RIGHT('Hello', 3);         -- llo
SELECT SUBSTRING('Hello', 2, 3);  -- ell
SELECT CHARINDEX('l', 'Hello');  -- 3
SELECT REPLACE('Hello', 'l', 'r'); -- Herro
SELECT CONCAT(First, ' ', Last);
SELECT STRING_AGG(Name, ', ') FROM Tags WHERE PostId = 1;
SELECT FORMAT(1234567.89, 'N2');  -- 1,234,567.89

Date Functions

SELECT GETDATE();                            -- current datetime
SELECT GETUTCDATE();                         -- UTC
SELECT SYSDATETIME();                        -- high precision
SELECT CAST(GETDATE() AS DATE);              -- date only
SELECT YEAR(GETDATE()), MONTH(GETDATE()), DAY(GETDATE());
SELECT DATEADD(DAY, 30, GETDATE());          -- +30 days
SELECT DATEDIFF(DAY, StartDate, EndDate);    -- days between
SELECT DATENAME(WEEKDAY, GETDATE());         -- Monday
SELECT EOMONTH(GETDATE());                   -- last day of month
SELECT FORMAT(GETDATE(), 'yyyy-MM-dd');

CTEs & Subqueries

-- CTE (Common Table Expression)
WITH TopCustomers AS (
    SELECT CustomerId, SUM(Total) AS TotalSpent
    FROM Orders GROUP BY CustomerId
    HAVING SUM(Total) > 1000
)
SELECT c.Name, t.TotalSpent
FROM Customers c JOIN TopCustomers t ON c.Id = t.CustomerId;

-- Recursive CTE (e.g. org hierarchy)
WITH OrgChart AS (
    SELECT Id, Name, ManagerId, 0 AS Level
    FROM Employees WHERE ManagerId IS NULL
    UNION ALL
    SELECT e.Id, e.Name, e.ManagerId, Level + 1
    FROM Employees e JOIN OrgChart o ON e.ManagerId = o.Id
)
SELECT * FROM OrgChart;

Stored Procedures

CREATE OR ALTER PROCEDURE GetOrdersByCustomer
    @CustomerId INT,
    @FromDate   DATE = NULL
AS
BEGIN
    SET NOCOUNT ON;
    SELECT o.*, p.Name AS ProductName
    FROM Orders o
    JOIN OrderItems oi ON o.Id = oi.OrderId
    JOIN Products p ON oi.ProductId = p.Id
    WHERE o.CustomerId = @CustomerId
      AND (@FromDate IS NULL OR o.OrderDate >= @FromDate)
    ORDER BY o.OrderDate DESC;
END;

-- Execute
EXEC GetOrdersByCustomer @CustomerId = 5, @FromDate = '2024-01-01';