Temos grandes tabelas armazenando dados XML como varchar(MAX). Os dados são para fins de referência/históricos, não são consultados. Com base no que li, armazenar como tipo de dados XML em vez de VARCHAR(MAX) deve resultar em economia de espaço, mas meus testes mostram o contrário. Veja abaixo, onde o tamanho de t1_XML é menor que t1_NVARCHARMAX, mas maior que t1_VARCHARMAX.
set nocount on;
drop table t1_XML;
drop table t1_VARCHARMAX;
drop table t1_NVARCHARMAX;
create table t1_XML(col1 int identity primary key, col2 XML);
create table t1_VARCHARMAX(col1 int identity primary key, col2 varchar(max));
create table t1_NVARCHARMAX(col1 int identity primary key, col2 nvarchar(max));
go
declare @xml XML = '<root><element1>test</element1><element2>test</element2><element3>test</element3><element4>test</element4><element5>test</element5></root>'
, @x int = 1;
while @x <= 10000
begin
begin tran
insert into dbo.t1_XML (col2) values (@xml);
insert into dbo.t1_VARCHARMAX (col2) values (cast(@xml as varchar(max)));
insert into dbo.t1_NVARCHARMAX (col2) values (cast(@xml as varchar(max)));
commit tran
set @x += 1;
end
exec sp_spaceused 'dbo.t1_XML';
exec sp_spaceused 'dbo.t1_VARCHARMAX';
exec sp_spaceused 'dbo.t1_NVARCHARMAX';
Há duas coisas para saber sobre o
XML
tipo de dados que, juntas, explicam o que você está enfrentando:XML
dados é otimizado. Ou seja, em vez de repetir nomes de elementos e atributos (que normalmente são bastante repetidos e são uma grande parte do motivo pelo qual tantas pessoas, às vezes com razão, reclamam que os documentos XML são tão volumosos), um dicionário / lista de pesquisa é criado para armazene cada nome exclusivo uma vez, dado um ID numérico, e esse ID é usado para preencher a estrutura do documento. É por isso que o tipo deXML
dados geralmente é uma maneira melhor de armazenar documentos XML.XML
tipo de dados usa UTF-16 (Little Endian) para armazenar valores de string (nomes de elementos e atributos, bem como qualquer conteúdo de string real). Esse tipo de dados não usa compactação, portanto, as strings são essencialmente 2 ou 4 bytes por caractere, com a maioria dos caracteres sendo da variedade de 2 bytes.Observando o documento XML de teste específico que você está usando e o
VARCHAR
tipo de dados (1 a 2 bytes por caractere, geralmente a variedade de 1 byte), agora podemos explicar o que você está vendo como resultado de:root
,element1
, etc) é usado apenas uma vez, então a única economia de colocar os nomes na lista de pesquisa é cortar o tamanho exatamente pela metade. Mas, o tipo XML usa UTF-16 para que o tamanho de cada string seja o dobro, cancelando a economia de mover os nomes dos elementos para a lista de pesquisa. Neste ponto, se olharmos apenas para a estrutura do documento (ou seja, nomes de elementos), não haverá efetivamente nenhuma diferença entre oXML
tipo e aVARCHAR
versão.test
) ocupa o dobro do número de bytes: 8 bytes emXML
oposição a 4 bytes emVARCHAR
. Dado que existem 5 instâncias de "teste" por cada linha, são 20 bytes extras por linha para oXML
tipo. Em 10k linhas, são 200.000 bytes extras da diferença de 600.000 bytes. O resto é a sobrecarga interna doXML
tipo e a sobrecarga de página adicional do número adicional de páginas de dados necessárias para armazenar o mesmo número de linhas devido ao fato de cada linha ser um pouco maior.Para ilustrar melhor esse comportamento, considere as duas variações de dados XML a seguir: a primeira é exatamente o mesmo XML da pergunta e a segunda é quase a mesma, mas com todos os elementos com o mesmo nome. Na segunda versão, todos os nomes de elementos são "element1" para que tenham o mesmo comprimento de cada elemento na versão original. Isso resulta no
VARCHAR
comprimento dos dados sendo o mesmo em ambos os casos. Mas os nomes dos elementos sendo os mesmos na segunda versão permitem que as otimizações internas sejam mais perceptíveis.Resultados:
Dos documentos sobre tipo de dados e colunas XML (SQL Server)
binary_representation_size
é aproximadamentedata
+information about the containment hierarchy, document order, and element and attribute values
-insignificant white spaces, order of attributes, namespace prefixes, and XML declaration
Isso não é uma vitória clara se você não tiver prefixos de namespace e espaço em branco você está apenas armazenando mais dados.
Também é mencionado explicitamente nos documentos que você pode querer usar
nvarchar(max)
se estiver apenas armazenando e não se importando com os recursos ou validação,O SQL Server 2016 introduziu a função COMPRESS . Aplicando isso ao exemplo do @Solomon:
Mais economia de espaços é obtida:
É digno de nota que o espaço é economizado para nomes de elementos únicos e repetidos.