For an instructor lead, in-depth look at learning SQL click below.
**
Introduction
In SQL Server, Filestream and FileTable were designed to deliver the best of SQL Server and Windows to store and manage documents and unstructured data. The rest of this post will take you through the process of working with them concurrently.
Getting Started with “Filestream”
SQL FILESTREAM feature allows storing of BLOBs (Binary Large OBjects) such as documents, images, videos, etc. directly to the NTFS file system. This largely eliminates the need for an extra layer to handle these types of data. Here is a quick example:
|
1 2 3 4 5 |
-- Enable Filestream EXEC sp_configure filestream_access_level, 2 RECONFIGURE |
Create a Database that Supports Filestream
|
1 2 3 4 5 6 7 8 9 10 11 12 |
CREATE DATABASE fsDB ON PRIMARY ( NAME = fsDB_dat, FILENAME = 'c:\data\fsDB.mdf'), FILEGROUP fsDB_FG1 CONTAINS FILESTREAM( NAME = fsDB_fg1_fs, FILENAME = 'c:\data\fsDB_fs') LOG ON ( NAME = fsDB_log, FILENAME = 'c:\data\fsDB.ldf') WITH FILESTREAM( NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N'fsDB_DIR') |
Working with “FileTable”
FileTable is a feature introduced in SQL Server 2012, which is built on the top of FILESTREAM technology. This makes it easier and more efficient to manage, access, and share file directories. Let’s start by enabling it:
|
1 2 3 4 |
-- Enable the t-sql command to allow the creation of file tables EXEC sp_filestream_configure_database @database_name = 'fsDB', @enable_level = 3; |
Creating a FileTable
|
1 2 3 4 5 |
-- Create a FileTable USE fsDB CREATE TABLE MyFileTable AS FileTable |
In Conclusion
Working with Filestream and FileTable together makes it possible to combine the benefits of SQL Server with those of the Windows operating system. This gentle introduction should serve as a starting point for using these tools to manage your unstructured data. But, of course, these features have more to offer, depending on your specific needs.
`