THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Page Free Space

See also my SQL Server technical articles on

Browse by Tags

All Tags » Internals » Aggregates » Query Plans

  • Fun with Aggregates

    There are interesting things to be learned from even the simplest queries. For example, imagine you are given the task of writing a query to list AdventureWorks product names where the product has at least one entry in the transaction history table, but Read More...
  • Row Goals and Grouping

    You might recall (from my last post) that query plans containing a row goal tend to favour nested loops or merge join over hashing. This is because a hash join has to fully process its build input (to populate its hash table) before it can start probing for matches from its second input. Hash join therefore has a high start-up cost, which is balanced by a lower per-row cost once probing begins. In this post, I’ll take a look at how row goals affect grouping operations. Grouping Strategies While the start-up cost of hash join often makes it unsuitable for plans with a row goal, there are times when hashing operations may feature in such plans, since the Hash Match iterator also supports a streaming mode. As an example, say we are asked to list one hundred unique first names from the AdventureWorks Contacts table: Read More...
  • Partitioning and the Common Subexpression Spool

    SQL Server 2005 introduced the OVER clause to enable partitioning of rowsets before applying a window function. This post looks at how this feature may require a query plan containing a 'common subexpression spool'. This query plan construction is required whenever an aggregate window function or the NTILE ranking window function is used. Example To illustrate, here is a simple query based on the AdventureWorks sample database. The AdventureWorks product warehouse is organised into shelves, with multiple bins per shelf. Each bin can hold several different products. We have been asked to produce a report with the following (partial) output: Notice that the total_in_bin column contains the sum of product quantities, partitioned by shelf and bin. We can meet the requirements using a query featuring the OVER clause: Read More...
Privacy Statement