O Problema

Bem pessoal este é um problema que tive no trabalho no qual gostaria de compartilhar com vocês. Na nossa base de dados há um número que reconhece unicamente a carteira de um profissional, Porém depois de um tempo reparamos que devido a integração que temos com outros estados, o número passou a obter letras. Pois bem, assim tivemos que pensar em uma solução de integração para que no momento de uma consulta entre os estados fosse retornado somente o número, sem as letras. Logo pensamos em realizar a criação de uma função escalar, assim ele trataria o valor retorno resolvendo assim o problema.

Porém sabemos que o valor de uma função pode “quebrar” o QO fazendo com que ele realize um CLUSTERED INDEX SCAN ou TABLE SCAN na tabela, ou seja isso não é nada bom. Dito e feito, depois de passarmos a sequinte cláusula = ON fn_SonenteNumerosInsc(Insc) = fn_SonenteNumerosInsc(@Insc) dentro da procedure de integração, tivemos o que já era esperado. Como o QO não sabe qual será o retorno da consulta ele realizou um SCAN em um tabela de 1 milhão de registros.

Sendo assim, vimos que essa não foi a melhor opção. Depois de pensarmos um pouco, foi sugerido a criação de uma Coluna Computada em cima do Número da Inscrição, sendo assim todas as informações do campo Insc seriam tratadas por uma função e assim teriamos os valores do campo com somente números, e o valor passado na procedure de integração poderia ser tratado antes de comparado.

Sabemos que é possível realizar a criação de uma coluna computada, porém a mesma tem que ser Deterministica, um valor determístico é o que possui sempre o mesmo valor de retorno.

Sendo assim vemos no BOL que : ”An index cannot be created on a computed column if thecomputed_column_expression references any nondeterministic functions.”

Maiores informações sobre Funções Determinísticas e Não-Deterministicas –http://msdn.microsoft.com/en-us/library/aa214775(v=sql.80).aspx

Para exemplificar criarei uma tabela contendo um campo INSC , logo depois criarei um função para tratamento desse campo.

CREATE TABLE Produtos

(

ID INT IDENTITY(1,1) NOT NULL,

Nome VARCHAR(50) NOT NULL,

Insc VARCHAR(50) NOT NULL,

Quantidade INT NOT NULL,

DataRegistro DATETIME2 NOT NULL DEFAULT GETDATE()

)

INSERT INTO Produtos (Nome, Insc, Quantidade)

VALUES (‘Pera’,’12461APF12MAN’,3),

(‘Abacaxi’,’174861ANBN17′,5),

(‘Melancia’,‘AON148459ANFA’,10),

(‘Limao’,‘ASDN39340DFS1′,2),

(‘Uva’,’174NDODN194NGFO1′,5),

(‘Jubuticaba’,’174NDODN864NGFO1′,5),

(‘Mamao’,’16FDSGNH1W4213DFGD’,30),

(‘Abacate’,‘HKL17F1JH89ASBD1213′,2)

CREATE FUNCTION [dbo].[Fnc_SomenteNumerosInsc] (

@TEXTO CHAR(15)

)

RETURNS CHAR(15)

BEGIN

DECLARE @RESULTADO VARCHAR(8000)
SET @RESULTADO = ”

;WITH SPLIT AS

(

SELECT 1 AS ID, SUBSTRING(@TEXTO, 1, 1) AS LETRA

UNION ALL

SELECT ID + 1, SUBSTRING(@TEXTO, ID + 1, 1)

FROM SPLIT

WHERE ID < LEN(@TEXTO)

)

SELECT @RESULTADO += LETRA

FROM SPLIT

WHERE LETRA LIKE ‘[0-9]‘

OPTION(MAXRECURSION 0)

RETURN @RESULTADO;

END

Essa função foi feito pelo SQLFROMHELL – http://sqlfromhell.wordpress.com/

Ela pega um valor de entrada e retira todos os caracteres retornando assim somente o número.

O próximo passo foi criar a coluna computada, porém nesse caso temos duas opções à utilizar PERSISTED ou não. Quando utilizamos essa opção dizemos para o SQL Server que queremos PERSISTIR a informação, inserir fisicamente os dados tradados dentro da tabela, sendo assim com os dados tratados e gravados dentro tabela é possível criar um índice dentro do campo, opção na qual não é possível sem a opção PERSISTED.

Porém quando fomos criar a coluna com a opção PERSISTED tivemos o seguinte retorno:

ALTER TABLE dbo.Produtos

ADD InscNumr AS dbo.Fnc_SomenteNumerosInsc(Insc) PERSISTED

“

Msg 4936, Level 16, State 1, Line 1
Computed column ‘InscNumr’ in table ‘Produtos’ cannot be persisted because the column is non-deterministic.

”

Vemos claramente que a coluna não pode ser criada pelo retorno da função que não deterministica, isso porque o valor de retorno da função não será sempre a mesma.

A Solução

Analisando, vemos no BOL que: “ Whether a user-defined function is deterministic or nondeterministic depends on how the function is coded. User-defined functions are deterministic if:

* The function is schema-bound.
* All built-in or user-defined functions called by the user-defined function are deterministic.
* The body of the function references no database objects outside the scope of the function. For example, a deterministic function cannot reference tables other than table variables that are local to the function.
* The function does not call any extended stored procedures.”

Sendo assim, na primeira premisa vemos que a função terá que possuir – SCHEMA BOUND. Se tentarmos alterar a opção adicionando a cláusula –

WITH SCHEMABINDING podemos ajudar a Engine do SQL Server a identificar que essa função poderá ser determinística.

ALTER FUNCTION [dbo].[Fnc_SomenteNumerosInsc] (

@TEXTO CHAR(15)

)

RETURNS CHAR(15)

WITH SCHEMABINDING

BEGIN

DECLARE @RESULTADO VARCHAR(8000)

SET @RESULTADO = ”

;WITH SPLIT AS

(