Mastering SQL: 12 Essential Tips to Optimize Performance and Secure Your Queries

Writing SQL queries might seem simple, but creating ones that are both efficient and easy to maintain takes practice. By following best practices, you can make sure your queries run smoothly and quickly. In this article, we’ll explore some key tips for writing better SQL, with examples to help you along the way.

1. Leverage Indexes Appropriately

Indexes can dramatically improve the performance of queries by allowing the database to quickly locate data. However, while indexes improve read performance, they can negatively impact write operations (like INSERT, UPDATE, or DELETE). Each time a write operation occurs, the database has to update the indexes as well. This can lead to slower performance for write-heavy workloads. Use them wisely, especially on columns frequently used in WHERE, JOIN, or ORDER BY clauses.

Example:

-- good code
CREATE INDEX idx_author ON books(author);
SELECT * FROM books WHERE author = 'J.K. Rowling';

-- over indexing 
CREATE INDEX idx_title ON books(title); 
CREATE INDEX idx_published_year ON books(published_year);

2. Limit the Use of Functions on Indexed Columns

Let’s say the created_at column in users table is indexed, and you want to find users who registered on a specific day. When you apply functions to columns that are indexed, it can prevent the database from using the index. This forces the database to perform a full table scan, slowing down performance.

Example:

-- Avoid applying a function on an indexed column:
SELECT * FROM users
WHERE DATE(created_at) = '2024-01-15';


-- Instead, rewrite the query:
SELECT * FROM users
WHERE created_at >= '2024-01-15 00:00:00' AND created_at < '2024-01-16 00:00:00';

3. Avoid Using SELECT *

Selecting all columns (SELECT *) can lead to retrieving more data than needed, which may slow down your query. It’s better to explicitly select the columns you need.

Example:

-- Inefficient query
SELECT * FROM orders;

-- Optimized query
SELECT order_id, customer_id, order_total FROM orders;

4. Avoid N+1 Query Problems

When querying a parent and child table, writing inefficient subqueries can result in multiple queries being run in a loop. Instead, use JOIN statements to get all the data in one query.

Example:

-- Inefficient N+1 query problem
SELECT customer_name FROM customers WHERE customer_id = 1;
SELECT * FROM orders WHERE customer_id = 1;

-- Optimized query
SELECT c.customer_name, o.order_id, o.order_total
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE c.customer_id = 1;

5. Use EXISTS Instead of COUNT for Better Performance

If you only need to check whether a record exists, using EXISTS can be more efficient than using COUNT(*) since EXISTS stops searching after finding the first match.

Example:

-- Inefficient query, checking if COUNT(*) > 0
SELECT COUNT(*) FROM orders WHERE customer_id = 123; 

-- Optimized query 
SELECT EXISTS (SELECT 1 FROM orders WHERE customer_id = 123);

6. Use Proper Data Types

Always define columns with the appropriate data types. This improves both performance and data integrity. For example, use INT for numeric fields instead of VARCHAR.

Example:

-- Inefficient
CREATE TABLE products (
 product_id VARCHAR(255),
 price VARCHAR(255)
);

-- Optimized
CREATE TABLE products (
 product_id INT,
 price DECIMAL(10, 2)
);

7. Handle NULLs Properly

When comparing data, remember that NULL behaves differently from other values. Use IS NULL or IS NOT NULL to handle NULL comparisons explicitly, instead of using = or !=.

Example:

-- Correct way to check for NULL values
SELECT * FROM customers WHERE last_name IS NULL;

-- Avoid this (Incorrect)
SELECT * FROM customers WHERE last_name = NULL; -- Will not return correct results

8. Implement Proper Error Handling

When writing SQL queries, especially in scripts or applications, ensure that you have proper error handling in place. This includes checking for exceptions and ensuring that your queries don’t leave the database in an inconsistent state. It allows you to commit all changes only if every operation is successful. If any operation fails, you can roll back all changes to maintain a consistent state.

Example:

BEGIN TRY
 BEGIN TRANSACTION;

 INSERT INTO orders (customer_id, order_total) VALUES (1, 100);

 -- This could cause an error if the customer already exists
 INSERT INTO orders (customer_id, order_total) VALUES (1, 200);

 COMMIT TRANSACTION;
