Transactions and ACID, CTEs, stored procedures, triggers, views, sharding and cursors.
A Common Table Expression (CTE) is a temporary named result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. CTEs make complex queries more readable and maintainable.
WITH high_earners AS (
SELECT employee_id, name, salary
FROM employees
WHERE salary > 100000
)
SELECT * FROM high_earners
ORDER BY salary DESC;
WITH RECURSIVE org_hierarchy AS (
-- Anchor member
SELECT employee_id, name, manager_id, 1 as level
FROM employees
WHERE manager_id IS NULL
UNION ALL
-- Recursive member
SELECT e.employee_id, e.name, e.manager_id, oh.level + 1
FROM employees e
INNER JOIN org_hierarchy oh ON e.manager_id = oh.employee_id
)
SELECT * FROM org_hierarchy;
A transaction is a logical unit of work that contains one or more SQL statements. ACID properties ensure reliable processing of database transactions.
BEGIN TRANSACTION;
-- Transfer $100 from account A to B
UPDATE accounts SET balance = balance - 100 WHERE account_id = 'A';
UPDATE accounts SET balance = balance + 100 WHERE account_id = 'B';
-- Check for errors
IF @@ERROR != 0
ROLLBACK TRANSACTION;
ELSE
COMMIT TRANSACTION;
-- SQL Server SET TRANSACTION ISOLATION LEVEL READ COMMITTED; -- MySQL SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; -- PostgreSQL SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
Window functions perform calculations across a set of rows related to the current row. PARTITION BY divides rows into groups, ORDER BY determines order within partitions.
SELECT
employee_id,
name,
department,
salary,
-- Running total within department
SUM(salary) OVER (
PARTITION BY department
ORDER BY hire_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) as running_total,
-- Rank within department
RANK() OVER (
PARTITION BY department
ORDER BY salary DESC
) as dept_salary_rank,
-- Compare to department average
salary - AVG(salary) OVER (PARTITION BY department) as diff_from_avg,
-- Moving average (last 3 months)
AVG(sales) OVER (
PARTITION BY salesperson_id
ORDER BY month_date
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) as moving_avg_3mo
FROM employees;
-- Find top 3 earners in each department
WITH ranked_employees AS (
SELECT
name,
department,
salary,
DENSE_RANK() OVER (
PARTITION BY department
ORDER BY salary DESC
) as rank
FROM employees
)
SELECT * FROM ranked_employees WHERE rank <= 3;
-- Calculate month-over-month growth
SELECT
month,
revenue,
LAG(revenue, 1) OVER (ORDER BY month) as prev_month_rev,
revenue - LAG(revenue, 1) OVER (ORDER BY month) as growth,
(revenue - LAG(revenue, 1) OVER (ORDER BY month)) * 100.0 /
LAG(revenue, 1) OVER (ORDER BY month) as growth_pct
FROM monthly_sales;
Stored procedures and functions are database objects containing SQL statements, but they serve different purposes and have different capabilities.
-- Create stored procedure
CREATE OR REPLACE PROCEDURE update_salary(
p_employee_id INT,
p_percentage DECIMAL(5,2)
)
LANGUAGE plpgsql -- PostgreSQL
AS $$
BEGIN
UPDATE employees
SET salary = salary * (1 + p_percentage/100)
WHERE employee_id = p_employee_id;
IF NOT FOUND THEN
RAISE NOTICE 'Employee % not found', p_employee_id;
END IF;
COMMIT;
END;
$$;
-- Call stored procedure
CALL update_salary(123, 10.0); -- Give 10% raise
-- Create function that returns a value
CREATE OR REPLACE FUNCTION calculate_bonus(
p_salary DECIMAL,
p_performance_rating INT
)
RETURNS DECIMAL
LANGUAGE plpgsql
AS $$
DECLARE
v_bonus DECIMAL;
BEGIN
CASE p_performance_rating
WHEN 1 THEN v_bonus := p_salary * 0.20; -- 20%
WHEN 2 THEN v_bonus := p_salary * 0.15; -- 15%
WHEN 3 THEN v_bonus := p_salary * 0.10; -- 10%
ELSE v_bonus := p_salary * 0.05; -- 5%
END CASE;
RETURN v_bonus;
END;
$$;
-- Use function in query
SELECT
name,
salary,
calculate_bonus(salary, performance_rating) as bonus
FROM employees;
Triggers are database objects that automatically execute in response to specific events (INSERT, UPDATE, DELETE) on tables.
-- PostgreSQL trigger example
CREATE OR REPLACE FUNCTION update_modified_column()
RETURNS TRIGGER AS $$
BEGIN
NEW.modified_at = CURRENT_TIMESTAMP;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER update_employees_modified
BEFORE UPDATE ON employees
FOR EACH ROW
EXECUTE FUNCTION update_modified_column();
-- Audit trail trigger
DELIMITER $$
CREATE TRIGGER audit_employee_changes
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
IF OLD.salary != NEW.salary THEN
INSERT INTO salary_audit (
employee_id,
old_salary,
new_salary,
changed_by,
change_date
) VALUES (
NEW.employee_id,
OLD.salary,
NEW.salary,
USER(),
NOW()
);
END IF;
END$$
DELIMITER ;
-- Instead of trigger
CREATE TRIGGER trg_instead_of_delete
ON employees
INSTEAD OF DELETE
AS
BEGIN
-- Archive instead of delete
INSERT INTO employees_archive
SELECT *, GETDATE(), SYSTEM_USER
FROM deleted;
-- Then delete from main table
DELETE e
FROM employees e
INNER JOIN deleted d ON e.employee_id = d.employee_id;
END;
CTEs and temporary tables both help organize complex queries but differ in scope, performance, and use cases.
-- Simple CTE
WITH high_earners AS (
SELECT employee_id, name, salary
FROM employees
WHERE salary > 100000
),
department_stats AS (
SELECT
department,
COUNT(*) as emp_count,
AVG(salary) as avg_salary
FROM employees
GROUP BY department
)
SELECT
h.name,
h.salary,
d.department,
d.avg_salary
FROM high_earners h
JOIN employees e ON h.employee_id = e.employee_id
JOIN department_stats d ON e.department = d.department;
-- Create temporary table
CREATE TEMPORARY TABLE temp_high_earners AS
SELECT employee_id, name, salary
FROM employees
WHERE salary > 100000;
-- Add index for performance
CREATE INDEX idx_temp_earners ON temp_high_earners(employee_id);
-- Use in multiple queries
SELECT * FROM temp_high_earners;
-- Complex operation on temp table
UPDATE temp_high_earners
SET salary = salary * 1.1
WHERE employee_id IN (
SELECT employee_id FROM employees WHERE performance_rating = 'A'
);
-- Clean up (optional, auto-dropped at session end)
DROP TABLE temp_high_earners;
CHECK OPTION ensures that data inserted or updated through a view satisfies the view's defining condition, maintaining data integrity.
-- Create view for active employees
CREATE VIEW active_employees AS
SELECT employee_id, name, department, salary, hire_date
FROM employees
WHERE status = 'active'
WITH CHECK OPTION;
-- This will succeed (status = 'active')
INSERT INTO active_employees (name, department, salary, status)
VALUES ('John Doe', 'Engineering', 80000, 'active');
-- This will FAIL due to CHECK OPTION
INSERT INTO active_employees (name, department, salary, status)
VALUES ('Jane Smith', 'Sales', 70000, 'inactive');
-- Error: new row violates check option for view "active_employees"
-- Update that maintains view condition (succeeds)
UPDATE active_employees
SET salary = 85000
WHERE employee_id = 123;
-- Update that violates view condition (fails)
UPDATE active_employees
SET status = 'inactive'
WHERE employee_id = 123;
-- Error: new row violates check option
-- Base view
CREATE VIEW ny_employees AS
SELECT * FROM employees WHERE location = 'NY'
WITH CHECK OPTION;
-- View based on another view
CREATE VIEW ny_engineers AS
SELECT * FROM ny_employees WHERE department = 'Engineering';
-- WITH CASCADED CHECK OPTION (default)
CREATE VIEW ny_engineers_cascaded AS
SELECT * FROM ny_employees WHERE department = 'Engineering'
WITH CASCADED CHECK OPTION;
-- Insert must satisfy ALL underlying view conditions
INSERT INTO ny_engineers_cascaded (location, department)
VALUES ('NY', 'Engineering'); -- Success
INSERT INTO ny_engineers_cascaded (location, department)
VALUES ('CA', 'Engineering'); -- Fails (location ≠ 'NY')
INSERT INTO ny_engineers_cascaded (location, department)
VALUES ('NY', 'Sales'); -- Fails (department ≠ 'Engineering')
-- WITH LOCAL CHECK OPTION
CREATE VIEW ny_engineers_local AS
SELECT * FROM ny_employees WHERE department = 'Engineering'
WITH LOCAL CHECK OPTION;
-- Insert only needs to satisfy THIS view's condition
INSERT INTO ny_engineers_local (location, department)
VALUES ('CA', 'Engineering'); -- May succeed (depends on DB)
Sharding horizontally partitions data across multiple databases/servers to distribute load and enable scalability beyond single server limits.
-- Shard by customer_id ranges
-- Shard 1: customer_id 1-1000000
-- Shard 2: customer_id 1000001-2000000
-- Shard 3: customer_id 2000001-3000000
-- Application determines shard based on range
function get_shard_for_customer(customer_id) {
if (customer_id <= 1000000) return 'shard1';
if (customer_id <= 2000000) return 'shard2';
return 'shard3';
}
-- Query specific shard
SELECT * FROM shard1.customers WHERE customer_id = 500000;
SELECT * FROM shard2.orders WHERE customer_id = 1500000;
-- Shard by hash of customer_id
-- Number of shards: 4
-- Shard = customer_id % 4
-- Consistent hashing algorithm
function get_shard_for_customer(customer_id) {
const shard_count = 4;
const hash = md5(customer_id.toString());
const shard_index = parseInt(hash.substring(0, 8), 16) % shard_count;
return `shard${shard_index + 1}`;
}
-- Hash sharding distributes evenly
-- customer_id 123 → shard3 (123 % 4 = 3)
-- customer_id 456 → shard1 (456 % 4 = 0)
-- customer_id 789 → shard2 (789 % 4 = 1)
-- Maintain shard mapping in lookup table
CREATE TABLE shard_mapping (
customer_id INT PRIMARY KEY,
shard_id INT,
shard_host VARCHAR(100),
shard_database VARCHAR(50)
);
-- Look up shard before querying
SELECT shard_host, shard_database
FROM shard_mapping
WHERE customer_id = 12345;
-- Result: 'db-server-3', 'customers_db_3'
-- Application connects to correct shard
-- Connection string: jdbc:mysql://db-server-3/customers_db_3
Cursors allow row-by-row processing of result sets, but they are generally inefficient and should be avoided in favor of set-based operations.
DECLARE @employee_id INT;
DECLARE @employee_name VARCHAR(100);
DECLARE @salary DECIMAL(10,2);
-- Declare cursor
DECLARE employee_cursor CURSOR FOR
SELECT employee_id, name, salary
FROM employees
WHERE department = 'Sales';
-- Open cursor
OPEN employee_cursor;
-- Fetch first row
FETCH NEXT FROM employee_cursor INTO @employee_id, @employee_name, @salary;
-- Process rows
WHILE @@FETCH_STATUS = 0
BEGIN
-- Process each row
IF @salary < 50000
BEGIN
UPDATE employees
SET salary = salary * 1.10
WHERE employee_id = @employee_id;
PRINT 'Updated salary for ' + @employee_name;
END
-- Fetch next row
FETCH NEXT FROM employee_cursor INTO @employee_id, @employee_name, @salary;
END
-- Clean up
CLOSE employee_cursor;
DEALLOCATE employee_cursor;
-- Same operation, set-based (much faster) UPDATE employees SET salary = salary * 1.10 WHERE department = 'Sales' AND salary < 50000; -- Print affected rows PRINT 'Updated ' + CAST(@@ROWCOUNT AS VARCHAR) + ' employees';
Database snapshots provide read-only, static views of a database at a point in time, using copy-on-write mechanism for storage efficiency.
-- Create database snapshot
CREATE DATABASE SalesDB_Snapshot_1200
ON (
NAME = SalesDB_Data,
FILENAME = 'D:\Snapshots\SalesDB_1200.ss'
)
AS SNAPSHOT OF SalesDB;
-- Multiple snapshots possible
CREATE DATABASE SalesDB_Snapshot_1400
ON (
NAME = SalesDB_Data,
FILENAME = 'D:\Snapshots\SalesDB_1400.ss'
)
AS SNAPSHOT OF SalesDB;
-- Query snapshot like regular database
SELECT * FROM SalesDB_Snapshot_1200.dbo.orders;
-- Restore database from snapshot
USE master;
RESTORE DATABASE SalesDB
FROM DATABASE_SNAPSHOT = 'SalesDB_Snapshot_1200';
-- Drop snapshot
DROP DATABASE SalesDB_Snapshot_1200;
-- Before modification: -- Source page: [A B C D] -- Snapshot: [empty] -- After first modification (change C to X): -- Source saves original page to snapshot -- Source page: [A B X D] (changed) -- Snapshot: [A B C D] (original saved) -- Subsequent reads from snapshot: -- If reading unchanged data (A,B,D): read from source -- If reading changed data (C): read from snapshot -- Storage efficiency: Only changed pages stored -- If 1% of pages change, snapshot uses 1% of source size
Materialized view logs track changes to base tables, enabling fast incremental refreshes instead of complete rebuilds.
-- Create materialized view log on base table
CREATE MATERIALIZED VIEW LOG ON employees
WITH ROWID, SEQUENCE
(employee_id, department_id, salary, hire_date)
INCLUDING NEW VALUES;
-- Create fast refreshable materialized view
CREATE MATERIALIZED VIEW mv_employee_summary
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS
SELECT
department_id,
COUNT(*) as emp_count,
AVG(salary) as avg_salary,
SUM(salary) as total_salary
FROM employees
GROUP BY department_id;
-- Manual refresh
BEGIN
DBMS_MVIEW.REFRESH('mv_employee_summary', 'F');
-- 'F' = Fast refresh (incremental)
-- 'C' = Complete refresh (rebuild)
-- '?' = Force refresh (try fast, fallback to complete)
END;
-- Check refresh status
SELECT mview_name, last_refresh_type, staleness
FROM user_mviews;
-- 1. Materialized view log captures changes -- When base table changes: -- INSERT: Logs new row with ROWID and values -- UPDATE: Logs old and new values -- DELETE: Logs deleted row ROWID -- 2. Refresh uses log to update materialized view -- Instead of: SELECT * FROM base_table (full scan) -- It does: Apply logged changes to materialized view -- Example: Add 3 rows, update 2 rows, delete 1 row -- Complete refresh: Processes all 1,000,000 rows -- Fast refresh: Processes only 6 changed rows -- 3. Log cleaned after refresh DELETE FROM mlog$_employees WHERE ...;