For an instructor lead, in-depth look at learning SQL click below.
Dealing with large amounts of data has its challenges, and one of the solutions SQL Server provides is the use of FileStream. This feature integrates the SQL Server Database Engine with the NTFS file system by storing varbinary(max) binary data as files on the file system. This approach can effectively handle BLOBs (Binary Large Object).
Setting Up FileStream
Before you can use the FileStream feature, you have to enable it on the instance level. This can be done using SQL Server Configuration Manager.
But if you need to enable it on the database level, you can use the following SQL code:
|
1 2 3 4 5 6 7 8 9 10 11 |
CREATE DATABASE MyDB ON PRIMARY ( NAME = MyDB, FILENAME = 'c:\data\mydb.mdf'), FILEGROUP FileStreamGroup1 CONTAINS FILESTREAM( NAME = MyDB_FG1, FILENAME = 'c:\data\mydbfg1') LOG ON ( NAME = MyDB_Log, FILENAME = 'c:\data\mydblog.ldf'); GO |
Create a Table to Use FileStream
With the FileStream feature enabled, you can create a database table. Here is an example of a table that contains filestream data:
|
1 2 3 4 5 6 7 8 |
CREATE TABLE MyTable ( Id UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE, Name VARCHAR(255), Doc VARBINARY (MAX) FILESTREAM NULL ) |
Inserting Data into FileStream
You can insert data into a filestream column using the T-SQL INSERT statement. Below is an example:
|
1 2 3 4 |
INSERT INTO MyTable VALUES (NEWID (), 'Document', CAST ('This is the content of my document.' AS VARBINARY(MAX))); |
Reading Data from FileStream
To read data from a filestream column, you use the standard T-SQL SELECT statement. Here is an example:
|
1 2 3 |
SELECT * FROM MyTable WHERE Name = 'Document'; |
Remember, when dealing with large amounts of data, FileStream is a handy feature to have in your toolkit as an SQL Server professional. Understanding how to implement and manipulate it using SQL code will significantly enhance your database management skills.