本文探讨了 SQL Server FILETABLE 的概述及其与 SQL Server FILESTREAM 的对比，重点介绍了如何在 SQL Server 2012 及以上版本中创建和管理 FILETABLE，以及其在目录层次结构和文件数据管理方面的优势。
filetable
This SQL Server FILETABLE article is the continuation of the SQL Server FILESTREAM series. The SQL Server FILESTREAM feature is available from SQL Server 2008 on. We can store unstructured objects into FILESTREAM container using this feature. SQL Server 2012 introduced a new feature, SQL Server FILETABLE, built on top of the SQL FILESTREAM feature. In this article, we will explore the SQL FILETABLE feature overview and its comparison with SQL FILESTREAM.
此SQL Server FILETABLE文章是SQL Server FILESTREAM系列的延续。 SQL Server FILESTREAM功能可从SQL Server 2008开始使用。 我们可以使用此功能将非结构化对象存储到FILESTREAM容器中。 SQL Server 2012引入了一项新功能SQL Server FILETABLE，该功能建立在SQL FILESTREAM功能的基础上。 在本文中，我们将探讨SQL FILETABLE功能概述及其与SQL FILESTREAM的比较。
Once we connect to SQL Server and expand any database in SSMS, we get the following tables options.
连接到SQL Server并在SSMS中扩展任何数据库后，我们将获得以下表格选项。
- System Tables: SQL Server manages these internal SQL Server tables 系统表 ：SQL Server管理这些内部SQL Server表
- FileTables: We will talk about these tables in this article FileTables ：我们将在本文中讨论这些表
- External Tables: External tables are new features in SQL Server 2017 onwards 外部表 ：外部表是SQL Server 2017及更高版本中的新功能
- Graph Tables: Graph tables are also a new feature in SQL Server 2017 onwards 图形表 ：图形表也是SQL Server 2017及更高版本中的新功能
SQL Server only stores metadata in FILESTREAM tables. It provides performance benefits by taking advantage of NTFS API streaming of the Windows file system. We do not control over the objects in FILESTREAM container using the SQL Server although it provides the transaction consistency.
SQL Server仅将元数据存储在FILESTREAM表中。 它利用Windows文件系统的NTFS API流提供了性能优势。 尽管它提供事务一致性，但我们无法使用SQL Server控制FILESTREAM容器中的对象。
SQL Server 2012 introduced the SQL Server FILETABLE feature that is built on SQL FILESTREAM. We can store directory hierarchies in a database. It provides compatibility with Windows applications to the file data stored in SQL Server. SQL FILETABLE stores the file and directory attributes along with the FILESTREAM data into FILETABLE. The user can access the objects from the container similar to a file share. We do not have to make changes in the applications to access these data. You can also paste the files in the container (host directory), and SQL Server automatically inserts metadata into FILETABLE.
SQL Server 2012引入了基于SQL FILESTREAM构建SQL Server FILETABLE功能。 我们可以将目录层次结构存储在数据库中。 它与Windows应用程序兼容，可以存储在SQL Server中的文件数据。 SQL FILETABLE将文件和目录属性以及FILESTREAM数据存储到FILETABLE中。 用户可以像文件共享一样从容器访问对象。 我们不必在应用程序中进行更改即可访问这些数据。 您也可以将文件粘贴到容器（主机目录）中，SQL Server会自动将元数据插入FILETABLE。
We need to perform the following steps to create SQL Server FILETABLE on SQL Server 2012 and above versions.
我们需要执行以下步骤在SQL Server 2012及更高版本上创建SQL Server FILETABLE。
- Enable FILESTREAM for Transact SQL access. 启用FILESTREAM以进行Transact SQL访问”。
- filestream_access_level using sp_configure. filestream_access_level 。
Value
Description
0
Disabled
1
Enabled for T-SQL access
2
Enabled for T-SQL and Windows streaming
值
描述
0
残障人士
1个
为T-SQL访问启用
2
为T-SQL和Windows流启用
We have already configured filestream_access_level during SQL FILESTREAM series. You can verify the configuration using the following query.
在SQL FILESTREAM系列中，我们已经配置了filestream_access_level 。 您可以使用以下查询来验证配置。
USE master GO SELECT [value],[value_in_use],description FROM [sys].[configurations] WHERE name= 'filestream access level'In the below screenshot, we can verify that we have enabled FILESTREAM access for both Windows streaming and T-SQL.
在下面的屏幕截图中，我们可以验证是否已为Windows流和T-SQL启用了FILESTREAM访问。
CREATE DATABASE SQLFileTable ON PRIMARY ( NAME = SQLFileTable_data, FILENAME = 'C:\sqlshack\FileTable\SQLFileTable.mdf' ), FILEGROUP FileStreamFG CONTAINS FILESTREAM ( NAME = SQLFileTable, FILENAME = 'C:\sqlshack\FileTable\FileTable_Container' ) LOG ON ( NAME = SQLFileTable_Log, FILENAME = 'C:\sqlshack\FileTable\SQLFileTable_Log.ldf' ) WITH FILESTREAM ( NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N'FileTableContainer' ); GOIn this database, we have specified following parameters.
在此数据库中，我们指定了以下参数。
- FILESTREAM filegroup along with the FILESTREAM container path. It should be a valid directory name. FILESTREAM文件组以及FILESTREAM容器路径。 它应该是一个有效的目录名称。
- Windows applications can use a file handle to access FILESTREAM data without any SQL transaction. Enable non-transactional access to files at the database level. If we have any existing database, we can validate it using the following query Windows应用程序可以使用文件句柄来访问FILESTREAM数据，而无需任何SQL事务。 在数据库级别启用对文件的非事务访问。 如果我们有任何现有数据库，则可以使用以下查询对其进行验证
SELECT DB_NAME(database_id) as DatabaseName, non_transacted_access, non_transacted_access_desc FROM sys.database_filestream_options where DB_NAME(database_id)='SQLFileTable'; GO
We can have the following options for non-transacted access.
对于非事务访问，我们可以有以下选项。
- OFF: Non-transactional access to FileTables is not allowed OFF ：不允许非事务性访问FileTables
- Read Only– Non-transactional access to FileTables is allowed for the read-only purpose 只读 -出于只读目的，允许对FileTables的非事务访问
- Full– Non-transactional access to FileTables is allowed for both reading and writing 完全 –允许对FileTables进行非事务访问以进行读取和写入
- Specify a directory for the SQL Server FILETABLE. We need to specify directory without directory path. This directory acts as a root path in FILETABLE hierarchy. We will explore more in a further section of this article 为SQL Server FILETABLE指定目录。 我们需要指定没有目录路径的目录。 该目录充当FILETABLE层次结构中的根路径。 我们将在本文的其他部分进一步探讨
If you have a SQL FILETABLE, you can use the following query to check the directory name for FILETABLE.
如果您有SQL FILETABLE，则可以使用以下查询来检查FILETABLE的目录名称。
Select DB_NAME ( database_id) as DatabaseName, directory_name FROM sys.database_filestream_options where DB_NAME(database_id)='SQLFileTable';
use SQLFileTable Go CREATE TABLE SQLShackDemoDocuments AS FILETABLE WITH ( FileTable_Directory = 'SQLShackDemo', FileTable_Collate_Filename = database_default );FILETABLE_DIRECTORY: It is the root directory for all objects in the SWQLFILETABLE. It should be unique in all FILETABLE directory names in particular database.
FILETABLE_DIRECTORY ：它是SWQLFILETABLE中所有对象的根目录。 在特定数据库中的所有FILETABLE目录名称中，它应该是唯一的。
FILETABLE_COLLATE_FILENAME: it is the collation for the Name column in the FILETABLE. If we specify database_default in this, it applies the source database collation. We can also specify the collation in this parameter. We can use the following query to specify the collation in FILETABLE.
FILETABLE_COLLATE_FILENAME：这是FILETABLE中“ 名称”列的排序规则。 如果我们在此指定database_default ，它将应用源数据库排序规则。 我们还可以在此参数中指定排序规则。 我们可以使用以下查询在FILETABLE中指定排序规则。
CREATE TABLE [dbo].[SQLShackDemoDocuments] AS FILETABLE WITH ( FILETABLE_DIRECTORY = N'SQLShackDemo', FILETABLE_COLLATE_FILENAME = Latin1_General_CI_AS ) GO
We cannot create a SQL Server FILETABLE using the GUI in SSMS. To demonstrate this limitation, connect to FILETABLE database in SSMS and right click on FileTables and then File Table
我们无法使用SSMS中的GUI创建SQL Server FILETABLE。 为了演示此限制，请在SSMS中连接到FILETABLE数据库，然后右键单击FileTables，然后单击File Table。
It gives the FileTable template as per the following screenshot. It does not launch the table designer window similar to a relational table.
它按照以下屏幕截图提供FileTable模板。 它不会启动类似于关系表的表设计器窗口。
Once we have created the SQL Server FILETABLE, expand the table to view the columns in this table.
创建SQL Server FILETABLE后，展开表以查看该表中的列。
If you look at creating FILETABLE script, we did not specify any column in the table. SQL Server uses predefined and fixed schema for the FILETABLE. It holds metadata of each file along with its characteristics. Let us look at each column description.
如果您查看创建FILETABLE脚本，则我们未在表中指定任何列。 SQL Server对FILETABLE使用预定义和固定的架构。 它保存每个文件的元数据及其特征。 让我们看看每个列的描述。
FILETABLE Column | Description |
Stream_id | It is a unique identifier rowguidcol column for each FILESTREAM table |
File_stream | It is the FILESTREAM column holding actual file content. |
Name | It is the name of the file or folder.
|
Path_locator | This column represents the file or folder hierarchy. It is also the primary key on SQL Server FILETABLE.
|
Parent_file_locator | Parent_file_locator column is a persisted computed column. It contains the path_locator of the parent folder. In the following screenshot, you can see that it uses GetAncestor() function on the path_locator field.
|
file_type | The file_type column shows the file type as per their extension. It is also a computed column. It uses function GETFILEEXTENSION() to get file extension
|
cached_file_size | It gives the file size in bytes. It is also a computed column and uses a datalength function on the file_stream column to calculate file size.
|
creation_time | It captures the date and time the file was created. It uses numeric precision 34 and numeric scale 7.
We can check the date format using the following query in SSMS. SELECT CAST(‘2019-03-08 12:35:29.1234567 +12:15’ AS datetimeoffset(7)) AS ‘datetimeoffset’ It gives output in the following format.
|
last_write_ time | It shows the date and time when the file or folder contents were last updated. It gives output in time in the same format as of creation_time. |
last_access_time | It gives the last file accessed date and time in the same format as of creation_time. |
is_directory | We can check if the particular row refers to a file or folder. Value 0 – File Value 1 – Folder |
is_offline | If the file is offline, it updates the attribute is_offline. |
is_hidden | It refers to the hidden attribute of the file. |
is_readonly | It refers to the read-only attribute of the file. |
is_archive | It gives information about the archive attribute. |
is_system | It gives information about the system file attribute. |
is_temporary | If the file is temporary, this flag is updated. |
FILETABLE栏 | 描述 |
Stream_id | 它是每个FILESTREAM表的唯一标识符rowguidcol列 |
File_stream | 它是保存实际文件内容的FILESTREAM列。 |
名称 | 它是文件或文件夹的名称。 |
路径定位器 | 此列表示文件或文件夹的层次结构。 它也是SQL Server FILETABLE上的主键。 |
Parent_file_locator | Parent_file_locator列是一个持久的计算列。 它包含了 父文件夹的path_locator。 在下面的屏幕截图中，您可以看到它在path_locator字段上使用GetAncestor（）函数。 |
文件类型 | file_type列按扩展名显示文件类型。 它也是一个计算列。 它使用函数GETFILEEXTENSION（）获取文件扩展名 |
cached_file_size | 它以字节为单位给出文件大小。 它也是一个计算列，并在file_stream列上使用datalength函数来计算文件大小。 |
creation_time | 它捕获创建文件的日期和时间。 它使用数字精度34和数字刻度7。 我们可以在SSMS中使用以下查询来检查日期格式。 选择 CAST（'2019-03-08 12：35：29.1234567 +12：15'AS datetimeoffset（7））AS 'datetimeoffset' 它以以下格式给出输出。 |
| last_write_ 时间 | 它显示文件或文件夹内容的最新更新日期和时间。 它以与creation_time相同的格式及时输出输出。 |
last_access_time | 它以与creation_time相同的格式给出上次访问文件的日期和时间。 |
is_directory | 我们可以检查特定行是否指向文件或文件夹。 值0 –文件 值1 –资料夹 |
is_offline | 如果文件处于脱机状态，它将更新属性is_offline。 |
is_hidden | 它指文件的隐藏属性。 |
is_readonly | 它是指文件的只读属性。 |
is_archive | 它提供有关存档属性的信息。 |
is_system | 它提供有关系统文件属性的信息。 |
is_temporary | 如果文件是临时文件，则更新此标志。 |
In my earlier articles on SQL FILESTREAM, we needed to define the FILESTREAM table, and it does not capture any predefined schemas or table columns. The SQL Server FILETABLE gives the advantage of capturing all metadata in the FILETABLE. We do not need to specify any metadata column while creating FILETABLE.
在我先前关于SQL FILESTREAM的文章中，我们需要定义FILESTREAM表，并且该表不捕获任何预定义的架构或表列。 SQL Server FILETABLE具有捕获FILETABLE中所有元数据的优点。 创建FILETABLE时，无需指定任何元数据列。
SQL FILESTREAM does not have control over the files in the container. SQL Server uses this metadata as per the file property. For example, if is_readonly attribute for a particular file is set to one, SQL Server does not allow making any changes in that particular file. We do not this control in the SQL FILESTREAM table.
SQL FILESTREAM无法控制容器中的文件。 SQL Server根据文件属性使用此元数据。 例如，如果特定文件的is_readonly属性设置为1，则SQL Server不允许对该特定文件进行任何更改。 我们不在SQL FILESTREAM表中使用此控件。
SQL服务FILETABLE中的键和约束 (Keys and Constraints in SQL Serve FILETABLE)
In the following screenshot, you can see the Keys and Constraint in the SQL Server FILETABLE. These are automatically created by SQL Server. We do not need to create these Keys and Constraint explicitly.
在下面的屏幕快照中，您可以在SQL Server FILETABLE中看到“键和约束”。 这些由SQL Server自动创建。 我们不需要显式创建这些键和约束。
SQL Server creates the following key on FILETABLE.
SQL Server在FILETABLE上创建以下键。
- Primary key on [path_locator] [path_locator]上的主键
- Foreign key on [parent_path_locator] [parent_path_locator]上的外键
- Unique key on [stream_id] [stream_id]上的唯一键
- Unique key on combination of [parent_path_locator] and [name] [parent_path_locator]和[name]组合时的唯一键
限制条件： (Constraints:)
SQL Server FILETABLE contains the following constraints
SQL Server FILETABLE包含以下约束
- Check constraints: These constraints ensure the columns having valid values. For example, it checks for the valid filename using it 检查约束：这些约束确保列具有有效值。 例如，它使用它检查有效的文件名
- Default constraints: these constraints set a default value for a file. For example by default [is_offline] column contains value 0 默认约束：这些约束为文件设置默认值。 例如，默认情况下[is_offline]列包含值0
In SQL Server FILESTREAM, we cannot access the FILESTREAM container from SQL Server. We need to go to FILESTREAM container and access the files. SQL Server FILETABLE allows control over the directories and files. We can right click on a FILETABLE and click on Explore FileTable Directory.
在SQL Server FILESTREAM中，我们无法从SQL Server访问FILESTREAM容器。 我们需要转到FILESTREAM容器并访问文件。 SQL Server FILETABLE允许控制目录和文件。 我们可以右键单击FILETABLE，然后单击探索 FileTable 目录 。
It opens the root path of the FILETABLE in the following format. In this format, it uses the machine name, the instance-level, FILESTREAM share name, database-level folder name and FileTable-level folder.
它以以下格式打开FILETABLE的根路径。 在这种格式下，它使用计算机名称，实例级别，FILESTREAM共享名称，数据库级别的文件夹名称和FileTable级别的文件夹。
\\servername\instance-share\database-directory\FileTable-directory
\\服务器名\实例共享\数据库目录\ FileTable目录
In the following screenshot, you can see the FILETABLE root folder structure as per specified format.
在下面的屏幕截图中，您可以按照指定格式查看FILETABLE根文件夹结构。
Create a new folder under the SQL Server FILETABLE directory. Right click and go to new and Folder. Provide a suitable name for the folder.
在SQL Server FILETABLE目录下创建一个新文件夹。 右键单击并转到新建和文件夹。 为文件夹提供合适的名称。
In the following screenshot, we created Rajendra folder under SQLShackDemo.
在以下屏幕截图中，我们在SQLShackDemo下创建了Rajendra文件夹。
Now, go to SSMS and select the records from FILETABLE.
现在，转到SSMS，然后从FILETABLE中选择记录。
select * from [dbo].[SQLShackDemoDocuments]
It automatically inserts the record in the FILETABLE. SQL Server uses the constraints and function to read the FILETABLE directory and access the record into FILETABLE. Remember, SQL Server FILESTREAM does not provide this functionality.
它会自动将记录插入FILETABLE中。 SQL Server使用约束和函数来读取FILETABLE目录，并将记录访问到FILETABLE中。 请记住，SQL Server FILESTREAM不提供此功能。
Now open the folder properties in Windows and compare with the inserted record in FILETABLE. In the following screenshot, you can see the corresponding values in SQL Server FILETABLE.
现在，在Windows中打开文件夹属性，并与FILETABLE中插入的记录进行比较。 在下面的屏幕快照中，您可以在SQL Server FILETABLE中看到相应的值。
Let us create another folder inside the parent folder Rajendra. I renamed this folder Raj.
让我们在父文件夹Rajendra内创建另一个文件夹。 我将此文件夹重命名为Raj。
Reaccess the SQL Server FILETABLE, and we can see the parent-child relationship in FILETABLE as well. In the following screenshot, you can notice that path_locator and parent_path_locator columns are lined together. It is due to the primary and foreign key relationship.
重新访问SQL Server FILETABLE，我们也可以在FILETABLE中看到父子关系。 在以下屏幕截图中，您会注意到path_locator和parent_path_locator列排在一起。 这是由于主键和外键的关系。
结论 (Conclusion)
In this article, we explored the overview of SQL Server FILETABLE and its comparison with SQL Server FILESTREAM. In further articles, we will explore FILETABLE feature in more detail.
在本文中，我们探讨了SQL Server FILETABLE的概述及其与SQL Server FILESTREAM的比较。 在其他文章中，我们将更详细地探讨FILETABLE功能。
目录 (Table of contents)
翻译自: https://www.sqlshack.com/sql-server-filetable-the-next-generation-of-sql-filestream/
filetable
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
