Database Reference
In-Depth Information
It is also worth mentioning that SQL Server always stores rows that fit into a single page using in-row allocations.
When a page does not have enough free space to accommodate a row, SQL Server allocates a new page and places the
row there rather than placing it on the half-full page and moving some of the data to row-overflow pages.
SELECT * and I/O
There are plenty of reasons why selecting all columns from a table with the
select *
operator is not a good idea.
It increases network traffic by transmitting columns that the client application does not need. It also makes query
performance tuning more complicated, and it introduces side effects when the table schema changes.
It is recommended that you avoid such a pattern and explicitly specify the list of columns needed by the client
application. This is especially important with row-overflow and LOB storage, when one row can have data stored in multiple
data pages. SQL Server needs to read all of those pages, which can significantly decrease the performance of queries.
As an example, let's assume that we have table
dbo.Employees
with one column storing employee pictures. The
Listing 1-14 creates the table and populates it with some data.
Listing 1-14.
Select * and I/O: Table creation
create table dbo.Employees
(
EmployeeId int not null,
Name varchar(128) not null,
Picture varbinary(max) 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 N2 AS T2) -- 1,024 rows
,IDs(ID) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM N5)
insert into dbo.Employees(EmployeeId, Name, Picture)
select
ID, 'Employee ' + convert(varchar(5),ID),
convert(varbinary(max),replicate(convert(varchar(max),'a'),120000))
from Ids;
The table has 1,024 rows with binary data amounting to 120,000 bytes. Let's assume that we have code in the client
application that needs the
EmployeeId
and
Name
to populate a drop-down box. If a developer is not careful, he or she
can write a select statement using the
select *
pattern, even though a picture is not needed for this particular use-case.
Let's compare the performance of two selects; one selecting all data columns and another that selects only
EmployeeId
and
Name
. The code to do this is shown in Listing 1-15. The execution time and number of reads on my
computer is shown in Table
1-1
.
Listing 1-15.
Select * and I/O: Performance comparison
set statistics io on
set statistics time on
select * from dbo.Employees;
select EmployeeId, Name from dbo.Employees;
set statistics io off
set statistics time off
