Configuring File Tables

Configuring File Tables

     FileTables are a special type of table that enables you to store files and documents within SQLServer 2012. These files and documents can be accessed from Windows applications as though they were stored normally in the file system. For example, you can add files and folders

to the FileTable by dragging and dropping them in Windows Explorer. You can remove them from the FileTable by using the same method.

    A FileTable provides the following functionality:

■■ A FileTable provides a hierarchy of files and directories.

■■ Each row in a FileTable represents a file or directory.

■■ Each row holds the following items:

■A FILESTREAM column for stream data and a file_id (GUIDE) identifier.

■ Path_locator and parent_path_locator columns. These represent the file and directory

hierarchy.

■Ten file attributes. These include creation data and modification date.

■ Type column that supports full-text and semantic search.

■You can update FileTables by using normal Transact-SQL queries.

To enable FileTables, perform the following steps:

1. Enable FILESTREAM at the instance level. You can do this with the following query:

EXEC sp_configure filestream_access_level, 2

RECONFIGURE

2.Enable Non-Transnational Access at the database level. You can do this when creating a new database by using the CREATE DATABASE statement and the FILESTREAM

NON_TRANSACTED_ACCESS option.

 For example:

CREATE DATABASE database_name

WITH FILESTREAM ( NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N'dir_name')

You can do this for an existing database by using the ALTER DATABASE statement withSET FILESTREAM option. 

For example:

ALTER DATABASE database_name

SET FILESTREAM (NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N'directory_name')

3. Specify a Directory for FileTables at the database level if you haven’t already done so

when configuring Non-Transactional Access. You can modify the directory name by using the ALTER DATABASE statement with the SET FILESTREAM option.

  

4.To create a FileTable by using SQL Server Management Studio, right-click the Tables node and choose New FileTable to open a new script window that contains a template Transact-SQL script that you can modify. You can also create a FileTable by using the CREATE TABLE statement with the AS FileTable option. For example, to create a new FileTable named Doc Store,

use the following query:

CREATE TABLE DocStore as FileTable

Go

           File Table tables have predefined and fixed schema, so it is not possible to add or change columns. It is possible to add custom indexes, triggers, and constraints to a File Table. Dropping a File Table also drops the directory and the sub directories that it contained.