There have been many a times when designing my database, I have faced this question. Should I store my files\images as BLOB or should I store it on the file system and keep a reference in the database. There has never been an easy answer to this question. Till now that is; With the FILESTREAM feature of SQL Server, the answer is “It does not matter”.
By using the FILESTREAM feature SQL Server 2008, you can store your files in the database and configure SQL Server to internally store it on the file system. This means you can make use of the large storage capacity of your file system without the overhead of having to store the path of the file in the database. In the next couple of blogs, I will try and show you how to work with the FILESTREAM feature.
To use this feature, you have to enable FILESTREAM on the SQL Server and the individual database. You can use SQL Server Configuration Services to enable FILESTREAM on your SQL Server. Go to the properties of the SQL Server instance you want to enable the feature on and go to the FILESTREAM tab. The rest of it should be self-explanatory. Enable the feature as shown below.
With that done, we will now go on to configure FILESTREAM on the database. We do that with the following script
EXEC sp_configure 'filestream access level', '2'
RECONFIGURE
GO
The script configures FILESTREAM access level to 2 which means you can access files with both T-SQL or streaming I/O. If you are sure you will access FILESTREAM only with T-SQL, you can set the access level to 1. Setting it to 0 disables the FILESTREAM. There is also a level ‘3’ that you use if you need Remote Access.
The next step is to indicate to the database, which folder we want SQL Server to use to store the files. To do this, you first create a SQL Server Filegroup with the following script
ALTER DATABASE TestDatabase ADD FILEGROUP TestFileGroup CONTAINS FILESTREAM
GO
What is remaining is to configure the path in which the files for the above filegroup will be stored. You can use the below script to do the same.
ALTER DATABASE TestDatabase
ADD FILE
(
NAME = FileFolder,
FILENAME = 'C:\FileStream\FileFolder'
)
TO FILEGROUP TestFileGroup
GO
where C:\FileStream should be an existing folder.
If you navigate to C:\FileStream you will see that FileFolder has now been created. With the above steps, your database is now ready to use the FileStream.
With all the configuration now done, the only remaining step is to create the table to store the data for the files. I have created the table with the script below
CREATE TABLE ProductPhotoWithFilestream(
[ProductPhotoID] [int] IDENTITY(1,1) NOT NULL,
[LargePhoto] varbinary(max) FILESTREAM NULL,
[RowGuid] UNIQUEIDENTIFIER NOT NULL ROWGUIDCOL UNIQUE DEFAULT NEWID(),
CONSTRAINT [PK_ProductPhotoWithFilestream_ProductPhotoID] PRIMARY KEY CLUSTERED
(
[ProductPhotoID] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Notice how, in the third line of the script you indicate a FILESTREAM column. You declare a FILESTREAM attribute on a varbinary(max) column. It is important to understand that FILESTREAM is not entirely a SQL Datatype. It is just an attribute that can be applied on the varbinary(max) column.
The other thing worth mentioning in the script above is the use of the RowGUID. This is a pre-requisite for using FILESTREAM. The table on which you enable FILESTREAM should have a GUID column.
Since, this post is now getting a bit lengthy, I will break now. In Part-II, I will show you how to use this feature from a .NET application.
