FILESTREAM (SQL Server)FILESTREAM (SQL Server)

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

借助 FILESTREAM,基于 SQL ServerSQL Server 的应用程序可以将非结构化的数据(如文档和图像)存储在文件系统中。FILESTREAM enables SQL ServerSQL Server-based applications to store unstructured data, such as documents and images, on the file system. 应用程序在利用丰富的流式 API 和文件系统的性能的同时,还可保持非结构化数据和对应的结构化数据之间的事务一致性。Applications can leverage the rich streaming APIs and performance of the file system and at the same time maintain transactional consistency between the unstructured data and corresponding structured data.

FILESTREAM 通过将 SQL Server 数据库引擎SQL Server Database Engine varbinary(max) 二进制大型对象 (BLOB) 数据作为文件存储在 NTFS 或 ReFS 文件系统中,将 与该文件系统集成在一起。FILESTREAM integrates the SQL Server 数据库引擎SQL Server Database Engine with an NTFS or ReFS file systems by storing varbinary(max) binary large object (BLOB) data as files on the file system. Transact-SQLTransact-SQL 语句可插入、更新、查询、搜索和备份 FILESTREAM 数据。statements can insert, update, query, search, and back up FILESTREAM data. 通过 Win32 文件系统接口可以流式方式访问数据。Win32 file system interfaces provide streaming access to the data.

FILESTREAM 使用 NT 系统缓存来缓存文件数据。FILESTREAM uses the NT system cache for caching file data. 这有助于减少 FILESTREAM 数据可能对 数据库引擎Database Engine 性能产生的任何影响。This helps reduce any effect that FILESTREAM data might have on 数据库引擎Database Engine performance. 由于没有使用 SQL ServerSQL Server 缓冲池,因此该内存可用于查询处理。The SQL ServerSQL Server buffer pool is not used; therefore, this memory is available for query processing.

在安装或升级 SQL ServerSQL Server时,并不会自动启用 FILESTREAM。FILESTREAM is not automatically enabled when you install or upgrade SQL ServerSQL Server. 您必须使用 SQL Server 配置管理器和 SQL Server Management StudioSQL Server Management Studio来启用 FILESTREAM。You must enable FILESTREAM by using SQL Server Configuration Manager and SQL Server Management StudioSQL Server Management Studio. 若要使用 FILESTREAM,您必须创建或修改数据库以包含一个特殊类型的文件组。To use FILESTREAM, you must create or modify a database to contain a special type of filegroup. 然后,创建或修改某个表,以使其包含一个具有 FILESTREAM 属性的 varbinary(max) 列。Then, create or modify a table so that it contains a varbinary(max) column with the FILESTREAM attribute. 在完成这些任务后,您可以使用 Transact-SQLTransact-SQL 和 Win32 来管理 FILESTREAM 数据。After you complete these tasks, you can use Transact-SQLTransact-SQL and Win32 to manage the FILESTREAM data.

何时使用 FILESTREAMWhen to Use FILESTREAM

SQL ServerSQL Server中,BLOB 可以是将数据存储在表中的标准 varbinary(max) 数据,也可以是将数据存储在文件系统中的 FILESTREAM varbinary(max) 对象。In SQL ServerSQL Server, BLOBs can be standard varbinary(max) data that stores the data in tables, or FILESTREAM varbinary(max) objects that store the data in the file system. 数据的大小和应用情况决定您应该使用数据库存储还是文件系统存储。The size and use of the data determines whether you should use database storage or file system storage. 如果满足以下条件,则应考虑使用 FILESTREAM:If the following conditions are true, you should consider using FILESTREAM:

  • 所存储的对象平均大于 1 MB。Objects that are being stored are, on average, larger than 1 MB.
  • 快速读取访问很重要。Fast read access is important.
  • 您开发的是使用中间层作为应用程序逻辑的应用程序。You are developing applications that use a middle tier for application logic.

对于较小的对象,将 varbinary(max) BLOB 存储在数据库中通常会提供更为优异的流性能。For smaller objects, storing varbinary(max) BLOBs in the database often provides better streaming performance.

FILESTREAM 存储FILESTREAM Storage

