When I first heard the word 'BLOB' I had no idea it was an acronym. I thought it really just meant an unformatted bunch of bits. :-)
But since that time, I've learned a bit more. SQL Server supports two kinds of Large Objects, and has since version 4.0 (Sybase). There is text, which is a large type holding character data, and image, which is a large type holding binary data. SQL 2000 added ntext, to hold Unicode characters.
So LOB (Large OBject) refers to any of these types, which have a special way of being stored internally, on special pages called 'LOB Pages'.
BLOB means Binary Large OBject and only refers to image.
CLOB means Character Large OBject and refers to text or ntext.
A column of LOB data can store up to 2GB worth of data, so that could be lots of pages. When you select a column holding LOB data (or select *), SQL Server needs to access every one of those pages. However, if you are looking at the page accesses using SET STATISTICS IO, you get different output in SQL 2000 and SQL 2005.
Here's an example on 2005.
-- First, create a table in a test database.
CREATE TABLE hugerows
c text );
-- Insert a row into the table; Note that replicate cannot return more than 8000 bytes unless you convert the first argument to varchar(max)
INSERT INTO hugerows
SELECT REPLICATE('g', 100), REPLICATE('h', 1000),
REPLICATE(CAST('x' as varchar(max)), 300000);
The following code will show you how many of each type of page belong to the 'hugerows' table.
SELECT type_desc, sum(total_pages)
FROM sys.partitions p JOIN sys.allocation_units a
ON p.partition_id = a.container_id
WHERE p.object_id = object_id('hugerows')
GROUP BY type_desc;
The output should indicate 40 pages for LOB data.
Now SET STATISTICS IO to ON and SELECT from this table:
SET STATISTICS IO ON;
SELECT * FROM hugerows;
You should see the following message:
(1 row(s) affected)
Table 'hugerows'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 224, lob physical reads 0, lob read-ahead reads 0.
SQL 2005 includes the metrics for 'lob logical reads' and 'lob physical reads' which are not available in SQL 2000 at all. SQL 2000 would just show you the regular logical reads value and that would NOT include the reads of the lob pages.
So be careful!