BeyeBLOGS | BeyeBLOGS Home | Get Your Own Blog

« SQL Server 2008 R2 Training Kit | Main | Key points on using Filestream attribute »

April 18, 2010

Key points on using Filestream attribute

According to this study from Microsoft Research, SQL Server handles BLOBs smaller than 256KB more efficiently than a file system, while NTFS is more efficient for BLOBS larger than 1MB. SQL Server 2008 introduced the Filestream storage attribute for varbinary(max) data type to store data in files.

Here are the steps to begin using this feature:
  • Enable Filestream storage at an operating system level using SQL Server Configuration Manager (system administrator). Then enable it on a SQL Server instance level using sp_configure (database administrator). See specific steps here or here.

  • Define a database filegroup that ties an NTFS file system location to a SQL Server database. It must be a local file system location.

  • Add a varbinary(max) column to a table definition with the FILESTREAM property. Add a uniqueidentifier column with the ROWGUIDCOL property (required).

  • Read and write the data through the stream-based APIs to get the performance advantage attributed to the Filestream property. In .NET 3.5 SP1 such support is provided by the SqlFileStream class.


    • Example of column definitions:

      Photo VARBINARY(max) FILESTREAM NULL,
      RowId UNIQUEIDENTIFIER NOT NULL ROWGUIDCOL UNIQUE DEFAULT NEWID()


    Reference:
    Bob Beauchemin, Programming with FileStreams in SQL Server 2008, MSDN Magazine

    Posted by Steve Mann at April 18, 2010 4:38 PM

    Comments

    Post a comment




    Remember Me?