Skip to content

cheatnotes/mssql-cheatsheet

Folders and files

NameName
Last commit message
Last commit date

Latest commit

 

History

2 Commits
 
 
 
 
 
 
 
 
 
 
 
 

MICROSOFT SQL SERVER CHEATSHEET

Comprehensive MS SQL Server cheatsheet covering queries, joins, aggregations, CTEs, indexes, DDL/DML, transactions, stored procedures, functions, triggers, error handling, dynamic SQL, and performance tuning. Includes syntax examples, data types, constraints, and system functions—perfect for developers and DBAs needing quick reference for Microsoft SQL database development.

TABLE OF CONTENTS

  1. Query Basics (SELECT, FROM, WHERE)
  2. Filtering & Logic (AND, OR, IN, BETWEEN, LIKE, NULL)
  3. Sorting & Limiting (ORDER BY, TOP, OFFSET-FETCH)
  4. Aggregation & Grouping (GROUP BY, HAVING, Aggregate Functions)
  5. Joins (INNER, LEFT, RIGHT, FULL, CROSS, SELF)
  6. Set Operations (UNION, INTERSECT, EXCEPT)
  7. Subqueries & CTEs (WITH, Correlated, Scalar)
  8. Data Types
  9. DDL (CREATE, ALTER, DROP, TRUNCATE)
  10. DML (INSERT, UPDATE, DELETE, MERGE)
  11. Constraints (PK, FK, UNIQUE, CHECK, DEFAULT)
  12. Indexes (Clustered, Nonclustered, Unique)
  13. Views
  14. Stored Procedures & Functions (UDF)
  15. Triggers
  16. Transactions & Locking (BEGIN, COMMIT, ROLLBACK, Isolation Levels)
  17. Dynamic SQL & Variables
  18. Error Handling (TRY...CATCH, THROW)
  19. System Functions & Useful Queries
  20. Performance & Query Tuning (Execution Plan, STATISTICS)

1. QUERY BASICS

-- Select all columns
SELECT * FROM Employees;

-- Select specific columns
SELECT FirstName, LastName, Salary FROM Employees;

-- Rename columns (alias)
SELECT FirstName AS [First Name], Salary AS MonthlyIncome FROM Employees;

-- Literal values and calculations
SELECT Name, Price, Price * 1.08 AS PriceWithTax FROM Products;

-- Remove duplicates
SELECT DISTINCT DepartmentID FROM Employees;

2. FILTERING & LOGIC

-- WHERE clause
SELECT * FROM Employees WHERE Salary > 50000;

-- Logical operators
SELECT * FROM Employees 
WHERE DepartmentID = 3 AND (Salary > 60000 OR Bonus > 5000);

-- IN operator
SELECT * FROM Employees WHERE DepartmentID IN (2, 4, 6);

-- BETWEEN (inclusive)
SELECT * FROM Employees WHERE HireDate BETWEEN '2020-01-01' AND '2023-12-31';

-- LIKE (pattern matching)
SELECT * FROM Employees WHERE LastName LIKE 'Sm%';   -- starts with Sm
SELECT * FROM Employees WHERE FirstName LIKE '_ohn'; -- 1 char + ohn
SELECT * FROM Employees WHERE Email LIKE '%@gmail.com';
SELECT * FROM Employees WHERE Name LIKE '[A-C]%';    -- starts A,B,C

-- NULL handling (IS NULL, not = NULL)
SELECT * FROM Employees WHERE ManagerID IS NULL;

-- CASE expression (inline logic)
SELECT Name, Salary,
  CASE 
    WHEN Salary < 50000 THEN 'Low'
    WHEN Salary BETWEEN 50000 AND 80000 THEN 'Medium'
    ELSE 'High'
  END AS SalaryGrade
FROM Employees;

3. SORTING & LIMITING

-- ORDER BY (default ASC)
SELECT * FROM Products ORDER BY Price DESC, Name ASC;

-- TOP (limit rows)
SELECT TOP 10 * FROM Orders ORDER BY OrderDate DESC;
SELECT TOP 20 PERCENT * FROM Customers ORDER BY CreditLimit DESC;

-- OFFSET-FETCH (pagination)
SELECT * FROM Employees 
ORDER BY EmployeeID 
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

4. AGGREGATION & GROUPING

-- Aggregate functions (ignore NULLs except COUNT(*))
SELECT 
  COUNT(*) AS TotalRows,
  COUNT(ManagerID) AS NonNullManagers,
  AVG(Salary) AS AvgSalary,
  SUM(Salary) AS TotalPayroll,
  MIN(HireDate) AS FirstHire,
  MAX(Salary) AS HighestPaid
