sp_tableoption (Transact-SQL)
設定使用者自訂資料表的選項值。sp_tableoption 可供您利用 varchar(max)、nvarchar(max)、varbinary(max)、xml、text、ntext、image 或大型使用者自訂型別資料行來控制資料表的 in-row 行為。
.gif "重要注意事項") 重要事項 |
|---|
未來的 SQL Server 版本將移除 text in row 功能。若要儲存大數值資料，我們建議您使用 varchar(max)、nvarchar(max) 和 varbinary(max) 資料類型。 |
.gif "主題連結圖示")
語法
sp_tableoption [ @TableNamePattern = ] 'table'
, [ @OptionName = ] 'option_name'
,[ @OptionValue =] 'value'
引數
[ @TableNamePattern =] 'table'
這是使用者定義資料庫資料表的完整或非完整名稱。如果提供其中包括資料庫名稱的完整資料表名稱，資料庫名稱就必須是目前資料庫的名稱。您不能同時設定多份資料表的資料表選項。table 是 nvarchar(776)，沒有預設值。[ @OptionName = ] 'option_name'
這是資料表選項名稱。option_name 是 varchar(35)，沒有預設值 NULL。option_name 可以是下列其中一個值。值
說明
table lock on bulk load
當停用 (預設值) 時，使用者定義資料表的大量載入處理序會取得資料列鎖定。當啟用時，使用者定義資料表的大量載入處理序會取得大量更新鎖定。
insert row lock
SQL Server 2008 不支援這個項目。
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 和大型使用者定義型別 (UDT) 資料行是在資料列外儲存，用 16 位元組指標指向根。
0 = 只要記錄能夠容納值，便將 varchar(max)、nvarchar(max)、varbinary(max)、xml 和大型 UDT 值直接儲存在資料列中，最多 8000 個位元組。如果記錄無法容納值，便會將指標儲存在同資料列中，其餘部分會儲存在資料列外 (LOB 儲存空間中)。0 是預設值。
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 Enterprise、Developer 和 Evaluation 版本。在 SQL Server 2008 及更新版本中，所有的使用者資料庫都會啟用 vardecimal 儲存格式。在 SQL Server 2008 及更新版本中，vardecimal 儲存格式已被取代。請改用資料列壓縮。如需詳細資訊，請參閱＜建立壓縮資料表及索引＞。0 是預設值。
[ @OptionValue =] 'value'
這是指 option_name 是啟用 (TRUE、ON 或 1) 或停用 (FALSE、OFF 或 0)。value 是 varchar(12)，沒有預設值。value 不區分大小寫。text in row 選項的有效選項值是 0、ON、OFF，或 24 至 7000 的整數。當 value 是 ON 時，限制預設為 256 個位元組。
傳回碼值
0 (成功) 或錯誤號碼 (失敗)
備註
sp_tableoption 只能用來設定使用者定義資料表的選項值。若要顯示資料表屬性，請使用 OBJECTPROPERTY。
只有包含文字資料行的資料表能夠啟用或停用 sp_tableoption 中的 text in row 選項。如果資料表沒有文字資料行，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 字串數目而不同，程序可能會很長。在轉換程序中，會鎖定這份資料表。
資料表變數 (包括傳回資料表變數的函數) 會自動擁有啟用了預設內嵌限制 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 資料類型儲存為 vardecimalstorage format。
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';