MSSQL文件表：调用数据更轻松

什么是MSSQL文件表？

MSSQL文件表是SQL Server 2014版本引入的新功能，它将文件系统中的数据文件和表数据绑定在一起。这个功能可以让开发人员更加轻松地处理文件数据，同时也提高了查询和存储文件数据的效率。

如何创建文件表？

创建数据库

首先我们需要创建一个数据库，可以使用以下的代码实现：

CREATE DATABASE MyDatabase
CONTAINMENT = NONE
ON PRIMARY
(NAME = MyDatabase_data,
FILENAME = 'C:\MyData\MyDatabase_data.mdf',
SIZE = 100MB,
MAXSIZE = 1GB,
FILEGROWTH = 10%);

注意：在FILENAME参数中，需要指定数据文件的路径。

创建文件表

创建文件表的代码如下：

USE MyDatabase;
CREATE TABLE MyFileTable
(
    FileTableID UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE,
    FileTableName NVARCHAR(255) COLLATE Chinese_PRC_CI_AS NOT NULL,
    CONSTRAINT PK_FileTableID PRIMARY KEY NONCLUSTERED (FileTableID)
        WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
              IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
              ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
    FILETABLE_STREAMID UNIQUEIDENTIFIER NOT NULL,
    FILETABLE_FILEID INT NOT NULL,
    FILETABLE_ROWGUID UNIQUEIDENTIFIER NOT NULL ROWGUIDCOL,
    FILETABLE_FULLPATH NVARCHAR(MAX) FILESTREAM NULL
) FILESTREAM_ON MyFileStreamGroup;

说明：

在创建表时需要注意：需要先启用文件流才能够创建文件表。具体可以在管理工具中开启。

在表中，FILESTREAM_ON定义表使用文件流进行存储，MyFileStreamGroup为存储区域的名称。

表中含有FileTableID、FileTableName、FILETABLE_STREAMID等列，其中FileTableName是必需的，表示文件的名称；其余列可以添加，以提高搜索效率。

如何操作文件表？

插入数据

我们可以使用以下代码向文件表中插入数据：

INSERT INTO MyFileTable (FileTableID, FileTableName, FILETABLE_STREAMID, FILETABLE_FILEID, FILETABLE_ROWGUID, FILETABLE_FULLPATH)
VALUES (NEWID(), 'MyFile', NEWID(), 1, NEWID(), '\\MyTable\MyFile.txt');

说明：

在插入时需要指定文件表需要的6个列（其中FileTableID、FILETABLE_STREAMID、FILETABLE_ROWGUID可以使用NEWID()函数自动生成）。

可以使用正常的SQL语句插入数据，也可以通过文件系统内的操作向文件表中添加数据。

查询数据

查询数据可以使用SQL语句实现：

SELECT FileTableID, FileTableName
FROM MyFileTable
WHERE FILETABLE_FULLPATH LIKE '%.txt';

说明：在查询数据时，我们可以使用FILETABLE_FULLPATH列进行查询，这个列存储了文件表中每个文件的完整路径。

读取数据

对于文件表中的数据，我们可以使用SQL语句进行读取，例如：

SELECT FILETABLE_FULLPATH.PathName(), GETFILESTREAM_TRANSACTION_CONTEXT()
FROM MyFileTable
WHERE FILETABLE_FILEID = 1;

说明：在读取数据时，使用FILETABLE_FULLPATH.PathName()函数获取文件的完整路径，GETFILESTREAM_TRANSACTION_CONTEXT()函数获取事务上下文。

删除数据

删除数据可以使用以下SQL语句实现：

DELETE MyFileTable
WHERE FileTableName = 'MyFile';

说明：在删除数据时，可以使用文件系统中的操作删除文件表中的数据。

总结

通过使用MSSQL文件表，开发人员可以更加轻松地操作文件数据，同时也能提高查询和存储文件数据的效率。在操作文件表时，我们需要了解创建数据库、创建文件表、插入数据、查询数据、读取数据、删除数据等操作方法，这些操作方法都可以通过SQL语句实现。