What is FileTable? How to set it up and test it?

• What is FileTable (or FileTables, Microsoft uses both interchangeably)?

FileTable is a special table storing file and directory in Windows share in the database.

It is a specialized user table with a pre-defined schema that stores FILESTREAM data, as well as file and directory hierarchy information and file attributes.

• What's for?

FileTable utilizes SQL Server for the storage and management of unstructured data that is currently residing as files on file servers.

Enterprises can move this data from file servers into FileTables to take advantage of integrated administration and services provided by SQL Server.

At the same time, they can maintain Windows application compatibility for their existing Windows applications that see this data as files in the file system.

It provides support for the Windows file namespace and compatibility with Windows applications to the file data stored in SQL Server.

In other words, you can store files and documents in special tables in SQL Server called FileTables, but access them from Windows applications as if they were stored in the file system, without making any changes to your client applications.

• How to do it?

--Step 1: Enable filestream at the instance level

       /*

       0 = Disables FILESTREAM support for this instance.

       1 = Enables FILESTREAM for Transact-SQL access.

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

       */

EXEC sp_configure filestream_access_level, 2
RECONFIGURE

--Step 2: Provide a FILESTREAM Filegroup at the Database Level

ALTER DATABASE myDatabase
ADD FILEGROUP FileTable_FG
CONTAINS FILESTREAM;

--Step 3: Provide a filename for filetables at the Database Level. Need to create the C:\SQLData directory

ALTER DATABASE myDatabaseADD FILE(
NAME= 'myFileTable_File',FILENAME = 'C:\SQLData\myFileTable_File')
TO FILEGROUP FileTable_FG;GO

--Step 4: Enable Non-Transactional Access and specify a Directory for FileTables at the Database Level

ALTER DATABASE myDatabase 

SET FILESTREAM ( NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N'myFileTable_File' )

--Step 5: Create a FileTable at the Database Level

CREATE TABLE DemoFileTable AS FILETABLEWITH(FILETABLE_DIRECTORY = 'myFileTable_File',FILETABLE_COLLATE_FILENAME = database_default);GO

--Step 6: Test it

--a. type \\YourMachinName\ at the run or searching box, that should open the windows shares on your network--b. alternatively, click computer, then click network at the bottom of the left panel.--c. go to the following path and create some files: \\YourMachineNamec\MSSQLSERVER\myFileTable_File\myFileTable_File--d. go to SMSS and right-click the FileTable "DemoFileTable", choose "Select Top 1000 Rows"
/* The columns are provided by MS
SELECT TOP 1000 [stream_id]      ,[file_stream]      ,[name]      ,[path_locator]      ,[parent_path_locator]      ,[file_type]      ,[cached_file_size]      ,[creation_time]      ,[last_write_time]      ,[last_access_time]      ,[is_directory]      ,[is_offline]      ,[is_hidden]      ,[is_readonly]      ,[is_archive]      ,[is_system]      ,[is_temporary]FROM [myDatabase].[dbo].[DemoFileTable]*/--e. if the contents of the file is string-type data, you can convert it from binary to characters.

select convert(varchar(max),file_stream),name
FROM [myDatabase].[dbo].[DemoFileTable]

How to delete records in chunk to avoid blocking?

SET NOCOUNT ON

WHILE 1=1

BEGIN

     DELETE TOP (1000)

     FROM YourTable

     WHERE FilterConditions

END