SQL SERVER

Complete Study Guide

From Fundamentals to Advanced Features
149 Topics | 10 Parts | T-SQL Reference & Best Practices
Table of Contents

Part Title Key Topics

1 Database & Table SSMS, CREATE/ALTER/DROP DB, Tables, Constraints, Identity, Keys
Fundamentals

2 Querying Data SELECT, DISTINCT, WHERE, GROUP BY, HAVING, ORDER BY, NULL handling

3 Joins INNER, LEFT, RIGHT, FULL, CROSS, SELF, Advanced Joins, Join Performance

4 Stored Procedures & Functions SP basics, Output Params, Scalar/Inline/Multi-Statement UDFs

5 Indexes & Views Clustered, Non-Clustered, Unique, Filtered, Views, Indexed Views

6 Triggers DML AFTER/INSTEAD OF, DDL Triggers, Logon Triggers, EVENTDATA()

7 CTEs, Subqueries & Set Ops Subqueries, CTEs, Recursive CTEs, UNION, PIVOT, APPLY, SELECT INTO

8 Transactions & Error Handling ACID, Isolation Levels, TRY/CATCH, Deadlocks, Blocking Queries

9 Window Functions ROW_NUMBER, RANK, DENSE_RANK, NTILE, LEAD/LAG,

FIRST/LAST_VALUE, ROWS vs RANGE

10 Advanced Topics Dynamic SQL, Temp Tables, Normalization, Sequences, GUIDs, MERGE,
Cursors, TVP, Grouping Sets
 PART 1

Database & Table Fundamentals

Chapter 1: Connecting & Managing Databases

SQL Server Management Studio (SSMS) is the primary GUI tool. It connects to Database Engine, SSAS, SSRS, or
SSIS. SSMS is the client -- SQL Server (Database Engine) is the server, usually on a dedicated machine.

1.1 Connecting via SSMS

• Server Type: Database Engine (most common)
• Server Name: (local) | . (period) | [Link] | ComputerName | IP Address
• Authentication: Windows Authentication (no password needed) or SQL Server Authentication (mixed mode)

1.2 Create, Alter, and Drop Databases

-- Create
CREATE DATABASE SampleDB

-- Rename
ALTER DATABASE SampleDB MODIFY NAME = NewSampleDB
EXECUTE sp_renameDB 'OldName', 'NewName' -- alternative

-- Drop
DROP DATABASE NewSampleDB

-- Force drop: disconnect all users first

ALTER DATABASE NewSampleDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE NewSampleDB
-- ROLLBACK IMMEDIATE: rolls back open transactions and closes connections

NOTE

Creating a database generates two files:

.MDF -- Master Data File: stores actual table and index data

.LDF -- Log Data File: transaction log used for recovery

System databases (master, model, msdb, tempdb) CANNOT be dropped.

Chapter 2: Tables & Constraints
2.1 Creating and Altering Tables
CREATE TABLE tblGender (
ID INT NOT NULL PRIMARY KEY,
Gender NVARCHAR(10) NOT NULL
)

CREATE TABLE tblPerson (

ID INT NOT NULL PRIMARY KEY,
Name NVARCHAR(50) NOT NULL,
Email NVARCHAR(50) NOT NULL,
GenderID INT FOREIGN KEY REFERENCES tblGender(ID)
)

-- Add a column
ALTER TABLE tblPerson ADD City NVARCHAR(50)

-- Drop a column
ALTER TABLE tblPerson DROP COLUMN City

-- Modify column type

ALTER TABLE tblPerson ALTER COLUMN Salary BIGINT

2.2 Constraint Types

Constraint Purpose Example

PRIMARY KEY Uniquely identifies each row; no ID INT NOT NULL PRIMARY KEY
NULLs allowed

FOREIGN KEY Enforces referential integrity to FOREIGN KEY (GenderID) REFERENCES tblGender(ID)
another table

DEFAULT Provides value when none is supplied ALTER TABLE t ADD CONSTRAINT DF_Name DEFAULT
on INSERT 'London' FOR City

CHECK Validates data against a boolean ALTER TABLE t ADD CONSTRAINT CK_Age CHECK
condition (Age >= 0 AND Age <= 150)

UNIQUE Enforces uniqueness; allows one NULL ALTER TABLE t ADD CONSTRAINT UQ_Email UNIQUE
(Email)

NOT NULL Prevents NULL values in a column Name NVARCHAR(50) NOT NULL

2.3 Cascading Referential Integrity

Option Behavior When Parent Row Is Deleted or Updated

NO ACTION (default) Error raised; child rows prevent the parent from being deleted/updated

CASCADE Child rows automatically deleted or updated along with parent

SET NULL Foreign key column in child rows set to NULL

SET DEFAULT Foreign key column in child rows set to its default value

2.4 Identity Column

-- IDENTITY(seed, increment) auto-generates sequential integers
CREATE TABLE tblPerson (
PersonID INT IDENTITY(1,1) PRIMARY KEY,
Name NVARCHAR(50)
)

-- Three ways to get the last generated identity value:

SELECT SCOPE_IDENTITY() -- last identity in CURRENT SCOPE (safest)
SELECT @@IDENTITY -- last identity in current CONNECTION (trigger-
affected)
SELECT IDENT_CURRENT('tblPerson') -- last identity for TABLE regardless of session

-- Allow explicit identity value (override auto-generation)

SET IDENTITY_INSERT tblPerson ON
INSERT INTO tblPerson (PersonID, Name) VALUES (100, John)
SET IDENTITY_INSERT tblPerson OFF

-- Re-seed (reset the identity counter)

DBCC CHECKIDENT(tblPerson, RESEED, 0)

INTERVIEW Q&A

Q: Difference between SCOPE_IDENTITY(), @@IDENTITY, IDENT_CURRENT()?

SCOPE_IDENTITY() -- last identity in CURRENT scope; unaffected by triggers

@@IDENTITY -- last identity in current session across any scope (trigger-affected)

IDENT_CURRENT(tbl) -- last identity for that TABLE regardless of session or scope

BEST PRACTICE: Always use SCOPE_IDENTITY() in stored procedures.

If a trigger fires an INSERT on another identity table, @@IDENTITY returns THAT value,

while SCOPE_IDENTITY() returns the value from your original INSERT.

 PART 2

Querying Data

Chapter 3: SELECT Statements

3.1 Core SELECT Patterns
SELECT Name, Salary, Gender FROM tblEmployee

-- Column aliases
SELECT Name AS EmployeeName, Salary AS Annual FROM tblEmployee

-- DISTINCT: eliminate duplicate rows

SELECT DISTINCT City FROM tblEmployee

-- TOP: limit rows

SELECT TOP 5 Name, Salary FROM tblEmployee ORDER BY Salary DESC
SELECT TOP 10 PERCENT Name FROM tblEmployee ORDER BY Salary DESC

-- WHERE filtering
SELECT * FROM tblEmployee WHERE Gender = 'Male' AND Salary > 5000
SELECT * FROM tblEmployee WHERE City IN ('London', 'Paris', 'Berlin')
SELECT * FROM tblEmployee WHERE Salary BETWEEN 3000 AND 8000

-- LIKE pattern matching

SELECT * FROM tblEmployee WHERE Name LIKE 'J%' -- starts with J
SELECT * FROM tblEmployee WHERE Name LIKE '%son' -- ends with son
SELECT * FROM tblEmployee WHERE Name LIKE '%ar%' -- contains ar
SELECT * FROM tblEmployee WHERE Name LIKE '_ohn' -- any char + ohn

SELECT Name, Salary FROM tblEmployee ORDER BY Salary DESC, Name ASC

3.2 GROUP BY and Aggregates

SELECT
Gender,
COUNT(*) AS TotalCount,
SUM(Salary) AS TotalSalary,
AVG(Salary) AS AvgSalary,
MIN(Salary) AS MinSalary,
MAX(Salary) AS MaxSalary
FROM tblEmployee
GROUP BY Gender

-- HAVING: filter AFTER grouping

SELECT DeptID, COUNT(*) AS EmpCount
FROM tblEmployee
 GROUP BY DeptID
HAVING COUNT(*) > 2

