quarta-feira, 5 de dezembro de 2012

SQL Dinâmico em Stored Procedure (Building Dynamic SQL In a Stored Procedure) (Parte 1/3)

Introdução


O SQL Dinâmico em STORED PROCEDURE é uma declaração Transact-SQL simples ou um conjunto de instruções armazenadas em uma variável e executado através de um comando SQL. Pode haver vários métodos de aplicação no SQL Server. Este artigo irá mostrar-lhe um bom método de aplica isso. Antes de entrar em uma explicação detalhada, deixe-me perguntar "Quando usar SQL dinâmico?" Nós não podemos dizer definitivamente que o SQL estático (comando direto) irá atender todas as nossas necessidades de programação. O Dynamic SQL é necessário quando precisamos recuperar um conjunto de registros com base em parâmetros de pesquisa. Digamos, por exemplo - Uma tela de pesquisa empregado ou um relatório de propósito geral que precisa executar uma instrução e selecionar com base em uma cláusula WHERE diferente.

NOTA: Mais importante ainda, as consultas SQL dinâmicas em uma variável não são compilados, analisados, marcada por erros até que eles sejam executados.



sp_executesql Vs comando EXECUTE


Na construção  dinâmica  de Transact-SQL pode ser executado através do comando EXECUTE ou declaração sp_executesql. Aqui, neste artigo e nos meus exemplos, eu vou usar sp_executesql que é mais eficiente, mais rápido na execução e também apóia a substituição de parâmetro. Se estamos usando EXECUTE para executar a seqüência de SQL, todos os parâmetros devem ser convertidos em texto e concatenado na consulta antes da execução. O sp_executesql proporciona uma melhor maneira de implementar isso. Isso nos permite substituir os valores dos parâmetros para qualquer parâmetro especificado na string SQL. Antes de entrar no exemplo real, deixe-me diferenciar esses dois comandos com um exemplo simples. Consulta: selecionar um registro da tabela empregado utilizando o ID na cláusula WHERE.



A sintaxe básica do comando  EXECUTE:


EXECUTE(@SQLStatement)



A sintaxe básica para usar o sp_executesql:

sp_executesql [@SQLStatement],[@ParameterDefinitionList],
[@ParameterValueList]

Exemplo 1.0

/* Declaração de Variávies */
DECLARE @EmpID AS SMALLINT
DECLARE @SQLQuery AS NVARCHAR(500)
/* Setando valor dos parâmetros */
SET @EmpID = 1001
/* Construindo o SQL com o parâmetro */
SET @SQLQuery = 'SELECT * FROM tblEmployees WHERE EmployeeID = ' + 
CAST(@EmpID AS NVARCHAR(10))
/* Executando a Transact-SQL */
EXECUTE(@SQLQuery)
No exemplo 1.0 acima, temos 2 variáveis declaras. A proimeira variável @EmpID é usada como parâmetro da procura e a segunda variável @SQLQuery é usada para construir a string SQL, a consulta propriamente dita. Pode-se perceber que essa variável @EmpID é convertida para NVarchar para fazer parte da string T-SQL. Se você imprimir a variável @SQLQuery  (PRINT @SQLQuery), você terá uma visualização aprecida com esta:
SELECT * FROM tblEmployees WHERE EmployeeID = 1001

No final a query acima será executada pelo comando EXECUTE

Exemplo 1.1

DECLARE @EmpID AS SMALLINT
DECLARE @SQLQuery AS NVARCHAR(500)
DECLARE @ParameterDefinition AS NVARCHAR(100)
/* Setando valores do parâmetro */
SET @EmpID = 1001
/* Contruindo o SQL incluindo os parâmetros */
SET @SQLQuery = 'SELECT * FROM tblEmployees WHERE EmployeeID = @EmpID' 
/* Especificando o formato do parâmetro */
SET @ParameterDefinition =  '@EmpID SMALLINT'
/* Executando o SQL */
EXECUTE sp_executesql @SQLQuery, @ParameterDefinition, @EmpID
In the example 1.1, there are two variables declared. The variable @EmpID is used as a parameter to the SQL Query and second variable @SQLQuery is used to build the SQL String, the third variable @ParameterDefinition is used to specify the parameter format before executing the SQL string. If you print the @SQLQuery string (PRINT @SQLQuery), you will get the query as shown below:
SELECT * FROM tblEmployees WHERE EmployeeID = @EmpID
Here, in this example, you can clearly see the parameter @EmpID is included in the statement. Finally, sp_executesqltakes the necessary information to do the parameter substitution and execute the dynamically built SQL string.
  1. @SQLQuery --> contains the SQL statement
  2. @ParameterDefinition --> contains the Parameter Definition
  3. @EmpID --> contains the parameter value to be substituted to the parameter in the SQL statement.
NOTE: The parameters included in the Dynamic SQL string must have a corresponding entry in the Parameter Definition List and Parameter Value List.




Texto original em: http://www.codeproject.com/Articles/20815/Building-Dynamic-SQL-In-a-Stored-Procedure

quinta-feira, 29 de novembro de 2012

USANDO COLUNAS COMPUTADAS (USING COMPUTED COLUMNS)

Em algumas bases de dados, alguns valores que foram calculados, muitas vezes geram diversos relatórios. Também existem algumas colunas que são dependentes de uma ou mais colunas. Quando uma coluna é atualizada, usa-se gatilhos ​​para sincronizar os novos valores em colunas dependentes. É necessária a utilização de um meio mais eficiente e padronizado para lidar com esses tipos de cenários. Então como é possível se livrar da sobrecarga de cálculos em tempo de geração de relatórios e como  evitar o uso de gatilhos para a sincronização de colunas atualizadas?

Solução:
Para esses cenários onde os valores calculados são necessárias ou valores são gerados através de manipulação em outras colunas, temos um poderoso recurso previsto no SQL Server. Este recurso é o de "Colunas computadas".

Uma coluna computada é calculada a partir de uma expressão que pode usar outra coluna ou colunas na mesma tabela. Funções, variáveis​​, constantes, nomes de colunas não computadas ou qualquer combinação de todos estes podem ser usados ​​junto com as operadoras para criar uma coluna computada.

Nesta dica, vamos percorrer um exemplo de implementação de uma coluna computada.

Vamos testar scripts em SQL Server 2005 e SQL Server 2008. No script a seguir será criada uma tabela chamada CCtest no banco de dados AdventureWorks com três colunas [empNumb], [DOBirth], [DORetirement].

Anexando o AdventureWorks baixado da Microsoft


USE [master]
GO
CREATE DATABASE [AdventureWorks2012] ON 
( FILENAME = N'D:\MSSQL\Data\AdventureWorks2012_Data.mdf' ),
( FILENAME = N'D:\MSSQL\Data\AdventureWorks2012_log.ldf' )
 FOR ATTACH
GO

Somos obrigados a ter a "Data de Aposentadoria" ([DORetirement]) para cada funcionário como (DOBirth + 60 anos - 1 dia). Em vez de calcular a cada vez que é chamado um relatório ou atualizar a coluna [DORetirement], executar um gatilho para quando o [DOBirth] é atualizado, temos uma melhor abordagem aqui criando [DORetirement] como uma coluna computada. Desde que esta regra pode ser mudada a qualquer momento estamos implementando-a como uma coluna computada em vez de um valor codificado.


USE [AdventureWorks2012] 
GO 
CREATE TABLE [dbo].[CCtest]
(
[empNumb] [int] NULL,
[DOBirth] [datetime] NULL,
[DORetirement] AS (dateadd(year,(60),[DOBirth])-(1)) PERSISTED
)
GO

Existe a possibilidade de criar a mesma tabela utilizando o SSMS, mas é muito bom manter em mente os códigos. Vamos prosseguir desta forma.


Agora temos o nosso CCtest tabela com uma coluna computada. Da mesma forma, podemos adicionar uma coluna computada a qualquer tabela existente usando o "ALTER TABLE" comando ou abrir a tabela no modo de design usando SSMS e fazer as alterações.

Vamos inserir alguns dados e executar uma consulta para testar a funcionalidade da coluna computada.


USE AdventureWorks2012
GO
INSERT INTO CCTest (empNumb, DOBirth)
SELECT 30, '1985-12-13'  UNION ALL
SELECT 25, '1980-11-18 ' UNION ALL
SELECT 21, '1978-01-19 ' UNION ALL
SELECT 7, '1985-12-13 ' UNION ALL
SELECT 5, '1975-07-23 '
GO
SELECT * FROM dbo.CCTest
GO



