early all applications require some sort of data integration—at minimum, retrieving some data and displaying it in the user interface. Usually, an application uses a combination of structured and unstructured data, which introduces a number of challenges. Often, you have to create, update, delete, and read these disparate data types within a single transaction—and that's difficult when the structured data resides in a relational database but the unstructured data resides in a file system. SQL Server 2008's new FILESTREAM
feature helps solve this disjunct data problem by letting you store unstructured data in the file system while still preserving the transactional integrity of that data. This article explores the FILESTREAM
feature and showcases how you can leverage it to gain more control over your own unstructured data.
Unstructured Data Options
With SQL Server 2005, when building an application that relies on both structured (relational) data and unstructured (non-relational) data, you had a couple of options:
- Store the relational data in the database and the non-relational data in a dedicated storage such as file systems and file servers. Although this approach can be cost-effective, it introduces additional complexity, because you need to manage the transactional integrity across both relational and non-relational systems.
- Store both relational and non-relational data in the database. For many years, databases have supported storage of non-relational data as Binary Large Objects, or BLOBs. SQL Server calls this data type a varbinary column. Although storing such data in the database is convenient, it typically comes at a higher cost (in disk space and storage/retrieval time) and can negatively impact the overall performance of the application.
However, with SQL Server 2008, the new FILESTREAM
attribute combines with a varbinary
column, so you can store the actual data on the server's file system while managing and accessing it within the context of the database. The feature also gives SQL Server the capability of maintaining the integrity not only between records in the database but also between database records and external files associated with those records. Because this feature is implemented as just a new attribute of the existing varbinary(max)
data type, developers can easily take advantage of FILESTREAM
without having to completely re-architect their applications.
When to Use FILESTREAM
You should consider using FILESTREAM
when any of the following conditions are true:
- When you are storing BLOBs with an average size of 1 MB or more
- When you need fast, read-only access to the data from your application
- When you want to access the BLOBs directly from your application's middle tier code
- When you need to store the non-relational data and relational data in the database in the context of a single database transaction