SQL Server & T-SQL

In short: DELETE (DML) removes rows one by one, supports WHERE, fires triggers, logs each row and keeps the identity counter.

• TRUNCATE (DDL) removes all rows by deallocating pages, is minimally logged and much faster, resets IDENTITY, doesn't fire triggers and isn't allowed on tables referenced by a foreign key.
• DROP removes the whole table definition, data, indexes and permissions.
• In SQL Server all three can be rolled back inside an explicit transaction.

In short: INNER JOIN (matching rows in both), LEFT/RIGHT OUTER JOIN (all rows from one side + matches, NULLs otherwise), FULL OUTER JOIN (all rows from both), CROSS JOIN (Cartesian product), SELF JOIN (table joined to itself, e.g. employee–manager).

• T-SQL also has CROSS APPLY/OUTER APPLY for correlated table expressions.
• Physical algorithms: nested loops, merge, hash.

In short: WHERE filters individual rows before grouping and can't use aggregates.

• HAVING filters groups after GROUP BY and can use aggregates (HAVING COUNT(*) > 5).
• Put non-aggregate conditions in WHERE for performance — fewer rows get grouped.

In short: Primary key: uniquely identifies each row, NOT NULL, one per table, creates a clustered index by default.

• Unique key: enforces uniqueness on other columns, allows one NULL in SQL Server, many per table, creates a non-clustered index by default.
• Foreign key: a column referencing another table's primary/unique key to enforce referential integrity (with cascade options).

In short: Organising tables to reduce redundancy and update anomalies.

• 1NF: atomic columns and no repeating groups.
• 2NF: 1NF and every non-key column depends on the whole primary key (no partial dependency — relevant for composite keys).
• 3NF: 2NF and no transitive dependency (non-key columns depend only on the key, e.g. move DeptName out of Employees into Departments).
• Data warehouses deliberately denormalise for read speed.

In short: A clustered index sorts and stores the table's data rows in key order — the leaf level is the data; only one per table.

• A non-clustered index is a separate structure with key columns and a pointer (clustered key or RID) back to the row; up to 999 per table.
• Clustered is ideal for range queries on the key; non-clustered for selective lookups on other columns, ideally covering with INCLUDE columns.

-- find
SELECT Email, COUNT(*) AS Cnt FROM dbo.Customers GROUP BY Email HAVING COUNT(*) > 1;

-- delete all but one (keep lowest Id)
WITH d AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY Email ORDER BY CustomerId) AS rn FROM dbo.Customers
)
DELETE FROM d WHERE rn > 1;

Then add a UNIQUE constraint so it can't happen again.

In short: All are window ranking functions.

• For salaries 100, 90, 90, 80: ROW_NUMBER gives 1,2,3,4 (unique, arbitrary order among ties), RANK gives 1,2,2,4 (ties share a rank, gaps follow), DENSE_RANK gives 1,2,2,3 (ties share, no gaps).
• Use ROW_NUMBER for deduplication and paging, DENSE_RANK for "Nth highest" with ties.

In short: A Common Table Expression (WITH name AS (...)) is a named temporary result set for a single statement — improves readability, can be referenced multiple times in that statement, and supports recursion.

• A recursive CTE has an anchor member UNION ALL a recursive member referencing the CTE;
• used for hierarchies (org charts, category trees, bill of materials) and generating sequences.
• Default max recursion 100 (OPTION (MAXRECURSION n)).

In short: Procedures can return multiple result sets, output parameters and an integer return code;

• can modify data, use transactions and TRY/CATCH;
• are called with EXEC and can't be used inside SELECT.
• Functions return a scalar or a table, must be side-effect free (no INSERT/UPDATE to tables), can't use TRY/CATCH or transactions, and can be used in SELECT/WHERE/JOIN/APPLY.
• Prefer inline TVFs for reusable query logic.

In short: READ UNCOMMITTED (dirty reads possible;

• NOLOCK), READ COMMITTED (default;
• no dirty reads), REPEATABLE READ (no non-repeatable reads), SERIALIZABLE (no phantoms;
• range locks), SNAPSHOT (row versioning, consistent view as of transaction start, readers don't block writers), and READ COMMITTED SNAPSHOT (database setting making read committed use versioning per statement;
• default in Azure SQL Database).
• Higher isolation = more consistency, more blocking.

In short: Two (or more) transactions each hold locks the other needs, so neither can proceed.