FILESTREAM 存储以 varbinary(max) 列的形式实现,在该列中数据以 BLOB 的形式存储在文件系统中。FILESTREAM storage is implemented as a varbinary(max) column in which the data is stored as BLOBs in the file system. BLOB 的大小仅受文件系统容量大小的限制。The sizes of the BLOBs are limited only by the volume size of the file system. 文件大小为 2 GB 的 varbinary(max) 标准限制不适用于存储在文件系统中的 BLOB。The standard varbinary(max) limitation of 2-GB file sizes does not apply to BLOBs that are stored in the file system.

若要指定列应将数据存储在文件系统中,请对 varbinary(max) 列指定 FILESTREAM 属性。To specify that a column should store data on the file system, specify the FILESTREAM attribute on a varbinary(max) column. 这样 数据库引擎Database Engine 会将该列的所有数据存储在文件系统,而不是数据库文件中。This causes the 数据库引擎Database Engine to store all data for that column on the file system, but not in the database file.

FILESTREAM 数据必须存储在 FILESTREAM 文件组中。FILESTREAM data must be stored in FILESTREAM filegroups. FILESTREAM 文件组是包含文件系统目录而非文件本身的专用文件组。A FILESTREAM filegroup is a special filegroup that contains file system directories instead of the files themselves. 这些文件系统目录称为“数据容器” 。These file system directories are called data containers. 数据容器是 数据库引擎Database Engine 存储与文件系统存储之间的接口。Data containers are the interface between 数据库引擎Database Engine storage and file system storage.

使用 FILESTREAM 存储时,请考虑以下内容:When you use FILESTREAM storage, consider the following:

  • 如果表包含 FILESTREAM 列,则每一行都必须具有唯一的一个非 Null 行 ID。When a table contains a FILESTREAM column, each row must have a nonnull unique row ID.
  • 可以将多个数据容器添加到 FILESTREAM 文件组。Multiple data containers can be added to a FILESTREAM filegroup.
  • 不能嵌套 FILESTREAM 数据容器。FILESTREAM data containers cannot be nested.
  • 使用故障转移群集时,FILESTREAM 文件组必须位于共享磁盘资源上。When you are using failover clustering, the FILESTREAM filegroups must be on shared disk resources.
  • FILESTREAM 文件组可位于压缩卷上。FILESTREAM filegroups can be on compressed volumes.

集成管理Integrated Management

由于 FILESTREAM 作为 varbinary(max) 列实现并直接集成到 数据库引擎Database Engine中,因此绝大多数 SQL ServerSQL Server 管理工具和函数在使用过程中不会修改 FILESTREAM 数据。Because FILESTREAM is implemented as a varbinary(max) column and integrated directly into the 数据库引擎Database Engine, most SQL ServerSQL Server management tools and functions work without modification for FILESTREAM data. 例如,您可以对 FILESTREAM 数据使用所有备份模式和恢复模式,并且 FILESTREAM 数据是与数据库中的结构化数据一起进行备份的。For example, you can use all backup and recovery models with FILESTREAM data, and the FILESTREAM data is backed up with the structured data in the database. 如果您不想将 FILESTREAM 数据与关系数据一起备份,则可以使用部分备份将 FILESTREAM 文件组排除在外。If you do not want to back up FILESTREAM data with relational data, you can use a partial backup to exclude FILESTREAM filegroups.

集成安全性Integrated Security

SQL ServerSQL Server中,保护 FILESTREAM 数据的方式与其他数据相同,即通过在表级或列级授予权限。In SQL ServerSQL Server, FILESTREAM data is secured just like other data is secured: by granting permissions at the table or column levels. 如果用户具有访问表中 FILESTREAM 列的权限,则用户可打开关联文件。If a user has permission to the FILESTREAM column in a table, the user can open the associated files.

备注

FILESTREAM 数据不支持加密。Encryption is not supported on FILESTREAM data.

仅对用于运行 SQL ServerSQL Server 服务帐户的帐户授予访问 FILESTREAM 容器的权限。Only the account under which the SQL ServerSQL Server service account runs is granted permissions to the FILESTREAM container. 建议不对其他帐户授予访问数据容器的权限。We recommend that no other account be granted permissions on the data container.

