SQL Server 2012的 FileTable

by adonis 19. 十二月 2013 15:20

作    者:楊先民
審    稿:張智凱

前言


自從 SQL Server 2008之後,資料庫系統開始支援 filestream功能後,以後就比較不用擔心把圖片或是影像資料存放在 SQL Server中了。

在 SQL Server 2008之前的資料庫,雖然有支援 varbinary(max)這種類型的資料型別,但我基本上來說不是建議直接把圖檔與影像直接儲存在 SQL Server資料庫當中的,原因很簡單,寫過程式就知道,你要把圖片存入資料庫當中,需要把圖片利用程式碼轉成二進位陣列(byte array),然後再轉入 varbinary(max)資料型別當中,此時你的圖片是在放在 mdf或是 ndf這種資料庫檔案中的。當然,如果你要把圖片取出,則是利用程式端將 varbinary(max)二進位資料再轉回成圖片格式,總而言之,你會需要耗費 CPU資源在做這些事情,不但沒有效率而且圖檔或是影片愈大,速度愈慢。
有時慢可能還好,重點是你不太好寫一個 progress bar讓使用者得知目前的進度為何,這樣使用者就無從得知究竟還要等圖檔多久才能完成,所以很多程式開發人員會把圖檔存放在檔案系統(例如放在 web server上),然後在資料庫端記錄的是圖檔所在的 hyper link位置(其實我也是建議這種方式),這樣的好處是圖檔是直接由 file server中取回,不需要再花時間轉換,而 file server是 web server,直接由使用者透過網路連到 web server,不需要耗費 SQL Server端的資源。
而 SQL Server 2012之後,又把原本的 filestream加以增進功能,也就是本期要講的主題:filetable。

FileTable的前身與設定

FileTable事實上與 SQL Server 2008那時的新功能 filestream有直接關係,所以如果你想設定 filetable的話,是要把 filestream的功能開啟。

為何說 filetable 和filestream有關係呢?事實上就是你可以直接把圖片透過任何方式存到 filetable所指向到的檔案系統中,而你不需要做任何的設定,就會在 filetable上看到你的檔案資訊已經被記錄了。

這個步驟我們稍候再說明,我們首先要先把 SQL Server中的 filestream功能打開,因為預設是關閉的。

開啟 SQL Server的組態管理員,選擇 SQL Server服務,按右鍵把 filestream的設定啟動,如下圖(其中 Windows共用名稱將會是之後路徑分享的名稱,這裡我設定為myFileTable ):

image

接下來,請下 T-SQL指令,把 filestream的存取層級開啟,如下:
EXEC sp_configure filestream_access_level, 2
RECONFIGURE

image

如果你沒有辦法在資料庫設定時,看到 filestream檔案群組可以建立的話(也就是如下圖),請把 SQL Server的服務重新啟動。

image

替資料庫建立一個 filestream的檔案群組,並且設定它為預設值。

image

建立 filestream檔案群組後,替此檔案群組建立一個檔案,檔案類型為 filestream,檔案群組則是剛才所建立的 filestream,路徑就設定一個預期要存放圖檔的位置,以本例而言是c:\myDB。
設定完之後,看一下資料庫選項,順便設定一下 filestream的目錄名稱,以本例而言,我取名為:FileTest。

image

以上的設定順序和 SQL Server 2008時的filestream設定並無不同,接下來我們就要設定 filetable了,只不過這個 filetable並沒有辦法用管理工具產生,必需要下指令。

image

在管理工具中,選擇FileTable按右鍵就可以點選「新增 FileTable」,這裡就會出現 T-SQL指令,只要把一些該填的資料輸入,我們的第一個 FileTable就可以產生。

image

在建立的時後,可以發現,你所設定的 FileTable_Directory的名稱,將來會成為網路芳鄰的分享名稱,而全部的 unc路徑是:
\\電腦名稱\windows共享名稱\filestream路徑名稱\fileTable目錄名稱

設定完後可以看一下你的檔案總管是不是多了一個分享,名稱正如上方所示的名字:

image

好的,接下來我們可以手動的將一些圖片利用複製貼上的方式將它複製到檔案總管的目錄之中,如下圖:

image

這個時後,再回到 SQL Server Management studio瀏覽資料表,你就會發現資料表已經自動幫你記錄有哪些圖檔,目錄以及 filestream的資訊:

image

image

是的,沒有錯,你只是把圖檔的圖片複製到目錄中,filetable就會自動幫你產生相對應的資料列,而當你把資料列刪除,則檔案總管中的圖檔就會自動刪除,或是你刪除檔案總管中的圖檔, filetable也會自動幫你把資料列刪除,等於免除了你寫一個檔案管理工具,算是相當好用的功能。

image

Tags:

評論 (64) -

cours de theatre paris
cours de theatre paris United States
2017/9/30 下午 09:03:04 #

Hey, thanks for the blog post.Really looking forward to read more. Cool.

回覆

can ho vung tau
can ho vung tau United States
2017/10/6 下午 10:45:53 #

I loved your blog post.Really thank you! Fantastic.

回覆

buy hacklink google
buy hacklink google United States
2017/10/9 下午 12:41:25 #

Very neat article.Much thanks again. Much obliged.

回覆

kamagra tanio
kamagra tanio United States
2017/10/9 下午 02:20:11 #

Thanks for sharing, this is a fantastic blog.Really thank you! Awesome.

回覆

can ho bien vung tau
can ho bien vung tau United States
2017/10/9 下午 04:36:32 #

I loved your post.Much thanks again. Much obliged.

回覆

solarmovie
solarmovie United States
2017/10/10 下午 06:23:56 #

Major thanks for the blog article.Thanks Again. Will read on...

回覆

Nathan Coombe
Nathan Coombe United States
2017/10/10 下午 08:32:00 #

Thank you ever so for you article.Much thanks again. Fantastic.

回覆

pirater un compte facebook
pirater un compte facebook United States
2017/10/10 下午 10:50:15 #

I am so grateful for your post.Really looking forward to read more. Much obliged.

回覆

buy hacklink
buy hacklink United States
2017/10/12 下午 07:14:50 #

This is one awesome blog.Much thanks again. Cool.

回覆

article source
article source United States
2017/10/14 下午 01:54:50 #

Wow, great blog article. Cool.

回覆

dragon city hack game
dragon city hack game United States
2017/10/15 下午 01:55:28 #

Really enjoyed this blog post.Really thank you! Great.

回覆

omega xl review
omega xl review United States
2017/10/15 下午 06:38:58 #

Enjoyed every bit of your post.Thanks Again. Keep writing.

回覆

click to investigate
click to investigate United States
2017/10/17 下午 01:13:27 #

Really appreciate you sharing this blog post.Much thanks again. Awesome.

回覆

sletrokor review
sletrokor review United States
2017/10/17 下午 06:46:28 #

Very neat post.Much thanks again. Want more.

回覆

officetel
officetel United States
2017/10/19 上午 04:09:12 #

Thanks so much for the post. Awesome.

回覆

VigRx
VigRx United States
2017/10/19 上午 05:49:23 #

A round of applause for your article post.Much thanks again.

回覆

browse this page
browse this page United States
2017/10/19 下午 04:33:28 #

A big thank you for your article.Much thanks again.

回覆

vung tau melody
vung tau melody United States
2017/10/21 上午 01:39:59 #

Im thankful for the blog post.Really looking forward to read more. Will read on...

回覆

carte grise en ligne
carte grise en ligne United States
2017/10/21 上午 05:21:47 #

Awesome article post.Really looking forward to read more. Keep writing.

回覆

elakekassa
elakekassa United States
2017/10/21 下午 03:46:23 #

I appreciate you sharing this article.Really thank you! Will read on...

回覆

Turbotax phone number
Turbotax phone number United States
2017/10/24 上午 06:29:56 #

I value the post. Really Cool.

回覆

son thinh
son thinh United States
2017/10/28 上午 07:42:48 #

Thanks-a-mundo for the blog post.Much thanks again. Awesome.

回覆

EZ Battery Reconditioning Scam
EZ Battery Reconditioning Scam United States
2017/10/30 上午 06:37:59 #

Thanks a lot for the article.Much thanks again.

回覆

informacje plock
informacje plock United States
2017/10/30 下午 02:22:40 #

Appreciate you sharing, great article.Really looking forward to read more. Awesome.

回覆

phenocal
phenocal United States
2017/11/1 下午 02:10:54 #

Looking forward to reading more. Great article. Awesome.

回覆

phentaslim review
phentaslim review United States
2017/11/3 上午 07:59:06 #

Very neat blog post.Thanks Again.

回覆

sciatica nerve pain
sciatica nerve pain United States
2017/11/15 上午 04:44:20 #

Im grateful for the blog.Much thanks again. Will read on...

回覆

criminel avocat
criminel avocat United States
2017/11/16 下午 03:23:26 #

I am so grateful for your blog article.Thanks Again. Fantastic.

回覆

Awesome post.Really looking forward to read more. Much obliged.

回覆

world mobile number tracker
world mobile number tracker United States
2017/11/21 下午 11:48:12 #

A big thank you for your blog post.Really looking forward to read more. Want more.

回覆

bikinis
bikinis United States
2017/11/23 下午 06:43:24 #

Really appreciate you sharing this article post.Really looking forward to read more. Want more.

回覆

Chad Boonswang and Jeffrey Goodman
Chad Boonswang and Jeffrey Goodman United States
2017/11/26 下午 02:55:41 #

Really appreciate you sharing this blog.Really looking forward to read more. Fantastic.

回覆

Chad Boonswang SEO
Chad Boonswang SEO United States
2017/11/26 下午 08:51:51 #

Thank you for your post.Really thank you! Keep writing.

回覆

truck wreckers
truck wreckers United States
2017/11/29 下午 01:17:18 #

This is one awesome post. Fantastic.

回覆

Osimi Sea view
Osimi Sea view United States
2017/11/29 下午 07:46:26 #

I really liked your blog post.Really thank you! Great.

回覆

business trade lines
business trade lines United States
2017/11/30 下午 09:43:27 #

Great, thanks for sharing this blog.Really looking forward to read more. Really Great.

回覆

porno
porno United States
2017/12/1 下午 01:26:25 #

Thank you for your blog post.Thanks Again. Great.

回覆

Build Business Credit
Build Business Credit United States
2017/12/3 上午 01:40:37 #

Thanks again for the article.Thanks Again. Awesome.

回覆

sextus.mobi
sextus.mobi United States
2017/12/5 上午 06:30:14 #

I value the post.Really thank you! Keep writing.

回覆

Thank you ever so for you article.Really looking forward to read more. Awesome.

回覆

best lawyers
best lawyers United States
2017/12/10 下午 03:18:27 #

Major thanks for the article post.Really looking forward to read more. Really Great.

回覆

Sheldon Shiffer
Sheldon Shiffer United States
2017/12/14 上午 06:19:13 #

I cannot thank you enough for the blog. Keep writing.

回覆

Christmas Music
Christmas Music United States
2017/12/14 下午 07:35:50 #

Thanks so much for the blog.Thanks Again. Fantastic.

回覆

canon drivers
canon drivers United States
2017/12/16 下午 02:51:47 #

I truly appreciate this blog post.Thanks Again. Will read on...

回覆

green coffee bean
green coffee bean United States
2017/12/16 下午 09:02:40 #

I am so grateful for your article.Thanks Again. Cool.

回覆

Very good article.Much thanks again. Will read on...

回覆

Get the facts
Get the facts United States
2017/12/17 下午 09:35:24 #

Very informative article.Much thanks again. Much obliged.

回覆

driver canon
driver canon United States
2017/12/23 上午 07:59:20 #

I cannot thank you enough for the post.Thanks Again. Really Great.

回覆

Darwin Horan
Darwin Horan United States
2017/12/23 下午 06:26:58 #

Great article.Thanks Again. Keep writing.

回覆

Hey, thanks for the blog.Thanks Again. Fantastic.

回覆

driver hp
driver hp United States
2017/12/25 下午 07:58:22 #

I loved your post.Much thanks again. Much obliged.

回覆

Thanks again for the blog.Really thank you! Want more.

回覆

Thanks-a-mundo for the article.Much thanks again. Cool.

回覆

SOCCER HIGHLIGHTS
SOCCER HIGHLIGHTS United States
2017/12/26 下午 02:09:35 #

Wow, great article post. Want more.

回覆

canon drivers
canon drivers United States
2017/12/27 下午 03:47:51 #

I really enjoy the post.Really thank you! Keep writing.

回覆

hp drivers
hp drivers United States
2018/1/2 上午 06:32:33 #

Really informative blog article.Really thank you! Keep writing.

回覆

hop over to this website
hop over to this website United States
2018/1/2 下午 02:30:11 #

Say, you got a nice blog article.Really thank you! Great.

回覆

Fantastic blog.Really thank you! Great.

回覆

hp drivers
hp drivers United States
2018/1/3 上午 05:49:50 #

I am so grateful for your post.Really thank you! Fantastic.

回覆

best bitcoin casino
best bitcoin casino United States
2018/1/4 下午 04:22:40 #

Im obliged for the article.Thanks Again. Really Great.

回覆

hp driver
hp driver United States
2018/1/5 下午 06:30:02 #

A round of applause for your blog.Really thank you! Really Cool.

回覆

FBA
FBA United States
2018/1/6 上午 10:36:17 #

Fantastic article post.Thanks Again. Really Great.

回覆

colocation chicago
colocation chicago United States
2018/1/10 上午 11:39:58 #

I think this is a real great post.Really looking forward to read more. Awesome.

回覆

新增評論




  Country flag
biuquote
  • 評論
  • 線上預覽
Loading






NET Magazine國際中文電子雜誌

NET Magazine國際中文電子版雜誌,由恆逸資訊創立於2000,自發刊日起迄今已發行超過500篇.NET相關技術文章,擁有超過40000名註冊讀者群。NET Magazine國際中文電子版雜誌希望藉於電子雜誌與NET Developer達到共同學習與技術新知分享,歡迎每一位對.NET 技術有興趣的朋友們多多支持本雜誌,讓作者群們可以有持續性的動力繼續爬文。<請加入免費訂閱>

月分類Month List