Trabalhando com OUTPUT e Foreign Keys

Quando se utiliza a cláusula OUTPUT em tabelas físicas com foreign keys, é possível surgir problemas com as referências, mesmo que seja um INSERT em uma tabela mãe (tabela referenciada) e com OUTPUT em uma tabela filha (tabela que possui a referência), exemplo:

CREATE TABLE Categoria (
	Codigo INT IDENTITY,
	Descricao VARCHAR(90) NOT NULL,
	CONSTRAINT PK_Categoria PRIMARY KEY (Codigo)
);
GO

CREATE TABLE Produto (
	Codigo INT IDENTITY,
	Categoria INT NOT NULL,
	Descricao VARCHAR(90) NOT NULL,
	CONSTRAINT PK_Produto PRIMARY KEY (Codigo),
	CONSTRAINT FK_Produto_Categoria FOREIGN KEY (Categoria)
		REFERENCES Categoria(Codigo)
);
GO

INSERT INTO Categoria
	OUTPUT INSERTED.Codigo, 'Limão' INTO Produto
VALUES ('Frutas');

Msg 332, Level 16, State 1, Line 18 The target table 'Produto' of the OUTPUT INTO clause cannot be on either side of a (primary key, foreign key) relationship. Found reference constraint 'FK_Produto_Categoria'.

A melhor maneira de se tratar isso é não ter foreign keys na tabela filha e foreign keys que referenciam a tabela filha, mas também é possível desabilitar as foreign keys durante o processo, exemplo:

ALTER TABLE Produto
NOCHECK CONSTRAINT FK_Produto_Categoria;
GO

INSERT INTO Categoria
	OUTPUT INSERTED.Codigo, 'Limão' INTO Produto
VALUES ('Frutas');
GO

ALTER TABLE Produto
CHECK CONSTRAINT FK_Produto_Categoria;

O problema de desabilitar as foreign keys é possibilidade de erro após a inserção e o fato do usuário que executa esta operação ter acesso de alteração da estrutura do banco de dados (ALTER TABLE).

Trabalhando com OUTPUT

Esta cláusula permite recuperar valores de DML (INSERT, UPDATE, DELETE), e inseri-los em tabelas físicas ou temporárias, bem úteis para identificar registros criados/alterados/excluídos durante uma transação.

Exemplo INSERT:

IF EXISTS (SELECT * FROM sys.tables WHERE name = 'Categoria')
	DROP TABLE Categoria

CREATE TABLE Categoria (
	Codigo INT,
	Descricao VARCHAR(90) NOT NULL,
	CONSTRAINT PK_Categoria PRIMARY KEY (Codigo)
);

DECLARE @Categoria TABLE (
	Codigo INT,
	Descricao VARCHAR(90) NOT NULL
)

INSERT INTO Categoria
	OUTPUT inserted.Codigo, inserted.Descricao
	INTO @Categoria
VALUES
	(1, 'Melancia'),
	(2, 'Melão')

SELECT * FROM Categoria

SELECT * FROM @Categoria

Exemplo DELETE:

DECLARE @Excluido TABLE (
	Codigo INT,
	Descricao VARCHAR(90) NOT NULL
)

DELETE FROM Categoria
	OUTPUT deleted.Codigo, deleted.Descricao
	INTO @Excluido
WHERE Codigo = 1

SELECT * FROM @Excluido

SELECT * FROM Categoria

Exemplo UPDATE:

DECLARE @Alterado TABLE (
	Codigo INT,
	Descricao VARCHAR(90) NOT NULL,
	CodigoAlterado INT,
	DescricaoAlterado VARCHAR(90) NOT NULL
)

UPDATE Categoria
SET Codigo = 1, Descricao = 'Laranja'
	OUTPUT deleted.Codigo, deleted.Descricao, inserted.Codigo, inserted.Descricao
	INTO @Alterado
WHERE Codigo = 2

SELECT * FROM @Alterado

SELECT * FROM Categoria

Outra utilidade do OUTPUT é recuperar valores de IDENTITY, NEWID, NEWSEQUENTIALID, ao invés de utilizar @@IDENTITY e SCOPE_IDENTITY() que somente retornam valores do último registro gerado.

IF EXISTS (SELECT * FROM sys.tables WHERE name = 'Categoria')
	DROP TABLE Categoria

CREATE TABLE Categoria (
	Codigo INT IDENTITY,
	CodigoGuid UNIQUEIDENTIFIER DEFAULT NEWID(),
	CodigoHash UNIQUEIDENTIFIER DEFAULT NEWSEQUENTIALID(),
	Descricao VARCHAR(90) NOT NULL,
	CONSTRAINT PK_Categoria PRIMARY KEY (Codigo)
);