备注

SQL 登录名不能与 FILESTREAM 容器配合工作。SQL logins will not work with FILESTREAM containers. 只有 NTFS 或 ReFS 身份验证可与 FILESTREAM 容器配合工作。Only NTFS or ReFS authentication will work with FILESTREAM containers.

使用 Transact-SQL 和文件系统流访问来访问 BLOB 数据Accessing BLOB Data with Transact-SQL and File System Streaming Access

在将数据存储在 FILESTREAM 列中之后,即可通过使用 Transact-SQLTransact-SQL 事务或 Win32 API 访问文件。After you store data in a FILESTREAM column, you can access the files by using Transact-SQLTransact-SQL transactions or by using Win32 APIs.

Transact-SQL 访问Transact-SQL Access

可以使用 Transact-SQLTransact-SQL来插入、更新和删除 FILESTREAM 数据:By using Transact-SQLTransact-SQL, you can insert, update, and delete FILESTREAM data:

  • 您可以使用插入操作用 null 值、空值或相对较短的内联数据预填充 FILESTREAM 字段。You can use an insert operation to prepopulate a FILESTREAM field with a null value, empty value, or relatively short inline data. 但是,大量数据将以流的方式更有效地导入到使用 Win32 接口的文件中。However, a large amount of data is more efficiently streamed into a file that uses Win32 interfaces.
  • 更新 FILESTREAM 字段时,即会修改文件系统中的基础 BLOB 数据。When you update a FILESTREAM field, you modify the underlying BLOB data in the file system. 将 FILESTREAM 字段设置为 NULL 即会删除与该字段相关联的 BLOB 数据。When a FILESTREAM field is set to NULL, the BLOB data associated with the field is deleted. 你不能使用作为 UPDATE Transact-SQLTransact-SQL .Write() 实现的成块更新以对数据执行部分更新。You cannot use a Transact-SQLTransact-SQL chunked update, implemented as UPDATE . Write(), to perform partial updates to the data.
  • 当删除行或者删除或截断包含 FILESTREAM 数据的表时,将会删除文件系统中的基础 BLOB 数据。When you delete a row or delete or truncate a table that contains FILESTREAM data, you delete the underlying BLOB data in the file system.

文件系统流访问File System Streaming Access

SQL ServerSQL Server 事务上下文中支持 Win32 流。The Win32 streaming support works in the context of a SQL ServerSQL Server transaction. 在事务之内,可以使用 FILESTREAM 函数来获取文件的逻辑 UNC 文件系统路径。Within a transaction, you can use FILESTREAM functions to obtain a logical UNC file system path of a file. 接着使用 OpenSqlFilestream API 来获取文件句柄。You then use the OpenSqlFilestream API to obtain a file handle. 然后 Win32 文件流接口(如 ReadFile() 和 WriteFile())可使用此句柄通过文件系统访问并更新文件。This handle can then be used by Win32 file streaming interfaces, such as ReadFile() and WriteFile(), to access and update the file by way of the file system.

由于文件操作属于事务操作,因此您无法通过文件系统删除或重命名 FILESTREAM 文件。Because file operations are transactional, you cannot delete or rename FILESTREAM files through the file system.

语句模型Statement Model

FILESTREAM 文件系统访问通过使用文件打开和关闭来构建 Transact-SQLTransact-SQL 语句模型。The FILESTREAM file system access models a Transact-SQLTransact-SQL statement by using file open and close. 当打开文件句柄时,语句开始;当关闭句柄时,语句结束。The statement starts when a file handle is opened and ends when the handle is closed. 例如,关闭写句柄时将激发表上注册的任何可能的 AFTER 触发器,就如 UPDATE 语句结束一样。For example, when a write handle is closed, any possible AFTER trigger that is registered on the table fires as if an UPDATE statement is completed.

存储命名空间Storage Namespace

