在SQL Server FILETABLEs中管理数据-CSDN博客

SQL Server FILETABLE is a next generation feature of SQL FILESTREAM. We can use it to store unstructured objects into a hierarchal directory structure. SQL Server manages SQL FILETABLE using computed columns and interacts with the OS using extended functions. We can manage SQL FILETABLEs similar to a relational table.

SQL Server FILETABLE是SQL FILESTREAM的下一代功能。我们可以使用它来将非结构化对象存储到分层目录结构中。 SQL Server使用计算所得的列管理SQL FILETABLE，并使用扩展功能与OS进行交互。我们可以管理类似于关系表SQL FILETABLE。

Before we proceed with this article, to follow along, please prepare the environment using my earlier articles (see TOC at bottom).

在继续本文之前，请先使用我以前的文章（请参见底部的目录）准备环境。

先决条件 (Prerequisites)

A SQL Server instance with FILESTREAM feature
具有FILESTREAM功能SQL Server实例
Configure filestream_access_level for T-SQL and Windows streaming
为 T-SQL和Windows流配置filestream_access_level
A SQL FILETABLE database having SQL FILESTREAM filegroup
具有SQL FILESTREAM文件组SQL FILETABLE数据库
A SQL FILETABLE
一个SQL文件表

探索一个FILETABLE (Explore a FILETABLE)

Connect to a SQL instance and expand the FILETABLE database. Right click on SQL Server FILETABLE SQLShackDemoDocuments and click on Explore FileTable Directory.

连接到SQL实例并展开FILETABLE数据库。右键单击“ SQL Server FILETABLE SQLShackDemoDocuments” ，然后单击“ 浏览 FileTable 目录”。

You get the FILETABLE directory in the following format.

您将获得以下格式的FILETABLE目录。

\\servername\instance-share\database-directory\FileTable-directory

\\服务器名\实例共享\数据库目录\ FileTable目录

In the SQL FILESTREAM table, we can insert records using an Insert statement. We can use an Insert statement for SQL Server FILETABLE as well.

在SQL FILESTREAM表中，我们可以使用Insert语句插入记录。我们也可以对SQL Server FILETABLE使用Insert语句。

Execute the following query to insert the object into FILETABLE. We need to only provide value of 2 columns [name] and [file_stream] in FILETABLE.

执行以下查询以将对象插入FILETABLE。我们只需要在FILETABLE中提供2列[name]和[file_stream]的值。

Select records from a FILETABLE and you can see in the following image, and all other columns are populated automatically using computed functions.

从FILETABLE中选择记录，您可以在下图中看到，所有其他列都使用计算函数自动填充。

Now, go to the FILETABLE directory, and you can see the following file into it.

现在，转到FILETABLE目录，您可以在其中看到以下文件。

Explore SQL Server FILETABLE in directory

SQL FILESTREAM provides an internal value to every object and file. It is not easy to identify the FILESTREAM object by looking in the directory. It does not store file extension either. Windows does not recognize the FILESTREAM object because it is doesn’t have an extension associated with it. But a SQL Server FILETABLE does make it easy for us in the following ways.

SQL FILESTREAM为每个对象和文件提供内部值。通过在目录中查找来标识FILESTREAM对象并不容易。它也不存储文件扩展名。 Windows无法识别FILESTREAM对象，因为它没有与之关联的扩展名。但是，通过以下方式，SQL Server FILETABLE确实使我们很容易。

SQL FILETABLE stores the original object name and extension for each object
SQL FILETABLE存储每个对象的原始对象名称和扩展名
Windows recognize these file. We can work with these files similar to a regular file
Windows会识别这些文件。我们可以像处理普通文件一样使用这些文件
The FILETABLE also maintains file properties and characteristics such as read-only
FILETABLE还维护文件属性和特征，例如只读

In the following screenshot, you can see the difference in SQL FILESTREAM and SQL FILETABLE objects in both naming convention and extensions.

在以下屏幕截图中，您可以在命名约定和扩展名中看到SQL FILESTREAM和SQL FILETABLE对象的差异。

