SQL Server - 神奇又好用的File Table

17 June 2013 — Written by Sky Chang
#SQL Server

老實說,完全沒想到,會寫到SQL Server的這個功能,也多虧了公司內,強大的DBA同事Winnie,和神人百敬老師的一步一步指導,所以就在這邊紀錄一下,不然根據小弟的老人痴呆,大概過幾天就忘記了吧- -…

但是開始前,還是要敘述一下,甚麼是File Table,是的,顧名思義,就是存放檔案的Table,但存放的不是超級大的二進位檔案,而是這個檔案的資料,例如,檔名等資訊。

不過就只存放這些東西,好像也沒甚麼大不了的?,但File Table真正神奇的是,它可以和目錄去做關聯!!是的,簡單的說,就是某個路徑下,存放了某個檔案後,Table就會自動新增這筆資料,如果砍掉,這筆資料就會自動消失!!

沒錯,就是這樣的神奇,所以,基於小弟要朝向"潮"的型男之路邁進,所以決定嘗試一下這個很"潮"的技術…

首先,開始前先提醒大家,目前LocalDB是不支援此功能的,如有興趣,可以裝SQL Server 2012的正式版本,或是SQL Server 2012 Express,目前小弟是裝Express版本。

如果是第一次安裝,其實可以看到FILESTREAM,預設是沒有安裝的,其實File Table底層就是FILESTREAM,如果這邊不知道該怎麼輸入,也可以先略過,未來再開啟。

image

當裝完後,要使用File Table,就一定要打開FILESTREAM,要打開FILESTREAM就必須先打開SQL Server組態管理員(如下圖),其中最後面允許遠端存取,通常都會打勾,但下圖沒截到圖,所以這邊提醒一下大家。另外要注意的是windows共用名稱,這個名稱未來會變成目錄結構之一,所以也在這邊提醒大家一下。

image

接下來,用SSMS登入後,我們要執行一下如下指令。

EXEC sp_configure filestream_access_level, 2 
RECONFIGURE

完成如下圖,完成之後,我們再重新開一下SQL Server。

image

接下來,我們要用SSMS來進行設定,我們先選擇我們這台SQL Server,並且設定伺服器屬性,先去如下圖的位置,確定FILESTREAM存取權限是否已啟用完整存取。

image

SQL Server設定完後,我們要針對要執行的DB,進行設定,以下圖,我們要針對Test的db進行設定。我們這邊要設定檔案群組。

image

設定完檔案群組後,我們要繼續設定檔案的部分。我們要新增一個FILESTREAM的檔案。

image

接下來,我們再到選項的地方,我們要填入FILESTREAM目錄名稱,並且把FILESTREAM非交易式存取設定為Full,這樣才能讓檔案總管來存取,如果沒有設定Full,就只能透過SQL Server來進行檔案的存取,但透過SQL Server進行檔案的存取,其實是會消耗SQL Server的IO的,所以沒有特別需要,還是利用檔案總管來存取比較好;其次,目錄名稱也請特別注意,這個部分未來也會形成存取檔案的目錄結構之一。

image

最後,我們就使用T-SQL語法來建立File Table。

image

當然,他會產生一長串的T-SQL如下圖,我們只要稍微編修一下。

image

編修後的Code如下,基本上,比較需要注意的是FILETABLE_DIRECTORY的這個設定,這個設定也會牽涉到目錄結構,如果大家對其他語法有不懂的,底下的參考連結有MSDN的網址,大家可以去看看喔。

-- =========================================
-- Create FileTable template
-- =========================================
USE Test
GO

IF OBJECT_ID('dbo.FileTable', 'U') IS NOT NULL
  DROP TABLE dbo.FileTable
GO

CREATE TABLE dbo.FileTable AS FILETABLE
  WITH
  (
    FILETABLE_DIRECTORY = 'FS',
    FILETABLE_COLLATE_FILENAME = database_default
  )
GO

建立完成後,就會看到新的Table了。

image

最後,我們只要用檔案總管,就可以來新增檔案;這時候,大家可以發現,必須要用網路芳鄰的方式來存取檔案,而這個路徑,就是我們一路設定過來的路徑名稱。

image

最後,如果我們在這個資料夾,新增一個txt檔案,再回到Table裡面去看,我們可以發現,Table已經同步更新了喔!!

image

此外,如果我們從資料庫刪除了此筆資料,那檔案也會同步不見喔!!有沒有超級方便的!?

到這邊就完成了,當然,除了透過檔案總管外,也可以透過資料庫來存取資料,如果想知道更詳細的作法,可以參考MSDN喔!!

後記

還是要感謝老師們,不捨棄我的教我一步一步完成QQ…

參考資料

Sky & Study4.TW