Once FILESTREAM is enabled for both the machine and the server
instance, any database running on the server instance can support
unstructured data by defining a filegroup with the special FILEGROUP…CONTAINS FILESTREAM clause of the CREATE DATABASE statement. For example, the statement in Example 1 creates a PhotoLibrary database that can store pictures using FILESTREAM. Before you can run this code, you need to create an empty folder named C:\Demo\PhotoLibrary.
Example 1. Creating a FILESTREAM-enabled database with FILEGROUP…CONTAINS FILESTREAM.
CREATE DATABASE PhotoLibrary
(NAME = PhotoLibrary_data,
FILENAME = 'C:\Demo\PhotoLibrary\PhotoLibrary_data.mdf'),
FILEGROUP FileStreamGroup1 CONTAINS FILESTREAM
(NAME = PhotoLibrary_group2,
FILENAME = 'C:\Demo\PhotoLibrary\Photos')
(NAME = PhotoLibrary_log,
FILENAME = 'C:\Demo\PhotoLibrary\PhotoLibrary_log.ldf')
The FILEGROUP…CONTAINS FILESTREAM clause in this otherwise ordinary CREATE DATABASE statement enables the FILESTREAM feature for the PhotoLibrary database.
when creating any database, the directory (or directories) specified
for the primary and log filegroups must exist at the time the database
is created. In Example 1, the CREATE DATABASE statement will fail if the C:\Demo\PhotoLibrary directory specified by FILENAME in its ON PRIMARY and LOG ON clauses does not exist. For the new FILESTREAM group specified by the FILEGROUP…CONTAINS FILESTREAM clause, a FILENAME keyword is specified pointing to a directory (not a file) that must not exist at the time that the database is created (although the path leading up to the final directory must exist), or the CREATE DATABASE statement will fail as well. SQL Server takes control of creating and managing this directory—called the FILESTREAM data container—much as it does for creating and managing the .mdf and .ldf files in the other filegroups. In Example 1, SQL Server automatically creates the C:\Demo\PhotoLibrary\Photos folder when the CREATE DATABASE statement is executed, and it will use that folder for storing all BLOB data (photos, in this example) in the PhotoLibrary database.
Execute the code in Example 1
to create the database. SQL Server creates the usual .mdf and .ldf
files for you, and also creates the Photos subdirectory for the FILESTREAM group, as shown in Figure 1.
Figure 1. FILESTREAM storage in the file system.
the scenes, SQL Server will store all your pictures as files in the
Photos subdirectory, and will transparently associate those files and
the relational tables that they logically belong to in columns defined
as varbinary(max) FILESTREAM. Unless you explicitly exclude the FileStreamGroup1
filegroup from a backup or restore command, all your picture files in
the Photos subdirectory will be included with the relational database
in the backup or restore operation.
You can create multiple FILESTREAM
filegroups, with each one pointing to a different file system location.
Doing so helps to partition BLOB data when you need to scale up,
because you can designate specific filegroups for each varbinary(max) FILESTREAM column you define in your tables.
Creating a Table with FILESTREAM Columns
You’re now ready to create a new PhotoAlbum table. SQL Server requires that any table using FILESTREAM storage has a uniqueidentifier column that is not nullable and specifies the ROWGUIDCOL attribute. You must also create a unique constraint on this column. Only one ROWGUIDCOL column can be defined in any given table, although you can then declare any number of varbinary(max) FILESTREAM columns in the table that you want for storing BLOB data. The statement in Example 2 creates the PhotoAlbum table with a Photo column declared as varbinary(max) FILESTREAM.
Example 2. Creating a FILESTREAM-enabled table.
CREATE TABLE PhotoAlbum(
PhotoId int PRIMARY KEY,
RowId uniqueidentifier ROWGUIDCOL NOT NULL UNIQUE DEFAULT NEWSEQUENTIALID(),
Photo varbinary(max) FILESTREAM DEFAULT(0x))
With this statement, you satisfy the FILESTREAM requirement for the ROWGUIDCOL column, yet you won’t actually have to do anything to maintain that column. By declaring the RowId column with its DEFAULT value set to call the NEWSEQUENTIALID
function, you can just ignore this column when inserting rows—simply
not providing values for it will cause SQL Server to automatically
generate the next available globally unique identifier (GUID) that it
needs to support FILESTREAM on the table. The column is set to not
accept NULL values and is defined with the required unique constraint.
You have also declared an integer PhotoId column for the table’s primary key value. You’ll use the PhotoId column to identify individual photos in the album, and SQL Server will use the RowId column to track and cross-reference photos in the file system with rows in the PhotoAlbum table. The Photo column holds the actual BLOB itself, being defined as a varbinary(max) data type with the FILESTREAM attribute applied. This means that it gets treated like a regular varbinary(max) column, but you know that its BLOB is really being stored under the covers in the file system by SQL Server. The Photo
column is also defined with a default value of 0x, which represents a
zero-length binary stream. This will come into play later when you
start programming with SqlFileStream. We’re not there yet, so you can just ignore the default assignment for now.