SQL Server 2012 ：認識 TRY_PARSE 轉換函數

適用版本：SQL Server 2012。

TRY_PARSE() 轉換函數
將會傳回運算式的結果，並轉譯為所要求的資料類型；若轉換失敗，則傳回 Null。

語法：

TRY_PARSE ( string_value AS data_type [ USING culture ] )

---

建議先閱讀以下的文章：

T-SQL：認識 PARSE 轉換函數
http://sharedderrick.blogspot.tw/2012/06/t-sql-parse.html

---

請參考以下的範例程式碼：

EX1：認識 PARSE() 與 TRY_PARSE() 轉換函數

-- PARSE() 轉換函數：
-- 繁體中文，日期的順序是：ymd
-- 英文 us_english，日期的順序是：mdy
SELECT PARSE('01/02/03' AS datetime2 USING 'zh-TW') N'日期(zh-TW)'
GO
SELECT PARSE('01/02/03' AS datetime2 USING 'en-US') N'日期(en-US)'
GO

-- TRY_PARSE() 轉換函數：
-- 繁體中文，日期的順序是：ymd
-- 英文 us_english，日期的順序是：mdy
SELECT TRY_PARSE('01/02/03' AS datetime2 USING 'zh-TW') N'日期(zh-TW)'
GO
SELECT TRY_PARSE('01/02/03' AS datetime2 USING 'en-US') N'日期(en-US)'
GO

-- 01_認識 PARSE() 與 TRY_PARSE() 轉換函數

---

EX2：刻意輸入不符合的資料，遭遇到錯誤

-- PARSE() 轉換函數：輸入台幣，但卻是用 en-US 來剖析
SELECT PARSE('NT$123,45' AS money USING 'en-US') N'金額(美金)'
GO
/*
錯誤訊息：
訊息 9819，層級 16，狀態 1，行 1
使用 'en-US' 文化特性將字串值 'NT$123,45' 轉換成資料類型 money 時發生錯誤。
*/

-- PARSE() 轉換函數：不存在的 culture 值
SELECT PARSE('NT$123,45' AS money USING 'en-DE') N'金額(美金)'
GO
/*
錯誤訊息：
訊息 9818，層級 16，狀態 1，行 1
函數呼叫中所提供的文化特性參數 'en-DE' 不受支援。
*/

-- 02_PARSE() 轉換函數：輸入台幣，但卻是用 en-US 來剖析

-- 03_PARSE() 轉換函數：不存在的 culture 值

-- TRY_PARSE() 轉換函數：輸入台幣，但卻是用 en-US 來剖析
SELECT TRY_PARSE('NT$123,45' AS money USING 'en-US') N'金額(美金)'
GO
/*
回傳：NULL
*/

-- 04_TRY_PARSE() 轉換函數：輸入台幣，但卻是用 en-US 來剖析

-- TRY_PARSE() 轉換函數：不存在的 culture 值
SELECT TRY_PARSE('NT$123,45' AS money USING 'en-DE') N'金額(美金)'
GO
/*
錯誤訊息：
訊息 9818，層級 16，狀態 1，行 1
函數呼叫中所提供的文化特性參數 'en-DE' 不受支援。
*/

-- 05_TRY_PARSE() 轉換函數：不存在的 culture 值

---

EX3：TRY_PARSE() 轉換函數：判斷輸入的字串，是否可以轉換為資料類型

-- TRY_PARSE() 轉換函數：去除貨幣符號，轉為台幣
SELECT TRY_PARSE('NT$123,45' AS money USING 'zh-TW') N'金額(台幣)'
GO

-- TRY_PARSE() 轉換函數：輸入字串：NT$123,45，判斷輸入的字串，是否可以轉換為指定的台幣貨幣
DECLARE @cs1 varchar(100)='NT$123,45', @cmoney money
SET @cmoney=TRY_PARSE(@cs1 AS money USING 'zh-TW')

IF @cmoney IS NOT NULL
 SELECT @cmoney N'轉換結果_金額(台幣)'
ELSE
 SELECT N'轉換失敗' N'轉換結果'
GO

-- 06_TRY_PARSE() 轉換函數：輸入字串：NT$123,45，判斷輸入的字串，是否可以轉換為指定的台幣貨幣

-- TRY_PARSE() 轉換函數：輸入字串：NT$123,45，嘗試轉換為歐元貨幣
SELECT TRY_PARSE('NT$123,45' AS money USING 'de-DE') N'金額(歐元)'
GO
/*
回傳：NULL
*/

-- TRY_PARSE() 轉換函數：輸入字串：NT$123,45，判斷是否可以轉換為歐元貨幣
DECLARE @cs1 varchar(100)='NT$123,45', @cmoney money
SET @cmoney=TRY_PARSE(@cs1 AS money USING 'de-DE')

IF @cmoney IS NOT NULL
 SELECT @cmoney N'轉換結果金額(歐元)'
ELSE
 SELECT N'轉換失敗' N'轉換結果'
GO

-- 07_TRY_PARSE() 轉換函數：輸入字串：NT$123,45，判斷是否可以轉換為歐元貨幣

-- TRY_PARSE() 轉換函數，搭配使用 CASE 運算式
-- 輸入字串：NT$123,45，判斷是否可以轉換為歐元貨幣
DECLARE @cs1 varchar(100)='NT$123,45', @cmoney money
SET @cmoney=TRY_PARSE(@cs1 AS money USING 'de-DE')

SELECT 
 CASE WHEN @cmoney IS NOT NULL
  THEN @cmoney
  ELSE 0 
 END N'轉換結果'
GO

-- 08_TRY_PARSE() 轉換函數，搭配使用 CASE 運算式

---

參考資料

T-SQL：認識 PARSE 轉換函數
http://sharedderrick.blogspot.tw/2012/06/t-sql-parse.html


TRY_PARSE (Transact-SQL)
http://msdn.microsoft.com/zh-tw/library/hh213126


TRY_CONVERT (Transact-SQL)
http://msdn.microsoft.com/zh-tw/library/hh230993

轉換函數 (Transact-SQL)
http://msdn.microsoft.com/zh-tw/library/hh231076

CASE (Transact-SQL)
http://msdn.microsoft.com/zh-tw/library/ms181765.aspx

SQL Server 2012 ：認識 TRY_CONVERT 轉換函數

適用版本：SQL Server 2012。


TRY_CONVERT() 轉換函數

如果轉換成功，會傳回轉換為指定之資料類型的值；否則會傳回 Null。


CAST 和 CONVERT 都是轉換函數。
CAST 是 ANSI-SQL 標準函數，但 CONVERT 是 SQL Server 所提供。

---

請參考以下的範例程式碼：

EX1：認識 TRY_CONVERT() 與 CONVERT() 轉換函數

-- 使用 CONVERT() 轉換函數
SELECT 'ANSI：' N'標準', CONVERT(varchar(30), GETDATE(),102) N'樣式'
 UNION ALL
SELECT 'Japanese：' N'標準', CONVERT(varchar(30),GETDATE(),111) N'樣式'
 UNION ALL
SELECT 'European：' N'標準', CONVERT(varchar(30),GETDATE(),113) N'樣式'
GO

-- 使用 TRY_CONVERT() 轉換函數
SELECT 'ANSI：' N'標準', TRY_CONVERT(varchar(30), GETDATE(),102) N'樣式'
 UNION ALL
SELECT 'Japanese：' N'標準', TRY_CONVERT(varchar(30),GETDATE(),111) N'樣式'
 UNION ALL
SELECT 'European：' N'標準', TRY_CONVERT(varchar(30),GETDATE(),113) N'樣式'
GO

-- 01_認識 TRY_CONVERT() 與 CONVERT() 轉換函數

-- 使用 CONVERT() 函數，轉換不存在日期：2012年02月31日
SELECT CONVERT(datetime2, '20120231')
GO
/*
錯誤訊息：
訊息 241，層級 16，狀態 1，行 5
從字元字串轉換成日期及/或時間時，轉換失敗。
*/

-- 02_使用 CONVERT() 函數，轉換不存在日期：2012年02月31日

-- 使用 TRY_CONVERT() 函數，轉換不存在日期：2012年02月31日
SELECT TRY_CONVERT(datetime2, '20120231')
GO
/*
回傳：NULL
*/

-- 03_使用 TRY_CONVERT() 函數，轉換不存在日期：2012年02月31日

---

EX2：TRY_CONVERT() 轉換函數：判斷輸入的字串，是否可以轉換為數值

-- 輸入字串：A123，判斷輸入的字串，是否可以轉換為數值
DECLARE @cs1 varchar(100) ='A123', @cnt INT
SET @cnt=TRY_CONVERT(int, @cs1)

IF @cnt IS NOT NULL
 SELECT @cnt N'轉換結果'
ELSE
 SELECT N'轉換失敗' N'轉換結果'
GO

-- 04_輸入字串：A123，判斷輸入的字串，是否可以轉換為數值

-- 輸入字串：123，判斷輸入的字串，是否可以轉換為數值
DECLARE @cs1 varchar(100) ='123', @cnt INT
SET @cnt=TRY_CONVERT(int, @cs1)

IF @cnt IS NOT NULL
 SELECT @cnt N'轉換結果'
ELSE
 SELECT N'轉換失敗' N'轉換結果'
GO

-- 05_輸入字串：123，判斷輸入的字串，是否可以轉換為數值

-- TRY_CONVERT() 轉換函數，搭配使用 CASE 運算式
-- 輸入字串：A123
DECLARE @cs1 varchar(100) ='A123', @cnt INT
SET @cnt=TRY_CONVERT(int, @cs1)

SELECT 
 CASE WHEN @cnt IS NOT NULL
  THEN @cnt
  ELSE 0 
 END N'轉換結果'
GO

-- 06_TRY_CONVERT() 轉換函數，搭配使用 CASE 運算式

---

EX3：TRY_CONVERT() 轉換函數，搭配查詢資料表

-- TRY_CONVERT() 轉換函數：轉換為整數
USE Northwind
GO
SELECT  CustomerID, City, PostalCode,
 TRY_CONVERT(int, PostalCode) N'PostalCode_TRY_CONVERT_整數'
FROM Customers
GO

-- 07_TRY_CONVERT() 轉換函數：轉換為整數

-- TRY_CONVERT() 轉換函數：轉換為整數，搭配 ISNULL() 函數
SELECT  CustomerID, City, PostalCode,
 TRY_CONVERT(int, PostalCode) N'PostalCode_TRY_CONVERT_整數',
 ISNULL(TRY_CONVERT(int, PostalCode), 0) N'PostalCode_TRY_CONVERT_ISNULL_整數'
FROM Customers
GO

-- 08_TRY_CONVERT() 轉換函數：轉換為整數，搭配 ISNULL() 函數

---

認識 TRY_CONVERT 轉換函數

TRY_CONVERT() 轉換函數
如果轉換成功，會傳回轉換為指定之資料類型的值；否則會傳回 Null。

語法：

TRY_CONVERT ( data_type [ ( length ) ], expression [, style ] )

引數說明：
(1) data_type [ ( length ) ]
expression 轉換成的資料類型。

(2) expression
要進行轉換的值。

(3) style
這是指定 TRY_CONVERT 函數如何轉譯 expression 的選用性整數運算式。

style 接受與 CONVERT 函數的 style 參數相同的值。 如需詳細資訊，請參閱＜CAST 和 CONVERT (Transact-SQL)＞。

可接受值的範圍由 data_type 值決定。 如果 style 是 Null，則 TRY_CONVERT 會傳回 Null。

注意事項：

TRY_CONVERT 會取得傳送的值，並會嘗試將其轉換為指定的 data_type。
如果轉換成功，TRY_CONVERT 會以指定的 data_type 傳回值；如果發生錯誤則會傳回 Null。

但是，若您要求明確不允許的轉換，則 TRY_CONVERT 會失敗並出現錯誤。

TRY_CONVERT 是一個新的關鍵字，相容性層級為 110。

函數能以遠端方式在具有 SQL Server 2012 及更高版本的伺服器上運作。
它在版本低於 SQL Server 2012 的伺服器上無法以遠端方式運作。

---

參考資料

TRY_CONVERT (Transact-SQL)
http://msdn.microsoft.com/zh-tw/library/hh230993

轉換函數 (Transact-SQL)
http://msdn.microsoft.com/zh-tw/library/hh231076

CASE (Transact-SQL)
http://msdn.microsoft.com/zh-tw/library/ms181765.aspx

SQL Server 2012 ：認識 PARSE 轉換函數

適用版本：SQL Server 2012。

PARSE 轉換函數
傳回轉譯成所要求之資料類型的運算式結果。

PARSE 適用於從字串轉換到日期/時間、數值、貨幣等類型。
也可以使用貨幣符號進行剖析。

一般類型轉換仍可繼續使用 CAST 或 CONVERT。
 請注意，剖析字串值將對效能造成一定程度的負擔。

語法：

PARSE ( string_value AS data_type [ USING culture ] )

如未提供 culture 引數，將會使用目前工作階段的語言。
此語言是以 SET LANGUAGE 陳述式隱含或明確加以設定。

culture 可以是 .NET Framework 所支援的任何文化特性，不限於 SQL Server 明確支援的語言。
如果 culture 引數無效，PARSE 將會產生錯誤。

---

請參考以下的範例程式碼：

EX1：PARSE() 轉換函數，轉換為 datetime2 資料類型

 -- 繁體中文，日期的順序是：ymd
 SELECT PARSE('01/02/03' AS  datetime2 USING 'zh-TW') N'日期'
GO

 -- 英文 us_english，日期的順序是：mdy
SELECT PARSE('01/02/03' AS  datetime2 USING 'en-US') N'日期'
GO

-- 01_PARSE() 轉換函數，轉換為 datetime2 資料類型

---

EX2：PARSE() 轉換函數，使用貨幣符號進行剖析

-- 去除貨幣符號
-- 台幣
SELECT PARSE('NT$123,45' AS money USING 'zh-TW') N'金額(台幣)'
GO

-- 歐元
SELECT PARSE('€123,45' AS money USING 'de-DE') N'金額(歐元)'
GO

-- 美金
SELECT PARSE('$123,45' AS money USING 'en-US') N'金額(美金)'
GO

-- 02_PARSE() 轉換函數，使用貨幣符號進行剖析

---

EX3：刻意輸入不符合的資料，遭遇到錯誤

-- 輸入台幣，但卻是用 en-US 來剖析
SELECT PARSE('NT$123,45' AS money USING 'en-US') N'金額(美金)'
GO
/*
錯誤訊息：
訊息 9819，層級 16，狀態 1，行 1
使用 'en-US' 文化特性將字串值 'NT$123,45' 轉換成資料類型 money 時發生錯誤。
*/

-- 03_錯誤訊息 9819

-- 不存在的 culture 值
SELECT PARSE('NT$123,45' AS money USING 'en-DE') N'金額(美金)'
GO
/*
錯誤訊息：
訊息 9818，層級 16，狀態 1，行 1
函數呼叫中所提供的文化特性參數 'en-DE' 不受支援。
*/

-- 04_錯誤訊息 9818

---

認識 PARSE 轉換函數

傳回轉譯成所要求之資料類型的運算式結果。

語法：

PARSE ( string_value AS data_type [ USING culture ] )

引數說明：
(1) string_value
nvarchar(4000) 值，代表要剖析為指定之資料類型的格式化值。

string_value 必須是所要求之資料類型的有效表示法，否則 PARSE 會引發錯誤。

(2) data_type
表示結果之資料類型的常值。

(3) culture
指出 string_value 據以格式化之文化特性的選用字串。

如未提供 culture 引數，將會使用目前工作階段的語言。
此語言是以 SET LANGUAGE 陳述式隱含或明確加以設定。

culture 可以是 .NET Framework 所支援的任何文化特性，不限於 SQL Server 明確支援的語言。
如果 culture 引數無效，PARSE 將會產生錯誤。

注意事項

以引數形式傳遞給 PARSE 的 Null 值，會以下列兩種方式處理：

(1) 如果傳遞了 NULL 常數就會引發錯誤。 Null 值無法以特定文化特定方式剖析為不同的資料類型。

(2) 如果在執行階段傳遞了 null 值的參數，將會傳回 null，以避免整個批次遭到取消。

PARSE 僅適用於從字串轉換到日期/時間及數字類型。
一般類型轉換仍可繼續使用 CAST 或 CONVERT。 請注意，剖析字串值將對效能造成一定程度的負擔。

PARSE 必須仰賴既存的 .NET Framework Common Language Runtime (CLR)。

因為必須要有 CLR 才可執行此函數，所以無法從遠端進行。 從遠端處理需要 CLR 的函數，會導致遠端伺服器發生錯誤。

data_type 參數的詳細資訊

data_type 的參數值僅適用於下表所示的類型與樣式。 此處所提供的樣式資訊可以協助您決定所要允許的模式類型。

-- 05_支援的資料類型

如需有關樣式的詳細資訊，請參閱 .NET Framework 文件集中的 System.Globalization.NumberStyles 和 DateTimeStyles 列舉。

---

參考資料

PARSE (Transact-SQL)
http://msdn.microsoft.com/zh-tw/library/hh213316.aspx

轉換函數 (Transact-SQL)
http://msdn.microsoft.com/zh-tw/library/hh231076

T-SQL：認識日期時間的分隔符號；搭配使用 SET DATEFORMAT、SET LANGUAGE
http://sharedderrick.blogspot.tw/2012/06/t-sql-set-dateformatset-language.html

SQL Server 2012 ：認識 CONCAT 字串函數

適用版本：SQL Server 2012。

CONCAT 字串函數，可以用來連接多項資料，回傳字串類型。

---

請參考以下的範例程式碼：

EX1：認識 CONCAT() 函數

-- 錯誤：資料類型不相容
SELECT N'伺服器目前的時間是：'+ SYSDATETIME()
GO
/*
回傳的錯誤訊息：
訊息 402，層級 16，狀態 1，行 1
add 運算子中的資料類型 nvarchar 和 datetime2 不相容。
*/

-- 01_遭遇到錯誤

-- 使用 CONCAT() 函數來連接資料
SELECT CONCAT(N'伺服器目前的時間是：', SYSDATETIME())
GO

