结构化数据SQL Server与非结构化数据的构建之道

1. 什么是结构化数据和非结构化数据？

在讨论SQL Server与非结构化数据的构建之前，我们需要先了解什么是结构化数据和非结构化数据。

1.1 结构化数据

结构化数据是指按照某种结构化的、规范化的格式进行组织和存储的数据。它具有如下特点：

数据的类型及其格式在存储过程中明确定义

数据具有固定的字段名和数据类型

数据之间存在明确的关系，并可以建立关系型数据库

结构化数据的一个典型例子是关系型数据库中的数据，如 SQL Server、Oracle、MySQL 等。

1.2 非结构化数据

非结构化数据则是指不按照某种固定格式进行组织和存储的数据，包括但不限于：

文本文件、Word 文档等

音频、视频文件

图片、PDF 文件等

社交媒体数据、日志文件等

这些数据的类型、格式、语义都没有固定的规范，导致无法使用传统的关系型数据库进行存储和管理。

2. 如何在 SQL Server 中存储非结构化数据？

虽然 SQL Server 是一种关系型数据库管理系统，但是它也支持在数据库中存储非结构化数据。下面介绍三种 SQL Server 中存储非结构化数据的方式。

2.1 使用文件表

在 SQL Server 中，可以使用文件表(file table)来存储文件。文件表定义了一个目录，用于存储文件系统中的文件。因此，文件表可以提供表格和文件系统访问的结合体。

现假设我们需要存储一些文档文件和图片等非结构化数据，可以创建一个文件表来进行存储。

--创建文件表
CREATE TABLE FileTableExample  
AS FILETABLE  
(  
   FileTableID UNIQUEIDENTIFIER NOT NULL PRIMARY KEY  
      DEFAULT NEWID(),  
   FileTableDirectory NVARCHAR(200) NOT NULL,  
   CONSTRAINT UNQ_FileTableName UNIQUE (FileTableDirectoryCollate BIN2_COLLATION))  
   WITH  
   (  
   FileTable_Directory = 'FileTableShare',  
   FileTable_Collate_Filename = 'BIN2',  
   FileTable_StreamID = 'FileStreamGUID',  
   FileTable_FULLPATH_UNIQUE_CONSTRAINT_NAME = 'UQ_FileTableExample_FULLPATH')  
GO 
--插入文档文件
INSERT INTO FileTableExample (name, file_stream)  
SELECT 'Document1.docx', * FROM OPENROWSET(  
   BULK N'C:\FileTableShare\Document1.docx', SINGLE_BLOB) AS document  
GO
--插入图片文件
INSERT INTO FileTableExample (name, file_stream)  
SELECT 'Picture1.jpg', * FROM OPENROWSET(  
   BULK N'C:\FileTableShare\Picture1.jpg', SINGLE_BLOB) AS picture  
GO

这样，我们就可以使用文件表来存储非结构化数据了。

2.2 使用 FileStream

FileStream 是 SQL Server 提供的一种文件流存储技术，它可以把非结构化数据存储到文件系统中，同时数据库仍然可以通过 Transact-SQL 和 ADO.NET 的方式访问这些数据。

与文件表不同，FileStream 不需要将整个文件存储在数据库中，而是只存储文件的指针及其元数据。这样做的好处在于，可以大大减少数据库的存储量，同时也不会影响文件系统的性能。

--创建 FileStream
USE master;
--检查是否启用了 FileStream
EXEC sp_configure 'filestream_access_level'  
--如果返回结果不为2，即需要修改配置，下面是修改配置的步骤  
EXEC sp_configure 'filestream_access_level', 2  
RECONFIGURE  
GO  
  
--创建文件组  
ALTER DATABASE TestDB  
ADD FILEGROUP FileStreamGroup1 CONTAINS FILESTREAM  
GO  
  
--在文件组上创建文件  
ALTER DATABASE TestDB  
ADD FILE  
(  
    NAME = FileStreamDBFile1,  
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\FileStreamDB1.ndf',  
    SIZE = 10MB  
)  
TO FILEGROUP FileStreamGroup1  
GO  
  
--创建表格，并启用 FileStream  
USE TestDB;  
CREATE TABLE TestFileStream  
(  
    ID INT PRIMARY KEY,  
    Name VARCHAR(50),  
    FileStreamFile VARBINARY(MAX)  
)  
FILESTREAM_ON FileStreamGroup1  
GO  

使用 FileStream 时，数据库会在指定文件夹下创建一个文件夹，用于存储文件流。我们可以将文件流保存到这个文件夹内，然后通过 Transact-SQL 和 ADO.NET 来访问这些数据。

2.3 使用 Varbinary(max)

如果你不想使用上述两种方法来存储非结构化数据，可以选择在 SQL Server 中使用 varbinary(max) 数据类型。varbinary(max) 类型可以存储最大长度为 2GB 的数据。

--创建表格
CREATE TABLE NonStructuredData  
(  
   ID INT PRIMARY KEY,  
   Content VARBINARY(max) NOT NULL  
)  
--插入数据
INSERT INTO NonStructuredData (ID, Content)  
SELECT 1, * FROM OPENROWSET(  
   BULK N'C:\temp\largefile.bin', SINGLE_BLOB) AS document  

这种方法较为简单，但是缺点也很明显，那就是无法对该数据进行有效的分析和管理。

3. 结语

在实际的应用中，我们常常需要处理各种格式的数据，包括结构化数据和非结构化数据。通过了解 SQL Server 的一些方法，我们可以将这些非结构化数据存储在数据库中，并使用 SQL Server 提供的分析、管理和其他功能进行处理。