FileTable (SQL Server)FileTables (SQL Server)

适用对象:是SQL Server 否Azure SQL 数据库 否Azure Synapse Analytics (SQL DW) 否并行数据仓库APPLIES TO: yesSQL Server noAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

FileTable 功能为 SQL ServerSQL Server中存储的文件数据提供对 Windows 文件命名空间的支持以及与 Windows 应用程序的兼容性支持。The FileTable feature brings support for the Windows file namespace and compatibility with Windows applications to the file data stored in SQL ServerSQL Server. FileTable 使得应用程序可以集成其存储和数据管理组件,可对非结构化数据和元数据提供集成的 SQL ServerSQL Server 服务(包括全文搜索和语义搜索)。FileTable lets an application integrate its storage and data management components, and provides integrated SQL ServerSQL Server services - including full-text search and semantic search - over unstructured data and metadata.

换言之,您可以在 SQL ServerSQL Server 中将文件和文档存储在称作 FileTable 的特别的表中,但是从 Windows 应用程序访问它们,就好像它们存储在文件系统中,而不必对您的客户端应用程序进行任何更改。In other words, you can store files and documents in special tables in SQL ServerSQL 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.

FileTable 功能是在 SQL ServerSQL Server FILESTREAM 技术的基础上生成的。The FileTable feature builds on top of SQL ServerSQL Server FILESTREAM technology. 有关 FILESTREAM 的详细信息,请参阅 FILESTREAM (SQL Server)To learn more about FILESTREAM, see FILESTREAM (SQL Server).

FileTable 功能的优点Benefits of the FileTable Feature

FileTable 功能的目标包括:The goals of the FileTable feature include the following:

  • 针对 SQL ServerSQL Server 数据库中存储的文件数据的 Windows API 兼容性。Windows API compatibility for file data stored within a SQL ServerSQL Server database. Windows API 兼容性包括以下方面:Windows API compatibility includes the following:

    • 对 FILESTREAM 数据的非事务性流式访问和就地更新。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.

    • 对 Windows 文件和目录管理 API 的支持。Support for Windows file and directory management APIs.

  • 与其他 SQL ServerSQL Server 功能的兼容性包括针对 FILESTREAM 和文件属性数据的管理工具、服务和关系查询功能。Compatibility with other SQL ServerSQL Server features including management tools, services, and relational query capabilities over FILESTREAM and file attribute data.

这样,FileTable 将消除使用 SQL ServerSQL Server 来存储和管理非结构化数据的一个巨大障碍,这些数据目前作为文件存储在文件服务器上。Thus FileTables remove a significant barrier to the use of SQL ServerSQL Server for the storage and management of unstructured data that is currently residing as files on file servers. 企业可以将这些数据从文件服务器移到 FileTable,以利用 SQL ServerSQL Server提供的集成管理和服务。Enterprises can move this data from file servers into FileTables to take advantage of integrated administration and services provided by SQL ServerSQL Server. 同时,它们可以保持现有 Windows 应用程序的 Windows 应用程序兼容性,将这些数据视为文件系统中的文件。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.

什么是 FileTable?What Is a FileTable?

SQL ServerSQL Server 对于需要在数据库中存储文件和目录的应用程序,借助 Windows API 兼容性和非事务性访问,提供一种特殊的 文件表,也称为 FileTableprovides 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. FileTable 是一种专用的用户表,它包含存储 FILESTREAM 数据的预定义架构以及文件和目录层次结构信息、文件属性。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.

FileTable 提供以下功能:A FileTable provides the following functionality:

  • FileTable 表示目录和文件的一种层次结构。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. 该层次结构以您创建 FileTable 时指定的根目录为起点。This hierarchy starts from a root directory that you specify when you create the FileTable.

  • FileTable 中的每行表示一个文件或目录。Every row in a FileTable represents a file or a directory.

  • 每行包含以下项:Every row contains the following items. 有关 FileTable 的架构的详细信息,请参阅 FileTable SchemaFor more information about the schema of a FileTable, see FileTable Schema.

    • 流数据的 file_stream 列和 stream_id (GUID) 标识符 。A file_stream column for stream data and a stream_id (GUID) identifier. file_stream 列对于目录为 NULL。)(The file_stream column is NULL for a directory.)

    • 用于表示和维护当前项(文件或目录)以及目录层次结构的 path_locator 列和 parent_path_locator 列 。Both path_locator and parent_path_locator columns for representing and maintaining the current item (file or directory) and directory hierarchy.

    • 10 个文件属性,如可用于文件 I/O API 的创建日期和修改日期。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.

  • FileTable 强制执行某些系统定义的约束和触发器以维护文件命名空间语义。A FileTable enforces certain system-defined constraints and triggers to maintain file namespace semantics.

  • 针对非事务性访问配置数据库时,在为 SQL ServerSQL Server 实例配置的 FILESTREAM 共享区下公开在 FileTable 中表示的文件和目录层次结构。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 ServerSQL Server instance. 这为 Windows 应用程序提供了文件系统访问方法。This provides file system access for Windows applications.

FileTable 的一些其他特性包括:Some additional characteristics of FileTables include the following:

  • 存储在 FileTable 中的文件和目录数据通过用于非事务性文件访问的 Windows 共享区(针对基于 Windows API 的应用程序)公开。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. 对于 Windows 应用程序,这看起来像一个包含文件和目录的普通共享区。For a Windows application, this looks like a normal share with its files and directories. 应用程序可使用一组广泛的 Windows API,用于对此共享区下的文件和目录进行管理。Applications can use a rich set of Windows APIs to manage the files and directories under this share.

  • 通过该共享区公开的目录层次结构是一个在 FileTable 中维护的纯逻辑目录结构。The directory hierarchy surfaced through the share is a purely logical directory structure that is maintained within the FileTable.

  • 通过该 Windows 共享区对创建或更改文件/目录的调用被 SQL ServerSQL Server 组件拦截并在 FileTable 中的相应关系数据中得到反映。Calls to create or change a file or directory through the Windows share are intercepted by a SQL ServerSQL Server component and reflected in the corresponding relational data in the FileTable.

  • Windows API 操作本质上是非事务性的,并不与用户事务关联。Windows API operations are non-transactional in nature, and are not associated with user transactions. 但是,完全支持对存储在 FileTable 中的 FILESTREAM 数据的事务性访问,就像对待常规表中的任何 FILESTREAM 列一样。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.

  • 还可以通过常规 Transact-SQLTransact-SQL 访问来查询和更新 FileTable。FileTables can also be queried and updated through normal Transact-SQLTransact-SQL access. 它们还与 SQL ServerSQL Server 管理工具和诸如备份的功能集成。They are also integrated with SQL ServerSQL Server management tools, and features such as backup.

  • 无法通过 dbmail 发送电子邮件请求以及附加位于文件流目录中的文件(因此无法附加 filetable)。You are unable to send an email request through dbmail and attach a file located in a filestream directory (and therefore filetable). 文件系统筛选器驱动程序 RsFx0420 会检查进出文件流文件夹的传入 I/O 请求。The filesystem filter driver RsFx0420 inspects incoming I/O requests going in and out of the filestream folder. 如果请求不是同时出自 SQLServer 可执行文件和文件流代码中,则会显式禁止它们。If the request is not both from the SQLServer executable and Filestream code, they are explicitly disallowed.

使用 FileTable 的其他注意事项Additional Considerations for Using FileTables

管理注意事项Administrative Considerations

关于 FILESTREAM 和 FileTableAbout FILESTREAM and FileTables

  • 独立于 FILESTREAM 配置 FileTable。You configure FileTables separately from FILESTREAM. 因此,您可以继续使用 FILESTREAM 功能,而不启用非事务性访问或创建 FileTable。Therefore you can continue to use the FILESTREAM feature without enabling non-transactional access or creating FileTables.

  • 除了通过 FileTable,没有对 FILESTREAM 数据的其他非事务性访问。There is no non-transactional access to FILESTREAM data except through FileTables. 因此,在启用非事务性访问时并不会影响现有的 FILESTREAM 列和应用程序的行为。Therefore, when you enable non-transactional access, the behavior of existing FILESTREAM columns and applications is not affected.

关于 FileTable 和非事务性访问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.

FileTable 不支持内存映射文件FileTables Do Not Support Memory-Mapped Files

FileTable 不支持内存映射文件。FileTables do not support memory-mapped files. “记事本”和“画图”是两个常见的使用内存映射文件的示例应用程序。Notepad and Paint are two common examples of applications that use memory-mapped files. 不能在 SQL ServerSQL Server 所在的计算机上使用这些应用程序来打开存储在 FileTable 中的文件。You cannot use these applications on the same computer as SQL ServerSQL Server to open files that are stored in a FileTable. 但是,可以从远程计算机使用这些应用程序来打开存储在 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.

相关任务Related Tasks

启用 FileTable 的先决条件Enable the Prerequisites for FileTable
介绍如何启用创建和使用 FileTable 的先决条件。Describes how to enable the prerequisites for creating and using FileTables.

创建、更改和删除 FileTableCreate, Alter, and Drop FileTables
说明如何创建新的 FileTable 或者更改或删除现有的 FileTable。Describes how to create a new FileTable, or alter or drop an existing FileTable.

将文件加载到 FileTable 中Load Files into FileTables
说明如何将文件加载或迁移到 FileTable 中。Describes how to load or migrate files into FileTables.

在 FileTable 中使用目录和路径Work with Directories and Paths in FileTables
说明 FileTable 中用于存储文件的目录结构。Describes the directory structure in which the files are stored in FileTables.

使用 Transact-SQL 访问 FileTableAccess FileTables with Transact-SQL
说明 Transact-SQL 数据操作语言 (DML) 命令如何与 FileTable 一起使用。Describes how Transact-SQL data manipulation language (DML) commands work with FileTables.

使用文件输入输出 API 访问 FileTableAccess FileTables with File Input-Output APIs
说明如何在 FileTable 上执行文件系统 I/O。Describes how file system I/O works on a FileTable.

管理 FileTableManage FileTables
说明用于管理 FileTable 的常见管理任务。Describes common administrative tasks for managing FileTables.

相关内容Related Content

FileTable SchemaFileTable Schema
说明 FileTable 的预定义固定架构。Describes the pre-defined and fixed schema of a FileTable.

FileTable 与其他 SQL Server 功能的兼容性FileTable Compatibility with Other SQL Server Features
说明 FileTable 如何与 SQL Server 的其他功能配合使用。Describes how FileTables work with other features of SQL Server.

FileTable DDL、函数、存储过程和视图FileTable DDL, Functions, Stored Procedures, and Views
列出用于支持 FileTable 功能的新增或更改的 Transact-SQLTransact-SQL 语句和 SQL ServerSQL Server 数据库对象。Lists the Transact-SQLTransact-SQL statements and the SQL ServerSQL Server database objects that have been added or changed to support the FileTable feature.

另请参阅See Also

Filestream 和 FileTable 动态管理视图 (Transact-SQL)Filestream and FileTable Dynamic Management Views (Transact-SQL)
Filestream 和 FileTable 目录视图 (Transact-SQL)Filestream and FileTable Catalog Views (Transact-SQL)
Filestream 和 FileTable 系统存储过程 (Transact-SQL)Filestream and FileTable System Stored Procedures (Transact-SQL)