Em qual tipo de dados armazenar dados XML: VARCHAR(MAX) ou XML

quais fatores devo considerar ao tentar decidir entre armazenar XML em uma xmlcoluna ou em uma varchar(MAX)coluna

Os fatores são:

1. O XMLtipo pode ser consultado/analisado por meio de expressões XQuery, inclusive podendo usar instrução FLWOR e iteração

2. Os dados em XMLvariáveis ​​e colunas podem ser modificados em linha usando expressões XQuery via XML DML .

3. XMLos dados são armazenados como UTF-16 LE (Little Endian), portanto, VARCHAR(MAX)seria uma má escolha, pois poderia resultar em perda de dados. Portanto, a verdadeira decisão deve estar entre XMLe NVARCHAR(MAX), dado que NCHAR/ NVARCHARtambém é UTF-16 LE.

4. XMLos dados podem ser validados em um arquivo XSD / XML SCHEMA COLLECTION. Nenhuma validação (além de garantir a boa formação) é feita se nenhuma Coleção de Esquema XML for especificada, mas esta opção não está disponível ao usar NVARCHAR(MAX).

5. Um grande benefício do tipo XML é que ele é armazenado em um formato altamente otimizado (não VARBINARY(MAX)conforme declarado na resposta de @Oleg ) que não armazena a representação de string exata que você vê, mas em vez disso possui um dicionário de nomes de elementos e atributos e refere-se a eles por seu ID. Também remove espaços em branco. Tente o seguinte:

   DECLARE @Test1 XML = N'<Test><TagName>1</TagName><TagName>2</TagName></Test>';
   
   DECLARE @String1 NVARCHAR(MAX) = CONVERT(NVARCHAR(MAX), @Test1);
   
   SELECT DATALENGTH(@Test1) AS [XmlBytes],
          LEN(@String1) AS [StringCharacters],
          DATALENGTH(@String1) AS [StringBytes];
   
   SET @Test1 = N'<Test><TagName>1</TagName><TagName>2</TagName><TagName>3</TagName>
   <TagName>4</TagName><TagName>5</TagName><TagName>6</TagName></Test>';
   
   SET @String1 = CONVERT(NVARCHAR(MAX), @Test1);
   
   SELECT DATALENGTH(@Test1) AS [XmlBytes],
          LEN(@String1) AS [StringCharacters],
          DATALENGTH(@String1) AS [StringBytes];
   

   Retorna:

   XmlBytes   StringCharacters   StringBytes
   56         53                 106
   
   XmlBytes   StringCharacters   StringBytes
   84         133                266
   

   Como você pode ver no exemplo de saída acima, adicionar quatro elementos (#s 3, 4, 5 e 6) adicionou 80 caracteres (portanto, 80 bytes se estiver usando VARCHAR) e 160 bytes à NVARCHARvariável. No entanto, ele adicionou apenas 28 bytes à variável XML, que é menos do que foi adicionado VARCHAR(apenas no caso de alguém argumentar a favor de VARCHARmais XMLporque XMLé UTF-16, que é [principalmente] byte duplo). Essa otimização pode economizar muito espaço e é motivo suficiente por si só para usar o tipo de XMLdados.

6. Os dados XML podem ser indexados por meio de índices XML especializados

Se as consultas no XML ocorrerem pelos recursos xml do servidor SQL, use o tipo XML para armazenar um xml para evitar a conversão

E

tenha em mente que o tipo XML pode ser armazenado um pouco mais devagar devido à validação xml, mas o tipo subjacente de XML é varbinary(max) comum

Um dos principais contribuintes para a diferença de armazenamento entre NVARCHAR(MAX) não discutido na resposta original é que NVARCHAR(MAX) não usa SCSU; usar pares de colunas NVARCHAR(N) + NVARCHAR(MAX) pode reduzir significativamente os requisitos de armazenamento, além da compactação de página mencionada abaixo. Consulte 'compressão' SCSU manual no SQL Server 2017 para NVARCHAR(MAX)

—

Uma consideração que não está presente nas outras respostas está relacionada à compactação de página. Com a compactação de página, existem alguns casos de uso muito específicos , nos quais o armazenamento de XML como NVARCHAR(MAX) pode ser benéfico, dependendo de alguns fatores específicos.

Este é um cenário especial e só deve ser considerado após análise de dados e uso. No cenário apresentado, isso parece improvável.

Considerações de quando alguém pode considerar NVARCHAR(MAX) sobre XML:

• A compactação de página é usada . O terceiro nível é a compactação de dicionário.
• Os fragmentos XML são pequenos (por exemplo, < 1/4 de caracteres de página) e contêm valores repetitivos suficientes e/ou possuem prefixos comuns que a compactação de página pode eliminar.
• Os discos custam $$$ e uma das principais metas de negócios é reduzir o uso do disco em detrimento de outros recursos . (Não se preocupe se o espaço em disco for amplo!)
• Os campos são salvos em Dados In-Row, pois os dados LOB não se beneficiam da Compactação de Página.
• Na verdade , a compactação de página permite que mais linhas caibam em uma única página. (Não se preocupe se linhas adicionais não puderem ser combinadas em uma única página!)
• As consultas XML nos campos são pouco frequentes, para evitar o custo de análise. Essa abordagem é mais eficaz para 'armazenamento a frio'.

Algumas desvantagens e contra-argumentos:

• Nenhuma validação nativa de XML no campo, digitado ou não.
• Índices XML não podem ser usados.
• Pague o custo da CPU em cada conversão do texto de volta para um tipo XML.
• A eficiência da compactação de página sobre codificação XML varia com base na quantidade de duplicação em valores (incluindo nós de texto) em registros em uma única página.
• A compactação de página não beneficiará grandes fragmentos XML movidos para LOB. Espera-se que a codificação XML resulte em menor uso de disco aqui. Pode-se dividir campos e usar COMPRESS para grandes dados XML, embora isso esteja adicionando outro nível de complexidade com preocupações adicionais.

Em um cenário muito específico em que tenho trabalhado, o uso de NVARCHAR(MAX) resulta em mais de 20% de redução no uso de disco quando aplicado a muitos fragmentos pequenos com alta duplicação. YMMV. Teste com dados e usos reais.