SQL Server FileTable File table

SQL Server 2012 provides a special "File table", also known as "FileTable". FileTable is a dedicated user table that contains predefined schemas for storing FILESTREAM data, as well as file and directory hierarchy information, and file attributes. The FileTable feature provides support for Windows file Namespaces and compatibility with Windows applications for file data stored in SQL Server. That is, you can store files and documents in SQL Server in a special table called FileTable, but access them from Windows applications as if they were stored in the file system without having to make any changes to the client application.

Enable FILESTREAM at the instance level: (Refer to enabling and configuring FILESTREAM)

Right-click SQL Server Service--attribute--filestream--tick--Restart service

To change the FILESTREAM access level for this instance of SQL Server: (refer to FILESTREAM access levels Server configuration options)

exec sp_configure N'filestream accesslevel' ,2reconfigure  with Override

You may need to configure the firewall to be configured for FILESTREAM access.

Create a FILESTREAM filegroup at the database level:

The database must first have a FILESTREAM filegroup before you can create a FileTable in the database.

--Create a FILESTREAM filegroup when creating a databaseCREATE DATABASEFilestreamdb onPRIMARY(NAME=Filestreamdb, FILENAME= 'G:\database\FileStreamDB.mdf', SIZE=5MB, MAXSIZE=25MB, FileGrowth=5MB), FILEGROUP filestreamgroupCONTAINSFILESTREAM (NAME=Filestreamfile, FILENAME= 'G:\database\FileStreamFile', MAXSIZE=  -MB)LOG  on(NAME=Filestreamdb_log, FILENAME= 'G:\database\FileStreamDB_log.ldf') withFILESTREAM (non_transacted_access=  Full, Directory_name=N'Filestreampath' )GO

--or add a FILESTREAM filegroup to the current databaseALTER DATABASE [AdventureWorks2012]ADDFILEGROUP FilestreamgroupCONTAINSFILESTREAM;ALTER DATABASE [AdventureWorks2012]ADD FILE(NAME=Filestreamfile, FILENAME= 'G:\database\FileStreamFile', MAXSIZE=  -MB) toFILEGROUP FilestreamgroupGO

When you create a file, filename only specifies the directory, and the directory filestreamfile is not present in the file system, and the directory folder is generated automatically after it is created

Specify the non-transactional access level and the Filetable directory at the database level (the database must be exclusive):

--Specify non-transactional access level and filetable directory at the database level (database must be exclusive)ALTER DATABASE [AdventureWorks2012]SETFILESTREAM (non_transacted_access=  Full, Directory_name=N'Filestreampath' )--when enabled, you can view its status informationSELECT db_name(database_id)[Database], non_transacted_access, Non_transacted_access_desc fromsys.database_filestream_optionsWHERENon_transacted_access_desc<> 'OFF'SELECT db_name(database_id)[Database], Directory_name fromsys.database_filestream_optionsWHEREDirectory_name is  not NULL

Create a new table as FileTable: (Refer to CREATE TABLE (Transact-SQL))

 Use [AdventureWorks2012]GOCREATE TABLE [Documentstore]  asFileTable with (     --file directory, case-insensitive, filetable name not specified [Documentstore]Filetable_directory=N'Filestreampath', Filetable_collate_filename=Database_default--Sorting Rules);GO--Change DirectoryALTER TABLE [Documentstore]    SET(filetable_directory=N'Filestreampath' );GO

Each directory creation generates a folder in the file system:

Related queries: (FileTable architecture)

--View FileTable InformationSELECT *  fromSys.filetables;SELECT *  fromSys.tablesWHEREIs_filetable= 1;--related objects of FileTableSELECTPARENT_OBJECT_ID,object_name(parent_object_id) as 'FileTable',object_id,object_name(object_id) as 'system-defined Object' fromsys.filetable_system_defined_objectsORDER  byFileTable,'system-defined Object';

At this point, the configuration is complete! ~

Now look at the file table, no records. You can view the UNC path to the table and open the path in the system file.

--View FileTableSELECT *  from [dbo].[Documentstore]--gets the root-level UNC path of a specific FileTable or current database. SELECTFiletablerootpath ();SELECTFiletablerootpath (N'Documentstore');SELECTFiletablerootpath (N'dbo. Documentstore');

In this path, the system can be copied directly into the other files:

Check the FileTable again, the data is automatically recorded

--     View FileTableSELECT*from[dbo]. [documentstore]

--     You can also use Getfilenamespacepath to view the UNC path of a file or directory in FileTable.  SELECT  file_stream. Getfilenamespacepath (), File_stream. Getfilenamespacepath (10)from[dbo]. [documentstore]

Delete the records in the table FileTable and the files will be deleted:

--delete the records in the table FileTable and the files will be deletedDELETE  from [dbo].[Documentstore] WHEREstream_id= 'ba483eca-ae0e-e511-8367-005056c00008'SELECT *  from [dbo].[Documentstore]

To obtain exclusive access that is required to perform certain administrative tasks, you may have to temporarily disable non-transactional access permissions.

Disable full non-transactional access permissions:

ALTER DATABASE [AdventureWorks2012]    SETFILESTREAM (non_transacted_access= OFF );GO--The path cannot be opened after closing--\\Kk-pc\mssqlserver\FileStreamPath\FileStreamPath--The file table [Documentstore] is still operating normallyALTER DATABASE [AdventureWorks2012]    SETFILESTREAM (non_transacted_access=read_only);GO--read-only state, paths can copy files, but cannot copy files to this directory--\\Kk-pc\mssqlserver\FileStreamPath\FileStreamPath--The file table [Documentstore] is still operating normally

To re-enable full non-transactional access:

--     re-enable full non-transactional access ALTERDATABASE[AdventureWorks2012]    SET= full ); GO

Disabling the FileTable namespace disables all system-defined constraints and triggers the constraints created with FileTable.

--     Disable the FileTable namespace --    (disabled after path \\Kk-pc\mssqlserver\FileStreamPath\FileStreamPath not accessible)  ALTERTABLE[documentstore]  DISABLE filetable_namespace; GO

--     re-enable the FileTable namespace ALTERTABLE[documentstore]  ENABLE Filetable_namespace; GO

More attention to things also need to refer to the official documentation: FileTable compatibility with other SQL Server features

More information: FileTable (SQL Server)

SQL Server FileTable File table