在 FILESTREAM 中, 数据库引擎Database Engine 控制 BLOB 物理文件系统的命名空间。In FILESTREAM, the 数据库引擎Database Engine controls the BLOB physical file system namespace. PathName是一个新增的内部函数,它提供对应于表中每个 FILESTREAM 单元的 BLOB 的逻辑 UNC 路径。A new intrinsic function, PathName, provides the logical UNC path of the BLOB that corresponds to each FILESTREAM cell in the table. 应用程序使用此逻辑路径来获取 Win32 句柄并通过使用常见的 Win32 文件系统接口对 BLOB 数据进行操作。The application uses this logical path to obtain the Win32 handle and operate on the BLOB data by using regular Win32 file system interfaces. 如果 FILESTREAM 列的值为 NULL,则此函数将返回 NULL。The function returns NULL if the value of the FILESTREAM column is NULL.

事务文件系统访问Transacted File System Access

GET_FILESTREAM_TRANSACTION_CONTEXT()是一个新增的内部函数,它提供表示与会话相关联的当前事务的标记。A new intrinsic function, GET_FILESTREAM_TRANSACTION_CONTEXT(), provides the token that represents the current transaction that the session is associated with. 事务必须已启动,且仍未被中止或提交。The transaction must have been started and not yet aborted or committed. 通过获取标记,应用程序将 FILESTREAM 文件系统流操作与已启动的事务绑定在一起。By obtaining a token, the application binds the FILESTREAM file system streaming operations with a started transaction. 在没有显式启动的事务的情况下,此函数将返回 NULL。The function returns NULL in case of no explicitly started transaction.

在提交或中止事务之前必须关闭所有文件句柄。All file handles must be closed before the transaction commits or aborts. 如果在事务范围之外仍有句柄处于打开状态,则对句柄执行的其他读取将失败,对句柄执行的其他写入将成功,但实际数据不会写入磁盘中。If a handle is left open beyond the transaction scope, additional reads against the handle will cause a failure; additional writes against the handle will succeed, but the actual data will not be written to disk. 与此类似,如果数据库或 数据库引擎Database Engine 实例关闭,则所有处于打开状态的句柄均无效。Similarly, if the database or instance of the 数据库引擎Database Engine shuts down, all open handles are invalidated.

事务持续性Transactional Durability

在事务提交时, 数据库引擎Database Engine 通过使用 FILESTREAM 可确保通过文件系统流访问进行修改的 FILESTREAM BLOB 数据的事务持久性。With FILESTREAM, upon transaction commit, the 数据库引擎Database Engine ensures transaction durability for FILESTREAM BLOB data that is modified from the file system streaming access.

隔离语义Isolation Semantics

隔离语义由 数据库引擎Database Engine 事务隔离级别决定。The isolation semantics are governed by 数据库引擎Database Engine transaction isolation levels. Transact-SQLTransact-SQL 和文件系统访问支持已提交读隔离级别。Read-committed isolation level is supported for Transact-SQLTransact-SQL and file system access. 支持重复的读取操作以及可序列化隔离和快照隔离。Repeatable read operations, and also serializable and snapshot isolations, are supported. 不支持脏读。Dirty read is not supported.

文件系统访问的打开操作不等待任何锁。The file system access open operations do not wait for any locks. 与此相反,如果打开操作因为事务隔离而无法访问数据,则打开操作将立即失败。Instead, the open operations fail immediately if they cannot access the data because of transaction isolation. 如果由于隔离冲突而无法继续执行打开操作,则流 API 调用将失败并返回 ERROR_SHARING_VIOLATION 错误。The streaming API calls fail with ERROR_SHARING_VIOLATION if the open operation cannot continue because of isolation violation.

为了允许执行部分更新,应用程序可发出一个设备 FS 控制 (FSCTL_SQL_FILESTREAM_FETCH_OLD_CONTENT) 以将旧内容提取到处于打开状态的句柄所引用的文件中。To allow for partial updates to be made, the application can issue a device FS control (FSCTL_SQL_FILESTREAM_FETCH_OLD_CONTENT) to fetch the old content into the file that the opened handle references. 这将触发服务器端的旧内容复制操作。This will trigger a server-side old content copy. 为了提升应用程序性能并在处理超大型文件时避免发生潜在的超时问题,建议您使用异步 I/O。For better application performance and to avoid running into potential time-outs when you are working with very large files, we recommend that you use asynchronous I/O.

