.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month

Home >> Articles >> .Net Framework >> Post New Resource Bookmark and Share   

 Subscribe to Articles

Handling BLOBs

Posted By:Shashi Ray       Posted Date: November 27, 2009    Points: 25    Category: .Net Framework    URL: http://www.dotnetspark.com  


Handling BLOBs

Today, many applications need to deal with data formats such as graphic and sound-or even more elaborate data formats, such as video-in addition to more conventional character and numeric data. There are many different types of graphic, sound, and video formats. From a storage perspective, however, they can all be thought of as lumps of binary data, typically referred to as binary large objects, or BLOBs.

SQL Server provides the binary, varbinary, and image data types to store BLOBs. Despite the name, BLOB data can also refer to text-based data. For example, you might want to store an arbitrarily long notes field that can be associated with a particular row. SQL Server provides the ntext and text data types for this purpose.

In general, for binary data less than 8 kilobytes (KB), use the varbinary data type. For binary data exceeding this size, use image. Table 2 highlights the main features of each data type.

Table. Data Type Features

Data type




Ranges from 1 to 8,000 bytes. Storage size is specified length plus 4 bytes.

Fixed-length binary data


Ranges from 1 to 8,000 bytes. Storage size is actual length of supplied data plus 4 bytes.

Variable-length binary data


Variable-length binary data from 0 to 2 gigabytes (GB).

Large-size, variable-length binary data


Variable-length data from 0 to 2 GB.

Character data


Variable-length data from 0 to 2 GB.

Unicode character data

Where to Store BLOB Data

SQL Server 7.0 and later have improved the performance of working with BLOB data stored in the database. One of the reasons for this is that the database page size has increased to 8 KB. As a result, text or image data less than 8 KB no longer needs to be stored in a separate binary tree structure of pages, but can be stored in a single row. This means that reading and writing text, ntext, or image data can be as fast as reading or writing character and binary strings. Beyond 8 KB, a pointer is maintained within the row, and the data itself is held in a binary tree structure of separate data pages-with an inevitable performance impact.

A commonly used alternative approach to handling BLOB data is to store the BLOB data in the file system, and store a pointer (preferably a Uniform Resource Locator [URL] link) in a database column to reference the appropriate file. For versions earlier than SQL Server 7.0, storing BLOB data externally to the database in the file system can improve performance.

However, improved BLOB support in SQL Server 2000, coupled with ADO.NET support for reading and writing BLOB data, makes storing BLOB data in the database a feasible approach.



Shashi Ray


 Subscribe to Articles


Further Readings:


No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More Articles on C#, ASP.Net, Vb.Net, SQL Server and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend