Working with Filetables

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\FileStream_Path\FileTable_Path\Filetable_name

Where

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

Transact SQL

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.

Syntax

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.

UPDATE [3-SEP-2012]:

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.