filetable
SQL Server FILETABLE provides benefits over SQL FILESTREAM available from SQL Server 2012. We can manage unstructured objects in the file system using SQL Server. It stores metadata in particular fixed schema tables and columns. It provides compatibility between an object in SQL Server table and Windows.
与SQL Server 2012中可用SQL FILESTREAM相比，SQL Server FILETABLE具有许多优点。我们可以使用SQL Server管理文件系统中的非结构化对象。 它将元数据存储在特定的固定模式表和列中。 它提供了SQL Server表中的对象与Windows之间的兼容性。
In my previous article, we explored that you can work with SQL Server FILETABLE directory in a similar way of a regular file or folder. You can do copy-paste, drag-drop files and it inserts data into FILETABLE as well using the Windows API functions and extended functions of SQL Server. It allows updating objects similar to a transaction table. SQL Server also maintains the critical information of files such as file type, extension, create date, modified date, and file properties (read-only, hidden, archive).
在上一篇文章中，我们探讨了您可以使用SQL Server FILETABLE目录的方式与常规文件或文件夹的方式类似。 您可以复制，拖放文件，也可以使用Windows API函数和SQL Server的扩展功能将数据插入FILETABLE。 它允许更新类似于事务表的对象。 SQL Server还维护文件的关键信息，例如文件类型，扩展名，创建日期，修改日期和文件属性（只读，隐藏，存档）。
In this article, we will explore a few user cases related to SQL FILETABLE.
在本文中，我们将探讨一些与SQL FILETABLE相关的用户案例。
先决条件 (Prerequisites)
- A SQL Server instance with FILESTREAM feature and configured filestream_access_level 具有FILESTREAM功能和配置的filestream_access_levelSQL Server实例
- A SQL Server database with SQL FILETABLE 具有SQL FILETABLESQL Server数据库
SQL FILETABLE用户案例 (SQL FILETABLE User Cases)
方案1：取消SQL FILETABLE的正在运行的事务 (Scenario 1: Cancel a running transaction for a SQL FILETABLE)
Suppose you are trying to insert or update records in SQL Server FILETABLE in a transaction using t-SQL. You need to cancel this transaction while it is still not completed. Usually, in a regular transaction table on SQL Server, if we cancel any query, it goes through a rollback process and UNDO any changes made so far. Once the rollback is finished, it should not contain records that are not committed. If there are many transactions, it might take longer for the recovery to take place.
假设您尝试使用t-SQL在事务中插入或更新SQL Server FILETABLE中的记录。 您仍需要取消该交易，但仍未完成。 通常，在SQL Server的常规事务表中，如果我们取消任何查询，它将经历回滚过程，并撤消到目前为止所做的任何更改。 回滚完成后，它不应包含未提交的记录。 如果有很多事务，则恢复可能需要更长的时间。
In SQL FILETABLE transaction, SQL Server maintains transaction consistency as well. If we remove any files from FILETABLE directory, it removes that particular row from SQL Server FILETABLE as well. If we cancel any transaction in SQL FILETABLE, it should remove any inserted objects as part of the rollback process. We should not have record in FILETABLE as well as in the FILETABLE directory.
在SQL FILETABLE事务中，SQL Server也会维护事务一致性。 如果我们从FILETABLE目录中删除任何文件，它也会从SQL Server FILETABLE中删除该特定行。 如果我们取消SQL FILETABLE中的任何事务，则它应在回滚过程中删除所有插入的对象。 我们不应该在FILETABLE以及FILETABLE目录中都有记录。
Let us look in the current environment set up for SQL FILETABLE. We have the following records in existing FILETABLE. In the following query, we selected a few columns from FILETABLE for demonstration purpose.
让我们看一下为SQL FILETABLE设置的当前环境。 现有的FILETABLE中有以下记录。 在以下查询中，我们从FILETABLE中选择了几列进行演示。
SELECT [name]
,[file_type]
,[cached_file_size]
,[creation_time]
,[last_write_time]
,[last_access_time]
FROM [SQLFileTable].[dbo].[SQLShackDemoDocuments]
We have 14 records in SQL FILETABLE including files and folders in FILETABLE directory.
我们在SQL FILETABLE中有14条记录，包括FILETABLE目录中的文件和文件夹。
In the following screenshot, you can see FILETABLE directory right click on table and click on Explore FILETABLE directory option)
在以下屏幕截图中，您可以看到FILETABLE目录，右键单击表，然后单击Explore FILETABLE directory选项）
Now, execute following query to insert an object into SQL Server FILETABLE. We are using BEGIN TRAN to start a transaction. We are using any commit transaction in this query.
现在，执行以下查询以将对象插入SQL Server FILETABLE。 我们正在使用BEGIN TRAN开始交易。 我们在此查询中使用任何提交事务。
BEGIN TRAN
INSERT INTO [dbo].[SQLShackDemoDocuments]
([name],[file_stream])
SELECT
'DSC_0312.JPG',
* FROM OPENROWSET(BULK N'c:\SQLShack\DSC_0312.JPG',
SINGLE_BLOB) AS FileData
GO
We have not committed this transaction yet. Once we have inserted a record into SQL Server FILETABLE, try to access the records from it in SSMS. It keeps executing the select statement and does not return any result. In the following screenshot, you can see query is running from more than 6 minutes. It does not give any error message as well. It will continue to run forever without any error message.
我们尚未落实此交易。 将记录插入SQL Server FILETABLE后，尝试在SSMS中从中访问记录。 它继续执行select语句，并且不返回任何结果。 在以下屏幕截图中，您可以看到查询运行了6分钟以上。 它也不会给出任何错误消息。 它将继续永远运行，没有任何错误消息。
Execute the following query to check why it is not giving any records. In this 66 is the session id in which we try to access records from FILETABLE.
执行以下查询以检查为什么不提供任何记录。 在这66个会话ID中，我们尝试在其中访问FILETABLE的记录。
sp_who2 66
We have a blocking session, and it shows block by session id 88. We are running an insert statement using session id 88.
我们有一个阻塞的会话，它按会话ID 88显示阻塞。我们正在使用会话ID 88运行插入语句。
We can see newly inserted object into FILETABLE directory.
我们可以看到新插入的对象到FILETABLE目录中。
If we try to access this file as well, it does not open. It is an image file. Therefore, OS try to open it in Windows Photo Viewer.
如果我们也尝试访问此文件，则该文件不会打开。 这是一个图像文件。 因此，操作系统尝试在Windows Photo Viewer中将其打开。
I get following error message that file is being edited in another program.
我收到以下错误消息，该文件正在另一个程序中进行编辑。
Now, let us cancel or kill SPID for the select statement that was running for long. We can access this FILETABLE using NOLOCK hint in SQL Server. It allows reading and showing uncommitted data as well. It shows the FILETABLE record quickly using NOLOCK hint.
现在，让我们取消或取消运行很长时间的select语句的SPID。 我们可以使用SQL Server中的NOLOCK提示访问此FILETABLE。 它也允许读取和显示未提交的数据。 它使用NOLOCK提示快速显示FILETABLE记录。
SELECT [name]
,[file_type]
,[cached_file_size]
,[creation_time]
,[last_write_time]
,[last_access_time]
FROM [SQLFileTable].[dbo].[SQLShackDemoDocuments] (nolock)
where name= ‘DSC_0312.JPG'
It gives a result for our recently inserted record in SQL FILETABLE with Begin Tran statement.
它使用Begin Tran语句为SQL FILETABLE中最近插入的记录提供结果。
We do not want to commit this transaction, therefore, execute command rollback transaction in same transaction window. It performs a rollback of the particular transaction. Let us view records in FILETABLE. It rollbacks the transaction, and we do not see any records in SQL Server FILETABLE for our inserted object. We do not need to use NOLOCK hint because rollback is already finished.
我们不想提交此事务，因此，请在同一事务窗口中执行命令回滚事务 。 它执行特定事务的回滚。 让我们查看FILETABLE中的记录。 它回滚事务，并且在SQL Server FILETABLE中看不到插入对象的任何记录。 我们不需要使用NOLOCK提示，因为回滚已经完成。
We can verify object in FILETABLE directory as well. SQL Server removes the file as part of the Rollback process.
我们也可以在FILETABLE目录中验证对象。 SQL Server在回滚过程中删除了该文件。
SQL Server maintains transactional consistently in SQL FILETABLE as well. However, we need to ensure that no transactions should be left open. We should either Commit or Rollback any transaction; otherwise, it might cause an issue to access FILETABLE due to blocking. It is always beneficial to keep transaction smaller to avoid any issues.
SQL Server也在SQL FILETABLE中始终保持事务性。 但是，我们需要确保没有任何交易处于开放状态。 我们应该提交或回滚任何交易； 否则，由于阻塞，可能导致访问FILETABLE的问题。 保持交易规模较小以避免任何问题总是有益的。
方案2：更新SQL FILETABLE中的记录并从目录访问对象 (Scenario 2: Update a record in SQL FILETABLE and accessing an object from the directory)
Let us look at another scenario for SQL Server FILETABLE. Suppose you are doing an update for an existing object in FILETABLE using SSMS. Execute the following code to update FILETABLE.
让我们看一下SQL Server FILETABLE的另一种情况。 假设您正在使用SSMS对FILETABLE中的现有对象进行更新。 执行以下代码以更新FILETABLE。
BEGIN TRAN
UPDATE [SQLFileTable].[dbo].[SQLShackDemoDocuments] SET name='UpdateFILETABLE_new.docx' where name='UpdateFILETABLE.docx'
We can see update filename in FILETABLE directory, note that we have not committed update query yet.
我们可以在FILETABLE目录中看到更新文件名，请注意，我们尚未提交更新查询。
Now, try to open this document using Microsoft Word, and you get an error message that file is currently in use.
现在，尝试使用Microsoft Word打开此文档，您会收到一条错误消息，指出该文件正在使用中。
SQL Server does not allow opening this file using Windows application because it is being locked by the Update transaction.
SQL Server不允许使用Windows应用程序打开此文件，因为它已被Update事务锁定。
Let us look at this situation in a different way. Before we move ahead, execute a Rollback Transaction to be a previous state of SQL FILETABLE.
让我们以不同的方式看待这种情况。 在继续之前，请执行回滚事务以将其作为SQL FILETABLE的先前状态。
Let us modify the highlighted file. Right-click on the file and click on Edit. It opens the document in Microsoft Word file.
让我们修改突出显示的文件。 右键单击该文件，然后单击“ 编辑”。 它将在Microsoft Word文件中打开文档。
Once the file is open for modification, access the FILETABLE records using NOLOCK hint. We can see a new record in SQL Server FILETABLE. When we open any file in Windows, it creates a hidden file, and you can see the entry for a hidden file in the table.
打开文件进行修改后，使用NOLOCK提示访问FILETABLE记录。 我们可以在SQL Server FILETABLE中看到一条新记录。 当我们在Windows中打开任何文件时，它将创建一个隐藏文件，并且您可以在表中看到隐藏文件的条目。
杀死SQL FILETABLE的Open句柄 (Kill Open handles for SQL FILETABLE)
Suppose a large number of users used to access FILETABLE directory and make changes to it. You might have faced this situation in regular files in Windows. In this case, you need to check processes causing a lock on a particular file and kill that process manually to release locks.
假设有大量的用户用来访问FILETABLE目录并对其进行更改。 在Windows中的常规文件中，您可能已经遇到过这种情况。 在这种情况下，您需要检查对特定文件造成锁定的进程，并手动终止该进程以释放锁定。
If you face this situation in SQL FILETABLE, do you still have to go through windows tools and kill the process?
如果您在SQL FILETABLE中遇到这种情况，是否仍然需要通过Windows工具杀死进程？
No, SQL Server allows monitoring the open handles associated with each object in SQL Server FILETABLE. We can use dynamic management view sys.dm_filestream_non_transacted_handles. We get one row per open file handle using this DMV. If there is any handle get closed and you rerun DMV to check open handle for FILETABLE, particular entry is removed from the output.
不，SQL Server允许监视与SQL Server FILETABLE中的每个对象关联的打开句柄。 我们可以使用动态管理视图sys.dm_filestream_non_transacted_handles。 使用此DMV，每个打开的文件句柄将获得一行。 如果关闭了任何句柄，然后重新运行DMV以检查FILETABLE的打开句柄，则会从输出中删除特定的条目。
Execute the following code to view current open associated handles in our SQL FILETABLE database.
执行以下代码，以查看SQL FILETABLE数据库中当前打开的关联句柄。
SELECT *
FROM sys.dm_filestream_non_transacted_handles
WHERE fcb_id IN
( SELECT request_owner_id FROM sys.dm_tran_locks );
GO
We get following output from this DMV.
我们从该DMV获得以下输出。
SELECT * FROM sys.dm_filestream_non_transacted_handles;
GO
In the following screenshot, you can see all columns in the output of this DMV.
在以下屏幕截图中，您可以在此DMV的输出中看到所有列。
We can see the following important columns in this output.
我们可以在此输出中看到以下重要列。
- database_id: SQL Server FILETABLE database ID database_id： SQL Server FILETABLE数据库ID
- object_id: SQL Server FILETABLE ID object_id： SQL Server FILETABLE ID
- handle_id: It is a unique handle ID handle_id：这是唯一的句柄ID
- State: It shows the current state of the handle. You can have an active, closed or killed state 状态 ：显示手柄的当前状态。 您可以处于活跃，已关闭或已杀死状态
- opened_file_name: It gives the source file name along with its FILETABLE directory path opens_file_name：给出源文件名及其FILETABLE目录路径
- Database_directory_name: We get FILETABLE directory name Database_directory_name：我们得到FILETABLE目录名
- Login_name: We get the principal name that is the owner of Open handle Login_name：我们获得的主体名称是Open句柄的所有者
- read_access: it shows it the file is opened for reading access read_access：显示文件已打开以进行读取访问
- Write_access: it shows if the file is opened for write access Write_access：显示文件是否已打开以进行写访问
We have checked open handle associated with SQL FILETABLE. We can close non-transactional file handles to SQL Server FILETABLE using stored procedure sp_kill_filestream_non_transacted_handles. We need to pass FILETABLE name to close all open file handles in SQL Server.
我们已经检查了与SQL FILETABLE相关的打开句柄。 我们可以使用存储过程sp_kill_filestream_non_transacted_handles关闭SQL Server FILETABLE的非事务性文件句柄。 我们需要传递FILETABLE名称以关闭SQL Server中的所有打开的文件句柄。
Suppose we want to close all associated open handles, therefore, give the FILETABLE in this stored procedure. Execute the following code in SQL FILETABLE database.
假设我们要关闭所有关联的打开句柄，因此，在此存储过程中提供FILETABLE。 在SQL FILETABLE数据库中执行以下代码。
EXEC sp_kill_filestream_non_transacted_handles @table_name = 'SQLShackDemoDocuments';
GO
In the output, you can see it killed three non-transactions FILESTREAM handles for SQL Server FILETABLE database.
在输出中，您可以看到它杀死了SQL Server FILETABLE数据库的三个非事务FILESTREAM句柄。
If we have multiple non-transactional handles for a FILETABLE object, we can close a particular file handle as well. We need to pass handle_id in a stored procedure in the FILETABLE database.
如果FILETABLE对象有多个非事务句柄，则也可以关闭特定的文件句柄。 我们需要在FILETABLE数据库的存储过程中传递handle_id。
Suppose, we have following open handles, and we want to close a particular file handle id 653.
假设我们有以下打开的句柄，并且我们想关闭特定的文件句柄ID 653。
Execute the following code, and in the output, you can see it killed one non-transactional handle.
执行以下代码，在输出中，您可以看到它杀死了一个非事务性句柄。
EXEC sp_kill_filestream_non_transacted_handles @handle_id = 653;
GO
It kills open handle for FILETABLE however we can still see an entry for a hidden file in SQL Server FILETABLE. It does not impact accessing FILETABLE however we can remove this particular row using delete statement.
它会杀死FILETABLE的打开句柄，但是我们仍然可以在SQL Server FILETABLE中看到隐藏文件的条目。 它不会影响对FILETABLE的访问，但是我们可以使用delete语句删除此特定行。
结论 (Conclusion)
In this article, we explored user cases associated with SQL FILETABLE. It is an important aspect to know how SQL Server FILETABLE works in case of any transactional failure or open file handles. We will continue covering more on SQL FILETABLE in my next article.
在本文中，我们探讨了与SQL FILETABLE相关的用户案例。 了解任何事务失败或打开文件句柄的情况下SQL Server FILETABLE的工作方式是一个重要方面。 在我的下一篇文章中，我们将继续介绍SQL FILETABLE。
目录 (Table of contents)
翻译自: https://www.sqlshack.com/sql-server-filetable-use-cases/
filetable
2427
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
