quinta-feira, 22 de dezembro de 2011

Dia da semana por extenso utilizando CASE

Aqui vai uma dica simples, mas que pode ajudar ao criar relatórios ou consultas mais detalhadas.

O script SQL abaixo utiliza o comando CASE e a função de data DATEPART do próprio SQL Server para exibir o dia da semana por extenso para uma determinada data.

SELECT 
CASE DATEPART(w, GETDATE()) 
WHEN 1 THEN 'Domingo'
WHEN 2 THEN 'Segunda-feira'
WHEN 3 THEN 'Terça-feira'
WHEN 4 THEN 'Quarta-feira'
WHEN 5 THEN 'Quinta-feira' 
WHEN 6 THEN 'Sexta-feira'
WHEN 7 THEN 'Sábado'
END AS 'Dia da Semana'

Até a próxima!

terça-feira, 29 de novembro de 2011

UPDATE com incremento


O script abaixo realiza uma tarefa interessante. Através dele é possível atualizar as linhas de uma tabela (ou um conjunto de linhas, caso um filtro seja aplicado) com valores incrementais (o valor inicial e o incremento podem ser definidos).


-- Criando tabela temporária para testes
CREATE TABLE #tmp (id int primary key identity(1,1), counter int default 0)
GO

-- Inserindo dados na tabela
INSERT INTO #tmp DEFAULT VALUES
GO 10

-- Veja como está a tabela
SELECT * FROM #tmp

-- PREENCHENDO O CAMPO counter  COM VALORES INCREMENTAIS
DECLARE @counter int
SET @counter = 0

UPDATE #tmp
SET @counter = counter = @counter + 1

-- Veja novamente a tabela com o campo counter preenchido
SELECT * FROM #tmp

Este script pode ser utilizado para popular qualquer campo que apresente um caráter sequencial, independente do incremento a ser utilizado.

Através dele, pode-se evitar o uso de Cursores e laços While ao fazer atualizações grandes em tabelas. 

Já recorri a ele algumas vezes. Foi bastante útil!

Até a próxima!

domingo, 27 de novembro de 2011

E-Book gratuito sobre o SQL Server 2012

A Microsoft liberou uma versão prévia do e-book com as novidades no SQL Server codename Denali, agora oficialmente chamado de SQL Server 2012.

O livro é uma excelente oportunidade para aqueles que querem conhecer e se preparar para a futura versão do SQL Server. O download é gratuito e o livro está em inglês.

Segue a URL para download direto do site do MSDN:


Boa leitura! =)

sábado, 26 de novembro de 2011

O que há de novo no SQL Server 2012

Este artigo tem como objectivo reunir conteúdo sobre a nova versão do SQL Serve 2012. A Microsoft já liberou um pacote release candidate nesta semana.
Atualizarei este artigo conforme encontrar mais informações relacionadas.

Notas da versão SQL Server 2012

Conheça as principais novidades da nova versão do SQL Server 2012

SQL Server 2012 What's New Whitepaper

SQL Server 2012 RC0

A primeira versão Release Candidate do SQL Server foi liberada nesta semana no site oficial da microsoft!

Durante este ano (2011), a Microsoft trabalhou em uma versão CTP com codinome "Denali", recolhendo feedbacks de usuários e aprimorando as funcionalidades do novo SQL Server.

Para maiores informações, acesse o site oficial:


SQL Server 2012 RC0 is Here!

Para baixar diretamente o pacote e testar a nova release, utilize a URL abaixo.
É necessário utilizar um Windows Live ID (conta do Hotmail, MSN Messenger ou Passport).


Aguarde! Em breve, publicarei mais novidades!
Até a próxima!

quarta-feira, 14 de setembro de 2011

Exclusão de colunas com valor default


Ao excluir uma coluna com valor default atribuído em uma tabela, o seguinte erro é disparado pelo SQL Server:

The object 'DF__Contato__ativo__6166761E' is dependent on column 'Ativo'.

Isso acontece porque antes de excluir uma coluna de uma tabela, é necessário certificar-se que ela não é referenciada por nenhuma chave ou restrição e também não tenha um valor default associado a ela.

Para resolver este problema, pode-se utilizar a stored procedure de sistema sp_unbindefault . Veja o exemplo abaixo:

-- Criando o valor default para o banco de dados
CREATE DEFAULT DF_Ativo AS 0
GO

-- Criando tabela sem o valor default associado
CREATE TABLE Contato
(
id int identity primary key
,nome varchar(50) not null
,ativo bit
)

-- Associando o valor default à coluna Ativo da tabela
EXEC sp_bindefault 'DF_Ativo','Contato.ativo'

-- Removendo o valor default da coluna Ativo
EXEC sp_unbindefault 'Contato.Ativo'

-- Excluindo coluna Ativo
ALTER TABLE Contato
DROP COLUMN Ativo

-- Verificando se a coluna foi excluída
SELECT * FROM Contato


O código acima retorna as seguintes mensagens:

Default bound to column.
Default unbound from table column.

E o comando SELECT retorna o seguinte resultado:

id nome
----------- --------------------------------------------------

Entretanto, esta procedure funciona apenas se o valor default foi associado através da stored procedure sp_bindefault. Quando um valor default é atribuído durante a criação da tabela ou através do comando ALTER TABLE, a procedure sp_unbindefault retorna uma mensagem de erro como abaixo:

Cannot unbind from 'Contato.Ativo'. Use ALTER TABLE DROP CONSTRAINT.

Seria fácil executar o comando ALTER se soubéssemos o nome da restrição gerada automaticamente pelo SQL Server.

Assim, para facilitar esta tarefa implementei o script abaixo. Ele remove a restrição de default utilizando apenas o nome da tabela e o da coluna.
Para utilizá-lo, basta configurar as variáveis @nomeTabela e @nomeColuna, conforme indicado:

DECLARE @nomeTabela VARCHAR(50)
DECLARE @nomeColuna VARCHAR(50)

/* Configure aqui */

   SET @nomeTabela = 'Contato'
   SET @nomeColuna = 'Ativo'

/* Fim das configurações */

DECLARE @command NVARCHAR(1000)

SELECT
    @command='ALTER TABLE '+@NomeTabela+' DROP CONSTRAINT '+D.name
FROM
    sysobjects D
        inner join sysobjects T on
           T.id = D.parent_obj
        inner join syscolumns C on
           C.id = T.id AND cdefault = D.id
WHERE
    T.name = @nomeTabela
    AND C.name = @NomeColuna
    AND D.xtype = 'D'

exec sp_executesql @command

Este script utiliza as views de sistema sysobjects e syscolumns para encontrar o nome da restrição que atribui o valor default à coluna. Com este valor, o comando de exclusão da restrição é gerado e executado.

Este script foi útil para você? Deixe o seu feedbacks abaixo através de comentários!

Até a próxima!

terça-feira, 30 de agosto de 2011

SQL Server Code Name “Denali” Feedback Challenge

A Microsoft iniciou uma campanha para obter feedbacks sobre a futura versão do SQL Server.

Segundo anunciado no site blog Oficial do SQL Server, cada um dos 300 primeiros usuários a reportar bugs e sugestões de melhoria deve receber presentes relacionados ao SQL Server no valor de 30 dólares.

Para aqueles que já estão testando a nova versão, esta é uma boa chance de ter uma sugestão ouvida e atendida pela Microsoft.

