Matrizes no SQL Server 2005 – Parte I

Bom Dia Pessoal,

Uma das interações naturais de um DBA é com a equipe de desenvolvimento. Essa interação normalmente acontece para esclarescimento de dúvidas, boas práticas, etc. Durante várias dessas interações uma dúvida comum aparecia com uma razoável freqüência. A dúvida era sempre as mesmas, mas às vezes disfarçada em perguntas diferentes:

  • Posso utilizar vetores no T-SQL ?
  • Como fazer para declarar matrizes em Transact-SQL?
  • Como passar vários parâmetros de uma só vez para uma Stored Procedure ?

Toda linguagem dá suporte a tipos básicos de dados como strings, inteiros, booleanos, datas, etc e com a SQL não é diferente. Como as linguagens de programação são bem mais poderosas que a SQL e seus dialetos (T-SQL, PL/SQL, PgPL/SQL, etc) é normal trabalhar com tipos mais complexos como objetos, coleções, etc. As linguagens de programação possuem o Integer e o T-SQL tem o INT como seu correspondente. As linguagens de programação possuem o String e o T-SQL tem o VARCHAR como seu correspondente mais imediato. As linguagens de programação tem os Arrays, Lists e Collection e a procura por algo similar no T-SQL é mais do que comum.

Imagina-se então que deva haver algum tipo de dados para fazer isso, algum comando oculto ou simplesmente algum SELECT imbutindo um simples truque para simular um array. Bom, embora truques e soluções alternativas existam, o fato é que por padrão o SQL Server não possui um tipo de dados Array. A natureza da SQL é ser atômica e um Array representa algo multivalorado e sem dúvida proibitivo para o mundo relacional. Imagine uma tabela com uma coluna Array ? Isso seria admitir múltiplos valores para uma mesma coluna em um determinado registro e seria algo não atômico. Se fosse possível, estaríamos presenciando os chamados grupos de repetição violando inclusive a 1ª forma normal.

Em todo caso, a explicação não contradiz o fato de que trabalhar com Arrays facilitaria muito o trabalho dos desenvolvedores. Os valores de um Grid poderiam constituir um Array e serem disparados para o SQL Server de uma só vez. O SQL Server com um tipo "Array" poderia receber todos esses valores e automaticamente gravá-los em uma tabela sem desrespeitar a atomicidade, pois, seria um registro para cada item do Array. Isso iria evitar iterações pelas linhas do Grid. Certamente que existem alguns "Grids" que já fazem isso, mas não deixam de fazer repetidas iterações para gravar o resultado no SQL Server. Cada iteração representa uma transação à parte e um tráfego de rede à parte. Tudo isso incorre em uma certa queda de desempenho.

O T-SQL nos fornece desde alguns truques até algumas soluções mais elaboradas para trabalhar com Arrays ainda que não haja um tipo específico para isso. Demonstrarei um pequeno truque para simular um Array através do T-SQL.

— Cria uma tabela de Clientes
CREATE TABLE tblClientes (Nome VARCHAR(80), UF CHAR(2))

— Popula a tabela de Clientes
INSERT INTO tblClientes VALUES (‘Aline’,‘SP’)
INSERT INTO tblClientes VALUES (‘Lívia’,‘DF’)
INSERT INTO tblClientes VALUES (‘Jaqueline’,‘DF’)
INSERT INTO tblClientes VALUES (‘Cecília’,‘MG’)
INSERT INTO tblClientes VALUES (‘Marana’,‘RS’)
INSERT INTO tblClientes VALUES (‘Ronaldo’,‘DF’)
INSERT INTO tblClientes VALUES (‘Gilvan’,‘PR’)
INSERT INTO tblClientes VALUES (‘Dayane’,‘DF’)
INSERT INTO tblClientes VALUES (‘Victor’,‘RJ’)
INSERT INTO tblClientes VALUES (‘Vinicius’,‘PR’)
INSERT INTO tblClientes VALUES (‘Marana’,‘RS’)

Agora vamos supor que seja necessário informar um RANGE de estados para realizar uma pesquisa. Ex: SP, RJ e RS. Uma tentadora consulta seria a seguinte:

— Faz uma ineficaz tentativa de pesquisa
DECLARE @Estados VARCHAR(20)
SET @Estados = ‘SP,RJ,RS’

SELECT Nome FROM tblClientes WHERE UF IN (@Estados)

Embora alguns fiquem inconformados ou até sem entender porque o código acima não funciona, ou seja, não retorna os cliente de SP, RJ e RS esse é o comportamento correto. A explicação é óbvia. O campo UF é um campo VARCHAR e suporta strings. E se por um acaso existisse um estado chamado SP,RJ,RS ? Se a suposta pesquisa trouxesse os dados de SP, RJ e RS ficaríamos em um ambigüidade, pois a consulta não saberia se deveria retornar registro cujo estado seja SP, RJ ou RS ou os registros cujo o estado fosse o fictício SP,RJ,RS. É por isso que a consulta acima não funciona, pois, não existe nenhum registro que pertença ao estado SP,RJ,RS até porque o UF é um CHAR(2).

Em todo caso, essa é uma demanda por um tipo Array (ainda que disfarçada). Não se tem a intenção de pesquisar os registros cujo estado seja igual a SP,RJ,RS mas sim os registros cujo o estado seja São Paulo, Rio de Janeiro ou Rio Grande do Sul. O anseio é que a string desempenhe um papel de um Array cujo valores são delimitados por , e não de um simples string. Podemos então transformar essa string em um "Array" através de uma function que exponha abaixo:

CREATE FUNCTION [dbo].[fnStringToArray] (@String VARCHAR(1000), @Separador CHAR(1))
RETURNS @Array TABLE (Valor VARCHAR(500))
AS
BEGIN

    IF PATINDEX(‘%’ + @Separador + ‘%’, @String) = 0
        INSERT INTO @Array VALUES (LTRIM(RTRIM(@String)))

    ELSE
    BEGIN
        WHILE
PATINDEX(‘%’ + @Separador + ‘%’, @String) > 0
        BEGIN
            INSERT INTO
@Array VALUES
            (SUBSTRING(LTRIM(RTRIM(@String)), 1, PATINDEX(‘%’ + @Separador + ‘%’, LTRIM(RTRIM(@String))) – 1))

            SET @String = SUBSTRING(@String, PATINDEX(‘%’ + @Separador + ‘%’, @String) + 1, LEN(@String))
        END
        INSERT INTO
@Array VALUES (LTRIM(RTRIM(@String)))
    END

    RETURN
END

Essa função foi emprestada por Alexandre VM em uma das threads do MSDN (embora existam outras soluções parecidas postada por Rafael Krisller e Marcelo Colla em um outra Thread). Esse tipo de função consegue "quebrar" uma string especificando um separador. O resultado é retornado em um formato tabular. Ex:

SELECT Valor FROM [dbo].[fnStringToArray] (‘SP,RJ,RS’, ‘,’)

O resultado desse SELECT corresponde os estados SP, RJ e RS em um formato tabular que dessa forma representa um Array e pode ser utilizado para operações de consulta, inserção, atualização e exclusão. Através dessa função podemos resolver o problema de pesquisa anterior.

— Declara um Array para passagem dos parâmetros e pesquisa posterior
DECLARE @Estados VARCHAR(20)
SET @Estados = ‘SP,RJ,RS’

SELECT Nome, UF FROM tblClientes WHERE UF IN (
SELECT Valor FROM [dbo].[fnStringToArray] (@Estados, ‘,’))

Dessa forma, a string armazenada em @Estados é convertida para um conjunto em formato tabular (o que não deixa de certa forma de ser um Array) e pode ser comparada elemento a elemento e trazer o resultado esperado. Outra possibilidade seria aplicar uma SQL dinâmica.

— Declara um Array para passagem dos parâmetros e pesquisa posterior
DECLARE @Estados VARCHAR(20), @cmdSQL NVARCHAR(200)
SET @Estados = ‘SP,RJ,RS’
SET @Estados = CHAR(39) + REPLACE(@Estados,‘,’,CHAR(39) + ‘,’ + CHAR(39)) + CHAR(39)
SET @cmdSQL = ‘SELECT Nome, UF FROM tblClientes WHERE UF IN (‘ + @Estados + ‘)’
EXEC sp_executesql @cmdSQL

Embora tenha suas desvantagens natas, para esse problema a SQL dinâmica é mais eficiente que utilizar um Array e essa diferença é proporcional a quantidades de elementos, ou seja, quanto mais elementos houver melhor tende a ser o desempenho da SQL dinâmica. Em todo caso, a idéia era abordar a utilização de Arrays por isso o código T-SQL anterior.

O uso da função fnStringToArray demonstra como simular um Array, mas esse método possui algumas limitações. Observe que o Array além de ser unidimensional limita-se a uma única característica para cada elemento, ou seja, apenas o estado. Seria extremamente trabalhoso imbutir o estado e algum outro elemento. De fato, essa implementação simula um Array, mas com muitas limitações (embora seja um começo). Irei demonstrar uma forma mais poderosa de trabalhar com Arrays posteriormente.

[ ]s,

Gustavo

2 Respostas para “Matrizes no SQL Server 2005 – Parte I

  1. Pingback: Alex Souza

  2. Pingback: “Array” no SQL Server « Alex Souza

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s