De acordo com este blog , os parâmetros para uma função ou um procedimento armazenado são essencialmente passados por valor se não forem OUTPUTparâmetros e tratados essencialmente como uma versão mais segura de passagem por referência se forem OUTPUTparâmetros.
A princípio, pensei que o objetivo de forçar a declaração do TVP READONLYera sinalizar claramente aos desenvolvedores que o TVP não pode ser usado como OUTPUTparâmetro, mas deve haver mais coisas acontecendo porque não podemos declarar não-TVP como READONLY. Por exemplo, o seguinte falha:
create procedure [dbo].[test]
@a int readonly
as
select @a
Msg 346, Nível 15, Estado 1, Teste de procedimento
O parâmetro "@a" não pode ser declarado READONLY, pois não é um parâmetro com valor de tabela.
- Como as estatísticas não são armazenadas no TVP, qual é a lógica por trás da prevenção de operações DML?
- Está relacionado a não querer que o TVP seja
OUTPUTparâmetro por algum motivo?
A explicação parece estar ligada a uma combinação de: a) um detalhe do blog vinculado que não foi mencionado nesta pergunta, b) a pragmática dos TVPs se encaixando em como os parâmetros sempre foram passados para dentro e para fora, c) e a natureza de variáveis de tabela.
O detalhe ausente contido na postagem do blog vinculado é exatamente como as variáveis são passadas para dentro e para fora dos Procedimentos e Funções Armazenadas (que se relaciona com a frase na Questão de "uma versão mais segura de passagem por referência se forem parâmetros OUTPUT") :
A parte 1 deste quebra-cabeça é que os parâmetros são sempre passados "por valor". E é somente quando o parâmetro é marcado como
OUTPUTe o procedimento armazenado é concluído com êxito que o valor atual é realmente enviado de volta. SeOUTPUTos valores fossem realmente passados "por referência", então o ponteiro para o local na memória dessa variável seria o que foi passado, não o valor em si. E se você passar o ponteiro (ou seja, endereço de memória), todas as alterações feitas serão refletidas imediatamente, mesmo que a próxima linha do procedimento armazenado cause um erro e aborte a execução.Para resumir a Parte 1: os valores das variáveis são sempre copiados; eles não são referenciados por seu endereço de memória.
Com a Parte 1 em mente, uma política de sempre copiar valores de variáveis pode levar a problemas de recursos quando a variável transmitida for muito grande. Não testei para ver como os tipos de blob são tratados (
VARCHAR(MAX),NVARCHAR(MAX),VARBINARY(MAX),XML, e aqueles que não devem mais ser usados:TEXT,NTEXTeIMAGE), mas é seguro dizer que qualquer tabela de dados passada pode ser bem grande. Faria sentido para aqueles que desenvolvem o recurso TVP desejar uma verdadeira capacidade de "passar por referência" para evitar que seu novo recurso legal destrua um número saudável de sistemas (ou seja, desejar uma abordagem mais escalável). Como você pode ver na documentação , foi isso que eles fizeram:Além disso, essa preocupação com o gerenciamento de memória não era um conceito novo, pois pode ser encontrada na API SQLCLR que foi introduzida no SQL Server 2005 (os TVPs foram introduzidos no SQL Server 2008). Ao passar
NVARCHAReVARBINARYdados para o código SQLCLR (ou seja, parâmetros de entrada nos métodos .NET em um Assembly SQLCLR), você tem a opção de ir com a abordagem "por valor" usando umSqlStringouSqlBinaryrespectivamente, ou você pode ir com a abordagem "por referência "abordagem usando umSqlCharsouSqlBytesrespectivamente. Os tiposSqlCharseSqlBytespermitem o streaming completo dos dados no .NET CLR, de forma que você possa obter pequenos blocos de valores grandes, em vez de copiar um valor inteiro de 200 MB (até 2 GB, certo).Para resumir a Parte 2: os TVPs, por sua própria natureza, teriam uma propensão a consumir muita memória (e, portanto, deteriorar o desempenho) se permanecessem dentro do modelo "sempre copiar o valor". Portanto, os TVPs fazem uma verdadeira "passagem por referência".
A parte final é por que a Parte 2 é importante: por que passar um TVP verdadeiramente "por referência" em vez de fazer uma cópia dele mudaria alguma coisa. E isso é respondido pelo objetivo de design que é a base da Parte 1: Procedimentos armazenados que não são concluídos com êxito não devem alterar, de forma alguma, nenhum dos parâmetros de entrada, sejam eles marcados como
OUTPUTou não. Permitir operações DML teria um efeito imediato no valor do TVP conforme ele existe no contexto de chamada (já que passar por referência significa que você está alterando o que foi passado, não uma cópia do que foi passado).Agora, alguém, em algum lugar, provavelmente está falando com seu monitor dizendo: "Bem, apenas construa uma instalação automágica para reverter quaisquer alterações feitas nos parâmetros TVP se alguma tiver sido passada para o procedimento armazenado. Duh. Problema resolvido." Não tão rápido. É aqui que entra a natureza das Variáveis de Tabela: as alterações feitas nas Variáveis de Tabela não são vinculadas por Transações! Portanto, não há como reverter as alterações. E, na verdade, esse é um truque usado para salvar as informações geradas em uma transação, caso haja a necessidade de uma reversão :-).
Para resumir a Parte 3: Table-Variables não permitem "desfazer" alterações feitas a elas no caso de um erro que faça com que o Stored Procedure seja abortado. E isso viola o objetivo do projeto de ter parâmetros que nunca refletem a execução parcial (Parte 1).
Portanto: a
READONLYpalavra-chave é necessária para evitar operações DML em TVPs, pois são variáveis de tabela que são realmente passadas "por referência" e, portanto, qualquer modificação nelas seria refletida imediatamente, mesmo que o procedimento armazenado encontrasse um erro e não houvesse outra maneira de evitar isso.Além disso, parâmetros de outros tipos de dados não podem ser usados
READONLYporque já são cópias do que foi passado e, portanto, não protegeriam nada que já não estivesse protegido. Isso, e a maneira como os parâmetros dos outros tipos de dados funcionam, foi planejado para ser leitura-gravação, portanto, provavelmente seria ainda mais trabalhoso alterar essa API para incluir agora um conceito somente leitura.Resposta do Community Wiki gerada a partir de um comentário sobre a pergunta de Martin Smith
Existe um item Connect ativo (enviado por Erland Sommarskog) para isso:
Relaxe a restrição de que os parâmetros da tabela devem ser somente leitura quando os SPs chamam uns aos outros
A única resposta da Microsoft até agora diz (ênfase adicionada):