FileTable的創建和使用_ZenDei技術網路在線

1. 首先要在實例級別啟用FILESTREAM，打開Sql Server Configuration Manager視窗，雙擊需要設置的SQL SERVER實例進行設置。 2. 需要更改 SQL SERVER 實例的 FILESTREAM 訪問級別，執行以下代碼，並重啟 SQL SERVER 服務： ...

1. 首先要在實例級別啟用FILESTREAM，打開Sql Server Configuration Manager視窗，雙擊需要設置的SQL SERVER實例進行設置。

2. 需要更改 SQL SERVER 實例的 FILESTREAM 訪問級別，執行以下代碼，並重啟 SQL SERVER 服務：

EXEC sp_configure filestream_access_level, 2 
RECONFIGURE
GO

參數定義如下：

3. 有可能需要防火牆配置為進行 FILESTREAM 訪問，若要在防火牆保護的環境中使用 FILESTREAM，客戶端和伺服器都必須能夠將 DNS 名稱解析為包含 FILESTREAM 文件的伺服器。FILESTREAM 要求 Windows 文件共用埠 139 和 445 處於打開狀態。

4. 以上都準備好了後就可以在創建資料庫時創建 FILESTREAM 文件組了，執行以下代碼：

--    創建資料庫時創建 FILESTREAM 文件組
CREATE DATABASE Archive 
ON
PRIMARY ( 
    NAME = ArchiveMDF, 
    FILENAME = 'D:\DataBase\ZL\Archive\archdat.mdf',     -- D:\DataBase\ZL\Archive路徑必須存在
    SIZE = 5MB,
    MAXSIZE = 25MB,
    FILEGROWTH = 5MB
),
FILEGROUP FileStreamGroupFirst CONTAINS FILESTREAM(
     NAME = ArchiveFILESTREAM, 
     FILENAME = 'D:\DataBase\ZL\Archive\MyFileStream',   -- D:\DataBase\ZL\Archive路徑下MyFileStream文件夾必須不存在
     MAXSIZE = 50 MB
)
LOG ON (
    NAME = ArchiveLDF,
    FILENAME = 'D:\DataBase\ZL\Archive\archlog.ldf'
)
WITH FILESTREAM ( NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N'MyDirectory' ) -- 指定資料庫訪問級別和指定目錄名字
GO

WITH FILESTREAM() 選項是用來指定對資料庫的非事務性FILESTREAM訪問的級別，說明如下：

執行成功後在目錄里就生成了 FILESTREAM 文件夾。

當然也可以為已存在的資料庫添加 FILESTREAM 文件組，代碼如下：

--    或者在當前資料庫中添加 FILESTREAM 文件組
ALTER DATABASE zl_test
ADD FILEGROUP FileStreamGroup CONTAINS FILESTREAM;

ALTER DATABASE zl_test
ADD FILE
(
     NAME = FileStreamFile, 
     FILENAME = 'D:\DataBase\ZL\zl_test\MyFileStream',
     MAXSIZE = 50 MB
)
TO FILEGROUP FileStreamGroup
GO

如果在創建資料庫時沒有用 WITH FILESTREAM 語句來指定非事務性訪問級別，可以在之後單獨用語句來進行，比如：

--    在資料庫級別上指定 非事務性訪問級別 和 FileTable目錄(資料庫須獨占)
ALTER DATABASE zl_test
SET FILESTREAM ( NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N'MyDirectoryZl')

註意，執行以上語句必須沒有其他進程連接到被操作資料庫上才能成功，否則將無限等待直至其他連接斷開。可以用下麵的語句查看所有連接到該資料庫上的進程：

select * from master..sysprocesses where dbid=db_id('zl_test')

然後結束其他進程即可：

kill 56

用下麵的語句可以查看當前實例中哪些資料庫已經設置了非事務性訪問級別，和FileTable目錄名：

--    啟用後即可查看到其狀態信息
SELECT DB_NAME(database_id) [database],non_transacted_access, non_transacted_access_desc
FROM sys.database_filestream_options
WHERE non_transacted_access_desc <> 'OFF'

SELECT DB_NAME(database_id) [database],directory_name 
FROM sys.database_filestream_options 
WHERE directory_name IS NOT NULL

返回結果如下：

5. 在新建的 Archive 資料庫中創建 FileTable，語句如下：

--  創建FILETABLE
USE Archive
GO
CREATE TABLE [FileStore] 
AS FileTable
WITH ( 
    --文件目錄,不區分大小寫,不指定則預設為filetable名稱[FileStore]
    FileTable_Directory = N'FileStore_Dev',
    FileTable_Collate_Filename = database_default    --排序規則
)
GO

執行成功後在對象瀏覽器中可以看到

FileTable 是專用的用戶表，它具有預定義的固定架構。此架構存儲 FILESTREAM 數據、文件和目錄信息以及文件屬性。由於FileTable有固定的結構，所以不必指定欄位名。創建FileTable的簡單語法可以指定以下設置：

　　1). FILETABLE_DIRECTORY。指定充當存儲在 FileTable 中的所有文件和目錄的根目錄的目錄。此名稱應在資料庫的所有 FileTable 目錄名稱中唯一。無論當前排序規則設置如何，唯一性比較都不區分大小寫。如果創建 FileTable 時沒有提供目錄名稱，則 FileTable 自身的名稱將用作目錄名稱。

　　2). FILETABLE_COLLATE_FILENAME。指定要應用於 FileTable 的“名稱”列的排序規則名稱。如果未提供 FILETABLE_COLLATE_FILENAME 的值，或指定了 database_default，則該列繼承當前資料庫的排序規則。如果當前資料庫排序規則區分大小寫，將引發錯誤，CREATE TABLE 操作將失敗。

　　3). 還可以指定要用於三個主鍵的名稱和自動創建的唯一約束。如果不提供名稱，則系統會生成名稱

　　　　a. FILETABLE_PRIMARY_KEY_CONSTRAINT_NAME

　　　　b. FILETABLE_STREAMID_UNIQUE_CONSTRAINT_NAME

　　　　c. FILETABLE_FULLPATH_UNIQUE_CONSTRAINT_NAME

也可以用語句來更改FileTable的目錄，比如:

ALTER TABLE dbo.FileStore
    SET ( FILETABLE_DIRECTORY = N'FileStore_Pro' )

右擊創建好的FileTable，選擇Explore FileTable Directory，可以在文件系統中打開FileTable的目錄，現在目錄里是空的，

紅線划起來的就是前面創建資料庫時 WITH FILESTREAM 語句中指定的DIRECTORY_NAME值

WITH FILESTREAM ( NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N'MyDirectory' )，由於一個實例中可能有多個資料庫都有FileTable，所以這個值必須唯一。

藍線划起來的就是上面創建FileTable時 WITH() 選項中指定的值

FileTable_Directory = N'FileStore_Dev'，由於在一個資料庫中可能存在多個FileTable，所以這個值在同一個資料庫中必須唯一，但不同的資料庫中可以相同。

往目錄里存放一些文件或文件夾，

查詢FileTable表，看見表中已經產生了對應的數據。

在目錄中刪掉某些文件則表中也會相應自動的刪掉某些數據，同樣在表中刪掉某些數據，則目錄中也會相應自動的刪掉某些文件。

6. 可以用下圖的語句獲取特定 FileTable 或當前資料庫的根級 UNC 路徑：

-- returns “\\SZSAUDIT\MSSQLSERVER\MyDirectory”
SELECT FileTableRootPath();

-- returns “\\SZSAUDIT\MSSQLSERVER\MyDirectory\FileStore”
SELECT FileTableRootPath(N'dbo.FileStore');

返回的結果，

可以用下麵的語句返回FileTable中指定的文件或目錄Locator ID值。

select GetPathLocator(FileTableRootPath('[dbo].[FileStore]')+'\ASRData.pdf')
select GetPathLocator('\\Szsaudit\mssqlserver\MyDirectory\FileStore\ASRData.pdf')

7. 下麵是一些關於創建和操作FileTable的其他說明，詳見MSDN。

　　1）.不能更改現有表以將其轉換為 FileTable。

　　2）.先前在資料庫級別指定的父目錄還必須具有非 Null 值。

　　3）.由於一個FileTable 包含一個 FILESTREAM 列，因此，FileTable 需要有效的 FILESTREAM 文件組。可以指定有效的 FILESTREAM 文件組作為 CREATE TABLE 命令的一部分以創建 FileTable（可選）。如果未指定文件組，則 FileTable 使用資料庫的預設 FILESTREAM 文件組。如果資料庫沒有 FILESTREAM 文件組，將引發錯誤。

　　4）.不能將表約束作為 CREATE TABLE…AS FILETABLE 語句的一部分創建。但是以後可以使用 ALTER TABLE 語句添加該約束。

　　5）.不能在 tempdb 資料庫或任何其他系統資料庫中創建 FileTable。

不能將 FileTable 作為臨時表創建。

　　6）.不能更改FILETABLE_COLLATE_FILENAME的值。

　　7）.不能更改，刪除或禁用FileTable的系統定義的列。不能將新的用戶列、計算列或持久化計算列添加到 FileTable。

　　8）.刪除FileTable時，將刪除 FileTable 的所有列以及與該表關聯的所有對象，如索引、約束和觸發器。

　　9）.刪除FileTable時，FileTable 目錄及其子目錄將從資料庫的 FILESTREAM 文件和目錄層次結構中消失。

　　10）.FileTable上的INSERT操作

　　　　a.所有文件屬性列具有 NOT NULL 約束。如果沒有顯式設置值，則提供適當的預設值。

　　　　b.如果 INSERT 語句設置了 name、path_locator、parent_path_locator 或文件屬性，則強制執行系統定義的約束。

　　　　c.該應用程式可以通過提供指向 GetPathLocator 函數的系統文件路徑，來獲取文件或目錄的 path_locator。

　　11）.FileTable上的UPDATE操作

　　　　a.允許更新任何用戶定義的數據。

　　　　b.如果 INSERT 語句設置了 name、path_locator、parent_path_locator 或文件屬性，則強制執行系統定義的約束。

　　　　c.可以對 file_stream 列中的 FILESTREAM 數據進行更新，且不會影響任何其他列（包括時間戳）。

　　12）.FileTable上的DELETE操作

　　　　a.刪除行還將從文件系統中刪除相應的文件或目錄。

　　　　b.如果該行與包含其他文件或目錄的目錄相對應，則刪除行操作將失敗。

　　13）.FileTable不支持分區。通過對多個FILESTREAM文件組的支持，在大多數方案中可以解決純向上擴展問題，而不必使用分區。

　　14）.FileTable不支持複製和相關功能，包括事務複製，合併複製，更改數據捕獲和更改跟蹤。

建議創建FileTable所在的資料庫最好不要是現有的業務資料庫，新建一個資料庫來單獨創建FileTable比較好。