Think Set-Based

Paul Randal explains why you should think in a set-based manner when writing T-SQL queries:

This explanation involves a similar problem but not involving SQL Server. Imagine you need to acquire twelve 4′ x 8′ plywood sheets from your local home improvement store.

You could drive to and from the store twelve times, and each time you need to go into the store, purchase the sheet, and wait for a staff member to become available to load the sheet into your pickup truck, then drive home and unload the sheet.

Or you could drive to the store once and purchase all twelve sheets in one go, with maybe four staff members making three trips each out to your pickup, carrying one sheet each time. Or even just one staff member making twelve trips out to your pickup.

Which method is more efficient? Multiple trips to the store or one trip to the store, no matter how many staff members are available to carry the sheets out?

On the SQL Server side, the APPLY operator is a method for bridging set-based operations with procedural thought patterns.  It’s not the perfect answer for everything, but there are some fantastic use cases (like simplifying calculations via chained APPLY operators).

Related Posts

Performance Concern: Anti-Join And Top

Paul White explains a scenario in which an innocent-looking execution plan can hide something sinister: Not every execution plan containing an apply anti join with a Top (1) operator on its inner side will be problematic. Nevertheless, it is a pattern to recognise and one which almost always requires further investigation. The four main elements […]

Read More

Character Columns And MAX Vs TOP+ORDER Differences

Kendra Little digs into a tricky performance problem: Most of the time in SQL Server, the MAX() function and a TOP(1) ORDER BY DESC will behave very similarly. If you give them a rowstore index leading on the column in question, they’re generally smart enough to go to the correct end of the index, and […]

Read More


May 2016
« Apr Jun »