• SQL Server's lock monitor detects it and kills the cheapest transaction (error 1205).
• Investigate with the system_health Extended Events session (deadlock graphs).
• Prevent: access tables in a consistent order, keep transactions short, add indexes so fewer rows are locked, avoid user interaction inside transactions, use RCSI/SNAPSHOT for readers;
• and retry 1205 in the application.

In short: Temp tables (#t) live in tempdb for the session/proc, have statistics, support any indexes, and are rolled back with transactions — good for larger intermediate data.

• Table variables (@t) are scoped to the batch, have no column statistics (estimates improved by 2019 deferred compilation), aren't affected by rollback, best for small sets.
• CTEs aren't stored at all — they're inlined into the single statement.

In short: Using functions, calculations or conversions on an indexed column in WHERE/JOIN prevents index seeks: WHERE YEAR(OrderDate) = 2026, WHERE ISNULL(Col, 0) = 5, WHERE Col + 1 = 10, LIKE '%abc', implicit conversions (nvarchar parameter vs varchar column).

• Rewrite as ranges or move the calculation to the parameter side: OrderDate >= '2026-01-01' AND OrderDate < '2027-01-01'.

In short: When a parameterised query/proc compiles, the optimizer "sniffs" the first parameter values to estimate row counts and caches that plan.

• If data is skewed, later calls with different values reuse a plan that's bad for them (e.g. a nested-loop plan built for a customer with 5 orders reused for one with 5 million).
• Fixes: OPTION (RECOMPILE) for infrequent queries, OPTIMIZE FOR UNKNOWN/specific value, local variables (generic estimate), separate procedures for different data shapes, Query Store plan forcing, and SQL Server 2022's Parameter Sensitive Plan optimisation.

In short: 1) Reproduce and measure (SET STATISTICS IO, TIME ON, actual execution plan, Query Store history).

• 2) Read the plan: scans on large tables, key lookups, bad estimates, spills, implicit conversions, parallelism issues.
• 3) Fix the query: SARGable predicates, remove SELECT *, avoid scalar UDFs/cursors (set-based), rewrite correlated subqueries, correct data types.
• 4) Fix indexes: covering/composite indexes in the right column order, filtered indexes, remove unused.
• 5) Update statistics; handle parameter sniffing.
• 6) Check blocking/waits (sys.dm_exec_requests, sp_WhoIsActive).
• 7) Validate the improvement and watch write overhead.

In short: Something changed in data or plans.

• Check Query Store for a plan regression (different plan since date X) and force the old plan as a stopgap;
• check statistics staleness after big data loads (update them);
• parameter sniffing (different first-call parameters);
• data growth crossing a threshold;
• new blocking (sp_WhoIsActive, wait stats);
• index changes or dropped indexes;
• server pressure (memory grants, tempdb spills, CPU).
• Then fix permanently with indexes/query rewrite.

WITH ranked AS (
    SELECT e.*, DENSE_RANK() OVER (PARTITION BY DeptId ORDER BY Salary DESC) AS rnk
    FROM dbo.Employees e
)
SELECT d.DeptName, r.FullName, r.Salary, r.rnk
FROM ranked r JOIN dbo.Departments d ON d.DeptId = r.DeptId
WHERE r.rnk <= 3
ORDER BY d.DeptName, r.rnk;

Use ROW_NUMBER for exactly 3 rows per department regardless of ties; DENSE_RANK to include ties.

In short: Likely blocking: the finance job holds long locks (large UPDATEs, SERIALIZABLE, or lock escalation) while the screen's SELECTs wait.

• Investigate with sp_WhoIsActive/sys.dm_exec_requests (blocking_session_id, wait types like LCK_M_S) during the window and the job's plans.
• Fixes: enable READ COMMITTED SNAPSHOT so readers don't block on writers, batch the job's updates into small transactions, add indexes so it locks fewer rows, schedule heavy parts off-hours, or read reports from a readable secondary/replica.

In short: Atomicity — all statements in a transaction succeed or none do (rollback on failure).

• Consistency — a transaction moves the database from one valid state to another, respecting constraints, keys and rules.
• Isolation — concurrent transactions don't see each other's uncommitted work, controlled by isolation levels and locks/row versions.
• Durability — once committed, changes survive crashes because the log is hardened to disk (write-ahead logging).
• Example: a money transfer debits one account and credits another inside one transaction so it can never half-complete.