Filestream is a very interesting feature, and an enhancement of FileTable with Filestream is equally exciting. Today in this post, we will learn how to set up the FileTable Environment in SQL Server.

The major advantage of FileTable is it has Windows API compatibility for file data stored within an SQL Server database. In simpler words, FileTables remove a barrier so that SQL Server can be used for the storage and management of unstructured data that are currently residing as files on file servers. Another advantage is that the Windows Application Compatibility for their existing Windows applications enables to see these data as files in the file system. This way, you can use SQL Server to access the data using T-SQL enhancements, and Windows can access the file using its applications. So for the first step, you will need to enable the Filestream feature at the database level in order to use the FileTable.

-- Enable Filestream
EXEC sp_configure filestream_access_level, 2
RECONFIGURE
GO
-- Create Database
CREATE DATABASE FileTableDB
ON PRIMARY
(Name = FileTableDB,
FILENAME = 'D:\FileTable\FTDB.mdf'),
FILEGROUP FTFG CONTAINS FILESTREAM
(NAME = FileTableFS,
FILENAME='D:\FileTable\FS')
LOG ON
(Name = FileTableDBLog,
FILENAME = 'D:\FileTable\FTDBLog.ldf')
WITH FILESTREAM (NON_TRANSACTED_ACCESS = FULL,
DIRECTORY_NAME = N'FileTableDB');
GO

Now, you can run the following code and figure out if FileStream options are enabled at the database level.

-- Check the Filestream Options
SELECT DB_NAME(database_id),
non_transacted_access,
non_transacted_access_desc
FROM sys.database_filestream_options;
GO

You can see the resultset of the above query which returns resultset as the following image shows. As you can see , the file level access is set to 2 (filestream enabled).

Now let us create the filetable in the newly created database.

-- Create FileTable Table
USE FileTableDB
GO
CREATE TABLE FileTableTb AS FileTable
WITH
(FileTable_Directory = 'FileTableTb_Dir');
GO

Now you can select data using a regular select table.

SELECT *
FROM FileTableTb
GO

It will return all the important columns which are related to the file. It will provide details like filesize, archived, file types etc.

You can also see the FileTable in SQL Server Management Studio. Go to Databases >> Newly Created Database (FileTableDB) >> Expand Tables

Here, you will see a new folder which says “FileTables”. When expanded, it gives the name of the newly created FileTableTb.

You can right click on the newly created table and click on “Explore FileTable Directory”. This will open up the folder where the FileTable data will be stored.

When you click on the option, it will open up the following folder in my local machine where the FileTable data will be stored:

\\127.0.0.1\mssqlserver\FileTableDB\FileTableTb_Dir

In tomorrow’s blog post as Part 2, we will go over two methods of inserting the data into this FileTable.

Reference : Pinal Dave (http://blog.sqlauthority.com)

Filed under: PostADay, SQL, SQL Authority, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology Tagged: Filestream