on how to import and export unstructured data in SQL Server 2005 by using the "image" data type.
In this article I will show how you can manipulate unstructured data in SQL Server 2008 or later by using the
feature. FILESTREAM allows storing unstructured data (i.e. music, video, documents, etc.) onto the NTFS file system and manipulating it via the Database Engine.
Step 2: Creating a FILESTREAM-Enabled Database
For creating a FILESTREAM-enabled database you just need to include a FILESTREAM filegroup. For example:
CREATE DATABASE FileStreamDB
PRIMARY ( NAME = FileStreamDBData,
FILENAME = 'C:\Blog\SQLData\filestreamDB_data.mdf'),
FILEGROUP FileStreamGroup_1 CONTAINS FILESTREAM( NAME = FileStreamDBFS,
FILENAME = 'C:\Blog\SQLData\filestream1')
LOG ON ( NAME = FileStreamDBLogs,
FILENAME = 'C:\Blog\SQLData\filestreamDB_log.ldf');
Step 3: Creating a Table for Storing FileStream Data
The only difference between a "normal" table and a table that can store filestream data is the use of the "FILESTREAM" data type for a specific column in the table's definition script:
CREATE TABLE dbo.Files
[Id] [uniqueidentifier] ROWGUIDCOL NOT NULL UNIQUE,
[ActualFile] VARBINARY(MAX) FILESTREAM NULL
Step 4: Storing FileStream Data
For this example, consider the following unstructured data file:
|Figure 2: Image file to be stored in FILESTREAM-enabled database.|
Now, let's store the file in our FILESTREAM-enabled database and table that was created earlier:
INSERT INTO dbo.Files
VALUES (newid (),
(SELECT * FROM OPENROWSET(BULK 'C:\Testing\2\SampleImage.png',SINGLE_BLOB) AS x)
Here's the contents of the table:
|Figure 3: The contents of the FILESTREAM-enabled table after inserting unstructured data (image file).|
As you can see, the file is visible on the file system level too:
Now let's try to open the file using MS Paint:
|Figure 4: Binary file stored using FILESTREAM - Accessible on the file system level.|
|Figure 5: Accessing the data stored in the FILESTREAM-enabled database from the file system level (Windows).|
As you can see, the image file is stored in the SQL Server database table but besides T-SQL access, you can also access it from Windows!
What we just did with the above example, shows a small glimpse of the real power of FILESTREAM, that is leveraging the performance and rich APIs of the Windows file system and at the same time maintaining consistency between structured and unstructured data in SQL Server.
FILESTREAM actually works like a bridge between structured and unstructured data via a combination of transactional data and file system access and can be extremely useful in cases where you have many binary objects like images and videos and you want to store it in SQL Server and being able to access it with the speed of the file system.