積沙成塔

實務上我們常需要在資料庫中儲存非結構化檔案的資訊，常用到的作法是在資料表中儲存檔案的相對或絕對路徑（本文稱之為檔案中繼資料），或是把檔案轉換成二進位格式之後儲存到資料表的 Image 型態的資料行。前者只儲存檔案路徑在資料庫，其所對應的檔案和資料庫中的資料是獨立的，意味著您備份資料庫的同時，需自行備份檔案系統中的檔案，SQL Server 2012 提供了新的 FileTable （檔案資料表）功能，讓 SQL Server 可以管理檔案系統中的檔案或目錄，透過 FileTable 您不需要自行備份檔案系統中的檔案，備份資料庫的同時，儲存在 FileTable 中的檔案也會一併備份，以下使用一個簡單的範例來說明，該如何將資料表中所儲存的檔案中繼資料移轉至 FileTable。

• 假設我們將檔案的路徑儲存在 UNCPath 欄位中：

   1:  use FTDB

   2:  go

   3:   

   4:  IF OBJECT_ID('Docs') > 0 

   5:  DROP TABLE Docs

   6:  GO

   7:   

   8:  CREATE TABLE Docs

   9:  (

  10:  PathLocator hierarchyid,

  11:  UNCPath varchar(512)

  12:  )

  13:  GO

  14:   

  15:  INSERT INTO Docs VALUES 

  16:  (null,'\\FileServer\Docs\Excel1.xlsx')

  17:  ,(null,'\\FileServer\Docs\PowerPoint1.pptx')

  18:   

  19:  SELECT *

  20:  FROM Docs

 

• 要將 UNCPath 中的所儲存的中繼資料移轉至 FileTable，首先必須需先將檔案透過檔案總管或是檔案複製工具複製至 FileTable 的根目錄，您可以在 Object Explorer > DB_Name > FileTables > Your_FileTable 上按右鍵選擇 Explore FileTable Directory 就可以快速切換至 FileTable 的根目錄。

 

• 接著利用下列的 T-SQL 分別利用 FILETABLEROOTPATH 方法來取得 FileTable 的根目錄，並取代現有的路徑名稱，再利用 GetPathLocator 來取得 FileTable 中檔案的 path locator ID。

   1:  UPDATE Docs

   2:  SET UNCPath = REPLACE(UNCPath

   3:  , '\\FileServer\Docs'

   4:  , FILETABLEROOTPATH('MyFileTable'))

   5:  GO

   6:   

   7:  UPDATE Docs

   8:  SET pathlocator = GetPathLocator(UNCPath)

   9:  GO

 

• 經過上述步驟，您就可以藉由 FileTable 所提供的功能來讓 SQL Server 管理非結構化的檔案或資料，因此，可以使用下列的 T-SQL 來查詢檔案資訊。

   1:  use FTDB

   2:  go

   3:   

   4:  select t.*

   5:  from Docs d

   6:  inner join MyFileTable t

   7:  on d.PathLocator = t.path_locator

 

 

【參考資料】

• FileTableRootPath (Transact-SQL)
• GetPathLocator (Transact-SQL)