一、官网地址
启用 FileTable 的先决条件 - SQL Server | Microsoft Docs
二、打开Sql Server Configuration Management 设置FILESTREAM
-
在 “开始” 菜单中,依次指向 “所有程序” 、 SQL Server、 “配置工具” ,然后单击 “SQL Server 配置管理器” 。
-
在服务列表中,右键单击“SQL Server 服务” ,然后单击“打开” 。
-
在“SQL Server 配置管理器” 管理单元中,找到要在其中启用 FILESTREAM 的 SQL Server 实例。
-
右键单击该实例,然后单击“属性” 。
-
在 “SQL Server 属性” 对话框中,单击 “FILESTREAM” 选项卡。
-
选中“针对 Transact-SQL 访问启用 FILESTREAM” 复选框。
-
如果要在 Windows 中读取和写入 FILESTREAM 数据,请单击“针对文件 I/O 流访问启用 FILESTREAM” 。 在 “Windows 共享名” 框中输入 Windows 共享的名称。
-
如果远程客户端必须访问存储在此共享中的 FILESTREAM 数据,请选择 “允许远程客户端针对 FILESTREAM 数据启用流访问” 。
-
单击“应用” 。
-
在 SQL Server Management Studio中,单击 “新建查询” 以显示查询编辑器。
-
在查询编辑器中,输入以下 Transact-SQL 代码:
EXEC sp_configure filestream_access_level, 2
RECONFIGURE
GO
-
单击“执行” 。
-
重新启动 SQL Server 服务。
三、创建数据库
USE [master]
GO
/****** Object: Database [TEST_DOC] Script Date: 5/14/2021 2:39:17 PM ******/
CREATE DATABASE [TEST_DOC]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'TEST_DOC', FILENAME = N'E:\DB\TEST_DOC.mdf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB ),
FILEGROUP [TESTDocFileStreamGroup] CONTAINS FILESTREAM DEFAULT
( NAME = N'TEST_DOC_fileStream', FILENAME = N'E:\DB\TEST_DOC_fileStream\TEST_DOC_fileStream' , MAXSIZE = UNLIMITED)
LOG ON
( NAME = N'TEST_DOC_log', FILENAME = N'D:\LOG\TEST_DOC_log.ldf' , SIZE = 8192KB , MAXSIZE = 2048GB , FILEGROWTH = 524288KB )
COLLATE Chinese_PRC_CI_AS
GO
ALTER DATABASE [TEST_DOC] SET COMPATIBILITY_LEVEL = 130
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [TEST_DOC].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [TEST_DOC] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [TEST_DOC] SET ANSI_NULLS OFF
GO
ALTER DATABASE [TEST_DOC] SET ANSI_PADDING OFF
GO
ALTER DATABASE [TEST_DOC] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [TEST_DOC] SET ARITHABORT OFF
GO
ALTER DATABASE [TEST_DOC] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [TEST_DOC] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [TEST_DOC] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [TEST_DOC] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [TEST_DOC] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [TEST_DOC] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [TEST_DOC] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [TEST_DOC] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [TEST_DOC] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [TEST_DOC] SET DISABLE_BROKER
GO
ALTER DATABASE [TEST_DOC] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [TEST_DOC] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [TEST_DOC] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [TEST_DOC] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [TEST_DOC] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [TEST_DOC] SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [TEST_DOC] SET HONOR_BROKER_PRIORITY OFF
GO
ALTER DATABASE [TEST_DOC] SET RECOVERY FULL
GO
ALTER DATABASE [TEST_DOC] SET MULTI_USER
GO
ALTER DATABASE [TEST_DOC] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [TEST_DOC] SET DB_CHAINING OFF
GO
ALTER DATABASE [TEST_DOC] SET FILESTREAM( NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N'TESTDocFileStreamGroup' )
GO
ALTER DATABASE [TEST_DOC] SET TARGET_RECOVERY_TIME = 60 SECONDS
GO
ALTER DATABASE [TEST_DOC] SET DELAYED_DURABILITY = DISABLED
GO
ALTER DATABASE [TEST_DOC] SET QUERY_STORE = OFF
GO
USE [TEST_DOC]
GO
ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = OFF;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET LEGACY_CARDINALITY_ESTIMATION = PRIMARY;
GO
ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 0;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET MAXDOP = PRIMARY;
GO
ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = ON;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET PARAMETER_SNIFFING = PRIMARY;
GO
ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = OFF;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET QUERY_OPTIMIZER_HOTFIXES = PRIMARY;
GO
ALTER DATABASE [TEST_DOC] SET READ_WRITE
GO
ALTER DATABASE [TEST_DOC] COLLATE Chinese_PRC_CI_AS
GO
四、创建FILETABLE(FILETABLE是 SQLSERVER 对FILESTREAM的扩展)
1、默认结构创建
CREATE TABLE file_content AS FILETABLE;
2、自定义结构创建
CREATE TABLE file_stream_table (
[id] [UNIQUEIDENTIFIER] ROWGUIDCOL NOT NULL PRIMARY KEY,
[data] VARBINARY(MAX) FILESTREAM NULL
)
FILESTREAM_ON [TESTDocFileStreamGroup];