01 Transact-SQL() 是與 SQL Server 溝通的核心 凡存取 SQL Server 執行個體的所有應用程式, 不論其使用者介面為何 ( 例如, 我們一般透過.NET VB ASP 寫的應用程式 SQL Server 自己的管理介面 SQL Server Management S

Transcription

1 4

2 01 Transact-SQL() 是與 SQL Server 溝通的核心 凡存取 SQL Server 執行個體的所有應用程式, 不論其使用者介面為何 ( 例如, 我們一般透過.NET VB ASP 寫的應用程式 SQL Server 自己的管理介面 SQL Server Management Studio sqlcmd 等 ), 都是藉由傳遞 陳述式給伺服器, 以要求 SQL Server 完成某項任務 本章中, 我們將介紹組成 語言的基本元素, 在之後的章節中, 再針對查詢 新增 修改 刪除等動作, 各別介紹不同型態的 語法 元素包含了註解 運算子 資料類型 等, 這些是使用 的基礎 接下來, 分別說明各元素所提供的內容 註解 (comment) 是 陳述式中不會執行的字串, 可用於說明, 或是暫時取消部分程式碼執行 使用註解說明, 可使後續的維護工作較為容易 註解經常用來記錄程式技巧 功能 參數 相關物件 原作者 修改者 審閱者姓名, 以及發佈 修改日期 版本等, 並用來描述複雜的計算或撰寫程式的邏輯 SQL Server 支援兩種類型的註解字元, 詳細說明如下 : -- 雙連字號 註解字元可與程式碼放在同一行執行, 或是自己占據一行 從 雙連字號 開始到該行結尾之間, 全部都是註解的一部分 對於多行註解而言, 雙連字號 必須出現在每一行註解的前面, 如範例程式碼 4-1 所示 : -- Northwind USE Northwind GO -- dbo.customers 116

3 SQL Server 2008 ORDER BY Country DESC -- /*... */ 斜線- 星號字元配對 註解字元可與程式碼放在同一行來執行, 也可以占據全行, 甚至參雜在可執行的程式碼中 從開始註解配對 (/*) 到結束註解配對 (*/) 之間, 全部都會視為註解的一部分 對於多行註解而言, 自 /* 起始直到 */ 結束間的多行文字全為註解 它與 C 系列語言註解標示的規則相同 範例程式碼 4-2 利用註解來說明程式碼區段應該執行的動作 /* */ SELECT ProductID,ProductName,UnitPrice,UnitsInStock FROM dbo.products 4-2 /**/ 多行註解必須包含在批次之中, 不得跨越批次 例如, 在 SSMS 程式碼編輯器 與 sqlcmd 公用程式中,GO 命令代表批次的結尾 若直接反白標示以下語法的上半段 : USE Northwind GO SELECT * FROM dbo.customers /* Employee GO GO */ SELECT * FROM dbo.products GO Country,CustomerID,CompanyName,ContactName FROM dbo.customers SELECT 4-3 /**/ 讓 SSMS 僅傳遞前半的 /* 註解符號, 則會有如下的錯誤訊息 : '*/' 117

4 01 若範例程式碼 4-3 上下兩段一起執行, 讓 GO 出現在 /* 與 */ 分隔符號之間, 則會 將 GO 當作註解的一部分, 而不會導致語法錯誤 資料庫物件名稱又稱為 識別碼 (identifiers) SQL Server 中每個物件都具有 04 識別碼, 例如, 伺服器 資料庫與資料庫物件 ( 如資料表 資料行 索引 條件約束 規則 觸發程序 檢視 預存程序 函數 等 ) 都有 識別碼 大 多數物件需要 識別碼, 但對部分物件如條件約束, 則是選擇性的需求, 若在 定義條件約束時未指定 識別碼, 系統會自動為你產生 定義物件時會建立物件 識別碼, 之後就可以使用 識別碼 來參考物件 例如, 以下陳述式會建立具有 識別碼 tba 的資料表, 以及具有 識別碼 KeyCol 與 Description 的兩個資料行 : CREATE TABLE tba( KeyCol INT PRIMARY KEY, Description nvarchar(80) ) 定義上述資料表時, 還有一個未命名的條件約束 PRIMARY KEY 若以 sp_help tba 檢視與 tba 相關的定義時, 可以看到 SQL Server 自動隨機賦予了 PK tba D7CB9CCC0A9D95DB 一類的名稱, 當然, 你看到的名稱應該與我的不同 識別碼 可以分為兩類, 以下兩小節會依序說明 符合規則的 識別碼 稱為 一般識別碼, 在 陳述式中可以省略符號 分隔 一般識別碼 的格式規則視資料庫相容性層級而定 這個層級可使用 sp_dbcmptlevel 系統預存程序或 SQL Server Management Studio 來設定 如果相容性層級是 90 (SQL Server 20 版本,2008 依然可行 ), 則適用以下規則 : 識別碼 的定序由其定義階層決定 SQL Server 執行個體階層物件的 識別碼 ( 如登入與資料庫名稱 ), 會被指派執行個體的預設定序 資料庫內物件的 識別碼 ( 如資料表 檢視與資料行名稱 ) 會被指派資料庫的預設定序 例如, 可區分大小寫的定序, 在資料庫中建立名稱相同且大小寫不同的兩個資料表, 但不可在不區分大小寫定序的資料庫中建立這兩個資料表名稱 118

5 SQL Server 2008 Unicode Standard 3.2 中所定義的字母 Unicode 的字母定義包括從 a 到 z 以 及從 A 到 Z 的拉丁字元, 還有其他語系中的字母字元 符號 # 符號或底線 ( _ ) 識別碼 開頭的某些符號在 SQL Server 中具有特殊意義 例如 : 符號開頭的一般 識別碼 代表本機變數或參數, 不能做為任何其他類型之物件的名稱 部分 符號為開頭 為了避免與這些功能混淆, 為開頭的名稱 開頭為 # 符號的 識別碼 代表暫存資料表或預存程序 開頭為兩個 ## 符號的 識別碼 代表全域暫存物件 雖然 # 符號或兩個 ## 符號字元可做為其他類型之物件的名稱開頭, 但是不建議此用法 可包含的後續字元如下 : Unicode Standard 3.2 中所定義的字母 其他 Basic Latin 或國家 ( 地區 ) $ # 等符號或底線 ( _ ) 識別碼 不可以是大 小寫的 保留字 不允許內嵌的空格或特殊字元 SQL Server 的變數名稱和函數 預存程序參數, 必須符合一般 識別碼 規則 在 陳述式中使用 識別碼 時, 如果 識別碼 與上述規則不符, 則必須使用雙引號 ( "" ) 或方括號 ( [ ] ) 加以分隔 以下為 分隔識別碼 的格式規則 : 第一個字元必須符合以下任一項 : 2 你可以在連接裡執行 CREATE TABLE #t 或 CREATE PROC #p 等語法建立物件, 它們實際都建立在 tempdb 系統資料庫內, 而非連接所在的資料庫 這些物件僅在該連接內可以使用, 同一時間其它連接看不到這些物件 ; 一旦連接結束後, 系統會刪掉這些暫存物件 119

6 01 04 分隔識別碼 可包含的字元數與一般 識別碼 相同 不包括分隔符號字 元在內, 可有 1 到 128 個字元 本機暫存資料表的 識別碼 最多為 116 個 字元 識別碼 本身可以包含目前字碼頁內任何字元的組合, 但分隔符號除外 例如, 分隔識別碼 中可以包含空白, 以及對一般 識別碼 有效的任何字元 範例如下所示 : SELECT * FROM [My Table] WHERE [order] = 符合規則的 識別碼, 不一定要使用分隔符號 不符合一般 識別碼 格式則要分隔 另外, 當連接的 QUOTED_IDENTIFIER 選項設為 ON 時, 雙引號必須括起 識別碼, 而非字串 程式碼如下所示 : SET QUOTED_IDENTIFIER ON SELECT * FROM "My Table" WHERE "order" = 10 依預設,Microsoft OLE DB Provider for SQL Server SQL Server ODBC 及.NET SqlClient 等驅動程式會在連接時, 將 QUOTED_IDENTIFIER 設為 ON 3 這時, 在 陳述式中使用雙引號 ( " ) 與單引號 ( ' ) 會遵循 SQL-92 規則 例如 : 雙 4 而不論 QUOTED_IDENTIFIER 的設定是什麼, 都可以使用方括號當分隔識別碼 如果字串內嵌單引號, 則應以兩個單引號代表一個單引號, 程式碼如下所示 : SELECT * FROM "My Produect Table" WHERE "Product Name" = 'A''Box' 3 你可以透過 SQL Profiler 公用程式錄製 SQL Server 所觸發的 Audit Login 事件, 當程式碼中的 Connection 物件執行 Open 方法時, 在伺服器端會收到 set quoted_identifier on 等語法 4 不過, 為維持與現有應用程式的相容性,SQL Server 沒有完全強制執行這項規則 如果字元字串未超過識別碼的長度, 就可以將字串括在雙引號中 但我們不建議採用此方式 120

7 SQL Server 2008 為 : 例如 : SET QUOTED_IDENTIFIER OFF SELECT * FROM [My Table] WHERE [Last Name] = "O'Brien" 保留關鍵字不應作為物件名稱使用 但從舊版 SQL Server 升級的資料庫, 識別 碼 可能會包含在先前版本中不是保留字, 但卻是 SQL Server 2008 的保留字 在 物件名稱變更之前, 可以先使用 分隔識別碼 來參考物件 SQL Server 可以在分隔識別碼中使用目前字碼頁的任何字元 不過, 於物件名稱任意使用特殊字元, 會使 SQL 陳述式和指令碼難以讀懂和維護 例如, 當建立名為 MyProduct] 的資料表時, 其中的右方括號是名稱的一部分 若要這麼做, 則必須多加上兩個方括號, 使右方括號逸出, 程式碼如下所示 : CREATE TABLE [MyProduct]]] ( ProductID int IDENTITY (1,1) NOT NULL, ProductName varchar(30), Price int ) 4-4 另外, 如果物件在命名尾端包含空白, 存取時可以不包含名稱尾端的空白, 當 然, 儘量避免這些混淆 SQL Server 保留一些關鍵字, 用來定義 操作和存取資料庫 例如, 在 sqlcmd 或 SQL Server 程式碼編輯器工作階段中, 使用 BACKUP 關鍵字即告訴 SQL Server 要備份資料庫或記錄檔 當 QUOTED_IDENTIFIER 設為 OFF 時,SQL Server 解釋單引號及雙引號的規則 121

8 01 04 自訂的物件名稱最好不要使用保留關鍵字 如果取相同名稱, 就必須以 分隔 識別碼 來參考物件 雖然這種方法允許物件名稱為保留字, 但建議不要使 用 如果物件名稱必須像保留關鍵字可以移除子音或母音 例如, 將程序命名為 bckup, 來為使用者自訂資料庫執行 BACKUP 陳述式 有關於保留關鍵字的詳細 列表, 請參考 Microsoft SQL Server 2008 線上叢書 的說明 運算子是一個符號, 負責一或多個運算式所執行的動作 運算子包含算術 比 較 串連 指派 等等 例如, 透過 比較運算子 測試資料, 檢查客戶的國家 / 地區資料行是否 NULL, 而 算術運算子 則可用於數學運算 列舉運算子可執 行的作業如下 : 變更資料 搜尋符合指定條件的資料列或資料行 在開始或認可交易, 或是執行特定的程式碼之前, 測試特定條件 當簡單的運算式合併為複雜的運算式時, 結果的資料類型是由運算子資料類型優 先順序的規則來決定 如果結果是字元或 Unicode 值, 那麼結果的定序則是由運 算子定序優先順序來決定 而決定結果的有效位數 小數位數及長度的規則, 則 是根據運算式的有效位數 小數位數, 以及長度來決定 算術運算子 會針對數值資料類型中, 一或兩種資料類型的兩個運算式執 行數學運算 提供的 算術運算子 包含如下 : 加 (+) 減 (-) 乘 (*) 除 (/) 取得除法的餘數 (%) 加號 (+) 和減號 (-) 運算子也可以對 datetime 和 smalldatetime 執行算術運算 例如 : datetime=' ' 會回傳 : :00:

