Working with Binary Large Objects (BLOBs) Using SQL Server and ADO.NET

Large objects come in many flavors. The LOBs can be broadly classified as Character Large Objects (CLOBs) or Binary Large Objects (BLOBs). Luckily, SQL server provides different data types to handle these different flavors of data. Have a look at the following table that lists various SQL Server data types that are used in conjunction with LOBs.

The Text, NText, and Image data types come from the previous versions of SQL Server. It is recommended that you use the newer varchar(MAX), nvarchar(MAX), and varbinary(MAX) data types instead. Varbinary(MAX) is the data type that allows you to deal with large binary data; therefore, the discussion will focus on using varbinary(MAX) data in .NET applications.

Storing BLOBs in SQL Server

Binary data comes in variety of flavors. Word documents, PDF invoices, Photos, product images, raw data are all examples of BLOB data. When storing BLOBs as a part of your .NET application, you have two choices:

  1. Store BLOBs as disk files
  2. Store BLOBs directly inside a SQL Server database

Both the choices have some pros and cons. The first approach is good if:

  1. You have few hosting resources in terms of SQL Server space.
  2. The BLOB data is being used by third parties. For example, you might have images that are being created by a graphics designer.
  3. You want to use a file system-based editor to manipulate the binary data. For example, you might be using Microsoft Office Suite to edit Word and Excel files.

The biggest drawback of storing binary data as a physical file is that your database easily can lose track of the binary data. Say, for example, you have a column that stores URLs of product images. If you move your application from one server to another, you must manually ensure that physical files representing product images are shipped along with the rest of the application. Also, any deletions to the database records also must be played on the physical image files.

Storing BLOBs directly inside SQL server provides the following advantages:

  1. Because BLOBs and related data are always together, there is no possibility of mismatch.
  2. BLOBs are transferred from one server to another along with the usual data transfer process. There is no need for any manual efforts to keep them in sync.
  3. BLOB data is also backed up along with routine SQL Server data.
  4. Applications, such as Content management Systems and Photo Albums, are tightly dependent on the BLOB data they use. Such applications are greatly benefited by storing BLOBs directly in the database.

In summary, depending on the application being developed, you should decide whether storing BLOB data in SQL Server is the way to go. That said, generally CMS systems, photo albums, slide show applications, document management systems, and so forth are good candidates for storing BLOBs inside SQL Server.



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s