A few weeks ago I posted an article on how to import and export unstructured data in SQL Server by using the “image” data type.
In this article I will show how you can manipulate unstructured data in SQL Server 2008 or later by using the FILESTREAM feature. FILESTREAM allows storing unstructured data (i.e. music, video, documents, etc.) onto the NTFS file system and manipulating it via the Database Engine.
Step 1: Enabling FILESTREAM in SQL Server
Before using the FILESTREAM feature you have to enable it. To this end you need to navigate to SQL Server Configuration Manager and under SQL Server Services right click on the SQL Server instance, select properties and in the “FileStream” tab check “Enable FILESTREAM for Transact-SQL access” and “Enable FILESTREAM for file I/O access”:
The last step for enabling FILESTREAM is from within SSMS, to open a new query window and execute the following T-SQL statement and then restart the SQL Server instance:
EXEC sp_configure filestream_access_level, 2; GO RECONFIGURE GO
Note: the available filestream access levels are:
- 0: Disables FILESTREAM support for this instance.
- 1: Enables FILESTREAM for Transact-SQL access.
- 2: Enables FILESTREAM for Transact-SQL and Win32 streaming access.
Step 2: Creating a FILESTREAM-Enabled Database
For creating a FILESTREAM-enabled database you just need to include a FILESTREAM filegroup. For example:
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
Step 3: Creating a Table for Storing FileStream Data
USE [FileStreamDB]; GO CREATE TABLE dbo.Files ( [Id] [uniqueidentifier] ROWGUIDCOL NOT NULL UNIQUE, [FileName] VARCHAR(100), [ActualFile] VARBINARY(MAX) FILESTREAM NULL ); GO
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).
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!
Step 4: Storing FileStream Data
Now, let’s store the file in our FILESTREAM-enabled database and table that was created earlier:
USE [FileStreamDB]; GO INSERT INTO dbo.Files VALUES (newid (), 'SampleImage.png', (SELECT * FROM OPENROWSET(BULK 'C:\Testing2SampleImage.png',SINGLE_BLOB) AS x) ) GO
What we just did with the above example, shows a small glimpse of the real power of FILESTREAM, that is leveraging the performance and rich APIs of the Windows file system and at the same time maintaining consistency between structured and unstructured data in SQL Server.
FILESTREAM actually works like a bridge between structured and unstructured data via a combination of transactional data and file system access and can be extremely useful in cases where you have many binary objects like images and videos and you want to store it in SQL Server and being able to access it with the speed of the file system.
Watch a Live Demonstration of the FILESTREAM Feature!Enroll Now with Discount!
Featured Online Courses:
- Boost SQL Server Database Performance with In-Memory OLTP
- Essential SQL Server Administration Tips
- SQL Server Fundamentals – SQL Database for Beginners
- Essential SQL Server Development Tips for SQL Developers
- The Philosophy and Fundamentals of Computer Programming
- .NET Programming for Beginners – Windows Forms with C#
- Introduction to Data Science and SQL Server Machine Learning
- Introduction to Azure SQL Database for Beginners
- SQL Server 2019: What’s New – New and Enhanced Features
- 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
Check our Related SQL Server Development Articles:
- The set identity_insert Command in SQL Server
- The Import Flat File Wizard in SSMS v17.3
- Listing all Tables of a Linked Server’s Database
- Executing Heavy Set-Based Operations Against VLDBs in SQL Server
- How to Import and Export Unstructured Data in SQL Server – FileTables
- How to Import and Export Unstructured Data in SQL Server – FILESTREAM
- How to Import and Export Unstructured Data in SQL Server – The IMAGE Datatype
- Manipulating EXCEL 97-2003 Worksheets with the OPENROWSET Command
Subscribe to our newsletter and stay up to date!
Check out our latest software releases!
Check our eBooks!
Rate this article:
Reference: SQLNetHub (https://www.sqlnethub.com)
Artemakis Artemiou is a Senior SQL Server Architect, Author, and a 9 Times Microsoft Data Platform MVP (2009-2018). He has over 15 years of experience in the IT industry in various roles. Artemakis is the founder of SQLNetHub and TechHowTos.com. 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. Artemakis currently serves as the President of the Cyprus .NET User Group (CDNUG) and the International .NET Association Country Leader for Cyprus (INETA).