Hard and Fast T-SQL Rules: OUTER APPLY and #TempTables

After years of optimizing SQL Server queries, I've learned that some patterns work consistently across different scenarios. Here are two fundamental rules that have transformed our reporting performance:

Rule 1: Use OUTER APPLY for "Top N Per Group" Queries

Instead of complex window functions or correlated subqueries, OUTER APPLY is often the cleanest solution for "top N per group" scenarios.

**Before (complex window function):**

WITH RankedData AS (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY Category ORDER BY Value DESC) as rn
  FROM MyTable
)
SELECT * FROM RankedData WHERE rn <= 3

**After (simple OUTER APPLY):**

SELECT c.*, t.*
FROM Categories c
OUTER APPLY (
  SELECT TOP 3 *
  FROM MyTable t
  WHERE t.CategoryId = c.Id
  ORDER BY t.Value DESC
) t

The OUTER APPLY approach is often faster and more readable.

Rule 2: Start with #TempTables, Not @TableVariables

This was a game-changer for us. @TableVariables have limitations that #TempTables don't:

- No statistics (SQL Server assumes 1 row)

- No parallel execution plans

- Limited indexing options

**The Performance Impact:**

- @TableVariables: Often results in nested loop joins

- #TempTables: Can use hash joins, merge joins, and parallel execution

Here's something I learned the hard way: I used to think I could start with @TableVariables for small datasets and switch to #TempTables later if needed. But that's backwards thinking.

On big datasets, I already knew to use #TempTables. But here's the key insight—because results could often grow as the report evolved, it's generally better to just start with #TempTables from the beginning.

You never know when:

• Your "small" dataset will grow

• Your report will be copied to use a base for another report

• You'll need to add more complex joins

Starting with #TempTables is best practice, even if you don't need the performance benefits immediately. Memorize or add a code snippet to drop the table at the beginning of the query, and you will save time fussing with them during development.

The Bottom Line

These two changes alone transformed our reporting performance. The OUTER APPLY pattern especially was a game-changer for our "top N per group" queries, and switching from @TableVariables to #TempTables with proper indexing eliminated most of our performance bottlenecks.

The fundamental patterns, which work consistently across different scenarios, are something that I am always looking for.