THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - 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;
CREATE TABLE hugerows
   (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:

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!

~Kalen

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

Comments

 

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

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Favorite Non-technical Sites or Blogs

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement