30 SQL Tips & Tricks: Database Performance Tuning & Schema Optimization

A definitive, production-grade guide to optimizing SQL database performance, query writing, schema definition (DDL), and index lifecycle management based on 18 years of real-world database engineering experience.

The Golden Rule of SQL Performance Tuning

Before implementing any optimization technique, engineers must understand how the SQL Query Optimizer evaluates operations across different data volumes.

Key Principles of Performance Engineering

1. Data Volume Context Matters: Micro-optimizations on small-to-medium datasets (hundreds of thousands of records) rarely yield noticeable performance improvements because the dataset easily fits within memory buffers. However, on enterprise-scale datasets (millions or hundreds of millions of records), non-optimal query structures degrade execution speeds from milliseconds to hours.
2. Execution Plan as the Absolute Truth: Never assume a rewrite is faster based on syntax alone. Always evaluate the Execution Plan (comparing I/O cost, CPU cost, operator types, and scan vs. seek operations).
3. Readability vs. Optimization Trade-off: If two query variations yield identical execution plans, prioritize the query that is cleaner, modular, and easiest for future developers to maintain. Only introduce structural complexity if proven necessary by performance metrics.

1. Core Selection & Retrieval Best Practices

Tip 1: Select Only Required Columns (Avoid SELECT *)

Using SELECT * forces the database engine to retrieve every column defined on the target table.

-- BAD: Reads unnecessary columns, increases memory, prevents index-only scans
SELECT * 
FROM sales_orders;

-- GOOD: Projects only the required attributes, reducing network and memory overhead
SELECT order_id, customer_id, order_status, order_date 
FROM sales_orders;

• Engine Impact: Explicitly projecting columns allows the query optimizer to leverage covering non-clustered indexes, avoiding expensive key/bookmark lookups back to the clustered index or heap.

Tip 2: Eliminate Unnecessary DISTINCT and ORDER BY Operators

DISTINCT (duplicate removal) and ORDER BY (sorting) are resource-intensive blocking operations within an execution plan. They require buffering rows in memory or dumping them to temp storage (tempdb) to sort or hash match.

-- BAD: Adding DISTINCT and ORDER BY out of habit when source data is unique
SELECT DISTINCT customer_id, email 
FROM customers 
ORDER BY customer_id;

-- GOOD: Omit sort and duplicate removal when uniqueness and ordering are not strictly required
SELECT customer_id, email 
FROM customers;

Tip 3: Limit Rows During Exploratory Queries

When inspecting unfamiliar, high-volume production tables, never issue unbounded queries. Restrict row output to avoid triggering full table scans across millions of rows.

-- BAD: Forces the engine to scan and return 100 million records
SELECT order_id, order_date 
FROM sales_orders;

-- GOOD: Fetches only a lightweight sample for inspection
SELECT TOP 10 order_id, order_date 
FROM sales_orders;

2. Where Clause Optimization & SARGability

SARGability (Search Argumentable) refers to writing query predicates in a format that enables the SQL engine to utilize available indexes via an Index Seek rather than defaulting to an Index Scan.

Tip 4: Create Non-Clustered Indexes on Frequent Filter Predicates

Columns heavily referenced in WHERE clauses should be backed by non-clustered indexes to enable fast index lookup trees.

CREATE NONCLUSTERED INDEX IX_sales_orders_order_status 
ON sales_orders(order_status);

Tip 5: Avoid Applying Functions directly to Indexed Columns

Applying scalar functions (e.g., LOWER(), SUBSTRING(), YEAR()) to a column in a predicate neutralizes index usage. The engine must execute the function row-by-row across the entire table, invalidating the pre-sorted index structure.

Example A: Case Conversion Functions

-- BAD: LOWER() invalidates index on order_status; forces full scan
SELECT order_id, order_status 
FROM sales_orders 
WHERE LOWER(order_status) = 'delivered';

-- GOOD: Pass exact data value without column-side transformation
SELECT order_id, order_status 
FROM sales_orders 
WHERE order_status = 'Delivered';

Example B: String Extraction Functions

-- BAD: SUBSTRING() prevents Index Seek on first_name
SELECT customer_id, first_name 
FROM customers 
WHERE SUBSTRING(first_name, 1, 1) = 'A';

-- GOOD: SARGable wildcards allow Index Seek
SELECT customer_id, first_name 
FROM customers 
WHERE first_name LIKE 'A%';

Example C: Date Transformation Functions

-- BAD: YEAR() function prevents index seek on order_date
SELECT order_id, order_date 
FROM sales_orders 
WHERE YEAR(order_date) = 2025;

-- GOOD: Range predicate using BETWEEN allows full Index Seek utilization
SELECT order_id, order_date 
FROM sales_orders 
WHERE order_date BETWEEN '2025-01-01 00:00:00' AND '2025-12-31 23:59:59';

Tip 6: Avoid Leading Wildcards in LIKE Patterns

Wildcards at the start of a search pattern (LIKE '%val') prevent the optimizer from navigating the index B-tree structure. Trailing wildcards (LIKE 'val%') preserve index seek capabilities.

-- BAD: Leading wildcard forces complete index/table scan
SELECT customer_id, last_name 
FROM customers 
WHERE last_name LIKE '%Gold';

-- GOOD: Trailing wildcard allows index seek navigation
SELECT customer_id, last_name 
FROM customers 
WHERE last_name LIKE 'Gold%';

Tip 7: Prefer IN Over Multiple OR Predicates

Using multiple OR conditions on the same column creates complex filter evaluations that can confuse the optimizer. The IN construct allows the engine to construct a clean multi-seeking predicate plan.

-- BAD: Multiple OR conditions lead to inefficient query trees
SELECT order_id, customer_id 
FROM sales_orders 
WHERE customer_id = 1 OR customer_id = 2 OR customer_id = 3;

-- GOOD: IN operator optimizes predicate evaluation
SELECT order_id, customer_id 
FROM sales_orders 
WHERE customer_id IN (1, 2, 3);

Predicate Optimization Summary

3. Optimizing Join Operations & Join Types

Tip 8: Understand Join Performance Hierarchies

Join types operate with different execution costs based on matching requirements:

• Guideline: Use INNER JOIN whenever non-matching records are not required by business logic.

Tip 9: Use Explicit ANSI Join Syntax

Avoid legacy non-ANSI implicit joins where tables are listed in the FROM clause and joined via the WHERE clause.

-- BAD: Legacy Implicit Join (Hard to read, error-prone, hard to optimize)
SELECT c.first_name, o.order_id 
FROM customers c, sales_orders o 
WHERE c.customer_id = o.customer_id;

-- GOOD: Modern Explicit ANSI Join Standard
SELECT c.first_name, o.order_id 
FROM customers c
INNER JOIN sales_orders o ON c.customer_id = o.customer_id;

Tip 10: Index Columns Referenced in ON Clauses

Failure to index foreign key joining keys forces full table scans on high-cardinality detail tables during join evaluation.

-- Create non-clustered index on child table foreign key
CREATE NONCLUSTERED INDEX IX_sales_orders_customer_id 
ON sales_orders(customer_id);

4. Advanced Join Techniques & High-Volume Dataset Processing

Tip 11: Filter Data Prior to Joining Large Tables

On massive tables, filtering data before passing it into a join operation reduces the memory footprint and intermediate row counts passed between execution operators.

-- APPROACH A (Standard - Ideal for Small/Medium Tables):
SELECT c.first_name, o.order_id, o.order_status
FROM customers c
INNER JOIN sales_orders o ON c.customer_id = o.customer_id
WHERE o.order_status = 'Delivered';

-- APPROACH B (Pre-Filtering via Subquery - Superior for Massive Tables):
SELECT c.first_name, o.order_id, o.order_status
FROM customers c
INNER JOIN (
    SELECT order_id, customer_id, order_status
    FROM sales_orders
    WHERE order_status = 'Delivered'
) o ON c.customer_id = o.customer_id;

Tip 12: Aggregate Data Before Joining

When joining a detail table to a primary table purely to compute aggregations, group the detail table in isolation (via subquery or Common Table Expression - CTE) before performing the join.

-- BAD: Correlated subquery executes row-by-row (RBAR - Row-By-Agonizing-Row)
SELECT c.customer_id, c.first_name,
       (SELECT COUNT(*) FROM sales_orders o WHERE o.customer_id = c.customer_id) AS order_count
FROM customers c;

-- BETTER FOR LARGE TABLES: Pre-aggregate in CTE/Subquery, then Join
WITH AggregatedOrders AS (
    SELECT customer_id, COUNT(*) AS order_count
    FROM sales_orders
    GROUP BY customer_id
)
SELECT c.customer_id, c.first_name, COALESCE(ao.order_count, 0) AS order_count
FROM customers c
LEFT JOIN AggregatedOrders ao ON c.customer_id = ao.customer_id;

Tip 13: Replace OR Conditions in Join Clauses with UNION

Using an OR condition inside a join ON clause prevents efficient index usage, often resulting in expensive nested loop or Cartesian scans.

-- BAD: OR operator in ON clause causes severe performance degradation
SELECT c.first_name, o.order_id
FROM sales_orders o
INNER JOIN customers c 
   ON o.customer_id = c.customer_id 
   OR o.salesperson_id = c.customer_id;

-- GOOD: Deconstruct into two independent queries merged via UNION
SELECT c.first_name, o.order_id
FROM sales_orders o
INNER JOIN customers c ON o.customer_id = c.customer_id
UNION
SELECT c.first_name, o.order_id
FROM sales_orders o
INNER JOIN customers c ON o.salesperson_id = c.customer_id;

Tip 14: Force Join Hints for High-Volume Asymmetric Joins

When joining a massive fact table with a smaller lookup table, the optimizer may default to a Nested Loop Join if statistics are skewed. In performance-critical cases on large datasets, a HASH JOIN hint can be evaluated.

-- Explicitly forcing Hash Join for large dataset joining operations
SELECT c.first_name, o.order_id
FROM sales_orders o
INNER JOIN customers c ON o.customer_id = c.customer_id
OPTION (HASH JOIN);

Note: Use Join Hints with caution. Hints override the dynamic behavior of the SQL engine query optimizer and should only be applied after thorough execution plan analysis.

5. Optimizing Set Operations: UNION vs. UNION ALL

Tip 15: Use UNION ALL when Duplicates Are Acceptable or Impossible

UNION implicitly performs a distinct sort operation across the entire merged result set to eliminate identical rows. If dataset uniqueness is guaranteed or duplicate retention is acceptable, use UNION ALL.

-- BAD: Forces overhead of deduplication sort when unnecessary
SELECT customer_id, city FROM US_Customers
UNION
SELECT customer_id, city FROM EU_Customers;

-- GOOD: Appends datasets directly without sort penalty
SELECT customer_id, city FROM US_Customers
UNION ALL
SELECT customer_id, city FROM EU_Customers;

Tip 16: Combine UNION ALL with Explicit DISTINCT for Massive Data Sets

When deduplication is required across multi-million row datasets, executing an explicit DISTINCT query wrapped around a UNION ALL subquery can provide better execution plan parallelization than standard UNION.

-- Optimized deduplication structure for high-volume datasets
SELECT DISTINCT customer_id, city
FROM (
    SELECT customer_id, city FROM US_Customers
    UNION ALL
    SELECT customer_id, city FROM EU_Customers
) AS CombinedCustomers;

6. Aggregations & Analytical Workloads

Tip 17: Deploy Clustered Columnstore Indexes for Aggregations

Standard B-tree indexes store data in a row-wise fashion. For large analytical queries (e.g., aggregate sums, averages across millions of records), Columnstore Indexes compress data by columns, dramatically decreasing I/O reads.

-- Convert huge analytical/fact tables to Clustered Columnstore Index
CREATE CLUSTERED COLUMNSTORE INDEX CCI_sales_orders 
ON sales_orders;

Tip 18: Materialize Pre-Aggregated Reports for Slow Queries

If an analytical reporting query takes minutes to compute due to complex logic, materialize the output into an aggregated reporting summary table.

-- Pre-aggregate complex analytical query into persistent table
SELECT customer_id, 
       COUNT(order_id) AS total_orders, 
       SUM(order_amount) AS total_spent
INTO sales_summary_reporting
FROM sales_orders
GROUP BY customer_id;

-- Fast reporting execution: Reads lightweight summary table directly
SELECT customer_id, total_orders, total_spent 
FROM sales_summary_reporting;

7. Subquery Optimization & Redundancy Reduction

Tip 19: Prefer EXISTS Over IN for Subquery Filtering

When checking existence across related tables, avoid using WHERE col IN (SELECT ...). The IN subquery structure can build complete intermediate result lists, whereas EXISTS halts execution as soon as a single matching record is found (Short-Circuit evaluation).

-- BAD: IN builds intermediate value set from subquery
SELECT order_id, order_date 
FROM sales_orders 
WHERE customer_id IN (
    SELECT customer_id FROM customers WHERE country = 'USA'
);

-- GOOD: EXISTS stops evaluating upon finding first match
SELECT o.order_id, o.order_date 
FROM sales_orders o
WHERE EXISTS (
    SELECT 1 FROM customers c 
    WHERE c.customer_id = o.customer_id AND c.country = 'USA'
);

Tip 20: Eliminate Redundant Logic using Window Functions

Repeated table scans to compute set-based metrics (e.g., comparing individual rows against table averages) create redundant resource usage. Utilize Window Functions (OVER()) to calculate metrics in a single pass over the table.

-- BAD: Scans table multiple times and repeats duplicate aggregation subqueries
SELECT employee_id, salary, 'Above Average' AS category
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees)
UNION ALL
SELECT employee_id, salary, 'Below Average' AS category
FROM employees
WHERE salary <= (SELECT AVG(salary) FROM employees);

-- GOOD: Single table scan using Window Functions and CASE logic
WITH SalaryMetrics AS (
    SELECT employee_id, salary,
           AVG(salary) OVER() AS avg_salary
    FROM employees
)
SELECT employee_id, salary,
       CASE 
           WHEN salary > avg_salary THEN 'Above Average'
           ELSE 'Below Average'
       END AS category
FROM SalaryMetrics;

8. Schema Design & Data Definition Language (DDL) Optimization

Suboptimal schema definitions enforce permanent query overhead. Schema optimizations yield significant performance improvements downstream.

Tip 21: Avoid Using VARCHAR or TEXT for Non-Textual Data

Never use generic string data types to hold numeric, date, or status values. Strings consume wider byte storage, break collation semantics, and slow down sorting algorithms.

-- Suboptimal Schema Definition
CREATE TABLE customer_info_bad (
    customer_id INT,
    score VARCHAR(50),       -- Should be INT
    birth_date VARCHAR(50)   -- Should be DATE
);

-- Optimized Schema Definition
CREATE TABLE customer_info_good (
    customer_id INT,
    score INT,
    birth_date DATE
);

Tip 22: Avoid Excessive Length Definitions (e.g., VARCHAR(MAX))

Defining overly broad data columns (such as VARCHAR(255) or VARCHAR(MAX) for attributes like names or codes) causes memory managers to over-allocate query memory grants during sorting/hashing operations.

-- Over-allocated column sizes inflate memory allocation requests
first_name VARCHAR(MAX),
country VARCHAR(255)

-- Right-sized data types optimize memory grant sizing
first_name VARCHAR(50),
country VARCHAR(50)

Tip 23: Enforce NOT NULL Constraints Wherever Applicable

Allowing NULL on columns forces the database engine to maintain extra null-bitmap metadata and complicates index navigation paths.

CREATE TABLE customer_info (
    customer_id INT NOT NULL,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    country VARCHAR(50) NOT NULL
);

Tip 24: Ensure Every Table Has a Clustered Primary Key

Tables constructed without a clustered index are termed Heaps. Heaps suffer from non-sequential storage fragmentation, lack B-Tree lookup capabilities, and produce inefficient row positioning pointers for non-clustered indexes.

ALTER TABLE customer_info 
ADD CONSTRAINT PK_customer_info PRIMARY KEY CLUSTERED (customer_id);

Tip 25: Index Operational Foreign Keys

While primary keys automatically construct clustered indexes in most database engines, Foreign Keys do NOT automatically generate indexes. Create non-clustered indexes on all foreign key columns involved in operational joins.

CREATE NONCLUSTERED INDEX IX_customer_info_employee_id 
ON customer_info(employee_id);

DDL Optimization Synthesis

-- BEFORE: Suboptimal DDL Standard
CREATE TABLE customer_info (
    customer_id INT,
    first_name VARCHAR(MAX),
    last_name TEXT,
    country VARCHAR(255),
    score VARCHAR(20),
    birth_date VARCHAR(50),
    employee_id INT
);

-- AFTER: Fully Optimized Production DDL Standard
CREATE TABLE customer_info (
    customer_id INT NOT NULL,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    country VARCHAR(50) NOT NULL,
    score INT NULL,
    birth_date DATE NULL,
    employee_id INT NULL,
    CONSTRAINT PK_customer_info PRIMARY KEY CLUSTERED (customer_id)
);

-- Secondary non-clustered indexing for join pathways
CREATE NONCLUSTERED INDEX IX_customer_info_employee_id 
ON customer_info(employee_id);

9. Advanced Index Lifecycle & Maintenance Management

Tip 26: Prevent Over-Indexing

While indexes accelerate read operations (SELECT), every added index slows down modification statements (INSERT, UPDATE, DELETE). The engine must synchronously update every underlying non-clustered index whenever underlying data changes.

Tip 27: Monitor Usage & Remove Unused Indexes

In enterprise environments, up to 90% of created indexes go unused over time. Periodically query system dynamic management views (DMVs) to locate indexes with zero seek/scan counts and drop them to free storage and speed up writes.

Tip 28: Implement Regular Statistics Maintenance Jobs

The query optimizer relies on histogram statistics to estimate row yields and construct execution plans. Outdated statistics produce inaccurate row estimates, leading to poor execution plan selections (e.g., selecting Nested Loops instead of Hash Joins). Ensure statistics updates run on a automated schedule.

-- Example T-SQL command to update statistics on a target table
UPDATE STATISTICS sales_orders;

Tip 29: Perform Automated Index Rebuilding and Reorganization

Frequent data modifications produce physical fragmentation, where logical B-Tree order no longer matches physical storage layout.

• Index Reorganization: Lightweight inline operation for low fragmentation levels (e.g., 5% - 30%).
• Index Rebuild: Complete rebuild of index structures for high fragmentation levels (> 30%).

Tip 30: Implement Table Partitioning for Massive Scale

For massive multi-million/billion-row datasets (such as enterprise transaction/fact tables), partition tables horizontally across boundary ranges (such as dates). Combining horizontal partitioning with Columnstore Indexes provides optimal analytical query performance.

Summary and Key Takeaways

1. Test via Execution Plans: Never rely on syntax assumptions. Use execution plan metrics (I/O costs, operator profiles) to validate optimizations.
2. Select Specifically: Eliminate SELECT * in production queries to minimize memory consumption and leverage covering index scans.
3. Maintain SARGability: Avoid applying scalar functions (LOWER(), YEAR()) or leading wildcards (LIKE '%val') to columns in WHERE clauses to keep B-Tree index seeks enabled.
4. Prioritize INNER JOIN & ANSI Syntax: Leverage explicit INNER JOIN operations over legacy implicit comma joins, ensuring join keys are backed by non-clustered indexes.
5. Optimize Data Pipelines: Filter and aggregate high-volume datasets prior to performing joins using isolated Common Table Expressions (CTEs) or subqueries.
6. Eliminate Redundant Set Operations: Default to UNION ALL over UNION whenever duplicate removal is unnecessary, avoiding costly sort operators.
7. Use EXISTS over IN for Lookups: Replace intermediate value list checks (WHERE col IN (...)) with short-circuiting correlated subquery evaluations (WHERE EXISTS (...)).
8. Enforce Strict Schema Rules (DDL): Avoid VARCHAR(MAX) or using string types for numeric/date values; declare explicit length bounds, enforce NOT NULL constraints, and define clustered primary keys.
9. Deploy Columnstore Indexes for Analytics: Transition high-volume fact/analytical tables to Clustered Columnstore Indexes to optimize large-scale aggregations.
10. Maintain the Index Lifecycle: Balance read/write requirements by purging unused indexes, updating table statistics routinely, and executing index rebuilds to counter storage fragmentation.