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) 資料類型。
語法
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) 或來自 的 24 7000 整數值時,新的 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
、 ON
OFF
或 或 0
)。 @OptionValue 為 Varchar(12) ,沒有預設值。 @OptionValue 不區分大小寫。
對於資料列選項中的文字,有效的選項值為 0
、 ON
、 OFF
或 來自 7000
24
的整數。 當 @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';
相關內容
意見反應
https://aka.ms/ContentUserFeedback。
即將登場:在 2024 年,我們將逐步淘汰 GitHub 問題作為內容的意見反應機制,並將它取代為新的意見反應系統。 如需詳細資訊,請參閱:提交並檢視相關的意見反應