THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Kalen Delaney

Geek City: LOBs are either BLOBs or CLOBs

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.

USE testdb;
   (a char(100), 
    b varchar(1000),
    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:

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!


Published Thursday, March 13, 2008 12:59 PM by Kalen Delaney

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



James Luetkehoelter said:

An excellent point to make Kalen. I've had the hardest time explaining the difference between BLOB an CLOB, regardless of the database platform.

March 13, 2008 7:41 PM

Joel Wiseheart said:

It turns out your first idea was the correct one! Blobs were originally just a blob of bits, according to Jim Starkey, the man at DEC who INVENTED blobs. Marketing people thought the original name 'blob' sounded unprofessional. First, they changed it to 'segmented strings'. Then they changed it back to blob, but assigned the ~backronym~ 'basic large objects', and then 'binary large objects. Don't believe me? Here's two independent posts from Jim Starkey, who tells the whole story that it didn't stand for anything, and was named after 'the thing that ate Cincinnati, Cleveland or whatever':

1997 post:

2015 CIO magazine interview:

February 28, 2015 2:55 AM

king said:



January 16, 2018 11:33 PM

aaaa said:

mt0417 mt0417

April 16, 2018 8:51 PM

chenyingying said:


May 6, 2018 11:05 PM

linying123 said:


May 10, 2018 8:10 PM

shenyuhang said:


June 1, 2018 7:04 PM

Leave a Comment


This Blog


Favorite Non-technical Sites or Blogs

Privacy Statement