Maiores informações podem ser encontradas diretamente no blog do SQL Server, através do endereço:


Se você ainda não conhece a futura versão do SQL Server, publiquei alguns artigos recentemente com referências para o conteúdo oficial da Microsoft. Basta acessar a URL abaixo:


Boa sorte!

quarta-feira, 13 de julho de 2011

SQL Server Denali - O CTP3 já está disponível!

Está disponível para download no site da Microsoft a versão Community Technology Preview 3 (CTP3) do SQL Server, com code-nome Denali.

A nova versão pode ser baixada em 32 ou 64 bits e é possível escolher o idioma Português!


Para esta versão, vale a mesma ressalva do CTP1: Embora aprimorada, esta ainda é uma versão em fase de testes e pode conter bugs e instabilidades...

Comentários e feedbacks sobre o CTP3 são muito bem-vindos!

Até a próxima!

quarta-feira, 1 de junho de 2011

Inserindo múltiplas linhas com um único comando

Uma das novidades do SQL Server 2008 em relação às versões anteriores é uma sintaxe alternativa para o comendo INSERT que permite a inserção de múltiplas linhas através da execução de um único comando.

A nova estrutura permite declarar várias linhas entre parênteses, separadas por vírgulas. Veja esta opção de sintaxe:

INSERT INTO tabela (coluna1,coluna2, coluna3)
VALUES (valor1,valor2,valor3),(valor4,valor5,valor6),(valor7,valor8,valor9)

Assim, pode-se substituir scripts como este:

INSERT INTO Contato(nome,email,telefone)
   VALUES ('Evandro','evandro@email.com','3456-7890')
INSERT INTO Contato(nome,email,telefone)
   VALUES ('Ronaldo','contato@ronaldo.com.br','8765-5432')
INSERT INTO Contato(nome,email,telefone)
    VALUES ('Mariana','mariana@email.com','3344-9988')
INSERT INTO Contato(nome,email,telefone)
    VALUES ('José','falecom@jose.com','5678-1011')

Por estruturas como esta:

INSERT INTO Contato(nome,email,telefone)
VALUES
   ('Evandro','evandro@email.com','3456-7890'),
   ('Ronaldo','contato@ronaldo.com.br','8765-5432'),
   ('Mariana','mariana@email.com','3344-9988'),
   ('José','falecom@jose.com','5678-1011')

Este modelo já vem sendo utilizado nos exemplos deste blog, pois diminui o tamanho dos scripts gerados e também facilita a vida do desenvolvedor SQL.

Verificar se um Banco de Dados existe em uma instância do SQL Server

Verificar se um banco de dados existe em um servidor SQL Server é uma tarefa simples quando se usa uma ferramenta gráfica ou assistente de conexão ao banco.

Porém, ao criar scripts mais complexos, pode ser necessário verificar manualmente se um banco de dados existe no servidor.

No SQL Server 2008, para cada banco de dados criado em uma instância, um novo registro na view de sistema sys.databases.

Assim, basta consultar esta tabela para realizar a tarefa desejada:

SELECT * FROM sys.databases WHERE name = 'dbTest'

Veja o resultado:


Observe que através da view sys.databases pode-se obter diversas informações sobre as bases de dados de uma instância. Pode-se também usá-la para exibir uma lista dos bancos de dados do servidor. Até a próxima!

terça-feira, 31 de maio de 2011

TechEd Brasil 2011!!! De 29/09 à 01/10 no Expo Center Norte

O TechEd Brasil 2011 já tem data e local definidos!

  • Data - de 29/setembro a 01/outubro de 2011
  • Local - Expo Center Norte - São Paulo - SP 

O TechEd é um evento organizado pela Microsoft realizado todos os anos em diversos locais pelo mundo. Durante o evento, palestras, cursos e demonstrações levam aos profissionais de TI as tendências e últimas tecnologias lançadas pela Microsoft.

Eu já estive em 2 versões do TechEd e posso dizer que vale a pena!

Por enquanto, o site do evento ainda não está atualizado. Nele, pode-se encontrar apenas um texto inicial passando as informações sobre os dias e local. Para ficar por dentro das novidades do evento, basta seguir o twitter @techedbrasil.

Assim que tiver novidades, escreverei sobre as palestras e outros aspectos do evento.

Até a próxima!

Certificações MCTS em SQL Server

Ultimamente tenho postado sobre referências e materiais de estudo para os exames de certificação Microsoft em SQL Server. Entretanto, ficou faltando descrever cada uma das certificações disponíveis para a versão 2008.

Neste artigo, vou descrever brevemente o conteúdo abordado em cada um dos exames das carreiras MCTS - Microsoft Certified Technology Specialist em SQL Server. Para cada certificação, haverá um link para a página oficial do exame, com maiores detalhes para os interessados.

Exame 70-432: TS: Microsoft SQL Server 2008, Installation and Maintenance
Voltada para DBAs SQL Server, o exame engloba assuntos como: Instalação, Configuração de Instâncias e Bancos de Dados, Gerenciamento de Arquivos, Full Text, Monitoramento, Performance Segurança, Alta Disponibilidade (Cluster, Espelhamento, Replicação, Log Shipping), Recuperação de Desastres (Backup e Restore), entre outros assuntos.

Para maiores informações sobre este exame, acesse:


Exame 70-433: TS: Microsoft SQL Server 2008, Database Development
Voltada para Desenvolvedores SQL Server, a prova abrange conteúdos relacionados à Criação de objetos de um banco de dados (Tabelas, Views, Stored Procedures, Funções, Triggers, etc), Manipulação de dados, CTEs, Manipulação de dados XML, Implementações de Full Text, Manipulação de dados geoespaciais, Otimização de consultas, Planos de execução, entre outros tópicos.

Para maiores informações sobre este exame, acesse:


Exame 70-448: TS: Microsoft SQL Server 2008, Business Intelligence Development and Maintenance
Este exame é voltado para o profissional de BI (Business Intelligence). A prova aborda 3 ferramentas principais do SQL Server:
  • SQL Server Analysis Services - utilizada para gerar e consultar cubos em bancos de dados de Data Warehouse, cada cubo pode ser amplamente customizado para atender às necessidades do negócio. Pode-se utilizá-lo como ferramenta de Data Mining.
  • SQL Server Integration Services - ferramenta de integração de dados do SQL Server, permite migrar e transformar dados entre diversas fontes de dados (desde arquivos texto até outros SGBDs).
  • SQL Server Reporting Services - ferramenta de relatório semelhante ao Crystal Reports, permite a criação de relatórios complexos e com excelente visual, acessando diversas fontes de dados. Cada relatório é representado por um arquivo XML com extensão RDL, este arquivo pode ser gerado através de ferramentas gráficas, como o Visual Studio ou o Report Builder.
Neste exame, para cada uma das ferramentas, são cobrados tópicos relacionados à instalação, configuração, implementação, segurança, performance, etc.

Para maiores informações sobre este exame, acesse:


Outros materiais
Aqui no SQL Server - Dicas você já pode encontrar referências para diversos materiais relacionados aos exames acima. Álém disso, muitos dos artigos postados também são relacionados ao conteúdo das provas.

Para ver todos os artigos relacionados aos exames de Certificação em SQL Server, acesse o endereço abaixo:


No momento estou estudando para realizar o exame 70-432, provavelmente postarei mais sobre os assuntos desta certificação. Em seguida, tentarei obter a 70-433 e a 70-448, nesta ordem.

Bom estudo para todos!

sábado, 28 de maio de 2011

Exame 70-432 - Vídeo com dicas!

Quem está estudando para o exame 70-432 pode encontrar um vídeo (em inglês) com os principais tópicos e algumas dicas muito interessantes no site da Microsoft Learning. Para baixar, acesse o endereço:


Para baixar vídeos e outros recursos relacionados aos outros produtos da Microsoft, acesse a página  abaixo (em inglês):


Bom estudo!

Dicas sobre Certificações!

A Microsoft Learning está organizando um evento on-line chamado Microsoft Exam Cram onde durante 1 hora profissionais das tecnologias Windows 7, Windows Server 2008 e  SQL Server 2008 farão apresentações sobre os tópicos avaliados nos principais exames de certificação relacionados a cada uma das tecnologias.

O evento será realizado no dia 8 de junho e as palestras serão em inglês. As incrições são gratuitas e limitadas. Confira mais detalhes em:
 

Na área de SQL Server, as 3 provas de certificação serão abordadas:

70-432 - Microsoft SQL Server 2008, Implementation and Maintenance
70-433 - Microsoft SQL Server 2008, Database Development
70-448 - Microsoft SQL Server 2008, Business Intelligence Development and Maintenance

Para registrar-se, acesse o endereço:


Já efetuei minha inscrição! Estou concluindo meus estudos para a o exame 70-432.

Comando DELETE - Retornando linhas excluídas

Para fins de log ou simples confirmação de sucesso da operação, o comando DELETE pode retornar as linhas excluídas em sua execução.

A cláusula OUTPUT é responsável por esta funcionalidade. Para utilizá-la, basta adicioná-la ao final do comando DELETE e referenciar os campos da tabela virtual (DELETED) gerada após a execução. Veja a sintaxe básica:

DELETE FROM [tabela]
OUTPUT DELETED.*

Além disso, através da cláusula WHERE pode-se filtrar as linhas retornadas. Veja o exemplo abaixo:

-- Criando tabela de teste
CREATE TABLE teste (id int identity (1,1))
GO

-- Populando tabela com 20 registros
INSERT INTO teste DEFAULT VALUES
GO 20

-- Excluindo registros da tabela e retornando apenas os registros -- excluídos com ID < 10.
DELETE FROM teste
OUTPUT DELETED.*
WHERE id < 10

-- Excluindo tabela de testes
DROP TABLE teste
GO

Ao executar este script, um conjunto de dados é retornado com as linhas excluídas cujo id não foi excluído pelo filtro. Repare que não existe um comando SELECT, o próprio comando DELETE retornou os dados:


Este é um importante recurso para a criação de logs e auditoria via banco de dados ou aplicação. Através do conjunto de dados retornado, pode-se registrar o exato momento e usuário responsável pela exclusão do registro.

Para outras informações relacionadas à criação de logs e monitoramento das atualizações em uma base de dados, veja outros artigos:


Em breve teremos mais artigos sobre auditoria e monitoramento de bancos de dados!

Até a próxima!

sexta-feira, 27 de maio de 2011

Encontrar Stored Procedures com um texto específico

Hoje precisei mapear todos os objetos que referenciam uma determinada coluna de uma tabela, pois era necessário avaliar os impactos de uma possível exclusão deste campo. Essa tarefa pode ser facilitada pela Ferramenta de Visualização de Dependências de Objetos do SQL Sever Management Studio, que lista todos os objetos que referenciam o objeto escolhido. Porém, o grande número de stored procedures referenciadas ainda tornaria esta demanda muito demorada e trabalhosa.

Para agilizar esta tarefa, procurei automatizar o processo de busca consultando informações sobre os objetos nas tabelas de sistemas.

Ao criar uma Stored Procedure em um banco de dados SQL Server, as informações referentes à procedure são armazenadas em tabelas de sistema. Apesar de não ser possível alterar os registros armazenados nestas tabelas, o SQL Server permite que os usuários - com as devidas permissões - consultem seus dados através das Views de sistema.

Veja abaixo a idéia utilizada para buscar estas informações:

SQL Server 2008
No SQL Server 2008, o script utilizado para criar a stored procedure fica armazenado no campo ROUTINE_DEFINITION da view de sistema INFORMATION_SCHEMA.ROUTINES. Assim, podemos procurar um texto no script de todas as procedures pertencentes ao banco através do script abaixo:

SELECT
      ROUTINE_SCHEMA
    , ROUTINE_NAME
FROM
    INFORMATION_SCHEMA.ROUTINES
WHERE
    ROUTINE_DEFINITION LIKE '%texto desejado%'

SQL Server 2000
No SQL Server 2000, o script de criação da stored procedure fica armazenado no campo text da view de sistema syscomments. Assim, podemos procurar um texto no script de todas as procedures pertencentes ao banco através do script abaixo:

SELECT
    O.name
FROM
    syscomments C
INNER JOIN sysobjects O ON
    C.id = O.id
WHERE
    text like '%texto desejado%'

As view de sistema contém muitas informações relevantes para facilitar a vida de um DBA ou desenvolvedor SQL Server. Explore-as!

Até a próxima!

quinta-feira, 19 de maio de 2011

[off-topic] Recuperar informações do Assembly em execução

Exibir a versão de uma aplicação em execução pode ser importante para se ter controle do impacto das alterações realizadas entre duas releases ou mesmo para identificar se um patch ou atualização foi aplicado no ambiente de produção.

Através do framework .Net, pode-se controla a versão de um aplicativo simplesmente alterando o número da versão do produto no arquivo Assembly.info, localizado na pasta Properties do projeto. A versão do projeto é representada por um conjunto de 4 números separados por pontos finais ".", cada empresa ou desenvolvedor pode definir regras para incrementar as casas que definem o número da versão.

Além disso, o .Net Framework disponibiliza algumas classes específicas para obter informações do assembly em execução. Veja no exemplo como é fácil recuperar a versão da aplicação em execução.

System.Reflection.Assembly.GetExecutingAssembly().GetName().Version.ToString()

É possível recuperar muitas outras informações utilizando o objeto da classe Assembly (objeto retornado pelo método GetExecutingAssembly()), como por exemplo a localização do próprio assembly, através da propriedade Location.

Explore as propriedades e métodos da classe Assembly sua aplicação pode ficar muito mais flexível e fácil de configurar!

quarta-feira, 4 de maio de 2011

Utilizando Colunas Computadas no SQL Server

Colunas computadas são colunas cujo valor é calculado dinamicamente através de uma expressão que pode ou não  referenciar valores de outras colunas da mesma tabela. Assim, não é possível definir valores para estas colunas durante operações de Insert e Update.

Colunas computadas podem ser utilizadas normalmente em filtros, ordenações e agrupamentos. Entretanto, para utilizá-las como chave estrangeira (foreign keys) e aplicar índices, deve-se utilizar a propriedade PERSISTED, detalhada no final deste artigo.

Ao criar uma tabela com coluna computada, omite-se o tipo de dados dessas colunas e adicona-se a cláusula AS seguida da expressão que define o valor da coluna. Veja abaixo:

CREATE TABLE Tabela
(
      colunaA INT
    , colunaB INT
    , colunaC AS colunaA + colunaB
)

