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

This year we hired a new data team member who's been working with SQL Server for over two decades. One of her first tasks was to review and optimize all our data warehouse stored procedures used in reports. I had written many of these custom SQL reports—both inline queries and stored procedures—and I was eager to learn what patterns she found.

I'll share the two most common optimizations that delivered the biggest performance gains. These aren't the typical "it depends" answers you usually get with SQL Server. These are hard and fast rules that work almost every time.

OUTER APPLY Instead of LEFT JOIN Subqueries

This was the biggest performance win, almost every single time we implemented it.

The Old Way (LEFT JOIN approach):

SELECT o.orderId, o.customerId, o.orderDate, items.itemName as highestPricedItem
FROM Orders o
LEFT JOIN OrderItems items ON items.orderId = o.orderId
    AND items.price = (
        SELECT MAX(price) 
        FROM OrderItems oi2 
        WHERE oi2.orderId = o.orderId
    )

The Better Way (OUTER APPLY approach):

SELECT o.orderId, o.customerId, o.orderDate, items.itemName as highestPricedItem
FROM Orders o
OUTER APPLY (
    SELECT TOP 1 itemName
    FROM OrderItems oi
    WHERE oi.orderId = o.orderId
    ORDER BY price DESC
) items

Why OUTER APPLY wins:

• Much easier to read and understand

• Only scans the OrderItems table once instead of multiple times

• For "top N per group" scenarios, OUTER APPLY is almost always faster

• The query optimizer handles it more efficiently

Use #TempTables Instead of @TableVariables (With Primary Keys)

I used to love @TableVariables because they seemed so convenient. Turns out they're performance killers in most scenarios.

The Problem with @TableVariables:

• SQL Server doesn't maintain statistics on them

• They're always estimated as having exactly 1 row (terrible for query optimization)

• No parallel execution plans

• Very limited indexing options

The Better Approach:

-- Instead of this slow approach:
DECLARE @CustomerData TABLE (
    CustomerID INT,
    OrderCount INT,
    TotalSpent DECIMAL(10,2)
)

-- Use this faster approach:
CREATE TABLE #CustomerData (
    CustomerID INT PRIMARY KEY,
    OrderCount INT,
    TotalSpent DECIMAL(10,2)
)

Key Performance Boosters:

• Always add a primary key to temp tables - this creates a clustered index automatically

• Add additional indexes as needed for your queries

• Statistics are maintained - SQL Server can make intelligent optimization decisions

Bonus: Clean Syntax (SQL Server 2016+):

-- Old verbose way:
IF OBJECT_ID('tempdb..#CustomerData') IS NOT NULL
    DROP TABLE #CustomerData

-- New clean syntax:
DROP TABLE IF EXISTS #CustomerData

When to Use Each:

• @TableVariables: Only for very small datasets (under 100 rows) or when you need the variable to persist across dynamic SQL

• #TempTables: Everything else, especially when joining or filtering large datasets

Pro Tip: Start with #TempTables from the Beginning

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

• You'll need to add more complex joins

• The query optimizer will need better statistics

• You'll want to add indexes for performance

Starting with #TempTables means you're already set up for success, even if you don't need the performance benefits initially.

In our testing with 10,000+ rows, temp tables with proper indexing often performed 5-10x faster than table variables.

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.

Sometimes the best optimizations aren't the fancy new features—they're the fundamental patterns that work consistently across different scenarios.