SQL & SQL Server
100 Questions & Answers with Code Examples
SQL (Structured Query Language) is the standard language for managing and querying relational databases. It covers creating tables, inserting, updating, deleting, and querying data. Nearly every relational engine — SQL Server, MySQL, PostgreSQL, Oracle — supports the same core SQL standard.
-- DDL: define structure
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
FullName NVARCHAR(100) NOT NULL,
Department NVARCHAR(50),
HireDate DATE
);
-- DML: manipulate data
INSERT INTO Employees VALUES (1, 'Alice', 'Engineering', '2022-03-15');
UPDATE Employees SET Department = 'DevOps' WHERE EmployeeID = 1;
DELETE FROM Employees WHERE EmployeeID = 1;
-- DQL: query data
SELECT FullName, Department FROM Employees WHERE HireDate > '2021-01-01';DDL (Data Definition Language) defines structure: CREATE, ALTER, DROP. DML (Data Manipulation Language) works on rows: SELECT, INSERT, UPDATE, DELETE. DCL (Data Control Language) manages permissions: GRANT, REVOKE.
-- DDL
CREATE TABLE Orders (OrderID INT PRIMARY KEY, Amount DECIMAL(10,2));
ALTER TABLE Orders ADD CustomerID INT;
DROP TABLE Orders;
-- DML
INSERT INTO Orders (OrderID, Amount) VALUES (1, 250.00);
UPDATE Orders SET Amount = 300.00 WHERE OrderID = 1;
DELETE FROM Orders WHERE OrderID = 1;
-- DCL
GRANT SELECT, INSERT ON Orders TO SalesUser;
REVOKE INSERT ON Orders FROM SalesUser;A PRIMARY KEY uniquely identifies every row in a table. It cannot be NULL and no two rows can share the same value. Each table may have only one primary key, and SQL Server automatically creates a Clustered Index on it.
-- Single-column PK
CREATE TABLE Products (
ProductID INT PRIMARY KEY,
ProductName NVARCHAR(100) NOT NULL
);
-- Composite PK (two columns together must be unique)
CREATE TABLE OrderItems (
OrderID INT,
ProductID INT,
Quantity INT,
CONSTRAINT PK_OrderItems PRIMARY KEY (OrderID, ProductID)
);A FOREIGN KEY is a column (or set of columns) that references the PRIMARY KEY of another table, establishing a relationship and enforcing Referential Integrity. You cannot insert a value that does not exist in the parent table.
CREATE TABLE Customers (
CustomerID INT PRIMARY KEY,
Name NVARCHAR(100)
);
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
CustomerID INT NOT NULL,
OrderDate DATE,
CONSTRAINT FK_Orders_Customer
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
ON DELETE CASCADE -- delete orders when customer is deleted
ON UPDATE CASCADE
);Both enforce uniqueness, but PRIMARY KEY does not allow NULL and a table can have only one. UNIQUE allows a single NULL value and a table can have many UNIQUE constraints. SQL Server creates a Clustered Index for the PK and a Non-Clustered Index for UNIQUE by default.
CREATE TABLE Users (
UserID INT PRIMARY KEY, -- NOT NULL, one per table
Email NVARCHAR(255) UNIQUE NOT NULL, -- unique, but separate constraint
Username NVARCHAR(100) UNIQUE, -- allows one NULL
SSN CHAR(9) UNIQUE -- multiple UNIQUE constraints OK
);
-- Violation examples
INSERT INTO Users VALUES (1, 'a@b.com', 'alice', '123456789');
INSERT INTO Users VALUES (2, 'a@b.com', 'bob', '999999999');
-- Error: Violation of UNIQUE KEY constraint on EmailNULL represents a missing or unknown value — it is not zero and not an empty string. Comparisons must use IS NULL / IS NOT NULL, not =. Functions ISNULL(), COALESCE(), and NULLIF() help replace or detect NULL values.
-- Wrong: = NULL never matches
SELECT * FROM Employees WHERE ManagerID = NULL; -- returns nothing
-- Correct
SELECT * FROM Employees WHERE ManagerID IS NULL;
SELECT * FROM Employees WHERE ManagerID IS NOT NULL;
-- ISNULL: replace NULL with a default
SELECT ISNULL(ManagerID, 0) AS ManagerID FROM Employees;
-- COALESCE: first non-NULL from a list (ANSI standard)
SELECT COALESCE(Phone, Mobile, 'N/A') AS Contact FROM Employees;
-- NULLIF: returns NULL when both args are equal
SELECT NULLIF(Quantity, 0) AS SafeQty FROM OrderItems; -- avoids divide-by-zeroWHERE filters individual rows before grouping and cannot use aggregate functions. HAVING filters groups after GROUP BY and is designed for aggregates like COUNT, SUM, and AVG.
-- WHERE filters rows before aggregation
SELECT Department, COUNT(*) AS HeadCount
FROM Employees
WHERE HireDate >= '2020-01-01' -- only recent hires
GROUP BY Department
HAVING COUNT(*) >= 5; -- only departments with 5+ of those
-- HAVING with SUM
SELECT CustomerID, SUM(Amount) AS Total
FROM Orders
GROUP BY CustomerID
HAVING SUM(Amount) > 1000; -- high-value customers onlyTRUNCATE removes all rows at once, is not filterable, does not fire DML triggers, resets the IDENTITY counter, and is minimally logged — making it much faster. DELETE removes rows one by one, supports a WHERE clause, fires triggers, and is fully logged.
-- DELETE — conditional, loggable, fires triggers
DELETE FROM Logs WHERE LogDate < '2023-01-01';
-- DELETE all rows (slow for large tables)
DELETE FROM StagingTable;
-- TRUNCATE — fast, resets identity, cannot filter
TRUNCATE TABLE StagingTable;
-- After TRUNCATE, identity resets to seed
INSERT INTO StagingTable (Name) VALUES ('Alice');
SELECT SCOPE_IDENTITY(); -- returns 1 (reset)
-- Note: TRUNCATE cannot be used when FK references the tableConstraints enforce rules on columns or tables to maintain data integrity. The six types are: NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, and DEFAULT.
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
FullName NVARCHAR(100) NOT NULL,
Email NVARCHAR(255) UNIQUE,
Age INT CHECK (Age >= 18 AND Age <= 65),
Status NVARCHAR(20) DEFAULT 'Active',
DeptID INT FOREIGN KEY REFERENCES Departments(DeptID)
);
-- Add a constraint after creation
ALTER TABLE Employees
ADD CONSTRAINT CHK_Email CHECK (Email LIKE '%@%');CHAR(n) is fixed-length — always stores exactly n characters, padding with spaces. VARCHAR(n) is variable-length — stores only the actual data plus 2 bytes overhead. Use CHAR for fixed-width data like codes; use VARCHAR for text of varying length. Prefix with N for Unicode.
CREATE TABLE Demo (
CountryCode CHAR(2), -- always 2 bytes: 'US', 'GB'
PostalCode CHAR(5), -- always 5 bytes: '10001'
FirstName NVARCHAR(100), -- variable Unicode, up to 100 chars
Description VARCHAR(MAX) -- variable, up to 2 GB
);
-- LEN vs DATALENGTH difference for CHAR
DECLARE @c CHAR(10) = 'Hi';
SELECT LEN(@c), -- 2 (logical length, trims padding)
DATALENGTH(@c); -- 10 (physical storage)INNER JOIN returns only rows that have a match in both tables. LEFT JOIN returns all rows from the left table; unmatched rows from the right table appear as NULL. Use LEFT JOIN to include records that may not have a related row.
-- INNER JOIN: only customers who have orders
SELECT c.Name, o.OrderID, o.Amount
FROM Customers c
INNER JOIN Orders o ON c.CustomerID = o.CustomerID;
-- LEFT JOIN: all customers, NULL for those without orders
SELECT c.Name, o.OrderID, o.Amount
FROM Customers c
LEFT JOIN Orders o ON c.CustomerID = o.CustomerID;
-- Find customers with NO orders at all
SELECT c.Name
FROM Customers c
LEFT JOIN Orders o ON c.CustomerID = o.CustomerID
WHERE o.OrderID IS NULL;FULL OUTER JOIN returns all rows from both tables. Unmatched rows from either side are filled with NULL on the opposite side. It is the union of LEFT JOIN and RIGHT JOIN — useful for comparing two datasets and finding what is exclusive to each.
SELECT e.FullName, p.ProjectName
FROM Employees e
FULL OUTER JOIN Projects p ON e.ProjectID = p.ProjectID;
-- Rows in result:
-- matched employees & projects
-- employees with no project (p.ProjectName IS NULL)
-- projects with no employees (e.FullName IS NULL)
-- Find rows only in one table
SELECT * FROM TableA a
FULL OUTER JOIN TableB b ON a.ID = b.ID
WHERE a.ID IS NULL OR b.ID IS NULL;A CROSS JOIN produces the Cartesian product — every row from the left table paired with every row from the right. No ON clause is used. If table A has 4 rows and table B has 3, the result has 12 rows. Useful for generating combinations.
-- Generate all size/color combinations for a product catalog
SELECT s.SizeName, c.ColorName
FROM Sizes s
CROSS JOIN Colors c;
-- 4 sizes × 5 colors = 20 rows
-- Generate a numbers table (1–100)
SELECT TOP 100 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N
FROM sys.objects
CROSS JOIN sys.columns;A SELF JOIN joins a table to itself using aliases to distinguish the two copies. The classic use case is a hierarchy in a single table — an Employees table where each employee has a ManagerID that points back to EmployeeID in the same table.
-- List each employee alongside their manager's name
SELECT e.FullName AS Employee,
m.FullName AS Manager
FROM Employees e
LEFT JOIN Employees m ON e.ManagerID = m.EmployeeID;
-- LEFT JOIN so CEO (no manager) still appears with NULL manager
-- Find employees who earn more than their manager
SELECT e.FullName, e.Salary, m.FullName AS Manager, m.Salary AS ManagerSalary
FROM Employees e
JOIN Employees m ON e.ManagerID = m.EmployeeID
WHERE e.Salary > m.Salary;Use a JOIN when you need columns from both tables in the result — the optimizer handles it efficiently. Use a subquery for filtering (EXISTS / IN) or deriving a scalar value. Correlated subqueries re-execute per row; rewriting them as JOINs or CTEs usually improves performance.
-- Subquery (works but re-evaluates per row)
SELECT FullName FROM Employees
WHERE DeptID IN (SELECT DeptID FROM Departments WHERE Location = 'NYC');
-- Equivalent JOIN (usually faster, columns from both available)
SELECT e.FullName
FROM Employees e
JOIN Departments d ON e.DeptID = d.DeptID
WHERE d.Location = 'NYC';
-- EXISTS is often best for "does related row exist?" checks
SELECT e.FullName FROM Employees e
WHERE EXISTS (SELECT 1 FROM Orders o WHERE o.SalesRepID = e.EmployeeID);Aggregate functions collapse a set of rows into a single value: COUNT, SUM, AVG, MIN, MAX. They ignore NULL values in the column (except COUNT(*)). They are always used with GROUP BY or in a Window Function.
SELECT
Department,
COUNT(*) AS HeadCount,
COUNT(ManagerID) AS WithManager, -- excludes NULLs
SUM(Salary) AS PayrollTotal,
AVG(Salary) AS AvgSalary,
MIN(Salary) AS LowestSalary,
MAX(Salary) AS HighestSalary,
MAX(HireDate) AS MostRecentHire
FROM Employees
GROUP BY Department
ORDER BY PayrollTotal DESC;COUNT(*) counts every row including those with NULL values. COUNT(column) counts only rows where that column is not NULL. COUNT(DISTINCT column) counts unique non-NULL values.
-- Sample data: 5 employees, 2 have no manager (NULL)
SELECT
COUNT(*) AS TotalRows, -- 5
COUNT(ManagerID) AS HasManager, -- 3 (NULLs excluded)
COUNT(DISTINCT DeptID) AS UniqueDepts -- counts distinct values
FROM Employees;
-- Practical: ratio of employees with a phone on file
SELECT
COUNT(Phone) * 100.0 / COUNT(*) AS PhoneCoverage
FROM Employees;The cleanest modern approach uses DENSE_RANK() in a CTE or subquery. A classic alternative is a correlated subquery or OFFSET … FETCH. DENSE_RANK handles ties correctly — two employees at the same salary share the same rank.
-- DENSE_RANK approach (handles ties)
WITH Ranked AS (
SELECT Salary,
DENSE_RANK() OVER (ORDER BY Salary DESC) AS rnk
FROM Employees
)
SELECT DISTINCT Salary FROM Ranked WHERE rnk = 2; -- 2nd highest
-- OFFSET-FETCH approach (simple but ignores ties)
SELECT DISTINCT Salary FROM Employees
ORDER BY Salary DESC
OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY;
-- Correlated subquery (classic interview answer)
SELECT MAX(Salary) FROM Employees
WHERE Salary < (SELECT MAX(Salary) FROM Employees);GROUP BY collapses rows with the same column values into one summary row and enables aggregate functions. ORDER BY sorts the result set — it does not change which rows appear, only their order. GROUP BY comes before ORDER BY in a query.
SELECT
Department,
COUNT(*) AS Headcount,
AVG(Salary) AS AvgSalary
FROM Employees
WHERE Status = 'Active' -- 1. filter rows
GROUP BY Department -- 2. collapse into groups
HAVING AVG(Salary) > 60000 -- 3. filter groups
ORDER BY AvgSalary DESC; -- 4. sort final resultROLLUP generates subtotals along a hierarchy of GROUP BY columns plus a grand total. CUBE generates subtotals for every possible combination of GROUP BY columns. Both are useful for reports and OLAP. Use GROUPING() to distinguish real NULLs from the subtotal NULL marker.
-- ROLLUP: subtotals per Year → Quarter, then grand total
SELECT Year, Quarter, SUM(Sales) AS TotalSales
FROM SalesData
GROUP BY ROLLUP (Year, Quarter);
-- rows: each Year+Quarter, each Year subtotal, grand total
-- CUBE: all combinations
SELECT Region, Product, SUM(Sales)
FROM SalesData
GROUP BY CUBE (Region, Product);
-- rows: Region+Product, Region total, Product total, grand total
-- GROUPING() = 1 means the NULL is a subtotal row, not real NULL
SELECT CASE WHEN GROUPING(Department)=1 THEN 'ALL' ELSE Department END,
SUM(Salary)
FROM Employees
GROUP BY ROLLUP (Department);A CTE is a named temporary result set defined with WITH … AS (…) before the main query. It exists only for the duration of that query, improves readability, and enables recursion. Multiple CTEs can be chained with commas.
-- Simple CTE: break a complex query into readable steps
WITH HighEarners AS (
SELECT EmployeeID, FullName, Salary, Department
FROM Employees
WHERE Salary > 80000
),
DeptStats AS (
SELECT Department, AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY Department
)
SELECT h.FullName, h.Salary, d.AvgSalary
FROM HighEarners h
JOIN DeptStats d ON h.Department = d.Department
ORDER BY h.Salary DESC;A non-correlated subquery runs once and its result is used by the outer query. A correlated subquery references a column from the outer query and re-executes for every outer row — making it slower. Rewriting correlated subqueries as JOINs often improves performance.
-- Non-correlated: runs once
SELECT FullName FROM Employees
WHERE DeptID IN (SELECT DeptID FROM Departments WHERE Location = 'NYC');
-- Correlated: re-runs for every employee row (references e.DeptID)
SELECT e.FullName
FROM Employees e
WHERE e.Salary > (
SELECT AVG(Salary) FROM Employees e2
WHERE e2.DeptID = e.DeptID -- ← reference to outer query
);
-- Rewrite as JOIN for better performance
SELECT e.FullName
FROM Employees e
JOIN (SELECT DeptID, AVG(Salary) AS AvgSal FROM Employees GROUP BY DeptID) d
ON e.DeptID = d.DeptID
WHERE e.Salary > d.AvgSal;A window function computes a value for each row based on a related set of rows ("window") without collapsing them into one row. Defined with OVER (PARTITION BY … ORDER BY …). Includes ranking (ROW_NUMBER, RANK), offset (LAG, LEAD), and aggregate (SUM OVER, AVG OVER) variants.
SELECT
FullName,
Department,
Salary,
ROW_NUMBER() OVER (PARTITION BY Department ORDER BY Salary DESC) AS RowNum,
RANK() OVER (PARTITION BY Department ORDER BY Salary DESC) AS Rank,
SUM(Salary) OVER (PARTITION BY Department) AS DeptTotal,
AVG(Salary) OVER (PARTITION BY Department) AS DeptAvg,
Salary - AVG(Salary) OVER (PARTITION BY Department) AS DiffFromAvg
FROM Employees;ROW_NUMBER assigns a unique sequential number — ties get different numbers. RANK assigns the same number to ties but skips the next rank (1, 1, 3). DENSE_RANK assigns the same number to ties without skipping (1, 1, 2). Choose based on how you want tied values treated.
-- Salaries: 90k, 90k, 75k, 60k
SELECT
FullName,
Salary,
ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowNum, -- 1, 2, 3, 4
RANK() OVER (ORDER BY Salary DESC) AS Rnk, -- 1, 1, 3, 4
DENSE_RANK() OVER (ORDER BY Salary DESC) AS DenseRnk -- 1, 1, 2, 3
FROM Employees;
-- Get the top earner per department (no duplicates)
WITH Ranked AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY Department ORDER BY Salary DESC) AS rn
FROM Employees
)
SELECT * FROM Ranked WHERE rn = 1;LAG(col, n) returns the value from n rows before the current row in the window. LEAD(col, n) returns the value from n rows after. Both accept an optional default for when no preceding/following row exists. Used heavily for comparing consecutive time-series rows.
-- Monthly revenue change vs previous month
SELECT
SaleMonth,
Revenue,
LAG(Revenue, 1, 0) OVER (ORDER BY SaleMonth) AS PrevMonthRevenue,
Revenue - LAG(Revenue, 1, 0) OVER (ORDER BY SaleMonth) AS MonthlyChange,
LEAD(Revenue, 1) OVER (ORDER BY SaleMonth) AS NextMonthRevenue
FROM MonthlySales
ORDER BY SaleMonth;
-- Per-employee: compare to previous hire in the same department
SELECT FullName, Department, HireDate,
LAG(HireDate) OVER (PARTITION BY Department ORDER BY HireDate) AS PrevHireDate
FROM Employees;An index is a data structure (typically a B-tree) that lets SQL Server find rows without scanning the entire table. Without an index the engine does a Table Scan — O(n). With one, it does an Index Seek — O(log n). Indexes speed up reads but add overhead to writes because they must be kept in sync.
-- Create a non-clustered index on a frequently filtered column
CREATE NONCLUSTERED INDEX IX_Employees_Department
ON Employees (Department);
-- Composite index: covers queries filtering by both columns
CREATE NONCLUSTERED INDEX IX_Orders_Customer_Date
ON Orders (CustomerID, OrderDate DESC);
-- View all indexes on a table
SELECT name, type_desc, is_unique
FROM sys.indexes
WHERE object_id = OBJECT_ID('Employees');A Clustered Index defines the physical sort order of the table's data pages — there can be only one per table (usually the PK). A Non-Clustered Index is a separate B-tree structure that stores index keys plus a pointer to the actual data row — up to 999 per table in SQL Server.
-- Clustered index (one per table, typically the PK)
CREATE CLUSTERED INDEX CIX_Orders_OrderDate
ON Orders (OrderDate); -- rows physically sorted by date
-- Non-clustered index (many allowed)
CREATE NONCLUSTERED INDEX IX_Orders_CustomerID
ON Orders (CustomerID); -- separate structure, pointer to data row
-- Check which indexes are clustered
SELECT name, type_desc
FROM sys.indexes
WHERE object_id = OBJECT_ID('Orders')
AND type_desc IN ('CLUSTERED','NONCLUSTERED');A covering index contains all columns a query needs, so SQL Server can satisfy the query entirely from the index without touching the base table (no Key Lookup). Extra columns are added via the INCLUDE clause — they are stored at the leaf level but not part of the key.
-- Query that benefits from a covering index
SELECT FullName, Email
FROM Employees
WHERE Department = 'Engineering';
-- Without INCLUDE: causes Key Lookup for FullName and Email
CREATE INDEX IX_Dept ON Employees (Department);
-- With INCLUDE: fully covers the query — no Key Lookup
CREATE INDEX IX_Dept_Covering
ON Employees (Department)
INCLUDE (FullName, Email); -- stored at leaf, not in keyThe Execution Plan shows how SQL Server will (Estimated) or did (Actual) execute a query: table access methods, join algorithms, index usage, and the estimated cost of each step. It is the primary tool for diagnosing slow queries. Press Ctrl+M in SSMS to include the actual plan, or prefix with SET STATISTICS IO ON.
-- See estimated plan without running the query
SET SHOWPLAN_ALL ON;
GO
SELECT * FROM Orders WHERE CustomerID = 42;
GO
SET SHOWPLAN_ALL OFF;
-- Track IO and time for an actual run
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO
SELECT * FROM Orders WHERE CustomerID = 42;
GO
-- Output shows: logical reads, physical reads, elapsed timeFragmentation occurs when index pages are no longer in contiguous physical order due to inserts, updates, and deletes. Under 30% fragmentation: use REORGANIZE (online, page-level). Over 30%: use REBUILD (creates a brand-new index). Check with the DMV sys.dm_db_index_physical_stats.
-- Check fragmentation
SELECT i.name, s.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('Orders'), NULL, NULL, 'LIMITED') s
JOIN sys.indexes i ON s.object_id = i.object_id AND s.index_id = i.index_id
WHERE s.avg_fragmentation_in_percent > 10;
-- Fix: reorganize (online, < 30%)
ALTER INDEX IX_Orders_CustomerID ON Orders REORGANIZE;
-- Fix: rebuild (may lock table in older editions, > 30%)
ALTER INDEX IX_Orders_CustomerID ON Orders REBUILD WITH (ONLINE = ON);A transaction is a logical unit of work that must succeed completely or fail completely — all or nothing. It starts with BEGIN TRANSACTION, is committed with COMMIT, or rolled back with ROLLBACK. The classic example is a bank transfer: debit one account and credit another must both succeed.
BEGIN TRANSACTION;
BEGIN TRY
UPDATE Accounts SET Balance = Balance - 500 WHERE AccountID = 1;
UPDATE Accounts SET Balance = Balance + 500 WHERE AccountID = 2;
IF (SELECT Balance FROM Accounts WHERE AccountID = 1) < 0
THROW 50001, 'Insufficient funds.', 1;
COMMIT TRANSACTION;
PRINT 'Transfer successful.';
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION;
PRINT 'Transfer failed: ' + ERROR_MESSAGE();
END CATCH;Atomicity — all or nothing. Consistency — a transaction moves the database from one valid state to another. Isolation — concurrent transactions don't see each other's uncommitted changes. Durability — once committed, changes survive crashes (written to the transaction log and data files).
-- Atomicity: both updates happen or neither does
BEGIN TRANSACTION;
UPDATE Inventory SET Stock = Stock - 1 WHERE ProductID = 5;
INSERT INTO SalesLog (ProductID, SaleDate) VALUES (5, GETDATE());
COMMIT; -- both committed atomically
-- Durability: even after a server crash, committed data survives
-- SQL Server writes to the Transaction Log (LDF) before data file (MDF)
-- This is the Write-Ahead Log (WAL) guarantee
-- Checking current isolation level
DBCC USEROPTIONS; -- look for 'isolation level' in outputIsolation levels control what uncommitted data a transaction can read. From least to most strict: READ UNCOMMITTED (dirty reads allowed) → READ COMMITTED (default) → REPEATABLE READ → SERIALIZABLE. SNAPSHOT ISOLATION is an optimistic alternative that avoids reader-writer blocking by using row versions.
-- Set for the current session
SET TRANSACTION ISOLATION LEVEL READ COMMITTED; -- default
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -- dirty reads OK
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; -- strictest, most blocking
-- Enable Snapshot Isolation at database level
ALTER DATABASE AdventureWorks SET ALLOW_SNAPSHOT_ISOLATION ON;
ALTER DATABASE AdventureWorks SET READ_COMMITTED_SNAPSHOT ON;
-- Then use it in a session
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;A deadlock occurs when two transactions each hold a lock the other needs — they wait forever. SQL Server's deadlock monitor detects this and automatically rolls back the "deadlock victim" (the cheaper transaction). Prevention: always access resources in the same order, keep transactions short, and consider using SNAPSHOT isolation.
-- Detect deadlocks via Extended Events (preferred over Profiler)
-- Or check the system health session after the fact:
SELECT xdr.value('@timestamp','datetime2') AS DeadlockTime,
xdr.query('.') AS DeadlockGraph
FROM (
SELECT CAST(target_data AS XML) AS TargetData
FROM sys.dm_xe_session_targets t
JOIN sys.dm_xe_sessions s ON s.address = t.event_session_address
WHERE s.name = 'system_health' AND t.target_name = 'ring_buffer'
) AS Data
CROSS APPLY TargetData.nodes('//RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData(xdr);SAVE TRANSACTION marks a point inside a transaction. ROLLBACK TRANSACTION savepoint_name undoes only the work done after that point, without cancelling the whole transaction. Useful for complex stored procedures that need partial rollback logic without aborting everything.
BEGIN TRANSACTION;
INSERT INTO AuditLog (Action) VALUES ('Step 1');
SAVE TRANSACTION Step1Done; -- savepoint
INSERT INTO AuditLog (Action) VALUES ('Step 2 - risky');
-- something goes wrong in step 2...
ROLLBACK TRANSACTION Step1Done; -- undo only step 2
-- step 1 insert is still in play
INSERT INTO AuditLog (Action) VALUES ('Step 2 - retry safe');
COMMIT TRANSACTION;A View is a named SELECT query stored in the database and used like a table. It stores no data (unless indexed). Benefits: simplify complex queries, hide sensitive columns, and provide a stable interface when underlying table structures change.
-- Create a view hiding salary details
CREATE VIEW vw_EmployeePublic AS
SELECT EmployeeID, FullName, Department, HireDate
FROM Employees
WHERE Status = 'Active';
-- Use it exactly like a table
SELECT * FROM vw_EmployeePublic WHERE Department = 'Engineering';
-- Indexed (materialized) view — stores data physically
CREATE UNIQUE CLUSTERED INDEX CIX_vw ON vw_EmployeePublic (EmployeeID);
-- Update or remove the view
ALTER VIEW vw_EmployeePublic AS SELECT EmployeeID, FullName FROM Employees;
DROP VIEW vw_EmployeePublic;A Stored Procedure is a compiled, named block of T-SQL stored on the server. Benefits: reduced network traffic (one call instead of many statements), plan caching, centralised business logic, and security (grant EXECUTE without exposing tables).
CREATE PROCEDURE usp_GetOrdersByCustomer
@CustomerID INT,
@StartDate DATE = NULL -- optional parameter with default
AS
BEGIN
SET NOCOUNT ON;
SELECT OrderID, OrderDate, Amount
FROM Orders
WHERE CustomerID = @CustomerID
AND (@StartDate IS NULL OR OrderDate >= @StartDate)
ORDER BY OrderDate DESC;
END;
-- Execute
EXEC usp_GetOrdersByCustomer @CustomerID = 42;
EXEC usp_GetOrdersByCustomer @CustomerID = 42, @StartDate = '2024-01-01';A Function must return a value (scalar or table), can be called inside a SELECT, and cannot perform DML. A Stored Procedure may or may not return data, can do DML, manage transactions, and is called with EXEC — not inside a query expression.
-- Scalar function — usable inside SELECT
CREATE FUNCTION dbo.fn_FullName (@First NVARCHAR(50), @Last NVARCHAR(50))
RETURNS NVARCHAR(101)
AS
BEGIN
RETURN LTRIM(RTRIM(@First + ' ' + @Last));
END;
SELECT dbo.fn_FullName(FirstName, LastName) AS FullName FROM Employees;
-- Inline Table-Valued Function — returns a table, used like a view
CREATE FUNCTION dbo.fn_OrdersByCustomer (@CustID INT)
RETURNS TABLE
AS
RETURN (SELECT * FROM Orders WHERE CustomerID = @CustID);
SELECT * FROM dbo.fn_OrdersByCustomer(42);A Trigger is T-SQL code that fires automatically in response to a DML event (INSERT, UPDATE, DELETE) on a table. AFTER triggers run after the operation; INSTEAD OF triggers replace it. Use cases: audit logging, enforcing business rules, and cascading changes.
-- AFTER UPDATE trigger for audit logging
CREATE TRIGGER trg_Employees_AuditSalary
ON Employees
AFTER UPDATE
AS
BEGIN
IF UPDATE(Salary)
BEGIN
INSERT INTO SalaryAudit (EmployeeID, OldSalary, NewSalary, ChangedAt, ChangedBy)
SELECT d.EmployeeID, d.Salary, i.Salary, GETDATE(), SYSTEM_USER
FROM deleted d -- old values
JOIN inserted i ON d.EmployeeID = i.EmployeeID; -- new values
END
END;An iTVF contains a single SELECT and returns a TABLE — the optimizer can "inline" it into the calling query, giving performance similar to a view but with parameters. It is superior to Multi-Statement TVFs for most use cases.
-- iTVF: parameterized view equivalent
CREATE FUNCTION dbo.fn_ActiveEmployeesByDept (@Dept NVARCHAR(50))
RETURNS TABLE
AS
RETURN (
SELECT EmployeeID, FullName, HireDate, Salary
FROM Employees
WHERE Department = @Dept
AND Status = 'Active'
);
-- Use with JOIN or CROSS APPLY
SELECT d.DeptName, e.*
FROM Departments d
CROSS APPLY dbo.fn_ActiveEmployeesByDept(d.DeptName) e;
-- Filter & aggregate on the result
SELECT * FROM dbo.fn_ActiveEmployeesByDept('Engineering')
WHERE Salary > 70000;Normalization is the process of organizing tables to minimize data redundancy and avoid anomalies (insert, update, delete anomalies). It decomposes a large table into smaller, related tables, each holding one fact. The process follows sequential Normal Forms: 1NF → 2NF → 3NF → BCNF.
-- ❌ Unnormalized: repeats customer name with every order
-- OrderID | CustomerName | CustomerCity | ProductName | Qty
-- 1 | Alice | NYC | Laptop | 1
-- 2 | Alice | NYC | Mouse | 2 ← redundancy
-- ✓ Normalized (3NF): three focused tables
CREATE TABLE Customers (CustomerID INT PK, Name NVARCHAR(100), City NVARCHAR(50));
CREATE TABLE Products (ProductID INT PK, ProductName NVARCHAR(100), Price DECIMAL);
CREATE TABLE Orders (OrderID INT PK, CustomerID INT FK, ProductID INT FK, Qty INT);1NF: atomic column values (no lists or repeating groups), unique rows. 2NF: meets 1NF + every non-key column depends on the whole primary key (no partial dependency — applies when PK is composite). 3NF: meets 2NF + no transitive dependencies (non-key column depends only on the PK, not on another non-key column).
-- 1NF violation: Phone stores multiple values
-- EmployeeID | Phone
-- 1 | '555-1234, 555-5678' ← not atomic
-- 1NF fix: separate table
CREATE TABLE EmployeePhones (EmployeeID INT, Phone VARCHAR(20), PRIMARY KEY(EmployeeID, Phone));
-- 3NF violation: DeptName depends on DeptID, not on EmployeeID
-- EmployeeID | DeptID | DeptName ← DeptName is transitively dependent
-- 3NF fix: move DeptName to a Departments table
CREATE TABLE Departments (DeptID INT PK, DeptName NVARCHAR(100));
CREATE TABLE Employees (EmployeeID INT PK, DeptID INT FK REFERENCES Departments);Denormalization intentionally adds redundancy to improve read performance at the cost of more complex writes. It is common in data warehouses and reporting databases (OLAP) where read speed matters more than update simplicity. Examples include flattening joined tables and pre-computing aggregates.
-- Normalized (requires JOIN every query)
SELECT o.OrderID, c.CustomerName, c.City
FROM Orders o JOIN Customers c ON o.CustomerID = c.CustomerID;
-- Denormalized: store CustomerName directly on Orders (faster reads)
ALTER TABLE Orders ADD CustomerName NVARCHAR(100), CustomerCity NVARCHAR(50);
-- Keep in sync with a trigger (write penalty accepted for read gain)
CREATE TRIGGER trg_Orders_DenormCustomer ON Customers AFTER UPDATE
AS
UPDATE Orders
SET CustomerName = i.Name, CustomerCity = i.City
FROM inserted i
WHERE Orders.CustomerID = i.CustomerID;One-to-One: one row in A links to exactly one row in B (e.g., Employee–Passport). One-to-Many: one row in A links to many rows in B (e.g., Customer–Orders). Many-to-Many: requires a junction table with FKs to both sides (e.g., Student–Course via Enrollment).
-- One-to-Many: Customers → Orders
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
CustomerID INT REFERENCES Customers(CustomerID) -- many orders per customer
);
-- Many-to-Many: Students ↔ Courses via junction table
CREATE TABLE Enrollments (
StudentID INT REFERENCES Students(StudentID),
CourseID INT REFERENCES Courses(CourseID),
PRIMARY KEY (StudentID, CourseID),
EnrollDate DATE DEFAULT GETDATE()
);A Natural Key is a real-world identifier (email, SSN) that already exists in the domain. A Surrogate Key is a system-generated identifier (INT IDENTITY, UNIQUEIDENTIFIER) with no business meaning. Surrogate keys are preferred: they never change, are compact, and are immune to business rule changes.
-- Natural key: email as PK (fragile — can change)
CREATE TABLE Users (Email NVARCHAR(255) PRIMARY KEY, Name NVARCHAR(100));
-- Surrogate key: system-generated INT (recommended)
CREATE TABLE Users (
UserID INT IDENTITY(1,1) PRIMARY KEY, -- surrogate
Email NVARCHAR(255) UNIQUE NOT NULL, -- natural key kept as UNIQUE constraint
Name NVARCHAR(100)
);
-- GUID as surrogate (useful for distributed systems / merge replication)
CREATE TABLE Events (
EventID UNIQUEIDENTIFIER DEFAULT NEWSEQUENTIALID() PRIMARY KEY,
EventName NVARCHAR(200)
);IDENTITY(seed, increment) auto-generates sequential integers for a column. The seed is the first value; increment is the step. Use SCOPE_IDENTITY() to retrieve the last inserted value in the current scope — safer than @@IDENTITY, which includes trigger-generated values.
CREATE TABLE Categories (
CategoryID INT IDENTITY(1, 1) PRIMARY KEY, -- starts at 1, increments by 1
Name NVARCHAR(100)
);
INSERT INTO Categories (Name) VALUES ('Electronics');
SELECT SCOPE_IDENTITY() AS NewID; -- returns 1
-- Re-seed the identity counter
DBCC CHECKIDENT ('Categories', RESEED, 100); -- next insert = 101
-- Temporarily allow explicit inserts
SET IDENTITY_INSERT Categories ON;
INSERT INTO Categories (CategoryID, Name) VALUES (999, 'Special');
SET IDENTITY_INSERT Categories OFF;@@IDENTITY returns the last identity value generated in the session — including inside triggers, which can be wrong. SCOPE_IDENTITY() returns the last value in the current scope only — always use this. IDENT_CURRENT('table') returns the last value for a specific table regardless of session.
INSERT INTO Orders (CustomerID, Amount) VALUES (5, 150.00);
-- ✓ Always use SCOPE_IDENTITY in production code
SELECT SCOPE_IDENTITY() AS SafeNewOrderID;
-- ⚠ @@IDENTITY may return identity from a trigger fired by the INSERT
SELECT @@IDENTITY AS MayBeWrong;
-- IDENT_CURRENT: last identity for a named table (any session)
SELECT IDENT_CURRENT('Orders') AS LastOrderIdentity;A Temp Table (#table) is stored in tempdb, has statistics, can be indexed, and is visible to nested stored procedures. A Table Variable (@table) exists in memory (usually), has no statistics, is limited to the current batch, and is not rolled back by ROLLBACK. For large data sets, prefer temp tables.
-- Temp table: survives until session ends or DROP
CREATE TABLE #TempOrders (OrderID INT, Amount DECIMAL(10,2));
INSERT INTO #TempOrders SELECT OrderID, Amount FROM Orders WHERE Amount > 1000;
SELECT * FROM #TempOrders;
DROP TABLE #TempOrders;
-- Table variable: lives only in the current batch
DECLARE @TopCustomers TABLE (CustomerID INT, TotalSpend DECIMAL(12,2));
INSERT INTO @TopCustomers
SELECT CustomerID, SUM(Amount) FROM Orders GROUP BY CustomerID HAVING SUM(Amount) > 5000;
SELECT * FROM @TopCustomers;MERGE performs INSERT, UPDATE, and DELETE in a single statement by comparing a source dataset against a target table. It is ideal for synchronisation (upsert) scenarios — loading a staging table into a production table, for example.
MERGE INTO Products AS target
USING Staging_Products AS source
ON target.ProductID = source.ProductID
WHEN MATCHED AND target.Price <> source.Price THEN
UPDATE SET target.Price = source.Price, target.UpdatedAt = GETDATE()
WHEN NOT MATCHED BY TARGET THEN
INSERT (ProductID, Name, Price) VALUES (source.ProductID, source.Name, source.Price)
WHEN NOT MATCHED BY SOURCE THEN
DELETE -- remove products no longer in source
OUTPUT $action, inserted.ProductID, deleted.ProductID;WITH (NOLOCK) lets a query read uncommitted data, avoiding shared-lock waits and boosting throughput. The danger: dirty reads (reading data that will be rolled back), phantom rows, and even duplicate or missing rows due to page splits. Prefer Read Committed Snapshot Isolation for non-blocking reads without dirty reads.
-- NOLOCK: fast but may return uncommitted / phantom data
SELECT COUNT(*) FROM Orders WITH (NOLOCK);
-- Safer alternative: enable Read Committed Snapshot at DB level
ALTER DATABASE MyDB SET READ_COMMITTED_SNAPSHOT ON;
-- Now READ COMMITTED (default) is non-blocking via row versions
-- No hint needed; no dirty reads possible
-- Check current snapshot settings
SELECT name, is_read_committed_snapshot_on
FROM sys.databases
WHERE name = DB_NAME();Dynamic SQL builds a query string at runtime and executes it. Use sp_executesql with parameters instead of string concatenation — this prevents SQL Injection and allows plan reuse. Never concatenate raw user input into a SQL string.
-- ❌ Dangerous: SQL Injection risk
DECLARE @sql NVARCHAR(500) = 'SELECT * FROM Employees WHERE Name = ''' + @UserInput + '''';
EXEC(@sql);
-- ✓ Safe: parameterized sp_executesql
DECLARE @sql NVARCHAR(500) = N'SELECT * FROM Employees WHERE Name = @Name AND DeptID = @Dept';
DECLARE @params NVARCHAR(100) = N'@Name NVARCHAR(100), @Dept INT';
EXEC sp_executesql @sql, @params, @Name = N'Alice', @Dept = 3;
-- Dynamic column name (object names cannot be parameterized — validate manually)
DECLARE @col NVARCHAR(128) = QUOTENAME('Salary'); -- wraps in [safe brackets]
SET @sql = N'SELECT ' + @col + N' FROM Employees';
EXEC sp_executesql @sql;PIVOT rotates distinct row values into column headers with an aggregate. UNPIVOT does the reverse — turns column headers into rows. For a dynamic list of pivot values, combine with Dynamic SQL to build the column list at runtime.
-- Static PIVOT: quarters as columns
SELECT Department, [Q1], [Q2], [Q3], [Q4]
FROM (
SELECT Department, Quarter, Sales FROM SalesData
) src
PIVOT (
SUM(Sales) FOR Quarter IN ([Q1],[Q2],[Q3],[Q4])
) pvt;
-- UNPIVOT: turn columns back to rows
SELECT Department, Quarter, Sales
FROM PivotedSales
UNPIVOT (
Sales FOR Quarter IN ([Q1],[Q2],[Q3],[Q4])
) unpvt;COALESCE(a, b, c, …) returns the first non-NULL value from any number of arguments — it is ANSI standard and evaluates lazily. ISNULL(a, b) is SQL Server-specific and accepts only two arguments. Prefer COALESCE for portability and multiple fallbacks.
-- ISNULL: only two args
SELECT ISNULL(Phone, 'N/A') AS Contact FROM Employees;
-- COALESCE: tries each value left-to-right
SELECT COALESCE(MobilePhone, HomePhone, WorkPhone, 'No Contact') AS BestPhone
FROM Employees;
-- Useful aggregation trick: build a comma-separated list
SELECT Department,
STUFF((SELECT ', ' + FullName
FROM Employees e2
WHERE e2.Department = e.Department
FOR XML PATH('')), 1, 2, '') AS MemberList
FROM Employees e
GROUP BY Department;The OUTPUT clause returns data from rows affected by INSERT, UPDATE, DELETE, or MERGE. Use INSERTED for new/updated values and DELETED for old/removed values. It eliminates the need for a separate SELECT after a DML operation.
-- Capture newly inserted IDs
DECLARE @NewIDs TABLE (ProductID INT, Name NVARCHAR(100));
INSERT INTO Products (Name, Price)
OUTPUT INSERTED.ProductID, INSERTED.Name INTO @NewIDs
VALUES ('Widget A', 9.99), ('Widget B', 14.99);
SELECT * FROM @NewIDs;
-- Capture old and new values during UPDATE
UPDATE Employees
SET Salary = Salary * 1.10
OUTPUT DELETED.Salary AS OldSalary, INSERTED.Salary AS NewSalary,
INSERTED.EmployeeID
INTO SalaryAudit (EmployeeID, OldSalary, NewSalary)
WHERE Department = 'Engineering';SQL Server 2016+ has built-in JSON functions: FOR JSON PATH/AUTO serialises query results to JSON, OPENJSON parses JSON into rows, JSON_VALUE extracts a scalar, and JSON_QUERY extracts an object or array. JSON is stored as NVARCHAR — use ISJSON() to validate.
-- Query result → JSON
SELECT EmployeeID, FullName, Department
FROM Employees
FOR JSON PATH, ROOT('employees');
-- Parse JSON string into rows
DECLARE @json NVARCHAR(MAX) = '[{"id":1,"name":"Alice"},{"id":2,"name":"Bob"}]';
SELECT * FROM OPENJSON(@json)
WITH (id INT '$.id', name NVARCHAR(100) '$.name');
-- Extract a single value
SELECT JSON_VALUE('{"user":{"city":"NYC"}}', '$.user.city'); -- NYC
-- Validate JSON
SELECT ISJSON('{"valid":true}'); -- 1STRING_AGG (SQL Server 2017+) concatenates values from a group into a single string with a separator. It replaces the old FOR XML PATH('') workaround. The optional WITHIN GROUP (ORDER BY …) controls concatenation order.
-- List employees per department as a comma-separated string
SELECT Department,
STRING_AGG(FullName, ', ') WITHIN GROUP (ORDER BY FullName) AS TeamMembers
FROM Employees
GROUP BY Department;
-- Old workaround (pre-2017, avoid if 2017+ available)
SELECT Department,
STUFF((SELECT ', ' + FullName
FROM Employees e2
WHERE e2.Department = e.Department
ORDER BY FullName
FOR XML PATH(''), TYPE).value('.','NVARCHAR(MAX)'), 1, 2, '') AS TeamMembers
FROM Employees e
GROUP BY Department;A computed column derives its value from an expression involving other columns in the same row. By default it is virtual (computed on read). Adding PERSISTED stores it physically, enabling indexes on it — useful for frequently queried expressions.
CREATE TABLE OrderItems (
OrderItemID INT PRIMARY KEY,
Quantity INT NOT NULL,
UnitPrice MONEY NOT NULL,
LineTotal AS (Quantity * UnitPrice), -- virtual
TaxAmount AS (Quantity * UnitPrice * 0.1) PERSISTED -- stored
);
-- You can index a PERSISTED computed column
CREATE INDEX IX_OrderItems_TaxAmount ON OrderItems (TaxAmount);
-- Query uses it transparently
SELECT OrderItemID, Quantity, UnitPrice, LineTotal, TaxAmount
FROM OrderItems;A SEQUENCE is a database object that generates sequential numbers independently of any table — unlike IDENTITY, it can be shared across multiple tables, used in default expressions, and consumed without inserting a row.
CREATE SEQUENCE dbo.seq_InvoiceNumber
START WITH 10000
INCREMENT BY 1
MINVALUE 10000
MAXVALUE 99999
CYCLE; -- wraps around after MAXVALUE
-- Get next value
SELECT NEXT VALUE FOR dbo.seq_InvoiceNumber; -- 10000
SELECT NEXT VALUE FOR dbo.seq_InvoiceNumber; -- 10001
-- Use as default in a table
CREATE TABLE Invoices (
InvoiceID INT DEFAULT (NEXT VALUE FOR dbo.seq_InvoiceNumber) PRIMARY KEY,
CustomerID INT
);INFORMATION_SCHEMA is a set of ANSI-standard read-only views that expose metadata about tables, columns, routines, and constraints. It is portable across relational databases. For SQL Server-specific details (index info, filegroups, etc.) use the sys.* catalog views instead.
-- List all tables in the current database
SELECT TABLE_NAME, TABLE_TYPE
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE';
-- List all columns with data types
SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Employees'
ORDER BY ORDINAL_POSITION;
-- Find all stored procedures
SELECT ROUTINE_NAME, ROUTINE_TYPE
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE';DMVs (sys.dm_*) expose real-time server state: currently running queries, lock waits, index usage, memory pressure, and more. They are essential for performance monitoring and diagnosing live issues. Data is reset on each SQL Server restart.
-- Active queries with CPU and duration
SELECT r.session_id, r.status, r.cpu_time, r.total_elapsed_time,
t.text AS QueryText
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.status = 'running';
-- Most expensive queries by CPU (from plan cache)
SELECT TOP 10 qs.total_worker_time / qs.execution_count AS AvgCPU,
qs.execution_count, t.text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) t
ORDER BY AvgCPU DESC;
-- Index usage stats
SELECT OBJECT_NAME(i.object_id) AS TableName, i.name,
s.user_seeks, s.user_scans, s.user_lookups, s.user_updates
FROM sys.dm_db_index_usage_stats s
JOIN sys.indexes i ON s.object_id = i.object_id AND s.index_id = i.index_id
WHERE s.database_id = DB_ID();SQL Injection happens when untrusted user input is concatenated directly into a SQL string, allowing an attacker to alter the query logic. Prevention: always use parameterized queries or sp_executesql with parameters — never build SQL by concatenating user input.
-- ❌ Vulnerable: user inputs ' OR '1'='1
DECLARE @login NVARCHAR(100) = ''' OR ''1''=''1';
DECLARE @sql NVARCHAR(500) = 'SELECT * FROM Users WHERE Username = ''' + @login + '''';
-- Executes: SELECT * FROM Users WHERE Username = '' OR '1'='1'
-- Returns ALL users — authentication bypass!
-- ✓ Safe: parameterized (cannot break out of the string literal)
SELECT * FROM Users WHERE Username = @Username AND Password = @Password;
-- or via sp_executesql with @params when dynamic SQL is neededWindows Authentication uses the Windows/Active Directory identity — no password in the connection string, tokens handled by the OS, and recommended by Microsoft. SQL Server Authentication uses a SQL login with username/password — necessary for non-domain clients or cross-domain scenarios.
-- Create a SQL Server login (Mixed Mode must be enabled)
CREATE LOGIN AppUser WITH PASSWORD = 'Str0ngP@ssword!';
-- Create a Windows login
CREATE LOGIN [DOMAIN\ServiceAccount] FROM WINDOWS;
-- Create a database user mapped to the login
USE MyDatabase;
CREATE USER AppUser FOR LOGIN AppUser;
-- Grant least-privilege permissions
GRANT SELECT, INSERT, UPDATE ON dbo.Orders TO AppUser;
DENY DELETE ON dbo.Orders TO AppUser;RLS restricts which rows a user can see or modify based on their identity, transparently — the application needs no changes. You define an inline TVF predicate and attach it to a security policy on the table.
-- Predicate function: user sees only their own rows
CREATE FUNCTION dbo.fn_RLS_Orders (@SalesRep NVARCHAR(100))
RETURNS TABLE WITH SCHEMABINDING
AS
RETURN (
SELECT 1 AS Result
WHERE @SalesRep = USER_NAME() -- current DB user
OR IS_MEMBER('db_owner') = 1 -- admins see all
);
-- Attach to the Orders table
CREATE SECURITY POLICY dbo.SalesRepPolicy
ADD FILTER PREDICATE dbo.fn_RLS_Orders(SalesRep) ON dbo.Orders
WITH (STATE = ON);
-- Now: SELECT * FROM Orders returns only current user's rows automaticallyTDE encrypts the database files on disk (MDF, LDF, and backups) transparently — data is decrypted in memory when read. It protects against physical theft of disk files without any application changes. Introduced in SQL Server 2008; meets PCI-DSS and HIPAA encryption-at-rest requirements.
-- 1. Create a master key in master DB
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MasterK3y!';
-- 2. Create a certificate
CREATE CERTIFICATE TDE_Cert WITH SUBJECT = 'TDE Certificate';
-- 3. Create a database encryption key
USE MyDatabase;
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDE_Cert;
-- 4. Enable TDE
ALTER DATABASE MyDatabase SET ENCRYPTION ON;
-- Verify
SELECT name, is_encrypted FROM sys.databases WHERE name = 'MyDatabase';SQL Server Audit (2008+) records security-relevant events to a file, Windows Security Log, or Application Log. You define a Server Audit object and then Audit Specifications at the server or database level. Required for compliance (SOX, PCI-DSS, HIPAA).
-- Create an audit (writes to file)
CREATE SERVER AUDIT MyAudit
TO FILE (FILEPATH = 'C:\AuditLogs\')
WITH (ON_FAILURE = CONTINUE);
ALTER SERVER AUDIT MyAudit WITH (STATE = ON);
-- Database-level audit specification: log SELECT on sensitive table
CREATE DATABASE AUDIT SPECIFICATION AuditSensitiveData
FOR SERVER AUDIT MyAudit
ADD (SELECT ON dbo.SalaryInfo BY PUBLIC)
WITH (STATE = ON);
-- Read audit log
SELECT event_time, action_id, session_server_principal_name, statement
FROM sys.fn_get_audit_file('C:\AuditLogs\*.sqlaudit', DEFAULT, DEFAULT);Full — complete copy of the database. Differential — only pages changed since the last Full. Transaction Log — the log since the last log backup; enables point-in-time recovery. File/Filegroup — individual files for very large databases. Restore chain: Full → Differential → Log backups.
-- Full backup
BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backups\AdventureWorks_Full.bak'
WITH COMPRESSION, STATS = 10;
-- Differential backup (only changes since last Full)
BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backups\AdventureWorks_Diff.bak'
WITH DIFFERENTIAL, COMPRESSION;
-- Transaction log backup (requires Full or Bulk-Logged recovery model)
BACKUP LOG AdventureWorks
TO DISK = 'C:\Backups\AdventureWorks_Log.trn'
WITH COMPRESSION;Simple — log is auto-truncated after each checkpoint; no log backups, no point-in-time recovery. Full — log is retained until backed up; full point-in-time recovery. Bulk-Logged — minimally logs bulk operations for performance while still allowing log backups.
-- Check current recovery model
SELECT name, recovery_model_desc
FROM sys.databases
WHERE name = 'AdventureWorks';
-- Change recovery model
ALTER DATABASE AdventureWorks SET RECOVERY FULL;
ALTER DATABASE AdventureWorks SET RECOVERY SIMPLE;
ALTER DATABASE AdventureWorks SET RECOVERY BULK_LOGGED;
-- After switching FROM SIMPLE to FULL, take a Full backup immediately
-- to start the log chain
BACKUP DATABASE AdventureWorks TO DISK = 'C:\Backups\AW_AfterModelChange.bak';Always On AG (Enterprise Edition) provides HA and DR by replicating databases to one or more secondary replicas. Synchronous replicas guarantee zero data loss; asynchronous replicas reduce latency overhead at the cost of potential data loss. Automatic failover is supported in synchronous mode.
-- Create an Availability Group (simplified)
CREATE AVAILABILITY GROUP AG_Production
WITH (AUTOMATED_BACKUP_PREFERENCE = SECONDARY)
FOR DATABASE AdventureWorks
REPLICA ON
'SQLNode1' WITH (ENDPOINT_URL = 'TCP://SQLNode1:5022',
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
FAILOVER_MODE = AUTOMATIC),
'SQLNode2' WITH (ENDPOINT_URL = 'TCP://SQLNode2:5022',
AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
FAILOVER_MODE = MANUAL);
-- Monitor health
SELECT ag.name, ar.replica_server_name, ars.role_desc, ars.synchronization_health_desc
FROM sys.availability_groups ag
JOIN sys.availability_replicas ar ON ag.group_id = ar.group_id
JOIN sys.dm_hadr_availability_replica_states ars ON ar.replica_id = ars.replica_id;Log Shipping automatically backs up transaction logs on the primary, copies the files to one or more secondary servers, and restores them — keeping the secondary database close to the primary. It is a simple HA/DR solution available in SQL Server Standard Edition but requires manual failover.
-- Check log shipping status
SELECT primary_server, primary_database,
secondary_server, secondary_database,
last_copied_file, last_restored_file,
last_restored_date
FROM msdb.dbo.log_shipping_monitor_secondary;
-- Monitor backup job on primary
SELECT ls.primary_database, lsb.last_backup_date, lsb.last_backup_file
FROM msdb.dbo.log_shipping_primary_databases ls
JOIN msdb.dbo.log_shipping_monitor_primary lsb
ON ls.primary_id = lsb.primary_id;Point-in-Time Recovery restores a database to an exact moment, undoing accidental deletes or updates. Requirements: Full recovery model and an unbroken chain of transaction log backups. Use STOPAT or STOPATMARK when restoring the final log backup.
-- Restore to a specific point in time (e.g. just before accidental delete)
-- Step 1: Restore full backup WITH NORECOVERY (leaves DB in restoring state)
RESTORE DATABASE AdventureWorks
FROM DISK = 'C:\Backups\AW_Full.bak'
WITH NORECOVERY;
-- Step 2: Restore differential WITH NORECOVERY
RESTORE DATABASE AdventureWorks
FROM DISK = 'C:\Backups\AW_Diff.bak'
WITH NORECOVERY;
-- Step 3: Restore log backup, STOPAT the target time
RESTORE LOG AdventureWorks
FROM DISK = 'C:\Backups\AW_Log.trn'
WITH RECOVERY, STOPAT = '2025-06-10 14:23:00'; -- one second before the accidentUse GROUP BY … HAVING COUNT(*) > 1 to identify duplicates. To delete all but one copy, assign ROW_NUMBER() partitioned by the duplicate columns, then delete rows with rn > 1.
-- Find duplicates
SELECT Email, COUNT(*) AS Cnt
FROM Users
GROUP BY Email
HAVING COUNT(*) > 1;
-- Delete duplicates, keep the row with the lowest UserID
WITH Dupes AS (
SELECT UserID,
ROW_NUMBER() OVER (PARTITION BY Email ORDER BY UserID ASC) AS rn
FROM Users
)
DELETE FROM Dupes WHERE rn > 1;
-- Verify
SELECT Email, COUNT(*) FROM Users GROUP BY Email HAVING COUNT(*) > 1;
-- (no rows returned = clean)Use OFFSET … FETCH NEXT (SQL Server 2012+) — clean, ANSI-standard, and optimizer-friendly. It requires an ORDER BY clause. For older versions, wrap a ROW_NUMBER() in a CTE and filter on the row number range.
-- OFFSET-FETCH: page 3, 10 rows per page
DECLARE @Page INT = 3;
DECLARE @PageSize INT = 10;
SELECT ProductID, Name, Price
FROM Products
ORDER BY Name
OFFSET (@Page - 1) * @PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY;
-- Old approach with ROW_NUMBER (pre-2012)
WITH Paged AS (
SELECT *, ROW_NUMBER() OVER (ORDER BY Name) AS rn FROM Products
)
SELECT * FROM Paged WHERE rn BETWEEN 21 AND 30;A Recursive CTE references itself and consists of an anchor (base case, runs once) and a recursive member (references the CTE, runs repeatedly), joined by UNION ALL. SQL Server adds MAXRECURSION protection (default 100 levels) to prevent infinite loops.
-- Walk an org chart from a given employee downward
WITH OrgChart AS (
-- Anchor: start with the CEO (no manager)
SELECT EmployeeID, FullName, ManagerID, 0 AS Level
FROM Employees
WHERE ManagerID IS NULL
UNION ALL
-- Recursive: join each employee to their manager found so far
SELECT e.EmployeeID, e.FullName, e.ManagerID, oc.Level + 1
FROM Employees e
JOIN OrgChart oc ON e.ManagerID = oc.EmployeeID
)
SELECT REPLICATE(' ', Level) + FullName AS OrgTree, Level
FROM OrgChart
OPTION (MAXRECURSION 50); -- safety limitUNION combines result sets and removes duplicate rows (implicit DISTINCT) — it sorts or hashes the data which adds overhead. UNION ALL appends all rows including duplicates — faster because it skips deduplication. If you know duplicates cannot exist, always prefer UNION ALL.
-- UNION: removes duplicates (adds sort/hash step)
SELECT CustomerID FROM Orders
UNION
SELECT CustomerID FROM Quotes; -- unique CustomerIDs across both tables
-- UNION ALL: faster, keeps all rows including duplicates
SELECT 'Orders' AS Source, OrderID, Amount FROM Orders
UNION ALL
SELECT 'Returns', ReturnID, Amount FROM Returns; -- full combined history
-- Rule of thumb: use UNION ALL unless you explicitly need deduplication
-- Profile with SET STATISTICS IO ON to see the differenceINTERSECT returns only rows that appear in both queries. EXCEPT returns rows in the first query that do not appear in the second. Both remove duplicates automatically. Useful for comparing datasets and finding discrepancies between tables.
-- INTERSECT: customers who placed both an order AND a return
SELECT CustomerID FROM Orders
INTERSECT
SELECT CustomerID FROM Returns;
-- EXCEPT: customers in Orders but NOT in Returns
SELECT CustomerID FROM Orders
EXCEPT
SELECT CustomerID FROM Returns;
-- Compare two tables for differences
SELECT * FROM Production.Products
EXCEPT
SELECT * FROM Staging.Products; -- rows in Production not in StagingIN checks membership in a list or subquery result. EXISTS checks whether a subquery returns any rows at all — it short-circuits on the first match, making it faster for correlated checks. EXISTS also handles NULL correctly; IN with a NULL in the list returns NULL (neither true nor false).
-- IN: compares against a result set
SELECT FullName FROM Employees
WHERE DeptID IN (SELECT DeptID FROM Departments WHERE Location = 'NYC');
-- EXISTS: stops at first match — efficient for "does any related row exist?"
SELECT FullName FROM Employees e
WHERE EXISTS (SELECT 1 FROM Orders o WHERE o.SalesRepID = e.EmployeeID);
-- NULL trap with IN (returns no rows if subquery has any NULL!)
SELECT * FROM A WHERE ID NOT IN (SELECT ID FROM B); -- ⚠ if B.ID has NULLs
-- Safe alternative
SELECT * FROM A WHERE NOT EXISTS (SELECT 1 FROM B WHERE B.ID = A.ID);A SARGable (Search ARGument Able) predicate can be satisfied by an index seek. Wrapping a column in a function or applying arithmetic to it makes it non-SARGable, forcing a scan. Always move transformations to the constant side of the comparison.
-- ❌ Non-SARGable: function on the column → Index Scan
WHERE YEAR(OrderDate) = 2024
WHERE LEFT(PostalCode, 3) = '100'
WHERE Salary * 1.1 > 80000
-- ✓ SARGable equivalents → Index Seek
WHERE OrderDate >= '2024-01-01' AND OrderDate < '2025-01-01'
WHERE PostalCode LIKE '100%'
WHERE Salary > 80000 / 1.1
-- ❌ Implicit conversion: non-SARGable (column is INT, value is VARCHAR)
WHERE EmployeeID = '123'
-- ✓ Explicit match types
WHERE EmployeeID = 123Use conditional aggregation — MAX(CASE WHEN … THEN … END) — which works in all SQL versions, is often clearer, and is more flexible when you need complex expressions or partial aggregates per column.
-- Conditional aggregation as PIVOT alternative
SELECT Department,
MAX(CASE WHEN Quarter = 'Q1' THEN Sales END) AS Q1,
MAX(CASE WHEN Quarter = 'Q2' THEN Sales END) AS Q2,
MAX(CASE WHEN Quarter = 'Q3' THEN Sales END) AS Q3,
MAX(CASE WHEN Quarter = 'Q4' THEN Sales END) AS Q4,
SUM(Sales) AS Annual
FROM SalesData
GROUP BY Department
ORDER BY Department;Use SUM() OVER (ORDER BY …) with a window frame. By default the frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Use ROWS BETWEEN for precise control and better performance on large datasets.
SELECT
OrderDate,
DailyRevenue,
SUM(DailyRevenue) OVER (
ORDER BY OrderDate
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS RunningTotal,
AVG(DailyRevenue) OVER (
ORDER BY OrderDate
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS Rolling7DayAvg
FROM DailyRevenue
ORDER BY OrderDate;Use LEAD() or LAG() to compare each row to its neighbor. A gap exists wherever the difference between consecutive values is greater than 1 (or one day for dates).
-- Find gaps in OrderID sequence
WITH Numbered AS (
SELECT OrderID,
LEAD(OrderID) OVER (ORDER BY OrderID) AS NextID
FROM Orders
)
SELECT OrderID + 1 AS GapStart,
NextID - 1 AS GapEnd,
NextID - OrderID - 1 AS MissingCount
FROM Numbered
WHERE NextID - OrderID > 1;
-- Find gaps in a date series (e.g. missing trading days)
WITH Gaps AS (
SELECT SaleDate,
LEAD(SaleDate) OVER (ORDER BY SaleDate) AS NextDate
FROM DailySales
)
SELECT DATEADD(DAY, 1, SaleDate) AS MissingFrom,
DATEADD(DAY,-1, NextDate) AS MissingTo
FROM Gaps
WHERE DATEDIFF(DAY, SaleDate, NextDate) > 1;Wrap risky code in BEGIN TRY … END TRY. On any error, execution jumps to BEGIN CATCH … END CATCH where you can inspect error details, rollback, log, and rethrow. Use THROW (2012+) instead of RAISERROR to rethrow with the original error number.
BEGIN TRY
BEGIN TRANSACTION;
INSERT INTO Orders (CustomerID, Amount) VALUES (999, 500); -- FK may fail
UPDATE Inventory SET Stock = Stock - 1 WHERE ProductID = 5;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
-- Log the error
INSERT INTO ErrorLog (ErrorNumber, ErrorMessage, ErrorLine, ErrorTime)
VALUES (ERROR_NUMBER(), ERROR_MESSAGE(), ERROR_LINE(), GETDATE());
THROW; -- re-raise original error to the caller
END CATCH;CAST is ANSI-standard: CAST(value AS type). CONVERT is SQL Server-specific and adds an optional style parameter for date formatting. TRY_CAST and TRY_CONVERT return NULL on failure instead of raising an error.
-- CAST (ANSI)
SELECT CAST('2024-06-10' AS DATE);
SELECT CAST(12345.678 AS INT); -- 12345 (truncates)
SELECT CAST(Price AS NVARCHAR(20)) FROM Products;
-- CONVERT with style (SQL Server specific)
SELECT CONVERT(VARCHAR, GETDATE(), 103); -- DD/MM/YYYY e.g. 10/06/2025
SELECT CONVERT(VARCHAR, GETDATE(), 120); -- YYYY-MM-DD HH:MI:SS
-- TRY_CAST: returns NULL instead of error
SELECT TRY_CAST('abc' AS INT); -- NULL (no error)
SELECT TRY_CONVERT(DATE, '99/99/9999'); -- NULLA cursor iterates over a result set row by row. Because SQL Server is optimised for set-based operations, cursors are orders of magnitude slower than equivalent set-based queries. Replace cursors with WHILE loops on temp tables, Window Functions, or set-based CTEs wherever possible.
-- ❌ Cursor (avoid unless absolutely necessary)
DECLARE @ID INT, @Name NVARCHAR(100);
DECLARE cur CURSOR FAST_FORWARD FOR SELECT EmployeeID, FullName FROM Employees;
OPEN cur;
FETCH NEXT FROM cur INTO @ID, @Name;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @Name;
FETCH NEXT FROM cur INTO @ID, @Name;
END;
CLOSE cur; DEALLOCATE cur;
-- ✓ Set-based equivalent
UPDATE Employees SET Status = 'Reviewed'
WHERE HireDate < '2020-01-01'; -- process all qualifying rows at onceT-SQL's WHILE loop repeats a block as long as a condition is true. Use BREAK to exit immediately and CONTINUE to skip the rest of the current iteration. Prefer set-based solutions; use WHILE only for batch processing with explicit chunking.
-- Batch-delete in chunks to avoid a huge transaction log
DECLARE @BatchSize INT = 1000;
DECLARE @Deleted INT = 1;
WHILE @Deleted > 0
BEGIN
DELETE TOP (@BatchSize)
FROM AuditLog
WHERE LogDate < '2022-01-01';
SET @Deleted = @@ROWCOUNT;
PRINT 'Deleted: ' + CAST(@Deleted AS VARCHAR);
WAITFOR DELAY '00:00:01'; -- brief pause between batches
END;
PRINT 'Cleanup complete.';CROSS APPLY works like an INNER JOIN with a table-valued function or subquery that references the outer row — it runs the right side for each outer row and returns matching rows. OUTER APPLY is like LEFT JOIN — returns the outer row even when the right side produces nothing.
-- CROSS APPLY: top 3 orders per customer
SELECT c.CustomerID, c.Name, o.OrderID, o.Amount
FROM Customers c
CROSS APPLY (
SELECT TOP 3 OrderID, Amount
FROM Orders o
WHERE o.CustomerID = c.CustomerID
ORDER BY Amount DESC
) o;
-- OUTER APPLY: include customers with no orders (NULL)
SELECT c.CustomerID, c.Name, o.OrderID, o.Amount
FROM Customers c
OUTER APPLY (
SELECT TOP 1 OrderID, Amount FROM Orders o
WHERE o.CustomerID = c.CustomerID
ORDER BY OrderDate DESC
) o;When SQL Server compiles a stored procedure, it "sniffs" the first parameter values and builds an optimal plan for them. If those values are unrepresentative, subsequent calls with different parameters use a bad plan. Fixes: OPTION (RECOMPILE), OPTIMIZE FOR, or local variable trick.
-- Symptom: great for CustomerID=1 (1 order), terrible for CustomerID=2 (1M orders)
CREATE PROCEDURE usp_GetOrders @CustomerID INT AS
SELECT * FROM Orders WHERE CustomerID = @CustomerID;
-- Fix 1: RECOMPILE per execution (generates a fresh plan each time)
SELECT * FROM Orders WHERE CustomerID = @CustomerID OPTION (RECOMPILE);
-- Fix 2: optimize for a typical value
SELECT * FROM Orders WHERE CustomerID = @CustomerID
OPTION (OPTIMIZE FOR (@CustomerID = 500));
-- Fix 3: local variable (hides the parameter from sniffing)
DECLARE @LocalID INT = @CustomerID;
SELECT * FROM Orders WHERE CustomerID = @LocalID;Query Store (SQL Server 2016+) captures query text, execution plans, and runtime statistics persistently. It detects plan regressions automatically and lets you force a known-good plan. Enable per database; overhead is minimal.
-- Enable Query Store
ALTER DATABASE MyDB SET QUERY_STORE ON;
ALTER DATABASE MyDB SET QUERY_STORE (
OPERATION_MODE = READ_WRITE,
MAX_STORAGE_SIZE_MB = 500,
QUERY_CAPTURE_MODE = AUTO
);
-- Find top 10 queries by average CPU
SELECT TOP 10 qt.query_sql_text, rs.avg_cpu_time, rs.execution_count
FROM sys.query_store_query_text qt
JOIN sys.query_store_query q ON qt.query_text_id = q.query_text_id
JOIN sys.query_store_plan p ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
ORDER BY rs.avg_cpu_time DESC;
-- Force a specific plan
EXEC sys.sp_query_store_force_plan @query_id = 42, @plan_id = 7;A Columnstore Index stores data column-by-column with high compression instead of row-by-row. It dramatically accelerates analytical / aggregation queries (Data Warehouse, OLAP) by scanning only the needed columns. SQL Server 2016+ supports an updateable Clustered Columnstore Index.
-- Non-clustered columnstore on a fact table (OLAP)
CREATE NONCLUSTERED COLUMNSTORE INDEX NCI_Sales_Analytics
ON FactSales (SaleDate, ProductID, RegionID, Amount, Quantity);
-- Clustered columnstore (replaces the entire table storage)
CREATE CLUSTERED COLUMNSTORE INDEX CCI_FactSales ON FactSales;
-- Measure compression ratio
SELECT i.name, s.used_page_count * 8 / 1024.0 AS UsedMB,
s.reserved_page_count * 8 / 1024.0 AS ReservedMB
FROM sys.dm_db_partition_stats s
JOIN sys.indexes i ON s.object_id = i.object_id AND s.index_id = i.index_id
WHERE i.object_id = OBJECT_ID('FactSales');SQL Server records index recommendations in sys.dm_db_missing_index_* DMVs whenever the optimizer estimates that an index would have helped. Sort by impact (seeks × average improvement) to prioritise which index to create first.
SELECT TOP 20
ROUND(s.avg_total_user_cost * s.avg_user_impact * (s.user_seeks + s.user_scans), 0)
AS ImpactScore,
d.statement AS TableName,
d.equality_columns,
d.inequality_columns,
d.included_columns,
s.user_seeks, s.user_scans
FROM sys.dm_db_missing_index_details d
JOIN sys.dm_db_missing_index_groups g ON d.index_handle = g.index_handle
JOIN sys.dm_db_missing_index_group_stats s ON g.index_group_handle = s.group_handle
WHERE d.database_id = DB_ID()
ORDER BY ImpactScore DESC;Partitioning divides a large table into physical segments (partitions) based on a column value range (usually a date). Queries that filter on the partition key only scan relevant partitions (Partition Elimination). It also simplifies archiving via fast Partition Switching.
-- Partition function: split by year
CREATE PARTITION FUNCTION pf_OrderYear (DATE)
AS RANGE RIGHT FOR VALUES ('2022-01-01','2023-01-01','2024-01-01','2025-01-01');
-- Partition scheme: map to filegroups
CREATE PARTITION SCHEME ps_OrderYear
AS PARTITION pf_OrderYear ALL TO ([PRIMARY]);
-- Partitioned table
CREATE TABLE Orders (
OrderID INT, OrderDate DATE, Amount MONEY
) ON ps_OrderYear (OrderDate);
-- Check which partition a row lands in
SELECT $PARTITION.pf_OrderYear('2023-07-15'); -- returns partition numberKey date functions: GETDATE() (current local datetime), GETUTCDATE() (UTC), DATEADD(part, n, date), DATEDIFF(part, start, end), DATEPART(part, date), FORMAT(date, 'pattern'), and EOMONTH(date) for end-of-month.
SELECT
GETDATE() AS Now,
GETUTCDATE() AS NowUTC,
CAST(GETDATE() AS DATE) AS TodayOnly,
DATEADD(MONTH, 3, GETDATE()) AS In3Months,
DATEADD(DAY, -7, GETDATE()) AS OneWeekAgo,
DATEDIFF(YEAR, '1990-05-15', GETDATE()) AS Age,
DATEDIFF(DAY, '2025-01-01', GETDATE()) AS DaysSinceNewYear,
DATEPART(WEEKDAY, GETDATE()) AS DayOfWeek,
EOMONTH(GETDATE()) AS LastDayOfMonth,
FORMAT(GETDATE(), 'dd/MM/yyyy HH:mm') AS Formatted;Essential string functions: LEN, SUBSTRING, CHARINDEX, REPLACE, TRIM/LTRIM/RTRIM, UPPER/LOWER, CONCAT, STRING_SPLIT, PATINDEX, and FORMAT. Strings are 1-based in SQL Server.
DECLARE @s NVARCHAR(100) = ' Hello, SQL World! ';
SELECT
LEN(TRIM(@s)) AS Length, -- 18
TRIM(@s) AS Trimmed,
UPPER(TRIM(@s)) AS Upper,
SUBSTRING(TRIM(@s), 1, 5) AS First5, -- Hello
CHARINDEX('SQL', @s) AS SQLPosition, -- 10
REPLACE(@s, 'World', 'Server') AS Replaced,
CONCAT(TRIM(@s), ' — nice!') AS Concat,
LEFT(TRIM(@s), 5) AS Left5,
RIGHT(TRIM(@s), 6) AS Right6; -- World!
-- Split CSV string into rows
SELECT value FROM STRING_SPLIT('apple,banana,cherry', ',');DATETIME2 has a wider date range (0001–9999 vs 1753–9999), higher precision (up to 100 nanoseconds), less storage at lower precision, and aligns with ISO 8601. Microsoft recommends using DATETIME2 for all new development.
-- DATETIME: 8 bytes, 1753-01-01 to 9999-12-31, ~3ms precision
DECLARE @dt DATETIME = '2025-06-10 14:23:45.123';
-- DATETIME2(n): 6-8 bytes, 0001-01-01 to 9999-12-31, 100ns precision
DECLARE @dt2 DATETIME2(7) = '2025-06-10 14:23:45.1234567';
SELECT @dt, @dt2;
-- Use DATE and TIME separately when you don't need both
DECLARE @d DATE = '2025-06-10'; -- 3 bytes
DECLARE @t TIME(0) = '14:23:45'; -- 3 bytes (0 decimal places)
-- DATETIMEOFFSET: stores timezone offset
DECLARE @dto DATETIMEOFFSET = SYSDATETIMEOFFSET(); -- '2025-06-10 14:23:45 +03:00'IIF(condition, true_val, false_val) is a compact inline conditional — shorthand for a two-branch CASE WHEN. CHOOSE(index, val1, val2, …) returns a value from a list by 1-based position. Both were added in SQL Server 2012 for readability.
-- IIF: cleaner than CASE for simple true/false
SELECT FullName,
IIF(Salary > 80000, 'Senior', 'Junior') AS Band
FROM Employees;
-- CHOOSE: look up by 1-based index
SELECT FullName,
CHOOSE(DATEPART(QUARTER, HireDate), 'Q1','Q2','Q3','Q4') AS HireQuarter
FROM Employees;
-- Equivalent CASE WHEN (still preferred for complex logic)
SELECT IIF(1=1, 'Yes', 'No'); -- Yes
SELECT CHOOSE(3, 'Mon','Tue','Wed','Thu','Fri'); -- WedA Filtered Index is a Non-Clustered Index with a WHERE clause — it indexes only a subset of rows. It is smaller, has less maintenance overhead, and can enforce conditional uniqueness. Ideal for columns with many NULLs or sparse, selective data.
-- Only index active orders (excludes archived rows)
CREATE NONCLUSTERED INDEX IX_Orders_Active
ON Orders (CustomerID, OrderDate)
WHERE Status = 'Active';
-- Conditional UNIQUE: only one open order per customer at a time
CREATE UNIQUE INDEX UX_OneOpenOrderPerCustomer
ON Orders (CustomerID)
WHERE Status = 'Open';
-- Index only non-NULL values (columns with many NULLs)
CREATE INDEX IX_Employees_ManagerID
ON Employees (ManagerID)
WHERE ManagerID IS NOT NULL;In-Memory OLTP (SQL Server 2014+) stores tables entirely in RAM using lock-free, latch-free data structures. Natively compiled stored procedures compile T-SQL to machine code at creation time. Delivers 30–100x throughput gains for high-volume OLTP workloads. Requires careful schema design within certain limitations.
-- Enable In-Memory for the database
ALTER DATABASE MyDB ADD FILEGROUP InMemFG CONTAINS MEMORY_OPTIMIZED_DATA;
ALTER DATABASE MyDB ADD FILE (NAME='InMemFile', FILENAME='C:\InMem\MyDB_InMem')
TO FILEGROUP InMemFG;
-- Create a memory-optimized table (DURABILITY = SCHEMA_AND_DATA or SCHEMA_ONLY)
CREATE TABLE dbo.SessionCache (
SessionID NVARCHAR(50) NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=1000000),
UserID INT NOT NULL,
ExpiresAt DATETIME2 NOT NULL,
INDEX IX_UserID NONCLUSTERED (UserID)
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY);
-- Natively compiled stored procedure
CREATE PROCEDURE usp_UpsertSession @SessionID NVARCHAR(50), @UserID INT, @Exp DATETIME2
WITH NATIVE_COMPILATION, SCHEMABINDING
AS BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'English')
IF EXISTS (SELECT 1 FROM dbo.SessionCache WHERE SessionID = @SessionID)
UPDATE dbo.SessionCache SET ExpiresAt = @Exp WHERE SessionID = @SessionID;
ELSE
INSERT dbo.SessionCache VALUES (@SessionID, @UserID, @Exp);
END;OLTP (Online Transaction Processing) handles day-to-day operations: fast single-row inserts, updates, deletes on a normalised schema. OLAP (Online Analytical Processing) handles reporting and analysis: complex aggregations over millions of rows on a denormalised Star/Snowflake schema with Columnstore Indexes.
-- OLTP pattern: precise, indexed, fast single-row operation
SELECT OrderID, Amount FROM Orders WHERE OrderID = 98765;
UPDATE Inventory SET Stock = Stock - 1 WHERE ProductID = 42;
-- OLAP pattern: aggregate across the full fact table
SELECT
d.Year, d.Quarter, p.Category,
SUM(f.SalesAmount) AS Revenue,
COUNT(DISTINCT f.CustomerKey) AS UniqueCustomers
FROM FactSales f
JOIN DimDate d ON f.DateKey = d.DateKey
JOIN DimProduct p ON f.ProductKey = p.ProductKey
GROUP BY d.Year, d.Quarter, p.Category
ORDER BY d.Year, d.Quarter;TRY_PARSE converts a string to a date/time or numeric type with culture support, returning NULL on failure instead of an error. Use it when input strings may have varying locale formats (e.g., US vs European date formats).
-- TRY_PARSE with culture
SELECT TRY_PARSE('06/10/2025' AS DATE USING 'en-US'); -- Jun 10 (US: MM/DD/YYYY)
SELECT TRY_PARSE('06/10/2025' AS DATE USING 'en-GB'); -- Oct 6 (UK: DD/MM/YYYY)
SELECT TRY_PARSE('not-a-date' AS DATE USING 'en-US'); -- NULL (no error)
-- TRY_CONVERT: type-safe conversion
SELECT TRY_CONVERT(INT, '123abc'); -- NULL
SELECT TRY_CONVERT(INT, '123'); -- 123
-- Practical: import a CSV with mixed/invalid dates
INSERT INTO Orders (OrderDate)
SELECT TRY_PARSE(RawDate AS DATE USING 'en-US')
FROM StagingOrders
WHERE TRY_PARSE(RawDate AS DATE USING 'en-US') IS NOT NULL;DBCC (Database Console Commands) are maintenance and diagnostic commands. DBCC CHECKDB validates database integrity. DBCC FREEPROCCACHE clears the plan cache. DBCC SHOW_STATISTICS inspects index statistics. DBCC SHRINKFILE reduces file size. Use them carefully in production.
-- Check database integrity (run regularly)
DBCC CHECKDB ('AdventureWorks') WITH NO_INFOMSGS, ALL_ERRORMSGS;
-- Clear plan cache (forces recompilation of all queries — use carefully)
DBCC FREEPROCCACHE;
-- Clear a specific plan from cache
DECLARE @plan_handle VARBINARY(64) = 0x...;
DBCC FREEPROCCACHE (@plan_handle);
-- View statistics for a specific index
DBCC SHOW_STATISTICS ('Orders', 'IX_Orders_CustomerID');
-- Re-seed identity counter
DBCC CHECKIDENT ('Orders', RESEED, 0);A Database is a physical container with its own data files (MDF/LDF) and is the boundary for backups, recovery, and many security settings. A Schema is a logical namespace inside a database (like dbo, sales, hr) for organising objects and applying permissions at a group level.
-- Create separate schemas for different domains
CREATE SCHEMA Sales;
CREATE SCHEMA HR;
CREATE SCHEMA Finance;
-- Create objects inside a schema
CREATE TABLE Sales.Orders (OrderID INT PRIMARY KEY, Amount MONEY);
CREATE TABLE HR.Employees (EmployeeID INT PRIMARY KEY, Name NVARCHAR(100));
CREATE TABLE Finance.Budgets (BudgetID INT PRIMARY KEY, FYYear INT);
-- Grant permissions at the schema level (affects all objects in it)
GRANT SELECT ON SCHEMA::Sales TO SalesAnalyst;
GRANT SELECT ON SCHEMA::Finance TO CFOTeam;
-- Reference with 4-part name across databases
SELECT * FROM OtherDB.Sales.Orders WHERE Amount > 1000;