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 access level ', 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 you create a database the creation database Filestreamdb onprimary (NAME = filestreamdb, FILENAME = ' G:\database\FileStreamD B.mdf ', SIZE = 5mb,maxsize = 25mb,filegrowth = 5MB), FILEGROUP filestreamgroup CONTAINS FILESTREAM (NAME = filestreamfile,
   filename = ' G:\database\FileStreamFile ', MAXSIZE = MB) LOG on (NAME = Filestreamdb_log,filename = ' G:\database\FileSt Reamdb_log.ldf ') with FILESTREAM (non_transacted_access = full, Directory_name = N ' Filestreampath ') GO

--or add FILESTREAM filegroup in the current database ALTER DATABASE [Adventureworks2012]add FILEGROUP filestreamgroup CONTAINS FILESTREAM; ALTER DATABASE [Adventureworks2012]add FILE (NAME = filestreamfile,  FILENAME = ' G:\database\FileStreamFile ', MAXSIZE = MB) to FILEGROUP 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]set FILESTREAM (non_transacted_access = Full, Directory_name = N ' Filestreampath ')--to view its status information when enabled select Db_name (database_id) [Database],non_transacted_ Access, Non_transacted_access_descfrom sys.database_filestream_optionswhere non_transacted_access_desc <> ' OFF ' SELECT db_name (database_id) [Database],directory_name from Sys.database_filestream_options WHERE directory_name is not NULL

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

Use [Adventureworks2012]gocreate TABLE [Documentstore] as Filetablewith (--File directory, case-insensitive, not specified filetable name [ Documentstore]filetable_directory = N ' Filestreampath ', filetable_collate_filename = database_default--sort rule); go--Change directory ALTER TABLE [Documentstore]    SET (filetable_directory = N ' Filestreampath '); GO

Each directory creation generates a folder in the file system:

Related queries: (FileTable architecture)

--View FileTable Information select * from Sys.filetables; SELECT * from sys.tables WHERE is_filetable = 1;--filetable Related objects Select Parent_object_id,object_name (parent_object_id) As ' FileTable ', Object_id,object_name (object_id) as ' system-defined object ' from Sys.filetable_system_defined_ Objectsorder by FileTable, ' 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. SELECT Filetablerootpath (); SELECT Filetablerootpath (N ' Documentstore '); SELECT Filetablerootpath (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 (1, 0) from [dbo]. [Documentstore]

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

--Delete the record from the table FileTable, and the file will be deleted delete from [dbo]. [Documentstore] WHERE stream_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]    SET FILESTREAM (non_transacted_access = OFF); The path cannot be opened when the go--is closed--\\kk-pc\mssqlserver\filestreampath\filestreampath--the file table [Documentstore] still operates normally. Alter DATABASE [ ADVENTUREWORKS2012]    SET FILESTREAM (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 working

To re-enable full non-transactional access:

--Re-enable fully non-transactional access alter DATABASE [AdventureWorks2012]    SET FILESTREAM (non_transacted_access = full); GO

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

--Disable FileTable namespace--(disabled after path \\Kk-pc\mssqlserver\FileStreamPath\FileStreamPath not accessible) ALTER TABLE [Documentstore] DISABLE Filetable_namespace; GO

--Re-enable FileTable namespace alter TABLE [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