Para exemplificar, utilizaremos uma tabela que armazena informações sobre pessoas. As informações armazenadas serão:

Utilizando essas informações, criaremos duas colunas calculadas para armazenar:
  • idade
  • IMC - índice de massa corporal =  peso / altura²

O script abaixo cria a tabela conforme especificado acima:

CREATE TABLE Pessoa
(
      id INT IDENTITY(1,1) PRIMARY KEY
    , nome VARCHAR(50)
    , dataNascimento DATE
    , idade AS (DATEDIFF(DAY,dataNascimento,getdate())/365)
    , altura FLOAT
    , peso FLOAT
    , imc AS CAST ((peso / (altura * altura)) AS DECIMAl(4,1))
)

Para inserir registros, omite-se as colunas computadas. O script abaixo insere duas linhas em nossa tabela:

INSERT INTO Pessoa VALUES ('João','1947-07-22',1.87,82)
INSERT INTO Pessoa VALUES ('Ricardo','1973-03-08',1.65,77)

Vamos agora selecionar os valores inseridos utilizando o comando abaixo:

SELECT * From Pessoa

Veja o resultado:


Observe que o valor das colunas Idade e IMC foi preenchido automaticamente, de acordo com a expressão definida.

Cuidado!

O SQL Server não permite a inserção ou atualização de valores em colunas computadas, o comando abaixo tenta realizar esta operação inserindo o valor 7 para a coluna IMC:

INSERT INTO Pessoa (nome,dataNascimento,altura,peso,imc) VALUES ('Ricardo','1973-03-08',1.65,77,7)

O comando acima resulta na seguinte mensagem de erro:

Msg 271, Level 16, State 1, Line 1
The column "imc" cannot be modified because it is either a computed column or is the result of a UNION operator.

Além disso, procure utilizar validações na tabela (triggers e contraints) e também nas expressões (ISNULL(), COALESCE(), etc.) para evitar erros de cálculo ao calcular o valor de uma coluna computada. Se a expressão resultar em erro em alguma das linhas do retorno uma constulta, o processamento da consulta é imediatamente encerrado e o erro é lançado. Se inserirmos uma linha com valor 0 para a coluna altura em nossa tabela, causaremos uma divisão por zero. A inserção será realizada, mas ao consultar os dados da tabela, o seguinte erro é lançado:

Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.

A Propriedade PERSISTED
O cálculo da expressão de uma coluna computada pode impactar no desempenho das consultas em uma tabela, principalmente se a expressão realizar operações complexas do ponto de vista computacional.

A proprierdade PERSISTED pode evitar esta perde de desempenho, pois as colunas computadas com esta propriedade armazenam fisicamente o valor no momento em que o registro é inserido ou alterado. Assim, não é necessário efetuar o cálculo novamente.

Mas lembre-se, para utilizar esta propriedade, a expressão que define o valor da coluna deve ser determinística.

sábado, 30 de abril de 2011

Exame 70-433 - Material de Estudo

Para quem está se preparando para o exame de certificação TS: Microsoft SQL Server 2008, Database Development, encontrei um blog com uma iniciativa muito interessante.

Criado por Lucas Romão e Andressa Martins, o blog contém um material de estudo para o exame dividido em capítulos baseados no Kit de Treinamento da Microsoft.

No momento, o material não está concluído, mas a iniciativa é muito boa! Para quem está aprendendo, vale a pena conferir e seguir os artigos publicados.

Segue o link para o blog:

E-book gratuito sobre o SQL Server 2008 R2

O SQL Server codename Denali está para ser lançado, mas muitos ainda não tiveram oportunidade de explorar as melhorias e novas funcionalidades do SQL Server 2008 R2.

A Microsoft Press disponibilizou gratuitamente o e-Book Introducing Microsoft SQL Server 2008 R2, que explora todas as novidades da 2ª release do SQL Server 2008.

O livro está em inglês e é dividido em duas partes - "Database Administration" e "Business Intelligence Development". Para quem tiver interesse, o download pode ser feito no formato PDF ou XPS através do link:

Até a próxima!

quarta-feira, 6 de abril de 2011

Auto-numeração em colunas (IDENTITY) - Parte 3

Para terminar a série de artigos sobre colunas auto-numeradas, seguem duas novas dicas: "Reiniciando uma coluna IDENTITY" e "Especificando o valor para a coluna IDENTITY ao inserir uma linha": 

Reiniciando uma coluna IDENTITY
É possível definir o valor da coluna IDENTITY a ser atribuído à próxima linha inserida na tabela. O comando DBCC CHECKIDENT realiza esta tarefa.

DBCC CHECKIDENT (yourtable, reseed, 34)

Importante: caso seja necessário configurar manualmente o próximo valor para a coluna IDENTITY pertencente a chave primária da tabela,  tome o cuidado de configurar um valor superior a todos os valores já inseridos para esta coluna, evitando conflitos com os registros já existentes. Para garantir que isto realmente acontecerá, pode-se utilizar o comando para encontrar o menor valor permitido para reiniciar o auto-incremento:

SELECT MAX(nome_campo) + 1 FROM tabela

Especificando o valor para a coluna IDENTITY ao inserir uma linha
Por padrão, não é possível definir o valor para uma coluna IDENTITY ao inserir ou atualizar um registro. Ao tentar realizar esta operação, o seguinte erro será lançado:

Msg 8101, Level 16, State 1, Line 7
An explicit value for the identity column in table [tabela] can only be specified when a column list is used and IDENTITY_INSERT is ON.

Veja o erro abaixo:


Entretanto, pode-se utilizar o comando SET para alterar a propriedade IDENTITY_INSERT da tabela desejada e desabilitar temporariamente a validação que causa o erro acima. Assim, os comandos para realizar esta tarefa teriam a seguinte estrutura:

SET IDENTITY_INSERT nome_tabela ON

   /* Comandos para inserir registros na tabela 
   determinando valores para a coluna IDENTITY  */
   INSERT  INTO nome_tabela (campo1,campo2,... ,campoN) 
        VALUES(valor1,valor2,... , valorN)

SET IDENTITY_INSERT nome_tabela OFF

Importante: Ao habilitar a inserção de valores para a coluna IDENTITY, é necessário explicitar o nome das colunas da tabela no comando INSERT.

No exemplo abaixo, a linha que causou erro no exemplo anterior é inserida com sucesso.


OBSERVAÇÃO: Ao inserir uma linha com um valor maior do que a contagem atual para a coluna IDENTITY, a numeração automática será automaticamente modificada para iniciar a partir do valor inserido.

Referências
DBCC CHECKIDENT (Transact-SQL)

quinta-feira, 31 de março de 2011

Microsoft WebSiteSpark

O WebsiteSpark é um programa criado pela Microsoft para impulsionar desenvolvedores que utilizem sua plataforma para criação de web sites e aplicações web.

Através deste programa, a Microsoft oferece gratuitamente diversas de suas ferramentas voltadas para o desenvolvimento e até mesmo para servidores de Produção entre eles o SQL Server Web Edition. Os participantes podem fazer o download dos softwares diretamente do site do programa, basta preencher um cadastro informando os detalhes da empresa onde trabalha.

Além dos softwares gratuito, os participantes do WebsiteSpark têm acesso a materiais de estudo e podem ligar-se a empresas parceiras da Microsoft, criando boas oportunidades de negócio.

