Facilitando a criação de queries complexas no SQL Server

Criar certas queries para um banco de dados pode ser um trabalho bastante intrincado. É o caso de consultas cujos filtros (cláusula WHERE) são baseados em cálculos independentes, bem como atualizações que são vinculadas ao resultado de alguma das funções de agregação (como SUM, MIN ou MAX). O cenário fica ainda pior se a manutenção em questão envolver mais de uma tabela, necessitando acessar outros dados através de joins.

Em geral, estas situações podem ser resolvidas mais facilmente usando-se cursores ou outra técnica de programação similar. Para quem usa o SQL Server, no entanto, a solução pode ser bem simples. Desde a versão 2005, esse gerenciador de Banco de Dados dispõe de um recurso chamado CTE - Common Table Expression ou "Expressão de Tabela Comum".

Basicamente, a CTE constrói uma tabela temporária com os registros resultantes de um comando SELECT especificado. A tabela temporária é, então, disponibilizada para o próximo comando SQL executado na mesma conexão com o banco de dados. Assim, esse segundo comando pode executar qualquer query (SELECTs, UPDATEs, DELETEs, INSERTs) referenciando a CTE como se este fosse uma tabela ordinária do banco de dados. Após esse comando ser executado, o SQL Server remove a tabela temporária criada pela CTE.

A sintaxe para declararmos uma CTE consiste na palavra chave WITH seguida pelo nome da tabela temporária, uma lista opcional para indicar os nomes que os campos assumirão, a palavra chave AS e, entre parênteses, a query propriamente dita. O quadro a seguir retrata a sintaxe descrita acima:

WITH nome-da-expressão [ ( nome-coluna-1 [ ,...nome-coluna-n ] ) ] AS
(
texto-do-SELECT
)

Para exemplificar na prática o uso desse recurso, considere o seguinte cenário: você possui uma tabela para registrar todas as alterações feitas num determinado documento em seu banco de dados. Cada alteração é cadastrada com um novo valor sequencial num campo que é parte da chave dos registros.

Em um determinado momento, você decide fazer uma faxina nos registros, limpando o banco de dados para manter apenas a última alteração registrada de cada documento. Como cada documento possui uma quantidade diferente de alterações registradas, não há um número de sequência que você possa usar como corte. A solução, então, é calcular a maior sequência de cada documento e remover os registros cujas sequências sejam menores que o valor calculado em cada documento diferente.

Veja abaixo uma definição simplificada da tabela e um conjunto hipotético dos registros contidos nela:

NUMERO_DOC	TIPO_DOC	SEQUENCIA	USUARIO	XML_ALTERACOES
1	NOTA FISCAL	0	GUSTAVO	0x00012010322032...
1	NOTA FISCAL	1	FULANO	0x00012A10962032...
1	NOTA FISCAL	2	GUSTAVO	0x012018650322027...
2	NOTA FISCAL	0	FULANO	0x012018650322027...
3	NOTA FISCAL	0	FULANO	0x012018650322027...
3	NOTA FISCAL	1	FULANO	0x012018650322027...
1	DUPLICATA CR	0	MANOELA	0x012018650322027...
1	DUPLICATA CR	1	MANOELA	0x012014603546520...
1	PEDIDO	0	JOSE	0x012014603520989...

Primeiro, vamos montar a CTE. Devemos obter um registro para cada documento diferente, calculando junto o maior número de sequência de alteração existente para cada um deles. Conseguimos isso facilmente com uma agregação simples:

WITH sel_docs AS (
SELECT numero_doc, tipo_doc, MAX(sequencia) AS sequencia
FROM docs
GROUP BY numero_doc, tipo_doc
)

Ao declarar esta CTE, omiti o nome das colunas na tabela resultante. Desse modo, os nomes adotados pelo SQL Server correspondem aos nomes na query interna. A coluna contendo o MAX não tem um nome por padrão, razão pela qual usei a cláusla AS para forçar num nome na própria query. No geral, é uma boa prática nomear explicitamente as colunas para evitar conflitos e tornar mais clara a operação como um todo.

A tabela abaixo mostra o conteúdo da nossa CTE, à qual dei o nome de sel_docs:

NUMERO_DOC	TIPO_DOC	SEQUENCIA
1	NOTA FISCAL	2
2	NOTA FISCAL	0
3	NOTA FISCAL	1
1	DUPLICATA CR	1
1	PEDIDO	0

Agora, a montagem do comando seguinte da conexão SQL considera o quadro acima como uma tabela do nosso banco de dados. Podemos, então, escrever o comando DELETE proposto no exemplo como um JOIN entre a CTE e a tabela original (DOCS):

DELETE docs FROM sel_docs
WHERE docs.numero_doc = sel_docs.numero_doc
AND docs.tipo_doc = sel_docs.tipo_doc
AND docs.sequencia < sel_docs.sequencia

O comando SELECT que compõe uma CTE pode ter cláusula WHERE e dispor de JOINS, se necessário. Poderíamos, portanto, ter restringido o tipo de documento na CTE do nosso exemplo ou até mesmo usar o resultado de algum cálculo envolvendo trechos do texto no campo XML_ALTERACOES para construir uma restrição mais elaborada.

Em tempo: o CTE está previsto na padronização do SQL ANSI-99; por isso, outros gerenciadores de banco de dados também implementaram esse recurso. O Oracle, por exemplo, o introduziu em sua versão 9i release 2, com sintaxe praticamente idêntica à apresentada neste post. Mais detalhes podem ser encontrados no artigo WITH Clause : Subquery Factoring.

Mais Informações
Common Table Expression, Usando expressões de tabelas comuns (CTE)

Avaliando a performance de queries no SQL Server

Muito da aceitação de um programa de computador por seus usuários pode ser atribuído à praticidade de sua interface gráfica e até mesmo a aparência que ela tenha. No entanto, a aceitação rapidamente se reverte se o programa apresentar problemas de performance. Com bastante frequência, a degradação de performance num programa está associado a queries mal projetadas submetidas a um banco de dados pelo programa.

Quando digo "mal projetada", eu me refiro a certas características das queries adotadas pelo programa, tais como não utilizar os índices apropriados das tabelas ou manter tabelas sem índice, fazer ligações ineficientes com outras tabelas, trazer registros demais que não serão utilizados ou ainda executar uma mesma query mais vezes do que o necessário.

A partir da sua versão 2005, o SQL Server mantém em tabelas virtuais uma série de informações a respeito das queries que foram executadas pelo banco de dados. Essas informações ficam armazenadas em memória e são descartadas quando o serviço do banco é desligado. No entanto, são informações bastante úteis para auxiliar na localização de problemas de performance gerados pelas queries mal planejadas.

Um usuário que queira consultar as informações disponibilizadas nessas tabelas virutais precisa ter atribuida a permissão VIEW SERVER STATE. Isso pode ser feito visualmente, através das propriedades do servidor no Management Studio ou via comando SQL, utilizando-se o GRANT.

Uma das views mais interessantes nesse contexto é a sys.dm_exec_query_stats, responsável pelas estatísticas de execução de queries. Ela traz, dentre outras informações, a quantidade de vezes que uma query foi executada, o tempo gasto nessas execuções, quantas linhas foram retornadas, a quantidade de leituras e gravações feitas durante a execução. Ela dá acesso também ao texto da query e ao plano de execução montado pelo SQL. O exemplo no quadro abaixo lista em ordem decrescente as queries que mais consumiram CPU:

SELECT qs.total_worker_time / qs.execution_count As 'Uso Médio da CPU',
qs.total_worker_time As 'Uso Total da CPU',
qs.execution_count As 'Qtde de Execuções',
qt.text As 'Query',
DB_NAME(qt.dbid) As 'Nome do Banco'
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) As qt
ORDER BY 1 DESC

Veja que a quantidade de vezes que uma query executou não é determinante para que ela tenha consumido mais CPU na média. Consulta semelhante pode ser montada para obter as queries que mais foram executadas (pra avaliar se todas as execuções são mesmo necessárias) ou a quantidade de linhas retornada (pra avaliar se não é possível restringir mais os registros, acrescentando outras comparações à cláusula WHERE). Os tempos listados estão em microsegundos; para convertê-lo em segundos, divida o valor por 1 milhão. É possível ainda levantar a quantidade de gravações feitas por uma query e avaliar se tantas alterações são mesmo necessárias

Outro detalhe a ser destacado no quadro anterior é que o texto da query não está na mesma view. Para obtê-lo, é necessário pegar o código contido no campo sql_handle e repassá-lo à função sys.dm_exec_sql_text.

Outra view bastante útil é a sys.dm_db_missing_index_details. Como o próprio nome diz, ela armazena detalhes sobre o uso de queries cujas execuções não estão otimizadas por que não foi possível encontrar na tabela um índice apropriado. Baseado nas informações retornadas por ela, podemos introduzir novos índices e melhorar a performance das consultas envolvendo as tabelas em questão. O quadro abaixo mostra um exemplo de consulta a essa view, listando as colunas encontradas na cláusula WHERE da query com problema, a tabela envolvida e a quantidade de vezes que a query problemática foi disparada:

SELECT equality_columns, inequality_columns, statement, count(*) As 'Qtde Exec'
FROM sys.dm_db_missing_index_details
WHERE DB_NAME(database_id) = 'ABC71DS94'
GROUP BY equality_columns, inequality_columns, statement
ORDER BY statement

A coluna statement traz o nome da tabela que está sem índice apropriado. A equality_columns lista as colunas usadas em comparações de igualdade no WHERE - algo como NOME_DO_CAMPO = VALOR. Em inequality_columns aparecem as colunas usadas em outros tipos de comparação do WHERE. Veja que é possível filtrar as informações por banco de dados instalado, permitindo avaliar os índices para cada banco isoladamente.

A lista de informações que podemos obter no DMV (Dynamic Management Views) inclui ainda os motivos de espera para uma query ser executada, dados de espelhamento e replicação, tamanho do banco de dados e suas partições, etc. A lista completa das informações disponíveis por esse meio pode ser encontrada em Dynamic Management Views and Functions no MSDN online.

Mais Informações
Uncover Hidden Data to Optimize Application Performance, Dynamic Management Views and Functions (Transact-SQL)

Trabalhando com rotinas T-SQL no SQL Server

O SQL (Structured Query Language) é muito mas que apenas uma linguagem para manutenções num banco de dados. Ela não se restringe às quatro operações básicas (INSERT, DELETE, UPDATE e SELECT) ou àquelas funções para gerenciar tabelas ou transações. O SQL Server da Microsoft, por exemplo, implementa uma extensão do padrão SQL chamada T-SQL (Transact SQL) que permite construir funções complexas. Elas combinam queries com elementos típicos das linguagens de programação comuns, fornecendo uma sintaxe que inclui variáveis, loops e controle de fluxo.

Com essa linguagem, você pode construir Triggers, Stored Procedures, Functions ou simplesmente executar comandos pelo SQL Server Management Studio (função antigamente disponível no Query Analyser). Neste post, vou usar essa ferramenta para mostrar alguns dos recursos do T-SQL.

Para montarmos o exemplo básico, considere que vamos gerar uma lista de preços baseada nas informações contidas numa tabela chamada LISTAPR, criada de acordo com o comando a seguir:

-- Em SQL Server
create table LISTAPR
(
ID int not null,
PRODUTO varchar(25) null,
DESCRICAO varchar(255) null,
PRECO numeric(15,4) null,
DATA_VLD smalldatetime null
)

Como toda linguagem, o T-SQL possui comandos de declaração de variáveis para armazenar valores ao longo do programa. Basicamente, os tipos de dados permitidos são os mesmos que são válidos para criação de campos num tabela, com exceção dos tipos que representam BLOBs (text, ntext e image). A declaração em si é feita com a palavra chave DECLARE seguida de um arroba (@) acompanhando o nome da variável e, para finalizar, o tipo de dado que essa variável será capaz de armazenar. Exemplos:

DECLARE @produto varchar(25)
DECLARE @descricao varchar(255)
DECLARE @preco numeric(15,4)
DECLARE @linha varchar(300)
DECLARE @nro_regs int

SELECT @nro_regs = 0

A forma como os valores são recuperados do banco de dados nos obriga a declarar variáveis para os campos que pretendemos manipular. Assim, no trecho acima eu declarei uma variável para cada campo que será impresso. Como se vê também, o comando SELECT pode ser usado para atribuir manualmente valores para as variáveis.

Há um tipo especial de variável para ser usado na obtenção de um result set (lista de registros). O nome desse tipo é CURSOR e é através dele que estabelecemos quais dados serão recuperados do banco, qual o tipo de navegação será permitida nos registros (apenas avançar ou ir pra frente e pra trás) e também se será possível atualizar os dados. Para o cenário proposto neste post, precisaremos de uma coleção read-only que contenha todos os registros da tabela LISTAPR que estejam dentro da validade. A declaração abaixo atende esses requisitos:

DECLARE lista CURSOR SCROLL FOR
SELECT produto, descricao, preco
FROM LISTAPR
WHERE DATA_VLD > GETDATE() OR DATA_VLD IS NULL

O tipo da navegação é irrelevante aqui mas eu inclui a palavra SCROLL na declaração, possibilitando a navegação pelos registros em qualquer direção que queiramos. Outro detalhe é que eu estipulei quais campos eu quero recuperar. Isso é uma boa prática pois, conforme veremos adiante, deixa claro quais campos corresponderão a quais variáveis.

Agora, podemos abrir o cursor criado, navegar pela lista de registros obtidos, processar os dados conforme necessário e, finalmente, encerrar o cursor. O quadro abaixo traz o código com os passos citados:

OPEN lista
-- Primeiro registro
FETCH FIRST FROM lista INTO @produto, @descricao, @preco

WHILE @@FETCH_STATUS = 0
BEGIN
-- O processamento do registro atual será feito aqui

-- Próximo registro
FETCH NEXT FROM lista INTO @produto, @descricao, @preco
END

CLOSE lista
DEALLOCATE lista

Vamos destrinchar esse código. O comando OPEN apenas abre o cursor, submetendo a query ao banco de dados. O comando FETCH FIRST posiciona no primeiro registro e transfere para as variáveis os valores obtidos. Veja que a ordem das variáveis na cláusula INTO segue a mesma ordem estabelecida na cláusula SELECT.

A instrução WHILE é um laço tradicional, repetindo os comandos entre o BEGIN e o END enquanto o valor da expressão após o WHILE for verdadeira. Neste caso, @@FETCH_STATUS é uma variável nativa do T-SQL. Ela é afetada pela execução de comandos FETCH, assumindo valor 0 (zero) quando há um registro válido posicionado. Com isso, o comando FETCH NEXT, é executado enquanto ainda houver registros a recuperar no cursor.

Para encerrar, o cursor é fechado com o comando CLOSE e os recursos usados por ele são devolvidos ao sistema através do comando DEALLOCATE. Variáveis do tipo CURSOR são as únicas que requerem a desalocação explícita.

Ficou faltando a parte do processamento interno do laço. Como planejamos simplesmente imprimir os valores das variáveis, podemos usar o comando PRINT para isso. Ele aceita um texto como parâmetro, de modo que teremos que fazer a conversão dos tipos numéricos antes de imprimir.

SELECT @nro_regs = @nro_regs + 1, @linha = ''

SELECT @linha = @linha + @produto + REPLICATE (' ', 21 - LEN (@produto))
SELECT @linha = @linha + @descricao + REPLICATE (' ', 21 - LEN (@descricao))
SELECT @linha = @linha + STR (@preco, 10, 2)

PRINT @linha

Cada chamada ao PRINT gera uma nova linha na janela de saída do Management Studio, razão pela qual concateno os valores num único texto antes de imprimí-lo. Assim, cada produto terá sua própria linha. A ginástica com o REPLICATE e o LEN é para garantir que produto e descrição apareçam sempre com a mesma quantidade de caracteres, mantendo o resultado alinhado, mais fácil de ler. O STR converte o preço em um texto para a impressão.

É claro que, dada a simplicidade do exemplo, poderíamos ter resolvido o problema apenas executando a query. Mas o objetivo aqui era mostrar a flexibilidade da linguagem já que ela permite conter cursores aninhados, trabalhar com decisões de fluxo (instrução IF), cálculos complexos e outras interações. Aqui na ABC71, nós aproveitamos essa flexibilidade do T-SQL mesclada a queries que obtêm informações sobre tabelas do banco de dados para gerar certos tipos de codigo fonte auxiliares de nosso ERP.

Mais Informações
Transact SQL, Funções nativas T-SQL para SQL Server 2008

Trabalhando com as funções de agregação do SQL

