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.
TYPE Used for...
IMAGE Binary data, such as pictures o Excel spreadsheets
TEXT Text data in an 8-bit character set
NTEXT Text 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.