### 🛑 The Mistake: Killing Performance with Non-SARGable Queries One of the most common mistakes beginners make in Microsoft SQL Server is using functions on indexed columns in the `WHERE` clause. This makes the query **non-SARGable** (Search Argument Able). #### The Scenario Imagine you have an `orders` table with millions of rows, and there is an index on the `order_date` column. You want to find all orders from the year 2023. A beginner might write: ```sql SELECT order_id, customer_id, order_date FROM orders WHERE YEAR(order_date) = 2023; ``` #### Why this is a disaster in MS SQL Server: When you wrap `order_date` in the `YEAR()` function, SQL Server cannot use the index on `order_date` to find the rows quickly! Instead, it has to: 1. Read every single row in the table (Table Scan or Index Scan). 2. Apply the `YEAR()` function to that row's date. 3. Check if the result is 2023. If you have 10 million rows, SQL Server does 10 million function calculations! This turns a query that should take milliseconds into one that takes seconds or even minutes, locking the table and slowing down the whole database. #### 🚀 The Solution: Keep the Column Naked! To avoid this, you must keep the column on one side of the operator "naked" (without functions) and put the logic on the value side. Here is the SARGable (Index-friendly) way to write it: ```sql SELECT order_id, customer_id, order_date FROM orders WHERE order_date >= '2023-01-01' AND order_date < '2024-01-01'; ``` #### Why this is fast: Now, the column `order_date` is untouched. SQL Server can look at the index and say: "Okay, I know exactly where '2023-01-01' starts and where '2024-01-01' ends." It does an **Index Seek**, fetching only the relevant rows instantly! #### 💡 Other Common Non-SARGable Traps: - **String manipulation:** `WHERE LEFT(last_name, 3) = 'Smi'` ❌ -> Use `WHERE last_name LIKE 'Smi%'` ✅ - **Implicit Conversion:** Comparing a `VARCHAR` column with an integer without quotes can also cause SQL Server to convert the column, disabling the index! Always remember: **Keep your columns naked in the WHERE clause!** #### 🙋♂️ Frequently Asked Questions **Q: Does using functions in the SELECT clause also kill performance?** A: Usually, no. Functions in the `SELECT` clause are calculated only for the rows that match the `WHERE` clause. So if your `WHERE` clause filters down to 10 rows, the function runs only 10 times. However, if you return millions of rows, it can still slow down the output generation. **Q: Are there any exceptions where functions in the WHERE clause are okay?** A: Yes. If the table is very small (a few hundred rows), the performance difference is negligible. Also, if the function does not depend on the column values (e.g., `WHERE order_date > DATEADD(day, -7, GETDATE())`), it is evaluated only once for the whole query and is SARGable!