Microsoft SQL Server Cheat Sheet
T-SQL essentials for Microsoft SQL Server covering DDL/DML, clustered vs nonclustered indexes, MERGE statements, and query plan analysis.
2 PagesIntermediateMar 2, 2026
T-SQL Basics
Connecting and browsing databases.
sql
-- Connect via sqlcmd-- sqlcmd -S localhost -U sa -P YourPasswordSELECT name FROM sys.databases;USE MyDb;GOSELECT TOP 10 * FROM Users;
DDL, DML & MERGE
Creating tables and upserting rows.
sql
CREATE TABLE Users ( Id INT IDENTITY(1,1) PRIMARY KEY, Email NVARCHAR(255) NOT NULL UNIQUE, CreatedAt DATETIME2 DEFAULT SYSUTCDATETIME());INSERT INTO Users (Email) VALUES ('[email protected]');MERGE INTO Users AS targetUSING (SELECT 1 AS Id, '[email protected]' AS Email) AS srcON target.Id = src.IdWHEN MATCHED THEN UPDATE SET Email = src.EmailWHEN NOT MATCHED THEN INSERT (Email) VALUES (src.Email);
Indexes & Query Plans
Index types and inspecting IO cost.
sql
CREATE CLUSTERED INDEX IX_Users_Id ON Users(Id);CREATE NONCLUSTERED INDEX IX_Users_Email ON Users(Email);SET STATISTICS IO ON;SELECT * FROM Users WHERE Email = '[email protected]';
Core Concepts
Terminology specific to SQL Server.
- Clustered index- determines the physical row order on disk; only one per table
- Nonclustered index- a separate structure with pointers back to the data row
- IDENTITY(seed, increment)- auto-incrementing column, SQL Server's AUTO_INCREMENT equivalent
- T-SQL- Microsoft's SQL dialect adding variables, TRY/CATCH, and control flow
- Schema- a namespace within a database, e.g. dbo.Users
- GO- a batch separator recognized by SSMS/sqlcmd, not part of T-SQL itself
Window Functions & Ranking
ROW_NUMBER, RANK, and running totals with OVER/PARTITION BY.
sql
SELECT DeptId, Salary, ROW_NUMBER() OVER (PARTITION BY DeptId ORDER BY Salary DESC) AS RowNum, RANK() OVER (PARTITION BY DeptId ORDER BY Salary DESC) AS SalaryRank, LAG(Salary) OVER (PARTITION BY DeptId ORDER BY Salary DESC) AS PrevSalary, SUM(Salary) OVER (PARTITION BY DeptId ORDER BY Salary DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotalFROM Employees;-- Top 3 earners per department without a self-joinWITH Ranked AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY DeptId ORDER BY Salary DESC) AS rn FROM Employees)SELECT * FROM Ranked WHERE rn <= 3;
Recursive CTEs
Walking hierarchical data (org charts, bill-of-materials) with a self-referencing CTE.
sql
WITH OrgChart AS ( SELECT EmployeeId, ManagerId, Name, 0 AS Depth FROM Employees WHERE ManagerId IS NULL UNION ALL SELECT e.EmployeeId, e.ManagerId, e.Name, o.Depth + 1 FROM Employees e INNER JOIN OrgChart o ON e.ManagerId = o.EmployeeId)SELECT REPLICATE(' ', Depth) + Name AS Indented, DepthFROM OrgChartOPTION (MAXRECURSION 100);
TRY/CATCH & Transactions
Structured error handling with THROW and explicit transaction control.
sql
BEGIN TRY BEGIN TRANSACTION; UPDATE Accounts SET Balance = Balance - 100 WHERE Id = 1; UPDATE Accounts SET Balance = Balance + 100 WHERE Id = 2; IF (SELECT Balance FROM Accounts WHERE Id = 1) < 0 THROW 51000, 'Insufficient funds', 1; COMMIT TRANSACTION;END TRYBEGIN CATCH IF XACT_STATE() <> 0 ROLLBACK TRANSACTION; DECLARE @Msg NVARCHAR(4000) = ERROR_MESSAGE(); DECLARE @Sev INT = ERROR_SEVERITY(); DECLARE @State INT = ERROR_STATE(); RAISERROR(@Msg, @Sev, @State);END CATCH;
Temp Tables, Table Variables & TVPs
Choosing the right transient-storage tool and passing structured data into procedures.
sql
-- Local temp table: statistics tracked, good for large intermediate setsCREATE TABLE #Staging (Id INT PRIMARY KEY, Amount DECIMAL(10,2));INSERT INTO #Staging SELECT Id, Amount FROM Orders WHERE Amount > 1000;-- Table variable: no statistics, scoped to the batch, less locking overhead for small setsDECLARE @Recent TABLE (Id INT, OrderDate DATETIME2);INSERT INTO @Recent SELECT Id, OrderDate FROM Orders WHERE OrderDate > DATEADD(DAY, -7, SYSUTCDATETIME());-- Table-valued parameter: pass a set of rows into a stored procedureCREATE TYPE IdListType AS TABLE (Id INT PRIMARY KEY);GOCREATE PROCEDURE GetOrdersByIds (@Ids IdListType READONLY)AS SELECT o.* FROM Orders o INNER JOIN @Ids i ON o.Id = i.Id;GO
Transaction Isolation Levels & Locking Hints
Concurrency control choices and their trade-offs in SQL Server.
- READ COMMITTED- default; readers block on uncommitted writes unless RCSI is enabled
- READ COMMITTED SNAPSHOT (RCSI)- database-level option giving readers a versioned snapshot instead of blocking on writers
- SNAPSHOT- session-level optimistic isolation; detects write-write conflicts at commit time
- SERIALIZABLE- strongest isolation; range locks prevent phantom reads but increase blocking
- WITH (NOLOCK)- reads uncommitted data (dirty reads); avoid on financial/critical paths
- WITH (ROWLOCK) / (TABLOCK)- hints forcing a specific lock granularity, overriding the optimizer's choice
- Deadlock (error 1205)- SQL Server auto-detects and kills the cheaper transaction; always wrap retries in TRY/CATCH
Pro Tip
Use SET STATISTICS IO ON together with the actual (not estimated) execution plan to see real logical page reads — a query can look cheap in the estimated plan while doing far more work than expected.
Was this cheat sheet helpful?
Explore Topics
#MicrosoftSQLServer#MicrosoftSQLServerCheatSheet#Database#Intermediate#TSQLBasics#DDLDMLMERGE#IndexesQueryPlans#CoreConcepts#Databases#Git#CheatSheet#SkillVeris
