sp_tableoption (Transact-SQL)
適用於：
SQL Server Azure SQL 資料庫 Azure SQL 受控執行個體
設定使用者定義數據表的選項值。 sp_tableoption可用來使用 varchar（max）、nvarchar（max）、varbinary（max）、xml、text、ntext、image 或大型使用者定義類型數據行來控制數據表的數據列內行為。
重要
數據列功能中的文字將會在未來的 SQL Server 版本中移除。 若要儲存大量數據，建議您使用 varchar（max）、 nvarchar（max） 和 varbinary（max） 數據類型。
語法
sp_tableoption
[ @TableNamePattern = ] N'TableNamePattern'
, [ @OptionName = ] 'OptionName'
, [ @OptionValue = ] 'OptionValue'
[ ; ]
引數
[ @TableNamePattern = ] N'TableNamePattern'
使用者定義資料庫數據表的限定或非限定名稱。 @TableNamePattern為 nvarchar（776），沒有預設值。 如果提供完整數據表名稱，包括資料庫名稱，資料庫名稱必須是目前資料庫的名稱。 無法同時設定多個數據表的數據表選項。
[ @OptionName = ] 'OptionName'
數據表選項名稱。 @OptionName為 varchar（35），而且可以是下列其中一個值。
| 值 | Description |
|---|---|
table lock on bulk load |
停用時（預設值），會導致使用者定義數據表上的大量載入程式取得數據列鎖定。 啟用時，它會導致使用者定義數據表上的大量載入進程取得大量更新鎖定。 |
insert row lock |
不再受支援。 此選項不會影響 SQL Server 的鎖定行為，而且只包含現有腳本和程式的相容性。 |
text in row |
當 OFF 或 0 （停用，預設值）時，它不會變更目前的行為，而且數據列中沒有 BLOB。當指定的 和 @OptionValue 為 ON （enabled） 或來自 的247000整數值時，新的 text、ntext 或 image 字串會直接儲存在數據列中。 所有現有的 BLOB（二進位大型物件： text、 ntext 或 image）數據都會在 BLOB 值更新時變更為數據列格式的文字。 如需詳細資訊，請參閱＜備註＞。 |
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 值會直接儲存在數據列中，最多限制為 8,000 個字節，只要值可以放入記錄中即可。 如果值不符合記錄，指標會儲存在數據列中，而其餘則儲存在LOB儲存空間中的數據列外。 0 是預設值。 大型使用者定義類型 （UDT） 適用於：SQL Server 2008 （10.0.x） 和更新版本。 TEXTIMAGE_ON使用 CREATE TABLE 的選項來指定大型資料類型儲存的位置。 |
| vardecimal 記憶體格式 | 適用於：SQL Server 2008 (10.0.x) 和更新版本。 當、 ON或 1時TRUE，指定的數據表會啟用 vardecimal 儲存格式。 當、 OFF或 0時FALSE，數據表未針對 vardecimal 儲存格式啟用。 只有在使用 sp_db_vardecimal_storage_format 啟用 vardecimal 儲存格式的資料庫時，才能啟用 vardecimal 記憶體格式。 在 SQL Server 2008 （10.0.x） 和更新版本中， vardecimal 記憶體格式已被取代。 請改用 ROW 壓縮。 如需詳細資訊，請參閱 [資料壓縮]。 0 是預設值。 |
[ @OptionValue = ] 'OptionValue'
指定是否啟用@OptionName （TRUE、 或 ） 或1已停用 （FALSE、 ONOFF或 0）。 @OptionValue為 varchar（12），沒有預設值。 @OptionValue不區分大小寫。
對於數據列選項中的文字，有效的選項值為 0、ON、 OFF或 來自 700024 的整數。 當 @OptionValue 為 ON時，限制預設為 256 個字節。
傳回碼值
0 （成功）或錯誤號碼（失敗）。
備註
sp_tableoption 只能用來設定使用者定義數據表的選項值。 若要顯示資料表屬性，請使用 OBJECTPROPERTY 或查詢 sys.tables。
中的數據 sp_tableoption 列選項只能在包含文字數據行的數據表上啟用或停用。 如果數據表沒有文字數據行，SQL Server 就會引發錯誤。
啟用數據列中的文字選項時， @OptionValue 參數可讓使用者指定要儲存在 BLOB 資料列中的大小上限。 默認值為 256 個字節，但值的範圍可以從 24 到 7000 個字節。
如果適用下列條件，text、 ntext 或 image 字串會儲存在數據列中：
- 已啟用數據列中的文字。
- 字串的長度比@OptionValue中指定的限制短。
- 數據列有足夠的可用空間。
當 BLOB 字串儲存在數據列中時，讀取和寫入 text、ntext 或 image 字串的速度可以和讀取或寫入字元和二進位字串一樣快。 SQL Server 不需要存取不同的頁面，才能讀取或寫入 BLOB 字串。
如果 text、ntext 或 image 字串大於指定的限制或數據列中的可用空間，則指標會改為儲存在數據列中。 儘管如此，將數據列儲存 BLOB 字串的條件仍適用：數據列中必須有足夠的空間來保存指標。
儲存在數據表數據列中的 BLOB 字串和指標會與可變長度字串類似。 SQL Server 只會使用儲存字串或指標所需的位元元組數目。
第一次啟用數據列中的文字時，不會立即轉換現有的 BLOB 字串。 字串只有在更新時才會轉換。 同樣地，當數據列選項限制中的文字增加時， 數據列中已有的 text、 ntext 或 image 字串不會轉換成遵守新限制，直到更新它們為止。
注意
停用數據列選項中的文字，或減少選項的限制需要轉換所有 BLOB;因此，程式可能很長，視必須轉換的 BLOB 字串數目而定。 轉換程式期間會鎖定數據表。
數據表變數，包括傳回數據表變數的函式，會自動啟用數據列中的文字選項，預設內嵌限制為256。 無法變更此選項。
數據列中的文字選項支援 TEXTPTR、WRITETEXT、UPDATETEXT 和 READTEXT 函式。 使用者可以使用 SUBSTRING（） 函式讀取 BLOB 的一部分，但必須記住，數據列文字指標與其他文字指標有不同的持續時間和數目限制。
若要將數據表從 vardecimal 記憶體格式變更回一般十進位 儲存格式，資料庫必須位於 SIMPLE 恢復模式中。 變更恢復模式會中斷記錄鏈結以供備份之用，因此您應該在從數據表中移除 vardecimal 記憶體格式之後，建立完整資料庫備份。
如果您要將現有的 LOB 數據類型數據行（text、ntext 或 image） 轉換成小型到中型大型實值類型 （varchar（max）、nvarchar（max）或 varbinary（max）），而且大部分語句不會參考您環境中的大型實值類型數據行，請考慮將large_value_types_out_of_row變更為1取得最佳效能。 當large_value_types_out_of_row選項值變更時，不會立即轉換現有的 varchar（max）、nvarchar（max）、varbinary（max）和 xml 值。 字串的儲存會在稍後更新時變更。 插入數據表的任何新值會根據生效的數據表選項來儲存。 為了立即產生結果，請建立數據的複本，然後在變更 large_value_types_out_of_row 設定之後重新填入數據表，或將每個小型到中型大型實值類型數據行更新為本身，讓字元串的儲存隨著數據表選項生效而變更。 請考慮在更新或重新擴展之後重建數據表上的索引，以壓縮數據表。
權限
若要執行 sp_tableoption ， ALTER 需要數據表的許可權。
範例
A. 將數據列的 XML 資料儲存在外
下列範例會指定數據表中的 HumanResources.JobCandidate xml 資料儲存在資料列外。
USE AdventureWorks2022;
GO
EXEC sp_tableoption 'HumanResources.JobCandidate', 'large value types out of row', 1;
B. 在數據表上啟用 vardecimal 儲存格式
下列範例會修改數據表，Production.WorkOrderRouting以 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 'AdventureWorks2022', 'ON';
GO
USE AdventureWorks2022;
GO
EXEC sp_tableoption 'Production.WorkOrderRouting',
'vardecimal storage format', 'ON';