For an instructor lead, in-depth look at learning SQL click below.
Filestream Filegroups allow SQL Server to store and manage unstructured data objects, such as images and text documents, as part of a database. This makes possible for SQL Server to leverage the rich streaming capabilities of win32 for these unstructured data while retaining transactional consistency between the unstructured data and corresponding structured data.
How to Enable FILESTREAM
To get started, we first need to enable the FILESTREAM feature in SQL Server. You can use the following SQL Server Configuration Manager settings:
|
1 2 3 4 |
EXEC sys.sp_configure 'filestream access level', 2 RECONFIGURE |
Creating a Filegroup for FILESTREAM
Once you’ve enabled FILESTREAM, the next step is to create a database with a FILESTREAM filegroup where you will be storing your unstructured file objects. Here’s a code example:
|
1 2 3 4 5 6 7 8 9 10 |
CREATE DATABASE FileStreamDB ON PRIMARY ( NAME = Main, FILENAME = 'c:\data\main.mdf'), FILEGROUP FileStreamGroup1 CONTAINS FILESTREAM(NAME = FSGroup1, FILENAME = 'c:\data\filestream1') LOG ON ( NAME = Archlog, FILENAME = 'c:\data\archlog.ldf') |
Creating a Table to Store FILESTREAM Data
After creating a Filestream filegroup, the next step is to create a table that will store your FILESTREAM data. The table should contain a column of type varbinary(max) with the FILESTREAM attribute. This column will hold the actual file data:
|
1 2 3 4 5 6 |
CREATE TABLE DocumentStore( [DocID] [uniqueidentifier] ROWGUIDCOL NOT NULL UNIQUE, [DocTitle] [nvarchar](50), [Document] [varbinary](max) FILESTREAM) |
Inserting Data into a FILESTREAM Table
To insert data into the FILESTREAM table, you can use standard Transact-SQL statements. Below is an example:
|
1 2 3 4 5 |
DECLARE @File AS varbinary(max) = (SELECT * FROM OPENROWSET(BULK N'C:\Photos\vacation.jpg', SINGLE_BLOB) AS Document) INSERT INTO DocumentStore VALUES (NEWID (), 'Vacation photo', @File) |
Retrieving FILESTREAM Data
FILESTREAM data can be retrieved using a SELECT statement:
|
1 2 3 |
SELECT * FROM DocumentStore WHERE DocTitle='Vacation photo' |
In conclusion, SQL Server’s FileStream Filegroups offer an effective way to store and manage unstructured data. By integrating these unstructured data objects with your structured data, you can create more comprehensive and efficient databases.