(Part 1 of 4 ) Why to use FILESTREAM or FILETABLE in SQL Server

FILESTREAM enables SQL Server-based applications to store unstructured data, such as documents and images, on the file system. Applications can leverage the rich streaming APIs and performance of the file system and at the same time maintain transactional consistency between the unstructured data and corresponding structured data.

FILESTREAM integrates the SQL Server Database Engine with an NTFS file system by storing varbinary(max) binary large object (BLOB) data as files on the file system. Transact-SQL statements can insert, update, query, search, and back up FILESTREAM data. Win32 file system interfaces provide streaming access to the data.

FILESTREAM uses the NT system cache for caching file data. This helps reduce any effect that FILESTREAM data might have on Database Engine performance. The SQL Server buffer pool is not used; therefore, this memory is available for query processing.

FILESTREAM is not automatically enabled when you install or upgrade SQL Server. You must enable FILESTREAM by using SQL Server Configuration Manager and SQL Server Management Studio.

 EXEC sp_configure filestream_access_level, 2
 RECONFIGURE
 GO
  

To use FILESTREAM, you must create or modify a database to contain a special type of filegroup.

 CREATE DATABASE WebApiFileTable
 ON PRIMARY
 (Name = WebApiFileTable,
 FILENAME = 'E:\filestreamsql\FTDB.mdf'),
 FILEGROUP FTFG CONTAINS FILESTREAM
 (NAME = WebApiFileTableFS,
 FILENAME='E:\filestreamsql\FS')
 LOG ON
 (Name = WebApiFileTableLog,
 FILENAME = 'E:\filestreamsql\FTDBLog.ldf')
 WITH FILESTREAM (NON_TRANSACTED_ACCESS = FULL,
 DIRECTORY_NAME = N'WebApiFileTable');
 GO
  

Then, create or modify a table so that it contains a varbinary(max) column with the FILESTREAM attribute.

 USE WebApiFileTable
 GO
 CREATE TABLE WebApiUploads AS FileTable
 WITH
 (FileTable_Directory = 'WebApiUploads_Dir');
 GO
   

After you complete these tasks, you can use Transact-SQL and Win32 to manage the FILESTREAM data.

 INSERT INTO [dbo].[WebApiUploads]
 ([name],[file_stream])
 SELECT
 'NewFile.docx', * FROM OPENROWSET(BULK N'd:\kk.docx', SINGLE_BLOB) AS FileData
 GO

 select * from WebApiUploads