共用方式為


sp_tableoption (Transact-SQL)

適用於:SQL Server Azure SQL 資料庫 Azure SQL 受控執行個體

設定使用者定義數據表的選項值。 sp_tableoption可用來使用 varchar(max)、nvarchar(max)、varbinary(max)xmltextntextimage 或大型使用者定義類型數據行來控制數據表的數據列內行為。

重要

數據列功能中的文字將會在未來的 SQL Server 版本中移除。 若要儲存大量數據,建議您使用 varchar(max)nvarchar(max)varbinary(max) 數據類型。

Transact-SQL 語法慣例

語法

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 OFF0 (停用,預設值)時,它不會變更目前的行為,而且數據列中沒有 BLOB。

當指定的 和 @OptionValueON (enabled) 或來自 的247000整數值時,新的 textntextimage 字串會直接儲存在數據列中。 所有現有的 BLOB(二進位大型物件: textntextimage)數據都會在 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) 和更新版本。

當、 ON1TRUE,指定的數據表會啟用 vardecimal 儲存格式。 當、 OFF0FALSE,數據表未針對 vardecimal 儲存格式啟用。 只有在使用 sp_db_vardecimal_storage_format 啟用 vardecimal 儲存格式的資料庫時,才能啟用 vardecimal 記憶體格式。 在 SQL Server 2008 (10.0.x) 和更新版本中, vardecimal 記憶體格式已被取代。 請改用 ROW 壓縮。 如需詳細資訊,請參閱 [資料壓縮]。 0 是預設值。

[ @OptionValue = ] 'OptionValue'

指定是否啟用@OptionNameTRUE、 或 ) 或1已停用 (FALSEONOFF0)。 @OptionValue為 varchar(12),沒有預設值。 @OptionValue不區分大小寫。

對於數據列選項中的文字,有效的選項值為 0ONOFF或 來自 700024 的整數。 當 @OptionValueON,限制預設為 256 個字節。

傳回碼值

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

備註

sp_tableoption 只能用來設定使用者定義數據表的選項值。 若要顯示資料表屬性,請使用 OBJECTPROPERTY 或查詢 sys.tables

中的數據 sp_tableoption 列選項只能在包含文字數據行的數據表上啟用或停用。 如果數據表沒有文字數據行,SQL Server 就會引發錯誤。

啟用數據列中的文字選項時, @OptionValue 參數可讓使用者指定要儲存在 BLOB 資料列中的大小上限。 默認值為 256 個字節,但值的範圍可以從 24 到 7000 個字節。

如果適用下列條件,textntextimage 字串會儲存在數據列中:

  • 已啟用數據列中的文字。
  • 字串的長度比@OptionValue中指定的限制短。
  • 數據列有足夠的可用空間。

當 BLOB 字串儲存在數據列中時,讀取和寫入 textntext 或 image 字串的速度可以和讀取或寫入字元和二進位字串一樣快。 SQL Server 不需要存取不同的頁面,才能讀取或寫入 BLOB 字串。

如果 textntext 或 image 字串大於指定的限制或數據列中的可用空間,則指標會改為儲存在數據列中。 儘管如此,將數據列儲存 BLOB 字串的條件仍適用:數據列中必須有足夠的空間來保存指標。

儲存在數據表數據列中的 BLOB 字串和指標會與可變長度字串類似。 SQL Server 只會使用儲存字串或指標所需的位元元組數目。

第一次啟用數據列中的文字時,不會立即轉換現有的 BLOB 字串。 字串只有在更新時才會轉換。 同樣地,當數據列選項限制中的文字增加時, 數據列中已有的 textntextimage 字串不會轉換成遵守新限制,直到更新它們為止。

注意

停用數據列選項中的文字,或減少選項的限制需要轉換所有 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_tableoptionALTER 需要數據表的許可權。

範例

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';