SQL Server 2012 FileTable- is a file stored IN the database?

Question

If I create a FileTable in SQL server 2012, and then was to drop a 4G file onto the NT filesystem (that was in the filestream), would that entire 4G file be read into the table's filestream column?

Is SQL in fact making a COPY of my 4G file? Or does the filestream column represent a pointer to my 4G file, which it begins to read on a query?

Im just trying to figure out if I added 100G of data to my file system, would that add 100G of data size to my DB.

Can someone help explain how this works? And even better point me to some docs with more detail than the MS/MSDN 'how-to' stuff?

EDIT: It's interesting- if I drop a 1G file in the FT dir, and then do a select the file_stream column contains all the data for that file (I think). But if I do sp_spaceused FileTableTb before and after dropping in that file the table size does not change. Perhaps this is evidence that the select opens the stream to the file and reads it in, but other than that the data is NOT stored in the table?

Answer 1

You are correct, the data is not stored in the database but in the filesystem itself, it is also important to note that the file is not "read in" to the database when it is placed on the filesystem, implying that it is copied to another location, it is actually stored as an NTFS file and the FileStream APIs show the file metadata inside the filetable.

The filetable is built on FileStream technology that was introduced in SQL 2008 but allows you to directly modify the data via windows explorer.

When you create your database, you specify that one of the filegroups is a filestream filegroup, this isn't like a normal filegroup and is actually a series of NTFS folders called Data Containers, this is where your files actually get stored, you can't modify this folder directly, but you can look at the contents (not that there's much human readable stuff in here though, you can find your actual files in here though if you look hard enough through the GUID folders and oddly named files :)).

The file share that you use to copy the files into the table is actually a representation of the data inside these data containers that is presented to Windows explorer via a filter driver which uses the streaming APIs to make the changes to the data containers and add the rows to the filetable etc.