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.
https://connect.microsoft.com/SQLServer/feedback/details/533766/make-noexpand-hint-usable-also-with-cte-views-and-subqueries
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
go
if object_id('dbo.fn_Nums') is not null drop function dbo.fn_Nums;
go
create function dbo.fn_Nums(@m as bigint) returns table
as
return
with
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
go
if object_id('dbo.properties') is not null drop table dbo.properties;
go
create table dbo.properties
(
id int not null,
ptype varchar(20) not null,
val varchar(50) not null,
filler binary(200) not null default(0x01)
);
insert into dbo.properties(id, ptype, val)
select n, 'number', cast(n as varchar(11)) from dbo.fn_nums(1000)
union all
select 100000, 'string', 'DONTWORK'
go
create index idx_val on dbo.properties(val);
go
with numbers as
(
select id, val
from dbo.properties
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
GO
IF OBJECT_ID('dbo.FactInternetSalesBig', 'U') IS NOT NULL
DROP TABLE dbo.FactInternetSalesBig
GO
SELECT * INTO dbo.FactInternetSalesBig FROM dbo.FactInternetSales fis
Go
INSERT INTO dbo.FactInternetSalesBig SELECT * FROM dbo.FactInternetSales fis
GO 10
WITH cte AS
(
SELECT
year_orderdate = dt.CalendarYear,
total_amount = SUM(fisb.SalesAmount)
FROM
dbo.FactInternetSalesBig fisb
INNER JOIN
dbo.DimTime dt ON fisb.OrderDateKey = dt.TimeKey
GROUP BY
dt.CalendarYear
)
SELECT
c.year_orderdate,
diff = c.total_amount - p.total_amount
FROM
cte AS c
LEFT JOIN
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!