9 SQL Server 2008 型, 則不可如此與整數類型混合運算, 若上述範例改成如下寫法 : date=' ' 將會傳回錯誤訊息 : : date int 比較運算子 用來測試兩個運算式是否相同 除了 text ntext 或 image 資料類 型的運算式, 所有運算式都可以使用 比較運算子 提供的 比較運算 子 包含 : (=) (>) (<) (>=) (<=) (<>) (!=) (!<) (!>) 邏輯運算子 會測試是否符合某些狀況, 如同 比較運算子, 邏輯運算子會傳回含 TRUE FALSE 或 UNKNOWN 值的 Boolean 資料類型 表 4-1 是 較常見的邏輯運算子, 大多用於 WHERE 子句中 AND OR TRUE TRUE TRUE TRUE 但 SQL Server 2008 所新增的 Date Time DateTime2 和 DateTimeOffset 等資料類 NOT BETWEEN EXISTS IN LIKE TRUE TRUE TRUE TRUE

10 01 04 等號 (=) 是 唯一的 指派運算子 下列範例中, 透過 指派運算子 將常數 1 INT = 1 你也可以利用 指派運算子 來建立資料行標題, 和定義資料行值與運算式之間的關聯性 下列範例會顯示資料行標題 FirstColumnHeading 和 SecondColumnHeading FirstColumnHeading 標題的資料行內, 所有資料列都會顯示 xyz 字串 之後,Products 資料表中的每個產品識別碼都會列在標題為 SecondColumnHeading 的資料行中 : SELECT FirstColumnHeading = 'xyz', SecondColumnHeading = ProductID FROM dbo.products 4-5 加號 (+) 除了是算數運算子, 也是串連字串的 字串串連運算子 其他字串操作則是利用 SUBSTRING 之類 ; 系統提供的字串函數來處理 在串連 varchar char 或 text 等資料類型的資料時, 會將空字串解譯成空字串 例如,'abc' + '' + 'def' 會儲存成 'abcdef' 5 位元運算子 會在整數資料類型之類別下 (bigint 6 int smallint tinyint); 任何資料類型的兩個運算式之間, 執行位元操作 表 4-2 列出 的 位元運算子 : 5 以往 SQL Server 6.5 時, 會將空字串當作單一空白字元來處理, 將 'abc' + '' + 'def' 儲存成 'abc def' 6 SQL Server 20 的線上說明似乎漏掉了 bigint 資料類型, 就筆者測試 bigint 也可以執行位元運算 124

11 SQL Server 2008 & ( AND) AND ( ) ( OR) OR ( ) ^ ( OR) 4-2 OR ( ) 下列範例分別對 7 和 5 兩個數字各位元作 AND OR XOR 的運算 : select 7 & 5 [111 & 101],7 5 [ ],7 ^ 5 [111 ^ 101] 其執行結果如下 : 111 & ^ 一元運算子 只用在數值資料類型的單一運算式上 表 4-3 為 的 一元運算子 : + ( ) - ( ) ~ ( NOT) bit 下列範例會將變數設為正值 decimal(10,2); = ; 要特別注意的是 ^ 常被人誤認為次方運算子, 但 需要用 Power 函數, 例如,2 的 3 次方要寫 作 Power(2,3), 而非 2^3 2^3 是二進位的 10 和 11 做 XOR, 變成 01 了 125

12 01 04 雖然加號可以出現在任何數值運算式之前, 但對運算式傳回的值並不做任何處 理 明確地說, 如果運算式是負的, 經過加號運算也不會傳回正值 如果負運算 式要傳回正值, 應使用 ABS 函數 以範例程式碼 4-6 為例, 使用 ABS 函數的運 算式才會傳回正值 USE tempdb; GO int; = -5; SELECT +@Num1, ABS(@Num1); GO 4-6 ABS 4-2 資料類型用來定義資料種類 ( 例如, 字元 整數或日期.. 等等 ), 告知電腦如何解釋連串的 0 與 1 善用資料庫的第一步就是善用資料類型, 對各類型的特徵 值域 精確度 所占空間的位元組數 數值類型小數點的進位或捨去 資料類型的隱含與明顯轉換 可用的函數 等等, 都了然於胸才能正確有效地使用資料 為了精確有效率地使用資源, 同時提供彈性與廣泛應用,SQL Server 每個版本都會新增一些資料類型, 以強化其功能 在接下來的小節中, 將為你整理 SQL Server 2008 版本, 可用的各種資料類型 SQL Server 2008 SQL Server 提供一組 資料類型, 以定義資料儲存的種類, 或變數型態, 例如 : 整數 浮點數 貨幣 日期和時間 字元 二進位 位元 等 瞭解它們才能適當地定義資料表的資料行以及變數 以下是具有資料類型的應用 : 126

13 SQL Server 2008 預存程序中的輸出 / 入參數 含有傳回碼的預存程序, 其資料類型一定是整數 (integer) 傳回一或多個特定資料類型的 函數與參數 變數 運算式的結果 資料類型 提供下列四種屬性用於定義物件: 資料種類 儲存值的長度或大小 數字的精確度 ( 只限數值資料類型 ) 數字的小數位數 ( 數值與時間資料類型 ) 提供的系統資料類型如表 4-4 所示 : date** datetime datetime2** datetimeoffset** smalldatetime Time** char nchar nvarchar varchar bigint decimal float int money numeric real smallint binary varbinary CLR* image^ ntext^ nvarchar (max)* text^ varbinary (max)* bit CLR UDT* cursor geography** geometry** hierarchyid** rowversion (timestamp) varchar smallmoney sql_variant (max)* tinyint table Xml* uniqueidentifier Xml* ** 資料表與檢視中的資料行

14 DD hh:mm:ss 表 4-4 中, 各符號所代表的意義如下 : *: 代表該資料類型在 SQL Server 20 版本後才提供 **: 代表該資料類型在 SQL Server 2008 版本後才提供 ^: 建議不再使用的資料類型 表 4-4 中, 有類型同時列在兩欄, 因為它們同時兼具兩種屬性 你也可以利用 或.NET Framework, 定義自己的 資料類型 提 供的 別名 是以系統的 資料類型 為基礎, 給特定格式一個別名, 例如, 稱 CHAR(10) 為 身分證字號 " SQL Server 20 版後新增 使用者自訂類型, 讓你利用.NET Framework 支援的程式語言建立類別, 透過其內的方法和運算子 定義它們的性質, 以延伸 SQL Server 既有的資料類型 以下將個別介紹不同資料類型的特徵, 我們就從 SQL Server 2008 增加最多的日期 和時間資料類型開始談起 SQL Server 2008 支援的日期和時間資料類型如表 4-5 所示 : Date* YYYY-MM-DD Datetime Y Y Y Y- M M - 31 [.nnn] 04 Smalldatetime YYYY-MM-DD hh:mm:ss Datetime2* (10-7 ) Y Y Y Y- M M - DD hh:mm:ss [.nnnnnnn] 4-5 SQL Server

15 SQL Server 2008 Datetimeoffset* Y Y Y Y- M M DD hh:mm:ss [. n n n n n n n ] [+ -]hh:mm Time* h h : m m : s s [.nnnnnnn] 4-5 SQL Server 2008 * SQL 2008 表 4-5 中, 所有類型都支援關係運算子 (< <= > >= 和 <>) 比較運算子 (= < <= > >= <>!< 和!>) 和邏輯運算子 (IS NULL IS NOT NULL IN BETWEEN EXISTS NOT EXISTS 和 LIKE) SQL Server 同時針對日期和時間資料類型, 提供一組專屬的函數, 例如, 以 DATEADD 和 DATEDIFF 加減日期, 以 GETDATE GETUTCDATE 來取回系統時間 等 而 SQL Server 也為了各種資料類型準備不同的系統函數, 你可以直接在 SQL Server Management Studio 的 物件總管 視窗內, 展開任一個資料庫後, 繼續展開其內的 可程式性 函數 系統函數 節點, 就可以看到各函數粗略的使用方式, 並透過線上叢書查詢詳細的說明, 其畫面如圖 4-1 所示 : SQL Server Management Studio 129

16 01 04 在正式討論日期資料類型前, 先看一下與曆法 / 日期相關的歷史沿革 : SQL Server DateTime Julian Gregorian ( 46 (Julius Caesar) Sosigenes Julian ( ) (Augustus Caesar) August 31 (Julius Caesar) (July) 16 Gregory Aloysius Lilius Christopher Clavius Gregorian Gregorian ( ) Gregorian 10 Julian 128 SQL Server Sybase Gregorian

17 SQL Server 2008 跟資料庫引擎溝通, 而在撰寫 時, 是以字串形式表示日期, 並讓資料庫引 擎自行判讀字串中哪一段代表年 月 日, 將會造成一些混淆, 例如 : 01// 這到底是哪年哪月哪日? 根據連接上不同的 Language 或 Dateformat 設定, 會影響 SQL Server 對字串轉日期的解讀, 其範例程式碼如下 : date,@d2 date -- Session Langauge Dateformat set language ' ' from sys.dm_exec_sessions where session_ id=@@spid set language 'English' from sys.dm_exec_sessions where session_ id=@@spid -- Session Dateformat Langauge set dateformat 'dmy' from sys.dm_exec_sessions where session_ id=@@spid 在輸入日期類型時, 首先需要注意的是表示方法, 因為我們大多利用 語法 4-7 其執行結果如圖 4-2 所示 : 4-2 Session Language Dateformat 131

18 01 04 從以上測試可以發現, 設定 language 會影響 dateformat,dateformat 則影響對有格 式的日期字串之解讀 需要強調的是 : 最佳的表現方式是不寫日期格式, 而是僅 以年月日的數字呈現 ( 在此列出了最長的日期時間表示 ): YYYYMMDD HH:MI:SS[.mmmmmmm][+ -]HH:MI 另外,Datetime 資料類型最小單位是 0.0 秒, 當資料不是正好等於該值時, Datetime 是採用最接近的值, 而三位小數可以出現的值之模式為 [0-9][0-9][7], 也就是最後一位數僅能是 0 3 或 7 而何謂最接近值呢? 其範例如下 : int=1,@s nvarchar(30)='' table(s nvarchar(30),d datetime) begin :59:59.99' + CONVERT(char(1),@i) values(@s,@s) end select * 執行結果如圖 4-3 所示 : 4-8 Datetime 4-3 DateTime 0.0 而這個誤差最可能造成的問題是 : 若要設小於某一天, 例如, 要 這一 天的紀錄, 若寫成如下的查詢語法 : where date between :00: and :59: 則因為上述的原因, 會包含 :00: 的資料 換句話說, 比較時, 用小於 (<) 會比小於等於 (<=) 正確 132

19 SQL Server 2008 Date: 僅儲存日期的資料類型 Time: 僅儲存時間的資料類型 datetime2: 與 datetime 相似, 但儲存的日期與時間範圍較大, 且精確度更高 datetimeoffset: 允許儲存相對於格林威治標準時間的時區資料 相較於既有的 DateTime SmallDatetime 資料類型, 現在可以將含有日期與 時間元素的資料內容分別存放於不同的資料類型欄位中, 當使用過濾條件 between...and... 取日期間隔時, 就變得方便許多 參照表 4-5, 儲存日期元素 時, 改用新的資料類型 DATE 佔用的空間, 最大可由 DateTime 資料類型 的 8 byte 減少到 3 byte 資料表的筆數成長到一定數量時, 除了可節省為數可觀 的硬碟空間外, 執行時也能降低記憶體需求, 建立索引 比對資料都較有效率 新的資料類型擴大儲存日期的範圍, 並將精確度由原先的微秒增加到 100 奈秒 (10 的負九次方秒 ) 而 除了舊有的時間函數仍可繼續延用於上述新的資料 類型,SQL Server 2008 也提供新的時間函數, 以取得更精確的時間 而帶有時間 的新資料類型, 如 :time datetime2 datetimeoffset 等型態的資料長度可變動, 能在宣告時, 指定秒數以下小數點的精確位數, 讓設計師可在精確度與節省空間 兩者間求取平衡 Server 2008 為了加強日期與時間類型的資料處理, 新增下列四種資料類型 : SQL Date Time 若要儲存員工的生日 到職日期 訂單日期 出貨日期... 等, 這類的資料只須存放日期部分即可, 但 SQL Server 2008 之前的版本中, 沒有可明確儲存日期的資料類型 要存放日期的資料, 就只能選用 DATETIME 或 SMALLDATETIME 資料類型 當輸入日期資料或是透過 GetDate() 函數, 取得當下的日期存入該欄位時, 時間會一併存入, 例如,12:00: AM 這時就需要透過格式化來呈現日期部分, 此外, 在查詢時也要格外小心時間所帶來的潛在錯誤 133

20 01 04 下列範例使用兩種不同的查詢語法, 會回傳不同的結果值 為 ' ' 時, 沒有指定時間內容, 因此, 時, 會預設成 ' :00:00.000' 進行比對 Use AdventureWorks GO -- HumanResources.JobCandidate 2004/01/23 -- datetime select * from HumanResources.JobCandidate where ModifiedDate=@date /01/24 select * from HumanResources.JobCandidate where ModifiedDate and dateadd(d,1,@date) 你可以利用 SQL Server 2008 提供的 DATE 資料類型, 指定僅儲存日期資料於欄 位中, 以避免過濾資料時, 因疏忽時間而導致錯誤 如下列範例所示, 可以感受 新資料類型帶來的便利 as DATE = getdate() 回傳值如下 : 另外, 時間被分為 24 小時, 每小時 60 分, 分又被分為 60 秒 在有些語系中, 像是波蘭語和阿拉伯語, 秒也以 60 進位制被再細分, 但現今大都是以十進位法來細分小數點以下的時間 例如 : 1 秒 = 1000 毫秒 (millisecond) 1 毫秒 = 1000 微秒 (microsecond) 1 微秒 = 1000 奈秒 (nanoseconds) 以往,SQL Server 處理時間的最小單位為毫秒 (Millisecond, 千分之一秒 ) 8, 這是軟 硬碟或光碟機在儲存資料時常用的計算單位 8 雖然也可以擴增時間, 例如,ks ( 千秒 ) Ms ( 百萬秒 ) 和 Gs ( 十億秒 ), 但實際上很少這樣使用, 大家都還是習慣用 60 進位的分 時, 以及 24 進位的日做為秒的擴充 134

21 SQL Server 2008 Datetime2 Datetimeoffset 資料類型 下列程式碼在宣告變數時, 直接賦與該資料 類型的精確值 若沒有宣告數值, 該資料類型會以最大的精確度當作預設值 5 Time(5)=GETDATE() -- GETDATE 7 datetime2(7)= SYSDATETIME () 上述範例中若延用舊的 GetDate() 函數, 將無法取得所要的精確度, 因此, 筆者 以 SQL Server 2008 新增的 SysDateTime 函數來做個比對, 它可以提供足夠的精確 度 下列範例資料表是以錄影帶出租店為例, 在資料表 Product 中記錄該店的商品 進貨日期 各錄影帶的時間長度與相關資料 首先, 示範以往沒有新的時間型態時, 使用者較常選擇的設計方式 資料表的進貨日期 StockDate 欄位使用 Datetime 資料類型, 錄影帶撥放時間 playlength 欄位使用 float 資料類型, 如範例程式碼 4-9 所示 : USE tempdb IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]. [Product]') AND type in (N'U')) DROP TABLE [dbo].[product] 而在 SQL Server 2008 版本中, 將時間的精確度延伸至奈秒, 例如,Time -- STEP 2: CREATE TABLE dbo.product( ProductID int primary key, FilmName nvarchar(30), direct nvarchar(10), Rent money NULL, Discount real NULL, StockDate datetime Null, PlayLength float NULL ) GO insert into Product values(1,' ( )',' ',70,0.2,'2004//30','6000') insert into Product values(3,' ',' ',70,0.2,'2004//30 15:30','6000') insert into Product values(4,' ',' ',60,0.2,'2004//30 16:15','4380') insert into Product values(5,' ',' ',60,0.2,'2004//30 16:20','4500') insert into Product values(2,' ',' ',80,0.2,'20/01/10','7140') select * from Product

22 01 在儲存錄影帶播放時間的 PlayLength 欄位中, 以秒為單位紀錄, 範例程式碼 4-9 執行後的結果如圖 4-4 所示 : 4-4 float 接下來, 改用新的資料類型, 記錄先前的資料內容, 如範例程式碼 4-10 所示 : Use tempdb IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]. [ProductNew]') AND type in (N'U')) DROP TABLE [dbo].[productnew] -- STEP 2: CREATE TABLE dbo.productnew( ProductID int primary key, FilmName nvarchar(30), direct nvarchar(10), rent money NULL, Discount real NULL, StockDate date Null, PlayLength time NULL ) GO insert into ProductNew values (1,N' ( )',N' ',70,0.2,'2004//30','01:40'), (3,N' ',N' ',70,0.2,'2004//30 15:30','01:40'), (4,N' ',N' ',60,0.2,'2004//30 16:15','01:13'), (5,N' ',N' ',60,0.2,'2004//30 16:20','01:15'), (2,N' ',N' ',80,0.2,'20/01/10','01:59') SELECT * FROM ProductNew 透過新的 Time 資料類型, 在儲存錄影帶播放時間的 PlayLength 欄位中, 可以更直觀地儲存時間長度 136

23 SQL Server 透過下列語法, 分別對新舊兩個資料表查詢錄影帶撥放長度大於 1.5 小時的資 料 由於 Product 資料表的 PlayLength 欄位是以秒數儲存, 所以, 過濾時需要 以 90 分 *60 秒取得總秒數後才能比較 而 ProductNew 資料表就方便多了, 由於 PlayLength 欄位的資料類型為 Time, 因此, 可以使用更直觀的方式過濾 : SELECT ProductID, FilmName, PlayLength FROM Product WHERE PlayLength>= 90 * 60 SELECT ProductID, FilmName, PlayLength FROM ProductNew WHERE PlayLength >= '01:30:00' time DateTimeOffset 以往儲存日期與時間時, 沒有提供屬於哪個時區的表示法 現在處理來自於不同時區的資料時, 可利用 SQL Server 2008 提供的 DateTimeOffset 資料類型儲存當地時間與該時區的資料 例如, 日本 台灣 美國三地相對於格林威志標準時間差如下 : 日本格林威治標準時間 (G.M.T.)+9 小時 台灣格林威治標準時間 (G.M.T.)+8 小時 美國太平洋沿岸, 如西雅圖 洛杉磯等城市, 時區為格林威治標準時間 (G.M.T.)-8 小時 137

24 01 04 因此, 假設 2008 年 5 月 1 日, 各地各有一筆資料於當地晚間 8 點存入資料 庫 這時, 各資料內容應分別記錄為 2008//01 20:45 +9: //01 20:45 +8: //01 20:45-8:00 利用 DATEDIFF 函數, 可以計算台灣與美國兩地區的時差, 程式碼如下所示 : -- DateDiff DATETIMEOFFSET(0) = ' :45:00 +8:00' DATETIMEOFFSET(0) = ' :45:00-8:00' SELECT DATEDIFF(hh,@dt,@Dt1) 其結果是 16 個小時 透過以下的方式可以取得包含時區資訊的完整日期時間 : DATETIMEOFFSET sysdatetimeoffset() 其結果如下 : 4/30/2008 4:23:19 PM +08: 與時間相關的函數中, 除了較常用的 GetDate Year Month Day 等系統函數外, 還有 CURRENT_TIMESTAMP DATEADD DATEDIFF DATENAME DATEPART GETUTCDATE 等 其用途與使用方式如表 4-6 所示 : CURRENT_TIMESTAMP DATEADD DATEDIFF GetDate datetime DATEADD (datepart,number,date ) DATEDIFF ( datepart, startdate, enddate ) 4-6 SQL Server

25 SQL Server 2008 DATEPART DATEPART ( datepart, date ) DATENAME DATAPART DATEPART ( datepart, date ) GETUTCDATE 4-6 SQL Server 20 利用這些函數, 可支援大部分的時間運算 另外,DATEPART DATENAME 系統函數的 DatePart 部分新增了四個選項 :microsecond nanosecond Tzoffset ISO_WEEK 前三個選項應該都可望文生義, 第四個 Iso_Week 代表 ISO 8601 定義, 規範兩年間交界的星期, 依星期四所在, 決定該星期屬於前 / 後哪一年 以 2004 年第一個星期為例,20/12/31 是星期三,2004/1/1 是星期四, 所以, 該星期屬於 2004 年 這會決定一年內有 52 或 53 個星期, 而這個標準通常執行於歐洲 以下以簡單的範例說明 DateName 函數搭配 DatePart 的各種設定, 將回傳不同的結果 : SELECT DATENAME(datepart,' :15: :10') 上述語法中,DatePart 分別設定不同的內容, 其回傳值如表 4-7 所示 : datepart year, yyyy, yy 20 quarter, qq, q 4 month, mm, m dayofyear, dy, y 3 day, dd, d 30 week, wk, ww SQL Server 2008 DateName DatePart 139

26 01 DATETIMEOFFSET datepart iso_week, isowk, isoww 44 weekday, dw hour, hh 12 minute, n 15 second, ss, s 32 millisecond, ms 123 microsecond, mcs nanosecond, ns TZoffset, tz SQL Server 2008 DateName DatePart 除上述函數外,SQL Server 2008 裡, 新增了 5 個與日期時間相關的系統函 數.. SYSDATETIME SYSDATETIMEOFFSET SYSUTCDATETIME SWITCHOFFSET 和 TODATETIMEOFFSET 分別說明如表 4-8: SYSDATETIME GETDATE SYSDATETIME 100 YYYY-MM-DD hh:mm:ss [.nnnnnnn] DATETIME2 SYSDATETIMEOFFSET SYSDATETIME YYYY- MM-DD hh:mm:ss[.nnnnnnn] [+ -]hh:mm 04 SYSUTCDATETIME 100 UCT(Universal Coordinated Time) SQL Server Y Y Y Y- M M - D D h h : m m : s s [. n n n n n n n ] DATETIME2 4-8 SQL Server

27 SQL Server 2008 SWITCHOFFSET TODATETIMEOFFSET 簡單的測試範例如下 : print SYSDATETIME() print SYSDATETIMEOFFSET() print SYSUTCDATETIME() 執行結果如下 : SWITCHOFFSET ( DATETIMEOFFSET, time_zone ) 4-8 SQL Server :25: :25: :00 ( ) :25: ( ) SWITCHOFFSET 函數轉換時間到對應的時區, 並回傳 DATETIMEOFFSET 類型 的資料 範例程式碼如下所示 : PRINT SYSDATETIMEOFFSET() PRINT SWITCHOFFSET (SYSDATETIMEOFFSET(), '-08:00') 回傳值 : :41: : :41: :00 上述程式碼中, 第一個回傳值取得目前的日期與當下的時區時間 以美國西雅圖為例, 其標準時區為 -08 因此, 以筆者所執行的伺服器為準, 作業系統的時區選擇為台灣, 所以, 回傳 :41: :00 而第二個回傳值為西雅圖當地時間 : :41: : 其實, 西雅圖當地時間還需要考慮美國政府所頒佈的日光節約時間, 不一定僅是考慮時差而已 141

28 01 04 TODATETIMEOFFSET 函數, 則可將本地日期時間值搭配指定的時區時差, 程 式碼如下所示 : print SYSDATETIME() print TODATETIMEOFFSET(SYSDATETIME(),'-08:00') 回傳值如下 : :08: :08: :00 對於上述 SQL Server 2008 新增的日期資料類型, 相信你已能充分掌握了 在此要 提醒你, 萬一需要將 DATE 與 TIME 資料類型之欄位內容相加時, 若沒有 經過轉換處理, 將會出現下列錯誤訊息 : Operand data type date is invalid for add operator. 運算上述兩個資料類型的內容時, 正確作法需要分別將其轉換為 SMALLDATETIME 資料類型才能運算 程式碼如下所示 : as DATE = getdate() as TIME = getdate() PRINT cast(@dt as smalldatetime) + cast(@dtt as smalldatetime) 回傳結果如下 : :: May :AM 討論完日期時間類型後, 接著來看字元字串資料類型 142

29 SQL Server 2008 SQL Server 2008 預設提供的字元字串如表 4-9 所示 : Char 8000 Unicode Varchar 8000 Unicode Varchar(Max)* 2G Unicode Text 2G Unicode Varchar(Max) Nchar 4000 Unicode Nvarchar 4000 Unicode Nvarchar(max)* 1G Unicode Ntext 1G Unicode Nvarchar(Max) 4-9 SQL Server 2008 * SQL 20 字元 類型可用於儲存各種文數字與符號, 非 Unicode 的編碼長度與方式 隨各國語言不同而有所不同 Unicode 則統一編碼全世界的字元, 支援的字元 (character) 範圍較廣 運用數字與字元的對照表, 可以將全球的所有語言符號納 入, 可以說它是全世界最廣泛使用的編碼配置 當所有電腦都以一致的方式使用 Unicode 格式時, 資料可以自由地在不同的資訊系統轉換, 不須擔心接收端的系 統會將資料解釋成錯誤的字元 在支援多國語言的系統時, 若沒有特殊需求的限 制, 建議你應選擇使用 Unicode 類型, 將字元轉換的問題減到最少 關於 Unicode 的解釋可以參照如下的網址 : 若沒有多國語言需求, 例如, 代碼 / 編號一類的應用, 只有英文字母與數字的組合, 則用非 Unicode 字元較省空間 143

30 01 使用 char[(n)] / varchar[(n)] / nchar[(n)] / nvarchar[(n)] 等字元類型時, 儲存空間的 大小可由使用者自行決定 如範例程式碼 4-12 所示, 將變數的資料類型定義為 varchar(10) / nvarchar(10), 則代表儲存空間可存放 10 個字元 當儲存的資料長度 04 超出允許的字元數, 就會截斷資料 varchar(10) nvarchar(10) is a long character string' is a long character string' 執行結果如圖 4-6 所示, 超過 10 個字元的資料將截斷為 "This is a" 使用 char varchar nchar nvarchar 資料類型時, 最好指定足夠的字元長度, 避免字元被截斷 變數宣告中使用 char 或 varchar 資料類型時, 若沒有指定字元長度, 其預設值為 1, 變數指派超過 1 個字元時, 字元會被截斷 4-6 SQL Server 如範例程式碼 4-13 所示, 的資料類型分 別設定為 varchar 與 char, 並指派三個字元給予變數 接著, 使用 DATALENGTH 函數檢視變數的位元組 (byte) 長度時, 其結果為 1 AS varchar AS char = 'abc' = 'abc' -- SELECT -- DATALENGTH SELECT DATALENGTH(@myVariable), DATALENGTH(@myNextVariable); 4-13 DATALENGTH 使用型態轉換函數 (CAST CONVERT) 時,char 與 varchar 的字元長度預設值為 30, 如範例程式碼 4-14 所示, 的字元長度為 40 個字元 但在查詢陳述式, 分別搭配 CAST CONVERT 等系統函數時, 預設長度是 30: 144

31 SQL Server 2008 = 'This string is longer than thirty characters' -- SELECT CAST(@myVariable AS varchar) SELECT DATALENGTH(CAST(@myVariable AS varchar)) AS 'VarcharDefaultLength' SELECT SELECT AS 'VarcharDefaultLength' 4-14 varchar char n 30 由於 char 與 varchar 的預設位元組數為 30, 造成變數內 的字元截斷 試著使用 DATALENGTH 測試預設的位元 長度, 執行結果圖 4-7 所示 : 試將範例程式碼 4-14 更改為 Unicode 類型的 nchar 與 nvarchar 資料類型, 測試執行的結果如圖 4-8 所示 字 元長度的預設值為 30, 使用 DATALENGTH 函數測試變 數的位元組長度則為 60 將資料新增到定義為 nchar 或 nvarchar 資料類型的資料行時, 在字元前面加上大寫的 N 關鍵字, 可指定以 Unicode 編碼新增到資料行中, 這在不同國別輸入資料 4-7 varchar char AS varchar(40) DECLARE 時, 要特別小心 INSERT Book (Name) values(n'sql Server ') 若沒有使用 N 這個關鍵字,SQL Server 就會使用預設的 code page 儲存這些資料 伺服器上的 code page 若無法 4-8 nvarchar nchar 60 辨識這些資料時, 就會存入不正確值 不過, 只有當使用的延伸字元沒有辦法被常用的 code page 辨識時才會發生 在此建議你應養成指定 Unicode 字元的習慣, 測試語法如範例程式碼 4-15: 145

32 char(6) char(6) ' ' ' ' 简 ' 简 ' 简 ' 简 ' SELECT SELECT 4-15 N 在上述範例中, 由於透過鍵盤 / 螢幕輸入文字時, 作業系統是採用 Unicode, 因此可以選到簡體中文字 當存入系統時, 若宣告為 char 或 varchar, 預設採用筆者系統所預設的字碼 (big5), 而該字碼頁並沒有 简 這個字, 因此, 都自動轉成問號 而變數若宣告為 nchar, 但在設定值未指定 N, 則會以為是 big5 要轉成 Unicode 則在輸入時, 由 Unicode 先轉成 Big5, 再從 Big5 換成 Unicode, 而失去了可對應的字 4-9 若要儲存資料的長度超出允許的字元數, 就會截斷資料 ; 但若輸入的資料長度小 於定義的字元數呢? 這個判讀有些複雜 它會與定義資料行時, 連接的 ANSI_ PADDING 設定為何, 以及該資料行是否可以 NULL 有交互影響的關係 146

33 SQL Server 2008 SQL Server 會根據建立資料行當時的設定來儲存值 更新資料時, 在連接對 ANSI_PADDING 設定的變更並不會影響現有資料行 若資料行指定 NOT NULL,char 資料類型就會是固定長度的資料類型 插入短於資料行長度的值, 則值的右邊會填上空白, 以補滿該資料行的大小 例如, 將資料行定義為 char(10), 並儲存 "music" 資料,SQL Server 會將此資料儲存為 "music", 也就是 music 後五個空白 建立 CHAR 類型且允許 NULL 的資料行時, 如果 ANSI_PADDING 是 ON, 其作用就相當於資料行類型為 CHAR 且 NOT NULL: 會在值的右邊填上空白, 以補滿該資料行的大小 建立 CHAR 且允許 NULL 的資料行時, 如果 ANSI_PADDING 是 OFF, 則截斷尾端的空白, 等同 ANSI_PADDING 設為 OFF 的 VARCHAR 資料行 VARCHAR 是可變長度的資料類型 輸入值如果短於資料行的大小, 也不會在右邊填上空白 建立資料行時, 如果 ANSI_PADDING 選項的設定為 OFF, 會截斷儲存在資料行中尾端的空白 如果 ANSI_PADDING 設為 ON, 就不截斷後隨的空白 其定義如表 4-10: ANSI_ PADDING char(n) NOT NULL binary(n) NOT NULL char(n) NULL binary(n) NULL varchar(n) varbinary(n) 而 ANSI_PADDING 設定只會在建立資料表時, 影響新資料行的定義 之後, ON c h a r ( n ) varchar char b i n a r y ( n ) N O T binary N U L L varbinary ( ) OFF v a r c h a r varchar char varbinary binary varbinary 4-10 CHAR VARCHAR BINARY VARBINARY NULL NOT NULL ANSI_PADDING 147

34 01 04 SET ANSI_PADDING 設定不會影響 nchar nvarchar ntext text image 和大數 值 它們一律會顯示 SET ANSI_PADDING ON 行為 這表示 nchar 皆會補齊資料 長度,nvarchar 不會修剪尾端空格 測試與示範的程式碼如下 : SET ANSI_PADDING ON; GO CREATE TABLE t1 ( charnull CHAR(16) NULL, charnotnull CHAR(16) NOT NULL, varcharnull VARCHAR(16) NULL, varcharnotnull VARCHAR(16) NOT NULL, varbinarycol VARBINARY(8) ); GO INSERT INTO t1 VALUES (' ', ' ', ' ', ' ', 0x00ee); INSERT INTO t1 VALUES (' ', ' ', ' ', ' ', 0x00ee00); -- > < SELECT '>' + charnull + '<' AS ANSI_PaddintOn_CharNull, '>' + charnotnull + '<' AS ANSI_PaddintOn_CharNotNull, '>' + varcharnull + '<' AS ANSI_PaddintOn_varharNull, '>' + varcharnotnull + '<' AS ANSI_PaddintOn_varcharNotNull, varbinarycol AS ANSI_PaddintOn_varbinary FROM t1; GO DROP TABLE t1 GO SET ANSI_PADDING OFF; GO CREATE TABLE t1 ( charnull CHAR(16) NULL, charnotnull CHAR(16) NOT NULL, varcharnull VARCHAR(16) NULL, varcharnotnull VARCHAR(16) NOT NULL, varbinarycol VARBINARY(8) ); GO INSERT INTO t1 VALUES (' ', ' ', ' ', ' ', 0x00ee); INSERT INTO t1 VALUES (' ', ' ', ' ', ' ', 0x00ee00); SELECT '>' + charnull + '<' AS ANSI_PaddintOff_CharNull, '>' + charnotnull + '<' AS ANSI_PaddintOff_CharNotNull, 148

35 SQL Server 2008 '>' + varcharnotnull + '<' AS ANSI_PaddintOff_varcharNotNull, varbinarycol AS ANSI_PaddintOff_varbinary FROM t1; GO DROP TABLE t Ansi_Padding NULL Char/Varchar 以下是換成 Unicode 的對照組 : SET ANSI_PADDING ON; GO CREATE TABLE t1 ( ncharnull NCHAR(16) NULL, ncharnotnull NCHAR(16) NOT NULL, nvarcharnull NVARCHAR(16) NULL, nvarcharnotnull NVARCHAR(16) NOT NULL ); GO INSERT INTO t1 VALUES (N' ', N' ', N' ', N' '); INSERT INTO t1 VALUES (N' ', N' ', N' ', N' '); SELECT '>' + ncharnull + '<' AS ANSI_PaddintOn_NCharNull, '>' + ncharnotnull + '<' AS ANSI_PaddintOn_NCharNotNull, '>' + nvarcharnull + '<' AS ANSI_PaddintOn_NvarharNull, '>' + nvarcharnotnull + '<' AS ANSI_PaddintOn_NvarcharNotNull FROM t1; GO DROP TABLE t1 GO '>' + varcharnull + '<' AS ANSI_PaddintOff_varharNull, SET ANSI_PADDING OFF; GO CREATE TABLE t1 ( ncharnull NCHAR(16) NULL, ncharnotnull NCHAR(16) NOT NULL, nvarcharnull NVARCHAR(16) NULL, nvarcharnotnull NVARCHAR(16) NOT NULL ); GO INSERT INTO t1 VALUES (N' ', N' ', N' ', N' '); INSERT INTO t1 VALUES (N' ', N' ', N' 149

36 01 04 SELECT '>' + ncharnull + '<' AS ANSI_PaddintOff_NCharNull, '>' + ncharnotnull + '<' AS ANSI_PaddintOff_NCharNotNull, '>' + nvarcharnull + '<' AS ANSI_PaddintOff_NvarharNull, '>' + nvarcharnotnull + '<' AS ANSI_PaddintOff_NvarcharNotNull FROM t1; GO DROP TABLE t Ansi_Padding NChar NVarchar 上述兩個範例程式碼的執行結果如圖 4-10 所示 : 4-10 Ansi_Padding NULL Char/Varchar/NChar/NVarchar ', N' '); 參照前文的說明, 比對範例程式碼 與圖 4-10 後, 相信你可以釐清這 些差異 需要提醒的是 : 未來的 SQL Server 版本中,ANSI_PADDING 一定是 ON, 若明確將此選項設定為 OFF 將會產生錯誤 請避免在新的專案中使用該功 能, 並規劃修改目前使用這項功能的應用程式 文字字串類型就討論到此, 接著來看數值資料類型 SQL Server 2008 支援的數值資料類型如表 4-11: 150

37 SQL Server 2008 ( ) tinyint smallint 2-2^15 (-32,768) 2^15 1 (32,767) int 4-2^31 (-2,147,483,648) 2^31 1 (2,147,483,647) bigint 8-2^63 (-9,223,372,6,854,775,808) 2^63-1 (9,223,372,6,854,775,8) d e c i m a l / numeric [p[,(s )]] ^ ^38-1 p (precision) 38 s p s 0 <= s <= p <= 38 (p ) 1~9 5 10~ ~ ~ decimal(38) select datalength(@i) numeric decimal 4-11 SQL Server

38 01 ( ) 04 float[(n)] E+308~1.79E+308 IEEE 754 n float n 1 53 n 53 n 1~ ~53 10~19 8 n real E+38~ IEEE 754 money , 3 3 7, 2 0 3, 6 8 5, ,337,2,685, smallmoney 4-214, , SQL Server 2008 雖然使用數值的資料類型非常簡單, 只要針對資料行或是變數評估資料範圍, 選 擇足夠容納該資料的類型就可以了 不過, 部分數值類型所配置的資料儲存空間 是固定的, 當定義了不適當的資料類型, 會浪費不少空間 例如, 某個資料行定 義為 int 類型, 但實際上儲存的數值都小於 50, 理當宣告 tinyint 類型就夠了 若 資料表共有 50,000 筆資料, 則浪費 0.14MB 空間 :((((4-1)*50,000)/14)/1048) IEEE 754 規格提供四種四捨五入模式 : 四捨五入進位 無條件進位 無條件捨去進位與四捨五入到零 SQL Server 則使用無條件進位 所有運算都準確到保證的精確度, 但可能產生稍微不同的浮點值 因為浮點數的二進位表示法可以使用任一種有效的四捨五入方式, 所以不可能有效地限定浮點數值 152

39 SQL Server 2008 料長度 在 SQL Server 20 Service Pack 2 版本後,decimal 資料類型可使用 Vardecimal 儲存格式來儲存成可變長度資料行,Vardecimal 是儲存方式的不同, 11 而非資料類型 該功能適用於 Enterprise Developer 和 Evaluation Edition 等版本 ; 但未來的 SQL Server 將移除 Vardecimal 功能 所以, 應避免在新的開發專案中使用該功能,SQL 2008 後, 建議改用新的 ROW 和 PAGE 壓縮功能 浮點數不需指定小數點前後的位數, 這與準確值資料類型剛好相反 浮點數之所以會歸類為近似值資料類型, 是由於浮點數字的小數點位數不像準確數值是固定的 不過, 浮點運算能處理的數字範圍較大, 這是它的優點 需要精確數值比較時, 須小心使用 float 和 real 資料類型, 因為會有數值運算誤差, 例如, 財務應用程式 牽涉到四捨五入的作業, 或者進行相等比較時 應使用 integer decimal money 或 smallmoney 資料類型 另外, 避免在 WHERE 子句的搜尋條件中使用 float 或 real 資料行, 尤其是 = 與 <= >= 運算子, 最好限制為 > 或 < 比較 money 和 smallmoney 資料類型的用法相當直觀, 其精確度可達它們所代表之金融單位的萬分之一 不過有兩個部分需要特別注意 首先, 不要為了呈現格式, 自行替金額加上逗點 這會讓 INSERT 與 UPDATE 的指令發生錯誤 其次, 預設的貨幣符號為美元 ( $ ), 如果要使用不同的貨幣符號, 只要在金額的前面加上指定 關於 decimal(numeric) 資料類型要提醒的一點是 : 該類型通常佔用磁碟固定資 的貨幣符號即可 程式碼如下所示 : INSERT Product (ProductName, Price) values( A-79, ) 不過, 指定前面有貨幣符號的貨幣值時,SQL Server 不會儲存任何與符號相關聯的貨幣資訊, 它只會儲存數值 11 啟動 Vardecimal 儲存格式的作法可參考線上叢書 : Database Engine 開發 查詢及變更資料 存取與變更資料庫資料 Transact-SQL 的元素 資料類型 (Database Engine) 使用 decimal float 與 real 資料 將十進位資料儲存成可變長度 153

40 處理數值資料類型時, 需要注意當運算使用到超過該類型資料可容許的小數位數時,SQL Server 將採用無條件進入 捨去, 還是四捨五入 各類型說明如下 : 整數類型 : 運算所產生的分數都會無條件捨去, 不會四捨五入 例如, SELECT 5/3 會傳回數值 1, 而非將分數的結果四捨五入得到 2 浮點數 (float 和 real): 無條件進入到精確範圍內 貨幣資料 : 小數點第四位後, 四捨五入, 範例如下 : select convert(money, ) [ ],CONVERT(money, ) [ ] 執行結果如圖 4-11 所示 : 4-11 十進位數值 (Decimal 或 Numeric): 四捨五入到有效的小數位數 其範例如下 : select convert(decimal(3,2),0.345) [0.345],CONVERT(decimal(3,2),0.2345) [0.2345] 其執行結果如圖 4-12 所示 : Round 4-12 Decimal 若要處理四捨五入, 較常搭配使用 Round 函數, 以四捨五入或捨去到指定長度的 有效位數 其語法定義如下 : ROUND (, [, ] ) 154

41 SQL Server 2008 數值運算式 : 精確數值或近似數值資料類型的運算式, 但 bit 資料類型除外 長度 : 數值運算式 取四捨五入的有效位數 長度 必須是 tinyint smallint 或 int 等類型的運算式 當 長度 是正數時, 數值運算式 會四 捨五入到 長度 所指定的小數位數 若是負數, 則對小數點左側位數四捨五 入 其範例如下 : s e l e c t R O U N D(45.45, -2) [-2],R O U N D(45.45, -1) [-1],R O U N D(45.45, 0) [0],ROUND(45.45, 1) [1] 執行結果如圖 4-13 所示 : 4-13 圖 4-13 中需要注意的是第一欄, 因為 長度 是負 2, 所以取小數點左邊第二 位, 範例中是 4, 被捨去後, 變成整個數值為 0 若數值為負, 且大於小數點 前面的位數,ROUND 會傳回 0, 例如 :ROUND(45.45, -3) 會傳回 0 功能 : 這是要執行的作業類型 功能 引數的資料類型必須是 tinyint smallint 或 int 當省略此參數, 其預設值為 0, 代表要四捨五入 數值運算式 當指定為非 0 值, 則是無條件捨去 其範例如下 : 函數會傳回與 數值運算式 相同的類型結果, 函數的引數定義如下 : Round select ROUND(45.45, -1,0) [-1,0],ROUND(45.45, -1,1) [-1,1],ROUND(45.45, 1,0) [1,0],ROUND(45.45, 1,1) [1,1] 執行結果如圖 4-14 所示 :

42 當需要儲存位元資料流 (stream), 例如, 多媒體資料 特殊格式的文檔 加密過的資料 等等, 就必須使用二進位資料類型 而 SQL Server 預設提供的一些類型, 如 uniqueidentifier(uuid) 實際存放時, 也就是二進位資料 04 SQL Server 提供了如表 4-12 所呈現的二進位資料類型 : Binary 8000 Varbinary 8000 Varbinary(max)* 2G Image 2G Varbinary(Max) 4-12 SQL Server 2008 * SQL 20 Binary 與 Varbinary 資料最多可儲存 8,000 個位元組, 但 Varbinary 宣告時若搭配 max, 最多可儲存 2^31 個位元組 二進位資料呈現時, 會在常數前面以 0x ( 一個零和小寫字母 x) 開始, 後面跟著以 16 進位表示的位元模式, 也就是以兩個 16 進位數字代表一個位元組 (byte), 在使用上不需要替插入資料行的資料加上單引號 而 Binary 和 Varbinary 的差異如同先前所介紹的 Char 和 Varchar, 存放固定或變動長度的資料 我們簡單地示範將二進位資料存入資料表 ; 並從資料表取出存回到檔案 : USE TempDB GO CREATE TABLE tbimg( PK INT IDENTITY PRIMARY KEY, FileName nvarchar(60), FileType nvarchar(30), ImageFile varbinary(max)) GO 156

43 SQL Server 2008 SELECT 'Img0408.jpg' AS FileName, 'JPG' AS FileType, * FROM OPENROWSET(BULK N'C:\Temp\IMG0408.JPG', SINGLE_BLOB) AS ImageFile -- SELECT * FROM tbimg /* Management Studio SQLCMD mode (Format File) SQLBINARY 0 0 "" 1 ImageFile "" */!!bcp "select ImageFile from tempdb.dbo.tbimg where PK=1" queryout "C:\temp\myJPG.jpg" -T -f bcp.fmt 4-18 上述查詢二進位資料的結果如圖 4-15 所示 : 範例程式碼 4-18 中, 利用 SQL Server 20 後替 OpenRowset 系統函數增加的功 能, 以 Bulk 方式直接讀入存在單一檔案內的大型二進位資料 (SINGLE_BLOB), 搭配 Insert 語法載入到資料表中 最後再以 bcp 工具程式藉由 Format file 指定格 式, 將資料表內的大型二進位資料輸出到對應的檔案中 INTO tbimg(filename, FileType, ImageFile) INSERT SQL Server 2008 後, 強化了 CONVERT 系統函數在二進位資料類型與 16 進位字串間的轉換 以 CONVERT 函數為例, 其語法格式如下 : CONVERT ( [ ( ) ], [, style ] ) 其強化的部分是在第三個參數 style, 使用說明如下 : 157

44 ( ) 將 ASCII 字元轉譯成二進位位元組, 或將二進位位元組轉譯成 ASCII 字元 每個字元或位元組都會以 1:1 的方式轉換 若 資料類型 是二 進位類型, 就會在結果的左邊加入字元 0x 1, 2 (SQL Server 2008 ) 資料類型 是二進位類型, 此 運算式 的結果就必須是字元 運算式 必須由偶數個十六進位代表數字 ( 如 :0,1,2,3,4,5,6,7,8,9,A,B,C,D,E,F,a, b,c,d,e,f) 所構成 如果為 style 1, 字元 0x 就必須是運算式中的前兩個字元, 若為 style 2 則否 如果此運算式含有奇數個字元, 或者其中任何字元無效, 會引發錯誤 若已轉換之運算式的長度超過 資料類型 的 長度, 結果就會向右截斷 固定長度的 資料類型 其定義若大於轉換之結果, 會在結果的右邊加入零 資料類型 是字元類型, 此 運算式 的結果就必須是二進位 每個二進位字元都會轉換成兩個十六進位字元 如果已轉換之運算式的長度超過 資料類型 的 長度, 就會向右截斷 若 資料類型 是固定大小的字元類型, 而且已轉換之結果的長度小於 資料類型 的 長度, 就會在轉換結果的右邊加入空格, 以便維持偶數個十六進位數字 若為 style 1, 就會在已轉換之結果的左邊加入字元 0x, 若為 style 2 則否 S E L E C T C O N V E R T(V A R C H A R(12), 0x49747A696B, 0) A S [0x49747A696B t o VARCHAR(12) Style 0] S E L E C T C O N V E R T(V A R B I N A R Y(5), '0x49747A696B', 0) A S ['0x49747A696B' t o VARBINARY(5) Style 0] SELECT CONVERT(VARCHAR(5),0x , 0) AS [0x to VARCHAR(5) Style 0] S E L E C T C O N V E R T(V A R C H A R(12), 0x49747A696B, 1) A S [0x49747A696B t o VARCHAR(12) Style 1] S E L E C T C O N V E R T(B I N A R Y(10), '0x49747A696B', 1) A S ['0x49747A696B' t o BINARY(10) Style 1 0] S E L E C T C O N V E R T(V A R C H A R(5), 0x49747A696B VARCHAR(5) Style 2 ] S E L E C T C O N V E R T(V A R C H A R(6), 0x49747A696B VARCHAR(6) Style 2 ] S E L E C T C O N V E R T(V A R B I N A R Y(5), '49747A696B' VARBINARY(5) Style 2];, 2) A S [0x49747A696B t o, 2) A S [0x49747A696B t o, 2) A S ['49747A696B' t o 4-19 Convert Style 158

45 SQL Server Convert Style 有了 Convert 搭配 Style 1 或 2 的方式後, 可以用來轉換透過雜湊 (Hash) 運算後的密碼值, 由於 HashBytes 系統函數在使用 MD2, MD4, MD5 等演算法時, 統一回傳 128 bits (16 bytes) 二進位資料, 搭配 SHA 和 SHA1 演算法則回傳 160 bits (20 bytes) 二進位資料, 所以經由如下的語法, 就可以字串的方式存放或傳回給其他應用 : SELECT CONVERT(VARCHAR(40),HASHBYTES('sha1','Password'),2) 其執行結果如圖 4-16 所示 : 除了利用上述的 Convert 系統函數外, 也可以叫用 sys.fn_varbintohexstr 系統函數, 它傳入 varbinary(max) 類型的參數, 一樣傳出 nvarchar(max) 類型資料, 並將輸入的二進位值以 16 進位數值的文字描述法呈現 範例如下 : select sys.fn_varbintohexstr(hashbytes('sha1','password')) 以上兩句語法依其在文章的先後順序, 執行結果如圖 4-17 所示 :

46 01 二進位資料就介紹到此, 接下來繼續討論大型資料類型 當單一資料超過 8 K 位元組大小時,SQL Server 提供可存放在資料表內的大型資料類型, 如表 4-13 所示 : 04 Varchar(max)* Text NVarchar(max)* NText Varbinary(max)* Image XML* unicode u n i c o d e Varchar(Max) unicode u n i c o d e NVarchar(Max) Varbinary(Max) Unicode (UTF-16) XML CLR * (UDT).NET 4-13 SQL Server 2008 * SQL 20 表 4-13 中,MAX 關鍵字是 SQL 20 新增功能, 當宣告了 MAX 關鍵字, varchar nvarchar varbinary 等類型欄位最多可以存放 2Giga (2^31-1) 位元組的資料 而不再受限於以往的 8K ( 也就是 SQL Server 資料存取基本單位的大小 ) 大型資料的最大長度都是 2G 位元組, 而 CLR UDT 需要等到 SQL Server 2008 才可以存放到 2G 大小 varchar(max) nvarchar(max) varbinary(max) 三種資料類型是 SQL Server 20 提供的新資料類型, 可用來取代 text ntext 以及 image 等資料類型 由 SQL Server 自行決定存放大型資料的方式, 而不需我們事先決定以指標結構存放 text, 還是一般資料頁內資料列的 char 等資料格式 160

47 SQL Server 2008 一般的 字元處理函數都可以操作 VARCHAR(MAX) / NVARCHAR(MAX) 欄位內的資料, 而不像 text ntext 會讓很多字元處理函數無法使用 SQL Server 20 針對這三種資料類型在 Update 語法上, 也新增了 Write 方法, 功能與以往 Text 和 Image 型態的文字指標功能近似, 讓你可以局部更新大型資料, 如範例程式碼 4-20 所示 : --NVARCHAR(MAX),VARCHAR(MAX),VARBINARY(MAX) --.write CREATE TABLE tbtest(c1 NVARCHAR(MAX)) INSERT tbtest VALUES(REPLICATE(N' ',1000)) UPDATE tbtest SET c1.write('lmnop',10,3) SELECT * FROM tbtest 4-20 Write 範例程式碼 4-20 透過 REPLICATE 函數將 字元重複 1000 次後加入資料表 ; 藉以模擬大量文字當做 c1 欄位的值 然後透過 WRITE 語法將欄位中第 10 個字開始, 以 lmnop 字元取代掉原始內容的 3 個字 表 4-13 的大型資料可以儲存在個別資料列中, 或是獨立於資料表之外的大型字元或二進位字串結構, 這是由 sp_tableoption 系統預存程序中的兩個選項來控制 : 而以 VARCHAR(MAX) / NVARCHAR(MAX) 取代 text ntext 最大的好處在於, large value types out of row 選項 : 適用於 varchar(max) nvarchar(max) varbinary(max) 和 xml 資料類型 text in row 選項 : 適用於 text ntext 和 image 資料類型 除非將 text in row 選項設為 ON, 否則 text ntext 或 image 資料類型都是儲存在資料列之外的大型字元或二進位字串 資料列僅存放 16 位元組的指標, 指向由內部指標所組成之樹狀結構的根節點 這些指標會對應用來儲存字串片段的分頁, 其示意圖如圖 4-18 所示 : 161

48 SQL Server 可以針對包含大型資料類型資料行的資料表來設定 text in row 選項 也可以指定 text in row 選項限制資料大小, 從 24 到 7,000 位元組 在此需要強調的是 : 未來的 SQL Server 版本中, 將會移除 text in row 選項 避免在新的開發工作中使用此選項, 並請計畫修改目前使用 text in row 的應用程式 建議使用 varchar(max) nvarchar(max) 或 varbinary(max) 資料類型來儲存大型資料 若要控制這些資料類型的儲存方式是同資料列或資料列外, 可使用 large value types out of row 選項 在此, 也就不細談 text ntext 或 image 資料類型, 若有需要, 請參考線上說明 : Database Engine 同樣地, 預設 large value types out of row 選項為 OFF,varchar(max) nvarchar(max) varbinary(max) 以及 xml 等類型的資料會儘可能地儲存在資料列中, 大數值資料類型的最大同資料列儲存體設定為 8,000 位元組 與 text in row 選項不同的是, 不能為資料表中的資料行指定同資料列限制 若設為 ON, 就會以非資料列方式來儲存值, 只有 16 位元組的指標會儲存在記錄中 162

49 SQL Server 2008 集 讓讀寫速度幾乎等同一般資料類型 反之, 若是以非資料列來儲存值, 資料庫引擎就會引發額外的分頁讀寫動作 另外, 也可能將指標從根節點移到資料列本身, 資料庫引擎不使用根節點, 以省略讀寫時的分頁存取, 這可提高執行效能 當使用根節點時, 會將其儲存成大型資料分頁中的字串片段, 最多可容納五個內部指標 資料列內必須有 72 位元組的空間, 才能儲存五個指標 當 text in row 選項為 ON, 或 large value types out of row 選項為 OFF, 如果資料列中的空間不足, 無法容納指標, 資料庫引擎可能需要配置 8K 分頁來容納它們 如果該值的資料長度超過 40,200 位元組, 則需要五個以上的同資料列指標, 這時會只存 24 位元組在主要資料列中, 並改在大型資料儲存空間中配置額外的資料頁 當大型字串儲存於資料列時, 其儲存方式和可變長度字串類似 資料庫引擎會以大小遞減的順序來將資料行排序, 並將超過 8K 的資料存到非資料列, 讓剩餘的資料行符合分頁大小 經由 sp_tableoption 系統預存程序可啟用與停止資料表的 large value types out of row 選項, 設定方式如下 : 將大型資料類型儲存在資料列, 資料庫引擎讀寫時, 不需存取其它分頁或分頁 CREATE TABLE tbimg ( PK INT IDENTITY PRIMARY KEY, FileName NVARCHAR(60), FileType NVARCHAR(30), ImageFile VARBINARY(Max) ) go exec sp_tableoption N'tbImg', 'large value types out of row', 'ON'

50 01 當改變 large value types out of row 設定值時, 現存的 varchar(max) nvarchar(max) varbinary(max) 及 xml 值並不會立刻轉換 在後續更新時, 才會變更儲存方式 新增的記錄, 會依照實際的資料表選項來儲存 若要檢查特定資料表的 large value types out of row 設定值, 可查詢 sys.tables 系統檢視的 large_value_types_out_of_row 資料行 如果資料表未啟用 large value types out of row, 該資料行是 0, 若啟用則為 1 範例如下: select name,text_in_row_limit,large_value_types_out_of_row from sys.tables where name LIKE 'tb%' 執行結果如圖 4-19 所示 : 4-19 否則, 由於大量資料儲存在資料列本身, 將降低每一分頁可容納的資料列數量 而大部分存取資料表的陳述式都不存取大型資料類型的資料行, 則減少分頁中的資料列, 將增加處理查詢時必須讀取的分頁 換句話說, 若多數對資料表的存取不需參考大型資料行, 建議將此選項設為 ON 以資料列外方式來儲存大型資料, 讓每個分頁可容納的資料列更多, 以降低掃描資料表時所需的 I/O 數量 在先前的範例程式碼 4-21 中, 簡單地將大型資料與一般資料放在相同的資料表中, 但若大型資料在一般應用時, 不常用到, 為避免使用者查詢時, 未明確指定 欄位 ( 例如, 用 select * 取資料 ), 而涵蓋了大型資料欄位, 雖然使用者用不到, 但卻需要從硬碟載入記憶體, 或在網路間傳遞, 這將浪費許多資源 建議將大型資料獨立放在另一個資料表中, 然後以 1 對 1 連結的方式對應存放相關資料的主資料表, 當使用者明確需要時, 再 Join 取得 04 最後, 當使用了大型資料類型後, 透過 sp_help 系統預存程序 ; 或 sys.columns 系統檢視分析資料表時, 相關資料行的 Length 欄位將傳回 -1 範例如下所示: exec sp_help 'tbimg' 164

51 SQL Server 特殊資料類型是指不屬於在前幾節中曾經討論過的任何一種資料類型 在 SQL Server 2008 中, 特殊資料類型包括 bit hierarchyid sql_variant sysname table timestamp CLR UDT 自訂資料表類型 別名資料 等諸多類型, 各有其特殊應用 展望未來,SQL Server 期待將世界上所有資料都納入管理, 相信在每個新版中, 都會增加些新的資料類型, 以因應更多特殊的用途 執行結果如圖 4-20 所示 : 各特殊資料類型的基本定義如表 4-14 所示 : bit cursor^ HierarchyID** Geography** 0 1 (cursor).net Common Language Runtime (CLR) ( )

52 01 Geometry**.NET Common Language Runtime (CLR) Euclidean ( ) Rowversion(timestamp) timestamp Sql_variant ( text, ntext, rowversion sql_variant ) sysname sysname SQL SQL Server sysname nvarchar(128) sysname NOT NULL SQL Server sysname varchar(30) sysname SQL Server Table^ uniqueidentifier 128 bit (16 ) (Global unique identifier GUID Universal unique identifier) XML* SQL Server 20 XML xml XML XML CLR UDT*.NET (class) (structure) SQL Server SQL Server 2008 HierarchyID Geometry Geography.NET 04 **^ SQL Server

53 SQL Server 2008 * 代表該資料類型在 SQL Server 20 版本後才提供 ** 代表該資料類型在 SQL Server 2008 版本後才提供 ^ 代表該資料類型不可用在資料表內資料行的類型宣告 表 4-14 中有些資料類型在此僅簡單說明 : hierarchyid : 用來管理具有階層式結構的階層式資料和資料表 若要 在 程式碼中使用階層式資料, 可考慮使用 hierarchyid 資料類型所提供的 各類函數, 以簡化開發 Geography Geometry : 存放經緯度或 X Y 座標資料 XML : 可以在資料表中建立 xml 類型的資料行, 或是 xml 類型的變數等, 儲存 XML 執行個體 CLR UDT :SQL Server 20 後, 可以利用.NET Framework Common Language Runtime (CLR) 建立的組件來當作資料庫物件 藉由 CLR 所提供的豐富程式設計模型, 建立觸發程序 預存程序 函數 彙總函數和使用者自訂類型 (CLR UDT) 等 由於上述資料類型的用法複雜, 需較多程式寫作的經驗, 你可以參閱 DBWorld ( 雜誌相關的專欄, 我們將在 SQL Server 2008 資料庫開發 一書中再詳細介紹 表 4-14 中, 各符號所代表的意義如下 : 以下個別介紹不同資料類型的特徵 bit 一般來說, 若僅是 是 / 否 真 / 偽 兩種值的資料, 可用 bit 資料類型來存 例如, 性別 婚姻 兵役 等 而字串值 true 和 false 也可以轉換成 bit 值, 如下列範例所示 : 167

54 01 BIT=1 SELECT CONVERT (bit, 'true') [True],CONVERT(bit, 'false') [bit ] 在上述範例中,true 會轉換成 1 而 false 會轉換成 0 執行結果如圖 4-21 所示 : true false bit 1 0 若資料表內僅有一個資料行的類型為 bit, 則該資料行仍占有 1 位元組 (byte) 但若資料表內有小於等於 8 個 bit 類型的資料行, 這些資料行將共佔掉一個位元組, 超過 8 但小於等於 16 個 bit 類型資料行, 則佔掉 2 個位元組, 依此類推 因此, 只要資料表內有多個資料行儲存 是 / 否 真 / 偽 兩種值的資料, 宣告成 bit 資料類型是最省空間, 並提升效率 cursor SQL Server 實作的 資料指標 (Cursor) 是以 DECLARE CURSOR 語法為基礎, 主要用於 指令碼 預存程序和觸發程序 資料指標是在伺服器上實作, 從用戶端傳送到伺服器的 陳述式來管理 預存程序只能在 OUTPUT 參數使用 cursor 資料類型 如果參數指定 cursor 資料類型, 就需要 VARYING 和 OUTPUT 參數 如果參數指定 VARYING 關鍵字, 資料類型必須為 cursor, 也必須指定 OUTPUT 關鍵字 在此利用預存程序簡單示範傳出資料指標的方式如下 ( 如何撰寫預存程序, 請參考 第 10 章 : 程式設計概論 ): -- CREATE TABLE t(c1 INT,C2 CHAR(1)) GO INT=1 BEGIN INSERT t VALUES(@i,CHAR(@i+64)) END GO 168

55 SQL Server 2008 CREATE PROCEDURE CURSOR VARYING OUTPUT AS = CURSOR FORWARD_ONLY STATIC FOR SELECT * FROM t; 4-22 接下來, 執行一個宣告區域資料指標變數的批次 執行此程序將資料指標指派給 區域變數, 再從資料指標擷取資料列 : CURSOR; EXEC OUTPUT; WHILE (@@FETCH_STATUS = 0) BEGIN; FETCH NEXT END; 範例程式碼 4-23 中, 透過資料指標與 Fetch Next 語法取回所有的記錄, 而後關 閉 / 釋放資料指標結構 在此僅作簡單的示範, 關於如何在 語法中使用 Cursor, 你可以參閱 DBWorld ( 雜誌相關的專欄, 我們 將在 SQL Server 2008 資料庫開發 一書中詳細介紹 t sql_variant 除了表 4-15 所列的資料類型外,sql_variant 資料類型可讓單一資料行 參數或變 數儲存不同資料類型的資料值 : 169

56 01 varchar (max) varbinary (max) nvarchar (max) xml text ntext image timestamp sql_variant hierarchyid cursor table 4-15 sql_variant 04 sql_variant 類型可以存放各種基底資料類型, 這提供了存放未知資料的彈性, 例 如, 屬性 / 值 資料表, 假設要存放賣場產品的屬性, 但會有什麼性質的資料 事前無法規劃, 因此, 用一個資料表擺放屬性與值的對應, 而設定成 sql_variant 類型的資料行, 則可以存放各式資料 12 sql_variant 資料行的每筆記錄 ( 執行個體 ) 都會同時存放資料值和描述此值的中繼 資料 當資料行內包含了各式各樣的資料後, 你可能想要判讀該資料的實際規 格 透過 SQL_VARIANT_PROPERTY 系統函數, 能取得有關 sql_variant 值的屬 性資訊, 如資料類型 有效位數或小數位數 總資料長度 等 範例程式碼 4-24 利用 SQL_VARIANT_PROPERTY 系統函數呈現相關的中繼資料 : CREATE variant) go TABLE tbproperty(prodid INT,Property NVARCHAR(50),Value sql_ INT=1 INSERT tbproperty VALUES(1,N' ',@i) -- INSERT tbproperty VALUES(1,N' ',@i) --NULL INSERT tbproperty VALUES(1,N' ', ) -- Bigint numeric INSERT tbproperty VALUES(2,N' ',CAST(1234 AS FLOAT)) -- cast INSERT tbproperty values(3,n' ',' ') INSERT tbproperty values(3,n' ',N' ') -- unicode INSERT tbproperty VALUES (3,N' ',CAST(N' ' AS NVARCHAR(50))) SELECT Value, SQL_VARIANT_PROPERTY(Value,'BaseType') AS [ ], SQL_VARIANT_PROPERTY(Value,'Precision') AS [ ], SQL_VARIANT_PROPERTY(Value,'Scale') AS [ ], SQL_VARIANT_PROPERTY(Value,'TotalBytes') AS [ ], SQL_VARIANT_PROPERTY(Value,'Collation') AS [ ], SQL_VARIANT_PROPERTY(Value,'MaxLength') AS [ ( )] FROM tbproperty 4-24 sql_variant 170

57 SQL Server sql_variant 由圖 4-22 可知, 資料庫引擎會因為輸入資料的內容, 自動判讀適合的類型 這讓 我想起 VB 或 VBScript 的 variant 型別, 或是.NET 的 Object 基礎類別, 要掌控得 好非常不易, 而這往往是潛藏 bug 的地方 若要使用它, 最好先熟知基本類型的 運作規則, 你可以參考如下的線上說明 : Database Engine Transact-SQL (Database Engine) sql_variant sysname sysname 資料類型可用於儲存資料物件名稱的資料行 變數和預存程序參數 sysname 的確切定義與資料庫引擎識別碼的規則有關 因此, 它可能會隨著不 同的 SQL Server 版本而變 sysname 在功能上與 nvarchar(128) 相同, 不同的是 sysname 預設為 NOT NULL 在 SQL Server 6.5 版或更舊版本僅支援較小的識別 碼, 換句話說,SQL Server 6.5 版以前 sysname 定義成 varchar(30) 執行結果如圖 4-22 所示 : 需要注意的是 : 在區分大小寫或有二進位定序的資料庫中, 只有當 sysname 以小寫出現時, 系統才會將它辨識為 SQL Server 系統資料類型 簡單做一個測試如下 : CREATE TABLE t(c1 sysname) S E L E C T n a m e,s y s t e m_t y p e_i d,t Y P E_N A M E(s y s t e m_t y p e_i d) [ ],m a x_ length,is_nullable FROM sys.columns WHERE object_id=object_id('t') 4-25 sysname SQL Server 這只是想像的用途 其實, 我不知道 sql_variant 適用在哪裡, 因為不曉得如何搭配撰寫應用程式 171

58 01 執行結果如圖 4-23 所示 : 4-23 sysname SQL Server table 此種資料類型僅可用來定義 table 類型的本機變數, 以及使用者自訂函數的傳回值, 無法當作資料行的資料類型使用 建立 table 資料類型變數的功能就如同暫存資料表, 它可用來儲存結果集, 以便之後處理 資料表變數或傳回值的定義包含資料行定義 資料類型 有效位數 每個資料行的小數位數, 以及選擇性的 PRIMARY KEY UNIQUE NULL CHECK 等條件約束 table 變數中儲存的資料列格式, 或使用者定義多重陳述式資料表值函數所傳回的資料列格式 ; 必須在宣告此變數或建立此函數時定義 ( 如何撰寫使用者自訂函數, 請參考 第 10 章 : 程式設計概論 ) 其語法是以 CREATE TABLE 語法為基礎, 如範例程式碼 4-26 所示 : -- Primary Key Not Null check CREATE FUNCTION fn(@p1 int,@p2 int,@p3 int) TABLE ( c1 int primary key, c2 int not null check(c2 > 10) ) AS BEGIN VALUES(@p1,@p2) VALUES(@p3,@p4) RETURN END GO 04 select * from dbo.fn(1,12,2,20) select * from dbo.fn(1,12,2,null)

59 SQL Server 2008 結構 接著, 在函數定義中, 賦予該資料表變數相關記錄後回傳 最下方, 利用 select 語法呼叫自訂的資料表值函數, 在此, 可以透過不同的參數值, 測試先前定義的 主鍵, 不可 NULL, 和 Check 等條件約束的功能 uniqueidentifier 在分散式資料處理時, 想要維持散在各處多個資料表的 唯一 主鍵 ( 例如, 各分店自行建立的客戶編號 ), 一般是設計成雙資料行主鍵, 由 identity 數值搭配區 13 域碼 而另一個作法是使用 uniqueidentifier 類型 要產生一個 UniqueIdentifier 值, 最簡單的方式是叫用 newid 系統函數, 範例如下 : uniqueidentifier = NEWID() 執行結果如下 : 9E9338-0FE9-47B0-8FFE-1D1D279248AF 另一個方式是呼叫 NEWSEQUENTIALID 系統函數, 它會根據所在電腦建立 GUID, 每次新產生出的 GUID 值大於之前由這個函數所產生的任何 GUID 在範例程式碼 4-26 一開始, 就以 TABLE 定義函數回傳的資料表變數 如果有資料安全的顧慮, 請勿將 GUID 資料值暴露給使用者看, 或不要使用這個函數 因為使用者不難猜出下一個 GUID 值, 進而存取與該 GUID 相關聯的資料 NEWSEQUENTIALID 系統函數只能用在 uniqueidentifier 類型之資料表資料行的 DEFAULT 條件約束 例如 : 13 筆者看過一個比喻, 若要兩個符合 GUID / UUID 公式產生出來的值相等, 其機率小於彗星撞地球, 換句話說, 若運氣不好, 分處兩地的資料表產生出的 UniqueIdentifier 值相等, 你也不用懊惱, 因為地球已經不在了 173

60 01 04 CREATE TABLE tb (C1 uniqueidentifier DEFAULT NEWSEQUENTIALID()) GO INSERT tb DEFAULT VALUES INSERT tb DEFAULT VALUES WAITFOR DELAY '00:01:30' INSERT tb DEFAULT VALUES INSERT tb DEFAULT VALUES select c1 [uniqueidentifier],convert(binary(16),c1) [uniqueidentifier 2 ] from tb 4-27 uniquedientifer 2 範例程式碼 4-27 的執行結果如圖 4-24 所示 : 4-24 uniquedientifer 2 在範例程式碼 4-27 中, 故意在利用 NEWSEQUENTIALID 系統函數產生 GUID 值時, 延遲 1 分 30 秒, 讓時間因素影響 NEWSEQUENTIALID 系統函數 當直接檢視 UniqueIdentifier 底層的 2 進位資料轉化成 GUID 表示法時, 會覺得資料值並沒有越來越大, 但若改以 2 進位資料直接檢視時, 可以發現從 0x 開始的前四個位元組之順序與 GUID 表示法是顛倒的, 而 GUID 實際的 2 進位值的確是越來越大 而利用 UniqueIdentifer 資料行排序時, 是以 2 進位值為準 當 NEWSEQUENTIALID 用於 DEFAULT 運算式時, 不能與其他純量運算子結合 例如, 不可執行下列作業 : CREATE TABLE t(c1 uniqueidentifier DEFAULT dbo.fn(newsequentialid())) 在上述範例中, 即便 f n ( ) 是個簡單的使用者自訂純量值函數, 僅接受和傳回 u n i q u e i d e n t i f i e r 值, 仍會有錯誤 我們直接以函數呼叫來證明 NEWSEQUENTIALID 不能在查詢中參考 其範例如下 : 174

61 SQL Server 2008 RETURNS UNIQUEIDENTIFIER AS BEGIN END GO SELECT dbo.fn(newid()) 4-28 uniquedientifer 範例程式碼 4-28 中, 雖然可以將 NEWID() 系統函數產生的 GUID 值賦予 fn 使用 者自訂純量值函數當參數, 但若改以如下的語法呼叫 : SELECT dbo.fn(newsequentialid()) 則會出現如下的錯誤 : CREATE TABLE ALTER TABLE newsequentialid() 'uniqueidentifier' DEFAULT 另一點要注意的是 : 每個使用 NEWSEQUENTIALID() 所產生的 GUID 在該電腦 上都是唯一的 但只有當來源電腦具有網路卡時, 使用 NEWSEQUENTIALID() 所產生的 GUID 在多部電腦上才是唯一的 FUNCTION fn(@p UNIQUEIDENTIFIER) CREATE timestamp / rowversion timestamp / rowversion 資料類型與時間或日期無關 timestamp 值是二進位數字, 指出資料庫中修改資料記錄的相對先後順序, 跨資料庫則沒有更新的相對順序 請勿在索引鍵中使用 timestamp 資料行, 尤其是主索引鍵, 因為每次修改資料列 時,timestamp 值就會變更 簡單的測試程式碼如下 : CREATE TABLE t(pk INT IDENTITY,c1 rowversion,c2 INT DEFAULT 0) CREATE TABLE t2(pk INT IDENTITY,c1 rowversion,c2 INT DEFAULT 0) CREATE TABLE t3(pk INT IDENTITY,c1 int DEFAULT 0) INSERT t DEFAULT VALUES INSERT t2 DEFAULT VALUES 175

62 01 04 INSERT t3 DEFAULT VALUES SELECT (SELECT c1 FROM t) t_c1,(select c1 FROM t2) t2_c1,(select c1 FROM t3) t3_c1 -- t,t2 -- rowversion -- rowversion int=1 begin update t update t2 update t3 end SELECT (SELECT c1 FROM t) t_c1,(select c1 FROM t2) t2_c1,(select c1 FROM t3) t3_c 執行結果如圖 4-25 所示 : 4-25 rowversion 只要是嘗試更動某一筆記錄, 資料庫引擎會自動為該筆記錄的 rowversion 類型資 料行加 1 接續上一段範例程式碼, 更動 t 資料表類 PK 資料行為 1 的 c2 資料行 記錄 : INSERT t DEFAULT VALUES SELECT * FROM t UPDATE t SET c2=1 WHERE PK=1 SELECT * FROM t go drop table t,t2,t

63 SQL Server rowversion 從圖 4-26 記錄更動前後的 RowVersion 類型欄位比較, 可以看到因為 FD7 被 PK=2 的資料行用掉, 所以 PK=1 的記錄更動後, 該筆記錄的 c1 欄位由 FD5 變成 FD8 由於更動資料列時, 資料庫引擎會自動會為該列的 rowversion 類型資料行遞增值 所以, 在撰寫多人存取的前端程式, 於先前取得資料表內容值, 在使用者端離線運作, 而後要再更新相同記錄時, 可以根據存取前後 rowversion 資料行的值是否相等, 來判讀期間是否有其他人更動過該筆記錄 若要記錄資料表中發生資料修改的時間, 請使用 datetime2 或 smalldatetime 資料類型來記錄, 在修改時自行更新這些值 更動完某筆記錄後, 重新查詢 t 資料表, 執行結果如圖 4-26 所示 : 利用 Create Type 陳述式可以在目前資料庫中 ; 建立 別名資料類型 使用者定義類型 或 使用者定義資料表類型 別名資料型別的實作是以 SQL Server 原生系統類型為基礎 使用者自訂類型是利用.NET Framework Common Language Runtime (CLR) 中之組件的類別或結構來實作, 而使用者定義資料表類型, 則是以宣告資料表的方式定義類型, 其 語法定義如下 : CREATE TYPE [ schema_name. ] { FROM base_type [ ( precision [, scale ] ) ] [ NULL NOT NULL ] EXTERNAL NAME [. ] AS TABLE ( { < > < > } [ < > ] [,...n ] ) } [ ; ] 177

64 01 04 別名資料型別讓你使用可描述特定用途的名稱和格式, 來擴充 SQL Server 基底資料型別 ( 例如 varchar) 另外, 除了 的 Create Type 語法外, 也可以利用 sp_ addtype 系統預存程序來建立別名資料類型 例如, 下列陳述式會實作 birthday 使用者定義資料類型, 此資料類型是以 datetime 資料類型為基礎, 而且允許 NULL 值 : EXEC sp_addtype birthday, datetime, 'NULL' 或是透過 SQL Server Management Studio 的 物件總管, 展開 可程式性 類型 節點後, 滑鼠右鍵點選 使用者定義資料類型 節點, 選擇 新增使用者定義資料類型 選項, 如圖 4-27 所示 : 4-27 建立完使用者定義資料類型後, 便可以當作一般的資料類型使用 例如, 我們的 身分證格式是 char(10) 然後建立 CHECK 條件約束, 以便強制將社會安全碼的 格式儲存在資料表中, 如下列範例所示 : CREATE TYPE PersonID FROM CHAR(10) NOT NULL GO CREATE TABLE t(employeeid int PRIMARY KEY, ID PersonID CHECK (ID LIKE '[a-z][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' ) ) INSERT t VALUES(1,'A ') -- PersonID NULL INSERT t VALUES(2,NULL) DROP TYPE PersonID

65 SQL Server 2008 使用者定義資料表類型 是依照資料表結構規範的使用者定義型別 可利用該 類型宣告預存程序或函數的 資料表值參數, 或是在批次 預存程序或函數的 內文中, 宣告成該類型的資料表變數 若要建立使用者定義資料表類型, 需使用 CREATE TYPE 陳述式 為了確保在使用者定義資料表類型中的資料符合特定的需求, 可在使用者定義資料表類型中建立 唯一 或 主鍵 條件約束 首先, 透過 語法定義自訂資料表類型 : CREATE TYPE tptbl AS TABLE (ID INT IDENTITY PRIMARY KEY, C1 INT NOT NULL UNIQUE CHECK(C1>10),C2 AS C1*10) 在 SQL Server 2008 Management Studio 的 物件總管 視窗內, 可以觀察到使用者自訂的資料表類型, 如圖 4-28 所示 :

66 01 在此, 先用 簡單測試先前定義的自訂資料表類型 : tptbl VALUES(11) SELECT * DROP TYPE tptbl 4-32 使用者自訂資料表類型除了在批次中存取, 使用方式如同宣告資料表變數外, 也可以當作預存程序或函數的 資料表值參數 資料表值參數 是 SQL Server 2008 新增的參數類型 經由使用者定義的資料表類型所宣告 透過資料表值參數, 可以將多筆資料列傳送到 陳述式 預存程序或函數 換句話說, 在呼叫預存程序或函數時, 若要傳遞多筆紀錄或陣列, 可避免建立暫存資料表 符號分格字串或許多參數, 且減少在網路上來回的批次數量 其範例語法如下 : -- SELECT * INTO tbtbl WHERE 1=0 INSERT tbtbl(c1) VALUES(12) GO CREATE PROCEDURE tptbl READONLY AS SET NOCOUNT ON UPDATE tbtbl SET C1=s.C1 FROM tbtbl t s ON t.id=s.id GO -- tptbl VALUES(11) EXEC SELECT * FROM tbtbl