A view on storing file data in SQL Server

Storing file data (BLOBs) (eg. pdf, word, excel ...) in SQL server always has been some kind of dark magic to a lot of people.

Before SQL Server 2008 we could store the BLOB data in VARBINARY(MAX) datatypes, in which you also had to convert the BLOB data into the binary format yourself ...


With SQL 2008, the FILESTREAM datatype was introduced. FILESTREAM enables us to integrate SQL Server with the NTFS file system ... BLOBs are stored as real files on the file system.

More on FILESTREAM: http://msdn.microsoft.com/en-us/library/bb933993.aspx

--Enable FILESTREAM

EXEC sp_filestream_configure @enable_level = 3;

--CREATE THE TABLE

CREATE TABLE TestFS

      (

            ID INT IDENTITY(1,1),

            FileName NVARCHAR(200),

            Content VARBINARY(MAX) FILESTREAM

      )

As from the new version of SQL Server, SQL 2012 (aka 'Denali'), a new feature has been introduced ... FILETABLE, based on the existing FILESTREAM feature in SQL Server. In fact, the FILETABLE feature is nothing more than storing files in special filetables in SQL server, but you will be able to access them from the file system. It is said that FILETABLE will be the next generation of FILESTREAM ...

More on FILETABLE: http://msdn.microsoft.com/en-us/library/ff929144(v=sql.110).aspx

--Create the table

CREATE TABLE DocumentStore AS FILETABLE

    WITH (

          FileTable_Directory = 'DocumentTable'

          FileTable_Collate_Filename = database_default

         );

GO
Have fun
Jurgen