1. Select Only the Columns You Need
Slow
SELECT * FROM Person
Fast
SELECT email, name FROM Person
2. Use TOP With ORDER BY for Pagination
- Good for first page and don't need succeeding next page
Examples: 'Show latest 10 notifications'
SELECT TOP 10 * FROM Notifications ORDER BY CreatedAt DESC;
3. Use Proper Indexes (Clustered + Non-Clustered)
Index columns used in
WHEREJOINORDER BY
CREATE INDEX IX_Users_Name
ON Users(Name);
4. Avoid Functions on Indexed Columns or Use SARGable Conditions
- SARG = Search ARGument
- Means SQL can use an index.
Slow
WHERE YEAR(CreatedDateAt) = 2024
Fast
WHERE CreatedDateAt >= '2024-01-01'
AND CreatedDateAt < '2025-01-01'
5. Use JOIN Correctly (Avoid CROSS JOIN)
Slow - CROSS JOIN
SELECT *
FROM Products
CROSS JOIN Colors;
or
SELECT *
FROM Products
JOIN Colors;
- Missing ON clause
Explanation
What it does Takes every row from table A and pairs it with every row from table B.
If:
- Table A = 10 rows
- Table B = 20 rows
Then:
- CROSS JOIN = 200 rows
Fast - Use Inner Join, Left, Right Join
SELECT *
FROM Products p
JOIN Colors c ON c.ColorId = p.ColorId;
- These join types are optimized and index-friendly
6. Use EXISTS instead of IN (for large sets)
SELECT *
FROM User u
WHERE EXISTS (
SELECT 1 FROM Order o
WHERE o.UserId = u.Id
)
7. Avoid SELECT DISTINCT unless necessary, use EXISTS instead
Slow
SELECT DISTINCT email
FROM User U
JOIN Order o
ON o.CustomerId = u.Id
Fast
SELECT u.Id, u.Email from User u
WHERE EXISTS (
SELECT 1 FROM Order o
WHERE o.CustomerId = u.Id
)
8. COUNT (*) vs COUNT (column)
- COUNT () = counts *all rows, even if columns contain NULL**
SELECT COUNT(*) FROM UserOrder
- COUNT(column) counts only rows where that column is NOT NULL.
SELECT COUNT(orderId) from UserOrder
9. Use IN when comparing 2 value in same column instead of OR
Slow
WHERE Status == 'Active' OR Status == 'Pending'
Fast
WHERE Status IN ('Active', 'Pending')
10. Avoid Wildcards at the start of LIKE
Slow - Cannot use index
WHERE Name LIKE '%rose'
Fast - Can use index
WHERE Name LIKE 'rose%'