DECLARE @Categoria TABLE (
	Codigo INT,
	CodigoGuid UNIQUEIDENTIFIER,
	CodigoHash UNIQUEIDENTIFIER,
	Descricao VARCHAR(90) NOT NULL
)

INSERT INTO Categoria (Descricao)
	OUTPUT inserted.Codigo, inserted.CodigoGuid, inserted.CodigoHash, inserted.Descricao
	INTO @Categoria
VALUES
	('Melancia'),
	('Melão'),
	('Laranja')

SELECT * FROM @Categoria

Iniciando com PowerShell – Step 4: Seleção

Quando pensamos em estabelecer critérios para seleção de dados, precisamos definir por quais atributos/propriedades queremos trabalhar, no caso do PowerShell, assim como no SQL, podemos selecionar, condicionar e ordenar resultados, para tal, temos alguns comandos bem úteis.

Para selecionar, além dos já conhecidos Format-Table, Format-Wide e Format-List, também podemos utilizar “Select-Object” ou simplesmente “Select”:

E utilizar critérios semelhantes aos comandos TOP do T-SQL e LIMIT do MySQL.

Select -First:

Select -Skip, para situações como “recuperar os 5 primeiros que estão após os 5 primeiros”:

Select –Last:

E para ordenação, Sort-Object ou “Sort”:

E o “-desc” ou “-descending”, para inverter a ordem:

E para as condições, temos Where-Object ou simplesmente “Where”:

Performance – Clustered Index Scan

É difícil identificar todos índices necessários para todas as consultas de um banco de dados em produção, mas tem alguma diferença entre uma consulta numa tabela HEAP (Table Scan) e uma tabela sem índices nos campos pesquisados (Clustered Index Scan)?

Para demonstrar que Table Scan e Clustered Index Scan podem ser igualmente problemáticos, abaixo um plano de execução que ilustra bem esta situação:

Se existem muitos “Clustered Index Scan”, é sinal que alguma coisa não esta indo muito bem, então, “dá-lhe” índices!

Os índices não clustered são estruturas compostas por uma ou mais colunas “indexadas” e outras colunas auxiliares (included columns), e a(s) coluna(s) do índice clustered (e quando índices não clustered estão em uma tabela HEAP, é usado um “identificador” do registro ao invés da coluna do índice clustered).

Microsoft Exam Pack com Free Second Shot!

Mais um motivo para tirar sua certificação Microsoft!

A promoção é “a mesma de sempre”, só que com alguns upgrades, confira:

http://www.prometric.com/microsoft/certpacks

Iniciando com PowerShell – Step 3: Output

Assim como qualquer ferramenta PowerShell também possui Input/Output, e no que diz respeito á Outputs temos alguns comandos interessantes.

Para visualização de dados no prompt, existem alguns comandos úteis para visualização dos dados, como:

Format-Table: Formatação tabular, na qual você pode informar em seguida as colunas que deseja que apareçam na sua “tabela”:

Format-List: Formato de lista, na qual você também pode informar as colunas que deseja que apareçam na sua listagem:

Format-Wide: Relaciona uma das colunas, exemplo, o nome dos serviços (lembra do “dir /w”??):

Para saída em arquivos, podemos utilizar:

Out-File: Saída em simples formato texto:

Export-Csv: Saída em forma CSV:

Export-CliXml: Saída em formato XML:

E por último, o mais interessante, ConvertTo-Html:

Performance – Heap

É comum encontrar chaves primárias como indexes clustered, tendo assim os registros armazenados em disco na mesma ordem das colunas que compõem as chaves, já que é uma configuração padrão das chaves primárias do SQL Server. Em algumas situações pode ser justificável utilizar outro índice da tabela como clustered ao invés da chave primária.

Então o que acontece quando uma tabela não tem uma chave primária clustered ou outro qualquer índex como clustered? Ela fica armazenada numa estrutura conhecida como HEAP (tradução livre: estrutura caótica de armazenamento de registros), que até em consultas simples ou tabelas com poucos registros, pode se tornar um gargalo para o banco de dados.

Para demonstrar um pouco do que seria este gargalo, abaixo a diferença relativa de custos de pesquisas entre uma tabela com chave primária clustered e outra heap, tendo as duas com aproximadamente 350.000 registros.

Uma tabela somente pode ter um índice ou chave primária como clustered e vários índices não clustered, mas não esqueça de definir ao menos um índice ou chave primária como clustered!

