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

Content now hosted at
More of my SQL Server technical articles are at

Browse by Tags

All Tags » Segment

  • Ranking Function Optimizer Transformations

    In my last post I showed how SQL Server 2005 and later can use a Segment Spool to efficiently implement aggregate window functions and the NTILE ranking function. The query optimizer is also smart enough to recognise that some queries are logically equivalent to a window function, even if they are written using different syntax. As a reminder, here's the sample data defining what the AdventureWorks report output should look like: Here's a query written to meet the same requirements, but without the SUM...OVER construction used last time: 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