Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

FileTable Feature in SQL Server 2012


By:   |   Updated: 2012-04-13   |   Comments (9)   |   Related: More > 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


Last Updated: 2012-04-13


get scripts

next tip button



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.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



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

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

 

 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

VERY GOOD..

THANK YOU...


Wednesday, May 02, 2012 - 6:10:34 PM - paul p Back To Top

One more...

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


Wednesday, May 02, 2012 - 6:05:04 PM - paul p Back To Top

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 02, 2012 - 11:47:37 AM - James Fogel Back To Top

I would leave that whole thing out of this article.


Wednesday, May 02, 2012 - 11:31:47 AM - mv Back To Top

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 02, 2012 - 11:22:24 AM - James Fogel Back To Top

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

Good Posting


Learn more about SQL Server tools