Importante, as estimativas dos planos de execução são igual previsão do tempo, nem sempre são 100% confiáveis, mas são ótimas formas de identificar problemas nas consultas.

Iniciando com PowerShell – Step 2

Visto que uma das idéias do PowerShell é a facilidade de criar scripts e executá-los posteriormente, para demonstrar este recurso utilizarei do Windows PowerShell ISE, que é uma ferramenta muito útil para editar e executar scripts.

Esta ferramenta possui uma interface bem descomplicada:

Podemos executar comandos diretamente na área prompt ou executar scripts que estamos editando apertando o botão assinalado, e acompanhar a execução na área resultados.

Quando você já tiver desenvolvido seu script e desejar executá-lo novamente, você pode abrir o PowerShell e informar onde o script esta localizado no seu computador, mas isso pode apresentar problemas por políticas de execução, exemplo:

Para isso execute o comando abaixo e ‘Y’ (Yes), pra eliminar a restrição a scripts:

Set-ExecutionPolicy RemoteSigned

E por fim, execute novamente seu script:

Iniciando com PowerShell – Step 1

Boa noite pessoas!

Não sou o cara do PowerShell como o MVP Laerte, mas gostaria de compartilhar alguns passos para que você também possa utilizar este recurso no seu dia-a-dia. O objetivo deste primeiro passo é utilizar PowerShell para trabalhar com os serviços do Windows.

Para iniciarmos, vamos executar o “powershell.exe” como administrador:

E como resultado teremos a seguinte tela, normalmente num tom de azul ou preto:

Calma ai! Você não entrou no DOS por acidente, só o PowerShell funciona “parecido” com o DOS, mas como “muito^n” mais recursos, pois até comandos “dir”, “cd” e “cls” estão presentes nesta ferramenta.

O primeiro comando que iremos utilizar será o Get-Service, que retorna a relação dos serviços que temos no computador.

Para filtrar esta relação, utilizaremos o seguinte comando:

Get-Service *SQL* 

Ou para os fãs de clausulas WHERE e LIKE:

Get-Service | Where {$_.Name -like '*SQL*'} 

Agora identifique o serviço que você deseja iniciar e execute o comando Start-Service seguido do nome do serviço, exemplo:

Start-Service MSSQLSERVER 

E o comando Stop-Service para parar o serviço.

Stop-Service MSSQLSERVER 

Como você recebeu o seguinte erro ao tentar parar o SQL Server?

Ok, você terá que parar os serviços dependentes do SQL Server ou forçar o serviço parar.

Como assim forçar o serviço parar? Da seguinte forma:

Stop-Service MSSQLSERVER -Force 

Mas você pode utilizar outros caminhos para iniciar e parar os serviços, exemplo:

# Parar o serviço MSSQLSERVER
Get-Service | Where {$_.Name -eq 'MSSQLSERVER'} | Foreach {$_.Stop()}

# Verificar o estado do serviço MSSQLSERVER
Get-Service | Where {$_.Name -eq 'MSSQLSERVER'}

# Ou
Get-Service MSSQLSERVER

# Iniciar o serviço MSSQLSERVER
Get-Service | Where {$_.Name -eq 'MSSQLSERVER'} | Foreach {$_.Start()} 

Agradecimentos ao MVP Laerte pelos artigos que me incentivaram a conhecer o potencial deste recurso no ambiente de infra-estrutura e banco de dados.

Query From Hell – Como 100 é menor que 11???

Você sabia que 100 é maior que 10, mas 100 é menor que 11, e mesmo que 1000 seja maior que 100, 1000 ainda será menor que 99? Não entendeu? Acha que estou ficando louco, então veja bem isso:

Comparação de texto	Comparação de valores numéricos

Não se trata de um bug, pois o algoritmo de comparação de textos compara caracteres por caracteres iniciando pela esquerda, isso quer dizer que “ANA BEATRIZ” é menor que “ANA PAULA” visto que “B” é menor que “P” (a mesma comparação utilizada pelo ORDER BY).

O fato do texto “10” ser menor que “9” pode soar meio estranho, mas o primeiro caractere dos dois textos diz que “1” é menor que “9”, logo o texto “10” é menor que “9”, mesmo que o valor numérico 10 seja menor que o valor numérico 9.

Para demonstrar melhor este fato, segue um esboço de como funciona o algoritmo de comparação de texto:

Muitos problemas podem surgir quando trabalhamos com campos do tipo texto que deveriam ser tratado como numéricos, como no caso da utilização da funções MIN/MAX ou até mesmo no ORDER BY, que pode ter resultados bem diferentes da comparação feita com valores numéricos: