THE SQL Server Blog Spot on the Web

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

Davide Mauri

A place for my thoughts and experiences the Microsoft Data Platform

NOEXPAND Hint – Give your feedback!

I’ve just filled a feedback on Connect to ask for the possibility to use the NOEXPAND hint with CTEs, Views and Subqueries. This will help to avoid several problems, both from a logical and performance point of view. 

Don’t you think is *so* important? Well, let me show you two example that, with Itzik’s help, I’ve set up to show what kind of problem we face right now…problems that can be solved only using a temporary table to put intermediate data. This means that we *must* use stored procedures, which is not always possible. With the ability to use the NOEXPAND hint, all those problems would be solved.

Problem 1 – Query Execution Error

This sample shows that sometimes the query processor may decide to push the predicate in the cte (ptype = ‘number’) to the outer query and process it after the outer query’s predicate (cast(val as int) = 100). Of course this lead to an error when the value “DONTWORK” is cast to int. If the CTE wouldn't have been expanded, its predicate would have been processed always before, thus guaranteeing execution correctness and stability. This error may not happen always, since if the table is small enough (say, 100 rows instead of 1000) the processor won’t “push out” the inner predicate, and thus will avoid the error. Unfortunately this behavior is not easily predictable so the results is that they query may or may not work from time to time. A NOEXPAND hint will solve this problem once and for all.

USE tempdb
if object_id('dbo.fn_Nums') is not null drop function dbo.fn_Nums;
create function dbo.fn_Nums(@m as bigint) returns table
t0 as (select n = 1 union all select n = 1),
t1 as (select n = 1 from t0 as a, t0 as b),
t2 as (select n = 1 from t1 as a, t1 as b),
t3 as (select n = 1 from t2 as a, t2 as b),
t4 as (select n = 1 from t3 as a, t3 as b),
t5 as (select n = 1 from t4 as a, t4 as b),
result as (select row_number() over (order by n) as n from t5)
select n from result where n <= @m
if object_id('') is not null drop table;
create table
  id int not null,
  ptype varchar(20) not null,
  val varchar(50) not null,
  filler binary(200) not null default(0x01)
insert into, ptype, val)
  select n, 'number', cast(n as varchar(11)) from dbo.fn_nums(1000)
  union all
  select 100000, 'string', 'DONTWORK'
create index idx_val on;
with numbers as
  select id, val
  where ptype = 'number'
select *
from numbers
where cast(val as int) = 100;

Problem 2 – Performances

This sample shows that the CTE gets evaluated two times, while it could have been evaluated only once, and thus doubling the performances of the query itself, requiring only the half of the original I/O. The workaround today is the usage of temporary table to store CTE results. Being able to use the NOEXPAND hint here will help avoid putting the result of the CTE into a temp table and the query that temp table to produce the final result with optimum performances.

USE AdventureWorksDW
IF OBJECT_ID('dbo.FactInternetSalesBig', 'U') IS NOT NULL 
    DROP TABLE dbo.FactInternetSalesBig
SELECT * INTO dbo.FactInternetSalesBig FROM dbo.FactInternetSales fis
INSERT INTO  dbo.FactInternetSalesBig SELECT * FROM dbo.FactInternetSales fis
GO 10
        year_orderdate = dt.CalendarYear,
        total_amount = SUM(fisb.SalesAmount)
        dbo.FactInternetSalesBig fisb
        dbo.DimTime dt ON fisb.OrderDateKey = dt.TimeKey
    diff = c.total_amount - p.total_amount
    cte AS c
    cte AS p ON c.year_orderdate = p.year_orderdate + 1

On the Connect page you can find the scripts above and the proposed solution. I think is a feature we *really* need. If you think so, please vote, thanks!

Published Wednesday, February 17, 2010 7:26 PM by Davide Mauri

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



Mazhar Karimi said:

Use Table Variable instead of CTE, and compare the results.

March 11, 2010 10:16 AM

Davide Mauri said:

Hi Mazhar, the problem with TV is that they are variables and thus for them SQL Server doesn't calculate data distribution statistics. This means that have a very high probability that you'll get sub-optimal execution plans.

March 12, 2010 4:38 AM

Leave a Comment


About Davide Mauri

Director of Software Development & Cloud Infrastructure @ Sensoria, an innovative smart garments and wearable company. After more than 15 year playing with the Microsoft Data Platform, with a specific focus on High Performance databases, Business Intelligence, Data Science and Data Architectures, he's now applying all his skills to IoT, defining architectures to crunch numbers, create nice user experiences and provide meaningful insights, all leveraging Microsoft Azure cloud. MVP on Data Platform since 2006 he has a very strong background development and love both the ER model and OO principles. He is also a fan of Agile Methodology and Automation, which he tries to apply everywhere he can, to make sure that "people think, machines do".

This Blog


Privacy Statement