Comparison of SQL FILESTREAM and SQL Sever FILETABLE

Suppose we want to insert a large number of unstructured objects into a SQL FILETABLE. We can use custom stored procedure or SSIS packages to loop through all files in the source directory and insert into the SQL FILETABLE. We explored this method in my earlier article (see TOC at the bottom).

假设我们要在SQL FILETABLE中插入大量非结构化对象。我们可以使用自定义存储过程或SSIS包循环遍历源目录中的所有文件，并将其插入SQL FILETABLE。我们在之前的文章中探讨了这种方法（请参阅底部的目录）。

We can use a similar method for SQL FILETABLEs as well. In this article, we will cover a better approach with using the FILETABLE feature.

我们也可以对SQL FILETABLE使用类似的方法。在本文中，我们将介绍使用FILETABLE功能的更好方法。

SQL Server FILETABLE allows dragging and dropping objects into SQL FILETABLE from the source folder. SQL Server interacts with these files using system functions and inserts records in SQL FILETABLE. Let us understand it using an example.

SQL Server FILETABLE允许将对象从源文件夹拖放到SQL FILETABLE中。 SQL Server使用系统功能与这些文件进行交互，并将记录插入SQL FILETABLE中。让我们通过一个例子来理解它。

In the following screenshot, we performed drag and drop activity for 27 objects from source folder to FILETABLE directory.

在以下屏幕截图中，我们对27个对象从源文件夹到FILETABLE目录执行了拖放活动。

Drag file into SQL Server FILETABLE directory

It starts a copy task in Windows. You can see the status of copying each file as per the following screenshot.

它在Windows中启动复制任务。您可以按照以下屏幕截图查看复制每个文件的状态。

Once the copy is completed, we can see all files in SQL Server FILETABLE directory.

复制完成后，我们可以在SQL Server FILETABLE目录中看到所有文件。

SQL FILESTREAM also allows copying any file in a FILESTREAM container. It does not create an association of these files into FILESTREAM table. You also get an error message about the unwanted object while taking backup of FILESTREAM database.

SQL FILESTREAM还允许复制FILESTREAM容器中的任何文件。它不会在FILESTREAM表中创建这些文件的关联。在备份FILESTREAM数据库时，您还会收到有关不需要的对象的错误消息。

SQL Server FILETABLE is aware of any activity in FILETABLE directory. It automatically reads each file and inserts a corresponding entry in SQL FILETABLE. In the following screenshot, we have an entry for each file we copied in SQL FILETABLE directory.

SQL Server FILETABLE知道FILETABLE目录中的任何活动。它会自动读取每个文件，并在SQL FILETABLE中插入一个相应的条目。在下面的屏幕截图中，我们为复制到SQL FILETABLE目录中的每个文件都有一个条目。

We can quickly move files around SQL FILETABLE directory without worrying about FILETABLE metadata.

我们可以在SQL FILETABLE目录中快速移动文件，而不必担心FILETABLE元数据。

For this demo, I created a folder image into SQL Server FILETABLE root directory. I moved all images into this folder.

对于此演示，我在SQL Server FILETABLE根目录中创建了一个文件夹映像。我将所有图像移到该文件夹中。

Move files into a new directory for SQL Server FILETABLE

If you look at the SQL FILETABLE directory, we have following parent-child relationship.

如果查看SQL FILETABLE目录，我们具有以下父子关系。

Parent Directory: \\Kashish\sql2019\FileTableContainer\SQLShackDemo
父目录：\\ Kashish \ sql2019 \ FileTableContainer \ SQLShackDemo
Child Directory: \\Kashish\sql2019\FileTableContainer\SQLShackDemo\Images
子目录：\\ Kashish \ sql2019 \ FileTableContainer \ SQLShackDemo \ Images

A FILETABLE maintains parent-child relationship using path_locator and parent_path_locator columns.

FILETABLE使用path_locator和parent_path_locator列维护父子关系。

