June 20, 2025 at 9:24 am
We are in the process of upgrading to SQL Server 2022 and would like to make use of the XML compression feature it offers, as we have a handful of tables that store considerable amounts of XML data.
I suspect the answer is "no", but is there a way to enable XML compression on an existing table without having to drop and recreate the entire table? It only appears to be available for the CREATE TABLE command, and not ALTER table.
CREATE TABLE dbo.JADTest (
MyXML xml NULL
)
WITH (XML_COMPRESSION = ON);
There also does not appear to be a way to check if XML compression is enabled in SQL Server Management Studio 20.2 (either via the Table Properties or Designer windows), and I cannot see any new column in sys.tables to store this setting, but perhaps I am looking in the wrong place?
Finally, does anyone have any production experience of using this feature? Have you noticed performance suffer as a result of any additional CPU overhead from having XML compression enabled? And how much reduction have you seen in storage space?
June 20, 2025 at 1:59 pm
hey i found it. the sys.partitions has two new-ish columns xml_compression and xml_compression_desc
the code below is something i use for scripting indexes, but if i was scripting a table, i would now need to take that into consideration.
SELECT
[sch].[schema_id] ,
[sch].[name] AS [schema_name] ,
[objz].[object_id] ,
[objz].[name] AS [object_name] ,
[sp].xml_compression ,
[sp].xml_compression_desc ,
[idxz].[index_id] ,
ISNULL([idxz].[name], '---') AS [index_name] ,
[partitions].[Rows] ,
[partitions].[SizeMB] ,
INDEXPROPERTY([objz].[object_id], [idxz].[name], 'IndexDepth') AS [IndexDepth] ,
[idxz].[type] ,
[idxz].[type_desc] ,
[idxz].[fill_factor] ,
[idxz].[is_unique] ,
[idxz].[is_primary_key] ,
[idxz].[is_unique_constraint] ,
ISNULL([Index_Columns].[index_columns_key], '---') AS [index_columns_key] ,
ISNULL([Index_Columns].[index_columns_include], '---') AS [index_columns_include] ,
ISNULL(' WHERE (' + [idxz].[filter_definition] + ')', '') AS [index_where_statement] ,
[sp].[data_compression_desc]
FROM [sys].[objects] [objz]
INNER JOIN [sys].[partitions] [sp] ON [objz].[object_id] = [sp].[object_id]
INNER JOIN [sys].[schemas] [sch] ON [objz].[schema_id] = [sch].[schema_id]
INNER JOIN [sys].[indexes] [idxz] ON [objz].[object_id] = [idxz].[object_id]
INNER JOIN ( SELECT [STATS].[object_id] ,
[STATS].[index_id] ,
SUM([STATS].[row_count]) AS [Rows] ,
CONVERT(NUMERIC(19, 3), CONVERT(NUMERIC(19, 3), SUM([STATS].[in_row_reserved_page_count] + [STATS].[lob_reserved_page_count]
+ [STATS].[row_overflow_reserved_page_count]))
/ CONVERT(NUMERIC(19, 3), 128)) AS [SizeMB]
FROM [sys].[dm_db_partition_stats] [STATS]
GROUP BY [STATS].[object_id] ,
[STATS].[index_id]
) AS [partitions] ON [idxz].[object_id] = [partitions].[object_id]
AND [idxz].[index_id] = [partitions].[index_id]
CROSS APPLY ( SELECT LEFT([Index_Columns].[index_columns_key], LEN([Index_Columns].[index_columns_key]) - 1) AS [index_columns_key] ,
LEFT([Index_Columns].[index_columns_include], LEN([Index_Columns].[index_columns_include]) - 1) AS [index_columns_include] ,
LEFT([Index_Columns].[index_columns_where], LEN([Index_Columns].[index_columns_where]) - 1) AS [index_columns_where]
FROM ( SELECT ( SELECT QUOTENAME([COLS].[name]) + CASE WHEN [IXCOLS].[is_descending_key] = 0 THEN ' asc'
ELSE ' desc'
END + ',' + ' '
FROM [sys].[index_columns] [IXCOLS]
INNER JOIN [sys].[columns] [COLS] ON [IXCOLS].[column_id] = [COLS].[column_id]
AND [IXCOLS].[object_id] = [COLS].[object_id]
WHERE [IXCOLS].[is_included_column] = 0
AND [idxz].[object_id] = [IXCOLS].[object_id]
AND [idxz].[index_id] = [IXCOLS].[index_id]
ORDER BY [IXCOLS].[key_ordinal]
FOR
XML PATH('')
) AS [index_columns_key] ,
( SELECT QUOTENAME([COLS].[name]) + ',' + ' '
FROM [sys].[index_columns] [IXCOLS]
INNER JOIN [sys].[columns] [COLS] ON [IXCOLS].[column_id] = [COLS].[column_id]
AND [IXCOLS].[object_id] = [COLS].[object_id]
WHERE [IXCOLS].[is_included_column] = 1
AND [idxz].[object_id] = [IXCOLS].[object_id]
AND [idxz].[index_id] = [IXCOLS].[index_id]
ORDER BY [IXCOLS].[index_column_id]
FOR
XML PATH('')
) AS [index_columns_include] ,
( SELECT QUOTENAME([COLS].[name]) + ',' + ' '
FROM [sys].[index_columns] [IXCOLS]
INNER JOIN [sys].[columns] [COLS] ON [IXCOLS].[column_id] = [COLS].[column_id]
AND [IXCOLS].[object_id] = [COLS].[object_id]
WHERE [IXCOLS].[is_included_column] = 1
AND [idxz].[object_id] = [IXCOLS].[object_id]
AND [idxz].[index_id] = [IXCOLS].[index_id]
ORDER BY [IXCOLS].[index_column_id]
FOR
XML PATH('')
) AS [index_columns_where]
) AS [Index_Columns]
) AS [Index_Columns]
WHERE 1 = 1
AND [objz].[name] = 'JADTest'
Lowell
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply