SQL Server Denali 新增的程式化功能(下)

by byron 15. 九月 2011 17:31

SQL Server Denali 基於 SQL Server 2008 後提供的 FileStream 機制,新增 FileTable 功能,讓資料表內的記錄可以直接對應到檔案目錄下的檔案。將 Windows 檔案系統與 SQL Server資料庫功能結合,提供以下的特徵:

  • 不參與 SQL Server 交易,直接存取和更新放在檔案系統上的 FILESTREAM 資料。
  • 以 SQL Server 的階層資料結構對應 Windows 檔案系統的目錄和檔案。
  • 以 T-SQL 存取檔案屬性,如唯讀、建立日期和修改日期…等,可在兩者間同步。
  • 支援透過 Windows API 直接管理檔案和目錄。
  • 與其他SQL Server功能整合,包括管理工具,服務和透過 T-SQL 存取 FILESTREAM 資料、檔案。

FileTables 試圖減少整合SQL Server的關聯資料和放在檔案服務器上非結構化數據的麻煩。企業可以將資料從檔案伺服器對應到 FileTable,透過 SQL Server 整合資訊。同時,仍可以維持現有應用程式存取 Windows 檔案系統之方式。

接下來就以範例說明如何使用 FileTable,首先是設定某個資料庫啟動 FileStream 和 FileTable 功能,如範例程式碼 1 所示:

USE [master]

GO

ALTER DATABASE [Northwind] ADD FILEGROUP [Files] CONTAINS FILESTREAM

GO

ALTER DATABASE [Northwind] ADD FILE ( NAME = N'Northwind_Files', FILENAME = N'C:\Northwind\Northwind_Files' ) TO FILEGROUP [Files]

GO

ALTER DATABASE [Northwind] SET FILESTREAM( NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N'NorthwindFiles' ) WITH NO_WAIT

GO

USE Northwind

CREATE TABLE DocumentStore AS FileTable

WITH ( FileTable_Directory ='Docs',

FileTable_Collate_Filename = database_default);

範例程式碼 1:啟動資料庫的 FileStream 功能,並建立 FileTable 類型的資料表

在範例程式碼 1 中,先替 Northwind 範例資料庫設定 FileStream 所要存放的檔案路徑,接著設定Northwind 資料庫可以非交易式存取,這也可以透過 Denali 新增如圖 1 的介面設定:

image

圖 1:設定資料庫啟動 FileTable 功能

一旦建立好 FileTable 類型的資料表後,可以在 Management Studio 的物件總管中,FileTable 子結點下看到新建的資料表,如圖 2 所示:

image

圖 2:透過檔案總管存取 FileTable

在圖 2 中滑鼠右鍵選擇快捷選單中的「瀏覽 FileTable 目錄」,可以透過檔案總管檢視該資料表所對應的 Windows 共享目錄,你可以在該目錄下新增檔案或子目錄,對應的資料表內容會自動更新。若想要直接查詢 FileTable 所對應的分享路徑,可以透過如下的語法:

select FileTableRootPath('dbo.DocumentStore');

回傳結果如下:

\\DENALICTP3\MSSQLSERVER\NorthwindFiles\Docs

從共享路徑名可以對應範例程式碼 1 所定義的 DIRECTORY_NAME、FileTable_Directory 等屬性。但其真正檔案是存放在資料庫所定義的 FILESTREAM 檔案群組目錄內,也就是範例程式碼 1 所定義的 C:\Northwind\Northwind_Files 目錄下。

筆者透過檔案總管增減一些檔案與子目錄後,在 Management Studio 查詢經由範例程式碼 1 建立的 DocumentStore 資料表,其回傳結果如圖 3 所示:

image

圖 3:透過 Management Studio 查詢 FileTable 類型資料

表回傳的結果

在 SQL Server 除了對該資料表進行全文檢索外,透過 Update 語法可以更新檔案內容(file_stream 欄位)與各種檔案屬性,刪除該筆記錄也能將其從檔案系統上直接刪除檔案。

SQL Server Denali 新增的系統函數

新版 SQL Server 依照慣例會新增系統函數,並強化舊的函數。在此分別用簡單的範例呈現新增函數之用法,詳細的功能說明請參照Denali線上說明:Programmability Enhancements (Database Engine) http://technet.microsoft.com/en-us/library/cc645577(SQL.110).aspx

新增的系統函數依其功能可隸屬以下類型:

  • 格式轉換函數:Parse、Try_Convert、Try_Parse。
  • 日期和時間函數:DateFromParts、Datetime2FromParts、DatetimeFromParts、DatetimeOffsetFromParts、SmallDatetimeFromParts、TimeFromParts、EOMonth。
  • 邏輯運算函數:Choose、IIf。
  • 字串函數:Concat、Format。
  • 分析用函數:CUME_DIST、PERCENT_RANK、First_Value、Last_Value、Lag、Lead、percentile_cont、percentile_disc。

在此依上述用途類型順序介紹。

格式轉換函數
  • Parse:將字串轉為指令資料類型,其範例如下:

select parse('NT$ 3' as money using 'zh-TW') -- 就 CTP 3 而言,線上叢書的範例用逗號是錯的,要用 using

執行結果為:

3.00

若使用 'en-US' 文化特性,將字串值 'NT$ 3' 轉換成資料類型 money 時會發生錯誤。

select parse('NT$ 3' as money using 'en-US')

執行結果為:

訊息 9819,層級 16,狀態 1,行 1

使用 'en-US' 文化特性將字串值 'NT$ 3' 轉換成資料類型 money 時發生錯誤。

另外,線上叢書關於 Parse 函數的這段說明需要注意:

PARSE relies on the presence of .the .NET Framework Common Language Runtime (CLR). If the server does not have the CLR installed, then the statement with the call to the PARSE function fails. If the user has disabled the CLR by using sp_configure, the PARSE function continues to work, since it is a system function and not a user-defined function.

This function will not be remoted since it depends on the presence of the CLR. Remoting a function that requires the CLR would cause an error on the remote server.

而很多新增的函數好像都跟 CLR 有關,換句話說,若 SQL Server 所在的 Windows 沒有安裝 CLR 將會導致許多函數無法執行。

  • Try_Convert將資料以指定型別傳回,若轉型失敗,則回傳 NULL。其範例如下:

with v(c1)as(select c1 from (values('a'),('123'),(NULL),('12.3')) t(c1))

select isnull(try_convert(int, c1),0) from v

執行結果如圖 4 所示:

image

圖 4:透過 Try_Convert 函數將傳入的資料以指定型別傳回

由於轉型失敗會回傳 NULL,由圖 4 透過 isnull 系統函數將 NULL 轉成 0 的結果來看,’a’、NULL、’12.3‘ 三種值都無法轉成 int 資料類型。

  • Try_Parse將字串內容嘗試轉成目的資料類型,若無法轉成目的類型,則傳回 null。其範例如下:

with v(c1)as(select c1 from (values('$3'),('NT$ 3'),(NULL),('12.3')) t(c1))

select try_parse(c1 as money using 'en-US') from v -- 就 CTP 3 而言,線上叢書的範例用逗號是錯的,要用 using

執行結果如圖 5 所示:

image

圖 5:透過 Try_Parse 函數將傳入的資料參考國別設定,以指定型別傳回

日期和時間函數
  • DateFromParts給予分別代表年、月、日的數值,回傳 Date 型別資料。其範例如下:

with v(Year,Month,Day)as(select * from (values(1,1,1),(null,1,1),(9999,12,31),(1,-1,1)) t(year,month,day))

select DateFromParts(Year,Month,Day) from v

執行結果如圖 6 所示:

image

圖 6:透過 DateFromParts 函數將分別代表年、月、日的數值轉成日期

由於第二筆記錄在代表年的參數傳入 NULL,回傳結果也是 NULL。而最後一筆紀錄的月輸入 -1,參數超出範圍造成錯誤,回傳如下的訊息:

訊息 289,層級 16,狀態 1,行 1

無法建構資料類型 date,部分引數擁有無效的值。

  • Datetime2FromParts給予 year, month, day, hour, minute, seconds, fractions, precision。其中,precision 指定小數位數,要是常數,且要大於、等於代表 fraction 的字元長度量。其範例如下:

with v(Y,M,D,h,mm,s,f)as(select * from (values(1,1,1,1,1,1,12345),(9999,12,31,23,59,59,1234567)) t(Y,M,D,h,mm,s,f))

select Datetime2FromParts(Y,M,D,h,mm,s,f,7) from v

執行結果如圖 7 所示:

image

圖 7:透過 Datetime2FromParts函數將分別代表年、月、日、時、分、秒、小數位數等數值轉成日期

從執行結果可以看到當指定 7 位數值時,指定 fractions 是 12345,轉換後的結果為 0.0012345 秒。

而除了上述 DateFromParts、Datetime2FromParts函數外,Denali 尚新增了 DatetimeFromParts、DatetimeOffsetFromParts、SmallDatetimeFromParts、TimeFromParts 等功能近似的函數。

  • EOMonth傳回相對於起始日期,指定新增月份的最後一天,傳回的資料類型會根據第一個參數的資料類型。其範例如下:

with v(n)as(select * from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11)) t(n))

select EOMonth(cast(getdate() as date),n) from v

執行結果如圖 8 所示:

image

圖 8:透過 EOMonth 函數傳回指定月份的最後一天

由於筆者是在 8 月測試並撰寫此文,因此 getdate() 函數傳回的結果加上 0 到 11 個月後,透過 EOMonth 傳回該月的最後一天,而 2012 年 2 月是閏年,故 2 月 29 日為最後一天。

邏輯運算函數
  • Choose與 Reporting Services 的 Choose 函數功能近似,可依據第一個參數的值,動態選擇回傳其後參數的內容。其範例如下:

with v(c1) as (select c1 from (values(1),(2),(3),(4)) t(c1))

select choose(c1, '1', 2, 3.0, convert(char(1),0x34) ) from v

執行結果如圖 9 所示:

image

圖 9:透過 Choose 函數回傳選擇其後參數列內的值

其回傳資料類型會因為呼叫該函數時提供的參數,再參照資料類型轉換優先順序(參照http://technet.microsoft.com/en-us/library/ms190309(SQL.110).aspx)決定。透過前文介紹的 sys.dm_exec_describe_first_result_set 系統動態函數可以檢視其回傳的資料類型:

select system_type_name from sys.dm_exec_describe_first_result_set

('SELECT CHOOSE ( 4, ''1'', 2, 3.0, convert(char(1),0x34) ) AS Result',NULL,0)

其結果為:

numeric(2,1)

  • IIf與 Visual Basic 的 IIF 相同,第一個參數值為真,則取第二個參數回傳,否則傳回第三個參數。其範例如下:

with v(c1,c2)as(select * from (values(1,2),(2,1),(2,2)) t(c1,c2))

select iif(c1>c2,c1,c2) from v

執行結果皆為 2,你可以模擬比較結果與其後選擇的記錄欄位值得知。

字串函數
  • Concat銜接傳入的參數字串,若參數值為 NULL 會被視為空字串(‘’)而非 NULL[1]。其範例如下:

SELECT concat('a','b')

UNION ALL

SELECT concat('a',null,'b')

UNION ALL

SELECT concat(0,null,1.23,'a')

執行結果如圖 10 所示:

image

圖 10:透過 Choose 函數銜接各參數內容回傳字串

  • Format將第一個參數的內容,依第二個參數指定的格式,傳回符合格式的字串,第三個選擇性參數指定的國別。其範例如下:

select format(123,'c4','en-US')

union all

select format(123,'yy/mm/dd','zh-TW')

union all

select format(getdate(),'yyyy/MM/dd','zh-TW')

union all

select format(getdate(),'y-M-d h:m:s')

執行結果如圖 11 所示:

image

圖 11:透過 Choose 函數回傳符合格式的資料

其第二個參數之格式設定可參照 .NET 的格式範本。

  • FORMATMESSAGE這不算新增的函數,但有增強功能。在先前的 SQL Server 版本中,FORMATMESSAGE函數僅從 sys.messages[2] 檢視中現有的記錄建構訊息,例如:

exec sp_addmessage 50001,9,'Hello %s','English'

select FORMATMESSAGE(50001,'World')

其結果為:

Hello World

Denali 後,第一個參數除了可以指定自訂訊息的編號外,也能夠直接輸入帶有預留位置的字串,例如:

SELECT FORMATMESSAGE('資料表 %s 內有 %d 筆記錄', 'tbl', 0);

其結果為:

資料表 tbl 內有 0 筆記錄

其字串中預留位置的表示法如表 1 所示:

類型規格

表示

d 或 i

帶正負號的整數

O

不帶正負號的八進位

S

字串

U

不帶正負號的整數

x 或 X

不帶正負號的十六進位

表 1:字串預留位置定義

分析用函數
  • CUME_DIST:計算群組內各記錄依欄位值的累積分布,也就是特定的記錄值相對於其他值所算出的位置。將群組內記錄依欄位值遞增排序後,若有一筆記錄 r,CUME_DIST 函數會計算群組內欄位值小於等於 r 欄位值的記錄數量,再除以整群記錄的數量。
  • PERCENT_RANK:計算某個多筆記錄的群組內,某筆記錄的依其值在群組內的排名百分率,兩個函數的範例如下:

with v(Part,ID) as (select * from (values(1,1),(1,1),(1,3),

(2,1),(2,2),(2,3),(2,4)) t(Part,ID))

SELECT Part,ID,

CUME_DIST () OVER (PARTITION BY Part ORDER BY ID) AS CumeDist,

PERCENT_RANK() OVER (PARTITION BY Part ORDER BY ID ) AS PctRank

FROM v

執行結果如圖 12 所示:

image

圖 12:分群取回各筆記錄的累積占比與排名百分率

  • First_Value群組內,依排序值的第一個,傳回指定欄位的值。
  • Last_Value似乎是群組內,依排序值若有相同值,取最後一個,傳回指定欄位的值,它似乎運算的邏輯並非與 First_Value 相反[3]。範例如下:

with v(Part,ID,Val) as (select * from (values(1,'a',1),(1,'b',1),(1,'c',3),

(2,'a',1),(2,'b',2),(2,'c',2),(2,'d',4)) t(Part,ID,Val))

SELECT Part,ID,Val,

First_Value (ID) OVER (PARTITION BY Part ORDER BY Val DESC) AS FirstVal,

Last_Value(ID) OVER (PARTITION BY Part ORDER BY Val) AS LastIDVal

FROM v

ORDER BY Part,ID

執行結果如圖 13 所示:

image

圖 13:取得群體內第一筆與同比較值的最後一筆,傳回特定欄位值

  • lag群組內,依照排序取當下成員的前 n 個成員,傳回指定欄位的值或是預設值。
  • lead群組內,依照排序取當下成員的後 n 個成員,傳回指定欄位的值或是預設值。兩個函數的範例如下:

with v(Part,ID) as (select * from (values(1,1),(1,2),(1,3),

(2,1),(2,2),(2,3),(2,4)) t(Part,ID))

SELECT Part,ID,

lag (id,1,-99) OVER (PARTITION BY Part ORDER BY ID) AS Lag1,

lead(id,1,-99) OVER (PARTITION BY Part ORDER BY ID ) AS Lead1

FROM v

執行結果如圖 14 所示:

image

圖 14:傳回依照順序前 n 或後 n 筆記錄的特定欄位值,或是預設值

在上述範例中,不管是呼叫 lag 或 lead 函數,第二參數都賦予 1,所以分別傳回前一或後一筆記錄,當沒有前一或後一筆記錄時,傳回預設值 -99。

with v(Part,ID) as (select * from (values(1,1),(1,2),(1,3),

(2,1),(2,2),(2,3),(2,4)) t(Part,ID))

SELECT Part,ID,

PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY ID)

OVER (PARTITION BY Part) AS [PERCENTILE_CONT(0.5)],

PERCENTILE_CONT(0.6) WITHIN GROUP (ORDER BY ID)

OVER (PARTITION BY Part) AS [PERCENTILE_CONT(0.6)],

PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY ID)

OVER (PARTITION BY Part) AS [PERCENTILE_DISC(0.5)],

PERCENTILE_DISC(0.6) WITHIN GROUP (ORDER BY ID)

OVER (PARTITION BY Part) AS [PERCENTILE_DISC(0.6)]

FROM v

執行結果如圖 15 所示:

image

圖 15:取群組內對應百分比例的內差值,或是特定記錄

從圖 15 可以看到,若是 percentile_cont 函式,當取第一個群組 1、2、3 三筆記錄相對於 0.6 的值時,依照公式計算(公式定義請參照上述網址):

1+(0.6*(3-1)=2.2

代表所要取的值是介於第 2 筆和第 3 筆之間,取內差的結果:

(3-2.2)*2+(2.2-2)*3=2.2

而 PERCENTILE_DISC 函式則似乎是取筆數乘百分率後無條件進入到整數,取其整數對應的記錄回傳。


[1] 照 SQL 的運算定義,任何與 NULL 運算的結果應為 NULL。

[2] 若要將自訂訊息加入到 SQL Server 系統資料表只能利用 sp_addmessage 系統預存程序,而後可以透過 sys.messages 查詢。

[3]雖然網路上的文章大多說這兩個函數功能只是一個取第一,一個取最後,但查詢結果並非如此。

Tags:

胡百敬Byron Hu | SQL Server資料庫

評論 (39) -

cours de theatre
cours de theatre United States
2017/9/30 上午 06:02:47 #

Thank you ever so for you post.Really looking forward to read more. Will read on...

回覆

can ho dic phoenix
can ho dic phoenix United States
2017/10/7 上午 02:08:53 #

This is one awesome post.Thanks Again. Fantastic.

回覆

Osimi sea view
Osimi sea view United States
2017/10/9 下午 07:57:03 #

Great blog.Much thanks again. Really Great.

回覆

Buy illegal backlinks
Buy illegal backlinks United States
2017/10/12 下午 10:35:14 #

Great post.Much thanks again. Keep writing.

回覆

This Site
This Site United States
2017/10/14 下午 05:51:49 #

Awesome blog post.Really thank you! Keep writing.

回覆

Discover More Here
Discover More Here United States
2017/10/17 下午 04:41:10 #

I appreciate you sharing this article post.Much thanks again. Great.

回覆

sletrokor
sletrokor United States
2017/10/17 下午 10:12:37 #

Major thankies for the blog post. Fantastic.

回覆

VigRx
VigRx United States
2017/10/19 上午 09:16:31 #

Thanks for the post.Much thanks again. Really Cool.

回覆

their website
their website United States
2017/10/19 下午 08:23:50 #

Major thanks for the blog.Thanks Again. Awesome.

回覆

carte grise en ligne
carte grise en ligne United States
2017/10/21 上午 09:23:54 #

Very neat blog article.Really looking forward to read more.

回覆

phentaslim
phentaslim United States
2017/11/3 上午 10:58:35 #

I loved your article post.Thanks Again.

回覆

Very informative blog article. Really Great.

回覆

spinal stenosis treatment exercises
spinal stenosis treatment exercises United States
2017/11/15 上午 05:12:02 #

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

回覆

avocat quebec
avocat quebec United States
2017/11/16 下午 03:50:44 #

I really like and appreciate your blog article.Much thanks again. Really Great.

回覆

Thanks-a-mundo for the blog.Thanks Again. Great.

回覆

I am so grateful for your post.Much thanks again. Much obliged.

回覆

swimwear
swimwear United States
2017/11/23 下午 07:12:23 #

wow, awesome blog post.Really looking forward to read more. Really Cool.

回覆

Web Developers Auckland
Web Developers Auckland United States
2017/11/25 下午 04:11:57 #

I truly appreciate this article post.Much thanks again.

回覆

Chad Boonswang SEO
Chad Boonswang SEO United States
2017/11/26 下午 09:20:37 #

Great, thanks for sharing this blog post.Much thanks again. Cool.

回覆

car wrecker scammer
car wrecker scammer United States
2017/11/29 下午 01:46:05 #

Im thankful for the blog article.Really thank you! Really Cool.

回覆

porno
porno United States
2017/12/1 下午 01:54:05 #

A round of applause for your blog. Really Cool.

回覆

primary trade lines
primary trade lines United States
2017/12/3 上午 02:09:21 #

Thanks for the blog post.Much thanks again. Much obliged.

回覆

moble porn
moble porn United States
2017/12/5 上午 06:58:12 #

Thank you ever so for you post.Thanks Again. Awesome.

回覆

Im grateful for the article post.Thanks Again. Awesome.

回覆

my lawyer
my lawyer United States
2017/12/10 下午 03:45:50 #

Muchos Gracias for your blog.Much thanks again. Want more.

回覆

click to investigate
click to investigate United States
2017/12/14 下午 01:37:02 #

Say, you got a nice article post.Really looking forward to read more. Keep writing.

回覆

Christmas Music
Christmas Music United States
2017/12/14 下午 08:03:48 #

I really enjoy the blog post.Thanks Again. Will read on...

回覆

canon drivers
canon drivers United States
2017/12/16 下午 03:19:42 #

wow, awesome article.Much thanks again. Really Great.

回覆

Transition
Transition United States
2017/12/17 下午 03:02:52 #

Thank you ever so for you post. Will read on...

回覆

Degreeshortcut
Degreeshortcut United States
2017/12/17 下午 10:03:05 #

Say, you got a nice blog.Much thanks again. Want more.

回覆

make money online free
make money online free United States
2017/12/20 下午 04:10:32 #

Say, you got a nice blog post.Much thanks again. Will read on...

回覆

Thanks so much for the blog post. Will read on...

回覆

canon printer series
canon printer series United States
2017/12/27 下午 04:16:03 #

wow, awesome blog.Thanks Again.

回覆

drivers hp
drivers hp United States
2018/1/2 上午 07:00:40 #

Thank you ever so for you article post.Really thank you! Much obliged.

回覆

Im grateful for the blog post.Really looking forward to read more. Really Great.

回覆

hp drivers
hp drivers United States
2018/1/3 上午 06:18:52 #

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

回覆

web hosting
web hosting United States
2018/1/10 上午 11:48:02 #

Really appreciate you sharing this article.Much thanks again. Want more.

回覆

新增評論




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






NET Magazine國際中文電子雜誌

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

月分類Month List