了解SQLServer中varchar(max)、nvarchar(max)和varbinary(max)打賞

在Microsoft SQLServer2005及以上的版本中,對于varchar(n)、nvarchar(n)和varbinary(n)有了max的擴展。可以使用如:varchar(max)、nvarchar(max)和varbinary(max)的大值數據類型來存儲最多2^30-1個字節的數據。

這幾個數據類型在行為上和較小的數據類型 varchar、nvarchar 和 varbinary 相同。
微軟的說法是用這個數據類型來代替之前的text、ntext 和 image 數據類型,它們之間的對應關系為:
varchar(max)-------text;
nvarchar(max)-----ntext;
varbinary(max)----image.
有了大值數據類型之后,在對大值數據操作的時候要比以前靈活的多了。比如:之前text是不能用‘like’的,有了varchar(max)之后就沒有這些問題了,因為varchar(max)在行為上和varchar(n)上相同,所以,可以用在varchar的都可以用在varchar(max)上。

以varchar為例,varchar支持8000字符,Text支持2GB字符,但是,替換函數在編寫出來的時候,就對TEXT數據類型不起作用。

Update TableName Set DocuMent=REPLACE(DocuMent,String1,String2) /*此行將報錯*/

同樣也對CHARINDEX或者SUBSTRING不起作用——或者至少是他們在超過8千個字符的情況下不起作用。更進一步地講,如果開發人員忘了處理TEXT或者IMAGE類型的本地變量,則實際上不支持任何操作。即使是簡單地更新一個文檔中的一個子字符串都需要用到很多的東西,以及難以使用的類似READTEXT和WRITETEXT的函數。

SQL Server 2005引入了一系列新的被稱為max的數據類型(或者說是參數類型)。這是varchar、nvarchar 和 varbinary類型的擴展,這幾種類型以前被限制在8000字節以下。max可以容納高達2GB的數據,與TEXT和IMAGE一樣——并且完全兼容所有的SQL Server內置的字符串函數。

用max關鍵字定義一個某種max類型的變量與替代字符串的尺寸(為varchar、nvarchar的時候)或者字節(為varbinary的時候)一樣簡單。

DECLARE @BigString varchar(max)
SET @BigString = 'abc'

雖然這個變量可以自由地操縱,并且可以傳遞給任何的內置的字符串函數,兼容性仍然不是沒有問題。首先,開發人員不能期望指定了尺寸的varchar和nvarchar變量在達到8000個字節的極限的時候可以自動“升級”到max版本。例如,如下的批處理:

DECLARE @String1 varchar(4001)
DECLARE @String2 varchar(4001)
SET @String1 = REPLICATE('1', 4001)
SET @String2 = REPLICATE('2', 4001)
SELECT LEN(@String1 + @String2)

4001+4001=8002,但是指定了尺寸的varchar的極限是8000。因為這兩個變量中沒有一個是max類型,LEN函數的結果就是8000,不是8002。在將兩個變量連接的時候,一種簡單的修正方法就是聲明這兩個變量中的一個為varchar(max)或者將其中的一個變量進行轉換。與一個規定了尺寸的類型進行連接的時候,優先考慮max類型,最終結果是max類型。所以,以下批處理的結果是8002,正如我們期望的一樣:

DECLARE @String1 varchar(4001)
DECLARE @String2 varchar(4001)
SET @String1 = REPLICATE('1', 4001)
SET @String2 = REPLICATE('2', 4001)
SELECT LEN(CONVERT(varchar(max), @String1) + @String2)

在傳遞給字符串函數的時候,開發人員意識到字符串的原意在默認情況下是規定了尺寸的,而不是max類型,也是至關重要的。例如,以下查詢的結果就很令人驚奇:

SELECT LEN(REPLICATE('1', 8002))

因為字符串‘1’是被作為規定了尺寸的varchar對待,而不是varchar(max),結果就是8000——但是在SQL Server 2005中,REPLICATE函數能夠產生高達2GB的字符串。要修正這個問題,可以將字符串轉換為varchar(max),這樣函數就會輸出同樣的類型了:

SELECT LEN(REPLICATE(CONVERT(varchar(max), '1'), 8002))

這個查詢現在將會返回期望的結果:8002。記住,總是要對采用了新特性編寫的代碼進行非常仔細的測試;隱藏的問題,例如上面描述的問題,可能并且毫無疑問地會在最壞的時間里造成災難性的后果。

除了變量之外,max類型也可以用于定義表的字段:

CREATE TABLE BigStrings (BigString varchar(max))

當用于表的時候,意識到max類型具有與TEXT和IMAGE類型稍微不同的行溢出行為是非常重要的。在SQL Server中,最大的行尺寸是8060字節。要超過這個限制,并且仍然管理每個都擁有高達2GB的存儲,用TEXT和IMAGE類型存儲的數據會被存儲引擎自動地斷行,在行里只留下一個16字節的指針。這意味著行的尺寸是減少了,這對性能有好處。然而,檢索大數據是昂貴的,因為它不是與同一行的數據存放在同一個位置。

max數據類型在默認情況下,使用TEXT/IMAGE溢出行為和正常尺寸的varchar/varbinary類型的行為的混合方式。如果一個字段的數據,加上表中所有其他字段的數據,總量少于8060字節,數據就存放在行內。如果數據超過8060字節,max字段的數據就會存放在行外。對于大字符串的表,以下的行將會與表中的其他數據存儲在同一個數據頁內:

INSERT BigStrings (BigString) VALUES (REPLICATE('1', 8000))
But the following row will result in an overflow: 
INSERT BigStrings (BigString) VALUES (REPLICATE(CONVERT(varchar(max), '1'), 100000))

你可以更改max數據類型在每個表的基礎上的默認的行為,它們會表現得和TEXT和IMAGE類型一樣。這是通過使用sp_tableoption 存儲過程中的“大數值類型在行外”選項實現的。為了修改大字符串表以將max類型的處理方式變得與TEXT和IMAGE數據類型的處理方式相同,可以使用如下的T-SQL:

EXEC sp_tableoption
  'BigStrings',   
  'large value types out of row',
  '1'

擴展:

既然是以max有這么多靈活性,一些數據庫設計師將會被引誘以下列的方式開始定義表:

CREATE TABLE Addresses(
  Name varchar(max),
  AddressLine1 varchar(max),
  AddressLine2 varchar(max),
  City varchar(max),
  State varchar(max),
  PostalCode varchar(max) 
  )

我建議你最好不要這樣做,一個企業應用中的數據模型既應該包含有具有實際限制的字段,還要給程序設計師提供有關字段尺寸的指導文檔。像這樣的表又該創建什么樣的文檔呢?

總知,max標記的數據類型為SQL Server 2005及之后版本處理大數據增加了很大部分的靈活性,但在使用中需要自己根據需求選擇字段類型。

了解SQLServer中varchar(max)、nvarchar(max)和varbinary(max)
文章《了解SQLServer中varchar(max)、nvarchar(max)和varbinary(max)》二維碼
  • 微信打賞
  • 支付寶打賞

已有13條評論

  1. 。。。。

    …..

    2013-07-21 15:41 回復
  2. 屌絲

    不知道博主交換鏈接否,謝謝

    2013-01-30 16:33 回復
  3. 屌絲必備,誰與爭鋒

    希望和博主換個鏈接,謝謝

    2013-01-23 16:47 回復
  4. 屌絲專用網

    希望做個鏈接謝謝博主

    2013-01-22 01:44 回復
  5. 土元養殖

    樓主懂得挺多,請指教

    2013-01-04 14:28 回復
    • 樸人博客

      抱歉,不做首頁了,請取消我的鏈接吧

      2012-12-28 20:07 回復
  6. PHP樂知博客

    好久不用SqlServer啦,自從專注PHP,一直在玩的就是Mysql

    2012-12-22 11:35 回復
  7. 潤初顏

    max標記的數據類型為SQL Server 2005及之后版本處理大數據增加了很大部分的靈活性,但在使用中需要自己根據需求選擇字段類型。

    2012-12-20 09:36 回復

(必填)

(必填)

(可選)

黑龙江22选5开奖