SQL Execution Plan: The Secret to Fast Queries

Most SQL developers write queries top to bottom. SQL doesn't run them that way. This one gap causes more bugs, more confusion, and more slow queries than almost anything else. Here's the actual order SQL executes: • FROM — load the table first • JOIN — combine the tables • WHERE — filter the rows • GROUP BY — group what's left • HAVING — filter the groups • SELECT — NOW it picks your columns • ORDER BY — sort the final result SELECT runs sixth. Not first. This is why you can't use a column alias from your SELECT in your WHERE clause — WHERE runs before SELECT even decides what the columns are called. This is why filtering in WHERE is always faster than filtering in HAVING — WHERE cuts rows before grouping, HAVING cuts after. This is why SELECT * on a large table is expensive even if you only need 2 columns — FROM scans everything before SELECT can trim it. Three rules that will save you hours: → Filter as early as possible — always in WHERE, never in HAVING unless you need it → Never reference SELECT aliases in WHERE or GROUP BY → Subqueries in FROM run first — use them to pre-filter large tables before joining Every SQL bug I've ever fixed started with forgetting this. Save this. Share it with every SQL writer on your team. Did you know this already — or did this just explain a bug you've had? 👇 #SQL #DataEngineering #Azure #Databricks #DataEngineer

🚀 Boost SQL Query Performance with Partitioning When your tables grow into millions (or billions) of rows, query performance starts to suffer. One powerful technique to solve this is **Partitioning**. 🔹 SQL Server Example (Step-by-Step – Orders Table) -- 1. Create Partition Function (by year) CREATE PARTITION FUNCTION pf_orders (DATE) AS RANGE RIGHT FOR VALUES ('2024-01-01', '2025-01-01', '2026-01-01'); -- 2. Create Partition Scheme CREATE PARTITION SCHEME ps_orders AS PARTITION pf_orders ALL TO ([PRIMARY]); -- 3. Create Partitioned Table CREATE TABLE orders ( order_id INT IDENTITY(1,1), order_date DATE NOT NULL, amount DECIMAL(10,2) ) ON ps_orders(order_date); -- 4. Insert Data INSERT INTO orders (order_date, amount) VALUES ('2023-12-15', 400), ('2024-06-10', 500), ('2025-03-15', 800); -- 5. Query (Partition Elimination) SELECT * FROM orders WHERE order_date BETWEEN '2025-01-01' AND '2025-12-31'; ``` 🔹 Why it’s powerful: ✅ Faster queries (partition elimination) ✅ Only relevant data is scanned ✅ Better performance for large tables 🔹 Pro Tip 💡 Always filter using direct date ranges for best performance. Partition smart → Query fast → Scale efficiently 🚀 #SQLServer #SQL #DataEngineering #PerformanceTuning

𝐐𝐮𝐞𝐫𝐲 𝐄𝐱𝐞𝐜𝐮𝐭𝐢𝐨𝐧 𝐏𝐥𝐚𝐧𝐬 When a SQL query is slow, many people look only at the query text. But SQL Server does not execute SQL the way people read it. It executes a **plan**. That is why **Query Execution Plans** are one of the most important tools in SQL Server performance tuning. A simple way to think about it: The query is your request.   The execution plan is SQL Server’s strategy for answering that request. And that strategy decides: - how data is accessed - which indexes are used - how joins are performed - whether sorting is needed - how much data moves through the operators This is why two queries that look very similar can perform very differently. Because the real cost is often not in the SQL text itself.   It is in the plan shape chosen by the optimizer. A plan can reveal problems like: - index scans instead of efficient seeks - expensive key lookups - poor join choices - missing indexes - inaccurate row estimates - sorts and spills - unnecessary parallelism That is the real value of execution plans. They help answer not just: **What is slow?** But: **Why is SQL Server executing it this way?** Good tuning starts when you stop reading only the query   and start reading the path SQL Server took to execute it. Because a slow query is often not just bad syntax. It is an expensive plan. #SQLServer #ExecutionPlan #QueryPerformance #SQLInternals #DatabasePerformance #PerformanceTuning #DatabaseAdministration

🚀 Your SQL queries are SLOW — and you might not even know why. I've seen developers write perfect SQL logic… but still kill database performance. 💀 The problem isn't the query. It's the habits behind the query. Here are 6 SQL Query Optimization Techniques every data professional must know 👇 ⚡ Quick Summary: 1️⃣ Use Indexes Effectively → 90% Faster No index on WHERE column = full table scan every time. One line of index creation can change everything. 2️⃣ Avoid SELECT * → 50% Faster You don't need all 40 columns. Ask only what you need. Less I/O = faster results. 3️⃣ Use EXISTS instead of IN → 70% Faster IN evaluates every row. EXISTS stops the moment it finds a match. Smart difference. 🧠 4️⃣ Optimize JOINs with Indexed Columns → 80% Faster Joining on unindexed columns = disaster for large tables. Index your JOIN keys. Always. 5️⃣ Filter Early — WHERE before GROUP BY → 60% Faster Why group 1 million rows when a WHERE clause can reduce it to 10,000 first? 6️⃣ Avoid Functions on Indexed Columns → 85% Faster YEAR(log_date) = 2024 breaks the index. log_date >= '2024-01-01' uses it perfectly. ✅ 💡 The Real Truth: Writing SQL that works is easy. Writing SQL that performs is a skill. And in production environments with millions of rows — the difference between optimized and unoptimized SQL is the difference between 2 seconds and 2 minutes. That's the difference between a junior and a senior data professional. 🔥 🎯 Action Step for today: Open any query you wrote this week. Check — are you using SELECT *? Are you filtering before grouping? Fix one thing. Ship better code. 💪 📌 Save this post — you'll need it every time you write a complex query! ♻️ Repost to help your network write faster, cleaner SQL! 👇 Comment "OPTIMIZE" if you want the full SQL Performance Series! #SQL #SQLOptimization #QueryOptimization #DataEngineering #DatabasePerformance #DataAnalytics #SQLServer #MySQL #PostgreSQL #DataScience #TechSkills #CareerGrowth #DataAnalyst #SoftwareEngineering #BackendDevelopment #LinkedInLearning #ShankarMaheshwari #SQLTips #DataCommunity #LearnSQL

This is spot on — SQL performance is where real expertise shows. Small changes like indexing or avoiding SELECT * can make massive differences at scale. Definitely a must-know for anyone working seriously with data.

🚀 Day 32/100 — SQL Subqueries: Thinking Inside Queries 🧠💻 Today I learned Subqueries, a powerful concept in SQL used to solve complex problems step by step. 📊 What is a Subquery? 👉 A query inside another query ➡️ Used to break down complex problems into simpler parts 📌 What I explored today: 🔹 Subqueries in SELECT 🔹 Subqueries in WHERE 🔹 Subqueries in FROM 🔹 Nested queries for filtering 💻 Example Scenario: 👉 Find customers who made orders above the average order value 📌 Example Query: SELECT customer_id, order_amount FROM orders WHERE order_amount > ( SELECT AVG(order_amount) FROM orders ); 📊 How it works: 👉 Inner query → calculates average 👉 Outer query → filters higher-than-average orders 🔥 Key Learnings: 💡 Subqueries help solve complex business questions 💡 Makes SQL more flexible and powerful 💡 Commonly asked in interviews 🚀 Real-world use cases: ✔ Filtering based on averages ✔ Comparing values within datasets ✔ Dynamic data selection 🔥 Pro Tip: 👉 Use subqueries when: You need step-by-step filtering OR when JOINs become complex 📊 Tools Used: SQL | MySQL ✅ Day 32 complete. 👉 Quick question: Do you prefer solving problems using JOINs or Subqueries? 🤔 #Day32 #100DaysOfData #SQL #Subqueries #DataAnalytics #LearningInPublic #CareerGrowth #JobReady #InterviewPrep

🚀 **Understanding VIEW in SQL Server** A **VIEW** in SQL Server is a **virtual table** created from a `SELECT` query. It does not usually store data itself — it displays data from one or more tables whenever you query it. Think of it as a **saved query** that you can use like a table. --- 🔹 **Why Use a VIEW?** ✅ Simplify complex JOIN queries ✅ Reuse business logic ✅ Improve security by exposing selected columns only ✅ Make application queries cleaner ✅ Easier maintenance --- 🔹 **Basic Syntax** ```sql CREATE VIEW vw_EmployeeList AS SELECT Id, Name, Department FROM Employees; ``` Now use it like this: ```sql SELECT * FROM vw_EmployeeList; ``` --- 🔹 **Example with JOIN** ```sql CREATE VIEW vw_CustomerOrders AS SELECT    c.Name,   o.OrderId,   o.Amount FROM Customers c JOIN Orders o  ON c.CustomerId = o.CustomerId; ``` Then simply: ```sql SELECT * FROM vw_CustomerOrders; ``` --- 🔹 **Real Benefit** Instead of repeating a long query in many places, create it once as a VIEW and reuse it everywhere. --- 🔹 **Important Notes** ⚠️ A normal VIEW does **not automatically improve performance** ⚠️ It is mainly for organization, reusability, and security ⚠️ Avoid using too many nested views --- 🔹 **When to Use It** ✔ Reports ✔ Repeated joins ✔ Shared business logic ✔ Cleaner backend queries ✔ Restrict direct table access --- 💡 **Simple Summary** A VIEW is a **virtual table based on a SQL query**. It helps developers write cleaner and more maintainable SQL code. #SQLServer #Database #TSQL #BackendDevelopment #SoftwareEngineering #Programming #DataEngineering #SQLTips

Index in GROUP BY Clause in SQL Server In this post, I’ll explain how indexes improve performance in a GROUP BY query and also touch on the concept of a covering query. SQL Server uses two main algorithms for grouping data: Hash Aggregate – Creates a temporary hash table to store grouped results. Sort + Group – Sorts data by grouping columns, then aggregates sequentially. Both approaches require intermediate processing, but the Sort + Group algorithm can leverage indexes to avoid sorting, improving performance. 🔹 Why Index Matters in GROUP BY Consider this query: SELECT PS.ProductID, SUM(PS.QunatitSold) AS TotalQuantitySold FROM ProductSales PS GROUP BY PS.ProductID Without an index, SQL Server performs a Table Scan, which is expensive. 🔹 Step 1: Add Index on GROUP BY Column CREATE NONCLUSTERED INDEX IX_ProductSales_ProductID ON ProductSales(ProductID) Now SQL Server uses an Index Scan, but still performs RID Lookup, which adds overhead. 🔹 Step 2: Create a Better (Composite) Index CREATE NONCLUSTERED INDEX IX_ProductSales_ProductID_QunatitSold ON ProductSales(ProductID, QunatitSold) Now the query uses this optimized index, reducing extra lookups and improving performance. 🔹 What is a Covering Query? A query is called a Covering Query when all required columns are available in the index itself. Example: SELECT ProductID, QunatitSold FROM ProductSales Since both columns exist in the composite index, SQL Server does not need to access the table, making the query faster. ✅ Key Takeaways Index on GROUP BY columns improves performance Composite indexes reduce lookups Covering queries eliminate table access SQL Server automatically chooses the best execution plan. #SQLServer #Database #DataEngineering #DataAnalytics #SQL #TSQL #DatabasePerformance #QueryOptimization #Indexing #DataScience #SQLServerTips #SQLPerformance #QueryTuning #ExecutionPlan #DatabaseOptimization #IndexStrategy #CoveringIndex #GroupBy #TechLearning #LearnSQL #ArtificialIntelligence #BigData #CloudComputing #MicrosoftSQLServer #Azure #TechTrends #DigitalTransformation

SQL Tutorial: Complex transformations & the WITH clause 👇 Last post covered basic arithmetic transformations. This one tackles a limitation you'll hit almost immediately when you start building real calculations. 🔹 The alias-in-same-SELECT problem SQL cannot reference a column alias in the same SELECT where it's defined. This fails: SELECT *,   revenue - ad_spend - (paid_orders * avg_cost) AS ad_contribution,   100 * ad_contribution / revenue AS contribution_rate -- ERROR FROM campaign_performance; The database hasn't finished creating ad_contribution yet when it tries to use it on the next line. 🔹 The fix: WITH clause (CTEs) A Common Table Expression (CTE) breaks the calculation into named steps. The final SELECT queries from the temporary result, where the intermediate column already exists: WITH extended AS (   SELECT *,     revenue - ad_spend - (paid_orders * avg_cost) AS ad_contribution   FROM campaign_performance ) SELECT *,   100 * ad_contribution / NULLIF(revenue, 0) AS contribution_rate FROM extended; Clean, readable, no repeated formulas. 🔹 Percent of total with scalar subqueries To express each row as a % of the total, you need the SUM of the entire column — but aggregate functions collapse rows. The solution is a scalar subquery: a nested query that returns one value every row can use: SELECT *,   ROUND(     100 * revenue / NULLIF((SELECT SUM(revenue) FROM category_sales), 0),   2) AS revenue_pot FROM category_sales; 🔹 One thing that trips people up Percentages can exceed 100% when negative values exist in the data. If some rows have negative margins, they shrink the total — making profitable rows represent a larger share than expected. Always check for negatives before interpreting % metrics. Next up: filtering data with WHERE and HAVING. #SQL #PostgreSQL #DataAnalysis #LearningInPublic #TechTips

SQL Server Notes by AB | Note #12 | Histogram In Action | Original Draft Date: 19 Jan, 2022 | Re-posted on 27 Apr, 2026 | #SQLServerWithAmitBansal   In one of my previous notes, I had talked about the histogram, which is one of the most critical things in the stats object. Histogram, as the name suggests, is a bucketing technique of how the column data is distributed - the highest value of a bucket, which defines a boundary, how many rows are between two boundary values, how many rows are equal to a specific boundary value, how many unique values are there between a range and, how many rows are there on average per distinct value. All of this helps the optimizer make the right estimates. Here is a sample histogram on TotalDue column of SalesOrderHeader table (not putting down all the columns for brevity): RANGE_HI_KEY  RANGE_ROWS   EQ_ROWS =============  ============   ========= 26.2769         40              142 30.1444         17              202 . . (more rows) Now, let's we write a query: SELECT * FROM Sales.SalesOrderHeader WHERE TotalDue = 30.1444 The above query will return 202 rows. If check the cardinality estimation from the execution plan, you will observe the Estimated Number of Rows = 202 and the Actual Number of Rows=202. This is perfect and the best-case scenario for the optimizer where the estimate and the actual matched 100%. How did this happen? Well, this is called Histogram Step Hit. The predicate value mentioned in the query (WHERE condition) has a step representation in the histogram and the optimizer does a perfect estimation with EQ_ROWS (the number of rows equal to the step value), which is 202. The above is just a quick explanation of the ways how the optimizer leverages the histogram.   Demo URL: https://lnkd.in/g5-ef5gq. Want to read more SQL notes like this one? Here: https://bit.ly/ABSQLNotes. Looking for deep-dive content on SQL Server Performance Tuning? Get lifetime access to master class recordings. Check this: https://lnkd.in/d-JrAG78