For an instructor lead, in-depth look at learning SQL click below.
FileStream in SQL Server is a feature that allows storing and managing unstructured data effortlessly in sql server. This feature is a godsend for storing and managing unstructured binary large object data. This includes documents, media files, backup files, and so on.
Enable FileStream in SQL Server
To utilize FileStream, you must first enable it in the SQL Server. You can do this using SQL Server Configuration Manager. But if you prefer to use T-SQL, you can employ the following code:
|
1 2 3 4 |
EXEC sp_configure filestream_access_level, 2 RECONFIGURE |
Here, ‘filestream_access_level’ is the feature name of FileStream in SQL Server Configuration Manager, and 2 signifies that all FileStream functionalities are enabled.
Establish a Database with FileStream
Next step is to establish a database with FileStream. This is how it’s done:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
CREATE DATABASE FileStreamDB ON PRIMARY ( Name = FSGroup1, FileName = 'c:\data\FileStreamDB.mdf'), FILEGROUP FileStreamGroup1 CONTAINS FILESTREAM( NAME = FSGroup1_dir, FILENAME = 'c:\data\FileStreamDB_fs') LOG ON ( Name = FileStreamDB_log, FileName = 'c:\data\FileStreamDB_log.ldf') WITH FILESTREAM ( NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N'FileStreamDB_dir') |
With the WITH FILESTREAM argument, the FILESTREAM feature is enabled for this database, and all FILESTREAM data associated with FileStreamDB will be stored in the directory ‘FileStreamDB_dir’.
Add FileStream Data to a Table
To demonstrate, let’s see how you can add FileStream data to a table:
|
1 2 3 4 5 6 |
CREATE TABLE DocumentStore( DocumentID int IDENTITY PRIMARY KEY, Document VARBINARY(MAX) FILESTREAM NULL, DocGUID UNIQUEIDENTIFIER NOT NULL ROWGUIDCOL UNIQUE DEFAULT NEWID ()) |
The above order creates a table named ‘DocumentStore’ with a DocumentID column as the primary key, a Document FileStream column to retain the file’s actual content, and DocGUID, a unique row GUID.
Inserting and Retrieving FileStream Data
Now, you can insert data into the DocumentStore table:
|
1 2 3 4 |
INSERT INTO DocumentStore (Document) SELECT * FROM OPENROWSET(BULK N'C:\Data\SampleFile.txt', SINGLE_BLOB) AS Document |
The SQL query will insert SampleFile.txt content into the Document column. You can then retrieve the data using a SELECT statement as follows:
|
1 2 3 |
SELECT Document FROM DocumentStore WHERE DocumentID = 1 |
OutputStream in SQL Server enables storing large, unstructured data in a database, maintaining the benefits of a SQL Server database with the convenience of accessing FileStream data like a regular file.
Once you have mastered the basics of FileStream, you can work wonders with SQL Server databases. Remember, convenience and efficiency are FileStream’s best aspects!