SQL SERVER – Working with FileTables in SQL Server 2012 – Part 1 – Setting Up Environment

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
-- Create Database
(Name = FileTableDB,
FILENAME = 'D:\FileTable\FTDB.mdf'),
(NAME = FileTableFS,
(Name = FileTableDBLog,
FILENAME = 'D:\FileTable\FTDBLog.ldf')

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),
FROM sys.database_filestream_options;

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).

SQL SERVER - Working with FileTables in SQL Server 2012 - Part 1 - Setting Up Environment filetable1

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

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

Now you can select data using a regular select table.

FROM FileTableTb

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.

SQL SERVER - Working with FileTables in SQL Server 2012 - Part 1 - Setting Up Environment filetable2

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.

SQL SERVER - Working with FileTables in SQL Server 2012 - Part 1 - Setting Up Environment filetable3

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


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

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

Previous Post
SQLAuthority News – Social Media Series – LinkedIn and Professional Profile
Next Post
SQL SERVER – Working with FileTables in SQL Server 2012 – Part 2 – Methods to Insert Data Into Table

Related Posts

17 Comments. Leave new

  • HI Pinal,

    i had enabled filestream option
    o/u:Configuration option ‘filestream access level’ changed from 2 to 2. Run the RECONFIGURE statement to install.

    but i m trying to execute 2 step creating db

    i m getting below error:
    Msg 5591, Level 16, State 1, Line 1
    FILESTREAM feature is disabled. can you please suggest me.

  • Hi Sir,

    FILESTREAM feature is disabled.

    i find it how to enable this one in MSDN
    pelase follow below

    In the SQL Server Configuration Manager snap-in, locate the instance of SQL Server on which you want to enable FILESTREAM.

    Right-click the instance, and then click Properties.

    In the SQL Server Properties dialog box, click the FILESTREAM tab.

    Select the Enable FILESTREAM for Transact-SQL access check box.

    If you want to read and write FILESTREAM data from Windows, click Enable FILESTREAM for file I/O streaming access. Enter the name of the Windows share in the Windows Share Name box.

    If remote clients must access the FILESTREAM data that is stored on this share, select Allow remote clients to have streaming access to FILESTREAM data.

    Any thing wrong please correct me.

  • 1.In Newly created table i had clicked on Explore file table Directory in system it showin message “THIS FOLDER IS EMPTY”

    2. after database created

    File Table DB 2 Full

    What is mean by 2 and FULL
    and other databases are showing 0 why ? is there any option 1 also.
    please expline bit more.

  • Sorry sir, I not get very clear from the description and where could I use it for any useful purpose. Could you please provide and useful scenario with any example and also a smooth step wise desction how to setup this feature enabled in sql server?

  • Is the inverse possible? I.e. store the files on the NTFS and expose it through SQL Server (like remote blob storage), or MUST the files actually be stored in SQL?

  • when i write query as —>

    SELECT DB_NAME(database_id), non_transacted_access, non_transacted_access_desc
    FROM sys.database_filestream_options;


    select * from sys.filetables

    it returns error

    Msg 208, Level 16, State 1, Line 1
    Invalid object name ‘sys.database_filestream_options’.

    Msg 208, Level 16, State 1, Line 2
    Invalid object name ‘sys.filetables’.

  • Hi,
    This is awesome information abt new feat in SQL SERVER 2012.But just curious to know if any real time application can you let me know…..


  • Would Like to know where the Index is getting stored when Full-text indexing is enabled for Filetable data-type

  • Awashesh Pandey
    December 20, 2012 2:15 am


    How to use filestream with SQL Server 2008

  • Juan Carballo
    May 11, 2013 8:48 pm

    Excellent place! Thank Pinal!!!!!

    My name is Juan. I love SQL, but my knowledge is about Microsoft Access. Is possible create a link odbc Access with SQL File Table technology? I need to add and read documents (PDF, Word, Excel, Videos, Photos, etc).

    Please, give me some advice.

  • deepak gupta
    May 29, 2013 9:35 pm

    Sir, Me having the setup of windows Server 2012 and SQL 2012. using file streaming.

    I want to do the Sql mirroring on BACKUP Server. I want to know the proceedure.Please help me in this regard.
    warm regards./deepak

  • when i used filetable to create a folder and the folder have over 390GB data with heavy access. The folder will not show up sometimes and I need to restart the SQL Server. Have you seen this issue?

  • I want this table(FileTable ) by a foreign key relationship with another table

  • 7thsunsoftware
    June 23, 2016 3:12 am

    It was unclear to me whether the actual data inside the file is stored in the file system, or inside sql server? I’m assuming in the file system, and what is inside sql server is just the metadata about the filesystem file. Is that correct?

  • Hi I am getting the error when i execute the script
    SELECT 1
    WHERE TABLE_NAME = N’SpendIntegration_FileStorage’
    CREATE TABLE SpendIntegration_FileStorage AS FILETABLE WITH(FILETABLE_DIRECTORY = ‘SpendIntegrationFiles’, FILETABLE_COLLATE_FILENAME = database_default);

    Error is

    Msg 33405, Level 16, State 12, Line 8
    An error occurred during the create table operation on a FileTable object. (HRESULT = ‘0x80070490’).

    I have filestream enable on the server, and other dbs are working fine.

    Could you please help me out ?



Leave a Reply