sp_tableoption (Transact-SQL)

更新： 2006 年 12 月 12 日

設定使用者自訂資料表的選項值。sp_tableoption 可供您利用 varchar(max)、nvarchar(max)、varbinary(max)、xml、text、ntext 或 image 資料行來控制資料表的 in-row 行為。

重要事項：
未來的 SQL Server 版本將移除 text in row 功能。若要儲存大數值資料，我們建議您使用 varchar(max)、nvarchar(max) 和 varbinary(max) 資料類型。

主題連結圖示 Transact-SQL 語法慣例

語法

sp_tableoption [ @TableNamePattern = ] 'table' 
          , [ @OptionName = ] 'option_name' 
          , [ @OptionValue = ] 'value'

引數

[ @TableNamePattern = ] 'table'
這是使用者自訂資料庫表格的完整或非完整名稱。如果提供其中包括資料庫名稱的完整資料表名稱，資料庫名稱就必須是目前資料庫的名稱。您不能同時設定多份資料表的資料表選項。table_pattern 是 nvarchar(776)，沒有預設值。

[ @OptionName = ] 'option_name'
這是資料表選項名稱。option_name 是 varchar(35)，沒有預設值。option_name 可以是下列值之一。

值	描述
table lock on bulk load	當停用 (預設值) 時，使用者自訂資料表的大量載入處理序會取得資料列鎖定。當啟用時，使用者自訂資料表的大量載入處理序會取得大量更新鎖定。
insert row lock	SQL Server 2005 不支援這個項目。 SQL Server 的鎖定策略是可能升級到頁面或資料表鎖定的資料列鎖定。這個選項對於 SQL Server 的鎖定行為沒有影響，併入它的目的，只是為了與現有的指令碼和程序相容。
text in row	當它是 OFF 或 0 (停用，預設值) 時，它不會變更目前的行為，資料列中沒有 BLOB。當指定這個值且 @OptionValue 是 ON (已啟用) 時，24 至 7000 的整數值、新的 text、ntext 或 image 字串會直接儲存在資料列中。當更新 BLOB 值時，所有現有的 BLOB (二進位大型物件：text、ntext 或 image 資料) 都會改成 text in row 格式。如需詳細資訊，請參閱＜備註＞一節。
large value types out of row	1 = 資料表中的 varchar(max)、nvarchar(max)、varbinary(max) 和 xml 資料行是在資料列外儲存，用 16 位元組指標指向根。 0 = 只要值能夠符合記錄，便將 varchar(max)、nvarchar(max)、varbinary(max) 和 xml 值直接儲存在資料列中，最多 8000 位元組。如果值不符合記錄，便會將指標儲存在同資料列中，其餘部分會儲存在資料列外，在 LOB 儲存空間中。
Vardecimal 儲存格式	若為 TRUE、ON 或 1，指定的資料表會啟用為 Vardecimal 儲存格式。若為 FALSE、OFF 或 0，資料表則不會啟用為 Vardecimal 儲存格式。只有在使用 sp_db_vardecimal_storage_format 將資料庫啟用為 Vardecimal 儲存格式時，才能啟用 Vardecimal 儲存格式。如需有關 Vardecimal 儲存格式的詳細資訊，請參閱＜將十進位資料儲存成可變長度＞。這個選項需要使用 SQL Server 2005 Service Pack 2。Vardecimal 儲存格式只適用於 SQL Server 2005 Enterprise、Developer 和 Evaluation 版本。

[ @OptionValue = ] 'value'
這是指啟用 (true、on 或 1) 或停用 (false、off 或 0) 的 option_name。value 是 varchar(12)，沒有預設值。value 不區分大小寫。

text in row 選項的有效選項值是 0、on、off，或 24 至 7000 的整數。當 value 是 on 時，限制預設為 256 位元組。

傳回碼值

0 (成功) 或錯誤號碼 (失敗)

備註

sp_tableoption 只能用來設定使用者自訂資料表的選項值。若要顯示資料表屬性，請使用 OBJECTPROPERTY。

只有包含文字資料行的資料表能夠啟用或停用 text in row 中的 sp_tableoption 選項。如果資料表沒有文字資料行，SQL Server 會產生錯誤。

當啟用 text in row 選項時，@OptionValue 參數可讓使用者指定 BLOB 的資料列中所儲存的大小上限。預設值是 256 位元組，但值的範圍在 24 和 7000 位元組之間。

如果適合下列狀況，text、ntext 或 image 字串會儲存在資料列中：

啟用 text in row。
字串長度比 @OptionValue 所指定的限制短
資料列沒有足夠的可用空間。

當 BLOB 字串儲存在資料列中，text、ntext 或 image 字串的讀取和寫入速度與字元和二進位字串的讀取或寫入速度一樣快。SQL Server 不需要存取個別的頁面來讀取或寫入 BLOB 字串。

如果 text、ntext 或 image 字串大於指定的限制或資料列的可用空間，指標會改成儲存在資料列中。將 BLOB 字串儲存在資料列的條件仍適用，不過，資料列必須有足以保留指標的空間。

資料表資料列所儲存之 BLOB 字串和指標的處理方式類似於可變長度的字串。SQL Server 只會使用儲存字串或指標所需要的位元組數。

當第一次啟用 text in row 時，不會立即轉換現有的 BLOB 字串。只有在更新字串時，才會轉換字串。同樣地，當增加 text in row 選項限制時，在更新已在資料列中的 text、ntext 或 image 字串之前，不會轉換這些字串來遵照新的限制。

附註：
停用 text in row 選項或縮減選項限制，必須轉換所有 BLOB；因此，隨著必須轉換的 BLOB 字串數目而不同，程序可能會很長。在轉換程序中，會鎖定這份資料表。

table 變數，其中包括傳回 table 變數的函數，會自動擁有啟用了預設內嵌限制 256 的 text in row 選項。這個選項無法改變。

text in row 支援 TEXTPTR、WRITETEXT、UPDATETEXT 和 READTEXT 函數。使用者可以利用 SUBSTRING() 函數來讀取 BLOB 的各部分，但必須記住同資料列文字指標的持續時間和數目限制與其他文字指標不同。如需詳細資訊，請參閱＜管理 ntext、text 與 image 資料＞。

若要將資料表從 Vardecimal 儲存格式變更回十進位儲存格式，資料庫必須處於 SIMPLE 復原模式。變更復原模式會中斷備份所需的記錄鏈結，因此，請先建立完整的資料庫備份，再從資料表移除 Vardecimal 儲存格式。

權限

執行 sp_tableoption 需要資料表的 ALTER 權限。

範例

A. 儲存資料列外的 xml 資料

下列範例會在資料列外儲存 HumanResources.JobCandidate 資料表中的 xml 資料。

USE AdventureWorks;
GO
EXEC sp_tableoption 'HumanResources.JobCandidate', 'large value types out of row', 1;

B. 在資料表上啟用 Vardecimal 儲存格式

下列範例會修改 Production.WorkOrderRouting 資料表，將 decimal 資料類型儲存為 Vardecimal 儲存格式。

USE master;
GO
-- The database must be enabled for vardecimal storage format
-- before a table can be enabled for vardecimal storage format.
EXEC sp_db_vardecimal_storage_format 'AdventureWorks', 'ON';
GO
USE AdventureWorks;
GO
EXEC sp_tableoption 'Production.WorkOrderRouting', 
   'vardecimal storage format', 'ON';

請參閱

變更歷程記錄

版本	歷程記錄
2006 年 12 月 12 日	新增內容：新增 SQL Server 2005 Service Pack 2 提供的 Vardecimal 儲存格式選項。

Last updated on 2008-12-15