Presents your  
SQL SERVER E-NEWSLETTER for September 2, 2003 
 
<-------------------------------------------> 
  
SEE WHY BLOBs CAN BE DIFFICULT TO MANAGE   
  
If you mainly use SQL Server 2000 for transaction processing, you're 
probably skilled in using SQL features on numerical data. SQL Server 2000, 
however, has a number of features that you can use to manipulate other 
types of large bits of data called BLOBs (which stands for binary large objects).   
  
A BLOB can refer to large data values such as those stored in text or 
ntext columns; a BLOB can also refer to a piece of binary data that is 
exceptionally large such as images or digital audio tracks.   
  
Text and image data types are best used when storage requirements for 
these elements exceed the 8,000-character column limit set by SQL. Text and 
image data types can store up to 2 GB of binary data per object.   
  
There are a number of functions and statements that you can use with 
BLOBs, including DATALENGTH, PATINDEX, SUBSTRING, TEXTPTR, TEXTVALID, 
READTEXT, SET TEXTSIZE, UPDATE TEXT, and WRITE TEXT.   
  
SQL Server 2000 doesn't store these large pieces of binary data in the 
table column. Instead, SQL stores a 16-byte pointer in the table and 
stores the actual data in a separate data page.   
  
It's important to note that BLOBs are cumbersome and hard on your 
database performance. If you have a choice of cutting your data into smaller 
pieces, you're advised to do so. What makes BLOBs even more difficult to
manage is that there are rules about columns and data types that you may be 
using that no longer apply. Therefore, you might be better off storing 
these large items as separate files and then storing paths to those files 
in your database.   
  
Here are more considerations about using BLOBs:   
  
* BLOBs are difficult to retrieve since you can't use a typical select 
statement. Also, you can't control the large amount of text you may be 
retrieving. You can, however, use the READTEXT statement to pull specific 
chunks of text from the full field.   
  
* If you're only looking for a smaller substring, PATINDEX is probably 
the function you want to use to locate the string and retrieve it.   
  
* Use UPDATETEXT to modify a portion of a BLOB field and WRITETEXT to 
replace the entire contents of the field.   
  
* You can use UPDATETEXT and WRITETEXT with WITH LOG, but the default 
for BLOB updates isn't to log them. This makes your transaction log 
virtually useless and means you have to do full backups to keep your
backup data useable.   
  
Now that you have the pertinent facts about BLOBs, you can make an 
educated decision about whether you still want to use them.   
  
J.E. Harvey, MCSD, MCDBA, has been tinkering with and writing about 
technology for more than two decades.   
  
----------------------------------------