如果在写入句柄之后发出 FSCTL,则将保持最后一个写入操作并丢失之前对句柄执行的写入操作。If the FSCTL is issued after the handle has been written to, the last write operation will persist, and prior writes that were made to the handle are lost.

文件系统 API 和支持的隔离级别File System APIs and Supported Isolation Levels

当文件系统 API 由于隔离冲突而无法打开文件时,将返回 ERROR_SHARING_VIOLATION 异常。When a file system API cannot open a file because of an isolation violation, an ERROR_SHARING_VIOLATION exception is returned. 两个事务尝试访问同一文件时,将发生此隔离冲突。This isolation violation occurs when two transactions try to access the same file. 访问操作的结果取决于打开该文件的模式和运行事务的 SQL ServerSQL Server 版本。The outcome of the access operation depends on the mode the file was opened in and the version of SQL ServerSQL Server that the transaction is running on. 下表概括了访问同一文件的两个事务的可能结果。The following table outlines the possibly outcomes for two transactions that are accessing the same file.

事务 1Transaction 1 事务 2Transaction 2 在 SQL Server 2008 上的结果Outcome on SQL Server 2008 在 SQL Server 2008 R2 和更高版本上的结果Outcome on SQL Server 2008 R2 and later versions
打开以进行读取。Open for read. 打开以进行读取。Open for read. 都成功。Both succeed. 都成功。Both succeed.
打开以进行读取。Open for read. 打开以进行写入。Open for write. 都成功。Both succeed. 事务 2 下的写入操作不影响在事务 1 中执行的读取操作。Write operations under transaction 2 do not affect read operations performed in transaction 1. 都成功。Both succeed. 事务 2 下的写入操作不影响在事务 1 中执行的读取操作。Write operations under transaction 2 do not affect read operations performed in transaction 1.
打开以进行写入。Open for write. 打开以进行读取。Open for read. 事务 2 下的打开失败,并发生 ERROR_SHARING_VIOLATION 异常。Open for transaction 2 fails with an ERROR_SHARING_VIOLATION exception. 都成功。Both succeed.
打开以进行写入。Open for write. 打开以进行写入。Open for write. 事务 2 下的打开失败,并发生 ERROR_SHARING_VIOLATION 异常。Open for transaction 2 fails with an ERROR_SHARING_VIOLATION exception. 事务 2 下的打开失败,并发生 ERROR_SHARING_VIOLATION 异常。Open for transaction 2 fails with an ERROR_SHARING_VIOLATION exception.
打开以进行读取。Open for read. 打开以进行 SELECT。Open for SELECT. 都成功。Both succeed. 都成功。Both succeed.
打开以进行读取。Open for read. 打开以进行 UPDATE 或 DELETE。Open for UPDATE or DELETE. 都成功。Both succeed. 事务 2 下的写入操作不影响在事务 1 中执行的读取操作。Write operations under transaction 2 do not affect read operations performed in transaction 1. 都成功。Both succeed. 事务 2 下的写入操作不影响在事务 1 中执行的读取操作。Write operations under transaction 2 do not affect read operations performed in transaction 1.
打开以进行写入。Open for write. 打开以进行 SELECT。open for SELECT. 事务 2 在事务 1 提交或结束事务前或该事务锁超时前一直阻塞。Transaction 2 blocks until transaction 1 commits or ends the transaction, or the transaction lock times out. 都成功。Both succeed.
打开以进行写入。Open for write. 打开以进行 UPDATE 或 DELETE。Open for UPDATE or DELETE. 事务 2 在事务 1 提交或结束事务前或该事务锁超时前一直阻塞。Transaction 2 blocks until transaction 1 commits or ends the transaction, or the transaction lock times out. 事务 2 在事务 1 提交或结束事务前或该事务锁超时前一直阻塞。Transaction 2 blocks until transaction 1 commits or ends the transaction, or the transaction lock times out.
打开以进行 SELECT。Open for SELECT. 打开以进行读取。Open for read. 都成功。Both succeed. 都成功。Both succeed.
打开以进行 SELECT。Open for SELECT. 打开以进行写入。Open for write. 都成功。Both succeed. 事务 2 下的写入操作不影响事务 1。Write operations under transaction 2 do not affect transaction 1. 都成功。Both succeed. 事务 2 下的写入操作不影响事务 1。Write operations under transaction 2 do not affect transaction 1.
打开以进行 UPDATE 或 DELETE。Open for UPDATE or DELETE. 打开以进行读取。Open for read. 事务 2 下的打开操作失败,并发生 ERROR_SHARING_VIOLATION 异常。The open operation on transaction 2 fails with an ERROR_SHARING_VIOLATION exception. 都成功。Both succeed.
打开以进行 UPDATE 或 DELETE。Open for UPDATE or DELETE. 打开以进行写入。Open for write. 事务 2 下的打开操作失败,并发生 ERROR_SHARING_VIOLATION 异常。The open operation on transaction 2 fails with an ERROR_SHARING_VIOLATION exception. 事务 2 下的打开操作失败,并发生 ERROR_SHARING_VIOLATION 异常。The open operation on transaction 2 fails with an ERROR_SHARING_VIOLATION exception.
打开以进行可重复读取的 SELECT。Open for SELECT with repeatable read. 打开以进行读取。Open for read. 都成功。Both succeed. 都成功。Both succeed.
打开以进行可重复读取的 SELECT。Open for SELECT with repeatable read. 打开以进行写入。Open for write. 事务 2 下的打开操作失败,并发生 ERROR_SHARING_VIOLATION 异常。The open operation on transaction 2 fails with an ERROR_SHARING_VIOLATION exception. 事务 2 下的打开操作失败,并发生 ERROR_SHARING_VIOLATION 异常。The open operation on transaction 2 fails with an ERROR_SHARING_VIOLATION exception.

从远程客户端写透Write-Through from Remote Clients

对 FILESTREAM 数据的远程文件系统访问是通过 Server Message Block (SMB) 协议启用的。Remote file system access to FILESTREAM data is enabled over the Server Message Block (SMB) protocol. 如果客户端为远程客户端,则客户端不对任何写入操作进行缓存。If the client is remote, no write operations are cached by the client side. 写入操作将始终发送到服务器。The write operations will always be sent to the server. 服务器端可对数据进行缓存。The data can be cached on the server side. 建议运行在远程客户端上的应用程序合并小型写入操作,以减小使用较大数据大小的写入操作数量。We recommend that applications that are running on remote clients consolidate small write operations to make fewer write operations using larger data size.

不支持通过使用 FILESTREAM 句柄创建内存映射视图(内存映射 I/O)。Creating memory mapped views (memory mapped I/O) by using a FILESTREAM handle is not supported. 如果内存映射用于 FILESTREAM 数据,则 数据库引擎Database Engine 将无法保证数据的一致性和持久性或数据库的完整性。If memory mapping is used for FILESTREAM data, the 数据库引擎Database Engine cannot guarantee consistency and durability of the data or the integrity of the database.

启用和配置 FILESTREAMEnable and Configure FILESTREAM
创建启用了 FILESTREAM 的数据库Create a FILESTREAM-Enabled Database
创建表以存储 FILESTREAM 数据Create a Table for Storing FILESTREAM Data
使用 Transact-SQL 访问 FILESTREAM 数据 为 FILESTREAM 数据创建客户端应用程序Access FILESTREAM Data with Transact-SQL Create Client Applications for FILESTREAM Data
使用 OpenSqlFilestream 访问 FILESTREAM 数据Access FILESTREAM Data with OpenSqlFilestream
对 FILESTREAM 数据进行部分更新Make Partial Updates to FILESTREAM Data
避免与 FILESTREAM 应用程序中的数据库操作冲突Avoid Conflicts with Database Operations in FILESTREAM Applications
移动启用了 FILESTREAM 的数据库Move a FILESTREAM-Enabled Database
在故障转移群集中设置 FILESTREAMSet Up FILESTREAM on a Failover Cluster
将防火墙配置为进行 FILESTREAM 访问Configure a Firewall for FILESTREAM Access

FILESTREAM 与其他 SQL Server 功能的兼容性FILESTREAM Compatibility with Other SQL Server Features
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)