sql server - Why does using a variable cause the query to never complete? -

i have table of ~10 million bigints offset larger known constant.

i'd know how many numbers within range of constant. (the actual query doesn't matter). when use constant converted bigint performance acceptable (1 second). when store constant in variable or parameter query never finishes. here script generate sample table:

if object_id('bigints', 'u') not null   drop table bigints;  t      (select *           (values(1),(1),(1),                       (1),(1),(1),                       (1),(1),(1),                       (1))f(n)) select num = convert(bigint, 123456789012)              + abs(binary_checksum(newid()))   bigints   t a,        t b,        t c,        t d,        t e,        t f,        t g  

and here example of failing query:

declare @const bigint = convert(bigint, 123456789012);  t      (select *           (values(1),(1),(1),                       (1),(1),(1),                       (1),(1),(1),                       (1))f(n)),      tally(n)      (select @const                 + row_number() on (order (select null))            t a,                 t b,                 t c,                 t d) select count(*)   tally t        join bigints b          on t.n = b.num  

if replace @const + row_number() expression convert(bigint, 123456789012) + row_number() query complete. why introduction of variable cause query run forever?

this done on sql server 2012 , sql server 2014 both cause same issue.

the fast plan gets 10,000 rows going hash join on bigints bitmap filter.

the slow plan gets 10,000 rows going nested loops join onto 10,000,000 table intermediate table spool.

10,000 executions against spool going slow.

it looks query optimiser doesn't give choice use nested loops though.

attempting force issue inner hash join hint produces

query processor not produce query plan because of hints defined in query. resubmit query without specifying hints , without using set forceplan.

indeed issue can seen in simpler queries adding reference variable below (also fails)

declare @s varchar(1) = '';  select *   master..spt_values t1        inner hash join master..spt_values t2                     on t1.name = (t2.name + @s); 

this additional reference variable shouldn't prevent hash (or merge) join on query equality predicate , fixed bug (relevant kb article - performance issues occur when join predicate in query has outer reference columns in sql server 2005 or in sql server 2008)

symptoms

you run query in microsoft sql server 2005 or in microsoft sql server 2008, , join predicate in query has outer reference columns. in case, may experience performance issues, , query cannot completed.

note issue not occur in microsoft sql server 2000.

cause

the problem described in "symptoms" section occurs because database engine cannot generate merge join or hash join. therefore, loop join used instead. using loop join causes performance problem.

however must running on 1 of builds (or later) described in hotfix link (sql server 2005 sp3 cu7, 2008 rtm cu9, 2008 sp1 cu6) , must running trace flag 4199 enabled take advantage of fix (even , including sql server 2014).

other less satisfactory work arounds on 2008 cu5+ parameter embedding optimization solves things option (recompile).

the following recompiled on each invocation treating variable though constant , compilation succeeds desired faster plan.

declare @const bigint = convert(bigint, 123456789012);  t      (select *           (values(1),(1),(1),                       (1),(1),(1),                       (1),(1),(1),                       (1))f(n)),  tally(n)      (select @const                 + row_number() on (order (select null))            t a,                 t b,                 t c,                 t d) select count(*)   tally t        inner hash join bigints b                     on t.n = b.num option (recompile); 

or following defeats issue , allows main join use hash or merge @ cost of plan complexity.

declare @s varchar(1) = '';  select *   master..spt_values t1        inner join master..spt_values t2                     on t1.name = (t2.name + (select max(@s)));