END TRY
BEGIN CATCH
 -- Handle the error
 PRINT 'An error occurred: ' + ERROR_MESSAGE();
 ROLLBACK TRANSACTION;
END CATCH;

9. Avoid Repeated Subqueries with Common Table Expressions (CTEs)

If you need to use the same subquery multiple times within a query, consider using a Common Table Expression (CTE) to simplify the structure and improve performance. CTEs also make your SQL easier to read and maintain.

Example:

-- Before (Without CTE: Repeated Subquery)
SELECT employee_id,
    (SELECT SUM(salary) 
     FROM salaries 
     WHERE employee_id = employees.employee_id) AS total_salary
FROM employees
WHERE (SELECT SUM(salary) 
      FROM salaries 
      WHERE employee_id = employees.employee_id) > 50000;

-- After (With CTE: Avoiding Repetition)
WITH TotalSalaries AS (
 SELECT employee_id, SUM(salary) AS total_salary
 FROM salaries
 GROUP BY employee_id
)
SELECT employees.employee_id, TotalSalaries.total_salary
FROM employees
JOIN TotalSalaries
   ON employees.employee_id = TotalSalaries.employee_id
WHERE TotalSalaries.total_salary > 50000;

10. Use Parameterized Queries to Prevent SQL Injection

When constructing queries dynamically, always use parameterized queries to protect against SQL injection attacks. This is especially important in applications that accept user input. In the following example, by using sp_executesql, you ensure that user input is treated as data, not executable SQL code, significantly reducing the risk of SQL injection.

Example:

DECLARE @CustomerId INT = 1;
DECLARE @OrderTotal DECIMAL(10, 2) = 250;

EXEC sp_executesql N'INSERT INTO orders (customer_id, order_total) VALUES (@CustomerId, @OrderTotal)',
                N'@CustomerId INT, @OrderTotal DECIMAL(10, 2)',
                @CustomerId, @OrderTotal;

11. Optimize Queries with the SET STATISTICS Command

Use SQL Server’s SET STATISTICS commands to analyze query performance by obtaining detailed information about the execution of your queries. This helps you understand how SQL Server processes your query and can guide you in optimizing it.

Example:

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

SELECT customer_id, SUM(order_total)
FROM orders
GROUP BY customer_id;

SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

12. Enforce Data Integrity with Constraints

Using constraints not only enforces data integrity but also improves performance by eliminating the need for triggers or manual checks in queries. They help prevent invalid data from being inserted into the table, thereby protecting the quality of your data.

Example:

-- Table creation without constraints
CREATE TABLE customers (
 customer_id INT,
 customer_name VARCHAR(100),
 email VARCHAR(255),
 age INT
);

-- Inserting invalid data (negative age, duplicate customer_id)
INSERT INTO customers (customer_id, customer_name, email, age)
VALUES (1, 'John Doe', 'john@example.com', -25);  -- Invalid age

INSERT INTO customers (customer_id, customer_name, email, age)
VALUES (1, 'Jane Smith', 'jane@example.com', 30);  -- Duplicate customer_id

-- Table creation with constraints
CREATE TABLE customers (
 customer_id INT PRIMARY KEY,        -- Primary key constraint ensures unique IDs
 customer_name VARCHAR(100) NOT NULL,   -- NOT NULL constraint prevents empty names
 email VARCHAR(255) UNIQUE,          -- Unique constraint ensures no duplicate emails
 age INT CHECK (age >= 18)           -- Check constraint ensures valid age
);

-- Inserting valid data
INSERT INTO customers (customer_id, customer_name, email, age)
VALUES (1, 'John Doe', 'john@example.com', 25);  -- Valid data

-- Attempting to insert invalid data (this will fail)
INSERT INTO customers (customer_id, customer_name, email, age)
VALUES (1, 'Jane Smith', 'jane@example.com', 17);  -- Age below 18 violates CHECK constraint

Have fun with your SQL journey, and feel free to revisit this guide whenever you need a refresher. Happy querying!