FileTable的创建和使用

FileTable 是 SQL Server 2012 引入的一个新特性，可用于将文件系统的文件和目录与 SQL Server 表进行关联，并提供了一些方便的查询和管理功能。下面是 FileTable 的创建和使用步骤：

1. 创建 FileTable 存储区

首先，需要在 SQL Server 中创建一个 FileTable 存储区，用于存储文件和目录。可以使用以下 T-SQL 语句创建：

CREATE DATABASE MyDB;
GO

USE MyDB;
GO

CREATE TABLE MyFileTable
(
    [Id] UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE,
    [stream_id] INT NOT NULL,
    [file_stream] VARBINARY(MAX) FILESTREAM NULL,
    [name] NVARCHAR(MAX) NOT NULL,
    [path_locator] HIERARCHYID NOT NULL,
    [file_type] NVARCHAR(20) NOT NULL,
    [cached_file_size] BIGINT NOT NULL,
    [creation_time] DATETIME2(0) NOT NULL,
    [last_write_time] DATETIME2(0) NOT NULL,
    [last_access_time] DATETIME2(0) NOT NULL,
    [is_directory] BIT NOT NULL,
    [is_offline] BIT NOT NULL,
    [is_hidden] BIT NOT NULL,
    [is_readonly] BIT NOT NULL,
    [is_archive] BIT NOT NULL,
    [is_system] BIT NOT NULL,
    CONSTRAINT [PK_MyFileTable_Id] PRIMARY KEY NONCLUSTERED ([Id] ASC),
    CONSTRAINT [DF_MyFileTable_stream_id] DEFAULT (0) FOR [stream_id],
    CONSTRAINT [DF_MyFileTable_file_type] DEFAULT ('') FOR [file_type],
    CONSTRAINT [DF_MyFileTable_cached_file_size] DEFAULT ((0)) FOR [cached_file_size],
    CONSTRAINT [DF_MyFileTable_creation_time] DEFAULT (SYSDATETIME()) FOR [creation_time],
    CONSTRAINT [DF_MyFileTable_last_write_time] DEFAULT (SYSDATETIME()) FOR [last_write_time],
    CONSTRAINT [DF_MyFileTable_last_access_time] DEFAULT (SYSDATETIME()) FOR [last_access_time],
    CONSTRAINT [DF_MyFileTable_is_directory] DEFAULT ((0)) FOR [is_directory],
    CONSTRAINT [DF_MyFileTable_is_offline] DEFAULT ((0)) FOR [is_offline],
    CONSTRAINT [DF_MyFileTable_is_hidden] DEFAULT ((0)) FOR [is_hidden],
    CONSTRAINT [DF_MyFileTable_is_readonly] DEFAULT ((0)) FOR [is_readonly],
    CONSTRAINT [DF_MyFileTable_is_archive] DEFAULT ((0)) FOR [is_archive],
    CONSTRAINT [DF_MyFileTable_is_system] DEFAULT ((0)) FOR [is_system]
) FILETABLE_DIRECTORY = 'MyFileTable';
GO

2. 将文件和目录插入 FileTable

可以使用 INSERT INTO SELECT 语句将文件和目录插入 FileTable 中。例如，下面的语句将 D:\Test 文件夹下所有文件和目录插入到 MyFileTable 中：

INSERT INTO MyFileTable
(Id, name, path_locator, file_type, cached_file_size, creation_time, last_write_time, last_access_time, is_directory, is_offline, is_hidden, is_readonly, is_archive, is_system)
SELECT NEWID(), f.name, CAST(FILETABLEROOTPATH() + N'\' + f.name AS HIERARCHYID), CASE WHEN f.type = 'D' THEN 'Directory' ELSE RIGHT(f.name, CHARINDEX('.', REVERSE(f.name))-1) END, f.size, f.create_time, f.write_time, f.access_time, CAST(CASE WHEN f.type = 'D' THEN 1 ELSE 0 END AS BIT), CAST(0 AS BIT), CAST(0 AS BIT), CAST(CASE WHEN f.attributes & 1 = 1 THEN 1 ELSE 0 END AS BIT), CAST(CASE WHEN f.attributes & 32 = 32 THEN 1 ELSE 0 END AS BIT), CAST(CASE WHEN f.attributes & 4 = 4 THEN 1 ELSE 0 END AS BIT)
FROM sys.dm_os_enumerate_filesystem(N'D:\Test', NULL) f
WHERE f.name NOT LIKE '%.ini';

3. 查询 FileTable

可以使用 SELECT 语句查询 FileTable 中的文件和目录，例如，下面的语句查询 MyFileTable 中所有文件和目录：

SELECT *
FROM MyFileTable;

4. 使用 Transact-SQL 操作文件和目录

可以使用 Transact-SQL 语句对 FileTable 中的文件和目录进行操作，例如，下面的语句将 MyFileTable 中名为 "test.txt" 的文件读取到变量 @file_content 中：

DECLARE @file_content VARBINARY(MAX);
SELECT @file_content = file_stream
FROM MyFileTable
WHERE name = 'test.txt';

PRINT CONVERT(NVARCHAR(MAX), @file_content);

以上便是 FileTable 的创建和使用方法，希望对你有所帮助。