A iniciativa é voltada para pequenas empresas (com até 10 colaboradores).

Para mais informações, acesse:

 
Antes de efetuar o cadastro, leia atentamente o regulamento do programa.

quarta-feira, 30 de março de 2011

Auto-numeração em colunas (IDENTITY) - Parte 2

No último artigo, explorei os aspectos básicos relacionados às colunas auto-numeráveis, neste artigo, aprofundaremos em algumas propriedades e funções relacionadas a este tipo de coluna

Recuperando o valor gerado para uma coluna auto incrementável em uma tabela

Existem situações onde é necessário recuperar o valor gerado pela coluna auto incrementável, ao inserir registros em tabelas relacionadas através de um campo deste tipo, por exemplo.

Para realizar esta tarefa, o SQL Server disponibiliza algumas funções de sistema:
  • @@IDENTITY - Retorna o último valor gerado para o campo IDENTITY independente do escopo, ou seja, se o comando INSERT disparar uma Trigger que realize um novo INSERT sobre esta tabela, a função @@IDENTITY retornará o último valor gerado.
  • SCOPE_IDENTITY() - Retorna o último valor gerado para o campo IDENTITY no escopo atual, ou seja, se o comando INSERT disparar uma Trigger que realize um novo INSERT sobre esta tabela, a função SCOPE_IDENTITY() retornará o valor gerado inicialmente.

  • IDENT_CURRENT( 'tableName' ) - Retorna o último valor gerado para a tabela cujo nome deve ser passado por parâmetro. O valor retornado é independente de escopo ou sessão.

No exemplo abaixo, recuperamos o último valor inserido no campo ID da tabela Pessoa utilizando a função @@IDENTITY:

CREATE TABLE Pessoa
(
    id INT IDENTITY(1,1) PRIMARY KEY,
    nome VARCHAR(50)
)

INSERT INTO Pessoa VALUES ('Evandro')
INSERT INTO Pessoa VALUES ('Mariana')
INSERT INTO Pessoa VALUES ('Regina')
INSERT INTO Pessoa VALUES ('Antonio')

SELECT @@IDENTITY

Veja na figura abaixo o resultado da execução deste script:


Observe que ao consultar o valor de @@IDENTITY, foi retornado o último valor gerado para o campo ID.

As funções IDENT_CURRENT('tablename') e SCOPE_IDENT() podem ser utilizadas da mesma forma. Veja abaixo:

IDENT_CURRENT('tablename'):


SCOPE_IDENTITY():


Aguarde! No próximo artigo veremos como especificar os valores a serem inseridos em um campo IDENTITY e também como definir qual o próximo valor a ser gerado para a coluna.


quinta-feira, 24 de março de 2011

Auto-numeração em colunas (IDENTITY) - Parte 1

Encontrar uma coluna (ou algumas colunas) que identifique(m) unicamente as linhas de uma tabela (chave primária) nem sempre é uma tarefa trivial. 

Alguns tipos de registros possuem características que os distingem unicamente dos demais, como os casos abaixo:
Por outro lado, existem registros que necessitam de uma chave composta por várias colunas da tabela ou mesmo onde não se pode garantir que a combinação diferenciará uma linhas das demais.

Uma das maneiras de garantir a unicidade de um registro é utilizar um código identificador (ID) para os registros da tabela. Para isso, pode-se usar uma coluna auto-numerável, cujo valor nunca se repete entre duas linhas.

A propriedade IDENTITY

A grande maioria dos SGBDs populares possuem o recurso de auto-numeração para colunas, como é o caso das SEQUENCES para Oracle, ou da propriedade AUTO_INCREMENT para o MySQL.

O SQL Server disponibiliza a propriedade IDENTITY para as colunas do tipo inteiro, permitindo a criação de uma coluna auto-numerável.

Algumas condições devem ser observadas para trabalhar com esta propriedade:
  • Apenas colunas do tipo INT, TINYINT, SMALLINT e BIGINT podem ser autonumeráveis
  • Somente uma única coluna pode ser autonumerável em uma tabela

Criando uma coluna IDENTITY

Para criar uma coluna com a propriedade IDENTITY, é necessário especificar um valor para o início da contagem e um valor de incremento, normalmente utiliza-se o valor 1 para ambos os parâmetros.

Veja o exemplo abaixo:

CREATE TABLE Pessoa 
(
    id INT IDENTITY(1,1) PRIMARY KEY,
    nome VARCHAR(50)
)

No script acima, criou-se uma tabela para armazenar Pessoas. O campo id será preenchdio automaticamente, iniciando em 1 para o 1º registro, 2 para o 2º registro e assim em diante.

Inserindo registros em uma tabela com coluna IDENTITY

Em condições normais, não é necessário especificar valores para o campo IDENTITY. Veja como os dados são inseridos na tabela Pessoa
 
INSERT INTO Pessoa VALUES ('Evandro')
INSERT INTO Pessoa VALUES ('Mariana')
INSERT INTO Pessoa VALUES ('Regina')
INSERT INTO Pessoa VALUES ('Antonio')

Veja o resultado da consulta em nossa tabela:


Observe que não foi necessário inserir os valores para a coluna ID.

Em breve, publicarei alguns recursos para manipular colunas com a  propriedade IDENTITY.


terça-feira, 15 de março de 2011

AdventureWorks para o SQL Server Denali CTP1

A Microsoft já liberou uma release beta da base de dados Adventure-Works para testes.

Esta release é baseada na base de dados disponibilizada com o SQL Server 2008 R2, contendo atualizações referentes as novas funcionalidades do SQL Server Denali CTP1

Assim como as versões anteriores, o download pode ser feito através do Codeplex, comunidade OpenSource da Microsoft.

Segue abaixo o link para o download:

quarta-feira, 9 de março de 2011

Junções entre tabelas (JOINS)

Uma das dificuldades dos iniciantes na linguagem SQL é a construção de consultas utilizando junções entre tabelas, um recurso fundamental para visualizar os dados de um banco relacional.

Ao juntar as linhas de duas tabelas A e B, o SQL Server tenta combinar cada linha de A com todas as linhas de B, seguindo um critério estabelecido ao criar a junção (cláusula ON). Assim, serão retornadas todas as associações válidas da linha de A com as linhas de B, podendo haver mais do que uma correspondência em B para cada linha de A.

O resultado de uma junção pode ser utilizado para retornar os dados para um usuário ou aplicação,  mas também pode ser referenciado em uma consulta como uma nova tabela, permitindo que novas junções e filtros sejam realizados sobre este sub-resultado. Além disso, pode-se selecionar colunas específicas de cada tabela participante de uma junção ou mesmo não retornar colunas de uma tabela.

Para exemplificar, utilizaremos duas tabelas temporárias muito simples com apenas uma coluna do tipo inteiro em cada uma.

-- Criando tabela A
CREATE TABLE #a (id int)  

-- Criando tabela B
CREATE TABLE #b (id int)

E popularemos estas tabelas com alguns dados para testarmos as junções:

-- Populando tabela A
INSERT INTO #a VALUES (1)
INSERT INTO #a VALUES (2)

-- Populando tabela B
INSERT INTO #b VALUES (1)
INSERT INTO #b VALUES (3)

Desta forma, nossas tabelas para testes serão:

#A
#B
id id
1 1
2 3

