Please make sure to point to right directories as per your SQL server’s installation and data and log file storage policies.
We can create a FileTable now with the following command:
USE SQLDocuments
CREATE TABLE SQLDocumentStore AS FileTable
WITH
(
FileTable_Directory = ‘SQLDocuments’,
FileTable_Collate_Filename = database_default
)
We can now explore the file table in the Object Explorer – When I refreshed my Object Explorer this is what I saw:
Cool! We have a new section named FileTables and the table that we created is sitting in prettily! The column list already should have some of you salivating. Yes, all these are supported and can be directly set using FileTable!
Now to extend the magic, right click on the table name and click on “Explore File Table Directory” and it will open the folder in Winodws Explorer! Yes, Windows, Explorer !! Now you can just drag drop some documents into this folder like you would normally do with any Windows Folder
Let us go back to SQL Management Studio and run this query:
USE SQLDocuments
SELECT stream_id, [name], file_type, is_readonly
FROM SQLDocumentStore
There you go!! Whatever is in the folder is listed here automatically! Now isn’t this a great feature to manage all pour documents from any client app just like any other table? Even folders can be differentiated from files!
The Windows explorer share and the FileTable are identical twins; any stimuli, change in one place will have immediate response, change in the other.
With the help of APIs and client Applications, we can use this to improve flexibility and usability of applications that use File stream to a great extent.
Happy SQLing!