How to create FileTable in SQL Server 2012

1.  Create Windows file share and grant R/W permission to SQL Service account

2.  Enable FileStream for Instance: Open SQL Configuration Manager. Right click on SQL Server service. Go to FILESTREAM tab and enable by providing windows file share created in step 1

3.  Create FileStream enabled database

CREATE DATABASE FileStreamDB

ON

PRIMARY ( NAME = FileStreamDB_data,

    FILENAME = 'D:\MSSQL\Data\FileStreamDB.mdf'),

FILEGROUP FileStreamGroup1 CONTAINS FILESTREAM( NAME = FileStreamDB_FS_Data,

    FILENAME = 'D:\MSSQL\Data\FileStream')

LOG ON  ( NAME = FileStreamDB_log,

    FILENAME = 'E:\TransactionLogs\FileStreamDB.ldf')

GO

4.  Alter DB to provide filestream directory name

USE [master]

GO

ALTER DATABASE [FileStreamDB] SET FILESTREAM( DIRECTORY_NAME = N'FileStream' ) WITH NO_WAIT

GO

5.  Create filetable

USE FileStreamDB

GO

IF OBJECT_ID('dbo.testFileTable', 'U') IS NOT NULL

  DROP TABLE dbo.testFileTable

GO

CREATE TABLE dbo.testFileTable AS FILETABLE

  WITH

  (

    FILETABLE_DIRECTORY = 'FileTable',

    FILETABLE_COLLATE_FILENAME = database_default

  )

GO

6.  Verify filestream directory by right clicking on the table and selecting Explore FileTable directory

7.  We can copy files to the directory or load files into filetable we can use either bulk insert

INSERT INTO [dbo].testFileTable

([name],[file_stream])

SELECT

'Yogesh.JPG', * FROM OPENROWSET(BULK N'D:\MSSQL\Data\FileStream\Yogesh.JPG', SINGLE_BLOB) AS FileData

GO

8.  Same was you can delete files by manually deleting from folder or running DELETE statement

9.  Verify data

USE FILESTREAMDB

GO

SELECT [name]

,[file_type]

,CAST([file_stream] AS VARCHAR) FileContent

,[cached_file_size]

,[creation_time]

,[last_write_time]

,[last_access_time]

FROM [dbo].testFileTable

GO