SQL Server — Cheat Sheet

Tags: sql, rdbms, t-sql, database, microsoft

Level: intermediate

TL;DR: Essential reference for Microsoft SQL Server, covering T-SQL features, indexing architecture, common interview questions, and performance optimization techniques.

Key concepts

Microsoft's enterprise Relational Database Management System (RDBMS).

Uses T-SQL (Transact-SQL), adding procedural programming, local variables, and error handling to standard SQL.

A clustered index dictates the physical sorting of data on disk; maximum one per table.

Non-clustered indexes are stored separately from data pages and contain pointers to actual rows.

ACID properties ensure reliable processing of all database transactions.

Execution plans visualize query processing to help identify performance bottlenecks and missing indexes.

Stored procedures cache execution plans, reducing network traffic and mitigating SQL injection risks.

CTEs (Common Table Expressions) create temporary result sets for more readable and recursive queries.

Typical questions and ready answers

Q: What is the difference between TRUNCATE and DELETE?R: TRUNCATE is a fast DDL command that resets identity columns and uses minimal transaction logging. DELETE is a DML command that logs every individual row deletion.

Q: Explain the difference between clustered and non-clustered indexes.R: A clustered index physically orders the data, allowing only one per table. Non-clustered indexes act like a book index, storing pointers to the actual data.

Q: How do you handle pagination in SQL Server?R: I use the OFFSET and FETCH NEXT clauses in conjunction with an ORDER BY statement to efficiently retrieve specific pages of data.

Q: What is the purpose of the tempdb database?R: It is a global workspace that holds temporary tables, intermediate results for sorting, and row versioning data. It is recreated every time the service restarts.

Pitfalls and common mistakes

Using SELECT * in production queries increases network I/O and can break application data mapping if schema changes.

Implicit data type conversions in JOIN or WHERE clauses prevent the query optimizer from using available indexes.

Over-indexing tables significantly degrades the performance of INSERT, UPDATE, and DELETE operations.

Leaving transactions open or forgetting appropriate isolation levels, leading to severe blocking and deadlocks.

Snippets and commandsSQL

```-- Standard CTE syntax

WITH DepartmentCTE AS (

SELECT DepartmentID, Name, GroupName

FROM HumanResources.Department

)

SELECT * FROM DepartmentCTE;

-- Window function example for pagination/ranking

SELECT

FirstName,

LastName,

ROW_NUMBER() OVER(PARTITION BY DepartmentID ORDER BY Salary DESC) as Rank

FROM Employees;

-- Enable statistics for performance tuning

SET STATISTICS IO ON;

SET STATISTICS TIME ON;

-- Create a basic non-clustered index

CREATE NONCLUSTERED INDEX IX_Employee_LastName

ON Person.Person (LastName);

```

To review

[ ] Window functions (ROW_NUMBER, RANK, DENSE_RANK, NTILE)

[ ] Transaction isolation levels (Read Uncommitted, Read Committed, Serializable)

[ ] Deadlock graph analysis and Extended Events

[ ] Table partitioning strategies