File Tables

--select value , value_in_use  from sys.configurations  where name like 'filestream access level'


--EXEC sp_configure filestream_access_level, 2
--RECONFIGURE

USE [master]
GO

CREATE DATABASE SQLDocumentStoreDB
ON PRIMARY
(NAME = SQLDocumentStoreDB
,FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLSERVER2012\MSSQL\DATA\SQLDocumentStoreDB.mdf'),
FILEGROUP FileStreamFG CONTAINS FILESTREAM
(NAME = SQLDocumentStoreFileTable
,FILENAME = 'C:\FileStreamShare\DocumentStore' ) --Folder location
LOG ON
(NAME = SQLDocumentStoreDB_Log
,FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLSERVER2012\MSSQL\Log\SQLDocumentStoreDB_Log.ldf')
WITH FILESTREAM
--Gives full non-transactional access to the share/ directory
(NON_TRANSACTED_ACCESS = FULL
,DIRECTORY_NAME = N'DocumentStore');
GO


USE [SQLDocumentStoreDB]
GO

CREATE TABLE MyDocuments AS FILETABLE
WITH (FileTable_Directory = 'MyDocumentStore');

--INSERT INTO MyDocuments(name,is_directory)
-- values('Docs',1)
-- INSERT INTO MyDocuments(name,is_directory)
-- values('Multimedia',1)

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT * FROM [SQLDocumentStoreDB].[dbo].[MyDocuments]

USE [master]
GO

SELECT DB_NAME(Database_id) AS [Database]
,[database_id] AS [DatabaseID]
,[non_transacted_access] AS [NonTransactedAccess]
,[non_transacted_access_desc] AS [NonTransactedAccessDesc]
,[directory_name] AS [DirectoryName]
FROM [sys].[database_filestream_options]
WHERE DB_NAME(Database_id) = 'SQLDocumentStoreDB'