SQL Server 2012 new features: FileTable

Filetable is a feature based on the FileStream. The following features are available:

• A row represents a file or directory.
• Each line contains the following information:
• • File_stream stream data, stream_id identifier (GUID).
  • User representation and maintenance of path_locator and Parent_path_locator of file and directory hierarchy relationships
  • There are 10 file attributes
  • A type column that supports full-text search and semantic search for files and documents.
• Filetable enforces certain system-defined constraints and triggers to maintain the semantics of namespaces
• For non-transactional access, the SQL Server configuration FileStream the file and directory structure that is represented in filetable under the shared zone

Configure the firewall for FILESTREAM access.

--1. Configure instance-level FILESTREAM with the following values--0: Disable FILESTREAM support for this instance. --1: Enable FILESTREAM for Transact-SQL access. --2: Enables FILESTREAM for Transact-SQL and WIN32 stream access. EXEC sp_configure ' filestream_access_level ', 2goreconfigure--start alter DATABASE test SET FileStream (non_transacted_ ACCESS = full, Directory_name = ' test ')--add FILESTREAM filegroup alter DATABASE test add FILEGROUP filestreamgroup CONTAINS filestr Eamgoalter DATABASE Test ADD FILE (NAME = ' fg1 ', FILENAME = ' D:\FileStream\fg1 ') to FILEGROUP filestreamgroupgo--requires non-transactional access, Directory_nameselect db_name (database_id), non_transacted_access, Non_transacted_access_desc, directory_name from Sy S. database_filestream_options; Gocreate TABLE Documentstore as FileTable with (filetable_directory = ' documenttable ', Filetable_co Llate_filename = Database_default); go--inserts the directory insert INTO dbo. Documentstore (name, is_directory) SELECT ' MyDir01 ', 1; --In the new directory, insert file SELECT @path = Path_locator from dbo. Documentstore WHERE name = ' MyDir01 ' SELECT @new_path = @path.  ToString () + CONVERT (VARCHAR), convert (BIGINT, SUBSTRING (CONVERT (+), NEWID ()), 1, 6)) + '. ' +  CONVERT (varchar), convert (BIGINT, SUBSTRING (CONVERT (+), NEWID ()), 7, 6)) + '. ' + CONVERT (varchar (a), convert (BIGINT, SUBSTRING (CONVERT (+), NEWID (), 4)) + '/' INSERT into dbo. Documentstore (name, File_stream, path_locator) Select N ' empty file. txt ', 0x, @new_path select Getpathlocator (' \\fanr-pc \sql2016\test\documenttable\1.txt '), * from Documentstore

　　

SQL Server 2012 new features: FileTable