Efficiently accessing large objects in the database: introduction

This is the first post of a series of articles on accessing large objects in the database. You can find all the links in this series at the bottom of this post.

You can use binary large objects (usually abbreviated as BLOBs) and character large objects (known as CLOBs) to store large pieces of data, such as an image or a document inside a field in a database table. We shall use the term large objects (LOBs) to refer to both BLOBs and CLOBs.

The process for reading and writing LOBs to and from a database may need to be different from the process that you use with normal, “small” fields, especially if the size of the objects that you are reading or writing is substantial.

TYPEUsed for...
IMAGEBinary data, such as pictures o Excel spreadsheets
TEXTText data in an 8-bit character set
NTEXTText data in Unicode

Starting with SQL Server 2005, some new data types were introduced, known as Large Value Data Types (LVDTs). They are intended ultimately to replace the LOB data types. LVDTs are based on the existing VARBINARY, VARCHAR, and NVARCHAR data types, using the MAX specifier to indicate the length of the column. For instance, you might define a column as being of type VARCHAR(MAX). These types of columns can store up to 2 GB of data, just the same as IMAGE, TEXT or NTEXT.

The advantage of using LVDTs is that you can use them in exactly the same way as ordinary columns. For example, you can concatenate them together, and you can use the regular SQL comparison operators to compare them (which you cannot do with IMAGE, TEXT and NTEXT).

When accessing these fields from your client code, you may need to apply some special techniques. For instance, if a VARCHAR(MAX) contains 2 Gigabytes on the database, you can’t just use a DataReader to read its value into a String in memory, like you would read an ordinary field. Even if you can fit the string into memory, moving such amounts of data every time you need to process a portion of that information is bound to produce a slow application.

Posts in this series

Alberto Poblacion


Alberto has been working for the computer industry for more than 27 years, most of them as a software developer. He has held just about every possible position in software development up to and including being the CSA for a small ISV. Currently he works as a freelance developer and consultant, as well as providing training as a Microsoft Certified Trainer, mostly on the field of software development under the .NET platform. He's a Microsoft MVP in C#.

, ,

No comments yet.

Leave a Reply