轉移資料表LOB(Image/Text)資料到其他File Group

2023-11-22

143
0
[SQL]資料維護
2023-11-23

當資料表中有nvarchar(max)這種大型欄位時，該資料表資料存放就會有兩個部分，一個是Row Data一個是LOB，當單一筆資料進來時該大型欄位資料內容過大無法在單一Page存放時，就會被SQL放到LOB區域去。

而LOB在建立資料表時不指定存放區則預設會放到Primary這一個File Group中。但如果我們事後想調整更換時該怎麼做呢?

首先用下面語法建立一資料表後，預設狀況下該資料表的Row Data及LOB物件存放區都會是在Primary中

Create Table BigTable(
	Id int Identity Primary Key Clustered,
	msg nvarchar(max)
)On [Primary];
GO


SELECT
    OBJECT_NAME(p.object_id) AS table_name,
    p.index_id,
    p.rows,
    au.type_desc AS alloc_unit_type,
    au.used_pages,
    fg.name AS fg_name
FROM sys.partitions as p
JOIN sys.allocation_units AS au on p.hobt_id = au.container_id
JOIN sys.filegroups AS fg on fg.data_space_id = au.data_space_id
WHERE p.object_id = OBJECT_ID('BigTable')
ORDER BY table_name, index_id, alloc_unit_type;
GO

上述執行完如下圖所示

也可以用在建立Table時指定該Table的Row Data及LOB物件存放區，如下語法

Create Table BigTable2(
	Id int Identity Primary Key Clustered,
	msg nvarchar(max)
)On [SecGroup] TEXTIMAGE_ON [SecGroup] ;
GO


SELECT
    OBJECT_NAME(p.object_id) AS table_name,
    p.index_id,
    p.rows,
    au.type_desc AS alloc_unit_type,
    au.used_pages,
    fg.name AS fg_name
FROM sys.partitions as p
JOIN sys.allocation_units AS au on p.hobt_id = au.container_id
JOIN sys.filegroups AS fg on fg.data_space_id = au.data_space_id
WHERE p.object_id = OBJECT_ID('BigTable2')
ORDER BY table_name, index_id, alloc_unit_type;
GO

上述執行完如下圖所示

但一旦資料表建立完成後想要移動資料表至別的File Group時可以使用下面語法，但執行後會發現SQL只會將Row Data轉移至新指定的File Group，而LOB物件依然在舊的File Group。

CREATE UNIQUE CLUSTERED INDEX PK__BigTable
ON dbo.BigTable ( [ID] )
WITH (ONLINE = ON, DROP_EXISTING = ON)
ON [SecGroup];

面對上述問題可改用 PartitionFunction達到轉移LOB物件的目的，如下語法

--建立PARTITION FUNCTION，預計分割資料的值就填入一個比BigTable的ID更大值
--下面我先填入10000
CREATE PARTITION FUNCTION pf_MyTable(INT)
AS RANGE RIGHT FOR VALUES (10000);

--建立PARTITION SCHEME，小於10000會放到[SecGroup]，大於就放到[PRIMARY]
CREATE PARTITION SCHEME ps_MyTable
AS PARTITION pf_MyTable
TO ( [SecGroup], [PRIMARY] );

--套用PARTITION SCHEME來Rebuild BigTable的Clustered Index。
CREATE UNIQUE CLUSTERED INDEX [PK__BigTable]
ON [dbo].[BigTable]([ID])
WITH (ONLINE = ON, DROP_EXISTING = ON)
ON ps_MyTable ([ID]);


--完成上述步驟後BigTable的Row Data跟LOB應該都轉入[SecGroup]
--此時我們再重建一次BigTable的Clustered Index，這次不套用PARTITION SCHEME
CREATE UNIQUE CLUSTERED INDEX [PK__BigTable]
ON [dbo].[BigTable]([ID])
WITH (DROP_EXISTING = ON, ONLINE = ON)
ON [SecGroup];

--完成轉移後，刪除PARTITION SCHEME
DROP PARTITION SCHEME ps_MyTable;
DROP PARTITION FUNCTION pf_MyTable;

完成上述語法後就會如下圖所示，BigTable的Row Data跟LOB都轉入[SecGroup]