FROM Employees;

-- GROUP BY
SELECT DepartmentID, COUNT(*) AS EmpCount, AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY DepartmentID;

-- HAVING (filter after grouping)
SELECT DepartmentID, AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY DepartmentID
HAVING AVG(Salary) > 70000;

-- GROUP BY multiple columns
SELECT DepartmentID, JobTitle, COUNT(*) 
FROM Employees
GROUP BY DepartmentID, JobTitle;

5. JOINS

-- INNER JOIN (matching rows only)
SELECT e.Name, d.DepartmentName
FROM Employees e
INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID;

-- LEFT JOIN (all left, matching right or NULL)
SELECT e.Name, d.DepartmentName
FROM Employees e
LEFT JOIN Departments d ON e.DepartmentID = d.DepartmentID;

-- RIGHT JOIN (less common, use LEFT instead)
SELECT e.Name, d.DepartmentName
FROM Employees e
RIGHT JOIN Departments d ON e.DepartmentID = d.DepartmentID;

-- FULL OUTER JOIN (all rows from both)
SELECT e.Name, d.DepartmentName
FROM Employees e
FULL JOIN Departments d ON e.DepartmentID = d.DepartmentID;

-- CROSS JOIN (Cartesian product)
SELECT e.Name, p.ProductName FROM Employees e CROSS JOIN Products p;

-- SELF JOIN (same table, different aliases)
SELECT e1.Name AS Employee, e2.Name AS Manager
FROM Employees e1
LEFT JOIN Employees e2 ON e1.ManagerID = e2.EmployeeID;

6. SET OPERATIONS

-- UNION (removes duplicates, columns must match)
SELECT FirstName, LastName FROM ActiveEmployees
UNION
SELECT FirstName, LastName FROM FormerEmployees;

-- UNION ALL (keeps duplicates, faster)
SELECT City FROM Customers
UNION ALL
SELECT City FROM Suppliers;

-- INTERSECT (rows in both)
SELECT ProductID FROM Orders2023
INTERSECT
SELECT ProductID FROM Orders2024;

-- EXCEPT (rows in first but not second)
SELECT ProductID FROM AllProducts
EXCEPT
SELECT ProductID FROM DiscontinuedProducts;

7. SUBQUERIES & CTES

-- Scalar subquery (single value)
SELECT Name, Salary,
  (SELECT AVG(Salary) FROM Employees) AS CompanyAvg
FROM Employees;

-- Correlated subquery (reference outer query)
SELECT e1.Name, e1.Salary
FROM Employees e1
WHERE Salary > (SELECT AVG(Salary) FROM Employees e2 WHERE e2.DepartmentID = e1.DepartmentID);

-- Subquery with IN/EXISTS
SELECT Name FROM Products
WHERE ProductID IN (SELECT ProductID FROM OrderDetails WHERE Quantity > 10);

SELECT Name FROM Products p
WHERE EXISTS (SELECT 1 FROM OrderDetails od WHERE od.ProductID = p.ProductID);

-- CTE (Common Table Expression)
WITH HighValueOrders AS (
  SELECT OrderID, SUM(Amount) AS TotalAmount
  FROM Orders
  GROUP BY OrderID
  HAVING SUM(Amount) > 10000
)
SELECT o.OrderID, c.CustomerName, h.TotalAmount
FROM Orders o
JOIN HighValueOrders h ON o.OrderID = h.OrderID
JOIN Customers c ON o.CustomerID = c.CustomerID;

-- Recursive CTE (hierarchy)
WITH OrgHierarchy AS (
  SELECT EmployeeID, Name, ManagerID, 0 AS Level
  FROM Employees WHERE ManagerID IS NULL
  UNION ALL
  SELECT e.EmployeeID, e.Name, e.ManagerID, oh.Level + 1
  FROM Employees e
  INNER JOIN OrgHierarchy oh ON e.ManagerID = oh.EmployeeID
)
SELECT * FROM OrgHierarchy;

8. DATA TYPES

Category Examples
Exact numeric INT, BIGINT, SMALLINT, TINYINT, DECIMAL(18,2), NUMERIC, MONEY
Approximate numeric FLOAT, REAL
Date/Time DATE, DATETIME, DATETIME2, SMALLDATETIME, TIME, DATETIMEOFFSET
Strings VARCHAR(n), CHAR(n), NVARCHAR(n) (Unicode), TEXT (deprecated)
Binary BINARY(n), VARBINARY(n), IMAGE (deprecated)
Other UNIQUEIDENTIFIER (GUID), XML, JSON (nvarchar), BIT (boolean)

9. DDL (DATA DEFINITION LANGUAGE)

-- CREATE DATABASE
CREATE DATABASE CompanyDB;
GO

-- CREATE TABLE
CREATE TABLE Employees (
  EmployeeID INT IDENTITY(1,1) PRIMARY KEY,
  FirstName NVARCHAR(50) NOT NULL,
  LastName NVARCHAR(50) NOT NULL,
  Email VARCHAR(100) UNIQUE,
  HireDate DATE DEFAULT GETDATE(),
  Salary DECIMAL(10,2) CHECK (Salary > 0)
);

-- ALTER TABLE (add/modify/drop column)
ALTER TABLE Employees ADD MiddleName NVARCHAR(50);
ALTER TABLE Employees ALTER COLUMN Salary DECIMAL(12,2);
ALTER TABLE Employees DROP COLUMN MiddleName;

-- DROP TABLE (permanent)
DROP TABLE Employees;

-- TRUNCATE (remove all rows, reset identity)
TRUNCATE TABLE Employees;

10. DML (DATA MANIPULATION LANGUAGE)

-- INSERT single row
INSERT INTO Employees (FirstName, LastName, Email, Salary)
VALUES ('John', 'Doe', 'john@example.com', 65000);

-- INSERT multiple rows
INSERT INTO Products (Name, Price) VALUES
('Laptop', 1200),
('Mouse', 25),
('Keyboard', 45);

-- INSERT from SELECT
INSERT INTO HighEarners (EmployeeID, Name, Salary)
SELECT EmployeeID, FirstName, Salary FROM Employees WHERE Salary > 80000;

-- UPDATE
UPDATE Employees SET Salary = Salary * 1.05 WHERE DepartmentID = 2;

-- UPDATE with JOIN
UPDATE e SET e.Salary = e.Salary * 1.10
FROM Employees e
INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID
WHERE d.Location = 'New York';

-- DELETE (with or without WHERE)
DELETE FROM Employees WHERE TerminationDate IS NOT NULL;

-- MERGE (upsert)
MERGE INTO TargetTable AS T
USING SourceTable AS S
ON T.ID = S.ID
WHEN MATCHED THEN UPDATE SET T.Name = S.Name
WHEN NOT MATCHED THEN INSERT (ID, Name) VALUES (S.ID, S.Name);

11. CONSTRAINTS

-- PRIMARY KEY (unique + clustered by default)
ALTER TABLE Employees ADD CONSTRAINT PK_EmployeeID PRIMARY KEY (EmployeeID);

-- FOREIGN KEY
ALTER TABLE Orders ADD CONSTRAINT FK_Orders_Customers 
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
ON DELETE CASCADE | SET NULL | SET DEFAULT | NO ACTION;

-- UNIQUE
ALTER TABLE Users ADD CONSTRAINT UQ_Username UNIQUE (Username);

-- CHECK
ALTER TABLE Products ADD CONSTRAINT CK_Price CHECK (Price >= 0);

-- DEFAULT
ALTER TABLE Orders ADD CONSTRAINT DF_OrderDate DEFAULT GETDATE() FOR OrderDate;

-- Drop constraint
ALTER TABLE Orders DROP CONSTRAINT DF_OrderDate;

12. INDEXES

-- Clustered (only one, physical order)
CREATE CLUSTERED INDEX IX_Employees_EmployeeID ON Employees(EmployeeID);

-- Nonclustered (multiple)
CREATE NONCLUSTERED INDEX IX_Employees_LastName ON Employees(LastName);
CREATE INDEX IX_Salary_Include ON Employees(Salary) INCLUDE (FirstName, LastName);

-- Composite index
CREATE INDEX IX_Name ON Employees(LastName, FirstName);

-- Unique index (like unique constraint)
CREATE UNIQUE INDEX UIX_Email ON Employees(Email);

-- Filtered index (partial)
CREATE INDEX IX_ActiveEmployees ON Employees(Lastname) WHERE TerminationDate IS NULL;

-- Drop index
DROP INDEX IX_Employees_LastName ON Employees;

-- Rebuild/Reorganize
ALTER INDEX IX_Employees_LastName ON Employees REBUILD;
ALTER INDEX IX_Employees_LastName ON Employees REORGANIZE;

13. VIEWS

-- Simple view (read-only, no ORDER BY allowed)
CREATE VIEW vw_ActiveEmployees AS
SELECT EmployeeID, FirstName, LastName, Salary
FROM Employees WHERE TerminationDate IS NULL;
GO
SELECT * FROM vw_ActiveEmployees;

