Query Optimization and Execution - Pro SQL Server Internals - page 457

Listing 25-4. Variable-length columns and memory grant: Table creation
create table dbo.Data1
(
ID int not null,
Value varchar(100) not null,
constraint PK_Data1
primary key clustered(ID)
);
create table dbo.Data2
(
ID int not null,
Value varchar(200) not null,
constraint PK_Data2
primary key clustered(ID)
);
;with N1(C) as (select 0 union all select 0) -- 2 rows
,N2(C) as (select 0 from N1 as T1 cross join N1 as T2) -- 4 rows
,N3(C) as (select 0 from N2 as T1 cross join N2 as T2) -- 16 rows
,N4(C) as (select 0 from N3 as T1 cross join N3 as T2) -- 256 rows
,N5(C) as (select 0 from N4 as T1 cross join N4 as T2 ) -- 65,536 rows
,Nums(Num) as (select row_number() over (order by (select null)) from N5)
insert into dbo.Data1(ID, Value)
select Num , replicate('0',100)
from Nums;
insert into dbo.Data2(ID, Value)
select ID, Value from dbo.Data1;
In the next step, let's run two identical queries against those tables, as shown in Listing 25-5. I am using the
variable as a way to discard the result set.
Listing 25-5. Variable-length columns and memory grant: Queries
declare
@V varchar(200)
select @V = Value
from dbo.Data1
where ID < 42000
order by Value, ID desc
select @V = Value
from dbo.Data2
where ID < 42000
order by Value, ID desc
As you can see in Figure 25-8 , an incorrect memory grant forced SQL Server to spill data to tempdb , which
increases the execution time.