同資料列資料

發行項
12/15/2008

更新： 2006 年 12 月 12 日

在 SQL Server 2005 中，小型至中型大數值類型 (varchar(max)、nvarchar(max)、varbinary(max) 和 xml) 及大型物件 (LOB) 資料類型 (text、ntext 和 image) 可以儲存在資料列中。此行為是由 sp_tableoption 系統預存程序中的兩個選項來控制的：適用於大數值類型的 large value types out of row 選項，以及適用於大型物件類型的 text in row 選項。具有下列情況的資料表，最適合使用這兩個選項：通常會將以上任一資料類型的資料值讀取或寫入在一個單位中，而且參照該資料表的大部分陳述式都會參照這種資料。依據使用情況或工作負載特性，以同資料列方式來儲存資料可能會沒有幫助。

重要事項：
在未來的 SQL Server 版本中，將會移除 text in row 選項。請避免在新的開發工作中使用此選項，並請計劃修改目前使用 text in row 的應用程式。建議您使用 varchar(max)、nvarchar(max) 或 varbinary(max) 資料類型來儲存大型資料。若要控制這些資料類型的同資料列和資料列外行為，請使用 large value types out of row 選項。

在未來的 SQL Server 版本中，將會移除 text in row 選項。請避免在新的開發工作中使用此選項，並請計劃修改目前使用 text in row 的應用程式。建議您使用 varchar(max)、nvarchar(max) 或 varbinary(max) 資料類型來儲存大型資料。若要控制這些資料類型的同資料列和資料列外行為，請使用 large value types out of row 選項。

除非將 text in row 選項設為 ON，或是設為特定的同資料列限制，否則 text、ntext 或 image 字串都是儲存在資料列之外的大型字元或二進位字串 (可高達 2GB)。資料列僅包含 16 位元組的文字指標，指向由內部指標所組成之樹狀結構的根節點。這些指標會對應用來儲存字串片段的分頁。如需有關儲存 text、ntext 或 image 字串的詳細資訊，請參閱＜使用 text 與 image 資料＞。

您可以針對包含 LOB 資料類型資料行的資料表來設定 text in row 選項。您也可以指定 text in row 選項限制，從 24 到 7,000 位元組。

同樣地，除非 large value types out of row 選項設為 ON，否則 varchar(max)、nvarchar(max)、varbinary(max) 和 xml 資料行都會儲存在資料列中 (在可能的情況下)。若是如此，「Database Engine」會盡量配合特定值，否則就會將其發送到非資料列。若 large value types out of row 設為 ON，就會以非資料列方式來儲存值，而且只有 16 位元組的文字指標會儲存在記錄中。

附註：
當 large value types out of row 設為 OFF，大數值資料類型的最大同資料列儲存體設定為 8,000 位元組。與 text in row 選項不同的是，您不能為資料表中的資料行指定同資料列限制。

若是將資料表設定為直接將大數值類型，或大型物件資料類型儲存在資料列，當有下列情況存在，實際資料行值會變成同資料列：

字串長度比指定的 text、ntext 及 image 資料行限制來得短。
資料列中有足夠的可用空間來容納字串。

將大數值類型或大型物件資料類型資料行值儲存在資料列時，「Database Engine」不需要存取另一個分頁或分頁集來讀取或寫入字元或二進位字串。這樣可以讓讀取及寫入同資料列字串的速度，幾乎與讀取或寫入限制大小 varchar、nvarchar 或 varbinary 字串一樣快。同樣地，若是以非資料列來儲存值，「Database Engine」就會引發額外的分頁讀取或寫入動作。

就大型物件資料類型而言，如果字串長度大於 text in row 選項限制或資料列中的可用空間，原本儲存在指標樹狀結構根節點的指標集，就會儲存在資料列中。若有下列任一情況存在，指標就會儲存在資料列中：

儲存指標所需的空間數量比指定的 text in row 選項限制來得少。
資料列擁有足夠的可用空間來容納指標。

將指標從根節點移到資料列本身後，「Database Engine」就不必使用根節點。這可省略讀取或寫入字串時的分頁存取。這樣可以提高執行效能。

當您使用根節點時，會將其儲存成 LOB 分頁中的其中一種字串片段，而且最多可容納五個內部指標。「Database Engine」要求資料列內有 72 個位元組的空間，才能儲存同資料列字串的五個指標。當 text in row 選項為 ON，或 large value types out of row 選項為 OFF，如果資料列中的空間不足，無法容納指標，「Database Engine」可能需要配置 8-K 分頁來容納它們。如果該值的資料長度超過 40,200 位元組，則需要五個以上的同資料列指標，同時只有 24 位元組會儲存在主要資料列中，並且會在 LOB 儲存體空間中配置額外的資料頁。

當大型字串儲存於資料列時，它們的儲存方式和可變長度的字串類似。「Database Engine」會以大小遞減的順序來將資料行排序，並將值發送到非資料列，直到剩餘的資料行符合資料頁 (8K)。

啟用及停用 large value types out of row 選項

您可以用下列方式來使用 sp_tableoption，以啟用資料表的 large value types out of row 選項：

sp_tableoption N'MyTable', 'large value types out of row', 'ON'

若您指定 OFF，varchar(max)、nvarchar(max)、varbinary(max) 及 xml 資料行的同資料列限制就會設為 8,000 位元組。只有 16 位元組根指標會以同資料列儲存，且該值會儲存在 LOB 儲存體空間中。若資料表中的大部分陳述式不會參考大數值類型資料行，建議將此選項設為 ON。以資料列外方式來儲存這些資料行，就表示每一頁可容納的資料列更多，因此可降低掃描資料表時所需的 I/O 作業數量。

若將此選項的值設為 OFF，很多字串最後可能都會儲存在資料列本身，因此可能會降低每一頁可容納的資料列數量。若要參考資料表的大部分陳述式都不會存取 varchar(max)、nvarchar(max)、varbinary(max) 及 xml 資料行，減少分頁中的資料列將會增加處理查詢時必須讀取的分頁數。若是減少每頁的資料列，當最佳化工具找不到可用的索引時，將會增加必須掃描的分頁數量。

您也可以使用 sp_tableoption 來停用 out-of-row 選項：

sp_tableoption N'MyTable', 'large value types out of row', 'OFF'

當 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。

啟用與停用 text in row 選項

您可以用下列方式來使用 sp_tableoption，以啟用資料表的 text in row 選項：

sp_tableoption N'MyTable', 'text in row', 'ON'

您也可以針對可儲存在資料列中的 text、ntext 及 image 字串，選擇性地指定最大限制，從 24 到 7,000 個位元組：

sp_tableoption N'MyTable', 'text in row', '1000'

若您指定 ON，而非特定的限制，限制的預設值將為 256 個位元組。此預設值可讓您在使用 text in row 選項的過程中，獲得最大的效能優勢。雖然您通常不會將該值設為低於 72，但您也不能將該值設得太高。這對於大部分陳述式都不會參考 text、ntext 及 image 資料行的資料表，或是含有多重 text、ntext 及 image 資料行的資料表，特別適用。

若您設定較大的 text in row 限制，並且有許多字串都儲存於資料列本身，您可以大幅減少每個分頁所容納的資料列數目。如果會參考資料表的大部分陳述式都不會存取 text、ntext 或 image 資料行，則減少分頁中的資料列將會增加處理查詢時必須讀取的分頁。減少每個分頁的資料列數將會增加索引的大小，以及當最佳化工具找不到可用的索引時所需掃描的分頁。text in row 限制的預設值 256 足以確保小字串及根文字指標可儲存於資料列中，但對於減少每頁的資料列數來影響效能卻不夠大。

針對具有資料表資料類型的變數，以及由使用者自訂函數 (用以傳回資料表) 所傳回的資料表，text in row 選項會自動設為 256。此設定無法變更。

您也可以將 sp_tableoption 的選項值指定成 OFF 或 0，以停用該選項：

sp_tableoption N'MyTable', 'text in row', 'OFF'

若要檢查特定資料表之 text in row 選項的值，請查詢 sys.tables 目錄檢視的 text_in_row_limit 資料行。如果資料表沒有啟用 text in row，則此資料行為 0；如果已設定同資料列限制，則該值大於 0。

使用 text in row 選項的效果

text in row 選項具有下列效果：

在您啟用 text in row 選項之後，可以使用 TEXTPTR、READTEXT、UPDATETEXT 或 WRITETEXT 陳述式，來讀取或修改儲存於資料表中任何 text、ntext 或 image 值的各部分。在 SELECT 陳述式中，您可讀取完整的 text、ntext 或 image 字串，或使用 SUBSTRING 函數來讀取部分的字串。參考該資料表的所有 INSERT 或 UPDATE 陳述式，都必須指定完整的字串，而且不能只修改部分的 text、ntext 或 image 字串。
第一次啟用 text in row 選項時，現有的 text、ntext 或 image 字串不會立即轉換成同資料列字串。字串只會在接下來更新時才轉換成同資料列字串。在啟用 text in row 選項之後才插入的任何 text、ntext 或 image 字串，將會插入成同資料列字串。
停用 text in row 選項會是很耗時的記錄作業。資料表會被鎖定，而且所有的同資料列 text、ntext 和 image 字串都會轉換成一般的 text、ntext 和 image 字串。指令必須執行的時間長度，以及修改的資料量，將取決於有多少 text、ntext 和 image 字串必須從同資料列字串轉換成一般的字串。
text in row 選項並不會影響 SQL Native Client OLE DB 提供者或 SQL Native Client ODBC 驅動程式的作業，它反而會加快 text、ntext 和 image 資料的存取速度。
在 text in row 選項啟用之後，即無法在資料表上使用 DB-Library Text 與 Image 函數 (例如，dbreadtext 和 dbwritetext)。

請參閱

變更歷程記錄

版本	歷程記錄
2006 年 12 月 12 日	新增內容：在＜啟用與停用 text in row 選項＞一節中新增段落，說明若要檢查特定資料表的 large value types out of row 選項值，請查詢 sys.tables 目錄檢視的 large_value_types_out_of_row 資料行。