For an instructor lead, in-depth look at learning SQL click below.
In today’s data-driven world, there’s an ever-growing need to handle large amounts of data, and this data is not limited to simple text or numeric data. Organizations frequently deal with large objects (LOBs), such as images, videos, and documents, which need to be stored and managed effectively while ensuring their integrity and security. Here, we’ll explore how SQL Server Filestream and Filetable features help us do just that.
Understanding the SQL Server Filestream
SQL Server Filestream is a set of SQL Server features that allows storing varbinary(max) BLOB data as files in the file system. This has a significant advantage in streaming that BLOB data. It keeps a varbinary(max) column in sync with an underlying file system file, providing transactional consistency between the two.
|
1 2 3 4 5 |
-- Enabling Filestream on the instance, execute this in SQL Server Management Studio EXEC sp_configure filestream_access_level, 2 RECONFIGURE |
SQL Server Filetables: An Overview
Filetables, a feature introduced in SQL Server 2012, set a layer on top of the Filestream feature. They let an application integrate its storage and data management components, and help in storing the files and directory hierarchy from the file system in a SQL Server database.
|
1 2 3 4 5 6 7 8 9 10 |
-- Creating a FileTable CREATE TABLE DocumentStore AS FileTable WITH ( FileTable_Directory = 'DocumentDirectory', FileTable_Collate_Filename = database_default ) GO |
Inserting Data into the Filetable
To load documents into our FileTable, you could use simple insert commands just as you would do for any other table, or you can drag-and-drop your documents into the directory.
|
1 2 3 4 5 |
-- Sample insertion of data INSERT INTO DocumentStore (name, file_stream) SELECT 'Document1', * FROM OPENROWSET(BULK N'C:\Data\Document1.pdf', SINGLE_BLOB) AS Document |
Reading Data from the Filetable
Accessing data from a Filetable is straightforward. You can query the table like any regular SQL table:
|
1 2 3 4 |
-- Sample selection of data SELECT name, file_type, cached_file_size FROM DocumentStore WHERE name = 'Document1' |
SQL Server Filestream and Filetable features confer a highly effective way to manage and access LOBs. Implementing the right strategies will significantly streamline data management procedures and ensure data integrity, enhancing an organization’s overall data capabilities.