Storing Files in SQL Server 2012

This is a big subject - my intention here is just to give an overview, and explain the concept involved.

FileTables

From SQL Server 2008 you have been able to store files in SQL Server using something called FileStreams.  You can now also create FileTables, which are tables which:

• contain (or at least give access to) files in Windows; but
• which you can access using standard Transact-SQL

Here's the SQL to create a file table, at its most simple:

Here's what this would create:

Here I've expanded the file table created to show its columns.  Most of the column meanings are easy enough to guess.

How FileTables Relate to Windows Explorer

You can right-click on any file table to see its contents in Windows Explorer:

Just choose this option and - providing that you have sufficient permissions - you'll see any files stored in this file table.

You can also run the following SQL to see where this file table's files are stored:

On my machine this returns:

The folder in which files are stored.

Typing the file path above into Windows Explorer and pressing Enter would therefore provide another way to get at the files stored in this file table.

Conclusion

Filetables integrate SQL Server with Windows Explorer - you can think of them as like ActiveDirectory, but easier to use.

Comments on this blog

This blog has one comment: