Binary Data in SQL

Erik Porter wonders about using Binary Data in SQL Server 2005. He thinks it’s a good idea, but he’s not sure whether it is worthwhile.

I’ve used binary data with SQL 2000. One way it’s been used is to store images that are to appear in reports. In conjunction with Crystal Reports, there is no easier way to put a company’s logo on a report than to have it stored in the database. It’s simply retrieved like any other data field, and Crystal Reports automatically handles it.

Getting the image data into SQL Server 2000 is not done in an intuitive way with VB6, but it’s well-documented. I wrote an image picker/import/export tool in the same time it takes to write any other straightforward data maintenance form. This does involve writing and reading the image data to the local file system because picture boxes and image controls can easily load images from a file, but don’t have support for directly applying a byte array, which is what you get when you use ADO to pull binary data from the database.

Some other commenters mentioned that file security can be an issue. In .NET, of course, isolated storage could be used for the storing of transient image files. With isolated storage, the developer doesn’t have to worry about security issues, and the user doesn’t have to worry about security issues, either.

Erik also questions performance in the face of a high volume of binary data. I don’t have any experience with high volume or with large size files. We deal with logos that are typically less than 100K in size. And the logos might be retrieved a couple hundred times a day from our database. We certainly haven’t noticed any issues with performance due to the use of binary data.

Advertisements
  1. Leave a comment

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

%d bloggers like this: