SQLServer2005：數據類型最大值

　　事情開始得很簡單MegaWare公司市場部門想要一個新的網站來發布文檔開發團隊覺得使用SQL Server 數據庫作為文檔存儲倉庫會使事情變得簡單Steve是MegaWare的數據庫管理員沒有看出這有什麼大問題;在數據庫中存儲文檔而不是使用文件系統意味著服務器需要多做一些工作但是它也會使得備份和管理容易得多數據庫與文件系統變得不同步也應該是不可能的
　　
　　市場部門想要存儲的許多文檔都超過了個字節那麼很明顯VARCHAR不是適合這項工作的數據類型作為替代TEXT數據類型被用來定義存放數據的字段因為每個TEXT都能容納GB的內容TEXT要存放市場部門的同事們扔進數據庫的最大的文件也是沒有問題的
　　
　　數月過去了市場用大量的無聊拷貝填滿了整個數據庫但是這還不是Steve真正關心的問題數據庫愉快地嗡嗡作響地運轉著每個人對項目的結果都很滿意
　　
　　直到公司的標語改變的那個重大的日子市場部的團隊認為MegaWare: Its really cool!要比原來的Its MegaWares Way or the Highway! 聽起來更好因為市場部團隊已經將原來的標語嵌入了倉庫中每個文檔的頁腳上現在Steve的工作就是更改所有這些文檔的頁腳
　　
　　沒有問題 Steve想打開SQL Server 查詢分析器工具執行了如下的TSQL批處理:
　　
　　UPDATE MarketingDocuments
　　
　　SET Document =
　　
　　REPLACE(Document
　　
　　Its MegaWares Way or the Highway!
　　
　　MegaWare: Its really cool!)
　　
　　當他看到出現的錯誤消息的時候Steve的輕松的微笑很快消失了替換函數的參數text數據類型無效
　　
　　替換函數在編寫出來的時候就對TEXT數據類型不起作用同樣也對CHARINDEX或者SUBSTRING不起作用——或者至少是他們在超過千個字符的情況下不起作用更進一步地講開發人員忘了處理TEXT或者IMAGE類型的本地變量;實際上不支持任何操作即使是簡單地更新一個文檔中的一個子字符串都需要用到晦澀的東西以及難以使用的類似READTEXT和WRITETEXT的函數而不是開發人員或者忙碌的數據庫管理員因為想要弄清如何正確使用而采用了不同類型的函數消耗了時間
　　
　　SQL Server的開發人員很幸運他們將會撥開烏雲見藍天SQL Server 引入了一系列新的被稱為MAX的數據類型這是VARCHARNVARCHAR和VARBINARY類型的擴展這幾種類型以前被限制在字節以下MAX可以容納高達GB的數據與TEXT和IMAGE一樣——並且完全兼容所有的SQL Server內置的字符串函數
　　
　　用MAX關鍵字定義一個某種MAX類型的變量與替代字符串的尺寸(為VARCHAR/NVARCHAR的時候)或者字節(為VARBINARY的時候)一樣簡單
　　
　　DECLARE @BigString VARCHAR(MAX)
　　
　　SET @BigString = abc
　　
　　雖然這個變量可以自由地操縱並且可以傳遞給任何的內置的字符串函數兼容性仍然不是沒有問題首先開發人員不能期望指定了尺寸的VARCHAR和VARBINARY變量在達到個字節的極限的時候可以自動升級到MAX版本例如如下的批處理:
　　
　　DECLARE @String VARCHAR()
　　
　　DECLARE @String VARCHAR()
　　
　　SET @String = REPLICATE( )
　　
　　SET @String = REPLICATE( )
　　
　　SELECT LEN(@String + @String)
　　
　　+=但是指定了尺寸的VARCHAR的極限是因為這兩個變量中沒有一個是MAX類型LEN函數的結果就是不是在將兩個變量連接的時候一種簡單的修正方法就是聲明這兩個變量中的一個為VARCHAR(MAX)或者將其中的一個變量進行轉換與一個規定了尺寸的類型進行連接的時候優先考慮MAX類型最終結果是MAX類型所以以下批處理的結果是正如我們期望的一樣:
　　
　　DECLARE @String VARCHAR()
　　
　　DECLARE @String VARCHAR()
　　
　　SET @String = REPLICATE( )
　　
　　SET @String = REPLICATE( )
　　
　　SELECT LEN(CONVERT(VARCHAR(MAX) @String) + @String)
　　
　　在傳遞給字符串函數的時候開發人員意識到字符串的原意在默認情況下是規定了尺寸的而不是MAX類型也是至關重要的例如以下查詢的結果就很令人驚奇:
　　
　　SELECT LEN(REPLICATE( ))
　　
　　因為字符串是被作為規定了尺寸的VARCHAR對待而不是VARCHAR(MAX)結果就是——但是在SQL Server 中REPLICATE函數能夠產生高達GB的字符串要修正這個問題可以將字符串轉換為VARCHAR(MAX)這樣函數就會輸出同樣的類型了:
　　
　　SELECT LEN(REPLICATE(CONVERT(VARCHAR(MAX) ) ))
　　
　　這個查詢現在將會返回期望的結果:記住總是要對采用了新特性編寫的代碼進行非常仔細的測試;隱藏的問題例如上面描述的問題可能並且毫無疑問地會在最壞的時間裡造成災難性的後果
　　
　　除了變量之外MAX類型也可以用於定義表的字段:
　　
　　CREATE TABLE BigStrings
　　
　　(
　　
　　BigString VARCHAR(MAX)
　　
　　)
　　
　　當用於表的時候意識到MAX類型具有與TEXT和IMAGE類型稍微不同的行溢出行為是非常重要的在SQL Server中最大的行尺寸是字節要超過這個限制並且仍然管理每個都擁有高達GB的存儲用TEXT和IMAGE類型存儲的數據會被存儲引擎自動地斷行在行裡只留下一個字節的指針這意味著行的尺寸是減少了這對性能有好處然而檢索大數據是昂貴的因為它不是與同一行的數據存放在同一個位置
　　
　　MAX數據類型在默認情況下使用TEXT/IMAGE溢出行為和正常尺寸的VARCHAR/VARBINARY類型的行為的混合方式如果一個字段的數據加上表中所有其他字段的數據總量少於字節數據就存放在行內如果數據超過字節MAX字段的數據就會存放在行外對於大字符串的表以下的行將會與表中的其他數據存儲在同一個數據頁內:
　　
　　INSERT BigStrings (BigString)
　　
　　VALUES (REPLICATE( ))
　　
　　But the following row will result in an overflow:
　　
　　INSERT BigStrings (BigString)
　　
　　VALUES (REPLICATE(CONVERT(VARCHAR(MAX) ) ))
　　
　　你可以更改MAX數據類型在每個表的基礎上的默認的行為它們會表現得和TEXT和IMAGE類型一樣這是通過使用sp_tableoption 存儲過程中的大數值類型在行外選項實現的為了修改大字符串表以將MAX類型的處理方式變得與TEXT和IMAGE數據類型的處理方式相同可以使用如下的TSQL:
　　
　　EXEC sp_tableoption
　　
　　BigStrings
　　
　　large value types out of row
　　
　　
　　
　　看看定義一個MAX數據類型有多容易與他們提供的靈活性一樣一些數據設計師將會被引誘以下列的方式開始定義表:
　　
　　CREATE TABLE Addresses
　　
　　(
　　
　　Name VARCHAR(MAX)
　　
　　AddressLine VARCHAR(MAX)
　　
　　AddressLine VARCHAR(MAX)
　　
　　City VARCHAR(MAX)
　　
　　State VARCHAR(MAX)
　　
　　PostalCode VARCHAR(MAX)
　　
　　)
　　
　　設計師要注意了:不要這樣做!一個企業中的數據模型既應該包含有具有實際限制的數據還要給用戶接口設計師有關字段尺寸的大致的指導像這樣的表又該創建什麼樣的用戶接口呢?
　　
　　除了數據整合和用戶接口含義之外如果設計師這樣不必要地使用這些類型還會帶來性能上的損害記住查詢優化器使用字段的尺寸作為判斷優化查詢計劃的眾多標准之一對於這個表優化器幾乎沒有任何選擇
　　
　　所以現在你知道了MAX數據類型為SQL Server 處理大數據增加了很大部分的靈活性但是MegaWare的那個不幸的數據庫管理員Steve會發生什麼變化?還在堅持使用SQL Server 他開始更新簡歷想象著如果更新表失敗了話他的工作也就失去了但是他也是幸運的——還有世界各地的MegaWare產品的擁護者——用GOOGLE的搜索可以很快地找到這篇文章《在TEXT字段中查找並替代》這篇文章告訴他如何正確的進行更新他花了整晚的時間來學習資料;再過幾個月之後TEXT和IMAGE數據類型就僅僅是一段不愉快的記憶了
From:http://tw.wingwit.com/Article/program/SQLServer/201311/22248.html