Para verificar se a coluna computada será atualizado para quaisquer atualizações, vamos atualizar [DOBirth] para [empNumb] 25.


UPDATE CCtest
SET DOBirth = '1960-03-25'
WHERE empnumb = 25
GO
SELECT * FROM dbo.CCTest
WHERE Empnumb = 25
GO



Persistência (PERSISTED)
Você deve ter notado que nós também usamos a propriedade "persisted" para a nossa coluna computada. Esta propriedade para colunas computadas foi introduzido no SQL Server 2005 e em diante. É importante para qualquer coluna computada, pois muitas características adicionais dependem dele. Para ser capaz de fazer uma coluna computada como persistentes que tem que ser determinista.

Aqui estão algumas regras:

  • Se a propriedade PERSISTED não foi configurada então coluna calculada será apenas uma coluna virtual. Não há dados para esta coluna serão armazenados no disco e os valores serão calculados a cada momento que for referenciado em um script. Se esta propriedade é definida como ativa então os dados de coluna computada serão armazenados no disco, aumenta-se o espaço em disco, porém diminui a necessidade de processamento do servidor.
  • Qualquer atualização na coluna referenciada serão sincronizadas automaticamente na coluna computada se é ela persistente.
  • Junto com algumas outras condições em PERSISTED são necessárias para criar um índice na coluna computada.

Nulidade(Nullibility)
Nullibility para um valor de coluna calculada será determinada pelo Engine do SQL em si. O resultado de uma coluna NOT NULL referenciado pode ser NULL em determinadas condições para evitar possíveis erros de excessão ou sobrecargas. É possível proporcionar um valor alternativo para NULL utilizando o ISNULL (check_expression, constante), se necessário.

Algumas limitações

  • Para o SQL Server 2000 você não pode criar uma coluna computada persistente.
  • Você não pode fazer referência a colunas de outras tabelas para uma expressão de coluna computada.
  • Você não pode inserir ou atualizar declarações em colunas computadas.
  • Se você estiver combinando operadores de dois tipos diferentes de dados em sua expressão então o operador de menor precedência será convertido para o de maior precedência. Se a conversão implícita não for possível, então erro será gerado.
  • Uma subconsulta não pode ser usado como uma expressão para criar uma coluna computada.
  • As colunas computadas podem ser utilizadas em  SELECT, WHERE ou ORDER BY  e como expressões regulares, mas para usar uma coluna computada como chave CHECK, estrangeiros ou não restrições NULL você tem que configurá-lo para PERSISTED.
  • Para usar uma coluna computada como PRIMARY ou UNIQUE KEYdeve ser definido por uma expressão determinística e tipo de dados de expressão de coluna computada deve ser indexável.

Próximos Passos

  • Durante o planejamento para qualquer coluna computada, por favor, tenha em mente que, apesar de uma coluna computada persistente irá reduzir a sobrecarga para os cálculos em tempo de execução ele vai consumir mais espaço em disco.
  • Para obter nulidade de qualquer coluna computada em uma tabela, use a função COLUMNPROPERTY com a propriedade AllowsNull.
  • Criando índices em colunas computadas requer certas condições para ser cumprida. Para mais detalhes sobre essas condições visite BOL (Books On Line)
Como sempre, não quero esgotar o assunto aqui, mas comentários e sugestões são bem vindas.

Até o próximo post!

sábado, 24 de novembro de 2012

TRATAMENTO DE NULL (NULL IN FIELDS)


Uma query  (SELECT) retorna do banco de dados todas as linhas para as quais a cláusula WHERE retorna True (verdadeiro). Muitos desenvolvedores se confundem assumindo que a consulta retornará todos os registros para os quais a cláusula WHERE retorne qualquer valor diferente de False (Falso). Isto é uma causa comum de relatórios incompletos.

Para explicar a razão, considere que a linguagem SQL usa uma lógica de três valores: True, False e Null (Nulo). Qualquer valor Null em uma expressão torna toda a expressão Null (com algumas exceções citadas abaixo).

Exemplo: O senso comum sugere que o SELECT abaixo retorne todas as linhas em uma tabela:

SELECT * FROM Pedidos WHERE (total < 1000 Or total >= 1000)

O SELECT não incluirá os registros para os quais o campo Total é Null, porque este valor faz com que toda a expressão contida na cláusula WHERE seja avaliada como Null.

SELECT * FROM Pedidos WHERE Total = 0

A consulta acima retorna todos os pedidos para os quais Total é zero, mas não aqueles para os quais o Total é Null. Se você quer incluir estes últimos, precisa pesquisar explicitamente pelos valores Null:

SELECT * FROM Pedidos WHERE total = 0 OR total IS NULL

ISNULL é uma função  T-SQL que é constantemente usada para lidar com valores Null. Simplesmente, ela sempre retorna o seu primeiro argumento exceto quando ele é Null (neste caso ela retorna o segundo argumento). Veja como você pode reescrever a consulta acima para lidar com valores Null:

-- converte valores Null para zero antes de compará-los
SELECT * FROM Pedidos WHERE ISNULL(total, 0) = 0 

Além disto, a T-SQL estende o padrão ANSI 92 e suporta Null também em cláusulas IN, de modo que você pode reescrever a consulta acima como abaixo:

SELECT * FROM Pedidos WHERE total IN (0, NULL)

Podemos, também, fazer a consulta inversa, procurando na tabela todos os registros cujo campo especificado seja diferente de Null.

SELECT * FROM Pedidos WHERE total IS NOT NULL

Agora, imagine se você quiser usar o comando CASE para testar os resultados com valor Null.

x
1
2
NULL
4
5

Este seria o método de entendimento direto de como fazer:


SELECT x,CASE x
WHEN NULL THEN ‘sim’
ELSE ‘nao’
END AS resultado
FROM Tabela


O resultado esperado seria:

x resultado
1 nao
2 nao
NULL sim
4 nao
5 nao

Mas ao invés disto teremos exatamente esse resultado:

x resultado
1 nao
2 nao
NULL nao
4 nao
5 nao

Mas então, porque não funcionou? Eu respondo:
NULL é desconhecido, NULL não é igual a NULL, e NULL é a "coisa" mais estranha que pode existir num banco de dados, comum sim, normal não.


Este seria o código exato:

SELECT x,
CASE
WHEN x IS NULL THEN ‘sim’
ELSE ‘nao’
END AS resultado
FROM Tabela



Espero que tirem proveito deste conhecimento. Até mais!


terça-feira, 20 de novembro de 2012

Campo de CNPJ usando notação científica (Scientific Notation for largue numbers)


Já tive vários problemas em realizar consultas em banco de dados com campo de CNPJ que gerassem outras tabelas, ou para relatório ou para concatenação de dados.
Não vou postar aqui hoje as telas, mas apenas o código que utilizei para resolver o problema.



SELECT CAST(CAST(CNPJ AS NUMERIC) AS NVARCHAR(15)) FROM TABELA_DADOS

A idéia aqui é Mudar o formato do campo para NUMERIC, deixando ele assim em “plain text” e depois Mudar novamente para NVARCHAR(15) para ficar texto. Eu sei que campos de CNPJ só tem 14 digitos, porém por segurança, sempre ponho esse byte a mais. Padrão meu, para os bancos de dados que tenho trabalhado esse byte “a mais” não tem afetado desempenho e nem feito aumentar de modo significativo meu banco de dados.

Assim que eu tiver algumas telas para exemplificar, eu posto…
sqlserver_sql_server_2008_logo

sábado, 17 de novembro de 2012

Primeiro contato com SCRUM (Knowing Scrum)


Scrum é uma metodologia ágil para gestão e planejamento de projetos de software. Se você chegou até aqui interessado em fazer uma das certificações disponíveis para Scrum, veja por que dizemos não à certificação?
No Scrum, os projetos são dividos em ciclos (tipicamente mensais) chamados deSprints. O Sprint representa um Time Box dentro do qual um conjunto de atividades deve ser executado. Metodologias ágeis de desenvolvimento de software são iterativas, ou seja, o trabalho é dividido em iterações, que são chamadas de Sprints no caso do Scrum.
As funcionalidades a serem implementadas em um projeto são mantidas em uma lista que é conhecida como Product Backlog. No início de cada Sprint, faz-se um Sprint Planning Meeting, ou seja, uma reunião de planejamento na qual o Product Ownerprioriza os itens do Product Backlog e a equipe seleciona as atividades que ela será capaz de implementar durante o Sprint que se inicia. As tarefas alocadas em um Sprint são transferidas do Product Backlog para o Sprint Backlog.
A cada dia de uma Sprint, a equipe faz uma breve reunião (normalmente de manhã), chamada Daily Scrum. O objetivo é disseminar conhecimento sobre o que foi feito no dia anterior, identificar impedimentos e priorizar o trabalho do dia que se inicia.
Ao final de um Sprint, a equipe apresenta as funcionalidades implementadas em umaSprint Review Meeting. Finalmente, faz-se uma Sprint Retrospective e a equipe parte para o planejamento do próximo Sprint. Assim reinicia-se o ciclo. Veja a ilustração abaixo:


Resolvendo fazer o curso, eu recomendo o site http://tiexames.com.br/curso_Scrum_Master.php

Site Oficial: http://www.scrum.org/assessments/

Otimizar a comunicação entre áreas com o BPM é o segredo de uma gestão saudável (Optimizing follow-up with BMP)


Em entrevista recentemente compartilhada neste link, Cassiana Bovo, especialista em gestão da qualidade estratégica e consultora empresarial, falou sobre a importância do Mapeamento de Processos na busca pela melhoria contínua das atividades executadas pelas empresas. Ela ressaltou que as empresas geralmente buscam o Mapeamento de Processos quando estão com sérios problemas de comunicação e que com a implantação da metodologia esses problemas podem ser evitados.
Segundo Cassiana, as palavras comunicação e informatização têm sido nos últimos anos foco do mundo empresarial. “Os empresários, buscando otimizar a produtividade, encontram na informatização um meio de controlar, monitorar e agilizar o processo produtivo, seja ele no meio industrial, varejo e atacado ou serviços”, comenta a consultora.
Atualmente a utilização de ferramentas BPM e de metodologias que agregam facilidades à sua implantação, como o Mapeamento de Processos, por exemplo, são alternativas que as empresas buscam a fim de otimizar seu trabalho e, a partir de um processo de melhoria contínua, alcançar bons resultados. “Uma tendência, que já é realidade nas grandes corporações, e está se tornando acessível às pequenas e médias empresas, é a implantação do Mapeamento de Processos (BPM) como ferramenta para reduzir as falhas de comunicação entre as diversas áreas das empresas”, explica Cassiana.
De acordo com a especialista em gestão da qualidade, a utilização de uma ferramenta BPM deve estar intimamente ligada ao planejamento estratégico das empresas, para que possa ser efetiva na busca pela melhoria contínua dos processos realizados. “A adoção do BPM traz resultados significativos no tangente aos níveis tático e estratégico das organizações, independente de seu porte, pois roteiriza o caminho a ser percorrido (do processo), facilita o controle e monitoramento dos processos através de indicadores, promove a rápida detecção de não conformidades e melhorias reduzindo custos administrativos e produtivos, evita que as informações se percam durante a realização das atividades da empresa e promove uma fácil visualização das responsabilidades e atribuições de cada setor”, completa.
“O mapeamento de processos (BPM), por seus diversos benefícios, proporciona uma melhor organização e sistematização das atividades da empresa, assim como melhora a comunicação entre os indivíduos e setores, propiciando um ambiente favorável para uma gestão saudável e a obtenção de bons resultados”, finaliza Cassiana.
Saiba mais sobre Cassiana Bovo: Administradora, Consultora Empresarial e da Qualidade, Especialista em Gestão Estratégica e Qualidade, Cassiana Bovo é sócia da 3DK Comunicação e Consultoria; e Diretora de Negócios do Empresas S/A.

Crie cenários de redes mais complexas com o simulador GNS3 (Create a complex network with a simulator)