O SQL - Structured Query Language (Linguagem de Consulta Estruturada) é uma linguagem usada para dar manutenção nos dados em um bancos de dados. Oracle, Sybase e MS SQL Server são alguns exemplos de servidores de bancos de dados que se valem dessa linguagem tanto para permitir a extração dos dados armazenados como para inserir novos dados ou atualizar aqueles já existentes. A primeira versão da sintaxe foi padronizada em 1986 e a última revisão foi aprovada pelo ISO em 2008.

Desde a primeira versão, o padrão incorpora funções classificadas como "agregação". São funções nativas de consulta que servem para agrupar informações, agregando numa única linha o conteúdo de vários registros. Dessa forma, os detalhes da informação original são postos de lado e as informações são tratadas em conjunto.

Atualmente, as funções de agregação disponíveis no SQL são as seguintes: AVG (calcula a média dos valores de um campo determinado), COUNT (retorna a quantidade de registros existentes), SUM (calcula a soma dos valores de um determinado campo), MAX (retorna o maior valor existente para um campo especificado) e MIN (retorna o menor valor existente para um campo especificado). Todas essas funções são aplicadas apenas aos registros que atenderem os critérios estabelecidos na cláusula WHERE da query onde estiverem sendo usadas.

Pode parecer confuso a princípio mas o conceito de agregação é, na verdade, bastante simples. Considere o seguinte conjunto de registros existente numa tabela no banco de dados:

NF	Produto	Cliente	Qtde	Valor	Dia
1	P001	C1	8	R$ 160,00	5
1	P002	C1	2	R$ 34,00	5
1	P003	C1	1	R$ 58,00	5
2	P002	C3	20	R$ 340,00	7
3	P001	C2	12	R$ 240,00	8
3	P002	C2	5	R$ 85,00	8
4	P001	C1	2	R$ 40,00	10
5	P003	C3	4	R$ 232,00	15

Um exemplo simples de agregação com esses registros seria somar a coluna Valor deles todos. Supondo que a tabela se chame TABELA, a query e seu retorno seriam:

SELECT SUM (Valor) FROM TABELA

------------------
1189,00

Repare que apenas um registro é retornado. É por isso que eu disse que as agregações escondem os detalhes dos registros envolvidos. O uso de agregações não impede que se aplique a cláusula WHERE na query, isto é, ainda posso restringir quais registros eu quero incluir na agregação. O exemplo da soma considerando apenas as notas do cliente C1 ficaria:

SELECT SUM (Valor) FROM TABELA
WHERE Cliente = 'C1'

------------------
292,00

Na maioria dos casos, restringir tanto os registros afetados e o retorno não é desejável. Numa aplicação real, quase sempre as informações terão que vir um pouco mais detalhadas. Pode ser necessário, por exemplo, trazer as somas de cada cliente separadamente, sem que se tenha que especificar um cliente em particular. Esse efeito é obtido agrupando-se os registros através da cláusula GROUP BY.

SELECT Cliente, SUM (Valor), COUNT(*) AS QtdeReg
FROM TABELA
GROUP BY Cliente

Cliente		QtdeReg
------------------	------------------	------------------
C1	292,00	4
C2	325,00	2
C3	572,00	2

Acrescentei no quadro anterior também uma coluna que mostra quantos registros da tabela foram considerados em cada linha trazida, isto é, quantos registros estão agregados em cada linha. Isso exemplifica o uso da função COUNT.

Se for preciso, é permitido detalhar ainda mais as informações adicionando novos agrupamentos - o produto ou o dia da movimentação, por exemplo. Além disso, todos os elementos de uma query comum podem ser usados, tais como o WHERE (já mostrado) e o ORDER BY (para que as linhas retornadas estejam classificadas numa determinada ordem).

A cláusula WHERE determina quais registros da tabela devem ser incluídos na agregação. E se você quiser restringir as linhas retornadas, trazendo apenas aquelas que atingirem determinado valor agregado ? Suponha, por exemplo, que na query do quadro anterior você queria exibir apenas os clientes cuja soma de valor seja superior a 300. Para esta situação, o padrão SQL define a cláusula HAVING que nada mais é que um WHERE aplicado ao resultado da agregação. Veja a implementação do exemplo:

SELECT Cliente, SUM (Valor), COUNT(*) AS QtdeReg
FROM TABELA
GROUP BY Cliente
HAVING (SUM (Valor) > 300) AND (COUNT(*) >= 1)

Cliente		QtdeReg
------------------	------------------	------------------
C2	325,00	2
C3	572,00	2

Repare que o HAVING é aplicado após o agrupamento (GROUP BY) enquanto o WHERE é aplicado ao SELECT. O exemplo também mostra que é possível combinar mais de um valor de agregação no HAVING para obter filtros mais complexos.

Mais Informações
Structured Query Language (SQL)

Obtendo informações sobre as tabelas de um banco SQL Server

Criar documentação para um sistema é sempre uma parte aborrecida e frequentemente negligenciada justamente por causa disso. No caso da ABC71, além da documentação das telas que é distribuída junto com o sistema, nós temos um documento que descreve as tabelas existentes no banco de dados. Sempre que uma tabela ou coluna nova é criada (ou modificada), nós registramos uma pequena descrição dela em uma tabela nossa, específica para esse fim.

Esse documento que descreve as tabelas é destinado a quem precise dar manutenção em dados diretamente no banco (parceiros, consultores, usuários avançados, etc.). Obviamente, ter apenas um breve descritivo de cada campo não é suficiente para esse objetivo - até porque é comum esquecer de alimentar a tal tabela.

Para gerar um documento mais completo, uso um conjunto de views do próprio SQL Server que permite obter informações sobre qualquer objeto existente no banco : tabelas e suas colunas, chaves, triggers, stored procedures, constraints, etc. Neste post, mostro como obter informações básicas, como a lista de tabelas, suas colunas e chaves.