Nos scripts de exemplo, utilizarei Alias para aumentar a legibilidade.

INNER JOIN

A junção mais utilizada para explorar os relacionamentos em tabelas é o INNER JOIN. Através desta junção, são retornadas todas as linhas das tabelas A e B que correspondam ao critério estabelecido na cláusula ON.

Podemos juntar os dados de nossas tabelas #a e #b utilizando o comando abaixo:

SELECT * FROM #a A
INNER JOIN #b B
   ON A.id = B.id

O resultado desta consulta está na figura abaixo:


Observe que o SQL Server retornou apenas as linhas que correspondem ao critério da cláusula ON.

LEFT JOIN

Um outro tipo de junção disponível na linguagem T-SQL é o LEFT JOIN. Ao juntar a tabela A com a tabela B utilizando o LEFT JOIN, todas as linhas de A serão retornadas, mesmo que não seja encontrada uma linha em B que atenda ao critério de alguma linha em A.

Se uma linha da tabela A não estiver associada a uma linha da tabela B e o resultado da consulta apresentar colunas da tabela B, estas colunas serão retornadas com o valor NULL para esta linha.

Abaixo, temos o comando para realizar um LEFT JOIN entre nossas tabelas de teste:

SELECT * FROM #a a
LEFT JOIN #b b
   ON a.id = b.id

Verifique o resultado da consulta na figura abaixo:


Observe que o SQL Server retornou todas as linhas da tabela A. Para a coluna pertencente à tabela B, só foram preenchidas com dados as linhas onde o critério definido na cláusula ON foi atendido.

Outro aspecto importante é a ordem das tabelas participantes do LEFT JOIN. Serão retornadas todas as linhas da primeira tabela, e apenas as linhas de B que atendam ao critério da junção.

RIGHT JOIN

O RIGHT JOIN é muito semelhante ao LEFT JOIN. A diferença fundamental é que serão retornadas todas as linhas da segunda tabela participante da junção.

O comando abaixo define uma consulta utilizando o RIGHT JOIN para juntar os dados de nossas tabelas de teste:

SELECT * FROM #a a
RIGHT JOIN #b b
   ON a.id = b.id

Veja o resultado:


Observe que o SQL Server retornou todas as linhas da tabela B. Para a coluna pertencente à tabela A, só foram preenchidas com dados as linhas onde o critério definido na cláusula ON foi atendido.

Assim como no LEFT JOIN, a ordem das tabelas participantes é importante e pode alterar o resultado de um RIGHT JOIN

FULL JOIN

O funcionamento do FULL JOIN pode ser considerado uma combinação do LEFT JOIN e o RIGHT JOIN, ou seja, quando se realiza uma junção entre duas tabelas A e B através do FULL JOIN, obtém-se como resultado uma combinação das linhas de A e B que respeitam a condição definida na cláusula ON e também farão parte do resultado as linhas de ambas as tabelas que não foram associadas.

Vejamos na prática. O comando abaixo realiza o FULL JOIN entre as tabelas que criamos para testar as junções:

SELECT * FROM #a a
FULL JOIN #b b
   ON a.id = b.id

A figura abaixo exibe o resultado deste comando:


CROSS JOIN

O CROSS JOIN diferencia-se dos outros tipos de junção, pois não permite que seja especificada uma condição para a junção (Cláusula ON). Assim, o resultado será uma combinação de TODAS as linhas da primeira tabela com TODAS as linhas da segunda tabela.

Esse é um dos tipos de junção menos utilizados, talvez pelo risco apresentado ao combinar duas tabelas com muitos registros ou mesmo por não permitir a criação de uma condição de junção, o que não é interessante para explorar dados relacionais.

O comando abaixo realiza o CROSS JOIN entre nossas tabelas de teste:

SELECT * FROM #a
CROSS JOIN #b

Observe que não há filtro. Define-se apenas quais tabelas participarão da junção. O resultado este comando é exibido na figura abaixo:


No resultado acima, verificamos que cada linha da tabela #a foi combinada com uma linha da tabela #b, não houve filtros ou restrições.

É preciso ter cuidado ao utilizar CROSS JOIN, pois devido à ausência de condições de junção, seu resultado pode apresentar um número muito grande de linhas (o número de linhas do resultado será sempre igual à multiplicação do número de linhas das tabelas participantes), o que pode causar impactos na performance do processamento, leitura e escrita em disco (I/O) e também gerar um grande tráfego na rede onde se localiza a instância do SQL Server.

SELF JOIN

Algumas pessoa costumam dar o nome de SELF JOIN para as junções onde uma tabela é associada a ela mesma. Todas as junções descritas acima podem ser utilizadas para realizar um SELF JOIN.

ALGUMAS DICAS...

  • Procure criar índices para os campos utilizados com frequencia para realizar junções, garantindo assim uma performance maior no processamento das junções.
  • Evite utilizar o CROSS JOIN. Ele pode impactar seriamente no desempenho de seu servidor SQL Server.

quarta-feira, 16 de fevereiro de 2011

Explore os recursos do SQL Server "Denali"

Nos sites da Microsoft já é possível encontrar informações e documentação sobre o SQL Server Denali. O TechNet já existe uma documentação em versão preview, que já contempla muitas das novas funcionalidades agregadas na próxima versão. Para os que tiverem interesse, segue o link:


Em breve, publicarei mais novidades!

terça-feira, 15 de fevereiro de 2011

Registrando alterações em uma base de dados - Parte 1

Quando se fala de segurança de uma base de dados, é fundamental conhecer os usuários que acessam uma base de dados e definir as permissões adequadas para cada usuário, configurando de forma que cada usuário tenha acesso somente aos dados que lhe são necessários.

Mesmo garantindo que cada usuário tenha as permissões adequadas, pode ser necessário armazenar um registro (log) das alterações realizadas na base de dados. Esse registro pode ser uma ferramenta importante para realizar auditorias, garantir a qualidade dos dados e também para monitorar alterações realizadas de forma maliciosa.

Um bom registro de log de alterações deve armazenar QUEM (login do usuário) realizou a alteração, QUANDO esta alteração foi realizada e O QUE foi alterado. Pode-se utilizar Triggers (discutiremos sobre elas nos próximos artigos) para disparar a gravação dos registros de um log.

Por enquanto falaremos sobre como obter as informações necessárias para gravar um histórico de alterações.

Como recuperar o usuário logado em uma base de dados SQL Server?
 
Para realizar esta tarefa, pode-se utilizar a função de sistema SYSTEM_USER.
Faça o teste:

SELECT SYSTEM_USER

Como recuperar o horário atual do servidor SQL Server?
 
A função GETDATE() retorna a data e hora atual do Sistema Operacional.
Faça o teste:

SELECT GETDATE()

Nos próximos artigos, veremos um pouco mais sobre auditoria de dados...

Referência:

MSDN - SYSTEM_USER 
MSDN - GETDATE (TRANSACT-SQL) 

terça-feira, 8 de fevereiro de 2011

A próxima versão do SQL Server está a caminho!

Está disponível para download no site da Microsoft a versão Community Technology Preview 1 (CTP1) do SQL Server, com code-nome Denali.

Para os que tiverem interesse em conhecer e testar a versão preview, basta acessar o link abaixo:


Lembrando que esta é uma versão em fase de testes e pode conter bugs e instabilidades...

Comentários e feedbacks sobre a nova versão são bem-vindos!

Em breve, postarei artigos sobre as novidades da próxima versão!

Alterando o Login de um Usuário de banco de dados

Aqui vai um script rápido para associar um usuário de um banco de dados SQL Server a um novo login no servidor:

ALTER USER dbUser WITH LOGIN = serverLogin

Essa alteração pode ser muito útil para manter as permissões de usuários ao realizar o RESTORE de um banco de dados em um novo servidor. 

Ao restaurar um banco, todos os usuários são criados, porém os logins do servidor antigo podem não existir no novo ambiente. Para manter as permissões associadas a cada usuário da base restaurada, basta associá-los a logins válidos no novo servidor (podendo estes ser criados preferencialmente com o mesmo nome, para garantir que as aplicações possam manter suas configurações para acessar o novo ambiente)

Agradeço a Bruno Peinado Brusco pela dica!

terça-feira, 1 de fevereiro de 2011

Alterando o Schema de uma tabela

O uso de schemas é uma boa prática para organizar a estrutura e as permissões de um banco de dados SQL Server.

Todos os objetos pertencentes a um Schema herdam suas permissões (desde que não existam permissões explícitas para cada objeto). Além disso, cada objeto pertence a um, e somente um, Schema.

Apesar do schema fazer parte da identificação de todo objeto, o simples renomeio de uma tavela  não possibilita associá-la a outro Schema.

Para realizar esta tarefa, deve-se utilizar a cláusula TRANSFER do comando ALTER SCHEMA.
Veja a sintaxe abaixo:

ALTER SCHEMA nome_do_schema TRANSFER outro_schema.nome_da_tabela; 

Note que o uso desta cláusula permite que a tabela nome_data_tabela seja "importada" para o Schema nome_do_schema.

No exemplo abaixo, 2 Schemas são criados (Marketing e RecursosHumanos). Em seguida, cria-se a a tabela Funcionario no Schema Marketing. Suponhamos que esta seja uma associação equivocada, e que a tabela Funcionario deva pertencer ao Schema RecursosHumanos.

-- Criando Schema para a área de Marketing
CREATE SCHEMA Marketing
GO

-- Criando Schema para a área de RecursosHumanos
CREATE SCHEMA RecursosHumanos
GO

-- Criando tabela Funcionario no Schema Marketing
CREATE TABLE Marketing.Funcionario
(
id int identity(1,1) PRIMARY KEY,
nome VARCHAR(100)
)
GO

/* Alterando o Schema da tabela Funcionario. 

(DE: Marketing PARA: RecursosHumanos) */
ALTER SCHEMA RecursosHumanos TRANSFER Marketing.Funcionario

GO

Observações:

  • Tome cuidado ao alterar nomes ou schemas de objetos de um banco de dados. Tenha sempre em mente que este objeto pode ser referenciado por aplicações ou mesmo por outros objetos existente no banco de dados. Caso isso aconteça, estas referências serão inválidas.

Agradeço a Douglas Almena pela dica!

segunda-feira, 31 de janeiro de 2011

Tutorial para iniciantes em SQL Server

A Microsoft disponibiliza, através do portal TechNet, diversos recursos de aprendizado para o SQL Server e várias outras tecnologias desenvolvidas por ela.

Para os que estão começando, existe um tutorial bastante completo sobre as operações mais comuns utilizadas em implementações e acesso a banco de dados.


O material está em inglês e é baseado em 3 lições principais:
  
- Lesson 1: Creating Database Objects - Aprenda a criar um Bancos de Dados, Tabelas e inserir/consultar dados.
- Lesson 2: Configuring Permissions on Database Objects - Aprenda como criar logins, definir pemissões. Veja também como implementar views e stored procedures, para acessar os dados armazenados no banco e controlar o acesso através de permissões.
- Lesson 3: Deleting Database Objects - Exclua dados de tabelas, objetos do banco (tabelas, procedures e views) e também o próprio banco de dados.
É uma ótima boa de iniciar o aprendizado.
Vale a pena conferir!

Estudo de caso: McLaren Electronic Systems

Para os que acham que o SQL Server não é capaz de atender aplicações de alta performance, aqui vai um exemplo prático...

Neste artigo, a Microsoft, em parceria com a equipe da McLaren, mostra o SQL Server trabalhando como banco de dados para uma aplicação de monitoramento e coleta de dados dos carros de Formula 1 da equipe durante os testes, treinos e corridas.

Esta aplicação exige uma alta performance do banco de dados, pois o volume de dados coletados é muito alto. Segundo o artigo, são gerados entre 1 e 2 Gigabytes de dados em uma corrida e, ao longo de um ano, são gerados entre 2 e 3 Terabytes!

Confira o texto completo (em inglês) e assista também ao vídeo disponibilizado com o artigo:

Microsoft Case Studies: McLaren Electronic Systems

Abraços!

Verificando se uma coluna existe em uma tabela (através de Views de Sistema)

As Views de Sistema são ferramentas muito úteis para recuperar informações sobre a estrutura e estatísticas de um banco de dados SQL Server.

Os scripts abaixo mostram como utilizar a view COLUMNS do Schema INFORMATION_SCHEMA para verificar se uma coluna existe. Nos exemplos, deseja-se incluir a coluna Telefone VARCHAR(15) à tabela Funcionarios a View Columns é utilizada para verificar se a coluna já pertence à tabela, evitando que o script resulte em erro (por tentativa de inserção de uma coluna duplicada).

Veja o exemplo para as versões 2005 e 2008 do SQL Server:

-- Criando tabela para teste
CREATE TABLE Funcionarios
(
    idFuncionario INT IDENTITY (1,1) PRIMARY KEY
    , nome VARCHAR(100) NOT NULL
)

-- Verificando se a coluna Telefone VARCHAR(15)
-- existe na tabela Funcionarios
IF NOT EXISTS
(
  SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS C
  WHERE
    TABLE_NAME = 'Funcionarios'
    AND TABLE_SCHEMA = 'dbo'
    AND COLUMN_NAME = 'Telefone'
    AND DATA_TYPE = 'VARCHAR'
    AND CHARACTER_MAXIMUM_LENGTH = 15
)
BEGIN

   -- Adicionando a coluna Telefone VARCHAR(15)
   -- à tabela Funcionarios
   ALTER TABLE Funcionarios
      ADD Telefone VARCHAR(15)

   PRINT 'Coluna Telefone adicionada à tabela Funcionarios'

END
ELSE
   BEGIN
     PRINT 'Coluna Telefone já existe na tabela Funcionarios'
   END

Para os que utilizam o SQL Server 2000, pode-se utilizar um conjunto de Views de Sistema (sysobjects, syscolumns e systypes) para obter resultado semelhante.Veja o exemplo abaixo:

-- Criando tabela para teste
CREATE TABLE Funcionarios
(
    idFuncionario INT IDENTITY (1,1) PRIMARY KEY
    , nome VARCHAR(100) NOT NULL
)

-- Verificando se a coluna Telefone VARCHAR(15) existe na tabela Funcionarios
IF NOT EXISTS 
(
    SELECT 1 FROM syscolumns Coluna
        INNER JOIN sysobjects    Tabela ON
             Coluna.id     = Tabela.id
        INNER JOIN systypes        Tipo ON
             Tipo.xtype = Coluna.xtype
    WHERE
            Coluna.name        = 'Telefone'
        AND Tabela.name        = 'Funcionarios'
        AND Tipo.name        = 'varchar'
        and Coluna.length    = 15
)
BEGIN
 
    -- Adicionando a coluna Telefone VARCHAR(15)
    -- à tabela Funcionarios
    ALTER TABLE Funcionarios
        ADD  Telefone VARCHAR(15)
     
    PRINT 'Coluna Telefone adicionada à tabela Funcionarios'
     
