How to create a file table on a file stream enabled database?

Problem: How to create a file table on a file stream enabled database?

Solution: Create, Alter, and Drop FileTables Starting SQL Server 2012

SQL Server provides a special table of files, also referred to as a FileTable, for applications that require file and directory storage in the database, with Windows API compatibility and non-transactional access. A FileTable is a specialized user table with a pre-defined schema that stores FILESTREAM data, as well as file and directory hierarchy information and file attributes.

A FileTable provides the following functionality:

·         A FileTable represents a hierarchy of directories and files. It stores data related to all the nodes in that hierarchy, for both directories and the files they contain. This hierarchy starts from a root directory that you specify when you create the FileTable.

·         Every row in a FileTable represents a file or a directory.

·         Every row contains the following items.

o    A file_stream column for stream data and a stream_id (GUID) identifier. (The file_stream column is NULL for a directory.)

o    Both path_locator and parent_path_locator columns for representing and maintaining the file and directory hierarchy.

o    10 file attributes such as created date and modified date that are useful with file I/O APIs.

o    A type column that supports full-text search and semantic search over files and documents.

·         A FileTable enforces certain system-defined constraints and triggers to maintain file namespace semantics.

·         When the database is configured for non-transactional access, the file and directory hierarchy represented in the FileTable is exposed under the FILESTREAM share configured for the SQL Server instance. This provides file system access for Windows applications.

Pre-requisites to setup for creating a file table are as follows:

·         SQL Server should be 2012 or higher.

·         FILESTREAM should be enabled at the Instance level

·         FILESTREAM FILEGROUP should be provided at the database level

·         Enable Non transaction access at the database level

·         Specify directory for FileTable at the database level

To know more about how to create a file stream on a database. Please go through this article. Create File Stream on a Database

Example:

In this Example, I would like to show we can create filetable on a file stream enabled database.

Before creating we would have set the database properties for the file directory to set some value or else filetable will error out saying directory cannot be null. Please see below screenshot.

-- =========================================

-- Create FileTable template

-- =========================================

USE Test

GO

IF OBJECT_ID('dbo.FT_Document', 'U') IS NOT NULL

  DROP TABLE dbo.FT_Document

GO

CREATE TABLE dbo.FT_Document AS FILETABLE

  WITH

  (

    FILETABLE_DIRECTORY = 'dbo.FT_Document',

    FILETABLE_COLLATE_FILENAME = database_default

  )

GO

select db_name(Database_id) as DBName,* from sys.database_filestream_options where directory_name='FileTable'

OUTPUT:

DBName	database_id	non_transacted_access	non_transacted_access_desc	directory_name
Test	20	0	OFF	FileTable

this is how it looks in the management studio once it gets created.