Sql Server Filetable (since 2012)

1.) Filestream TSQL

A) Pepare SQL Server

1) configuration Manager / SQL-Server Db Service Propteries / Filestream Tab: Check all
2) EXEC sp_configure filestream_access_level, 2  RECONFIGURE
3) restart SQL Server Service

B) Prepare DATABASE

ALTER DATABASE testFileTable SET FILESTREAM (NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N'FilesDir')

alter database testFileTable  ADD FILEGROUP fsg CONTAINS FILESTREAM;
ALTER DATABASE testFileTable  ADD FILE ( NAME = N'fsf', FILENAME = N'D:\sqlData\filesteamtest\' ) TO FILEGROUP fsg;
ALTER DATABASE testFileTable  SET READ_COMMITTED_SNAPSHOT OFF WITH NO_WAIT;

C) Prepare TABLE

CREATE TABLE dbo.MyFiles AS FILETABLE
WITH
(
FILETABLE_DIRECTORY = 'MyFilesDir',
FILETABLE_COLLATE_FILENAME = database_default
)

D) Write Files

just drag them into the filetabledir - get UNC Path of it:

SELECT FileTableRootPath('TestFileTable') AS FileTableRootPath

or
INSERT INTO [dbo].[FileTableTb] ([name],[file_stream])SELECT'NewFile.txt', * FROM OPENROWSET(BULK N'd:\NewFile.txt', SINGLE_BLOB) AS FileData
or
INSERT INTO [dbo].StpFile ([name],[file_stream]) SELECT 'test1.txt', CONVERT(VARBINARY(MAX),'TestFileText') AS FileData

create Dir:
INSERT INTO FileTableTb (name, is_directory)   VALUES ('testFolder', 1)

GO

create file in subdir: have to get pathlocator first:

select GetPathLocator(CONCAT(FileTableRootPath('FileTableTb'), '\testFolder')).GetDescendant(Null,NULL);

INSERT INTO [dbo].FileTableTb  ([name],[file_stream],path_locator) SELECT 'test1.txt', CONVERT(VARBINARY(MAX),'TestFileText'), GetPathLocator(CONCAT(FileTableRootPath('FileTableTb'), '\testFolder')).GetDescendant(Null,NULL) AS FileData

Parent_Path_Locator column is readonly !

E) Read Files

with Explorer - get UNC Path of filetable:
SELECT FileTableRootPath('TestFileTable') AS FileTableRootPath

physical path of filetable isn't available, this UNC Path is given by a virtual device
The Filetable stores the files like filestream in a guid structure but offers a filesystemdriver, which respresents those files in a more readable form under a UNC Path, which is available from:
SELECT FileTableRootPath('TestFileTable') AS FileTableRootPath

select stream_id, name, convert(varchar(max), file_stream) as textContent, file_stream.GetFileNamespacePath() as RelPath,
FileTableRootPath('TestFileTable')+file_stream.GetFileNamespacePath() as AbsPath from TestFileTable