Now, query the SQL FILETABLE again. In the following screenshot, you will notice the following.

现在，再次查询SQL FILETABLE。在以下屏幕截图中，您将注意到以下内容。

Writing to SQL Server FILETABLE.xlsx is having 写入SQL Server FILETABLE.xlsx具有path_locator value but does not have path_locator值，但没有parent_path_locator parent_path_locator

We have placed an object into a parent folder; therefore, it doesn’t have a parent associated with it.

我们已经将对象放入父文件夹；因此，它没有与之关联的父项。

parent_path_locator value equal to parent_path_locator值等于上一个条目的path_locator of the previous entry. We have created the images folder inside the parent folder (SQLShackDemo) path_locator 。我们已经在父文件夹（SQLShackDemo）中创建了images文件夹。

Parent-child relationsip in SQL Server FILETABLE

You can better understand our parent-child relationship association using SQL FILETABLEs with the following image.

您可以通过下图使用SQL FILETABLEs更好地了解我们的父子关系。

We can easily remove objects from SQL Server FILETABLE similar to a regular windows file. I have deleted a few objects from the images folder. SQL Server removes the metadata from SQL FILETABLE automatically.

我们可以像常规Windows文件一样轻松地从SQL Server FILETABLE中删除对象。我已经从图像文件夹中删除了一些对象。 SQL Server自动从SQL FILETABLE中删除元数据。

SQL FILESTREAM does not allow placement of objects directly in a FILESTREAM container. Suppose we want to update content in a Word document, in SQL FILESTREAM, we need to update it using an update query. SQL Server prepares a new copy of the object in FILESTREAM container and removes the old file using a garbage collector process if it not required by the recovery process. The garbage collector process depends upon your recovery model.

SQL FILESTREAM不允许将对象直接放置在FILESTREAM容器中。假设我们要更新Word文档中的内容，在SQL FILESTREAM中，我们需要使用更新查询来更新它。 SQL Server在FILESTREAM容器中准备对象的新副本，如果恢复过程不需要，则使用垃圾回收器过程删除旧文件。垃圾收集器的过程取决于您的恢复模型。

Updating a FILETABLE

更新FILETABLE

Let us explore updating SQL Server FILETABLE. For this demo, I am going to create a new Microsoft Office Word Document. Right click on the desired directory, go to New and click on Microsoft Office Word Document.

让我们探讨更新SQL Server FILETABLE。对于此演示，我将创建一个新的Microsoft Office Word文档。右键单击所需的目录，转到“ 新建” ，然后单击“ Microsoft Office Word文档” 。

I renamed this Word document to UpdateFILETABLE.docx.

我将此Word文档重命名为UpdateFILETABLE.docx。

Currently, it has no content, therefore you can see 0 KB size as well.

当前，它没有内容，因此您也可以看到0 KB的大小。

Open this Word document and place content inside it. I have placed following image in this and saved the doc. As stated earlier, We can do an in-place update to FILETABLE object directly. It removes a barrier with SQL FILESTREAM in which we require update query and create a new copy of the object. It requires more space as well to store files.

打开此Word文档并将内容放入其中。我将以下图像放置在此并保存了文档。如前所述，我们可以直接对FILETABLE对象进行就地更新。它消除了SQL FILESTREAM的障碍，在其中我们需要更新查询并创建对象的新副本。它还需要更多空间来存储文件。

Once we saved Word document, you can query SQL Serve FILETABLE, and you will see modified document timestamp in the last_write_time column.

保存Word文档后，您可以查询SQL Server FILETABLE，并且您会在last_write_time列中看到修改后的文档时间戳。

You can match this value with file modified time in the following screenshot.

您可以在以下屏幕截图中将该值与文件修改时间进行匹配。

重命名SQL FILETABLE中的对象 (Rename objects in a SQL FILETABLE)

Suppose we want to rename an object in SQL Server FILETABLE directory. We can right click on that file and rename it.

假设我们要重命名SQL Server FILETABLE目录中的对象。我们可以右键单击该文件并将其重命名。

