One of the great features inSQL 2008is it'sFILESTEAMsupport. To make things short, we can now not only store the path to the file in our database, but we can now store the whole file inside the SQL Server and than backup, restore and manage it just as we would do with any other data.
In this post series I will build a file management database withSQL 2008 FILESREAMand build a .net client application that works with it.
1. Enable FILESTREAM support
To enable FILESTREAM support, run the following sql command
exec[sp_filestream_configure] @enable_level = 3;
This command enables or disables the support according to the parameter. The value 3 means that FILESTREAM will be enabled for Transact-SQL, local file system access, and remote file system access.
You can find more details aboutsp_filestream_configurehere.
2. Create a database with a File Group that contains FILESTREAM
To create a database instance with a file group that contains FILESTREAM, run the following sql command. This creates the database with 3 file groups, but only one of them contains FILESTREAM as you can see in the command.
FILEGROUP FileStreamGroupCONTAINSFILESTREAM (
LOG ON(NAME= FileManagement_Log,
This command will create the following directories structure:
In the Data Directory, I could find the files specified above:
and in the FileManagement directory, I could find the following content:
3. Create a Table with FILESTREAM
Note: FILESTREAM is not a type of a column, but it is a property you put on a varbinary(max) column.
So, in order to create a table with a varbinary(max) column that will be used for FILESTREAM, run the following command. Note that I don't have any additional columns to the file itself.
FileIDuniqueidentifier NOT NULL ROWGUIDCOL PRIMARY KEY,
FileContentsvarbinary(max) FILESTREAMDEFAULT NULL
4. Add Test Data
In order to get the feeling of the experience of working with files, let add an empty file:
If we now query the database to see the files in it:
we will get the following result (the GUID will probably be different...)
In this post I showed how to create anSQL Server 2008database withFILESTREAMsupport and add some text data into it. In the next post I will show how to write a .net client application that works with this database to add and change files.