sp_tableoption (Transact-SQL)

  • 發行項

適用於：SQL ServerAzure SQL DatabaseAzure SQL 受控執行個體

設定使用者定義資料表的選項值。 sp_tableoption可用來使用 Varchar（max）、Nvarchar（max）、Varbinary（max） 、 xml 、 text 、 Ntext 、 image 或大型使用者定義類型資料行來控制資料表的資料列內行為。

重要

資料列功能中的文字將會在未來的 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 當 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';

相關內容