-- WHERE filters rows BEFORE grouping; HAVING filters groups AFTER aggregation
-- WHERE cannot use aggregate functions; HAVING can

3.3 NULL Handling

-- IS NULL / IS NOT NULL
SELECT * FROM tblEmployee WHERE ManagerID IS NULL

-- ISNULL(check, replacement) -- SQL Server specific

SELECT ISNULL(MiddleName, 'N/A') AS MiddleName FROM tblEmployee

-- NULLIF(expr1, expr2) -- returns NULL if both equal

SELECT NULLIF(10, 10) -- returns NULL
SELECT NULLIF(10, 20) -- returns 10

-- COALESCE(v1, v2, ...) -- returns first non-NULL; ANSI standard

SELECT COALESCE(HomePhone, MobilePhone, WorkPhone, 'No Phone') AS Phone
FROM tblEmployee
-- COALESCE accepts N params; ISNULL accepts only 2
 PART 3

Joins

Chapter 4: SQL Server Joins

Joins retrieve data from two or more related tables. The ON clause specifies the join condition, typically
matching primary and foreign keys.

4.1 Join Types

Join Type Returns NULL behavior

INNER JOIN Only rows with matching values in BOTH tables No NULLs from join itself

LEFT JOIN (LEFT OUTER) ALL rows from LEFT table + matched rows from Right-side columns are NULL
RIGHT where no match

RIGHT JOIN (RIGHT OUTER) ALL rows from RIGHT table + matched rows from Left-side columns are NULL
LEFT where no match

FULL JOIN (FULL OUTER) ALL rows from BOTH tables NULLs on either side where no
match found

CROSS JOIN Cartesian product: every row paired with every N/A
row (no ON clause)

SELF JOIN A table joined to itself using two different aliases Depends on join type used

4.2 Join Examples

-- INNER JOIN: only matching rows
SELECT [Link], [Link]
FROM tblEmployee E
INNER JOIN tblDepartment D ON [Link] = [Link]

-- LEFT JOIN: all employees (even without a department)

SELECT [Link], [Link]
FROM tblEmployee E
LEFT JOIN tblDepartment D ON [Link] = [Link]

-- RIGHT JOIN: all departments (even without employees)

SELECT [Link], [Link]
FROM tblEmployee E
RIGHT JOIN tblDepartment D ON [Link] = [Link]

-- FULL OUTER JOIN

 SELECT [Link], [Link]
FROM tblEmployee E
FULL OUTER JOIN tblDepartment D ON [Link] = [Link]

-- CROSS JOIN: all combinations

SELECT [Link], [Link]
FROM tblEmployee E CROSS JOIN tblDepartment D

-- SELF JOIN: employee with their manager (both in same table)

SELECT [Link] AS Employee, [Link] AS Manager
FROM tblEmployee E
LEFT JOIN tblEmployee M ON [Link] = [Link]

4.3 Advanced Joins: Non-Matching Rows

-- Employees with NO department (LEFT table non-matches)
SELECT [Link] FROM tblEmployee E
LEFT JOIN tblDepartment D ON [Link] = [Link]
WHERE [Link] IS NULL

-- Departments with NO employees (RIGHT table non-matches)

SELECT [Link] FROM tblEmployee E
RIGHT JOIN tblDepartment D ON [Link] = [Link]
WHERE [Link] IS NULL

-- Rows in ONLY ONE table (non-matches from BOTH sides)

SELECT [Link], [Link]
FROM tblEmployee E
FULL OUTER JOIN tblDepartment D ON [Link] = [Link]
WHERE [Link] IS NULL OR [Link] IS NULL

INTERVIEW Q&A

Q: INNER JOIN vs LEFT JOIN?

INNER JOIN: returns rows only where both tables have a match.

LEFT JOIN: returns ALL rows from left + matched rows from right (NULLs where no match).

Q: What is a SELF JOIN and when is it used?

A SELF JOIN joins a table to itself using two different aliases.

Classic use: Employee/Manager hierarchy where both are rows in the same table.

Use LEFT JOIN (not INNER) to include root records that have no manager.

Q: What is the difference between WHERE [Link] IS NULL vs WHERE [Link] IS NOT NULL?

WHERE [Link] IS NULL after LEFT JOIN: returns only NON-matching rows (orphaned left rows).

WHERE [Link] IS NOT NULL after LEFT JOIN: same as INNER JOIN result.
 PART 4

Stored Procedures & Functions

Chapter 5: Stored Procedures

A stored procedure is a precompiled, named group of T-SQL statements. The execution plan is compiled once,
cached, and reused -- reducing overhead compared to ad-hoc queries.

5.1 Basic Stored Procedures

-- Simple SP (no parameters)
CREATE PROCEDURE spGetAllEmployees
AS BEGIN
SELECT Name, Gender FROM tblEmployee
END

EXEC spGetAllEmployees

-- SP with INPUT parameter

CREATE PROCEDURE spGetByGender @Gender NVARCHAR(20)
AS BEGIN
SELECT * FROM tblEmployee WHERE Gender = @Gender
END

EXEC spGetByGender 'Male'

EXEC spGetByGender @Gender = 'Female' -- named parameter

-- SP with OUTPUT parameter

CREATE PROCEDURE spCountByGender
@Gender NVARCHAR(20),
@EmployeeCount INT OUTPUT
AS BEGIN
SELECT @EmployeeCount = COUNT(ID)
FROM tblEmployee WHERE Gender = @Gender
END

DECLARE @Total INT

EXEC spCountByGender @Gender='Male', @EmployeeCount=@Total OUTPUT
SELECT @Total AS MaleCount

-- Optional parameters using defaults

CREATE PROCEDURE spSearch
@Name NVARCHAR(50) = NULL,
@Gender NVARCHAR(10) = NULL
AS BEGIN
SELECT * FROM tblEmployee
WHERE (@Name IS NULL OR Name LIKE @Name)
 AND (@Gender IS NULL OR Gender = @Gender)
END

-- Modify SP
ALTER PROCEDURE spGetAllEmployees AS BEGIN ...

-- Drop SP
DROP PROCEDURE spGetAllEmployees

5.2 Output Parameters vs Return Values

Feature Output Parameters RETURN Values

Data type Any SQL data type INTEGER only

Multiple values Yes -- can have multiple OUTPUT params No -- single RETURN value only

NULL capable Yes Yes

Typical purpose Return computed result data to caller Signal success (0) or error code (non-
zero)

5.3 Advantages of Stored Procedures

• Execution Plan Reuse: Plans compiled once and cached; no recompilation on each call
• Reduces Network Traffic: Only the procedure name travels over the network, not full SQL
• Security: Grant EXECUTE without giving direct table SELECT/INSERT/UPDATE/DELETE access
• Prevents SQL Injection: Parameterized SPs treat inputs as data, not executable code
• Centralized Logic: Business rules in one place -- change SP once and all callers benefit

Chapter 6: User-Defined Functions (UDF)

6.1 Scalar Functions
Returns one value. Can be used in SELECT, WHERE, ORDER BY, DEFAULT constraints. Must be called with
schema: dbo.fn_Name().
CREATE FUNCTION dbo.fn_AgeFromDOB (@DOB DATE)
RETURNS INT
AS
BEGIN
DECLARE @Age INT
SET @Age = DATEDIFF(YEAR, @DOB, GETDATE())
-- Adjust if birthday has not occurred yet this year
IF (MONTH(@DOB) > MONTH(GETDATE())) OR
(MONTH(@DOB) = MONTH(GETDATE()) AND DAY(@DOB) > DAY(GETDATE()))
SET @Age = @Age - 1
RETURN @Age
END
 -- Usage
SELECT Name, dbo.fn_AgeFromDOB(DateOfBirth) AS Age FROM tblEmployee
SELECT * FROM tblEmployee WHERE dbo.fn_AgeFromDOB(DateOfBirth) > 30

6.2 Inline Table-Valued Functions (ITVF)

Returns a TABLE from a single SELECT. Faster than Multi-statement TVF -- optimizer inlines them like views.
CREATE FUNCTION dbo.fn_GetByGender (@Gender NVARCHAR(20))
RETURNS TABLE
AS
RETURN (
SELECT ID, Name, Gender, Salary
FROM tblEmployee WHERE Gender = @Gender
)

SELECT * FROM dbo.fn_GetByGender('Male')

-- Can JOIN with other tables

SELECT [Link], [Link]
FROM dbo.fn_GetByGender('Female') F
JOIN tblDepartment D ON [Link] = [Link]

6.3 Multi-Statement TVF (MSTVF)

Returns a table built by multiple statements. Slower than ITVF but supports complex procedural logic.
CREATE FUNCTION dbo.fn_EmpSummary (@ManagerId INT)
RETURNS @Result TABLE (
EmployeeName NVARCHAR(50),
ManagerName NVARCHAR(50),
Salary INT
)
AS
BEGIN
INSERT INTO @Result
SELECT [Link], [Link], [Link]
FROM tblEmployee E
LEFT JOIN tblEmployee M ON [Link] = [Link]
WHERE [Link] = @ManagerId
RETURN
END

SELECT * FROM dbo.fn_EmpSummary(5)

6.4 Stored Procedure vs Function

Feature Stored Procedure Function

Return value Optional; uses OUTPUT params or Must return a value (scalar or table)
RETURN n

Use in SELECT/WHERE Cannot be used inline Scalar/TVF can be used inline

DML inside INSERT, UPDATE, DELETE allowed NOT allowed in functions

TRY/CATCH Supported NOT supported

Transactions Supported NOT supported

Deterministic Not required Deterministic UDFs can be indexed

 PART 5

Indexes & Views

Chapter 7: Indexes
Indexes allow queries to find rows quickly without full table scans. Without an index, SQL Server does a Table
Scan -- checking every row. The right index can cut query time from minutes to milliseconds.

7.1 Index Types

Type Key Characteristics

Clustered Determines physical row order on disk. Only ONE per table. AUTO-created by PRIMARY
KEY.

Non-Clustered Separate B-tree structure with row pointers. Up to 999 per table.

Unique Enforces uniqueness of index keys. Can be clustered or non-clustered.

Filtered Index on a subset of rows (uses a WHERE clause). Smaller and more efficient.

Columnstore Column-based storage optimal for analytics, DW, and large aggregation queries.

Index with INCLUDE Non-clustered with extra columns added to leaf level -- eliminates Key Lookup.

7.2 Creating and Managing Indexes

-- View existing indexes
EXECUTE sp_helpindex tblEmployee

-- Clustered Index (only one per table)

CREATE CLUSTERED INDEX IX_Salary ON tblEmployee (Salary)

-- Non-Clustered Index
CREATE NONCLUSTERED INDEX IX_Name ON tblEmployee (Name)

-- Covering index: INCLUDE avoids Key Lookup

CREATE NONCLUSTERED INDEX IX_Gender
ON tblEmployee (Gender)
INCLUDE (Name, Salary)

-- Unique Index
CREATE UNIQUE INDEX IX_Email ON tblEmployee (Email)

-- Filtered Index (only active employees)

 CREATE INDEX IX_Active ON tblEmployee (Name) WHERE IsActive = 1

-- Drop
DROP INDEX tblEmployee.IX_Name

KEY CONCEPT

CLUSTERED INDEX: Leaf nodes ARE the actual data pages.

Data is physically ordered by the clustered key.

Table with no clustered index = HEAP (unordered storage).

NON-CLUSTERED INDEX: Separate B-tree with row locators.

Leaf nodes contain: index key + clustered key (or RID for heaps).

KEY LOOKUP: When a non-clustered index is used but additional columns are needed,

SQL Server must look up the row in the clustered index -- expensive for many rows.

Fix: use INCLUDE to add needed columns to the non-clustered index leaf level.

PRO TIP

Index columns used in WHERE, JOIN ON, and ORDER BY clauses.

Use INCLUDE columns to create covering indexes that eliminate Key Lookups.

Avoid over-indexing OLTP tables: each index adds overhead to INSERT/UPDATE/DELETE.

Use sys.dm_db_index_usage_stats to identify unused indexes consuming write resources.

Chapter 8: Views
A View is a saved SQL query -- a virtual table. By default no data is stored (non-indexed view executes the
underlying query each time). Views simplify queries, implement security, and provide backward compatibility.

8.1 Basic Views

-- Create
CREATE VIEW vwEmployeeDetails
AS
SELECT [Link], [Link], [Link], [Link] AS Department
FROM tblEmployee E
INNER JOIN tblDepartment D ON [Link] = [Link]

-- Use like a table

 SELECT * FROM vwEmployeeDetails
SELECT * FROM vwEmployeeDetails WHERE Gender = 'Female'

-- DML through updateable views (single base table, no aggregates)

UPDATE vwEmployeeDetails SET Name = 'John' WHERE ID = 1

-- WITH ENCRYPTION: hides view source from sp_helptext

CREATE VIEW vwSecure WITH ENCRYPTION AS ...

-- WITH SCHEMABINDING: prevents base tables from being modified/dropped

-- Must use 2-part names ([Link])
CREATE VIEW vwBound WITH SCHEMABINDING AS
SELECT [Link], [Link]
FROM [Link] E JOIN [Link] D ON [Link] = [Link]

8.2 Indexed (Materialized) Views

Indexed Views physically store the result set. SQL Server auto-maintains them on DML. Best for complex
aggregations queried frequently.
-- Requirements:
-- 1. WITH SCHEMABINDING
-- 2. First index must be UNIQUE CLUSTERED
-- 3. COUNT_BIG(*) required if GROUP BY used
-- 4. ANSI_NULLS and QUOTED_IDENTIFIER ON (session settings)

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON

CREATE VIEW vwDeptSummary WITH SCHEMABINDING AS

SELECT DepartmentId, COUNT_BIG(*) AS EmpCount,
SUM(CONVERT(BIGINT, Salary)) AS TotalSalary
FROM [Link]
GROUP BY DepartmentId

-- Materialize the view

CREATE UNIQUE CLUSTERED INDEX UIX_DeptSummary ON vwDeptSummary (DepartmentId)

8.3 View Limitations

• Cannot pass parameters to views -- use Inline Table-Valued Functions instead
• Cannot use ORDER BY unless combined with TOP or FOR XML
• Cannot reference temporary tables or table variables
• DML through multi-table views is restricted
• WITH ENCRYPTION prevents sp_helptext from showing source (cannot be decrypted)
 PART 6

Triggers

Chapter 9: DML Triggers

Triggers are special stored procedures that fire automatically when DML or DDL events occur. They cannot be
called directly -- they execute in response to INSERT, UPDATE, DELETE, or DDL events.

9.1 Trigger Types

Type When It Fires Typical Use

AFTER INSERT After INSERT completes Audit logging, cascading data changes

AFTER UPDATE After UPDATE completes Track changes, maintain audit trail

AFTER DELETE After DELETE completes Archive records before deletion

INSTEAD OF INSERT Replaces the INSERT Make non-updateable views insertable

INSTEAD OF UPDATE Replaces the UPDATE Complex updates through views

INSTEAD OF DELETE Replaces the DELETE Soft delete, cascade through views

DDL (DATABASE) On CREATE/ALTER/DROP in Prevent schema changes, audit DDL

current database

DDL (SERVER) On server-level DDL events Cross-database auditing, governance

Logon Trigger On user LOGON event Restrict connections, audit logins

KEY CONCEPT

INSERTED and DELETED -- the two magic virtual tables in triggers:

INSERTED: Contains NEW row data (new values after INSERT or UPDATE)

DELETED: Contains OLD row data (old values before DELETE or UPDATE)

For INSERT triggers: only INSERTED is populated

For DELETE triggers: only DELETED is populated

 For UPDATE triggers: DELETED = old values, INSERTED = new values

IMPORTANT: Triggers fire ONCE per DML statement, not once per row.

If UPDATE affects 100 rows, INSERTED and DELETED each have 100 rows.

Never assume single-row triggers -- always handle multi-row cases.

9.2 AFTER Triggers

