Melhoras no Transact SQL 2005 - TOP, OUPTUP, CTE, CRIPTOGRAFIA

Conheça através deste artigo mais algumas melhorias implementadas no SQL Server 2005.

por Thiago Pastorello Gervazoni

O Top foi inserido no SQL 7.0, porém esta clausula no SQL 2005 traz algumas alterações. Permite, por exemplo, o número da percentagem a ser retornada em um SELECT. E pode também ser utilizada também para INSERT, UPDATE e DELETE.

--Vamos criar e inserir para testes
CREATE TABLE dbo.tblTeste
(
    col1 varchar(150)
)

INSERT INTO dbo.tblTeste VALUES ("Linha 1")
INSERT INTO dbo.tblTeste VALUES ("Linha 2")
INSERT INTO dbo.tblTeste VALUES ("Linha 3")
INSERT INTO dbo.tblTeste VALUES ("Linha 4")
INSERT INTO dbo.tblTeste VALUES ("Linha 5")
--

--Retorna Linha 1 e Linha 2
SELECT TOP(2) * FROM dbo.tblTeste

--Atualiza Linha 1 e Linha 2 para "S"
UPDATE TOP(2) dbo.tblTeste SET Col1="S"

--Apaga as 2 primeiras linhas onde "S" foi apagado
DELETE TOP(2) dbo.tblTeste
SELECT * FROM dbo.tblTeste

CREATE TABLE dbo.tblTeste2
(
    col1 varchar(150) 
)

--Linha 1 e Linha 2 são inseridas
INSERT TOP(2) dbo.tblTeste
SELECT * FROM dbo.tblTeste2

Existe uma grande diferença no top antigo no que diz respeito a expressões e definições como segue :

DECLARE @a int
DECLARE @b int
DECLARE @c int

SET @a = 10
SET @b = 5
SELECT @c = @a / @b

SELECT TOP(@c) * FROM dbo.tblTeste

INSERT INTO dbo.tblTeste VALUES("Linha 6")
INSERT INTO dbo.tblTeste VALUES("Linha 7")
INSERT INTO dbo.tblTeste VALUES("Linha 8")

SELECT TOP(SELECT COUNT(*) FROM dbo.tblTeste2) FROM dbo.tblTeste

OUTPUT

A execução de uma DML (Data Manipulation Language), como INSERT, UPDATE, DELETE, antigamente não tínhamos como checar o que realmente foi alterado em um destes comandos, agora é possível no SQL 2005 com o OUTPUT.

CREATE TABLE dbo.tblOutput
(
    id int identity
   ,col1 varchar(15)
)

INSERT INTO dbo. TblOutput VALUES ("Linha 1")
INSERT INTO dbo. TblOutput VALUES ("Linha 2")
INSERT INTO dbo. TblOutput VALUES ("Linha 5")
INSERT INTO dbo. TblOutput VALUES ("Linha 6")
INSERT INTO dbo. TblOutput VALUES ("Linha 7")
INSERT INTO dbo. TblOutput VALUES ("Linha 8")
INSERT INTO dbo. TblOutput VALUES ("Linha 9")
INSERT INTO dbo. TblOutput VALUES ("Linha 10")

DECLARE @del TABLE (deletedId INT, deletedVal  INT)

DELETE dbo.tblOutput
OUTPUT DELETED.id, DELETED.col1 INTo @del WHERE id < 3
SELECT * FROM @del
GO


deletedID  deletedValue
________   __________
1                 row1
2	row2

( 2 row(s) affected)

COMMON TABLE EXPRESSION (CTE)

O CTE é uma expressão que produz uma tabela que é referenciada por nome dentro de um contexto de queries simples como segue :

WITH MathConst ( PI, Avogadro)
AS
(SELECT 3.14159, 6.022e23)

SELECT * FROM MathConst



PI             Avogrado
_______   ___________
3.14159    6.022E+23

( 1 row(s) affected)

É possível ainda definir multiplas tabelas numa CTE como segue :

WITH MathConst (PI, Avogrado)
AS
SELECT 3.14159, 6.022e23),
--Segunda tabela
Package (Length, Width)
AS (SELECT 2, 5)

SELECT * FROM MathConst, Package



PI                Avogrado      Length  Width 
________   __________   _____   _____
3.14159      6.022E+23      2           5

( 1 row(s) affected)

CRIPTOGRAFIA

Antigamente os únicos recursos de criptografia que tínhamos era o WITH ENCRYPTION, como opção ao criar um objeto, porém este recurso era definitivo e não tinha como descriptar, claro tudo como recurso de segurança, se o script original não fosse guardado, em alguma manutenção a procedure necessitaria ser refeita, mas agora temos a opção de encriptar e descriptar frases e palavras dentro de objetos como segue :

DECLARE @Palavra VARCHAR(1000)
SET @Palavra = "CRIPTOGRAFIA"
DECLARE @Hash VARCHAR(1000)
SET @Hash = "AGOSTO"
DECLARE @Criptografa VARBINARY(5000)
SET @Criptografa = ENCRYPTBYPASSPHRASE(@Palavra, @Hash)

DECLARE @Descripta VARCHAR(5000)
SET @Descripta = DECRYPTBYPASSPHRASE(@Palavra, @Criptografa)

SELECT @Palavra,@Hash,@Criptografa,@ Descripta



CRIPTOGRAFIA, HASH, 0xD7A389D47bdcd8465cdfeee7ae3a6aee345bd21d006…, CRIPTOGRAFIA

( 1 row(s) affected)

Até Mais.