SQL Server FILESTREAM AND FILETABLE

If the stored objects are on average larger than 1 MB, FileStream is the best option because of the faster read access. For smaller objects, storing varbinary(max) BLOBs in the database often provides better streaming performance.

FILESTREAM: save large files to DB, FileStream data type is implemented as a varbinary (max) column.

  

FILETABLE: (sub type of filestream), support direct access to file from file system and support full-text search

SETTING UP FILESTREAM AND FILETABLE IN SQL SERVER

1) For Sql Server 2019: windows > run > SQLServerManager15.msc

2) Enable FILESTREAM access level with Transact-SQL code

--0 = Disables FILESTREAM support for this instance.

--1 = Enables FILESTREAM for Transact-SQL access.

--2 = Enables FILESTREAM for Transact-SQL and Win32 streaming access.

USE Master

GO

EXEC sp_configure filestream_access_level ,2

RECONFIGURE

3) Create folder "FILESERVER" on c:\

4)  Create DB with name "FILESDB"

CREATE DATABASE FILESDB

ON PRIMARY (NAME = FS,FILENAME = 'C:\FILESERVER\FILESDB.mdf'),

FILEGROUP FileStreamFS CONTAINS FILESTREAM(NAME = FStream, FILENAME = 'c:\FILESERVER\Fs')

LOG ON  (NAME = FILESDBLog, FILENAME = 'C:\FILESERVER\FILESDBLog.ldf')

WITH FILESTREAM (NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N'FILESERVER')

GO

5) Create table with name "FileTable"

CREATE TABLE Documents AS FileTable

GO

6) View Table on HD by right click on FileTable and choose "Explore FileTable Directory"