-- Indexed view (must be schemabound, unique clustered)
CREATE VIEW vw_SalesSummary WITH SCHEMABINDING AS
SELECT ProductID, SUM(Quantity) AS TotalQty, COUNT_BIG(*) AS Cnt
FROM dbo.OrderDetails
GROUP BY ProductID;
GO
CREATE UNIQUE CLUSTERED INDEX IX_SalesSummary ON vw_SalesSummary(ProductID);

-- Alter/Drop
ALTER VIEW vw_ActiveEmployees AS ...;
DROP VIEW vw_ActiveEmployees;

14. STORED PROCEDURES & FUNCTIONS

-- Stored procedure (can modify data, no return value required)
CREATE PROCEDURE usp_GetEmployeesByDept
  @DepartmentID INT,
  @MinSalary DECIMAL(10,2) = 0
AS
BEGIN
  SET NOCOUNT ON;
  SELECT * FROM Employees 
  WHERE DepartmentID = @DepartmentID AND Salary >= @MinSalary;
END;
GO
EXEC usp_GetEmployeesByDept @DepartmentID = 3, @MinSalary = 50000;

-- Scalar function (returns single value)
CREATE FUNCTION fn_GetFullName(@FirstName NVARCHAR(50), @LastName NVARCHAR(50))
RETURNS NVARCHAR(101)
AS
BEGIN
  RETURN @FirstName + ' ' + @LastName;
END;
GO
SELECT dbo.fn_GetFullName(FirstName, LastName) FROM Employees;

-- Table-valued function (inline)
CREATE FUNCTION fn_GetEmployeesByDept(@DeptID INT)
RETURNS TABLE
AS
RETURN (SELECT * FROM Employees WHERE DepartmentID = @DeptID);
GO
SELECT * FROM fn_GetEmployeesByDept(3);

-- Function restrictions: no side effects (no INSERT/UPDATE/DELETE), no dynamic SQL

15. TRIGGERS

-- AFTER trigger (DML)
CREATE TRIGGER trg_AuditSalaryChange
ON Employees
AFTER UPDATE
AS
BEGIN
  IF UPDATE(Salary)
  BEGIN
    INSERT INTO SalaryAudit (EmployeeID, OldSalary, NewSalary, ChangeDate)
    SELECT i.EmployeeID, d.Salary, i.Salary, GETDATE()
    FROM inserted i
    INNER JOIN deleted d ON i.EmployeeID = d.EmployeeID;
  END
END;

-- INSTEAD OF trigger (view updates)
CREATE TRIGGER trg_UpdateView ON vw_Employees
INSTEAD OF UPDATE
AS
BEGIN
  UPDATE Employees SET ... FROM inserted ...;
END;

-- Disable/Enable trigger
DISABLE TRIGGER trg_AuditSalaryChange ON Employees;
ENABLE TRIGGER trg_AuditSalaryChange ON Employees;

16. TRANSACTIONS & LOCKING

-- Basic transaction
BEGIN TRANSACTION;
  UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 1;
  UPDATE Accounts SET Balance = Balance + 100 WHERE AccountID = 2;
  -- Check for errors
  IF @@ERROR <> 0
    ROLLBACK;
  ELSE
    COMMIT;

-- Savepoint
BEGIN TRAN
  INSERT INTO Orders ...;
  SAVE TRAN SavePoint1
  UPDATE Orders ...;
  IF @@ERROR <> 0 ROLLBACK TRAN SavePoint1;
COMMIT;

-- Isolation levels
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;  -- Dirty reads
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;    -- Default
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;   -- Prevents non-repeatable reads
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;      -- No phantom reads
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;          -- Version-based

-- Lock hints (table-level)
SELECT * FROM Orders WITH (NOLOCK);  -- Dirty read
SELECT * FROM Orders WITH (UPDLOCK); -- Update lock

17. DYNAMIC SQL & VARIABLES

-- Variables
DECLARE @Counter INT = 0;
DECLARE @TableName NVARCHAR(100) = 'Employees';
DECLARE @SQL NVARCHAR(MAX);

-- Dynamic SQL (execute)
SET @SQL = 'SELECT * FROM ' + @TableName;
EXEC sp_executesql @SQL;

-- Parameterized dynamic SQL (safe)
SET @SQL = 'SELECT * FROM Employees WHERE Salary > @MinSal';
EXEC sp_executesql @SQL, N'@MinSal DECIMAL', @MinSal = 50000;

-- Temporary tables
CREATE TABLE #TempEmployees (ID INT, Name NVARCHAR(100)); -- Local temp
CREATE TABLE ##GlobalTemp (ID INT);                       -- Global temp
DROP TABLE #TempEmployees;

