I have created an article sometime back about filetables but I have covered how to work with it. It’s now time for writing this article. In my earlier article you came to know what’s filetable and how it’s useful. Let’s now jump in to create and work with filetables. You need to satisfy all the pre-requisites which we have taken care in the earlier article.
Creating a FileTable
Use dbname GO CREATE TABLE [dbo].[YourFiletableName] AS FILETABLE --All the lines below are optional. Either you can specify it or ignore it --FILETABLE_DIRECTORY - You can specify the physical directory name, best practice is to use the samename as filetable --FILETABLE_COLLATE_FILENAME - Collation for filetable, either specify it or say it as DEFAULT ON [PRIMARY] FILESTREAM_ON [Filetable_Filegroup_name] WITH ( FILETABLE_DIRECTORY = N'YourFiletableName', FILETABLE_COLLATE_FILENAME = SQL_Latin1_General_CP1_CI_AS )
Inserting \ Loading BLOB Data’s into Filetable
Now you have created a filetable which is actually pointing to a windows directory. Now this Filetable is tightly integrated with Windows API which is smart enough to capture the changes occurring in windows directory. This helps us to insert data in the following methods
Windows File system
It’s very well-known process everyone is using this method day to day. Just copy and paste the file to the FileTable location. You can use any tool to do it, it can be xcopy, copy , robocopy and so on. Next question strikes in your mind is that how to find FileTable share details? You can use the query to get it
SELECT FileTableRootPath('Your_Filetable_Name') [FileTable Root Path]
Usually the path will be referred as below
Servername is your machine name
FileStream_Path is the share name which you gave during configuring Filestream in SQL Server services property
FileTable_Path is the directory name which you specify during database configuration for filestream.
Filetable_name is the name of the filetable
If you are going to load data through T-SQL then no need to worry about filepath. Whatever you BLOB data you store in this filetable will store the content as varbinary. You can find file_stream column in filetable which is the varbinary column. So when you insert the data into filetable you need to have the below columns compulsory in the query..
Name – This is the logical name of the file which you are going to store in Filetable
file_stream – Actual data, insert it as BLOB.
Let’s insert a document into Filetable
INSERT INTO [dbo].[FileTableName] ([name],[file_stream]) SELECT'File_Name', * FROM OPENROWSET(BULK N'Physical_file_Path_Including_Filename', SINGLE_BLOB) AS FileData GO
That’s it you have stored a document in filetable. Now this file entry will be available in Filetable and physical file can be found in Filetable path
Selecting Data from FileTable
It’s very simple select statement which we do for normal queries. Let’s select the data.
SELECT * FROM FileTable
We will get so many information about the file and it’s properties, to know all the columns in Filetable check the KB article http://msdn.microsoft.com/en-us/library/gg492084.aspx, lets customize it to get more information
SELECT FT.Name ,IIF(FT.is_directory=1,'Directory','Files') [File Category] ,FT.file_type [File Type] ,(FT.cached_file_size)/1024.0 [File Size (KB)] ,FT.creation_time [Created Time] ,FT.file_stream.GetFileNamespacePath(1,0) [File Path] ,ISNULL(PT.file_stream.GetFileNamespacePath(1,0),'Root Directory') [Parent Path] FROM YourFileTableName FT LEFT JOIN YourFileTableName PT ON FT.path_locator.GetAncestor(1) = PT.path_locator
Deleting a record from FileTable
Same normal tsql delete statement you can use it as show below
DELETE FROM YourFileTableName where name ='DocumentToDelete'
That’s it now you are familiar with working FileTables. I was discussing about Filetables with one of my friend and he asked me whether Filetable is transaction scoped and files that are worked in memory? As per logic it should be transaction scoped so I done a test on this and it’s below
In middle of a FileTable transaction if it got rolled back what will happen to the file created in Folder?
I have used the query below to test it and you know it’s successfully rolled back. The file has been cleared from the physical folder
BEGIN TRY BEGIN TRAN INSERT INTO [dbo].[Ftable] ([name],[file_stream]) SELECT'newdoc', * FROM OPENROWSET(BULK N'C:\Users\332252\Desktop\GCP\help.html', SINGLE_BLOB) AS FileData DECLARE @tmp TABLE (ID INT) INSERT INTO @tmp VALUES('SAGAR') COMMIT END TRY BEGIN CATCH PRINT 'Failed to Insert Data'; ROLLBACK END CATCH;
You are in middle of updating the FileTable, what if the user changes or deletes the file from Windows files system?
You can set properties or name of the files from FileTable so when you do that actually SQL Server locks the file and you aren’t supposed to modify anything from windows file system. Similarly if the file is opened in windows and if you try to alter the properties from SQL it will wait till you close the file. At any point of time the file can be used in either in windows or by SQL Server. Let’s update the name of the file
BEGIN TRAN UPDATE Ftable SET name='alteredfile.docx' where name='new.docx'
If you note it I haven’t committed or rollback the transaction. In the meantime if I try to update the filename or property from windows, it won’t allow.
What happens to files that is worked in memory (notepad or paint brush )
I thought this will work for notepad or paint brush, my assumption is wrong. I inserted one text file from T-SQL and tried to open it from windows, it thrown error stating “The request is not supported.”. I thought it’s based on extension & again I had a wrong assumption. In other words you won’t be able to use notepad or paint brush to view the files from Filetable, which is not supported.
INSERT INTO [dbo].[Ftable] ([name],[file_stream]) SELECT'YServer.txt', * FROM OPENROWSET(BULK N'D:\YServer.txt', SINGLE_BLOB) AS FileData GO
Useful queries for Filetable
--Script to get databases which are enabled for filetables USE DBname GO SELECT DB_NAME(database_id) [Database Name] ,non_transacted_access_desc ,directory_name FROM sys.database_filestream_options Where non_transacted_access <> 0 --Script to get details about filetable and system objects created on filetables USE DBname GO SELECT OBJECT_NAME(parent_object_id) [FileTableName] ,OBJECT_NAME(object_id) [System Defined Objects - FileTables] FROM sys.filetable_system_defined_objects --Script to get list of filetables available in the database USE DBname GO SELECT OBJECT_NAME (object_id) ,directory_name ,filename_collation_name FROM sys.filetables SELECT * from sys.tables WHERE is_filetable = 1
Assume you are editing a file from Windows file system and if you try to query the filetable through tsql then your query will be executing for a long. Use the query below to check which windows process ID is blocking the file.
--Script to retrieve windows process ID which blocks Filetable object SELECT DB_NAME (database_id) [Databae Name] ,OBJECT_NAME (object_id) [FileTableName], state_desc ,item_name ,opened_file_name ,open_time ,database_directory_name ,login_name ,CONVERT(INT,correlation_process_id) [Windows Process ID] FROM sys.dm_filestream_non_transacted_handles
I opened one word file and then executed the query above. I could get all the details about windows process
Now you found the windows process ID, to close the connection you can do it from windows task manager. Assume you don’t have windows login permission, for this purpose MS introduced a new procedure to kill non transact sessions.
sp_kill_filestream_non_transacted_handles [[ @table_name = ] ‘table_name’, [[ @handle_id = ] @handle_id]]
-- Close all open handles in the current database. EXEC sp_kill_filestream_non_transacted_handles -- Close all open handles in myFileTable. EXEC sp_kill_filestream_non_transacted_handles @table_name = 'YourFileTableName' -- Close a specific handle in myFileTable. EXEC sp_kill_filestream_non_transacted_handles . @table_name = 'YourFileTableName' , @handle_id = 0x00000 -- get this value from sys.dm_filestream_non_transacted_handles dmv
That’s it for this article, now I hope you will be familiar with Filetables. I’ll try to cover the rest of the things in next article.
I read in some post that we can open memory resident files in notepad or paint brush if we map Filetable share as a network drive. I tested this and it’s working perfectly so there is a workaround for memory resident files, just map the Filetable share as a network drive and you are allowed to read or write the files.