FileTables (SQL Server)

  • Article

The FileTable feature brings support for the Windows file namespace and compatibility with Windows applications to the file data stored in SQL Server. FileTable lets an application integrate its storage and data management components, and provides integrated SQL Server services - including full-text search and semantic search - over unstructured data and metadata.

In other words, you can store files and documents in special tables in SQL Server called FileTables, but access them from Windows applications as if they were stored in the file system, without making any changes to your client applications.

The FileTable feature builds on top of SQL Server FILESTREAM technology. To learn more about FILESTREAM, see FILESTREAM (SQL Server).

In This Topic

  • Benefits of the FileTable Feature

  • What Is a FileTable?

  • Additional Considerations for Using FileTables

    • Administrative Considerations

    • FileTables Do Not Support Memory-Mapped Files

  • Related Tasks

  • Related Content

Benefits of the FileTable Feature

The goals of the FileTable feature include the following:

  • Windows API compatibility for file data stored within a SQL Server database. Windows API compatibility includes the following:

    • Non-transactional streaming access and in-place updates to FILESTREAM data.

    • A hierarchical namespace of directories and files.

    • Storage of file attributes, such as created date and modified date.

    • Support for Windows file and directory management APIs.

  • Compatibility with other SQL Server features including management tools, services, and relational query capabilities over FILESTREAM and file attribute data.

Thus FileTables remove a significant barrier to the use of SQL Server for the storage and management of unstructured data that is currently residing as files on file servers. Enterprises can move this data from file servers into FileTables to take advantage of integrated administration and services provided by SQL Server. At the same time, they can maintain Windows application compatibility for their existing Windows applications that see this data as files in the file system.

[TOP]

What Is a FileTable?

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. For more information about the schema of a FileTable, see FileTable Schema.

    • A FILESTREAM column for stream data and a file_id (GUID) identifier. (The FILESTREAM column is NULL for a directory.)

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

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

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

Some additional characteristics of FileTables include the following:

  • The file and directory data stored in a FileTable is exposed through a Windows share for non-transactional file access for Windows API based applications. For a Windows application, this looks like a normal share with its files and directories. Applications can use a rich set of Windows APIs to manage the files and directories under this share.

  • The directory hierarchy surfaced through the share is a purely logical directory structure that is maintained within the FileTable.

  • Calls to create or change a file or directory through the Windows share are intercepted by a SQL Server component and reflected in the corresponding relational data in the FileTable.

  • Windows API operations are non-transactional in nature, and are not associated with user transactions. However, transactional access to FILESTREAM data stored in a FileTable is fully supported, as is the case for any FILESTREAM column in a regular table.

  • FileTables can also be queried and updated through normal Transact-SQL access. They are also integrated with SQL Server management tools, and features such as backup.

[TOP]

Additional Considerations for Using FileTables

Administrative Considerations

About FILESTREAM and FileTables

  • You configure FileTables separately from FILESTREAM. Therefore you can continue to use the FILESTREAM feature without enabling non-transactional access or creating FileTables.

  • There is no non-transactional access to FILESTREAM data except through FileTables. Therefore, when you enable non-transactional access, the behavior of existing FILESTREAM columns and applications is not affected.

About FileTables and non-transactional access

  • You can enable or disable non-transactional access at the database level.

  • You can configure or fine-tune non-transactional access at the database level by turning it off, or by enabling read only or full read/write access.

[TOP]

FileTables Do Not Support Memory-Mapped Files

FileTables do not support memory-mapped files. Notepad and Paint are two common examples of applications that use memory-mapped files. You cannot use these applications on the same computer as SQL Server to open files that are stored in a FileTable. However you can use these applications from a remote computer to open files that are stored in a FileTable, because in these circumstances the memory-mapping feature is not used.

[TOP]

[TOP]

Related Content

[TOP]