A Way Around "Binary Hell" in SQL Server

Warning: this article may offend your religious feelings - sorry about that. All others: play loud!

What is Hell? According to the famous rock band AC/DC (yeah!) it "ain't a bad place to be". For many people, regardless of their confession, it's supposedly some kind of dark pit, nobody knows exactly where it is, but it ought to be used to stow away the bad guys and gals for eternity. Once in a while it might happen that one of its "inhabitants" shows up on the surface, causing nothing but trouble ...

Believe it or not: SQL Server has such a place for the doomed, too. And it's got a name for hell: Binary Large Object, aka BLOB (or LOB for short).

BLOB data-types (in SQL text, image, etc.) are basically used to storing data that does not fit into a "proper" data-type (as int, decimal, varchar, etc.), for example to save Pictures, XML data or Documents and stuff. Technically, there are actually better alternatives for such a storage - or "linkage" to be true -, like using "Record Links" in NAV or the FILESTREAM feature introduced with SQL Server 2008 (unfortunately NAV cannot take advantage of that, yet). But bad habits die hard ... thus still the BLOB is often used ... too often ...

The technical aspects:
Binary data could be stored with up to 2^31 Byte (= 2GB) per field. Usually this data is not saved within the actual row/record as this would tremendously increase the size of the table, respective the "Clustered Index" (I guess we could call that "Hell on Earth" then). Thus, only a 16 Byte pointer is saved in the record, the BLOB ...