Todos conhecem o Packet Tracer, que na minha opinião é um excelente simulador e deve ser utilizado por todos aqueles que estão começando seus estudos ou se preparando para o exame do CCNA. No entanto, para aqueles que estão estudando para um nível mais avançado o Packet Tracer não atende a todas as necessidades, pois cenários de redes mais complexas não podem ser criados no Packet Tracer.
Então, se você está estudando para algum exame Cisco depois do CCNA quais caminhos pode tomar:
  1. Montar um lab próprio, com equipamentos reais, em sua casa. Para aqueles que possuem uma boa situação financeira até pode ser um bom caminho, mas no final o custo acaba não sendo nada barato.
  2. Procurar por empresas que aluguem laboratórios para estudo. Essa pode ser uma tarefa dificil, pois aqui no Brasil esse tipo de serviço não é muito comum, além da incoveniência de ter que estudar com hora marcada e tempo de acesso limitado.
  3. Essa é a minha preferida – utilizar o simulador de redes GNS3.

Mas o que é o GNS3?

O simulador GNS3 é um software grátis (isso mesmo, grátis), open source, e que pode ser baixado e utilizado livremente. O GNS3 funciona com imagens IOS da Cisco reais, que são emuladas através de um programa chamado Dynamips. Podemos dizer que o GNS3 é a interface gráfica para o Dynamips, que é o programa que faz todo o trabalho pesado de emular os equipamentos utilizando IOS reais da Cisco.
Com sua interface gráfica intuitiva e bem fácil de se utilizar o GNS3 se mostra como uma poderosa ferramenta, capaz de emular redes complexas e que pode ser utilizada por todos aqueles que estejam em busca de uma certificação avançada da Cisco.

O que podemos fazer com o GNS3

De acordo com o website do GNS3 veja algumas das características desse poderoso simulador:
  • Design de topologias de redes complexas
  • Emulação de muitas plataformas de roteadores Cisco router e PIX firewall
  • Simulação de switches ethernet simples, ATM e Frame Relay
  • Conexão da rede simulado com o mundo real
  • Captura de pacotes utilizando o Wireshark

Plataformas Suportadas pelo GNS3

Na lista a seguir, citamos algumas das plataformas atualmente suportadas pelo GNS3. Como você pode notar, podemos utilizar uma grande quantidade de equipamentos para construir topologias complexas com o GNS3.
  • 1710, 1720, 1721, 1750, 1751, 1760
  • 2610, 2610XM, 2611, 2611XM, 2620, 2620XM, 2621, 2621XM, 2650XM, 2651XM, 2691
  • 3620, 3640, 3660, 3725, 3745
  • 7200

Utilizando o Simulador de Redes GNS3

Abaixo mostramos algumas telas do GNS3, apenas para vocês terem uma breve idéia de como é essa ferramenta.
Essa primeira figura mostra a tela inicial do programa. No lado esquerdo da tela temos todos os equipamentos que poderão ser utilizados. Basta clicar e arrastar para área principal central, onde a topologia será construida.
Abaixo temos um breve exemplo de uma topologia com 3 roteadores em uma rede Frame-Relay.
Por último mostramos um saída do comando show run em um dos roteadores.

Considerações Finais

Como podemos notar é possível fazer muita coisa com o GNS3. Você será capaz de montar topologias diversas, com diferentes níveis de complexidade e com as características necessárias para o exame de certificação específico que estiver estudando.
Mas como tudo na vida, temos o lado bom e o lado ruim. Um dos principais pontos negativos é que você irá precisar de IOS Cisco reais. O GNS3 não vem com imagens IOS embutidas no simulador, ou seja, você deverá ter disponível os IOS que irá utilizar e informar o caminho da pasta para o GNS3, caso contrário o simulador não irá funcionar.
Para baixar essas imagens diretamente do site da Cisco você deverá possuir uma conta CCO. É claro que existem outras fontes de onde conseguir imagens IOS da Cisco, mas definitivamente nós não recomendamos essa alternativa. O recomendado é que você realmente utilize imagens IOS baixadas diretamente da Cisco.
Outro ponto importante é a quantidade de recurso de CPU e memória RAM consumidos ao se utilizar GNS3. Quanto mais complexa a topologia montada, maior quantidade de memória e CPU será consumida. Portanto, saiba que para rodar esse simulador será necessário um computador com uma certa robustez.
Portanto é isso aí… utilizem que vale a pena! E em breve vamos postar mais artigos sobre o GNS3 e algumas de suas facilidades.
Fonte: Blog da DlteC