In the following screenshot, I renamed highlighted image file.

在以下屏幕截图中，我重命名了突出显示的图像文件。

Once we query FILETABLE, we can see modified name appearing in this list.

查询FILETABLE之后，我们可以看到修改后的名称出现在此列表中。

Now, let us change the properties of a particular file to Read-Only. Right click on the file and go to properties. Put a check on Read-Only and click Apply.

现在，让我们将特定文件的属性更改为只读。右键单击该文件，然后转到属性。选中“只读”，然后单击“ 应用” 。

Select the record in SQL Server FILETABLE, and you can see the is_readonly flag is set to 1 for that file.

在SQL Server FILETABLE中选择记录，您可以看到该文件的is_readonly标志设置为1。

Read-Only properties of a file in SQL Server FILETABLE

We can do it other ways as well. In the following query, we set read-only and archive properties for an image file.

我们也可以通过其他方式做到这一点。在以下查询中，我们为图像文件设置了只读和存档属性。

USE [SQLFileTable]
GO
 
UPDATE [dbo].[SQLShackDemoDocuments]
   SET 
      [is_readonly] = 1
      ,[is_archive] = 1
     
 WHERE name='IMG_20140316_133802.jpg'
GO

Once you have executed this query, open file properties in FILESTREAM directory and we can see the changes at OS level as well.

执行完此查询后，在FILESTREAM目录中打开文件属性，我们也可以在操作系统级别看到更改。

Modified properties of SQL Server FILETABLE

使用t-SQL创建文件夹 (Create a Folder using t-SQL)

Previously we created a folder in SQL Server FILETABLE directory in OS level. We can also create a folder using t-SQL as well in FILETABLE directory.

以前，我们在操作系统级别SQL Server FILETABLE目录中创建了一个文件夹。我们还可以在FILETABLE目录中使用t-SQL创建一个文件夹。

In the following query, we are creating two directory SQLShack and DemoFolder. We need to specify an is_directory flag to 1 so that SQL Sever can create an appropriate directory in FILETABLE.

在以下查询中，我们将创建两个目录SQLShack和DemoFolder。我们需要将is_directory标志指定为1，以便SQL Sever可以在FILETABLE中创建适当的目录。

INSERT INTO [SQLShackDemoDocuments](Name, is_directory)
 
 values('SQLShack',1)
 
 INSERT INTO [SQLShackDemoDocuments] (Name, is_directory)
 
 values('DemoFolder',1)

In the following screenshot, you can see newly created directories inside FILETABLE root directory.

在以下屏幕截图中，您可以在FILETABLE根目录中看到新创建的目录。

限制用户对FILETABLE执行插入 (Restrict a user from performing inserts on a FILETABLE)

Suppose we want to restrict a SQL user from performing inserts on SQL Server FILETABLE. We can explicitly deny permissions to that user similar to a relational table.

假设我们要限制SQL用户在SQL Server FILETABLE上执行插入操作。我们可以像关系表一样，明确拒绝对该用户的权限。

First, let us create a SQL user and assign db_reader and db_writer permission on FILETABLE database.

首先，让我们创建一个SQL用户，并在FILETABLE数据库上分配db_reader和db_writer权限。

USE [master]
GO
CREATE LOGIN [DemoLogin] WITH PASSWORD=N'DemoLogin', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [SQLFileTable]
GO
CREATE USER [DemoLogin] FOR LOGIN [DemoLogin]
GO
USE [SQLFileTable]
GO
ALTER ROLE [db_datareader] ADD MEMBER [DemoLogin]
GO
USE [SQLFileTable]
GO
ALTER ROLE [db_datawriter] ADD MEMBER [DemoLogin]
GO

Once user is created, run following query to deny permissions for Insert command.

创建用户后，运行以下查询以拒绝“插入”命令的权限。

Deny insert on [dbo].[SQLShackDemoDocuments] to [DemoLogin]

Now, connect to SQL user with DemoLogin we created and try to run an insert command on a FILETABLE.

