Problem solve Get help with specific problems with your technologies, process and projects.

Avoid using BLOBs to store image data in SQL Server databases

Storing images in SQL Server with BLOBs can cause problems.

Binary large objects, or BLOBs, are freeform data objects in SQL Server that can be put to a variety of uses. One possible application is using BLOBs to store image data. For instance, a Web site with data records pertaining to a particular item could have a table of BLOBs that contain images of items. Some programmers are attracted to the idea of being able to store everything in the database in this fashion, for the sake of convenience and portability. As elegant an idea as it may sound, it's simply not a very good one, and there are several reasons for this.

First, devoting that much additional database processing power and memory bandwidth to shuttling images back and forth eats into the overall performance of the database. Images do not have to be stored in the database, but the regular data that makes up the database does; the more room there is for what the database really should be doing, the better.

Second, there is already a very powerful, hierarchical database in every installation of Windows Server, designed expressly for storing and copying out masses of data in bulk — especially image data. It's called the NTFS file system. Windows already has a good deal of system resources devoted to caching and managing the file system, so to not take advantage of it is senseless. When set up with proper permissions and security, the existing file system is a perfectly legitimate way to store images used in conjunction with a database. Don't try to redo the work of the file system.

Third, storing large amounts of binary data in SQL Server creates physical database management problems and wastes space. SQL Server allocates data in 8K pages, whereas NTFS can work in blocks as small as 512 bytes. Storing a great many images in a SQL Server database produces that much more wasted space. SQL Server can store a chunk of binary data less than 64 bytes as part of the root structure of the B-tree that makes up the database, but there are very few images that are less than 64 bytes! What's more, backing up and restoring a monolithic database that can grow to many gigabytes in size is much more problematic and time-consuming than incrementally backing up individual files.

Serdar Yegulalp is the editor of the Windows 2000 Power Users Newsletter. Check out his Windows 2000 blog for his latest advice and musings on the world of Windows network administrators – please share your thoughts as well!

Dig Deeper on Windows client management