Arquivo da tag: T-SQL

FOREIGN KEY confiável no SQL Server

Foreign key (FK) é um dos inúmeros meios que utilizamos para manter a consistência dos nossos bancos de dados; porém, é muito comum nas tarefas do dia-a-dia, a necessidade de desabilita-las temporariamente, por exemplo: para agilizar a importação de uma grande massa de dados.

Um dia você terá que desabilitar uma FK e os erros que você não deve cometer: esquecer uma FK desabilitada… e ao habilita-la, esquecer de realizar uma nova avaliação de consistência dos seus dados para que o SQL Server volte a confiar na sua FK!

No exemplo a seguir você verá:

  • Como desabilitar uma foreign key;
  • Como consultar se suas FKs estão habilitadas e confiáveis;
  • Como realizar uma nova checagem dos dados, antes de reativar a foreign key.

Mão na massa!

Primeiro criamos uma tabela Pai e uma tabela Filho. Note que na tabela “Filho” criamos uma FK com a tabela “Pai”. A partir deste momento não posso ter, por exemplo, filhos sem pai (acompanhe também os comentários nos scripts)

create table pai
(nomePai varchar(100) not null primary key)

go

create table filho
(nomePai varchar(100)
      constraint fk_pai_filho
      foreign key references pai(nomePai),
nomeFilho varchar(100))

Agora cadastramos alguns pais e alguns filhos:

insert into pai values ('Antonio'), ('Carlos'), ('João')

go

--E a seguir cadastramos um filho;
--neste caso "Antonio é pai do Silas":

insert into filho values ('Antonio', 'Silas')

go
--A inserção abaixo falha, porque tentamos
--inserir um filho sem pai
--(note que o erro referencia nossa FK):

insert into filho values ('', 'Luiz')

go

Erro:The INSERT statement conflicted with the FOREIGN KEY constraint “fk_pai_filho”. The conflict occurred in database “master”, table “dbo.pai”, column ‘nomePai’.

Consultando a view sys.foreign_keys temos a confirmação de que a FK está ativa e é confiável (is_not_trusted = 0); isso garante que a tabela tem dados consistentes e que não permitirá a inclusão de dados inconsistentes (porque a FK está ativa):

select name, is_not_trusted, is_disabled
from sys.foreign_keys
where name = 'fk_pai_filho'

go

--Mas posso forçar a inserção de um filho sem pai,
--se antes eu desabilitar a foreign key:

alter table filho nocheck constraint fk_pai_filho

go

insert into filho values ('', 'Luiz')

go

A partir do momento que você desabilita uma FK, o SQL Server marca ela como não confiável (not trusted=1). Veja a evidência na consulta abaixo:

select name, is_not_trusted
from sys.foreign_keys
where name = 'fk_pai_filho'

E é depois de abrirmos uma excessão, desabilitando uma FK, que cometemos os dois erros comuns:

  • Primeiro erro: esquecer de habilitar a FK.
  • Segundo erro: habilitar, mas esquecer de revalidar os dados (para o SQL Server voltar a “confiar” na FK).

Portanto, ao reativar uma foreign key, não se esqueça de utilizar o WITH CHECK (infelizmente este não é um comportamento padrão do SQL Server, portanto você deve explicita-lo):

alter table filho
with check
check constraint fk_pai_filho

A sintaxe é um tanto curiosa (2 “check” seguidos), mas é isso que garante que o SQL Server revalidará os dados da sua tabela e reativara a FK num estado confiável novamente!

No próximo post falaremos aqui sobre o impacto de uma FK não confiável no desempenho de suas consultas!

Até +

 

T-SQL – Manipulando o retorno de stored procedures

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.

O que é SQL?

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é +

DML, DDL?!? O que é isso?

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 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é +

Desafio T-SQL – CHAR X VARCHAR

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?

Colocando um script em espera

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!

Ranking usando ROW_NUMBER()

Existem algumas coisas que não sabemos da existência, outras até conhecemos mas não temos idéia do dia que precisaremos delas.

Há uns quatro anos eu precisava realizar uma consulta e retornar uma coluna com um seqüencial, numerando as linhas, algo bem simples assim:

cod_produto    des_produto                    sequencial

————– —————————— ————-

516E           Arroz                                1

03G            Azeitona                           2

98K            Salame                              3

514A           Tomate                            4

Como retornar um sequencial num comando Select simples? Eu não queria usar cursor, nem uma tabela auxiliar, nada disso, queria apenas um seqüencial, simples e objetivo. Enfim, procurei diversos meios e até hoje não soube uma forma simples de fazer isso no SQL Server 2000.

Com o SQL Server 2005 aquele bordão “Seus problemas acabaram” cabe perfeitamente nesse cenário. Existe uma função Row_Number() que  faz exatamente isso. Veja um exemplo:

SELECT
cod_produto,
des_produto,
ROW_NUMBER() over (order by des_produto) sequencial
FROM
tb_teste_produto

Como fica subtendido a consulta SQL irá retornar os campos da tabela mais um campo  seqüencial baseado na ordenação do nome do produto.

Simples não é?

Trabalhando com subconsultas é possível por exemplo atualizar todos os registros entre as linhas 60 e 80 de uma consulta… é o T-SQL dando suporte às nossas necessidades!

Se você tem outra solução para este cenário contribua deixando seu comentário :)