-- 02_使用 CONCAT() 函數來連接資料

---

EX2：連接多個資料行的資料

-- 僅是使用 + (加號) 去串連有 NULL 值的資料行
USE Northwind
GO
SELECT CustomerID, City, Region, Country,
 City+ ', ' + Region + ', ' + Country N'地區' 
FROM dbo.Customers
GO

-- 03_僅是使用 + (加號) 去串連有 NULL 值的資料行

-- 使用 CONCAT() 函數，連接多個資料行的資料
-- 請留意：資料行 Region 部分是 NULL
SELECT CustomerID, City, Region, Country,
 CONCAT(City, ', ' + Region, + ', ' + Country) N'地區' 
FROM dbo.Customers
GO

-- 04_使用 CONCAT() 函數，連接多個資料行的資料，有包含 NULL

-- 若是改用 ISNULL() 函數
SELECT CustomerID, City, Region, Country,
 City + ', ' + ISNULL(Region,'') + ', ' + Country N'地區'
FROM dbo.Customers
GO

-- 若是改用 COALESCE() 函數
SELECT CustomerID, City, Region, Country,
 City + ', ' + COALESCE(Region,'') + ', ' + Country N'地區'
FROM dbo.Customers
GO

-- 05_若是改用 ISNULL() 函數

---

認識 CONCAT (Transact-SQL)

傳回串連兩個以上之字串值的結果字串。

語法：

CONCAT ( string_value1, string_value2 [, string_valueN ] )

CONCAT 會採用可變數量的字串引數，並將其連成單一字串。
其至少需要兩個輸入值，否則會引發錯誤。

所有引數皆會以隱含方式轉換為字串類型，然後再行串連。
Null 值以隱含方式轉換為空白字串。 如果所有引數都是 Null，會傳回類型為 varchar(1) 的空白字串。

隱含轉換成字串會遵循現有的資料類型轉換規則。

---

參考資料

CONCAT (Transact-SQL)
http://technet.microsoft.com/zh-tw/library/hh231515

T-SQL：認識 COALESCE() 函數
http://sharedderrick.blogspot.tw/2012/06/t-sql-coalesce.html

T-SQL：認識 COALESCE() 函數，補充範例
http://sharedderrick.blogspot.tw/2012/06/t-sql-coalesce_14.html

T-SQL：認識 ISNULL 函數
http://sharedderrick.blogspot.tw/2012/06/t-sql-isnull.html

SQL SERVER – Denali – String Function – CONCAT() – A Quick Introduction
http://blog.sqlauthority.com/2011/09/16/sql-server-denali-string-function-concat-a-quick-introduction/

SQL Server 2012 ：分頁處理：認識 OFFSET 和 FETCH 子句

適用版本：SQL Server 2012。

OFFSET - FETCH 是 ORDER BY 子句的延伸功能。

使用 OFFSET 和 FETCH 限制傳回的資料列。
讓你可以過濾篩選特定範圍的資料列。

提供了對結果集的分頁處理功能。
可以指定跳過的行數，指定要取回的資料列筆數。

而且，OFFSET 和 FETCH 子句是依據 draft ANSI SQL:2011 標準。
因此，會比 TOP 子句具備更好的 SQL 語言相容性。

語法：

ORDER BY {order_by_list}
OFFSET {offset_value} ROW(S)
FETCH FIRST|NEXT {fetch_value} ROW(S) ONLY

其中 OFFSET 是必要子句，不可以省略，但 OFFSET 是選擇性子句。

---

請參考以下的範例程式碼： 使用 OFFSET 和 FETCH 來限制查詢所傳回的資料列數目

EX1. 為 OFFSET 和 FETCH 值指定整數常數

-- 查詢資料表的內容
USE Northwind
GO
SELECT OrderID, CustomerID, EmployeeID, OrderDate
FROM dbo.Orders;
GO

-- 00_查詢資料表的內容

-- 使用 TOP 子句：查詢最近的 50 筆訂單記錄，0.042186
SELECT TOP 50 OrderID, CustomerID, EmployeeID, OrderDate
FROM dbo.Orders
ORDER BY OrderDate DESC;
GO

-- 01_使用 TOP 子句：查詢最近的 50 筆訂單記錄

--  使用 OFFSET 和 FETCH 子句：查詢最近的 50 筆訂單記錄，0.042186
SELECT OrderID, CustomerID, EmployeeID, OrderDate
FROM dbo.Orders
ORDER BY OrderDate DESC
 OFFSET 0 ROWS 
 FETCH FIRST 50 ROWS ONLY;
GO

-- 02_使用 OFFSET 和 FETCH 子句：查詢最近的 50 筆訂單記錄

--  使用 OFFSET 和 FETCH 子句：依據日期排序，但是跳過前 10 筆資料列
SELECT OrderID, CustomerID, EmployeeID, OrderDate
FROM dbo.Orders
ORDER BY OrderDate DESC
 OFFSET 10 ROWS;
GO

-- 03_使用 OFFSET 和 FETCH 子句：依據日期排序，但是跳過前 10 筆資料列

---

EX2. 為 OFFSET 和 FETCH 值指定變數

-- 使用 OFFSET 和 FETCH
-- 依據日期排序，但是跳過前 10 筆資料列，再取 10 筆資料列；也就是說：依據日期排序，但僅查詢第 11 到 20 筆的資料列。
-- 0.042196
USE Northwind
GO
DECLARE @OFFSET tinyint =10, @FETCH tinyint =10

SELECT OrderID, CustomerID, EmployeeID, OrderDate
FROM dbo.Orders
ORDER BY OrderDate DESC
 OFFSET @OFFSET ROWS
 FETCH NEXT @FETCH ROWS ONLY;
GO

-- 04_使用 OFFSET 和 FETCH 子句：依據日期排序，但僅查詢第 11 到 20 筆的資料列

-- 05_檢視回傳的資料列

-- 使用 ROW_NUMBER() 函數
-- 傳回結果集資料分割內某資料列的序號，序號從 1 開始，每個資料分割第一個資料列的序號是 1。
-- 適用版本：SQL Server 2005、2008、2008 R2、2012
-- 0.042294
DECLARE @begin tinyint =11, @end tinyint =20