A principal dessas views é a sys.sysobjects cujas linhas produzem um inventário dos objetos existentes no banco. As principais colunas retornadas por essa view estão descritas abaixo.

name é o nome de um objeto
id é o identificador único de cada objeto, independente do tipo desse objeto. O id é chave estrangeira em outras views, podendo ser usado para obter mais detalhes a respeito de um objeto. Por exemblo, há uma view que relaciona as colunas de uma determinada tabela e, nessa view, o id identifica qual é a tabela associada.
xtype identifica o tipo do objeto em cada linha da view. Essa coluna pode ser usada para filtrar os registros corretos quando for necessário um join para obter mais detalhes em outras views, como no caso das colunas citado acima. Os tipos possíveis podem ser encontrados neste link do MSDN.

Como exemplo, a query abaixo lista as tabelas de um banco.

select id, name from sys.sysobjects where xtype = 'U'

Efeito semelhante pode ser obtido simplesmente listando as linhas da view systables:

select * from sys.systables

As colunas de uma tabela são recuperadas acessando-se a view sys.syscolumns, cujas principais colunas eu comento abaixo.

name é o nome da coluna.
id identifica a tabela a qual essa coluna pertence. É o mesmo id encontrado em sys.sysobjetcs, podendo ser relacionado com ele para obter o nome da tabela.
Em type encontramos a identificação do tipo de dado atribuído à coluna. O nome para o tipo pode ser localizado na tabela sys.types. Para tipos núméricos, as colunas prec (precisão) e scale também deve ser levadas em consideração. No caso de tipos alfanuméricos ou binários, a coluna lenght determinará o tamanho máximo permitido.
O status informa, por exemplo, se essa coluna aceita o valor NULL. Para saber quais são os valores possíveis, veja a documentação no MSDN.
A ordem com que cada coluna foi criada dentro da tabela é estabelecida pelo campo colorder.

Se você não estiver trabalhando com tipos de dados criados pelo usuário, a query abaixo consegue listar as colunas de todas as tabelas de seu banco:

select *,
(select name from sys.types t
where c.type = t. system_type_id
and t.is_user_defined = 0) As Tipo
from sys.syscolumns c
--where c.id = 0 /* use o id de uma tabela p/ obter as colunas específicas */

Para levantar quais são as chaves criadas para uma tabela, podemos usar as informações retornadas pela view sys.sysindexes em conjunto com as linhas da view sys.sysindexkeys. Basicamente, a primeira view traz os nomes das chaves enquanto a segunda traz as colunas que constituem cada chave. Os campos mais relevantes da sys.sysindexes são os seguintes:

name é o nome atribuído à chave.
id novamente identifica uma tabela, cujo nome pode ser obtido relacionando-se esse campo com id encontrado em sys.sysobjetcs.
indid é um código único definido por tabela e que serve para identificar cada chave dessa tabela. Aparentemente, o valor 0 (zero) é sempre um valor interno do SQL; portanto, nos interessa apenas os registros cujo valor é maior que zero.
keycnt retorna a quantidade de campos que compõem a chave.
Use o status para determinar se a chave é primária, isso é, se os valores dos campos dessa chave poderão ser repetidos em outros registros ou não.

Na view sys.sysindexkeys encontramos os campos id (a tabela em sysobjects), indid (a chave em sysindexes) e colid (a coluna que compõe a chave, relacionada com a view syscolumns). O último campo é keyno, que indica a ordem em que as colunas aparecem na chave. A query abaixo usa essas informações para obter os nomes das colunas que compõem uma chave:

select I.name as Chave, IK.keyno as Ordem, c.name as Coluna
from sys.sysindexes I, sys.sysindexkeys IK, sys.syscolumns C
where I.id = IK.id
and I.id = c.id
and I.indid = IK.indid
and IK.colid = C.colid
and I.id = 2042867486 /* id da tabela */
and IK.indid = 2 /* número de uma chave da tabela */
order by IK.keyno

Note que há valores fixos para o objeto (tabela) e para a chave. Esse números devem ser substituídos por códigos válidos no seu banco de dados.

Mais Informações
Views do SQL Server para informações sobre um banco de dados

Depurando comandos enviados ao Oracle

Desenvolver um ERP é um trabalho bastante grande já que normalmente engloba vastas áreas da operação de uma Empresa. Se a construção desse ERP tem como premissa ser independente do fabricante do banco de dados onde ele vai armazenar as transações, esse trabalho será ainda maior pois adiciona a dificuldade de compatibilizar a sintaxe dos comandos SQL para realizar as operações no banco de dados.

A ABC71 trabalha com essa premissa e seu ERP é homologado para executar em alguns bancos de dados, entre eles SQL Server e Oracle. Embora haja um SQL padrão, na prática cada fabricante acaba introduzindo diferenças de sintaxe para refletir diferenças de implementação - seja para melhorar performance ou usabilidade, entre outros. É por essa razão que não utilizamos Stored Procedures para implementar funcionalidades do ERP; teríamos praticamente que construir um ERP para cada tipo de banco de dados.

Esse cenário levanta outra questão, relacionada com situações em que é necessária a depuração do programa. Uma vez que os comandos SQL são montados internamente no programa e podem ser diferentes dependendo do banco ao qual são direcionados, como interceptar o comando que é de fato submetido ao banco ? Isto é, será que os valores enviados como parâmetros ao comando SQL estão corretos ? E quanto à sintaxe do comando em si ?

Junto com o SQL Server é distribuído o programa Profiler que é capaz de interagir com o gerenciador de banco de dados e capturar os comandos SQL, incluindo parâmetros, se houverem. No caso do Oracle, não há uma ferramenta que venha junto com o banco para permitir esse tipo de depuração.

