FileTable Feature in SQL Server 2012

By:   |   Comments (9)   |   Related: > FILESTREAM


Problem

The FileTable feature of SQL Server 2012 is an enhancement to the FILESTREAM feature which was introduced in SQL Server 2008. In this tip we will take a look at how to use FileTable feature of SQL Server 2012.

Solution

A FileTable is a new user table which gets created within a FILESTREAM enabled database. Using the FileTable feature, organizations can now store files and documents within a special table in SQL Server and they will have the ability to access those files and documents from windows. When you use this feature it will appear to you as if the files and documents are residing on a file system rather than in SQL Server. However, in order to use the FileTable feature you need to enable the FILESTREAM feature on the instance of SQL Server 2012. Follow the steps mentioned in this tip, Different ways to enable FILESTREAM feature of SQL Server 2008, to enable the FILESTREAM feature on SQL Server 2012. Database administrators can define indexes, constraints and triggers; however the columns and system defined constrains cannot be altered or dropped. Also, in order to enable the FILESTREAM feature you need to be a member of the SYSADMIN or SERVERADMIN fixed server roles.

If you are new to FILESTREAM then I would recommend you read the below tips to learn more about the FILESTREAM feature.

Steps to Setup

1. Execute the below mentioned TSQL code to enabling the XP_CMDSHELL feature on SQL Server 2012. Once XP_CMDSHELL feature is enabled it will create a folder on the C: drive to store the FILESTREAM data (note: you can use any drive, but I am using the C: drive for this example).

USE master
GO
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'xp_cmdshell', 1;
GO
RECONFIGURE;
GO
EXEC xp_cmdshell 'IF NOT EXIST C:\DemoFileTable MKDIR C:\DemoFileTable';
GO

2. Create a database named DemoFileTable which uses the FILESTREAM feature for the purpose of the demo using the below mentioned TSQL code. In the below script you can see that we are specifying new options for the FILESTREAM clause i.e. "NON_TRANSACTED_ACCESS = FULL" and we have also provided the windows directory name "DemoFileTable" which we created in the previous step.

IF EXISTS (SELECT 1 FROM sys.databases WHERE name = 'DemoFileTable') BEGIN
ALTER DATABASE DemoFileTable SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE DemoFileTable;
END;
CREATE DATABASE DemoFileTable
WITH FILESTREAM
( 
NON_TRANSACTED_ACCESS = FULL,
DIRECTORY_NAME = N'DemoFileTable'
);
GO
/* Add a FileGroup that can be used for FILESTREAM */
ALTER DATABASE DemoFileTable
ADD FILEGROUP DemoFileTable_FG
CONTAINS FILESTREAM;
GO
/* Add the folder that needs to be used for the FILESTREAM filegroup. */
ALTER DATABASE DemoFileTable
ADD FILE
(
NAME= 'DemoFileTable_File',
FILENAME = 'C:\DemoFileTable\DemoFileTable_File'
)
TO FILEGROUP DemoFileTable_FG;
GO

3. Next will be to Create a FileTable within FILESTREAM enabled database. This can be done by executing the below mentioned TSQL script which will create a FileTable within the FILESTREAM enabled database. The name of the FileTable is DemoFileTable and you need to specify FILETABLE_DIRECTORY as DemoFileTableFiles and FILETABLE_COLLATE_FILENAME as database_default

USE DemoFileTable;
GO
/* Create a FileTable */
CREATE TABLE DemoFileTable AS FILETABLE
WITH
( 
FILETABLE_DIRECTORY = 'DemoFileTableFiles',
FILETABLE_COLLATE_FILENAME = database_default
);
GO

4. Once the FileTable is created successfully, in Object Explorer > Expand Databases > Expand DemoFileTable database > Expand Tables > Expand FileTables > Expand dbo.DemoFileTable > Expand Columns to view the structure of FileTable as shown below.

 Expand Columns to view the structure of FileTable.

5. In the below snippet you can see the files which were created within the C:\DemoFileTable\DemoFileTable_File folder when the FILESTREAM enabled database is created along with the FileTable DemoFileTableFiles. The filestream.hdr is a very important system file which basically contains FILESTREAM header information. Database Administrators need to make sure that this file is not removed or modified as this will corrupt the FILESTREAM enabled database.

make sure that this file is not removed or modified by any chance as this will corrupt the FILESTREAM enabled database.

6. Once the FileTable is created successfully you can access the FileTable using Windows Explorer. The path to access the FileTable will be: 
\\SERVERNAME\FILESTREAM_WINDOWS_SHARE_NAME\FILESTREAM_TABLE_NAME\FILETABLE_DIRECTORY\

Copying Documents and Files to the FileTable

Now that we have created a FILESTREAM enabled database and a FileTable the next step will be to copy the documents and files to the newly created FileTable in Windows Explorer. You can copy the files by dragging files or by using the Copy-and-Paste operation to the below mentioned location.

\\SERVERNAME\FILESTREAM_WINDOWS_SHARE_NAME\FILESTREAM_TABLE_NAME\FILETABLE_DIRECTORY\

In the below snippet you can see that I have copied MSSQLTIPS.gif logo to FileTable folder. To open the image file double click the MSSQLTips.gif file and it will open in Internet Explorer.

Copying Documents and Files to the FileTable

How to View Documents and Files Stored in FileTable Using SQL Server Management Studio

To view the files and documents stored in a FileTable execute the below mentioned TSQL code.

Use DemoFileTable;
GO
SELECT * FROM DemoFileTable;
GO

To view the file and documents stored in a FileTable execute the below mentioned TSQL code.

Finally disable the XP_CMDSHELL feature which was enabled for this demo by executing the below mentioned TSQL code.

USE master
GO
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'xp_cmdshell', 0;
GO
RECONFIGURE;
GO
Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Ashish Kumar Mehta Ashish Kumar Mehta has been contributing to the MSSQLTips.com community since 2009 with over 60 tips.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Monday, June 11, 2018 - 1:33:01 PM - Daniel Back To Top (76180)

My problem is as follows:

When I already have files in the DemoFileTable folder, I do a select, update, insert, delete everything works well, At first I can go to explore the filetable directory without problem, paste files, delete, etc. I turned off my Notebook and I lost the connection to the database, since then it is is impossible to enter the database.

I get this error: The File location cannot be opened. Either access is not enabled or you do not have permissions.


Tuesday, January 23, 2018 - 4:37:12 AM - Serdar BALA Back To Top (75014)

 

 very usefull for me 

about 6 hours work on one db contains filetable table 

 

but this sql spent one secont this job


Wednesday, September 27, 2017 - 7:29:40 AM - arash Back To Top (66651)

VERY GOOD..

THANK YOU...


Wednesday, May 2, 2012 - 6:10:34 PM - paul p Back To Top (17243)

One more...

Can other tables be added to the DemoFileTable database at a later point?


Wednesday, May 2, 2012 - 6:05:04 PM - paul p Back To Top (17242)

Thanks for the information. A few questions:

  1. Can the drive being used for the DemoFileTable be a mounted drive (from a SAN) or must it be a local drive on the SQL Server machine?
  2. Can the directory containing the DemoFileTable be "shared" (multiple writers from many other machines)? Will file locks & collisions be handled by Windows?
  3. Can you use a shared SAN drive directly? Instead of FILENAME = 'C:\DemoFileTable\DemoFileTable_File', can I use FILENAME = '\\MySanServer\MyShare\MyDirectory'?

Actually, #3 is the main question. #1 and #2 are answered if #3 is true.


Wednesday, May 2, 2012 - 11:47:37 AM - James Fogel Back To Top (17236)

I would leave that whole thing out of this article.


Wednesday, May 2, 2012 - 11:31:47 AM - mv Back To Top (17235)

he only used xp_cmdshell to make a folder to put the files  in  - he could have done that using windows explorer and never turned xp_cmdshell on.


Wednesday, May 2, 2012 - 11:22:24 AM - James Fogel Back To Top (17234)

Isn't the use of xp_cmdshell a big security hole here? I get a lot of use out of it but I know most organizations would freak out at activating this. Is it the only way for SQL Server to get a file in to the database?


Tuesday, April 17, 2012 - 9:30:17 AM - Rajasekhar Back To Top (16970)

Good Posting















get free sql tips
agree to terms