For an instructor lead, in-depth look at learning SQL click below.
One of the powerful features available in SQL Server is FileTable, a functionality that makes it possible to store and manage unstructured data alongside structured data in the SQL Server database. In today’s post, we are going to delve into the world of FileTables, and by the end, I hope you’ll have the necessary knowledge to harness this feature in your SQL Server applications.
What is a FileTable?
Introduced in SQL Server 2012, FileTable is designed to leverage the existing capabilities of FILESTREAM, yet takes it a step further by providing storage for file and directory data. FileTable allows applications to work with data exactly as if it were stored in the file system, making integration of SQL Server applications with existing applications, tools, and scripts, which today manage files and directories, much easier.
Let’s Set Up a FileTable
Before we demonstrate how to use FileTable, it’s important to note that any database that you plan to use FileTables in, must have FILESTREAM enabled. You can easily accomplish this using the following SQL Server script:
|
1 2 3 4 |
EXEC sp_configure filestream_access_level, 2 RECONFIGURE |
This script configures the filestream access level on the SQL Server instance.
Creating Our First FileTable
To create a new FileTable, we can use the CREATE TABLE statement with the AS FILETABLE clause. The following script creates a new FileTable named ‘MyFileTable’:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
USE TestDB GO CREATE TABLE MyFileTable AS FileTable WITH ( FileTable_Directory = 'Directory', FileTable_Collate_Filename = database_default ) GO |
Working with FileTable
Now, once a FileTable is created, you could easily insert, update, delete rows from it, as you would do with any other SQL Server table. Here’s a simple query to retrieve all rows from our created FileTable:
|
1 2 3 |
SELECT * FROM MyFileTable |
Conclusion
FileTable is a great feature in SQL Server, making file storage, alongside structured data, more seamless and easier to manage. It’s a powerful tool to have in your toolkit, especially for managing unstructured data in modern applications.
Summary:
To wrap up, we’ve covered what a FileTable is, how to set it up, and given a brief primer on interacting with it using standard SQL queries. I hope you’ve found this introduction to FileTable helpful and are now inspired to dive deeper into this powerful SQL Server feature.