TVP vs JSON vs XML como parâmetros de entrada no SQL Server

Por que alguém escolheria XML ou JSON em vez de TVPs?

Somente se o uso de Parâmetros com Valor de Tabela (TVPs) não for uma opção devido a alguma limitação da estrutura.

O problema com a passagem em JSON ou XML de uma perspectiva de desempenho é que a maioria das pessoas escreverá (ou copiará/colará) alguma função divisora ​​de string terrível e correlacionará com a string dividida em sua consulta. O problema ainda existe ao usar a função STRING_SPLIT nativa do SQL Server também.

O otimizador do SQL Server não tem como analisar o conteúdo dessas strings , mesmo depois que o conteúdo é dividido, a menos que você as coloque em uma tabela #temp primeiro. Lembre-se de que as variáveis ​​@table não têm estatísticas geradas para elas, mesmo quando indexadas .

A equipe do EF Core tentou usar JSON no lugar de cláusulas IN , e os resultados no mundo real foram um desastre de desempenho. Eles foram avisados ​​com bastante antecedência, mas continuaram com isso mesmo assim. Recomendei penas de prisão para essa malfeitoria.

Mas voltando um pouco, TVPs são apoiados por variáveis ​​@table, e variáveis ​​@table têm problemas no SQL Server. Mesmo quando vários esquemas são empregados para obter cardinalidade em nível de tabela (quantas linhas há na tabela), a falta de informações estatísticas sobre quais dados estão na(s) coluna(s) frequentemente leva a problemas de desempenho.

De qualquer forma, normalmente você acabará despejando o conteúdo de um TVP em uma tabela #temp, pois elas tendem a resultar em melhores escolhas de planos.

Não consigo pensar em nenhuma distinção entre JSON e XML que seja particularmente relevante aqui. Então, é realmente TVPs vs dados de string estruturados (e usarei JSON para significar qualquer um deles, ou qualquer outro protocolo de dados de string estruturados, no restante da resposta).

O verdadeiro problema com os TVPs é sua inflexibilidade e não generalidade.

Eles exigem que um tipo de tabela definido pelo usuário seja declarado separadamente do cabeçalho do procedimento, o que torna um obstáculo para o chamador do procedimento procurar a forma e um obstáculo para o criador do procedimento alterar a forma (o que requer a retirada do procedimento, a alteração do tipo de tabela e, então, a reintegração do procedimento).

Eles são somente de entrada, então a técnica de usar JSON deve permanecer para parâmetros de saída (e, por sua vez, eles às vezes são necessários porque você não pode aninhar insert-execs).

E eles tendem a ser mais difíceis de usar quando os procedimentos são chamados de linguagens do lado do cliente (ou seja, fora do TSQL).

Muitas vezes, esses recursos aparentemente estranhos como TVPs cobrem algumas circunstâncias incomuns, então não me surpreenderia descobrir que TVPs têm melhor desempenho do que JSON com volumes de dados muito altos. Posso primeiro estar inclinado a usar uma tabela temporária, mas aí você potencialmente perde a reentrada.

Mas para "algumas colunas e algumas centenas de linhas", hoje em dia eu tentaria JSON primeiro, simplesmente porque a mesma técnica pode ser aplicada consistentemente a uma variedade maior de circunstâncias.