How do you create a filetable? I assume you’ve enabled Filestream and created a filegroup for your filestream and filetable data. Then you just do this:

-- Create a filetable
CREATE TABLE AuthorDrafts 
  AS FileTable
GO

The only optional part of this statement is the table name. No other options, no columns, no schema needed. The FileTable has a fixed schema, which is mostly metadata about the files that you put in it.

If I were to select from this table, I’d use this statement. I’m not showing all the columns in the results since there are a lot, but they are in the select.

-- check the table.
select 
   stream_id ,
          file_stream ,
          name ,
          path_locator ,
          parent_path_locator ,
          file_type ,
          cached_file_size ,
          creation_time ,
          last_write_time ,
          last_access_time ,
          is_directory ,
          is_offline ,
          is_hidden ,
          is_readonly ,
          is_archive ,
          is_system ,
          is_temporary
 from AuthorDrafts;
go

Most of these are really meta data about the file. If I were to drop a table in the share, I’d see results like this:

Putting files inside the table is really a drag and drop from Windows. I can get the share name for my filetable from :

-- check the share
select  FileTableRootPath('dbo.AuthorDrafts');
go

If I paste this in Explorer, I see my file:

I can drag and drop, or use any scripting commands (Powershell, VBScript, etc) to move files in and out of this share, and they will appear in my table.

It’s that easy to start working with FileTables. How you use them in your application? That’s a whole other series of posts. I’ll work on a few examples you can use over time.

Filed under: Blog Tagged: Filetable, sql server, syndicated, T-SQL