-- AFTER INSERT trigger
CREATE TRIGGER tr_Employee_AfterInsert
ON tblEmployee FOR INSERT
AS
BEGIN
INSERT INTO tblAudit (AuditData, AuditDate)
SELECT 'New Employee: ' + Name, GETDATE()
FROM INSERTED
END

-- AFTER UPDATE trigger (uses both DELETED and INSERTED)

CREATE TRIGGER tr_Employee_AfterUpdate
ON tblEmployee FOR UPDATE
AS
BEGIN
INSERT INTO tblAudit (AuditData, AuditDate)
SELECT [Link] + changed salary to + CAST([Link] AS NVARCHAR), GETDATE()', '
FROM DELETED D INNER JOIN INSERTED I ON [Link] = [Link]', 'END', '', '-- AFTER
DELETE trigger', 'CREATE TRIGGER tr_Employee_AfterDelete', 'ON tblEmployee FOR
DELETE', 'AS', 'BEGIN', " INSERT INTO tblAudit (AuditData, AuditDate)",
" SELECT 'Deleted: ' + Name, GETDATE() FROM DELETED", 'END', ]))
content_parts.append(el()) content_parts.append(h2('9.3 INSTEAD OF Triggers'))
content_parts.append(code_block([ '-- INSTEAD OF INSERT: make a multi-table view
insertable', 'CREATE TRIGGER tr_vwEmpDept_Insert', 'ON vwEmployeeDept',
'INSTEAD OF INSERT', 'AS', 'BEGIN', ' DECLARE @DeptID INT', '
SELECT @DeptID = DeptId FROM tblDepartment', ' WHERE DeptName = (SELECT DeptName
FROM INSERTED)', '', ' IF @DeptID IS NULL', ' BEGIN', '
INSERT INTO tblDepartment (DeptName)', ' SELECT DeptName FROM INSERTED', '
SET @DeptID = SCOPE_IDENTITY()', ' END', '', ' INSERT INTO tblEmployee
(Name, Gender, DepartmentId)', ' SELECT Name, Gender, @DeptID FROM INSERTED',
'END', '', '-- Manage triggers', 'DISABLE TRIGGER tr_Employee_AfterUpdate ON
tblEmployee', 'ENABLE TRIGGER tr_Employee_AfterUpdate ON tblEmployee', 'DROP
TRIGGER tr_Employee_AfterInsert', ])) content_parts.append(el())
content_parts.append(h2('9.4 DDL Triggers')) content_parts.append(code_block([ '--
Database-scoped: prevent table changes', 'CREATE TRIGGER tr_PreventDDL', 'ON
DATABASE FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE', 'AS', 'BEGIN', '
ROLLBACK', " PRINT 'Table changes are not permitted.'", 'END', '', '--
Server-scoped: audit DDL changes across all databases', 'CREATE TRIGGER tr_AuditDDL',
'ON ALL SERVER FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE', 'AS', 'BEGIN', "
INSERT INTO [Link] (DatabaseName, ObjectName, EventType, SQLCmd, LogDate)",
" SELECT", " EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]',
'NVARCHAR(250)'),", " EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]',
'NVARCHAR(250)'),", " EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]',
'NVARCHAR(250)'),", " EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand)[1]',
'NVARCHAR(2500)'),", ' GETDATE()', 'END', '', '-- Trigger
execution order', "EXEC sp_settriggerorder @triggername='tr_First', @order='First',
@stmttype='INSERT'", '-- Server-scoped triggers ALWAYS fire before database-scoped
triggers', ])) content_parts.append(el()) content_parts.append(pb()) # ==== PART 7 ====
content_parts.append(part(7, 'CTEs, Subqueries & Set Operations'))
content_parts.append(h1('Chapter 10: Subqueries & CTEs')) content_parts.append(h2('10.1
Subqueries')) content_parts.append(code_block([ '-- Non-correlated: executes once',
'SELECT Name FROM tblEmployee', 'WHERE Salary > (SELECT AVG(Salary) FROM
tblEmployee)', '', '-- Correlated: references outer query; runs once per outer
row', 'SELECT Name, Salary,', ' (SELECT AVG(Salary) FROM tblEmployee E2',
' WHERE [Link] = [Link]) AS DeptAvg', 'FROM tblEmployee E1', '',
'-- EXISTS: true if subquery returns any rows', 'SELECT Name FROM tblDepartment D',
'WHERE EXISTS (SELECT 1 FROM tblEmployee E WHERE [Link] = [Link])', '', '-- NOT
EXISTS: true if no rows returned', 'SELECT Name FROM tblDepartment D', 'WHERE NOT
EXISTS (SELECT 1 FROM tblEmployee E WHERE [Link] = [Link])', '', '-- SubQuery vs
JOIN: in most cases SQL Server produces the same execution plan.', '-- Use EXISTS
instead of IN for nullable columns to handle NULLs correctly.', ]))
content_parts.append(el()) content_parts.append(h2('10.2 Common Table Expressions
(CTEs)')) content_parts.append(p('A CTE is a named temporary result set defined with
WITH. It improves readability, can be referenced multiple times, and supports
UPDATE/DELETE.')) content_parts.append(code_block([ '-- Basic CTE', 'WITH
CTE_HighEarners AS (', ' SELECT ID, Name, Salary, Gender', ' FROM
tblEmployee WHERE Salary > 5000', ')', "SELECT * FROM CTE_HighEarners WHERE
Gender = 'Male'", '', '-- Multiple CTEs', 'WITH', "CTE_Male AS (SELECT
* FROM tblEmployee WHERE Gender = 'Male'),", "CTE_Female AS (SELECT * FROM
tblEmployee WHERE Gender = 'Female')", "SELECT 'Male', COUNT(*) FROM CTE_Male",
'UNION ALL', "SELECT 'Female', COUNT(*) FROM CTE_Female", '', '-- Updatable
CTE (single-table)', 'WITH CTE_Update AS (', ' SELECT TOP 3 Salary FROM
tblEmployee ORDER BY Salary DESC', ')', 'UPDATE CTE_Update SET Salary = Salary *
1.10', ])) content_parts.append(el()) content_parts.append(h2('10.3 Recursive CTE'))
content_parts.append(code_block([ '-- Recursive CTE: anchor member UNION ALL
recursive member', 'WITH CTE_OrgChart AS (', ' -- Anchor: top of hierarchy
(CEO with no manager)', ' SELECT ID, Name, ManagerID, 0 AS Level', ' FROM
tblEmployee WHERE ManagerID IS NULL', '', ' UNION ALL', '', ' --
Recursive: join CTE back to employees', ' SELECT [Link], [Link], [Link],
[Link] + 1', ' FROM tblEmployee E', ' INNER JOIN CTE_OrgChart CTE ON
[Link] = [Link]', ')', 'SELECT ID, Name, Level FROM CTE_OrgChart ORDER BY
Level, Name', 'OPTION (MAXRECURSION 500) -- default=100; 0=unlimited (use with
caution)', ])) content_parts.append(el()) content_parts.append(h2('10.4 UNION,
INTERSECT, EXCEPT'))
content_parts.append(dtable( ['Operator','Returns','Duplicates'],
[ ['UNION','Rows from first OR second (combined)','Removes duplicates (like
DISTINCT)'], ['UNION ALL','Rows from first OR second (combined)','Keeps ALL
duplicates (faster)'], ['INTERSECT','Rows present in BOTH result sets','No
duplicates'], ['EXCEPT','Rows in FIRST set but NOT in second','No duplicates'],
], [1800, 4800, 2760] )) content_parts.append(el()) content_parts.append(h2('10.5
PIVOT and UNPIVOT')) content_parts.append(code_block([ '-- PIVOT: rotate unique row
values into column headers', 'SELECT Country, [2012], [2013], [2014], [2015]',
'FROM SalesSummary', 'PIVOT (', ' SUM(SaleAmount)', ' FOR SaleYear IN
([2012], [2013], [2014], [2015])', ') AS PivotTable', '', '-- UNPIVOT: rotate
columns back into rows', 'SELECT Country, SaleYear, SaleAmount', 'FROM
PivotTable', 'UNPIVOT (', ' SaleAmount FOR SaleYear IN ([2012], [2013],
[2014], [2015])', ') AS UnpivotTable', '-- Note: UNPIVOT cannot reverse
aggregated PIVOT data', ])) content_parts.append(el()) content_parts.append(h2('10.6
CROSS APPLY and OUTER APPLY')) content_parts.append(code_block([ '-- APPLY: invoke a
TVF for each row of a driving table', '-- CROSS APPLY = like INNER JOIN (excludes
rows where TVF returns nothing)', '-- OUTER APPLY = like LEFT JOIN (includes rows
even if TVF returns nothing)', '', '-- Top 3 employees per department using CROSS
APPLY', 'SELECT [Link], [Link], [Link]', 'FROM tblDepartment D',
'CROSS APPLY (', ' SELECT TOP 3 Name, Salary FROM tblEmployee', ' WHERE
DepartmentId = [Link]', ' ORDER BY Salary DESC', ') E', ]))
content_parts.append(el()) content_parts.append(h2('10.7 SELECT INTO'))
content_parts.append(code_block([ '-- Copy data into a NEW table (table is auto-
created)', 'SELECT * INTO tblEmployeeBackup FROM tblEmployee', '', '-- Copy
structure only (no data)', 'SELECT * INTO tblEmptyClone FROM tblEmployee WHERE 1 =
0', '', '-- Note: indexes, constraints, and triggers are NOT copied', '-- You
must recreate them manually on the new table', ])) content_parts.append(el())
content_parts.append(pb()) # ==== PART 8 ==== content_parts.append(part(8, 'Transactions
& Error Handling')) content_parts.append(h1('Chapter 11: Transactions'))
content_parts.append(code_block([ '-- Transaction with TRY/CATCH (recommended
pattern)', 'BEGIN TRY', ' BEGIN TRANSACTION', ' UPDATE tblInventory
SET Qty = Qty - @Qty WHERE ItemId = @ItemId', ' INSERT INTO tblSales VALUES
(@ItemId, @Qty, GETDATE())', ' COMMIT TRANSACTION', 'END TRY', 'BEGIN
CATCH', ' ROLLBACK TRANSACTION', ' SELECT ERROR_NUMBER() AS ErrNum,
ERROR_MESSAGE() AS ErrMsg', 'END CATCH', ])) content_parts.append(el())
content_parts.append(h2('11.1 ACID Properties'))
content_parts.append(dtable( ['Property','Definition','Example'],
[ ['Atomic','All-or-nothing: all statements succeed or all roll back','Bank
transfer: debit and credit both succeed or both fail'], ['Consistent','Data is
left in a logically valid state after every transaction','Inventory cannot go negative
after a sale'], ['Isolated','Concurrent transactions cannot see each other s
uncommitted changes','Two users booking same seat -- only one wins'],
['Durable','Committed changes survive crashes and power failures','Written to disk and
transaction log before COMMIT returns'], ], [1600, 3600, 4160] ))
content_parts.append(el()) content_parts.append(h2('11.2 Isolation Levels & Concurrency
Problems')) content_parts.append(dtable( ['Problem','Description','Prevented By
Isolation Level'], [ ['Dirty Read','Reading uncommitted data that may be
rolled back','Read Committed or higher'], ['Lost Update','Two tx read same value,
both update -- second overwrites first','Repeatable Read or higher'], ['Non-
Repeatable Read','Same row read twice gets different values','Repeatable Read or
higher'], ['Phantom Read','Query gets different row count on re-
execution','Serializable'], ], [2400, 3600, 3360] )) content_parts.append(el())
content_parts.append(code_block([ '-- Set isolation level', 'SET TRANSACTION
ISOLATION LEVEL READ UNCOMMITTED -- dirty reads allowed', 'SET TRANSACTION ISOLATION
LEVEL READ COMMITTED -- default', 'SET TRANSACTION ISOLATION LEVEL REPEATABLE
READ', 'SET TRANSACTION ISOLATION LEVEL SERIALIZABLE -- strictest locking',
'SET TRANSACTION ISOLATION LEVEL SNAPSHOT -- row versioning, no shared locks',
'', '-- Enable Read Committed Snapshot Isolation (RCSI) at database level',
'ALTER DATABASE SampleDB SET READ_COMMITTED_SNAPSHOT ON', '-- RCSI: reads use row
versions instead of shared locks', '-- No application code changes needed; resolves
most blocking issues', '', '-- Snapshot vs RCSI:', '-- Snapshot: protects
from UPDATE conflicts; app may need retry logic', '-- RCSI: no update conflict risk;
works transparently with existing apps', ])) content_parts.append(el())
content_parts.append(h1('Chapter 12: Error Handling')) content_parts.append(h2('12.1
TRY/CATCH and Error Functions')) content_parts.append(code_block([ '-- Error
information functions (only valid inside CATCH block)', 'SELECT', '
ERROR_NUMBER() AS ErrorNumber,', ' ERROR_SEVERITY() AS Severity,', '
ERROR_STATE() AS State,', ' ERROR_PROCEDURE() AS Procedure,', '
ERROR_LINE() AS Line,', ' ERROR_MESSAGE() AS Message', '', '--
RAISERROR: raise user-defined error (older syntax)', "RAISERROR ('Custom error: %s',
16, 1, 'detail')", '-- Parameters: message, severity (11-19=user error, 20-25=fatal),
state', '', '-- THROW (SQL Server 2012+): preferred; re-throws original error',
'BEGIN TRY', ' SELECT 1/0', 'END TRY', 'BEGIN CATCH', ' ; THROW
-- semicolon required before THROW', ' -- THROW with no args: re-raises original
error unchanged', 'END CATCH', ])) content_parts.append(el())
content_parts.append(h2('12.2 Deadlocks')) content_parts.append(p('A deadlock occurs
when two transactions each hold a lock the other needs. SQL Server detects deadlocks via
a lock monitor (runs every 5 seconds) and kills one transaction (the deadlock victim) to
break the cycle.')) content_parts.append(code_block([ '-- Log deadlocks to error log
(enable trace flag 1222)', 'DBCC TRACEON (1222, -1) -- -1 means server-wide',
'', '-- Catch deadlock error (error 1205) in T-SQL', 'BEGIN CATCH', ' IF
ERROR_NUMBER() = 1205', " PRINT 'Deadlock victim -- retry the transaction'",
' ELSE', ' ; THROW', 'END CATCH', '', '-- Control which session
is chosen as victim', 'SET DEADLOCK_PRIORITY LOW -- prefer this session to be
killed', 'SET DEADLOCK_PRIORITY HIGH -- prefer other session to be killed', '',
'-- DEADLOCK PREVENTION best practices:', '-- 1. Access tables in the SAME ORDER in
all transactions', '-- 2. Keep transactions SHORT to minimize lock duration', '--
3. Use SNAPSHOT or RCSI isolation to eliminate read locks', '-- 4. Add indexes to
reduce lock scope (fewer rows locked)', '', '-- Find blocking queries',
'SELECT blocking_session_id, session_id, wait_type, wait_time', 'FROM
sys.dm_exec_requests WHERE blocking_session_id <> 0', ])) content_parts.append(el())
content_parts.append(pb()) # ==== PART 9 ==== content_parts.append(part(9, 'Window
Functions')) content_parts.append(h1('Chapter 13: OVER Clause & Window Functions'))
content_parts.append(p('Window functions compute values across a set of rows (the
"window") related to the current row. Unlike GROUP BY, they do not collapse rows -- all
rows are retained with additional computed columns alongside.'))
content_parts.append(el()) content_parts.append(h2('13.1 OVER Clause'))
content_parts.append(code_block([ '-- Syntax: function() OVER ([PARTITION BY cols]
[ORDER BY cols] [ROWS/RANGE ...])', '', '-- Aggregates with OVER: group stats
alongside individual rows', 'SELECT', ' Name, Gender, Salary,', '
COUNT(*) OVER (PARTITION BY Gender) AS GenderCount,', ' AVG(Salary) OVER
(PARTITION BY Gender) AS GenderAvg,', ' SUM(Salary) OVER () AS TotalSalary',
'FROM Employees', '', '-- Running total (ORDER BY inside OVER)', 'SELECT
Name, Salary,', ' SUM(Salary) OVER (ORDER BY Salary ROWS UNBOUNDED PRECEDING) AS
RunningTotal', 'FROM Employees', ])) content_parts.append(el())
content_parts.append(h2('13.2 ROW_NUMBER, RANK, DENSE_RANK, NTILE'))
content_parts.append(dtable( ['Function','Ties Handling','Gaps After Tie','Example (5
rows, 2-way tie at top)'], [ ['ROW_NUMBER()','Unique sequential number for
all rows','No gaps','1, 2, 3, 4, 5'], ['RANK()','Same rank for ties','Gaps after
tie','1, 1, 3, 4, 5'], ['DENSE_RANK()','Same rank for ties','No gaps','1, 1, 2,
3, 4'], ['NTILE(n)','Distributes rows into n groups','N/A','Groups of ~equal
size'], ], [2200, 2200, 1400, 3560] )) content_parts.append(el())
content_parts.append(code_block([ 'SELECT', ' Name, Gender, Salary,', '
ROW_NUMBER() OVER (PARTITION BY Gender ORDER BY Salary DESC) AS RowNum,', ' RANK()
OVER (PARTITION BY Gender ORDER BY Salary DESC) AS Rnk,', ' DENSE_RANK() OVER
(PARTITION BY Gender ORDER BY Salary DESC) AS DRnk,', ' NTILE(4) OVER (ORDER
BY Salary) AS Quartile', 'FROM Employees', '', '-- Pagination with ROW_NUMBER
(SQL 2005-2011)', 'WITH Paged AS (', ' SELECT *, ROW_NUMBER() OVER (ORDER BY
Name) AS RowNum FROM Employees', ')', 'SELECT * FROM Paged WHERE RowNum BETWEEN
11 AND 20 -- page 2', '', '-- OFFSET FETCH (SQL 2012+): cleaner pagination',
'SELECT * FROM Employees', 'ORDER BY Name', 'OFFSET 10 ROWS FETCH NEXT 10 ROWS
ONLY', ])) content_parts.append(el()) content_parts.append(h2('13.3 LEAD, LAG,
FIRST_VALUE, LAST_VALUE')) content_parts.append(code_block([ '-- LAG: access previous
row data', 'SELECT Name, Salary,', ' LAG(Salary, 1, 0) OVER (ORDER BY Salary)
AS PrevSalary,', ' Salary - LAG(Salary, 1, 0) OVER (ORDER BY Salary) AS Increase',
'FROM Employees', '-- LAG(column, offset, default_when_null)', '', '-- LEAD:
access next row data', 'SELECT Name, Salary,', ' LEAD(Salary, 1, 0) OVER
(ORDER BY Salary) AS NextSalary', 'FROM Employees', '', '-- FIRST_VALUE:
first value in the window', 'SELECT Name, Gender, Salary,', '
FIRST_VALUE(Name) OVER (PARTITION BY Gender ORDER BY Salary) AS LowestPaid', 'FROM
Employees', '', '-- LAST_VALUE: REQUIRES explicit ROWS clause for correct
behavior!', 'SELECT Name, Gender, Salary,', ' LAST_VALUE(Name) OVER (', '
PARTITION BY Gender', ' ORDER BY Salary', ' ROWS BETWEEN UNBOUNDED
PRECEDING AND UNBOUNDED FOLLOWING', ' ) AS HighestPaid', 'FROM Employees',
'-- Without ROWS clause: default is RANGE CURRENT ROW, meaning', '-- LAST_VALUE only
considers up to the current row -- NOT the true last!', ])) content_parts.append(el())
content_parts.append(h2('13.4 ROWS vs RANGE')) content_parts.append(box('ARCH',
[ 'ROWS: Physical framing -- counts actual rows in the window.', ' ROWS BETWEEN
UNBOUNDED PRECEDING AND CURRENT ROW = running total', ' ROWS BETWEEN 1 PRECEDING
AND 1 FOLLOWING = 3-row moving average', ' ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING = entire partition', '', 'RANGE: Logical framing --
groups rows with the same ORDER BY value together.', ' Default when ORDER BY is
specified (but no ROWS/RANGE explicit):', ' RANGE BETWEEN UNBOUNDED PRECEDING AND
CURRENT ROW', ' For tied ORDER BY values: all tied rows are in the same "current"
group.', '', 'KEY DIFFERENCE for ties:', ' ROWS: each row counted
independently; running total increments one row at a time.', ' RANGE: all rows with
same ORDER BY value are processed together; total jumps for all.', '',
'RECOMMENDATION: Use ROWS explicitly for running totals to get predictable results.',
'Use ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING for LAST_VALUE.', ]))
content_parts.append(el()) content_parts.append(pb()) # ==== PART 10 ====
content_parts.append(part(10, 'Advanced Topics')) content_parts.append(h1('Chapter 14:
Dynamic SQL')) content_parts.append(p('Dynamic SQL is T-SQL code constructed as a string
at runtime and then executed. Required when object names (tables, columns) or filter
criteria are not known at compile time.')) content_parts.append(el())
content_parts.append(h2('14.1 EXEC vs sp_executesql'))
content_parts.append(dtable( ['Feature','EXEC','sp_executesql'],
[ ['Parameters','No parameterization','Typed input and output parameters'],
['Plan caching','New plan per unique string','Plans reused when parameterized'],
['SQL Injection','Vulnerable with string concat','Safe with parameters'],
['Performance','Worse for repeated calls','Better -- plan reuse'], ['Best used
for','Simple one-off statements','Production code, repeated queries'], ], [2500,
3300, 3560] )) content_parts.append(el()) content_parts.append(code_block([ '-- EXEC:
simple (use QUOTENAME for object names)', 'DECLARE @SQL NVARCHAR(1000)', 'SET
@SQL = N''SELECT * FROM '' + QUOTENAME(@TableName)', 'EXEC (@SQL)', '', '--
sp_executesql: parameterized (safe + plan caching)', 'DECLARE @SQL
NVARCHAR(1000)', 'DECLARE @ParamDef NVARCHAR(500)', "SET @SQL = N'SELECT *
FROM tblEmployee WHERE Gender = @Gender AND Salary > @Min'", "SET @ParamDef =
N'@Gender NVARCHAR(20), @Min INT'", "EXEC sp_executesql @SQL, @ParamDef, @Gender =
'Male', @Min = 5000", '', '-- sp_executesql with OUTPUT parameter', 'DECLARE
@CountSQL NVARCHAR(500)', 'DECLARE @Count INT', "SET @CountSQL = N'SELECT
@TotalCount = COUNT(*) FROM tblEmployee'", "EXEC sp_executesql @CountSQL,
N'@TotalCount INT OUTPUT', @TotalCount = @Count OUTPUT", 'SELECT @Count AS Total',
'', '-- QUOTENAME: wraps object name in [] and escapes embedded brackets', "SET
@SQL = 'SELECT * FROM ' + QUOTENAME(@TableName)", "-- Input: 'Bad]Table' -> Output:
[Bad]]Table]", ])) content_parts.append(el()) content_parts.append(box('WARN',
[ 'NEVER concatenate user input directly into SQL strings -- this enables SQL
Injection.', "Example attack: @Name = '; DROP TABLE tblEmployee; --' destroys your
table.", '', 'RULE 1: For VALUES in WHERE clause -- use sp_executesql
parameters.', 'RULE 2: For OBJECT NAMES (tables, columns) -- use QUOTENAME().',
'', 'Plan caching: sp_executesql caches plans when parameterized.', 'EXEC creates
a brand new plan for every unique string -- wastes resources.', ]))
content_parts.append(el()) content_parts.append(h1('Chapter 15: Temporary Tables & Table
Variables')) content_parts.append(code_block([ '-- Local temp table: # prefix;
visible only to current session', 'CREATE TABLE #TempEmp (ID INT, Name NVARCHAR(50),
Salary INT)', 'INSERT INTO #TempEmp SELECT ID, Name, Salary FROM tblEmployee',
'SELECT * FROM #TempEmp', 'DROP TABLE #TempEmp -- or auto-dropped when session
ends', '', '-- Global temp table: ## prefix; all sessions can see it',
'CREATE TABLE ##GlobalEmp (ID INT, Name NVARCHAR(50))', '-- Dropped when the creating
session ends AND all other references close', '', '-- Table variable: @ prefix;
scoped to batch only', 'DECLARE @T TABLE (ID INT, Name NVARCHAR(50))', "INSERT
INTO @T VALUES (1, 'Alice'), (2, 'Bob')", 'SELECT * FROM @T', '', '-- Dynamic
SQL + temp tables: create BEFORE calling sp_executesql', 'CREATE TABLE #Results (ID
INT, Name NVARCHAR(50))', "EXEC sp_executesql N'INSERT INTO #Results SELECT ID, Name
FROM tblEmployee'", 'SELECT * FROM #Results -- works! Table is in outer scope.',
'-- Temp tables created INSIDE sp_executesql are dropped when it finishes.', ]))
content_parts.append(el()) content_parts.append(dtable( ['Feature','Local Temp
(#)','Global Temp (##)','Table Variable (@)'], [ ['Visible to','Current
session only','All sessions','Current batch only'], ['Lifetime','Session end or
DROP','All references closed','End of batch'], ['Transactions','Participates; can
rollback','Yes','Not rolled back on ROLLBACK'], ['Indexes','CREATE INDEX
supported','Yes','Only via PRIMARY KEY/UNIQUE constraints'],
['Statistics','Maintained by SQL Server','Yes','None -- optimizer uses estimate of 1
row'], ['Best for','Medium result sets; complex SP logic','Shared scratch
space','Small sets; simple lookups'], ], [2600, 2100, 2000, 2660] ))
content_parts.append(el()) content_parts.append(h1('Chapter 16: Normalization'))
content_parts.append(p('Normalization is the process of organizing data to minimize
redundancy and ensure consistency. It proceeds through a series of "normal forms", each
eliminating a specific type of data anomaly.')) content_parts.append(el())
content_parts.append(dtable( ['Normal Form','Requirement','Eliminates'], [
['1NF','Atomic values in each cell; no repeating groups; primary key defined','Multi-
valued columns; repeating groups'], ['2NF','Must be 1NF; every non-key column
depends on the ENTIRE primary key (no partial dependencies)','Partial dependencies in
composite key tables'], ['3NF','Must be 2NF; non-key columns depend ONLY on the
primary key (no transitive dependencies)','Transitive dependencies: non-key column ->
non-key column'], ], [1800, 4000, 3560] )) content_parts.append(el())
content_parts.append(code_block([ '-- 1NF violation: multiple values in one cell',
'-- PersonID | Phone', '-- 1 | 555-1234, 555-5678 <- not atomic!', '--
Fix: separate PhoneNumbers table with PersonID FK', '', '-- 2NF violation:
partial dependency', '-- OrderItem: OrderID, ProductID, ProductName, Qty', '--
ProductName depends only on ProductID (partial, not full composite key)', '-- Fix:
extract Product table; OrderItem keeps only (OrderID, ProductID FK, Qty)', '',
'-- 3NF violation: transitive dependency', '-- Employee: EmpID, ZipCode, City,
State', '-- City and State depend on ZipCode, not directly on EmpID', '-- Fix:
extract ZipCodes table; Employee keeps only (EmpID, ZipCode FK)', ]))
content_parts.append(el()) content_parts.append(h1('Chapter 17: Grouping Sets, ROLLUP &
CUBE')) content_parts.append(code_block([ '-- GROUPING SETS: define multiple grouping
combinations in one query', 'SELECT City, Gender, COUNT(*) AS Count', 'FROM
tblEmployee', 'GROUP BY GROUPING SETS (', ' (City, Gender), -- group by City
AND Gender', ' (City), -- group by City only', ' (Gender),
-- group by Gender only', ' () -- grand total row', ')',
'', '-- ROLLUP: hierarchical subtotals, right-to-left', 'SELECT Country, State,
City, SUM(Sales) AS Total', 'FROM SalesData', 'GROUP BY ROLLUP (Country, State,
City)', '-- Produces: City totals -> State totals -> Country totals -> Grand Total',
 '', '-- CUBE: ALL possible combinations of grouping sets', 'SELECT Country,
State, City, SUM(Sales)', 'FROM SalesData', 'GROUP BY CUBE (Country, State,
City)', '-- 3 columns = 2^3 = 8 combinations including grand total', '', '--
GROUPING(col): 1=aggregated summary row, 0=detail row', 'SELECT', " CASE WHEN
GROUPING(City) = 1 THEN 'All Cities' ELSE City END AS City,", ' SUM(Sales) AS
Total', 'FROM SalesData', 'GROUP BY ROLLUP (City)', '', '--
GROUPING_ID(c1,c2,...): binary mask of which cols are aggregated', 'SELECT Country,
Gender, SUM(Salary),', ' GROUPING_ID(Country, Gender) AS Level', 'FROM
tblEmployee', 'GROUP BY CUBE (Country, Gender)', ])) content_parts.append(el())
content_parts.append(h1('Chapter 18: Sequences, GUIDs & MERGE'))
content_parts.append(h2('18.1 Sequence Object (SQL Server 2012+)'))
content_parts.append(code_block([ '-- Create a Sequence', 'CREATE SEQUENCE
[Link]', ' START WITH 1', ' INCREMENT BY 1', ' MINVALUE 1',
' MAXVALUE 99999', ' CYCLE -- restart from MINVALUE after MAXVALUE',
' CACHE 10 -- pre-allocate 10 values for performance', '', '-- Use',
'SELECT NEXT VALUE FOR [Link] AS NextOrderID', '', '-- Get value BEFORE
INSERT (advantage over IDENTITY)', 'DECLARE @NewID INT = NEXT VALUE FOR
[Link]', 'INSERT INTO tblOrder (OrderID) VALUES (@NewID)', 'INSERT INTO
tblOrderDetail (OrderID) VALUES (@NewID)', ])) content_parts.append(el())
content_parts.append(dtable( ['Feature','IDENTITY','SEQUENCE'],
[ ['Scope','Tied to a specific table column','Independent database object'],
['Shared across tables','No','Yes'], ['Get value before INSERT','No','Yes -- NEXT
VALUE FOR'], ['Cycling','DBCC CHECKIDENT to reset','ALTER SEQUENCE; CYCLE option
built-in'], ['Min/Max control','No','Yes -- MINVALUE / MAXVALUE'],
['Performance','N/A','CACHE option pre-allocates values'], ], [2700, 3300,
3360] )) content_parts.append(el()) content_parts.append(h2('18.2 GUIDs
(UNIQUEIDENTIFIER)')) content_parts.append(code_block([ '-- GUID: 16-byte, globally
unique identifier', 'SELECT NEWID() -- random GUID; causes index
fragmentation as PK', 'SELECT NEWSEQUENTIALID() -- sequential GUID; better for
clustered index PKs', '', 'CREATE TABLE tblSession (', ' SessionID
UNIQUEIDENTIFIER DEFAULT NEWSEQUENTIALID() PRIMARY KEY,', ' UserName
NVARCHAR(50)', ')', '', '-- Check if NULL or empty GUID', 'IF @MyGuid IS
NULL PRINT ''GUID is NULL
IF @MyGuid = '00000000-0000-0000-0000-000000000000' PRINT 'GUID is empty'

18.3 MERGE Statement

-- MERGE: perform INSERT, UPDATE, and DELETE in a single statement
MERGE tblProduct AS Target
USING tblUpdates AS Source
ON [Link] = [Link]

WHEN MATCHED AND [Link] <> [Link] THEN

UPDATE SET [Link] = [Link]

WHEN NOT MATCHED BY TARGET THEN

INSERT (ProductID, Name, Price)
VALUES ([Link], [Link], [Link])

WHEN NOT MATCHED BY SOURCE THEN

DELETE; -- semicolon required at end of MERGE statement
Chapter 19: Built-in Functions
19.1 String Functions
Function Description

LEN(str) Length of string (excluding trailing spaces)

UPPER(str) / LOWER(str) Convert case

LTRIM(str) / RTRIM(str) Remove leading / trailing spaces

LEFT(str,n) / RIGHT(str,n) Return leftmost / rightmost n characters

SUBSTRING(str,start,len) Return part of string starting at position

CHARINDEX(find,str) Find position of substring (0 if not found)

REPLACE(str,old,new) Replace all occurrences of old with new

REPLICATE(str,n) Repeat a string n times

PATINDEX(pattern,str) Pattern search (supports wildcards); returns position or 0

STUFF(str,start,len,new) Delete len chars at start, then insert new string

REVERSE(str) Reverse string characters

SPACE(n) Return n spaces

ASCII(char) / CHAR(n) Convert between character and ASCII code

19.2 Date & Time Functions

Function Description

GETDATE() Current server date and time (datetime)

GETUTCDATE() Current UTC date and time

SYSDATETIME() Higher-precision current datetime (datetime2)

DATEADD(part,n,date) Add n of datepart: DATEADD(DAY,7,GETDATE()) = next week

DATEDIFF(part,d1,d2) Difference in datepart units between two dates

DATEPART(part,date) Returns integer: DATEPART(MONTH,GETDATE()) = 1-12

DATENAME(part,date) Returns string: DATENAME(MONTH,GETDATE()) = January

DAY/MONTH/YEAR(date) Shorthand for DATEPART(DAY/MONTH/YEAR,date)

ISDATE(expr) 1 if valid date/time; 0 otherwise (returns 0 for datetime2)

EOMONTH(date) Last day of the month of the given date

DATEFROMPARTS(y,m,d) Build a date from year, month, day integers

FORMAT(val,format) Flexible formatting: FORMAT(GETDATE(),dd/MM/yyyy)

CONVERT(type,val,style) Convert with optional format style (103=DD/MM/YYYY)

TRY_PARSE(str AS type) SQL 2012+: parse string to date/numeric; NULL on failure

TRY_CONVERT(type,val) SQL 2012+: convert with NULL on failure instead of error

CHOOSE(n,v1,v2,...) SQL 2012+: return nth item (1-based index)

IIF(cond,true,false) SQL 2012+: inline if-else (shorthand for CASE WHEN)

19.3 DateTime Type Comparison

Type Date Range Accuracy Size

SmallDateTime 1900-01-01 to 2079-06-06 1 minute 4 bytes

DateTime 1753-01-01 to 9999-12-31 3.33 milliseconds 8 bytes

DateTime2 (preferred) 0001-01-01 to 9999-12-31 100 nanoseconds 6-8 bytes

Date 0001-01-01 to 9999-12-31 Day only 3 bytes

Time N/A (time only) 100 nanoseconds 3-5 bytes

DateTimeOffset 0001-01-01 to 9999-12-31 + tz 100 nanoseconds 8-10 bytes

19.4 Mathematical & Utility

Function Description

ABS(n) Absolute value: ABS(-10) = 10

CEILING(n) Round up to nearest integer: CEILING(1.1) = 2

FLOOR(n) Round down to nearest integer: FLOOR(1.9) = 1

ROUND(n,d) Round to d decimal places: ROUND(3.456,2) = 3.46

SQUARE(n) / SQRT(n) Square and square root

POWER(base,exp) Power: POWER(2,10) = 1024

RAND() Random float between 0 and 1

SCOPE_IDENTITY() Last identity value generated in current scope

IDENT_CURRENT('tbl') Last identity value for specific table, any session

ISNULL(check,rep) Replace NULL with replacement value (2 params)

COALESCE(v1,v2,...) First non-NULL value from list (N params; ANSI standard)

NULLIF(a,b) Returns NULL if a equals b; else returns a

NEWID() Generate a random GUID

NEWSEQUENTIALID() Generate a sequential GUID (use as clustered index PK)
Quick Reference Summary

Topic / Command Key Syntax / Rule

Create Database CREATE DATABASE dbName

Force Drop DB ALTER DATABASE db SET SINGLE_USER WITH ROLLBACK IMMEDIATE then
DROP DATABASE db

Identity Column ColName INT IDENTITY(1,1) PRIMARY KEY

Get Last Identity SCOPE_IDENTITY() -- always use this (not @@IDENTITY)

Default Constraint ALTER TABLE t ADD CONSTRAINT DF_Name DEFAULT 'value' FOR ColName

Check Constraint ALTER TABLE t ADD CONSTRAINT CK_Name CHECK (condition)

INNER JOIN FROM A INNER JOIN B ON [Link] = [Link] -- only matching rows

LEFT JOIN FROM A LEFT JOIN B ON [Link] = [Link] -- all from A + NULLs

SELF JOIN FROM tbl E LEFT JOIN tbl M ON [Link] = [Link]

Non-matching LEFT LEFT JOIN ... WHERE [Link] IS NULL -- orphan rows only

Create SP CREATE PROCEDURE spName @P type AS BEGIN...END

Execute SP EXEC spName @Param = value

SP Output Param @Count INT OUTPUT -- pass with OUTPUT keyword when calling

Scalar UDF CREATE FUNCTION dbo.fn_Name(...) RETURNS type AS BEGIN...RETURN v

END

Inline TVF CREATE FUNCTION fn_Name() RETURNS TABLE AS RETURN (SELECT...)

Clustered Index CREATE CLUSTERED INDEX IX ON tbl(col) -- only 1 per table

Covering Index CREATE INDEX IX ON tbl(col) INCLUDE (col2, col3) -- eliminates Key Lookup

Indexed View CREATE VIEW WITH SCHEMABINDING then CREATE UNIQUE CLUSTERED
INDEX

AFTER Trigger CREATE TRIGGER tr ON tbl FOR INSERT AS BEGIN...END

INSERTED/DELETED INSERTED = new rows after INSERT/UPDATE; DELETED = old rows

INSTEAD OF Trigger CREATE TRIGGER tr ON tbl INSTEAD OF INSERT AS BEGIN...END

Basic CTE WITH CTE AS (SELECT...) SELECT * FROM CTE

Recursive CTE Anchor UNION ALL Recursive; use OPTION (MAXRECURSION n)

PIVOT PIVOT(AGG(col) FOR col IN ([v1],[v2]))

CROSS APPLY FROM tbl CROSS APPLY tvf_Function([Link])

SELECT INTO SELECT * INTO newTable FROM source -- indexes not copied
Transaction BEGIN TRAN...COMMIT TRAN / ROLLBACK TRAN

TRY/CATCH BEGIN TRY...END TRY BEGIN CATCH ROLLBACK; ;THROW END CATCH

Isolation Level SET TRANSACTION ISOLATION LEVEL READ COMMITTED (default)

ROW_NUMBER ROW_NUMBER() OVER (PARTITION BY col ORDER BY col)

RANK vs DENSE_RANK RANK: gaps after ties (1,1,3); DENSE_RANK: no gaps (1,1,2)

LEAD / LAG LEAD/LAG(col, offset, default) OVER (ORDER BY col)

LAST_VALUE Needs: ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED

FOLLOWING

Running Total SUM(col) OVER (ORDER BY col ROWS UNBOUNDED PRECEDING)

Dynamic SQL (safe) sp_executesql @sql, @params, @v = value -- parameters prevent injection

Object Names in SQL QUOTENAME(@TableName) -- brackets and escapes embedded brackets

Local Temp Table CREATE TABLE #Name (...) -- session-scoped, supports indexes

Table Variable DECLARE @t TABLE (...) -- batch-scoped, no rollback, no stats

ROLLUP GROUP BY ROLLUP(c1,c2) -- hierarchical subtotals right-to-left

CUBE GROUP BY CUBE(c1,c2) -- all 2^n grouping combinations

Sequence CREATE SEQUENCE s; SELECT NEXT VALUE FOR s -- table-independent

GUID NEWID() random; NEWSEQUENTIALID() for clustered index PK columns

MERGE MERGE Target USING Source ON ... WHEN MATCHED THEN

UPDATE/DELETE; WHEN NOT MATCHED THEN INSERT;

1NF Atomic values; no repeating groups; primary key defined

2NF 1NF + no partial dependencies on composite primary key

3NF 2NF + no transitive dependencies (non-key depending on non-key)

SQL SERVER -- Complete Study Guide

149 Topics | 10 Parts | T-SQL Reference & Best Practices