SELECT OrderID, CustomerID, EmployeeID, OrderDate 
FROM(
 SELECT ROW_NUMBER() OVER (ORDER BY OrderDate DESC) rid, 
  OrderID, CustomerID, EmployeeID, OrderDate
 FROM dbo.Orders) rktb
WHERE rid BETWEEN @begin AND @end
GO

-- 06_使用 ROW_NUMBER() 函數：依據日期排序，但僅查詢第 11 到 20 筆的資料列

與 OFFSET 和 FETCH 子句比較起來，使用 ROW_NUMBER() 函數會多耗用一點資源。

---

EX3. 為 OFFSET 和 FETCH 值指定常數純量子查詢

-- 依據日期排序，跳過前 10 筆資料列，利用亂數 RAND() 函數，隨機查詢前 0 ~ 99 筆資料列。
USE Northwind
GO
DECLARE @OFFSET tinyint =10

SELECT OrderID, CustomerID, EmployeeID, OrderDate
FROM dbo.Orders
ORDER BY OrderDate DESC
 OFFSET @OFFSET ROWS
 FETCH NEXT (SELECT CAST(RAND()*100 AS tinyint)) ROWS ONLY;
GO

-- 07_為 OFFSET 和 FETCH 值指定常數純量子查詢

---

認識 OFFSET 和 FETCH 子句

語法如下：

ORDER BY order_by_expression
    [ COLLATE collation_name ] 
    [ ASC | DESC ] 
    [ ,...n ] 
[  ]

 ::=
{ 
    OFFSET { integer_constant | offset_row_count_expression } { ROW | ROWS }
    [
      FETCH { FIRST | NEXT } {integer_constant | fetch_row_count_expression } { ROW | ROWS } ONLY
    ]
}

(1)
OFFSET { integer_constant | offset_row_count_expression } { ROW | ROWS }

指定要略過的資料列數目，然後才開始從查詢運算式傳回資料列。
值可以是大於或等於零的整數常數或運算式。

offset_row_count_expression 可以是變數、參數或常數純量子查詢。
在使用子查詢時，它無法參考定義在外部查詢範圍中的任何資料行。也就是，它不能與外部查詢相互關聯。

ROW 和 ROWS 是同義字，基於 ANSI 相容性提供它們。

在查詢執行計畫中，位移資料列計數值會顯示在 TOP 查詢運算子的 Offset 屬性中。

(2)
FETCH { FIRST | NEXT } { integer_constant | fetch_row_count_expression } { ROW | ROWS } ONLY

指定要在已處理 OFFSET 子句之後傳回的資料列數目。
值可以是大於或等於一的整數常數或運算式。

fetch_row_count_expression 可以是變數、參數或常數純量子查詢。
在使用子查詢時，它無法參考定義在外部查詢範圍中的任何資料行。 也就是，它不能與外部查詢相互關聯。

FIRST 和 NEXT 是同義字，基於 ANSI 相容性提供它們。
ROW 和 ROWS 是同義字，基於 ANSI 相容性提供它們。

在查詢執行計畫中，位移資料列計數值會顯示在 TOP 查詢運算子的 Rows 或 Top 屬性中。

---

(一)
在允許 TOP 和 ORDER BY 的任何查詢中，都可以使用 OFFSET 和 FETCH，但有下列限制：

3-1：OVER 子句不支援 OFFSET 和 FETCH。

3-2：OFFSET 和 FETCH 不能直接在 INSERT、UPDATE、MERGE 和 DELETE 陳述式中指定，但是可以在這些陳述式中所定義的子查詢中指定。
例如，在 INSERT INTO SELECT 陳述式中，OFFSET 和 FETCH 可以在 SELECT 陳述式中指定。

3-3：在使用 UNION、EXCEPT 或 INTERSECT 運算子的查詢中，只能在指定查詢結果順序的最後查詢中指定 OFFSET 和 FETCH。

3-4：TOP 無法與相同查詢運算式 (相同查詢範圍) 中的 OFFSET 和 FETCH 結合。

(二)
使用 OFFSET 和 FETCH 限制傳回的資料列

我們建議您使用 OFFSET 和 FETCH 子句 (而不要使用 TOP 子句)，來實作查詢分頁方案，並限制傳送給用戶端應用程式的資料列數目。

如果使用 OFFSET 和 FETCH 做為分頁方案，需要針對傳回給用戶端應用程式的每一「頁」資料執行查詢一次。

例如，若要以 10 個資料列的增量傳回查詢結果，您必須執行一次查詢傳回 1 到 10 的資料列，然後再執行一次查詢傳回 11 到 20 的資料列，依此類推。
每個查詢各自獨立，無論如何都不相關。
這表示，不同於使用資料指標執行查詢一次，並在伺服器上維護狀態，用戶端應用程式會負責追蹤狀態。

若要使用 OFFSET 和 FETCH，在查詢要求之間達到穩定結果，必須符合下列條件：

(1) 查詢所使用的基礎資料不可以變更。

也就是說，查詢接觸的資料列不會更新，或者對網頁的所有查詢要求都是在使用快照集或可序列化交易隔離的單一交易中執行。
如需這些交易隔離等級的詳細資訊，請參閱＜SET TRANSACTION ISOLATION LEVEL (Transact-SQL)＞。

ORDER BY 子句包含的資料行或資料行組合保證是唯一。

請參閱本主題稍後的＜範例＞一節中的範例＜在單一交易中執行多個查詢＞。

(2) 如果一致的執行計畫是分頁方案的要素，請考慮為 OFFSET 和 FETCH 參數使用 OPTIMIZE FOR 查詢提示。

如需有關 OPTIMZE FOR 的詳細資訊，請參閱＜查詢提示 (Transact-SQL)＞。

---

參考資料

ORDER BY 子句 (Transact-SQL)
http://msdn.microsoft.com/zh-tw/library/ms188385%28v=SQL.110%29.aspx

TOP (Transact-SQL)
http://msdn.microsoft.com/zh-tw/library/ms189463.aspx

次序函數 (Transact-SQL)
http://msdn.microsoft.com/zh-tw/library/ms189798.aspx

ROW_NUMBER (Transact-SQL)
http://technet.microsoft.com/zh-tw/library/ms186734.aspx

SQL:2011
http://en.wikipedia.org/wiki/SQL:2011

What's new in SQL:2011
http://www.sigmod.org/publications/sigmod-record/1203/pdfs/10.industry.zemke.pdf

Implement LIMIT keyword
http://connect.microsoft.com/SQLServer/feedback/details/124495/implement-limit-keyword

新手學SQL Server 2012「自主資料庫(Contained Database)」(2)

在前一篇文章中，介紹了在伺服器層級上啟用自主資料庫驗證、建立自主資料庫、在自主資料庫上建立有密碼的SQL使用者以及使用具有密碼的自主資料庫之使用者來登入系統等主題。

在本期文章內，我們將繼續討論在「自主資料庫」上撰寫Transact-SQL陳述式、定序與自主資料庫之間關係討論、備份自主資料庫、還原自主資料庫等主題。

完整文章，請參考以下的網址：
新手學SQL Server 2012「自主資料庫(Contained Database)」(2)

---

參考資料

新手學SQL Server 2012「自主資料庫(Contained Database)」(1)
http://sharedderrick.blogspot.tw/2012/04/sql-server-2012contained-database1.html

SQL Server：認識 COALESCE() 函數，補充範例

請先參考前一篇文章：
T-SQL：認識 COALESCE() 函數
http://sharedderrick.blogspot.tw/2012/06/t-sql-coalesce.html

---

請參考以下的範例：

EX1. 刪除 SQL Server 上全部的連線，但保留本身這條連線不會刪除

-- 檢視 SQL Server 目前所有的連線 session ;所有目前的處理序
USE master
GO
SELECT * FROM sys.dm_exec_connections
GO

-- 01_查詢目前所有的連線

-- 使用 ISNULL() 函數：刪除 SQL Server 上全部的連線，但保留本身這條連線不會刪除
DECLARE @str1 nvarchar(MAX)

SELECT @str1 = ISNULL(@str1, '') + 'KILL ' + CAST(session_id AS nvarchar(10)) + ';'
FROM sys.dm_exec_connections
WHERE session_id<>@@SPID

-- PRINT @str1
EXEC sp_executesql @str1
GO

/*
PRINT 陳述式所回傳的結果：
KILL 52;KILL 51;KILL 53;KILL 54;KILL 56;KILL 57;KILL 58;KILL 59;KILL 60;KILL 61;KILL 62;KILL 63;KILL 64;KILL 65;
*/

-- 02_PRINT 陳述式所回傳的結果

-- 使用 COALESCE() 函數：刪除 SQL Server 上全部的連線，但保留本身這條連線不會刪除
DECLARE @str1 nvarchar(MAX)

SELECT @str1 = COALESCE(@str1, '') + 'KILL ' + CAST(session_id AS nvarchar(10)) + ';'
FROM sys.dm_exec_connections
WHERE session_id<>@@SPID

-- PRINT @str1
EXEC sp_executesql @str1
GO

-- 03_只剩下自身此條連線

---

EX2. 使用逗號分隔回傳的資料

-- 使用 ISNULL() 函數：使用逗號分隔回傳的資料
USE Northwind
GO
DECLARE @titlelist varchar(MAX)
SELECT @titlelist = ISNULL(@titlelist + ',' , '') + Title
FROM dbo.Employees

SELECT @titlelist N'職稱列表'
GO

-- 使用 COALESCE() 函數：使用逗號分隔
USE Northwind
GO
DECLARE @titlelist varchar(MAX)
SELECT @titlelist = COALESCE(@titlelist + ',' , '') + Title
FROM dbo.Employees

SELECT @titlelist N'職稱列表'
GO

-- 04_使用逗號分隔回傳的資料

---

參考資料

T-SQL：認識 COALESCE() 函數
http://sharedderrick.blogspot.tw/2012/06/t-sql-coalesce.html

How to use COALESCE and ISNULL?
http://www.sqlusa.com/bestpractices/coalesce/

Performance of COALESCE vs. IS NULL
http://sqlserverperformance.idera.com/tsql-optimization/performance-coalesce-null/

SQL Server：認識 COALESCE() 函數

比較 ISNULL() 函數以及 COALESCE() 函數

(1)
COALESCE() 函數是 ANSI SQL-92 標準。
ISNULL() 函數不是 ANSI SQL-92 標準。

(2)
COALESCE() 函數，支援多個輸入參數。
若只有使用兩個參數，則 COALESCE() 函數的行為類似於 ISNULL() 函數。

(3)
若是有多個參數時，COALESCE() 函數 ，可以替代 CASE 運算式搭配 ISNULL() 函數。

(4)
若以效能觀點來看，在某些情境下，ISNULL() 函數可能會優於 COALESCE() 函數。

請參考以下的文章：
Performance of COALESCE vs. IS NULL
http://sqlserverperformance.idera.com/tsql-optimization/performance-coalesce-null/

---

以下範例示範使用 COALESCE() 函數：

EX1. 兩個參數：ISNULL() 函數、COALESCE() 函數

USE Northwind
GO
-- 使用 COALESCE() 函數
SELECT CustomerID, Country, Region, City, 
 Country + ',' + COALESCE(Region, ' ') + ',' + City N'地區'
FROM dbo.Customers
GO
-- 使用 ISNULL() 函數
SELECT CustomerID, Country, Region, City, 
 Country + ',' + ISNULL(Region, ' ') + ',' + City N'地區'
FROM dbo.Customers
GO

-- 01_兩個參數：ISNULL() 函數、COALESCE() 函數

---

EX2. 比較 ISNULL() 函數以及 COALESCE() 函數

-- 使用 ISNULL() 函數，僅支援兩個參數
SELECT ISNULL(NULL, 'A') -- 回傳 A
GO
--
SELECT ISNULL(NULL, NULL) -- 回傳 NULL
GO

-- 02_使用 ISNULL() 函數，僅支援兩個參數

-- ISNULL() 函數，卻輸入三個參數
SELECT ISNULL(NULL, NULL, 'A')  -- 失敗
GO
/*
訊息 174，層級 15，狀態 1，行 2
isnull 函數需要 2 個引數。
*/

-- 03_ISNULL() 函數，卻輸入三個參數

-- 使用 COALESCE() 函數
SELECT COALESCE(NULL, 'A') -- 回傳 A
GO

-- 04_使用 COALESCE() 函數

-- COALESCE() 函數，全部的參數都是 NULL
SELECT COALESCE(NULL, NULL) -- 失敗
GO
/*
訊息 4127，層級 16，狀態 1，行 1
COALESCE 的引數至少要有一個是非 NULL 常數的運算式。
*/

-- 05_COALESCE() 函數，全部的參數都是 NULL

-- COALESCE() 函數，使用三個參數
SELECT COALESCE(NULL, NULL, 'A') -- 回傳 A
GO

-- COALESCE() 函數，使用四個參數
SELECT COALESCE(NULL, 'A', NULL, 'B') -- 回傳 A 
GO

-- 06_COALESCE() 函數，使用多個參數

---

EX3. 使用 ISNULL() 函數，可能會有資料被截斷的問題

DECLARE @str1 varchar(5), @str2 varchar(100)
SET @str1 =NULL
SET @str2 = 'This is a book.'

SELECT @str1 N'變數@str1', @str2 N'變數@str2'

SELECT ISNULL(@str1, @str2) N'ISNULL() 函數', DATALENGTH(ISNULL(@str1, @str2))  N'位元組' -- 回傳：This
SELECT COALESCE(@str1, @str2) N'COALESCE() 函數', DATALENGTH(COALESCE(@str1, @str2)) N'位元組' -- 完整回傳：This is a book.
GO

-- 07_使用 ISNULL() 函數，可能會有資料被截斷的問題

---

EX4. 使用 COALESCE() 函數、 CASE  運算式以及 CASE  運算式 + ISNULL() 函數等版本

-- 建立範例資料表
USE tempdb
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[myInvoices]') AND type in (N'U'))
 DROP TABLE [dbo].[myInvoices]
GO
SELECT *
 INTO myInvoices
FROM (SELECT CustomerID, DATEPART(M,OrderDate) N'OrderMonth', SUM(UnitPrice) N'SubTotal'
 FROM Northwind.dbo.Invoices
 GROUP BY DATEPART(M,OrderDate),CustomerID) myInvoices
PIVOT (SUM(SubTotal)
  FOR OrderMonth IN ([1],[2],[3], [4], [5],[6],[7],[8],[9],[10],[11],[12]) ) pvt
GO

-- 查詢：資料表內容
SELECT * FROM myInvoices
GO

-- 08_查詢：資料表內容

需求：找出客戶第一次有下單月份的金額

-- 使用 COALESCE() 函數
SELECT CustomerID, 
 COALESCE([1], [2], [3]) N'第一次有下單月份的金額', 
 [1], [2], [3]
FROM myInvoices
GO

-- 09_使用 COALESCE() 函數，找出客戶第一次有下單月份的金額

-- CASE  運算式版本，找出客戶第一次有下單月份的金額
SELECT CustomerID, 
 CASE 
  WHEN [1] IS NOT NULL THEN [1]
  WHEN [2] IS NOT NULL THEN [2]
  WHEN [3] IS NOT NULL THEN [3]
  ELSE NULL
 END N'首月的績效', 
 [1], [2], [3]
FROM myInvoices
GO

-- CASE  運算式 + ISNULL() 函數版本，找出客戶第一次有下單月份的金額
SELECT CustomerID, 
 ISNULL([1], ISNULL([2], ISNULL([3],[3]))) N'首月的績效', 
 [1], [2], [3]
FROM myInvoices
GO

---

認識 COALESCE 函數

COALESCE 函數：傳回其引數中第一個非 Null 的運算式。

語法：
COALESCE ( expression [ ,...n ] )

引數
expression
這是任何類型的運算式。

傳回類型
傳回具有最高資料類型優先順序的 expression 資料類型。
如果所有運算式都不可為 Null，結果的類型也是不可為 Null。

如果所有引數都是 NULL，COALESCE 便會傳回 NULL。

ISNULL 和 COALESCE 雖然相當，但行為可能不同。
使用 ISNULL 和非 Null 參數的運算式會視為是非 NULL，而使用 COALESCE 和非 Null 參數的運算式則會視為是 NULL。

在 SQL Server 中，對於使用 COALESCE 和非 Null 參數的索引運算式而言，計算資料行可以使用 PERSISTED 資料行屬性保存。

---

參考資料

ISNULL (Transact-SQL)
http://msdn.microsoft.com/zh-tw/library/ms184325.aspx

COALESCE (Transact-SQL)
http://msdn.microsoft.com/zh-tw/library/ms190349(v=sql.110).aspx

Differences between ISNULL and COALESCE
http://blogs.msdn.com/b/sqltips/archive/2008/06/26/differences-between-isnull-and-coalesce.aspx

ISNULL() and COALESCE()
http://archive.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=ISNULL_COALESCE

How to use COALESCE and ISNULL?
http://www.sqlusa.com/bestpractices/coalesce/

Performance of COALESCE vs. IS NULL
http://sqlserverperformance.idera.com/tsql-optimization/performance-coalesce-null/

影片：SQL Server 2012 Active/Active模式-以AlwaysOn可用性群組為例(6)

簡介：
在本次課程中，將延續先前的主題，繼續討論在AlwaysOn可用性群組上，如何組態為：Active/Active模式，實踐讀寫分流、分散負載的作法，充分的使用到各台伺服器上的資源。
並討論應用程式端應如何組態等相關注意事項。

使用環境：
SQL Server 2012 RTM 11.0.2100.60

主題：
SQL Server 2012 Active/Active模式-以AlwaysOn可用性群組為例(6)

影片：SQL Server 2012 Active/Active模式-以AlwaysOn可用性群組為例(5)

簡介：
在本次課程中，將延續先前的主題，繼續討論在AlwaysOn可用性群組上，如何使用SSMS管理工具內建的容錯移轉可用性群組精靈，來執行手動容錯移轉或強制手動容錯移轉。
並討論如何使用Transact-SQL方式來監視AlwaysOn可用性的運行狀態。

使用環境：
SQL Server 2012 RTM 11.0.2100.60

主題：
SQL Server 2012 Active/Active模式-以AlwaysOn可用性群組為例(5)

影片：SQL Server 2012 Active/Active模式-以AlwaysOn可用性群組為例(4)

簡介：
在本次課程中，將繼續討論在AlwaysOn可用性群組上，如何使用AlwaysOn儀表板來檢視與監控AlwaysOn 可用性群組及其可用性複本和資料庫的運行健全狀況。

使用環境：
SQL Server 2012 RTM 11.0.2100.60

主題：
SQL Server 2012 Active/Active模式-以AlwaysOn可用性群組為例(4)

影片：SQL Server 2012 Active/Active模式-以AlwaysOn可用性群組為例(3)

簡介：
在本次課程中，將討論在AlwaysOn可用性群組上，如何建立「可用性群組接聽程式」，應用程式端將使用此所組態的虛擬網路名稱、接聽程式通訊埠、虛擬 IP等來資訊來連線。
並且討論如何將其他次要角色上的資料庫加入「可用性群組」內。

使用環境：
SQL Server 2012 RTM 11.0.2100.60

主題：
SQL Server 2012 Active/Active模式-以AlwaysOn可用性群組為例(3)

影片：SQL Server 2012 Active/Active模式-以AlwaysOn可用性群組為例(2)

簡介：
在本次課程中，將討論在AlwaysOn可用性群組上，安裝SQL Server 2012的注意事項，以及如何啟用AlwaysOn可用性群組，並建立「可用性群組」等作業。

使用環境：
SQL Server 2012 RTM 11.0.2100.60

主題：
SQL Server 2012 Active/Active模式-以AlwaysOn可用性群組為例(2)

影片：SQL Server 2012 Active/Active模式-以AlwaysOn可用性群組為例(1)

簡介：
本系列主題將討論 SQL Server 2012高可用性新增加的功能：AlwaysOn可用性群組。

此項技術將取代先前的資料庫鏡像，其結合了資料庫鏡像與Windows Server容錯移轉叢集(WSFC)的優點，不但提供五個可用性複本，讓你可以依據業務需求組態為非同步認可與同步認可模式，更支援Active/Active模式，讓寫入與讀取分流，分散負載，讓你所購買的各台伺服器，都能夠充分的被使用到。

---

相關資料

影片：SQL Server 2012 Active/Active模式-以AlwaysOn可用性群組為例(1)
http://sharedderrick.blogspot.tw/2012/06/sql-server-2012-activeactive-alwayson1.html

影片：SQL Server 2012 Active/Active模式-以AlwaysOn可用性群組為例(2)
http://sharedderrick.blogspot.tw/2012/06/sql-server-2012-activeactive-alwayson2.html

影片：SQL Server 2012 Active/Active模式-以AlwaysOn可用性群組為例(3)
http://sharedderrick.blogspot.tw/2012/06/sql-server-2012-activeactive-alwayson3.html

影片：SQL Server 2012 Active/Active模式-以AlwaysOn可用性群組為例(4)
http://sharedderrick.blogspot.tw/2012/06/sql-server-2012-activeactive-alwayson4.html

影片：SQL Server 2012 Active/Active模式-以AlwaysOn可用性群組為例(5)
http://sharedderrick.blogspot.tw/2012/06/sql-server-2012-activeactive-alwayson5.html

影片：SQL Server 2012 Active/Active模式-以AlwaysOn可用性群組為例(6)
http://sharedderrick.blogspot.tw/2012/06/sql-server-2012-activeactive-alwayson6.html

---

使用環境：
SQL Server 2012 RTM 11.0.2100.60

主題：
SQL Server 2012 Active/Active模式-以AlwaysOn可用性群組為例(1)

SQL Server：認識 ISNULL 函數

ISNULL 函數：以指定的取代值來取代 NULL。

語法：
ISNULL ( check_expression , replacement_value )

check_expression
這是要檢查 NULL 的運算式。check_expression 可以是任何類型。

replacement_value
這是 check_expression 是 NULL 時所傳回的運算式。replacement_value 必須是能夠隱含地轉換成 check_expresssion 類型的類型。


請參考以下的範例程式碼

---

EX1. 認識 ISNULL 函數

USE Northwind
GO
--
SELECT CustomerID, City, Region, Country
FROM Customers
GO

-- 使用 ISNULL 函數，用 N/A 字串來取代所有 NULL 項目
SELECT CustomerID, City, ISNULL(Region, 'N/A') Region, Country
FROM Customers
GO

-- 01_使用 ISNULL 函數，用 NA 字串來取代所有 NULL 項目

---

EX2. 彙總函數與 NULL

-- 建立資料表：tbAggs
USE tempdb
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tbAggs]') AND type in (N'U'))
 DROP TABLE [dbo].[tbAggs]
GO
CREATE TABLE tbAggs
(rid int, sales int)
GO
INSERT tbAggs VALUES(1,10),(2,20)
INSERT tbAggs(rid) VALUES(3)
GO
-- 查詢資料表內容
SELECT * FROM tbAggs
GO

-- 02_查詢資料表內容

-- 使用 SUM 函數、AVG 函數
SELECT SUM(sales) N'總和', AVG(sales) N'平均值'
FROM tbAggs
GO

-- 03_使用 SUM 函數、AVG 函數

-- 使用 ISNULL 函數，用 0 來取代所有 NULL 項目
SELECT rid, ISNULL(sales, 0) 'sales'
FROM tbAggs
GO

-- 04_使用 ISNULL 函數，用 0 來取代所有 NULL 項目

-- 加入 AVG 函數，搭配使用 ISNULL 函數
SELECT AVG(ISNULL(sales, 0)) N'平均值'
FROM tbAggs
GO

-- 05_加入 AVG 函數，搭配使用 ISNULL 函數

---

參考資料

ISNULL (Transact-SQL)
http://msdn.microsoft.com/zh-tw/library/ms184325.aspx

COALESCE (Transact-SQL)
http://msdn.microsoft.com/zh-tw/library/ms190349(v=sql.110).aspx