MSSQL’s Hidden Power: FileTable for File Management and Real-Life Usage

MSSQL is widely recognized as a relational database management system (RDBMS), but its advanced file management capabilities often go unnoticed by many developers. One of these features is FileTable, which uniquely integrates database management and file system access. In this article, we’ll explore what FileTable is, how it works, and how it can be applied in real-world scenarios.

What is FileTable?

FileTable is an advanced feature of SQL Server that allows files to be stored within a database while also being accessible through the Windows file system. Introduced in SQL Server 2012, FileTable provides a powerful solution for large-scale file management and document management projects by combining the strengths of relational database management with traditional file system access.

FileTable is especially useful in projects requiring large-scale file storage, such as documents, images, videos, and other media files. With FileTable, these files are managed with SQL Server’s robust data management capabilities while being accessed by users as if they were stored in a local file system.

What Can Be Done with FileTable?

Store Files in a Database: Files are stored in the SQL Server database, benefiting from the security, backup, and management features that come with database systems.
File System Access: Users can access the stored files through Windows Explorer or other file systems as though they were working with regular folders.
T-SQL File Management: You can query and manage files using T-SQL commands, just like a normal SQL table.
Full-Text Search: SQL Server’s full-text search feature allows you to search within the content of files.

How Does FileTable Work?

FileTable is built on top of SQL Server’s FILESTREAM technology. FILESTREAM allows large files to be stored on an NTFS file system, optimizing database performance by managing file data outside the database. FileTable builds on this by allowing access and management of these files directly through SQL Server.

When you create a FileTable, you are essentially creating a table in SQL Server that stores files. However, instead of traditional data, these tables store file content, and users can access the files through the file system. SQL Server manages these files in the background, allowing you to use SQL Server’s security and backup features.

Steps to Use FileTable:

Enable FILESTREAM:

EXEC sp_configure filestream_access_level, 2;
RECONFIGURE;

Set Up the Database:

CREATE DATABASE LegalDocsDB
ON PRIMARY
( NAME = LegalDocsDB,
  FILENAME = 'C:\LegalDocsDB\LegalDocsDB.mdf' )
LOG ON
( NAME = LegalDocsDB_log,
  FILENAME = 'C:\LegalDocsDB\LegalDocsDB_log.ldf' )
FILESTREAM
( NAME = LegalDocsDB_FS,
  FILENAME = 'C:\LegalDocsDB\FileTable_FS' );

Create the FileTable:

CREATE TABLE MyFileTable AS FileTable
WITH ( FILETABLE_DIRECTORY = 'MyDocuments' );

Real-Life Usage of FileTable: Document Management System in a Law Firm

Now, let’s look at a real-world example of how FileTable can be used. A law firm generates a large number of case files, contracts, and other legal documents daily. The firm needs a Document Management System (DMS) to store, organize, and quickly retrieve these files.

The Law Firm’s Requirements

Centralized Storage: All documents need to be securely stored in one location.
Secure Access: Only authorized users should be able to access certain documents.
Full-Text Search on Files: Users need to search within the content of documents.
User-Friendly Interface: Users should continue to manage files as they are accustomed to through the file system.

Solution: Document Management Using FileTable

The law firm decided to solve these issues using MSSQL’s FileTable feature. Documents are stored in the SQL Server database through FileTable, but users can access these files as if they were stored on the local file system.

File System Access: The firm uploads all case files to SQL Server via FileTable. Users, however, can manage the files through Windows Explorer without needing to interact with SQL Server directly.

Full-Text Search for Finding Documents: With MSSQL’s full-text search feature, users can search for keywords within the content of files. For example, a lawyer can run the following SQL query to find all documents containing the term “contract”:

SELECT name, file_stream
FROM DavaDosyalari
WHERE CONTAINS(file_stream, 'contract');

Security and Backup: Files are protected by SQL Server’s security features and included in the database’s regular backup processes.

Advantages

Integrated File and Database Management: Files are managed through SQL Server’s robust data management capabilities while remaining accessible through the file system.
Backup and Recovery: Since files are part of the SQL Server database, they are included in regular database backups and can be restored easily.
User-Friendly: Users can manage files using their familiar file system interface without knowing about the underlying SQL Server processes.

Conclusion

MSSQL’s FileTable is a powerful tool for managing large file storage projects. By combining SQL Server’s security, backup, and management features with file system accessibility, FileTable provides an ideal solution for document management or media storage projects. In real-world applications like a law firm’s document management system, FileTable enables the secure storage and quick retrieval of files, making it a valuable asset for businesses that manage large volumes of documents.

This feature offers a hidden yet highly efficient approach to file management for MSSQL users.

Gökhan Aravi