现在，使用我们创建的DemoLogin连接到SQL用户，并尝试在FILETABLE上运行插入命令。

INSERT INTO [dbo].[SQLShackDemoDocuments]
([name],[file_stream])
SELECT
 'Writing to SQL Server FILETABLE.xlsx',
  * FROM OPENROWSET(BULK N'c:\SQL\Writing to SQL Server FILETABLE.xlsx',
 SINGLE_BLOB) AS FileData
GO
We get an error message that you do not have permissions to use the bulk load statement.

We get an error message that you do not have permissions to use the bulk load statement.

我们收到一条错误消息，提示您您无权使用批量加载语句。

Similarly, we can control permissions to update, delete, select on SQL FILETABLE as per our requirements.

同样，我们可以根据我们的要求控制对SQL FILETABLE进行更新，删除和选择的权限。

识别特定文件的FILEPATH (Identify the FILEPATH for a particular file)

Suppose we have a large number of directories and files in SQL Server FILETABLE. We can search for a particular file using Windows Search; however, it is possible from t-SQL as well. We need to use system functions FileTableRootPath() and GetFileNamespacePath to retrieve relevant information.

假设我们在SQL Server FILETABLE中有大量目录和文件。我们可以使用Windows搜索来搜索特定文件。但是，也可以从t-SQL中获得。我们需要使用系统函数FileTableRootPath（）和 GetFileNamespacePath来检索相关信息。

In the following query, we want to identify the directory path for IMG_20140316_133802. We specified a variable @name to hold objectname. It is the name of the object we want to search in FILETABLE directory.

在以下查询中，我们要标识IMG_20140316_133802的目录路径。我们指定了一个变量@name来保存对象名。它是我们要在FILETABLE目录中搜索的对象的名称。

Execute the following query to get directory information for that particular file.

执行以下查询以获取该特定文件的目录信息。

DECLARE @name varchar(1000)
DECLARE @filetableroot varchar(256)
DECLARE @filepath varchar(1000)
Set @name='IMG_20140316_133802.jpg';
 
SELECT @filetableroot = FileTableRootPath();
 
SELECT @filetableroot + file_stream.GetFileNamespacePath() as FILEPATH
FROM [dbo].[SQLShackDemoDocuments]
WHERE Name = @name;

有关SQL FILETABLE的重要事实 (Important facts about SQL FILETABLE)

We can perform standard insert, update, delete on FILETABLE
我们可以在FILETABLE上执行标准的插入，更新，删除
We can use DML and DDL triggers on the FILETABLE
我们可以在FILETABLE上使用DML和DDL触发器
FILETABLE can be dropped similar to a relational table
可以删除FILETABLE，类似于关系表
SQL Server ensures transactions consistency in FILETABLE
SQL Server确保FILETABLE中的事务一致性
We can use built-in functions to retrieve information about FILETABLE and interaction with OS
我们可以使用内置函数来检索有关FILETABLE以及与OS交互的信息
We can use copy,robocopy and drag-drop operations through WindowsExplorer for direct T-SQL data inserts in a FILETABLE
我们可以通过WindowsExplorer使用复制，自动复制和拖放操作在FILETABLE中直接进行T-SQL数据插入
We can access objects in FILETABLE directory without authorization from the SQL Server
我们可以在未经SQL Server授权的情况下访问FILETABLE目录中的对象

结论 (Conclusion)

In this article, we talked about SQL FILETABLE features and its integration with the OS. We can use unstructured store data in the file system and keeps metadata in a database. FILETABLE allows non-transactional access. SQL Server ensures consistency as well for SQL Server FILETABLE. We will cover a few more aspects in my next article.

在本文中，我们讨论了SQL FILETABLE功能及其与OS的集成。我们可以在文件系统中使用非结构化存储数据，并将元数据保留在数据库中。 FILETABLE允许非事务访问。 SQL Server还确保SQL Server FILETABLE的一致性。在我的下一篇文章中，我们将涉及更多方面。