Good evening (or good morning) friends all around the globe! Wherever you are, whatever you do, wishing you a nice rest of the day (or night!) 🙂
This is the last of a series of posts that deal with the task of importing and exporting unstructured data in SQL Server.
This article discusses the FileTables feature which builds on top of SQL Server FILESTREAM technology. FileTables was first introduced in SQL Server 2012.
What is FileTables in SQL Server?
The FileTables feature allows the user to store unstructured data (i.e. files, documents, images, etc.) in special tables in SQL Server called FileTables, but being able to access them from the file system. To this end, if you have an application that needs to access unstructured data, you can directly access them from the File System even though the data is stored into FileTables in SQL Server.
Example of Using SQL Server FileTables
Enough with the talking, let’s see an example of using this great feature.
First, let’s enable FILESTREAM (this is a prerequisite – for more info, see the second article of this series):
Then, let’s create a FILESTREAM-enabled database (make sure to update the path “C:\BlogSQLData” if you are using a different one):
CREATE DATABASE FileStreamDB ON PRIMARY ( NAME = FileStreamDBData, FILENAME = 'C:BlogSQLDatafilestreamDB_data.mdf'), FILEGROUP FileStreamGroup_1 CONTAINS FILESTREAM( NAME = FileStreamDBFS, FILENAME = 'C:BlogSQLDatafilestream1') LOG ON ( NAME = FileStreamDBLogs, FILENAME = 'C:BlogSQLDatafilestreamDB_log.ldf'); GO
Then we need to enable non-transactional access at the database level as well as specify the directory for FileTables:
ALTER DATABASE FileStreamDB SET FILESTREAM ( NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N'FileTablesDir') GO
Now it’s time to create the FileTable:
USE FileStreamDB; GO CREATE TABLE DocumentStore AS FileTable WITH ( FileTable_Directory = 'FileTablesDir', FileTable_Collate_Filename = database_default ); GO
Check out FileTables
OK, it’s time for the magic to take place. Let’s check the contents of the FileTable:
Now, let’s open the FileTable directory in Explorer, and by accessing it directly from the Windows File System, copy some files:
Now, let’s check again the contents of the FileTable from SSMS:
And that’s it! As you can see in the above screenshot, by copying the two files from Windows Explorer directly into the FileTable directory, the corresponding two records have been created in the FileTable in the SQL Server instance! Now you can access the files either from the SQL Server Database Engine via T-SQL or directly from the Windows File System!
FileTables along with the FILESTREAM technology, enables the user with more options when it comes to storing binary objects in SQL Server. The seamless integration of FileTables with the Windows File System (NTFS) allows the user to store large binary objects in fast speeds and at the same time to be able to use the powerful features of SQL Server’s Database Engine to traverse this data (i.e. full-text search, semantic search, etc.).
Strengthen you SQL Server Development Skills – Enroll to our Online Course!
Check our online course titled “Essential SQL Server Development Tips for SQL Developers”
(special limited-time discount included in link).
Via the course, you will sharpen your SQL Server database programming skills via a large set of tips on T-SQL and database development techniques. The course, among other, features over than 30 live demonstrations!
Featured Online Courses:
- Introduction to Azure SQL Database for Beginners
- SQL Server 2019: What’s New – New and Enhanced Features
- SQL Server Fundamentals – SQL Database for Beginners
- Essential SQL Server Administration Tips
- Boost SQL Server Database Performance with In-Memory OLTP
- Essential SQL Server Development Tips for SQL Developers
- Working with Python on Windows and SQL Server Databases
- Introduction to Computer Programming for Beginners
- .NET Programming for Beginners – Windows Forms with C#
- Introduction to SQL Server Machine Learning Services
- Entity Framework: Getting Started – Complete Beginners Guide
- How to Import and Export Data in SQL Server Databases
- Learn How to Install and Start Using SQL Server in 30 Mins
- A Guide on How to Start and Monetize a Successful Blog
- How to Import and Export Unstructured Data in SQL Server – FILESTREAM
- How to Import and Export Unstructured Data in SQL Server – The IMAGE Datatype
- Azure Cosmos DB: Learn by Example
- Learn Azure SQL Database: Creating your First Database
- How to Create an Azure SQL Server Virtual Machine
- How to Backup a SQL Server Database from On-Premises to Azure Storage
- What is Azure Advisor?
- … all our Azure-related articles
Check our latest software releases!
Easily generate SQL code snippets with Snippets Generator!
Convert static T-SQL to dynamic and vice versa with Dynamic SQL Generator.
Secure your SQL Server instances with DBA Security Advisor.
Benchmark SQL Server memory-optimized tables with In-Memory OLTP Simulator.
Rate this article:
Reference: SQLNetHub.com (https://www.sqlnethub.com)
Artemakis Artemiou is a Senior Database and Software Architect, Certified Database, Cloud and AI professional, Author, a 9 Times Microsoft Data Platform MVP (2009-2018). He has over 20 years of experience in the IT industry in various roles. Artemakis is the founder of SQLNetHub. Artemakis is the creator of the well-known software tools Snippets Generator and DBA Security Advisor. Also, he is the author of many eBooks on SQL Server. Moreover, Artemakis teaches on Udemy, you can check his courses here.