Professional SQL Server™ 2005 Programming

7.5. Dealing with File-Based Information

BLOBs. You haven't really seen enough of them to hate them yet. Whether that's a "yet" or not largely depends on whether you need to support backward compatibility or not.

Beginning with SQL Server 2005, we have some new data types (varchar(max), nvarchar(max), and varbinary(max)) available that can greatly simplify dealing with BLOBs. When used with a compatible data access model (ADO.NET 2.0 or higher), you can access BLOB data as though it were the same as its smaller base data type (varchar, nvarchar, or varbinary). Unfortunately, for many of you reading this, you'll need to deal with backward compatibility issues, so you'll have to use the older (and even slower) "chunking" method to access your data. Regardless of which access method you're using, BLOBs, or Binary Large Objects, are slow — very slow and big. Hey, did I mention they were slow?

BLOBs are nice in the sense that they let you break the 8K barrier on row size (BLOBs can be up to about 2GB in size). The first problem is that they can be clumsy to use (particularly under the old data types and access methods). Perhaps the larger problem, however, is that they are painfully slow (I know, I'm repeating myself, but I suspect I'm also making a point here). In the race between the BLOB and the tortoise (the sequel to the tortoise and the hare), the BLOB won only after the tortoise stopped for a nap.

Okay, okay, so I've beaten the slow thing into the ground. Indeed, there have ...