Persisted Column + Função Escalar

Posted on março 26, 2012

1


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 the computed_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

     (

          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

Logo após adicionar essa opção, tentamos realizar novamente a criação da coluna e vemos que.

 

ALTER TABLE dbo.Produtos

     ADD InscNumr AS dbo.Fnc_SomenteNumerosInsc(Insc) PERSISTED

“Command(s) completed successfully. “

Com a coluna agora PERSISTIDA em banco de dados é possivel realizar a criação de um índice.

 

CREATE NONCLUSTERED INDEX idxNCL_Produtos_InscNumr

ON dbo.Produtos (InscNumr)

“Command(s) completed successfully. “

Agora é possível realizar um SEEK em cima do campo InscNumr

 

SELECT InscNumr

FROM dbo.Produtos

WHERE InscNumr IN (‘1246112’,‘17486117’,‘148459’,‘393401’)

 

 

image

Posted in: SQL Server 2008