30 SQL Tips and Tricks From 15 Years of Experience
Best Practices to Improve the Performance of Your Queries
Hey friends! 👋
Over the past 15 years of working on SQL projects, I’ve learned a ton about optimizing performance, and today I’m excited to share 30 of my best tips and tricks with you.
To make it even easier, I’ve put together a handy Cheat Sheet for quick reference! And if you want all the details, I’ve just published a video where I break everything down step by step. Check it out!
FETCHING DATA
#1 Tip | Only select the columns you need. Avoid using SELECT *
#2 Tip | Avoid using DISTINCT or ORDER BY unless absolutely necessary, as they can slow down queries.
#3 Tip | For exploration, limit the number of rows to avoid fetching unnecessary data.
FILTERING DATA
#4 Tip | Create non-clustered indexes on columns frequently used in the WHERE clause to speed up queries.
#5 Tip | Avoid applying functions (e.g., UPPER(), DATE()) to columns in the WHERE clause, as this prevents indexes from being used.
#6 Tip | Avoid starting string searches with a wildcard (%example), as this disables index usage.
#7 Tip | Use IN instead of multiple OR conditions for better readability and performance.
JOINING DATA
#8 Tip | Understand the performance implications of different join types. Use INNER JOIN when possible for efficiency.
#9 Tip | Always use explicit (ANSI-style) joins (INNER JOIN, LEFT JOIN, etc.) instead of older implicit join syntax.
#10 Tip | Ensure that the columns in the ON clause of your joins are indexed for optimal performance.
#11 Tip | Filter before joining large tables to reduce the size of the dataset being joined.
#12 Tip | Aggregate before joining large tables to reduce the size of the dataset being joined.
#13 Tip | Replace OR conditions in join logic with UNION where possible to improve query performance.
#14 Tip | Be aware of nested loops in your query execution plan. Use SQL Hints if needed to optimize performance.
#15 Tip | Use UNION ALL instead of UNION if duplicates are acceptable, as it is faster.
#16 Tip | When duplicates are not acceptable, use UNION ALL + DISTINCT instead of UNION for better performance.
AGGREGATING DATA
#17 Tip | Use columnstore indexes for queries involving heavy aggregations on large tables.
#18 Tip | Pre-aggregate data and store the results in a separate table for faster reporting.
SUBQUERIES
#19 Tip | Understand when to use JOIN, EXISTS, or IN. Avoid IN with large lists as it can be inefficient.
#20 Tip | Simplify your queries by eliminating redundant logic and conditions by using CTE
DDL (Data Definition Language)
#21 Tip | Avoid VARCHAR or TEXT types unnecessarily; choose more precise data types to save storage and improve performance.
#22 Tip | Avoid defining excessive lengths in your data types (e.g., VARCHAR(MAX)) unless truly needed.
#23 Tip | Use NOT NULL constraints wherever possible to enforce data integrity.
#24 Tip | Ensure all tables have a clustered primary key to provide structure and improve query performance.
#25 Tip | Add non-clustered indexes to foreign keys that are frequently queried to speed up lookups.
INDEXING
#26 Tip | Avoid Over Indexing, as it can slow down insert, update, and delete operations.
#27 Tip | Regularly review and drop unused indexes to save space and improve write performance.
#28 Tip | Update table statistics weekly to ensure the query optimizer has the most up-to-date information.
#29 Tip | Reorganize and rebuild fragmented indexes weekly to maintain query performance.
#30 Tip | For large tables (e.g., fact tables), partition the data and then apply a columnstore index for best performance results.
I hope you found this helpful, and if you have any questions or want to share your own tips, I’m all ears! Happy querying!
Baraa
Hey friends —
I’m Baraa. I’m an IT professional and YouTuber.
My mission is to share the knowledge I’ve gained over the years and to make working with data easier, fun, and accessible to everyone through courses that are free, simple, and easy!