Mundo.Net: Windows 7, .Net 4, Azure e Service Broker!

Já faz um bom tempo que sou revisor dos artigos da revista Mundo.NET junto com Eduardo Ordine, Marcio Gomes, Zavaschi e Daniel Oliveira. E quando o pessoal da revista lembra-se da minha existência, me mandam um exemplar (valeu Carol!!!). Na semana passada recebi a revista número 21, que trouxe matérias interessantes sobre Windows 7 e Windows Phone 7 e artigos sobre Framework .NET 4 e Azure.

Só consegui ler/reler estes artigos hoje…

Mas uma matéria que faço questão recomendar, é sobre o Caso de Sucesso do MVP Gustavo Maia Aguiar sobre Implementação de Mensageira no SQL Server (Service Broker), que até eu que tenho uma aversão severa de Service Broker, gostei muito!

Realmente ficou muito boa esta edição, para saber mais, confira a Revista Mundo.Net: Edição 21!

Revista Mundo.Net:
http://www.mundodotnet.com.br

Anúncios

Formatando valores numéricos, CPF e CNPJ

Boa noite pessoas!

Como prometi na semana passada, hoje estarei demonstrando algumas formas de formatar valores numéricos e aplicar máscaras para dados como CNPJ e CPF. Antes de tudo, quero que vocês levem em consideração algumas recomendações importantes (do Gustavo Maia Aguiar):

“Não acho muito prudente que isso seja feito no banco de dados. Normalmente a aplicação possui diversas funções de formatação muito mais poderosas do que qualquer SGBD será. Isso é natural, pois, o SGBD tem como premissas básicas gravar e recuperar dados. A formatação fica por conta da apresentação e não por conta do banco.

Vejamos a simples conversão de um INT (4 bytes) para um número com 5 casas decimais. Um número como 1000 (INT – 4bytes) passará para um CHAR de 10 posições 1000.00000. Isso significa mais ciclos de CPU para fazer a conversão, mais memória para armazenar e mais rede para trafegar (6 bytes a mais).

Parece bobo e inocente, mas multiplique a utilização da formatação no banco de dados para todas as consultas e sistemas e aí sim as coisas começam a ficar mais perceptíveis.

Por essas e outras, se possível eu colocaria a formatação na aplicação e não no banco de dados.”

(Gustavo Maia Aguiar, http://social.msdn.microsoft.com/Forums/pt-BR/520/thread/243f12e7-75c8-483e-b3b3-afe898d32ef0)

Infelizmente não é recomendado criar rotinas de conversão e máscaras no banco de dado quando há a possibilidade de realizá-las na aplicação, mas sabemos que nem sempre podemos seguir estas recomendações, principalmente quando o banco de dados não possui entre a aplicação uma camada (WebService ou DLL) para aplicar questões de máscara, validações, conversões de dados e regras de negócio, tornando necessário centralizar estas rotinas dentro do banco de dados.

Feitas as recomendações, uma forma para aplicar um número específico de casas decimais para valores numéricos é a conversão para DECIMAL, definindo a escala e a precisão utilizada, exemplo:

-- Duas casas após a vírgula
SELECT CAST(89 AS DECIMAL(4, 2))
-- Três casas após a vírgula
SELECT CAST(89 AS DECIMAL(5, 3))
-- Duas casas após a vírgula, já no formato de texto
SELECT CAST(CAST(89 AS DECIMAL(4, 2)) AS CHAR)
-- Três casas após a vírgula, já no formato de texto
SELECT CAST(CAST(89 AS DECIMAL(5, 3)) AS CHAR)

Não se esqueça de definir a escala e a precisão do tipo decimal utilizado de acordo com o valor esperado, mesmo que um tipo “DECIMAL(9,2)” já dê conta de boa parte das situações, mais detalhes: http://msdn.microsoft.com/en-us/library/ms187746.aspx

Antes de tratar sobre como formatar CPF e CNPJ, vamos falar um pouco sobre a modelagem. Uma boa recomendação é o uso informações de CNPJ e CPF em formato numérico no banco de dados e sem máscara e se possível em um tipo de dado numérico, para evita utilizar memória desnecessária no armazenamento, no processamento e na comunicação com a aplicação. Para seguir esta recomendação, já vi sistemas utilizarem de tipos decimais (com precisão e escala para definir o tamanho do campo) e flutuantes (float, real), mas para estes tipos têm respectivamente as seguintes desvantagens utilizam espaço desnecessário e não permitem consistência adequada deste tipo de informação, desta forma para campos de CPF e CNPJ recomendo o uso de inteiros (bigint), assim como para outros códigos numéricos, mas sempre verifique o range dos tipos que você irá utilizar (http://msdn.microsoft.com/pt-br/library/ms187745.aspx).

Para armazenar CPF e CNPJ em um único campo, dois tipos de modelagem eu já tive a oportunidade de ver ser utilizada, a primeira com um campo adicional (boleando, bit) para identificar pessoa jurídica/física, que também era utilizado para questões de validar os campos de RG ou IE/IM. Em outra situação observei o uso de CPF com range negativo e CNPJ com range positivo.

Mas independente da modelagem que você utilize, sempre deixe estas definições bem claras aos outros profissionais envolvidos.

Quando utilizamos tipos inteiros para CPF e CNPJ, teremos o problema dos dados que começam com 0 (ex.: 000.094.093-84, que será salvo como 9409384), o que não permite utilizar uma formatação mais simples como:

DECLARE @CPF CHAR(11)
SET @CPF = 9409384
SELECT SUBSTRING(@CPF,1,3) + '.' + SUBSTRING(@CPF,4,3) + '.' + SUBSTRING(@CPF,7,3) + '-' + SUBSTRING(@CPF,10,2) -- #ERRO

A melhor forma seria enviar para a camada de aplicação tratar este valor, mas como algumas vezes não é possível, uma rotina que já supri esta necessidade é:

-- Para formatar CPF:
DECLARE @CPF VARCHAR(11)
SET @CPF = 9409384
SET @CPF = REPLICATE(0, 11 - LEN(@CPF)) + @CPF
SELECT SUBSTRING(@CPF,1,3) + '.'
+ SUBSTRING(@CPF,4,3) + '.'
+ SUBSTRING(@CPF,7,3) + '-'
+ SUBSTRING(@CPF,10,2)
-- Para formatar CNPJ:
DECLARE @CNPJ VARCHAR(14)
SET @CNPJ = 9409310084
SET @CNPJ = REPLICATE(0, 14 - LEN(@CNPJ)) + @CNPJ
SELECT SUBSTRING(@CNPJ,1,2) + '.'
+ SUBSTRING(@CNPJ,3,3) + '.'
+ SUBSTRING(@CNPJ,6,3) + '/'
+ SUBSTRING(@CNPJ,9,4) + '-'
+ SUBSTRING(@CNPJ,13,2)

Também desenvolvi uma rotina genérica que pode aplicar mascaras em valores numéricos, conforme abaixo:

DECLARE @VALOR BIGINT
, @MASCARA VARCHAR(40)
, @RESULTADO VARCHAR(40)
, @VALOR_TMP VARCHAR(40)
SELECT @VALOR = 9409384,
@MASCARA = '999.999.999-99',
@VALOR_TMP = @VALOR
;WITH SPLIT_MASCARA AS
(
SELECT 1 AS ID, SUBSTRING(@MASCARA, 1, 1) AS LETRA
UNION ALL
SELECT ID + 1, SUBSTRING(@MASCARA, ID + 1, 1)
FROM SPLIT_MASCARA
WHERE ID < LEN(@MASCARA)
)
, SPLIT_VALOR AS
(
SELECT 1 AS ID, SUBSTRING(@VALOR_TMP, 1, 1) AS LETRA
UNION ALL
SELECT ID + 1, SUBSTRING(@VALOR_TMP, ID + 1, 1)
FROM SPLIT_VALOR
WHERE ID < LEN(@VALOR_TMP)
)
, TRATAMENTO_MASCARA AS
(
SELECT
ID,
CASE WHEN LETRA = '9'
THEN '0'
ELSE LETRA
END AS LETRA,
CASE WHEN LETRA = '9'
THEN ROW_NUMBER() OVER (PARTITION BY LETRA ORDER BY ID)
ELSE 0
END AS ID_TMP
FROM SPLIT_MASCARA
)
, TRATAMENTO_VALOR AS
(
SELECT
(SELECT COUNT(*) FROM SPLIT_MASCARA WHERE LETRA = '9')
- (SELECT COUNT(*) FROM SPLIT_VALOR)
+ ID
AS ID_TMP,
LETRA
FROM SPLIT_VALOR
)
, RESULTADO AS
(
SELECT ID, ISNULL(V.LETRA, M.LETRA) AS LETRA
FROM TRATAMENTO_MASCARA M
LEFT JOIN TRATAMENTO_VALOR V
ON V.ID_TMP = M.ID_TMP
)
SELECT @RESULTADO = (SELECT LETRA + '' FROM RESULTADO ORDER BY ID FOR XML PATH(''))
PRINT @RESULTADO

Na verdade esta última rotina fiz mais como um desafio para evitar falar de SQL-CLR e ‘brincar’ com CTE. Pois em C# ou VB.NET seria um “pulo” para desenvolver uma rotina para aplicar máscaras. Mas… CTE Rocks!!!

Então pessoal, só para revisar:

  • Formatações, validações e regras de negócio (se possível) somente na aplicação ou em uma camada intermediária (WebService ou DLL).
  • Somente armazene dados numéricos com máscara quando for realmente necessário.
  • Lembre de sempre validar as informações sobre a alocação de memória para os tipos de dados utilizados, evitando problemas de “overflow”.

Espero que tenham gostado dos exemplos e as dicas que eu trouxe até vocês. Na próxima semana volto a tratar questões de configurações do SQL Server, então até lá!

Artigos relacionados:

FUNÇÕES: Validação de CNPJ e CPF:
https://sqlfromhell.wordpress.com/2009/11/19/validacao-de-cnpj-e-cpf/

FUNÇÕES: Somente Números (com CTE)
https://sqlfromhell.wordpress.com/2009/11/18/somente-numeros-com-cte/

FUNÇÕES: Somente Números (com WHILE)
https://sqlfromhell.wordpress.com/2009/11/17/somente-numeros-com-while/

SQL Server Day – Grande tecnologia, por grandes profissionais!

sqlserverday

O evento SQL Server Day 2009 trará mais de 12 horas de palestras com os maiores nomes de SQL Server no Brasil. Este primeiro evento será realizado no dia 07/11/2009 começando às 9 hs da manhã, mas como é on-line, o coffee break fica por sua conta!

Pelo post do Zavaschi, a grade do evento será a seguinte:

09:30 – 10:00 – Abertura do Evento
10:00 – 11:00 – Resource Monitor e Policy Management – Vitor Fava e Alexandre Lopes
11:00 – 12:00 – Entenda porque o Query Optimizer é mais esperto que você – Fabiano Amorim
12:00 – 13:00 – SQL Azure Database – Diego Nogare
13:00 – 14:00 – Novos Recursos de Desenvolvimento do SQL Server 2008 – Higor Fernandes
14:00 – 15:00 – Compressão de Dados e Backup no Microsoft SQL Server 2008 – Pedro A. G. Junior
15:00 – 16:00 – Entendendo TDE (Transparent Data Encryption) – Felipe Ferreira
16:00 – 17:00 – Entendendo as Common Table Expressions (CTE) – Thiago Zavaschi
17:00 – 18:00 – Powershell Coletando e Analisando os Dados – Laerte Junior e Thiago Zavaschi
18:00 – 19:00 – Solução Avançada de Problemas com Extended Events – Vladimir Magalhães
19:00 – 20:00 – Disaster Recovery – Backup, Restore e Tópicos Avançados – Gustavo Maia Aguiar
20:00 – 21:00 – Integre seu código .NET com o SQL Server usando o CLR – Roberto Fonseca
21:00 – 22:00 – Analise de Desempenho utilizando as Estatísticas de Espera – Alex Rosa

 

Mais informações:

SQL Server Day – Site Oficial
http://www.sqlserverday.com.br

CTE – Introdução

Para quem já têm acompanhado meus posts, deve ter percebido minha “afeição” às CTEs (Common Table Expression) do SQL Server 2005/2008.

Mas para salvação daqueles que não conhecem este recurso, o Thiago Zavaschi (um ótimo DBA e amigo com o qual tenho a honra de trabalhar na TechResult e no MIC Curitiba-PR) tomou a iniciativa de escrever dois post sobre o assunto (na verdade um post divido em duas partes).

Abaixo o link para os posts:

Entendendo as Common Table Expressions – CTE:
http://tinyurl.com/nbflaf (parte 1)
http://tinyurl.com/lrvmyp (parte 2)

Site do Thiago Zavaschi:
http://www.zavaschi.com/  

Para quem quiser saber ainda mais sobre o assunto, o Gustavo Maia Aguiar escreveu um artigo ótimo sobre o assunto na revista Mundo.Net deste mês:
http://gustavomaiaaguiar.spaces.live.com/blog/cns!F4F5C630410B9865!695.entry 

 

Posts relacionados:

WHILE vs CTE – Popular tabela de testes:
https://sqlfromhell.wordpress.com/2009/09/12/while-vs-cte-popular-tabela-de-testes/

Trabalhando com Datas – Lista de meses e dias da semana:
https://sqlfromhell.wordpress.com/2009/08/15/trabalhando-com-datas-lista-de-meses-e-dias-da-semana/

Arquivo de log gigante?

Para quem sofre com este problema, a prática mais comum no SQL Server 2005 é utilizar o Backup Log Truncate em seguida Shrink (at. http://blog.sqlauthority.com/2006/12/30/sql-server-shrinking-truncate-log-file-log-full/ ), mas será que é realmente a melhor escolha a fazer? Será que não há um motivo para não haver isso no SQL Sever 2008? Será que não vale a pena mudar o Recovery Model do banco de dados para Simple?

Bem quer saber o jeito correto de para resolver o problema com o arquivo de log? Então dê uma boa lida nestes dois tópicos sobre o assunto, escritos pelo MVP Gustavo Maia Aguiar:
Piores Práticas – Utilizar o comando BACKUP LOG com a opção WITH TRUNCATE_ONLY – Parte I
Piores Práticas – Utilizar o comando BACKUP LOG com a opção WITH TRUNCATE_ONLY – Parte II

Já tive uns problemas legais com arquivo de log gigante, que nem com o método Truncate/Shrink deu jeito, mas sempre vale a pena verificar se o plano de manutenção do SQL Server não esta mal estruturado (já resolvi este problema assim), ou se tem replicações “mortas” no banco de dados (um dia, talvez eu entenda o que aconteceu e como resolvi o problema com replicações “mortas” e o arquivo de log, para poder escrever sobre).

Trabalhando com datas – dia/mes/ano

Durante todos os dias me deparo com muitas queries trabalhando com datas, principalmente em T-SQL, algumas bem complexas e outras simples, que muita gente ainda insiste em complicar. O MVP Gustavo Maia Aguiar trabalhou alguns tópicos neste sentido no seu blog, mas o meu objetivo é trabalhar do mais simples (formato da data) até passear um pouco em cálculos complexos com datas nos próximos tópicos.

Neste primeiro post demonstrarei como formatar campos e variáveis do tipo DATETIME para dia/mês/ano, utilizando a função Convert. O motivo de trabalhar isso é pelo fato de encontrar muitas vezes, algo assim:

Cabeçalho para demonstrações:

DECLARE @data DATETIME
SET @data = GETDATE()

Não faça isso:

SELECT CAST(DAY(@data), VARCHAR) + '/' + CAST(MONTH(@data), VARCHAR) + '/' + CAST(YEAR(@data), VARCHAR)

E até mesmo algumas coisas assim:

Também não faça isso:

SELECT
  (CASE WHEN DAY(@data) > 9
    THEN ''
    ELSE '0'
   END) +
CAST(DATE(@data) AS VARCHAR) +
  (CASE WHEN MONTH(@data) > 9
    THEN '/'
    ELSE '/0'
   END) +
CAST(MONTH(@data) AS VARCHAR) +
  '/' +
CAST(YEAR(@data) AS VARCHAR)

Se o objetivo era demonstrar o conhecimento em lógica, realmente os dois exemplos acima já me impressionam, pois não é qualquer um que chega a este raciocínio. Mas vamos para a versão que chamo de forma elegante:

SELECT CONVERT(VARCHAR, @data, 103)

Gostou da simplicidade, dê uma olhada no tópico de CAST e CONVERT da biblioteca do MSDN, que possui uma relação gigante de formatos possíveis.
http://msdn.microsoft.com/en-us/library/ms187928.aspx

Fóruns do MSDN – Upgrade!?

Ganhei mais uma medalhinha!!! Um dia chegou a +2000 respostas igual ao Gustavo Maia Aguiar!rsss 

Upgrade

Bem estou gostando do Fóruns do MSDN, pois mesmo que eu escreva alguma bobagem, alguém bem mais experiente, como Israel Aece, Gustavo Maia Aguiar, Junior Galvão e outros MVPs (e gente que nem na hora de almoçar larga o computador, como o pessoal que conheci no Community Zone), sempre estará lá para puxar minha orelha!rsss

O site do MSDN já me salvou muitas vezes, principalmente quando trabalhei com tecnologias como Microsoft Office Interop e Share Point e também quando preciso caçar alguma funcionalidade do SQL.

Esta sendo legal investir meu tempo e aprender mais ajudando o pessoal nos fóruns, e pelo número de respostas devo estar ajudando com o pouco de conhecimento que tenho.