SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Creating a Filetable

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

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.
   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 ,
 from AuthorDrafts;

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');

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

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest


Leave a comment on the original post [voiceofthedba.wordpress.com, opens in a new window]

Loading comments...