-- Table variable
DECLARE @TableVar TABLE (ID INT, Name NVARCHAR(100));
INSERT INTO @TableVar SELECT EmployeeID, FirstName FROM Employees;

18. ERROR HANDLING

-- TRY...CATCH
BEGIN TRY
  INSERT INTO Employees (EmployeeID, FirstName) VALUES (1, 'John');
END TRY
BEGIN CATCH
  SELECT 
    ERROR_NUMBER() AS ErrorNumber,
    ERROR_MESSAGE() AS ErrorMessage,
    ERROR_SEVERITY() AS Severity,
    ERROR_STATE() AS State,
    ERROR_LINE() AS ErrorLine,
    ERROR_PROCEDURE() AS ProcName;
END CATCH

-- Custom error with THROW
IF @Amount < 0
  THROW 50001, 'Amount cannot be negative', 1;

-- RAISERROR (legacy, but still used)
RAISERROR('Custom error: %s', 16, 1, 'Insufficient funds');

19. SYSTEM FUNCTIONS & USEFUL QUERIES

-- String functions
SELECT LEN('Hello'), UPPER('test'), LOWER('TEST'), SUBSTRING('ABCDEF', 2, 3);
SELECT CHARINDEX('l', 'Hello'), REPLACE('Hello', 'l', 'x'), RTRIM(LTRIM('  hi  '));

-- Date functions
SELECT GETDATE(), SYSDATETIME(), CURRENT_TIMESTAMP;
SELECT DATEADD(DAY, 7, GETDATE()), DATEDIFF(YEAR, HireDate, GETDATE());
SELECT YEAR(GETDATE()), MONTH(GETDATE()), DAY(GETDATE()), DATEPART(WEEKDAY, GETDATE());

-- Conversion
SELECT CAST('123' AS INT), CONVERT(INT, '456'), TRY_CAST('abc' AS INT); -- NULL if fails
SELECT FORMAT(GETDATE(), 'yyyy-MM-dd'), PARSE('01/02/2023' AS DATE USING 'en-US');

-- System queries
-- List all tables
SELECT * FROM INFORMATION_SCHEMA.TABLES;
-- Get table schema
SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE 
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Employees';
-- Index usage
SELECT * FROM sys.dm_db_index_usage_stats WHERE database_id = DB_ID();
-- Running queries
SELECT text, status, command, blocking_session_id 
FROM sys.dm_exec_requests CROSS APPLY sys.dm_exec_sql_text(sql_handle);

20. PERFORMANCE & QUERY TUNING

-- Show execution plan (text)
SET SHOWPLAN_TEXT ON;
GO
SELECT * FROM Employees WHERE LastName = 'Smith';
GO
SET SHOWPLAN_TEXT OFF;

-- Actual execution plan (statistics)
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT * FROM Employees;
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

-- Common tuning techniques
-- 1. Avoid SELECT * (use only needed columns)
-- 2. Use EXISTS instead of IN for large subs
-- 3. Avoid functions on indexed columns: WHERE YEAR(DateCol) = 2023  (bad)
--    Use: WHERE DateCol >= '2023-01-01' AND DateCol < '2024-01-01'
-- 4. Use appropriate indexes
-- 5. Update statistics: UPDATE STATISTICS Employees;
-- 6. Rebuild fragmented indexes
-- 7. Use WITH (RECOMPILE) for parameter sniffing issues
CREATE PROCEDURE usp_GetOrder @ID INT WITH RECOMPILE AS ...
-- 8. Use Query Store (SQL Server 2016+)
ALTER DATABASE CurrentDB SET QUERY_STORE = ON;

-- Identify missing indexes
SELECT * FROM sys.dm_db_missing_index_details;

-- Identify expensive queries
SELECT TOP 10 query_hash, SUM(total_worker_time) AS total_cpu
FROM sys.dm_exec_query_stats
GROUP BY query_hash ORDER BY total_cpu DESC;

Pro Tips:

  • Use ; as statement terminator (ANSI standard)
  • GO separates batches (SSMS-specific)
  • N prefix for Unicode strings: N'Café'
  • Always backup before DELETE/UPDATE without WHERE
  • Use BEGIN TRAN ... ROLLBACK to test risky DML first

About

Comprehensive MS SQL Server cheatsheet covering queries, joins, aggregations, CTEs, indexes, DDL/DML, transactions, stored procedures, functions, triggers, error handling, dynamic SQL, and performance tuning. Includes syntax examples, data types, constraints, and system functions.

Topics

Resources

License

Code of conduct

Contributing

Security policy

Stars

Watchers

Forks

Releases

No releases published

Packages

 
 
 

Contributors

Generated from cheatnotes/cheatnotes