Funções determinísticas e não determinísticas

  • Artigo

Aplica-se a:SQL ServerBanco de Dados SQL do AzureInstância Gerenciada de SQL do Azure

As funções determinísticas sempre retornam o mesmo resultado sempre que são chamadas com um conjunto específico de valores de entrada e com o mesmo estado do banco de dados. Funções não determinísticas podem retornar resultados diferentes cada vez que são chamadas com um conjunto específico de valores de entrada, mesmo que o estado do banco de dados que elas acessam permaneça o mesmo. Por exemplo, a função AVG sempre retorna o mesmo resultado, dadas as qualificações declaradas acima, mas a função GETDATE, que retorna o valor datetime atual, sempre retorna um resultado diferente.

Há várias propriedades de funções definidas pelo usuário que determinam a capacidade do Mecanismo de Banco de Dados do SQL Server de indexar os resultados da função, tanto por meio de índices em colunas computadas que chamam a função como por meio de exibições indexadas que referenciam a função. O determinismo de uma função é uma dessas propriedades. Por exemplo, um índice clusterizado não poderá ser criado em uma exibição se ela fizer referência a funções não determinísticas. Para obter mais informações sobre propriedades de funções, inclusive determinismo, consulte Funções definidas pelo usuário.

As funções determinísticas devem estar associadas ao esquema. Use a cláusula SCHEMABINDING ao criar uma função determinística.

Este artigo identifica o determinismo das funções internas do sistema e o efeito sobre a propriedade determinística das funções definidas pelo usuário quando elas contêm uma chamada para procedimentos armazenados estendidos.

Determinando se uma função é determinística

Você pode verificar se uma função é determinística consultando a propriedade do objeto is_deterministic para essa função. O exemplo abaixo determina se a função Sales.CalculateSalesTax é determinística.

SELECT OBJECTPROPERTY(OBJECT_ID('Sales.CalculateSalesTax'), 'IsDeterministic');

Determinismo de função interna

Você não pode influenciar o determinismo de nenhuma função interna. Cada função interna é determinística ou não determinística com base em como ela é implementada pelo SQL Server. Por exemplo, a especificação de uma cláusula ORDER BY em uma consulta não altera o determinismo de uma função usada nessa consulta.

Todas as funções internas de cadeia de caracteres são determinísticas, exceto FORMAT. Para obter uma lista dessas funções, consulte Funções de sequência de caracteres (Transact-SQL).

As seguintes funções internas pertencentes a categorias de funções internas que não sejam de cadeia de caracteres sempre são determinísticas.

  • ABS
  • ACOS
  • ASIN
  • ATAN
  • ATN2
  • CEILING
  • COALESCE
  • COS
  • COT
  • DATALENGTH
  • DATEADD
  • DATEDIFF
  • DAY
  • DEGREES
  • EXP
  • FLOOR
  • ISNULL
  • ISNUMERIC
  • LOG
  • LOG10
  • MONTH
  • NULLIF
  • POWER
  • RADIANS
  • ROUND
  • SIGN
  • SIN
  • SQRT
  • SQUARE
  • TAN
  • YEAR

As funções a seguir nem sempre são determinísticas, mas podem ser usadas em exibições indexadas ou índices em colunas computadas quando são especificadas de forma determinística.

Função Comentários
todas as funções de agregação Todas as funções agregadas são determinísticas, a menos que sejam especificadas com as cláusulas OVER e ORDER BY. Para obter uma lista dessas funções, consulte Funções de agregação (Transact-SQL).
CAST Determinística a menos que usado com datetime, smalldatetimeou sql_variant.
CONVERT Determinística, a menos que um destas condições exista:

O tipo é sql_variant.

O tipo de destino é sql_variant e seu tipo de origem é não determinístico.

O tipo de origem ou de destino é datetime ou smalldatetime, o outro tipo de origem ou destino é uma cadeia de caracteres, e um estilo não determinístico é especificado. Para ser determinístico, o parâmetro de estilo deve ser uma constante. Além disso, estilos menores ou iguais a 100 são não determinísticos, com exceção dos estilos 20 e 21. Estilos maiores que 100 são determinísticos, com exceção dos estilos 106, 107, 109 e 113.
CHECKSUM Determinística, exceto para CHECKSUM(*).
ISDATE Determinístico somente se usado com a função CONVERT, o parâmetro de estilo CONVERT é especificado e o estilo não é igual a 0, 100, 9 ou 109.
RAND RAND só é determinística quando um parâmetro seed é especificado.

Todas as funções estatísticas de configuração, cursor, metadados, segurança e sistema são não determinísticas. Você pode ver uma lista dessas funções.

As funções internas a seguir de outras categorias nunca são determinísticas.

  • @@CONNECTIONS
  • @@CPU_BUSY
  • @@DBTS
  • @@IDLE
  • @@IO_BUSY
  • @@MAX_CONNECTIONS
  • @@PACKET_ERRORS
  • @@PACK_RECEIVED
  • @@PACK_SENT
  • @@TIMETICKS
  • @@TOTAL_ERRORS
  • @@TOTAL_READ
  • @@TOTAL_WRITE
  • AT TIME ZONE
  • CUME_DIST
  • CURRENT_TIMESTAMP
  • DENSE_RANK
  • FIRST_VALUE
  • FORMAT
  • GETDATE
  • GETUTCDATE
  • GET_TRANSMISSION_STATUS
  • LAG
  • LAST_VALUE
  • LEAD
  • MIN_ACTIVE_ROWVERSION
  • NEWID
  • NEWSEQUENTIALID
  • NEXT VALUE FOR
  • NTILE
  • PARSENAME
  • PERCENTILE_CONT
  • PERCENTILE_DISC
  • PERCENT_RANK
  • RAND
  • RANK
  • ROW_NUMBER
  • TEXTPTR

Chamar procedimentos armazenados estendidos a partir de funções

Funções que chamam procedimentos armazenados estendidos são não determinísticas, pois esses procedimentos podem causar efeitos colaterais no banco de dados. Efeitos colaterais são alterações em um estado global do banco de dados, como a atualização de uma tabela ou de um recurso externo, como um arquivo ou a rede; por exemplo, a modificação de um arquivo ou o envio de uma mensagem de email. Não confie no retorno de um conjunto de resultados consistente ao executar um procedimento armazenado estendido a partir de uma função definida pelo usuário. Funções definidas pelo usuário que criam efeitos colaterais no banco de dados não são recomendadas.

Quando chamado de dentro de uma função, o procedimento armazenado estendido não pode retornar conjuntos de resultados para o cliente. Todas as APIs Open Data Services que retornam conjuntos de resultados para o cliente têm um código de retorno FAIL.

O procedimento armazenado estendido pode se conectar novamente ao SQL Server. No entanto, o procedimento não pode participar da mesma transação que a função original que invocou o procedimento armazenado estendido.

Semelhante a invocações de um procedimento armazenado ou em lotes, o procedimento armazenado estendido será executado no contexto da conta de segurança do Windows na qual o SQL Server está em execução. O proprietário do procedimento armazenado estendido deve considerar as permissões do contexto de segurança, ao conceder permissões a outros usuários para executar o procedimento.

Confira também