Data Storage Internals - Pro SQL Server Internals - page 22

Listing 1-16. Data row size and performance: Table creation

create table dbo.LargeRows
(
ID int not null,
Col char(2000) null
);

create table dbo.SmallRows
(
ID int not null,
Col varchar(2000) null
);

;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
,IDs(ID) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM N5)
insert into dbo.LargeRows(ID, Col)
select ID, 'Placeholder'
from Ids;

;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
,IDs(ID) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM N5)
insert into dbo.SmallRows(ID, Col)
select ID, 'Placeholder'
from Ids;

Now let's run the selects that scan the data and compare the number of I/O operations and execution times. You
can see the code in Listing 1-17. The results I got on my computer are shown in Table 1-3 .

Listing 1-17. Data row size and performance: Comparing performance

set statistics io on
set statistics time on

select count(*) from dbo.LargeRows;
select count(*) from dbo.SmallRows;

set statistics io off
set statistics time off

Table 1-3. Number of reads and execution time of the queries

dbo.SmallRows dbo.LargeRows

Number of reads 227 16,384

Execution time 5 ms 31 ms