Recursos do T-SQL


Assim como uma parte dos desenvolvedores, acabo também tendo acesso à bases de dados SQL Server. Como a aplicação utiliza ORM para persistência das informações, o código SQL é totalmente gerado pelo NHibernate. Enquanto o ORM é responsável por fazer a persistência, tenho a necessidade que recorrer ao T-SQL para manipular e gerar massas de dados para exibir (telas e relatórios) nesta mesma aplicação.

À medida em que a versão do SQL Server avança, além das funcionalidades que são criadas em nível ferramental, a sua linguagem também ganha novos recursos, para tornar cada vez mais fácil, otimizado e inteligente a extração e manipulação de dados. Muitas vezes deixamos de acompanhar essa evolução, e quando você se dá conta, há diversos novas opções que facilitam a forma como lidamos com os dados; algo que às vezes, você até recorre à uma linguagem genuína (C#, por exemplo) para tentar realizar uma atividade, pois o T-SQL é “limitado”. A finalidade deste pequeno artigo, é demonstrar alguns recursos, que não são necessariamente novos, mas que podem facilitar o nosso dia-à-dia. Todos os scripts estão disponíveis neste endereço.

PIVOT: Possibilidade de tornar linhas em colunas, agrupando (MAX, MIN, SUM, etc.) os valores que deseja.

SELECT
	*
FROM
(
	SELECT
		  MONTH(p.Data) As Mes
		, SUM(Valor) As Total
	FROM Pedido p
	WHERE YEAR(p.Data) = 2016
	GROUP BY MONTH(p.Data)
) sq
PIVOT
(
	SUM(Total)
	FOR Mes IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])
) pvt

ROW_NUMBER: Permite numerar as linhas do resultado. Há a opção com PARTITION, que como o próprio nome diz, particiona o contador dado um critério de ordenação.

SELECT
	  p.Data
	, p.Valor
	, ROW_NUMBER() OVER (PARTITION BY MONTH(Data) ORDER BY Data ASC) As Linha
FROM Pedido p

PAGINAÇÃO: Se a aplicação suportar, a consulta pode separar em páginas e exibir parte do resultado, que é justamente aquilo que o usuário consegue ver de uma única vez.

DECLARE @PageSize As Int = 10
DECLARE @PageNumber As Int = 1

SELECT 
	  Data
	, Valor
FROM Pedido
ORDER BY Data
	OFFSET @PageSize * (@PageNumber - 1) ROWS
	FETCH NEXT @PageSize ROWS ONLY;

WITH TIES: Quando especificamos a cláusula TOP (N), ele retorna exatamente o número de registros que foi especificado. Com esta opção e dependendo do critério de ordenação, ele acaba extrapolando o número de itens do TOP entendendo que os registros que possuem o mesmo valor da ordenação, devem também estar sendo incluídos no resultado.

SELECT TOP 5 WITH TIES
	*
FROM Pedido
ORDER BY MONTH(Data) DESC

BULK INSERT: Se possui um arquivo do tipo CSV, é possível utilizar este recurso para interpretar o mesmo e conseguir inserir diretamente em uma determinada tabela da base de dados.

Id;Data;Valor;Pago
;2016-03-30 00:00:00;14000.00;0
;2016-04-30 00:00:00;14000.00;1
;2016-05-30 00:00:00;14000.00;1

BULK INSERT Pedido
FROM 'C:\Temp\05 - BulkInsert.csv'
WITH
(
	FIRSTROW  = 2,
	FIELDTERMINATOR = ';',
	ROWTERMINATOR = '\n'
);

IIF: Operador ternário. Simplifica o uso excessivo de CASE WHEN, que às vezes acaba tornando a consulta ilegível.

SELECT
	  Data
	, Valor
	, IIF(Pago = 1, 'Sim', 'Não') As Pago
FROM Pedido

AGREGAÇÃO COM WINDOW FUNCTION: Permite aplicar uma regra de agregação em partes do resultado, dado um critério de particionamento do mesmo. No exemplo abaixo, a ideia é ir criando totalizações linha a linha, com a soma de tudo o que está acima da linha corrente.

SELECT
	  Data
	, Valor
	, SUM(Valor) OVER (ORDER BY Data ASC) As Total
FROM Pedido
ORDER BY Data ASC

TABLE VALUED PARAMETERS: Um novo tipo de dados que simula um array de um determinado tipo. Permite a parametrização da cláusula IN. Pode ser utilizado nativamente com o ADO.NET.

CREATE TYPE RelacaoDeIDs AS TABLE 
(
	Id Int NOT NULL
)
GO

DECLARE @Ids As RelacaoDeIds
INSERT INTO @Ids VALUES (3)
INSERT INTO @Ids VALUES (34)
INSERT INTO @Ids VALUES (48)

SELECT * FROM Pedido WHERE Id IN (SELECT Id FROM @Ids)

CONSULTA COM EXPRESSÃO REGULAR: Utilizando uma expressão regular para retornar os clientes onde o nome termina com algum número.

SELECT * FROM Cliente WHERE Nome LIKE '%[0-9]'

LAG/LEAD: Funções que permite acessar alguma coluna da linha anterior ou posterior, respectivamente.

-- Retorna NULL
SELECT
	  Data
	, Valor
	, LAG(Data, 1) OVER(ORDER BY Data ASC) As PedidoAnteriorEm
FROM Pedido 
ORDER BY Data ASC

-- Retorna 2016-01-02 00:00:00.000
SELECT
	  Data
	, Valor
	, LEAD(Data, 1) OVER(ORDER BY Data ASC) As ProximoPedidoEm
FROM Pedido 
ORDER BY Data ASC

WITH CTE: Caso você precise de uma massa de dados múltiplas vezes, você pode incluir este resultado em uma CTE (Common Table Expressions) e reutilizar a mesma. Consultas recursivas (exemplo: empregado com superior, itens de menu, etc.), também podem usufruir deste recurso.

WITH totalizacao
As
(
	SELECT
		SUM(Valor) As Valor
	FROM Pedido
)
SELECT
	  Data
	, Valor
	, ROUND(Valor / (SELECT Valor FROM totalizacao) * 100, 2) As Percentual
FROM Pedido p

FORMATMESSAGE: Opção para formatar strings sem ter que ficar utilizar o caractere de concatenação (+).

SELECT
	  Data
	, Valor
	, FORMATMESSAGE('Status: %s.', IIF(Pago = 1, 'Pago', 'Não Pago')) As Pagamento
FROM Pedido

EXCLUSÃO DE OBJETOS: Sintaxe mais simples para remoção de objetos.

-- Antes
IF OBJECT_ID('Pedido', 'U') IS NOT NULL
	DROP TABLE Pedido

-- Agora
DROP TABLE IF EXISTS Pedido

STRING_SPLIT: Função que recebe uma string (podendo ser uma coluna de uma tabela) separada por um determinado caractere e permite utilizar o resultado na cláusula FROM ou JOIN.

SELECT value FROM string_split('CQRS;HTTP;Data', ';')

COMPRESSÃO: Opção nativa para compressão e descompressão de dados utilizando o varbinary.

INSERT INTO Pedido VALUES (COMPRESS('Dados da Nota Fiscal 123'))

SELECT CONVERT(Varchar(100), DECOMPRESS(NotaFiscal)) FROM Pedido
Anúncios

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s