FileTable

One of the new features in SQL 2012 is a new type of table, the file table. The file table can store files and documents, and have those files available to Windows applications. You can drag and drop files into the file table’s directory, and they’ll be added to the table.
To create a file table, you’ll need to enable FILESTREAM access for the database.
FILESTREAM Pre-reqs
1) In SQL Server Configuration Manager, select the properties for your SQL instance. On the FileStream tab, enable FileStream.
2) In T-SQL, enable FileStream access.
exec sp_configure filestream_access_level, 2
reconfigure
go
— sp_configure options
— 0 = Disables FILESTREAM support for this instance.
— 1 = Enables FILESTREAM for Transact-SQL access.
— 2 = Enables FILESTREAM for Transact-SQL and Win32 streaming access.

3) Create FILESTREAM filegroup.
alter database TestDB
add filegroup TestFileStreamFileGroup contains filestream
go
alter database TestDB add file (
name = ‘TestFileStreamDataFile’,
filename = ‘C:\SQLData\TestFileStreamDataFile’)
to filegroup TestFileStreamFileGroup
go

4) Enable FILESTREAM non-transacted access
alter database TestDB
set filestream(non_transacted_access = full, directory_name = ‘TestFileStreamDirectory’) with no_wait
go

Now we can create the actual table. We don’t specify columns for this table, the schema for a file table is already defined. You can also right-click on ‘Tables’ and select ‘New File Table’.
create table TestFileTable as filetable
with (
filetable_directory = ‘TestFileStreamDataFile’,
filetable_collate_filename = database_default,
filetable_primary_key_constraint_name = PK_TestFileTable
);
go

Once the table has been created, you can right-click on the table in Object Explorer, and select ‘Explore FileTable directory’. You can add files to this directory, and they will be added to the table. A SELECT from the table will return a record for each file added.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: