Tag Archives: T-SQL

Posted on by

A partir do SQL Server 2012 é possivel manipular o retorno de stored procedures de uma forma elegante e funcional.

Considere a seguinte procedure:

create procedure usp_testeResultSet
as
(
select   getdate() hoje,
getdate()-1 ontem,
123456 sequencia,
'abc' alfa
)

 

Até o 2008 R2 podíamos manipular o retorno de uma procedure através de uma tabela temporária:

create table ##resultSetAntes2012
(
hoje date,
ontem date,
sequencia bigint null,
alfa nvarchar(100)
);
go
insert into ##resultSetAntes2012
execute usp_testeResultSet;

No entanto quando consideramos o mapeamento entre aplicação e banco de dados esta não é uma abordagem tão prática.

No SQL Server 2012 temos o WITH RESULT SETS que permite alterar a forma como os dados são retornados e com este comando é possivel alterar:

  • a identificação de atributos;
  • o tipo do dado (datatype) de atributos.
  • Não é possivel, porém, omitir/incluir atributos.

Como funciona?

Na prática a nova cláusula é bem intuitiva. Digamos que o desenvolvedor precise aproveitar o retorno de uma procedure já existente (neste exemplo a usp_testeResultSet) porém, necessita que os campos de datas sejam retornados com datatype DATE (ao invés de DATETIME) e que o campo alfa seja retornado no formato unicode.

Observe abaixo a diferença da chamada (e seu retorno) com o WITH RESULT SETS:

execute usp_testeResultSet

go

execute usp_testeResultSet
with result sets (
(
hoje date,
ontem date,
sequencia bigint null,
alfa nvarchar(100)
)
)

withResultSets
Conclusão

A cláusula WITH RESULT SETS pode facilitar ainda mais a vida do programador, porém, recomendo o uso com parcimonia. Lembre-se que esta cláusula altera o retorno em tempo de execução. Se a procedure for alterada (com a adição de um novo atributo, por exemplo) a chamada na aplicação que contém o WITH RESULT SETS deve ser alterada, pois do contrário a aplicação “quebrará”. Lembre-se que a nova cláusula não permite a omissão/inclusão de atributos.

Posted on by

Você pode utilizar a função LEN() para recuperar o tamanho do conteúdo (da string) contido em um atributo ou variável.

Portanto, se quiser consultar todos os registros da tabela Clientes, onde o nome do cliente tenha mais de 30 caracteres, poderá utilizar esta consulta:

select *
from cliente
where len(nome) >30

Mas… e se ao invés da quantidade de caracteres você desejar conhecer o tamanho em bytes?

Para isso você utilizará a função DATALENGHT().

Exemplo: se quiser consultar todos os registros da sua tabela Livro, onde a sinopse exceda 10 KB, poderá utilizar a consulta abaixo:

select *
from livro
where datalength(sinopse) > 10240

O comando datalenght() está disponível a partir da versão 2005 do SQL Server.

Até +

Posted on by

tsqlOutput

Hoje a dica é sobre uma cláusula interessante disponível em comandos DML a partir do SQL Server 2005: o OUTPUT.

Sabe aquelas sentenças T-SQL que você escreve e que depois da execução nota que milhares de linhas foram afetadas e então percebe que gostaria de saber como estavam os registros antes da atualização?

Para sanar uma necessidade tão comum a tantos programadores, a partir do SQL Server 2005 foi disponibilizada a cláusula OUTPUT. A sintáxe deste novo recurso é muito simples e para recuperar o antes e depois dos dados utiliza as famosas “tabelas” DELETED e INSERTED (bem conhecidas por quem já trabalhou com triggers no SQL Server).

Veja um exemplo de como esta cláusula é utilizada no comando UPDATE (note que ela entra em ação entre as clausulas SET e WHERE):

Agora que tal um exemplo rápido para ver como a coisa funciona?

Começamos criando uma tabela e inserindo alguns dados para nosso teste:

create table testeoutput
(    id int identity,
     nome varchar(50) not null,
     dtNascto datetime not null);

insert into testeoutput (nome, dtNascto)
values    ('Silas Mendes', '19810330'),
('Frascisco Candido', '19690516'),
('Yolanda Albuquerque', '19731201'),
('Yoara Medice', '19870823');

Antes do UPDATE declaramos uma variável do tipo TABLE que irá armazenar o ANTES e DEPOIS da alteração:

declare  @historico table(
     valorAntigo varchar(100), valorNovo varchar(100) );

-- Alteramos um registro
update testeoutput
set nome = 'Silas S. Mendes'
output DELETED.nome, INSERTED.nome
into @historico
where id = 2;

-- E visualizamos os registros afetados:
select *
from @historico;

Bacana não? :)

Posted on by

Dica rápida:

Para quem coloca a mão na massa no T-SQL pode ser muito útil exibir no editor do código o números das linhas. Digamos que isto facilita o desenvolvedor a se achar no emaranhado do código. O Management Studio (SSMS) é a ferramenta utilizada pela grande maioria de desenvolvedores T-SQL, portanto mostraremos aqui como exibir a numeração das linhas neste editor:

1º acesse o menu Tools, opção Options…

2º dentro da nova janela, expanda a árvore Text Editor > Transact-SQL > General. À direita desta janela, na área Display, marque a opção Line numbers e clique em OK;

Pronto:

A configuração é aplicada na hora, sem precisar iniciar uma nova janela :)

Até +

Posted on by

nasNuvens

No último post falamos um pouco sobre “nuvem”, de um ponto de vista mais genérico. Hoje vamos “falar” um pouco sobre o SQL Azure; um SQL Server apto a gerenciar seu banco de dados na nuvem.

A idéia do SQL Azure parte do mesmo princípio de outras soluções na nuvem: você paga pelo que usa, tem alta disponibilidade desde planos mais básicos e reduz (ao menos teoricamente) custos com gerenciamento… mas tudo isso já foi abordado no post anterior, portanto, aqui focaremos nas principais características do SQL Azure e suas diferenças comparadas à versão stand-alone que instalamos em nossos servidores.

Transparência

Em primeiro lugar, é preciso entender que como as demais soluções na nuvem, a administração do SQL Azure é muito transparente para o usuário. Se hoje, você DBA se preocupa com  as questões físicas da sua instância, como a localização dos seus datafiles, tamanho dos arquivos de log, rotinas de backup, recovery model, atualização de service packs, etc… esqueça. Tudo isso é abstraído no SQL Azure.

Comandos como xp_cmdshell, backup database, restore database não são suportados pelo SQL Azure.

Bases de dados diferentes

Uma mudança significante que pode afetar a forma como trabalhamos com o SQL Server é que no SQL Azure  não podemos realizar operações entre bases de dados diferentes da forma como fazemos nas versões instaladas em nossos servidores; isso se aplica desde consultas, até cenários de replicação, database snapshot, mirroring e etc.

Bancos de sistemas

Outra coisa que você sentirá falta: o SQL Azure não expõe todas as bases de dados de sistema (model, tempdb, etc). A única exposta é a master, mas ela não tem exatamente o mesmo papel da master nas versões stand-alone e mesmo sendo o administrador da instância, não é possível criar objetos na master do SQL Azure.

Segurança

Na segurança, justamente por seu banco de dados estar na nuvem, não existe a opção de Windows Authentication; aqui você utilizará somente o SQL Server authentication.

Outras opções de segurança foram adicionadas para você administrar seu ambiente na nuvem. Você poderá, por exemplo, definir uma faixa de IPs que terão permissão para acessar sua instância.

Para o gerenciamento de logins utilizamos o Azure Management Portal. No Management Studio não temos mais interface para esta finalidade (mas ainda podemos mante-los via script).

Desenvolvimento

Para o desenvolvedor T-SQL não existem mudanças drásticas. Stored procedures, triggers, funções, transações, índices e etc são plenamente suportados.

Uma das principais mudanças é que nesta edição não é possível criar tabela temporária global. Consultas distribuídas, CLR, service broker também não são suportados.

Para ver uma lista completa do que não é suportado, consulte este link.

Armazenamento

Atualmente o SQL Azure em sua ediçao Web Edition permite armazenarmos até 5 GB; na ediçao Business o limite de armazenamento chega a 150 GB (percebemos aqui uma certa limitaçao que pode inviabilizar algumas soluçoes, mas certamente esta é uma limitaçao temporária).

Resumindo…

Pensar num SQL Server onde não temos sequer a opção de fazer backup parece assustador; mas é preciso compreender que este é o ponto focal do Azure: abstrair os pontos de administração!

Vimos aqui algumas das principais diferenças entre o SQL Azure e o SQL Server que instalamos em nossos servidores locais (stand-alone).

Nos próximos posts darei dicas de como começar a utilizar o SQL Azure e sobre como planejar a migraçao do seu banco de dados para a nuvem.

Por enquanto continuamos aqui, com a cabeça nas nuvens e os pés no chão ;)

Posted on by

Pra começar você precisa saber que SQL não é um banco de dados; SQL é um idioma (entre especialistas costumamos substituir o termo “idioma” por “linguagem”). A sigla SQL significa Structured Query Language; em português: linguagem de consulta estruturada. Destaquei o “consulta” porque o foco dela é justamente isso: consultar!

Os bancos de dados (ou gerenciadores de bancos de dados) nasceram antes da linguagem SQL e cada qual tinha sua própria linguagem de consulta. Logo os usuários e fabricantes notaram que essa torre de babel não era interessante e optaram pela criação de um único idioma para consultar bancos de dados relacionais (em outra oportunidade falaremos sobre os bancos dimensionais). O órgão American National Standards Institute (ANSI) ficou responsável pela padronização desta linguagem e de tempos em tempos realiza encontros entre fabricantes para discutir a linguagem SQL e propor melhorias; no entanto esta padronização não impede que cada fabricante personalize a linguagem SQL para atender suas necessidades, e é aí que surgem os dialetos.  Por exemplo, o “dialeto” do gerenciador de banco de dados Oracle é o PL/SQL; o do SQL Server é o T-SQL (transact SQL) e etc.

Agora é importante que você não confunda a linguagem SQL com gerenciadores de banco de dados! Isso é um erro muito comum!

Por exemplo, o gerenciador de banco de dados Microsoft SQL Server (como o nome já diz), é um programa que gerencia bancos de dados. A arquitetura dos sistemas gerenciadores de bancos de dados (SGBDs) é definida de forma que os dados possam estar sempre consistentes e que sejam recuperados da forma mais rápida possível! Digamos que estes são itens de série de qualquer SGBD. Para tornar os gerenciadores de bancos de dados ainda mais atraentes, os fabricantes adicionam outras inúmeras funcionalidades para facilitar o trabalho dos DBAs, aumentar a segurança, a disponibilidade e etc.

Hoje existem diversos gerenciadores de bancos de dados disponíveis no mercado, como o Oracle, o Microsoft SQL Server, o PostgreSQL, entre outros. Todos utilizam a linguagem SQL para consultar dados.

A grande dúvida que paira na cabeça de alguns profissionais de TI é: qual o melhor banco de dados? SQL Server? Oracle?! DB2?

O que eu digo é o seguinte: o melhor gerenciador de banco de dados é aquele que atende adequadamente o seu negócio. É como comprar um carro: você compraria uma Ferrari para fazer rally?! Compraria um Fusca para fazer uma longa viagem pelo Brasil?

Conclusão

SQL é uma linguagem de consulta a bancos de dados relacionais. No mercado atual existem inúmeros sistemas gerenciadores de bancos de dados relacionais (comumente chamados apenas pela sigla SGBD); podemos citar como exemplo o Oracle, Microsoft SQL Server, DB2, etc. Estes gerenciadores de bancos de dados utilizam a linguagem SQL para consultar os dados; porém, adicionam à esta linguagem soluções para atender suas particularidades e aí nascem dialetos como o PL/SQL, PL/pgSQL e o T-SQL.

Nos próximos posts falaremos mais sobre a linguagem SQL e os gerenciadores de bancos de dados.

Para conhecer mais detalhes sobre a linguagem SQL, dê uma olhada nesse post e fique familiarizado com outras siglas populares no mundo SQL como: DDL e DML!

Até +

Posted on by

A linguagem SQL é composta por inúmeros comandos, os quais são agrupados da seguinte maneira:

  • Comandos que definem dados;
  • Comandos que manipulam dados;
  • Comandos de controle de dados.

Algumas literaturas adicionam outras subdivisões, mas neste post vamos nos ater somente as citadas acima.

  • DDL

Quando você cria um banco de dados, ele inicialmente está “vazio”; antes de começar a consultar e alterar dados é preciso definir onde e como as informações serão gravadas dentro do novo banco; então você cria diversas tabelas explicitando o tipo de dados de cada campo, as chaves estrangeiras, os índices, as regras e etc. Estes comandos de criação e alteração de estrutura são os comandos de DEFINIÇÃO dos dados, pois definem como os dados serão armazenados; em inglês são chamados de: Data Definition Language (DDL). Logo, quando falamos em comandos do tipo DDL estamos falando de comandos do tipo: CREATE, ALTER ou DROP; comandos que criam, alteram ou excluem objetos como tabelas, procedures, índices, relacionamentos e etc.

Exemplos de comandos DDL:


-- Cria uma tabela

CREATE TABLE Pessoa(
codigoPessoa char(11),
nomePessoa varchar(50));

-- Altera tabela

ALTER TABLE Pessoa
ADD numeroTelefone varchar(11);

-- Exclui tabela

DROP TABLE Pessoa;
  • DML

Depois que você criou suas tabelas, definiu relacionamentos, índices e etc., é hora de  manipular seus dados; os comandos SQL que MANIPULAM dados são os populares SELECT, INSERT, UPDATE e DELETE. Em inglês esses comandos são chamados de Data Manipulation Language o que explica a abreviação DML.

Em algumas literaturas o comando SELECT não é considerado um comando de manipulação de dados, sendo enquadrado numa outra categoria: DQL, Data Query Language, em português: Linguagem de consulta.

Exemplos de comandos DML:


-- Seleciona dados

SELECT nome+' '+sobrenome
FROM Pessoa;

-- Altera dados

UPDATE Pessoa
SET codigoPessoa = '35649869711';

-- Exclui dados

DELETE Pessoa;
  • DCL

Com o banco de dados pronto e rodando é importante definir quem poderá acessá-lo, enfim, precisamos definir a segurança do seu banco. Em inglês, os comandos responsáveis pelo controle dos dados são chamados de Data Control Language (DCL). Os comandos GRANT e REVOKE fazem parte deste grupo.


-- Permissão de seleção na tabela Pessoa para a usuária Maria

GRANT SELECT ON Pessoa TO Maria;

-- Permissão de seleção e inserção na tabela Pessoa para o usuário Alberto

GRANT SELECT, INSERT ON Pessoa TO Alberto;

-- Negando alterações na tabela Pessoa para usuários do grupo GRP_RH

DENY INSERT, UPDATE, DELETE ON Pessoa TO GRP_RH;
  • CONCLUSÃO

A linguagem SQL é constituída de diferentes tipos de comandos e estes comandos são agrupados em três categorias principais, sendo: comandos DDL as instruções que definem estruturas de armazenamento dos dados; DML os comandos que manipulam os dados e DCL os comandos que controlam o acesso aos dados.

Durante as atividades do dia-a-dia você utilizará com mais freqüência os comandos do tipo DDL e DML.

Até +

Posted on by

O SQL Server 2008 trouxe para o T-SQL um comando brilhante que realmente facilita a vida de muita gente; o comando em questão é o MERGE. A idéia deste comando é mesclar dados, logo você pode comparar duas tabelas e utilizar o comando MERGE para sincronizá-las.

Vamos a um exemplo simples:

Primeiro criamos duas tabelas com estrutura idêntica. A chave primária destas tabelas é o campo CPF:

create table Tabela1 (
Nome varchar(50),
Idade int,
CPF char(11) primary key);

create table Tabela2 (
Nome varchar(50),
Idade int,
CPF char(11) primary key);

Agora inserimos dados nas tabelas. Observe que os dados estão diferentes, alguns tem diferença no nome, outros registros estão sobrando ou faltando em ambas tabelas:

insert into Tabela1 values
('Silas Mendes' , 29, '08148338716'),
('Maria José'   , 49, '55978913269'),
('Pedro Ribeiro', 53, '05899714732'),
('Joana Silva'  , 18, '74105689623');

insert into Tabela2 values
('SILAS Mendes'       , 29, '08148338716'),
('Maria Jose'         , 49, '55978913269'),
('Pedro Ribeiro Souza', 53, '05899714732'),
('Carlos Mania'       , 19, '12345678999');

Agora precisamos sincronizar as tabelas, vamos analisar passo-a-passo a construção do comando MERGE (o comando completo está disponível no fim do texto) .

A tabela de destino (target) é a tabela2; a tabela de origem (source) é a tabela1. A chave primária das tabelas será utilizada na comparação:

merge into tabela2 as target
using (select nome, idade, cpf from tabela1)
  as source (nome, idade, cpf)
  on (target.cpf = source.cpf)

Quando os números dos CPFs forem encontrados em ambas tabelas então atualizamos os dados na tabela alvo (target):

when matched then
		 update set target.nome = source.nome,
					target.idade = source.idade,
					target.cpf = source.cpf

Quando os números dos CPFs não forem encontrados na tabela de destino, então inserimos os dados da tabela de origem:

when not matched by target then
		 insert (nome, idade, cpf) values (nome, idade, cpf)

E finalmente, quando existirem números de CPFs no destino que não existem na origem, apagamos os registros que sobram:

when not matched by source then
		 delete;

Veja o código completo do comando MERGE:

merge into tabela2 as target
using (select nome, idade, cpf from tabela1)
  as source (nome, idade, cpf)
  on (target.cpf = source.cpf)

-- Atualiza registros diferentes
when matched then
		 update set target.nome = source.nome,
					target.idade = source.idade,
					target.cpf = source.cpf

-- Insere registros que não existem no destino
when not matched by target then
		 insert (nome, idade, cpf) values (nome, idade, cpf)

-- Se existir no destino e não existir na origem é apagado
when not matched by source then
		 delete;

Por fim realizamos o SELECT nas duas tabelas para verificar o resultado após a sincronização:


select * from Tabela1;
select * from Tabela2;

Bom trabalho, bom estudo!

Posted on by

Hoje a tarde meu amigo Adilson Pereira me mandou um desafio bem interessante, vou postar aqui exatamente do modo como ele propôs:

Observem a diferença que dá no resultado de um comando usando o CHAR e o VARCHAR:

declare @data datetime
set @data = '20100310 05:05:05'
SELECT
RIGHT('0' + CONVERT(   CHAR(2), DATEPART(mi, @data)), 2) as 'CHAR',
RIGHT('0' + CONVERT(VARCHAR(2), DATEPART(mi, @data)), 2) as 'VARCHAR'

A solução é interessante :)

E você? Tem um palpite do porque?

Posted on by

Uma dica rápida antes do feriadão:

Como agendar um script ou executá-lo de forma recorrente sem utilizar o SQL Agent?

O SQL possui um comando de controle de fluxo que pode nos auxiliar nessas tarefas: WAITFOR.

Como utilizá-lo?

Digamos que você queira monitorar o crescimento dos logs a cada 5 minutos. Nesse caso podemos utilizar o WAITFOR DELAY junto com uma estrutura de repetição como o while. Veja o exemplo:

declare @i int

set @i = 1

while @i < 12

begin

– coleta informação sobre espaço utilizado pelo log

dbcc sqlperf(logspace)

– aguarda 05 minutos para continuar

waitfor delay ’00:05:00′

set @i = @i + 1

end

Agora imagine um cenário onde você precise “agendar” a execução de um script para as 22h. Veja o exemplo:

– espera até às 22h

waitfor time ’22:00:00′

GO

dbcc sqlperf(logspace)

GO

select getdate() horaExecucao

É importante salientar que este comando não substitui o SQL Agent! Ele normalmente é utilizado em situações pontuais. Por exemplo, para o DBA às vezes é interessante monitorar durante alguns minutos a situação dos locks e para isso não é necessário criar um job e agendá-lo no SQL Agent, é mais simples utilizar o WAITFOR DELAY. Além disso você pode acompanhar o resultado das execuções diretamente no Management Studio, Query Analyser, sqlcmd, etc.

O comando é interessante, mas não veja nele uma forma de implementar uma nova política de backup, ok?

Bom feriadão!