Procurando na internet, localizei uma ferramenta chamada Statement Tracer for Oracle, cuja empresa desenvolvedora chama-se Aboves Software. Ela é gratuita mas faz pelo Oracle exatamente o que o Profiler faz pelo SQL Server: captura os comandos SQL submetidos ao banco de dados por uma aplicação. Os comandos capturados são exibidos em linhas, sequencialmente de acordo com a data e hora em que foram submetidos. Ao selecionar uma das linhas, o programa transfere o comando completo para um painel na parte de baixo da interface, permitindo sua análise e, eventualmente, sua cópia.

O Statement Tracer é executado localmente, isto é, no mesmo computador em que você está executando o programa que submeterá as queries. Ele, então, criará uma lista com todos os programas que estejam com alguma conexão ativa com um banco de dados Oracle, exibindo cada programa numa guia diferente. Para iniciar o monitoramento, basta apertar um botão na interface.

O programa permite que você configure quais classes de comando você quer interceptar, incluindo controle de transações, preparação e execução das queries (SELECT, INSERT, etc), visualização de parâmetros em queries parametrizadas e comandos relacionados a operações no esquema do banco de dados (CREATE TABLE, por exemplo). Você pode também incluir ou remover determinadas colunas. Uma dessas colunas traz o tempo que o comando relacionado demorou para executar, sendo uma ferramenta bastante útil para detectar pontos no sistema que possam ter a perfomance melhorada.

Há ainda um configuração de regras para aplicar uma cor diferente às linhas do Tracer, baseado no texto apresentado pelo comando. A utilidade disto é ampla e vai desde descobrir qual comando enviou um determinado valor ao banco de dados até saber quais comandos envolveram determinada tabela ou visão.

Caso queira analisar num outro momento os comandos que foram capturados, basta salvar todo o log para um arquivo texto.

O download da ferramenta pode ser feito neste link. Há outras ferramentas da mesma empresa que podem ser encontradas neste endereço. Veja que há aí um Tracer para interceptar comandos diretamente no ADO, indepentemente do Banco de Dados conectado. No entanto, essa ainda não me pareceu totalmente madura pois às vezes gera invasões de memória.

Mais Informações
Statement Tracer For Oracle

Sintaxe do comando INSERT no SQL Server

Por ser parte integrante de uma das 4 operações básicas de uma aplicação que trabalhe com Banco de dados (Incluir, Atualizar, Excluir e Consultar) o comando SQL INSERT é imprescindível neste cenário. Os exemplos que vou mostrar aqui foram montados com o SQL Server da Microsoft mas uma parte deles deve funcionar com outros Gerenciadores de Banco de Dados.

Provavelmente, a sintaxe mais usada desse comando é a que permite a inclusão de um único registro numa tabela. Considere a tabela criada com o comando abaixo:

-- Em SQL Server
create table INFO
(
ID int not null,
VALOR numeric(15,4) null,
DESCRICAO varchar(255) null,
DATA smalldatetime null
)

Para incluir um único registro com valores fixos, a sintaxe básica pede as palavras chaves INSERT INTO seguidas pelo nome da tabela, a palavra VALUES e, entre parênteses, a lista dos valores para cada campo separados por vírgula. Um exemplo com a tabela descrita acima:

INSERT INTO INFO
VALUES (10, NULL, 'Texto da descrição', '20091009')

Os valores para colunas que sejam do tipo texto (Varchar) ou do tipo data devem ser envolvidos em aspas simples. A ordem em que os valores devem ser colocados segue exatamente a mesma ordem com que os colunas foram declaradas na criação da tabela; com essa sintaxe básica, todas as colunas devem ter um valor associado no VALUES. As colunas que foram marcadas na criação da tabela com as palavras NOT NULL são obrigatórias, isto é, não posso passar NULL no lugar do valor reservado para elas.

Para uma tabela com muitas colunas, essa sintaxe é bastante trabalhosa, já que um valor para cada coluna deve ser providenciado, e na ordem correta. Neste caso, há uma variação da sintaxe básica que pode ser útil pois permite que selecionemos quais as colunas que serão alimentadas pelo comando, restringindo a quantidade de informações àquelas colunas que forem realmente necessárias na inclusão. Para isso, basta criar após o nome da tabela uma lista entre parênteses com os nomes das colunas separadas por vírgula. A ordem das colunas nessa lista é irrelevante mas a lista com os valores a inserir deve respeitar a ordem estabelecida.

INSERT INTO INFO
(ID, DESCRICAO)
VALUES
(10, 'Texto da descrição')

As colunas que forem omitidas no comando serão inseridas com valor NULL. Por isso, todas as colunas declaradas com NOT NULL devem ser informadas, caso contrário um erro será reportado e o registro não será incluído. Na tabela usada como exemplo, apenas a coluna ID é obrigatória com essa sintaxe.

E para o caso de querer popular a tabela com valores lidos de outra tabela ? Na verdade, há uma variação da sintaxe do INSERT que permite informar uma cláusula SELECT completa. Então, a origem dos dados pode ser uma única tabela, um JOIN entre tabelas ou mesmo de uma visão (VIEW) que tenha sido criada no banco de dados. Para esse resultado, basta substituir a cláusula VALUES e sua lista de valores pelo SELECT desejado.

INSERT INTO INFO (ID, DESCRICAO, VALOR)
SELECT ID_INFO, 'Texto da descrição', VLR_TRANS
FROM INFOTRANS
WHERE ID_INFO > 100

Mais uma vez, a ordem e o tipo das colunas retornadas no SELECT devem respeitar a sequência de colunas conforme estabelecido no próprio comando, como no exemplo. Caso essa sequencia seja omitida, novamente passa a valer a ordem de declaração das colunas quando a tabela foi criada.

Veja também que o comando SELECT montado trás misturado valores de colunas e valores fixos. Poderiam ter sido usadas também funções do SQL Server ou qualquer outra expressão válida. Em todas essas situações, os tipos de dado das colunas no SELECT devem ser compatíveis com os tipos das colunas que vão ser inseridas. Como resultado do INSERT deste exemplo, podem ser incluidos vários registros de uma vez só - será um registro na tabela INFO para cada registro retornado pelo SELECT.

O SQL Server permite que você informe o nome completo de uma tabela ao montar uma cláusula SELECT, isto é, você pode dizer qual é o nome do banco de dados onde a tabela está e o nome do "Owner" da tabela. Na prática, isso possibilita, por exemplo, a cópia de registros de uma base de testes para a base de Produção.

INSERT INTO INFO (ID, DESCRICAO, VALOR)
SELECT ID, DESCRICAO, VALOR
FROM BASETESTES.dbo.INFO

Se o banco de dados onde o SELECT será submetido estiver em um computador (Servidor de banco de dados) diferente daquele onde você vai executar o comando INSERT, deve ser usada a função OPENDATASOURCE, que existe desde a versão 2000 do SQL Server. Ela é necessária por causa da política de segurança, isto é, você provavelmente vai ter que fornecer um usuário e senha para poder se conectar nesse servidor remoto antes de conseguir extrair dados dele.

Mais Informações
Documentação do INSERT no MSDN, Função OPENDATASOURCE no SQL Server

Criando queries com uma coluna RecordNumber (ranking)

A ABC71 disponibiliza para seus Clientes uma ferramenta para construção de relatórios baseada no Report Builder, onde se monta relatórios costurando queries prontas a um layout de apresentação. Outro dia precisei montar uma query para um relatório desses e era imperativo que uma das colunas trouxesse valores sequenciais (um ranking) de modo que no primeiro registro teria valor 1, no 2o teria valor 2 e assim por diante.

Pesquisei no help do SQL Server 2005 e me deparei com uma solução talhada para resolver esse tipo de problema já que a Microsoft incluiu funções para ranking nessa versão! Para o que eu precisava, a função ROW_NUMBER bastava. O uso dela é na cláusula SELECT como no exemplo:

SELECT EMP_FIL, FORNEC, ROW_NUMBER() OVER (ORDER BY FORNEC) AS SEQUENCIA
FROM CFOR
ORDER BY EMP_FIL

A cláusula OVER que vem depois do nome da função é obrigatória e indica qual é o campo (ou campos) que deve(m) ser usado(s) para sequenciar os registros. A grosso modo, a query é primeiro ordenada segundo as instruções dessa cláusula, um número sequencial é atribuído a cada registro e só então o ORDER BY geral da query é aplicado antes dos registros serem retornados.

Como não é obrigatório que o ORDER BY estipulado no OVER seja o mesmo da query, pode ser que a coluna com o ranking não venha na ordem.

Essa solução seria perfeita se ABC71 não tivesse Clientes que usassem outros tipos de banco de dados, como ORACLE, PostgreSQL e versões mais antigas do SQL Server. E como esses comandos não são do padrão SQL ....

Pesquisando na Internet, encontrei outras sugestões para solucionar esse problema numa gama maior de versões e fabricantes de bancos de dados. A que me pareceu mais consistente sugere criar um campo IDENTITY na tabela que deve ser rankeada, sendo o campo programado para iniciar com valor 1 e para ir incrementando de 1 em 1 a cada novo registro inserido. O trecho de script que segue é um exemplo em SQL Server para criar uma tabela cuja coluna ID tem essas características:

CREATE TABLE [dbo].[CFOR](
ID identity(1, 1),
[EMP_FIL] [int] NOT NULL,
[FORNEC] [numeric](14, 0) NOT NULL,
[RAZ_SOCIAL] [varchar](35) NULL,
[NOME_FANTAS] [varchar](25) NULL
)

Para quem já tem muitos Clientes usando uma aplicação com a tabela, no entanto, essa abordagem ainda pode ser inconveniente pois será necessário reorganizar a tabela para incluir o novo campo.

Uma solução que deve ser aplicável a todos os bancos relacionais envolve criar um OUTER JOIN já que esse é um recurso padrão SQL. A ideia é montar uma query agrupando (GROUP BY) todas as colunas desejadas e ligando essa query a uma outra que envolva a mesma tabela. A ligação é através de LEFT OUTER JOIN, usada para trazer todos os registros da primeira query e usando a segunda para contar quantos registros são maiores ou iguais ao registro atual. O comando reproduzido abaixo traz o mesmo resultado da primeira query deste post. Tal comando está usando a sintaxe do SQL Server.

SELECT F1.EMP_FIL, F1.FORNEC, COUNT(*) AS SEQUENCIA
FROM CFOR F1 LEFT OUTER JOIN
CFOR F2 ON (F1.FORNEC > F2.FORNEC)
OR (F1.FORNEC = F2.FORNEC AND F1.EMP_FIL >= F2.EMP_FIL)
GROUP BY F1.FORNEC, F1.EMP_FIL
ORDER BY F1.FORNEC, F1.EMP_FIL

Já que nem tudo pode ser perfeito, esta query resolve o problema para diversos bancos mas tem um tempo de execução muito maior que a primeira, usando ROW_NUMBER. Com ROW_NUMBER, executou praticamente instantâneo para 23.000 registros enquanto a que usa LEFT OUTER JOIN levou quase 2 minutos no mesmo servidor.

Mais Informações
Funções de Ranking para SQL Server 2005 e posteriores, colunas do tipo INDENTITY

Erro no backup do SQL Server

A maior parte dos Clientes da ABC71 usa o banco de dados MS SQL Server e, de vez em quando, algum deles precisa mandar a base para analisarmos.

Outro dia, recebi a base de um Cliente num pen-drive e tentei restaurá-la em nosso servidor de testes. A tentativa me rendeu uma mensagem escabrosa:

Msg 3241, level 16
The media family on Device ‘device-name’ is incorrectly formed. SQL Server cannot process this media family

Revirei a internet para tentar achar uma forma de contornar o problema mas simplesmente não foi possível; parece que o arquivo de backup se corrompeu em algum momento do processo e não poderia ser utilizado.

Procurei também pela causa provável deste problema e listo abaixo as que achei mas plausíveis:
1) Havia usuários conectados ao Banco de Dados - talvez até alguma transação em andamento - no instante em que o backup estava sendo feito. O resultado é um backup inconsistente ou incompleto.
2) O backup gerou um arquivo muito grande e a tentativa de copiá-lo ou compactá-lo resultou num arquivo corrompido. Isto é particularmente válido se há setores ruins no disco rígido ou se você instalou o SQL Server num Virtual PC - o Virtual PC tem problemas em lidar com arquivos grandes e transferí-los para a máquina real pode corrompê-los.

Para eliminar a primeira possibilidade, pedi ao Cliente que executasse o comando de restauração com a opção de apenas verificar a integridade do backup. A sintaxe é a seguinte:

RESTORE VERIFYONLY
FROM DISK = 'C:\TEMP\DB.bak'

No meu caso, o próprio Cliente não conseguia visualisar o conteúdo nem restaurar o backup - o SQL dele reportava a mesma mensagem de erro. Então, não havia muito o que fazer a não ser pedir que fizesse outro backup para enviar.

Este é o ponto aqui: geralmente, faz-se o backup na ilusão de que qualquer problema com a base é só restaurá-lo. No entanto, negligencia-se um aspecto básico, que é certificar que o próprio backup feito está íntegro e poderá ser restaurado sem problemas. Use o comando RESTORE VERIFYONLY para fazer tal checagem.

Se foi verificado pelo RESTORE VERIFYONLY na estação original que o backup está íntegro mas o erro na restauração persiste, então é certo que o arquivo foi corrompido no processo de transferência para a máquina onde se quer restaurá-lo.

Caso a cópia tenha sido feita através de um Shared Folder do Virtual PC, copie novamente o arquivo, usando desta vez a rede normal no Virtual PC.

Caso a cópia tenha sido feita num pen-drive ou gravada num DVD, certifique-se que a mídia não está corrompida ou que a gravação foi feita com sucesso. Considere ainda a possibilidade de seu disco rígido estar corrompido.

Mais Informações
Restore Database Failure, Comando RESTORE do SQL Server.

Comando INSERT do SQL em tabela com BLOB

Tem algum maluco aí que já tentou incluir via comando SQL um registro numa tabela que tenha um campo BLOB ? Pois é, eu já… Mas tenho um bom argumento em minha defesa : desenvolvi uma ferramenta para ABC71 que permite manipular e transformar dados, isto é, copiar dados de uma tabela num banco de dados, realizar cálculos e outras transformações nesses dados e lançá-los numa outra tabela, num outro banco de dados. Como isso é feito através de um VBScript, foi necessário passar o comando SQL apropriado, incluindo o dado BLOB. O quê ? Já viu isso em algum lugar ? Está certo ... Foi inspirado no DTS da Microsoft.

Voltando ao assunto, em SQL Server, a sintaxe é como no exemplo abaixo :

insert into lare (emp_fil, chave_1, descricao, objetos)
values (10001, 'CONFIG', 'Configuração de Usuário', 0x71720002090000434F445F4E554D00494420437573)

onde o campo “objetos“ é do tipo IMAGE, isto é, é um blob.

O texto 0x (zero xis) no início do valor do BLOB é fixo e indica que se trata de um valor hexadecimal. Os demais valores são a representação hexadecimal de cada um dos bytes que compõem a informação que se deseja gravar. Exemplos : um byte com valor 00000000 é representado como 00, o byte 00000001 vai como 01, o byte 11111111 vai como FF, etc.

Como a limitação de tamanho desse campo é ditada pelo espaço em disco e os valores são bytes, pode-se armazenar qualquer informação com esse método : um texto XML, uma estrutura proprietária de sua aplicação, uma imagem, etc..

Essa sintaxe funciona ligeiramente diferente para Oracle e Postgre. Em Oracle, basta remover o 0x do início e colocar os dados entre apóstrofes :

insert into lare (emp_fil, chave_1, descricao, objetos)
values (10001, 'CONFIG', 'Configuração de Usuário', '71720002090000434F445F4E554D00494420437573')

Em Postgre, é preciso decodificar a informação, chamando a função DECODE. O segundo parâmetro indica que a informação no primeiro parâmetro é um valor hexadecimal :

insert into lare (emp_fil, chave_1, descricao, objetos)
values (10001, 'CONFIG', 'Configuração de Usuário',
DECODE ('71720002090000434F445F4E554D00494420437573','hex') )

A função em VBScript listada abaixo aceita como parâmetro um dado binário e produz como resultado a representação hexadecimal desse dado. Esse resultado pode ser usada no INSERT mas é preciso ainda considerar as diferenças de sintaxe citadas antes :

Function BinaryToHex(Binary)
Dim c1, Out, OneByte
For c1 = 1 To LenB(Binary)
OneByte = Hex(AscB(MidB(Binary, c1, 1)))
If Len(OneByte) = 1 Then
OneByte = "0" & OneByte
End If
Out = Out & OneByte
Next
BinaryToHex = Out
End Function

Até a próxima ...

Mais Informações
Comando INSERT no SQL Server