END
ELSE
    BEGIN
        PRINT 'Coluna Telefone já existe na tabela Funcionarios'
    END

Nestes exemplos, comparamos o nome, tipo de dado, e tamanho do campo para encontrar a coluna. As view de sistema disponibilizam muitas outras informações (collation, precisão decimal, etc.). Explore estas informações de acordo com a sua necessidade!

terça-feira, 25 de janeiro de 2011

Inserindo o resultado de uma consulta em uma tabela já existente

No artigo anterior, verificamos como inserir os resultados de uma consulta em uma nova tabela utilizando a cláusula INTO do comando SELECT. Agora, vamos explorar como inserir o resultado de uma consulta em uma tabela já existente.

O comando INSERT possui uma sintaxe alternativa que auxilia a execução desta tarefa. Veja abaixo:

INSERT INTO tabelaDestino
SELECT campo1, campo2, ... , campoN
FROM tabelaOrigem
WHERE condicionais

Utilizando a tabela Funcionarios do artigo anterior, criaremos

CREATE TABLE Funcionarios
(
    idFuncionario INT IDENTITY (1,1) PRIMARY KEY
    , nome VARCHAR(100) NOT NULL
    , dataAdmissao DATETIME NOT NULL
    , dataDemissao DATETIME NULL
)

-- Inserindo dados fictícios para testes
INSERT INTO Funcionarios VALUES
    ('Evandro', '2001-07-03', NULL)
    ,('José','2002-02-09',NULL)
    ,('Ronaldo','1995-09-25','2001-05-03')
    ,('Márcia','2003-03-22','2004-06-04')
    ,('Régis','1997-09-25','2004-08-07')
    ,('Renata','2008-02-27',NULL)
    ,('Mariana','2009-01-15',NULL)

Em seguida, criaremos a tabela para armazenar os funcionários demitidos (FuncionariosDemitidos), com a mesma estrutura da tabela Funcionarios

-- Criando a tabela de Funcionários demitidos com a mesma estrutura original
CREATE TABLE FuncionariosDemitidos
(
    idFuncionario INT
    , nome VARCHAR(100) NOT NULL
    , dataAdmissao DATETIME NOT NULL
    , dataDemissao DATETIME NULL
)

Para preencher a nova tabela, basta utilizar o comando INSERT com a sintaxe indicada:

-- Inserindo os dados dos funcionários demitidos
INSERT INTO FuncionariosDemitidos      
    SELECT *
    FROM Funcionarios
    WHERE
        dataDemissao is not null

Algumas consideraçõe sobre este procedimento:
  • Ao contrário do comando SELECT com a cláusula INTO, o comando INSERT não cria a tabela tabelaDestino. Ou seja, a tabela tabelaDestino precisa obrigatoriamente existir.
  • Evite utilizar o caracter curinga * para definir os campos a serem inseridos na tabela tabelaOrigem, principalmente quando esta possuir campos com auto-numeração (IDENTITY)

Para maiores informações sobre o comando INSERT, consulte no MSDN:
INSERT (T-SQL)

Inserindo o resultado de uma consulta em uma nova tabela

Esta é uma tarefa simples, muito utilizada para fazer backup de tabelas ou extrair dados segundo um filtro.

Se a tabela ainda não existir, pode-se utilizar a cláusula INTO do comando SELECT para inserir os resultados em uma nova tabela com as mesmas colunas definidas no retorno do comando SELECT.

Para exemplificar, criaremos uma tabela simples para simular o cadastro de funcionários de uma empresa:

CREATE TABLE Funcionarios
(
    idFuncionario INT IDENTITY (1,1) PRIMARY KEY
    , nome VARCHAR(100) NOT NULL
    , dataAdmissao DATETIME NOT NULL
    , dataDemissao DATETIME NULL
)

-- Inserindo dados fictícios para testes
INSERT INTO Funcionarios VALUES
    ('Evandro', '2001-07-03', NULL)
    ,('José','2002-02-09',NULL)
    ,('Ronaldo','1995-09-25','2001-05-03')
    ,('Márcia','2003-03-22','2004-06-04')
    ,('Régis','1997-09-25','2004-08-07')
    ,('Renata','2008-02-27',NULL)
    ,('Mariana','2009-01-15',NULL)

Veja abaixo como o comando SELECT (com a cláusula INTO) pode ser utilizado para extrair os funcionários demitidos de uma empresa (Data de demissão preenchida) e armazená-los em uma nova tabela FuncionariosDemitidos:

-- Criando tabela com os funcionários demitidos
SELECT *
INTO FuncionariosDemitidos
FROM Funcionarios
WHERE
    dataDemissao is not null 

Observe o resultado de uma consulta sobre a nova tabela FuncionariosDemitidos:

 

Observe que apenas as linhas com a coluna DataDemissao preenchida foram inseridas na nova tabela.

Além disso, o nome dos campos criados na nova tabela são idênticos aos da tabela original.

Para maiores sobre a cláusula INTO do comando SELECT, consulte no MSDN: 
INTO Clause (Transact-SQL)

segunda-feira, 10 de janeiro de 2011

Criando scripts parametrizáveis

Scripts dinâmicos são aqueles criados em tempo de execução de uma consulta do SQL Server e são importantes ferramentas para tornar o desenvolvimento mais flexivel e muitas vezes mais otimizado.

Assim como acontece com as Stored Procedures, o SQL Server permite a criação dinâmica de batches parametrizáveis, isto é, pode-se criar scripts que recebem valores através de parâmetros. Scripts de manutenção, ou de consulta mais complexos (aqueles que não pertencem a sistemas e também convém encapsular em stored procedures) podem ser executados dessa forma. 
Utilizaremos a stored procedure de sistema sp_executesql para  realizar esta tarefa, muito conhecida para a execução de scripts armazenados em strings. A sintaxe utilizada será:

exec sp_executesql @script, @params, @param1, @param2, ... @paramN

Neste caso, @script é uma string contendo o conteúdo do script, @paramsé uma string contendo os nomes e tipos dos parâmetros, e os parâmetros @param1, @param2, ... @paramN representam os N parâmetros definidos em @params.

O exemplo abaixo mostra como parametrizar um script no SQL Server:

 /* Declarando os parâmetros e seus respectivos tipos */
DECLARE @vars NVARCHAR (100)
SET @vars = '@valor CHAR(30)'

/* Declarando o conteúdo do script, utilizando o parâmetro definido acima */
DECLARE @script NVARCHAR(100)
SET @script = 'PRINT ''Exibindo o valor do parâmetro: '' + @valor'

/* Executando o script passando o parâmetro declarado */
exec sp_executesql @script, @vars, '$valor$'

Na figura abaixo, observamos o script acima em execução. Observe que o valor passado foi utilizado durante a execução do script:


Observações:
  • A stored procedure sp_executesql pode rodar apenas um batch por execução, ou seja, não é possível adicionar o comando "GO" ao script passado por parâmetro.