TSQL Masters

1.Clustered Index Scan ✅ What it is: A Clustered Index Scan means SQL Server is scanning every row of the entire table,  essentially performing a "table scan" because the clustered index is the table itself.  📌 When it happens: No WHERE clause or very broad filter (low selectivity). Join/filter columns aren’t indexed selectively. Query needs most or all columns (covering queries). 🧠 Performance Insight: Can be expensive for large tables. Usually appears when SQL Server can’t seek and needs to read all data. Example:    Table: CREATE TABLE dbo.Sales (     SaleID INT NOT NULL IDENTITY,     SaleDate DATE NOT NULL,     Quantity INT NOT NULL, PRIMARY KEY (SaleID) ) Query: SELECT * FROM dbo.Sales WHERE  SaleDate  BETWEEN '2025-02-04' AND '2025-02-10' 2. Index Scan (Non-Clustered) ✅ What it is: A Non-Clustered Index Scan scans all rows in a specific non-clustered index( not the full table) 📌 When it happens: The query filters on a ...

Nested Loop Join in SQL Server Execution Plan 🧩 What Is a Nested Loop Join? A Nested Loop Join is a physical join operator used by SQL Server to combine rows from two input tables based on a specified join condition. It is best suited when: The outer input (typically the smaller dataset) is small. The inner input is indexed on the join key. This operator iteratively compares each row from the outer table with matching rows from the inner table , evaluating the join condition each time. ⚙️ How It Works – Execution Flow Read the first row from the outer input (top table). Use the join key from this row to search (seek/scan) the inner input (bottom table) for matching rows. If matches are found, combine the outer and inner rows and return them as part of the result. Repeat this process for each row in the outer input. 🧱 Understanding Outer and Inner Inputs   Outer Table      The input table processed first. SQL Server loops ...