O que mudou no SQL Server 2012 em relação ao T-SQL – Na visão dos Desenvolvedores

Uma necessidade muito comum no desenvolvimento de aplicações é o uso de paginação de dados no SQL Server, de modo que apenas uma quantidade limitada de registros sejam retornados pelas consultas, fazendo com que menos dados sejam processados na saída e as consultas tenham um tempo de resposta menor. Cenário esse, que é bastante comum em aplicações, que costumam paginar os dados na tela, tanto para evitar o excesso de informação quanto para diminuir o tempo necessário para carregar as informações.

Com o surgimento do ROW_NUMBER() no SQL Server 2005, muitas pessoas passaram a utilizar essa função para criar paginação de dados, funcionando desta forma:

Resultado:

Entretanto, a partir do SQL Server 2012 temos a funcionalidade de paginação nativa no próprio SQL Server e que muita gente acaba não utilizando por desconhecimento. Estamos falando do recurso OFFSET e FETCH, que funcionam juntos para permitir que possamos paginar os nossos resultados antes de exibir e enviá-los para as aplicações e clientes.

Vejam com o seu uso é simples:

Resultado:

Caso você queira saber mais sobre esse recurso, não deixe de visitar o meu artigo SQL Server – Como criar paginação de dados nos resultados de uma consulta com OFFSET e FETCH

Velha conhecida dos DBA’s Oracle, a feature de Sequence foi introduzida no SQL Server 2012 e consiste em criar sequenciais (parecido com o IDENTITY) não associados a um objeto específico, ou seja, você pode criar um sequencial para uma tabela específica ou pode utilizar uma sequence para mais de uma tabela de modo que os números dos ID’s não serão repetidos entre as tabelas envolvidas.

Exemplo clássico: Tabelas Pessoa_Fisica e Pessoa_Juridica. Caso você utilize IDENTITY para gerar um sequencial, as 2 tabelas terão um registro com o ID = 25, por exemplo. Caso você utilize uma sequence única para controlar o ID dessas duas tabelas, o ID = 25 só existirá em uma das duas tabelas, funcionando assim:

Código-fonte do teste

Resultado

Caso você queira saber mais sobre as Sequences no SQL Server, leia o meu artigo SQL Server 2012 – Trabalhando com Sequences e comparações com IDENTITY.

Todo bom desenvolvedor de query e rotinas de banco utiliza tratamento de erros e exceções em seus códigos T-SQL. A partir do SQL Server 2005, a Microsoft introduziu o recurso de TRY…CATCH no T-SQL, onde poderíamos gerar exceções utilizando o comando RAISEERROR. A partir do SQL Server 2012, foi incorporado ao SGBD o novo método de THROW, que permite simplificar e deixar mais inteligente, a geração de exceções no banco de dados.

Tratamento de divisão por zero utilizando RAISEERROR

Resultado:

Tratamento de divisão por zero utilizando THROW

Resultado:

Bem mais simples né ? E não para por aí.. Para utilizar mensagens personalizadas, o THROW também é muito mais simples, pois o código do erro da mensagem (error_number) não precisa estar cadastrado na sys.messages.

Outra vantagem do THROW, é possível gravar output das mensagens de erro retornadas pelo THROW em uma tabela de histórico (utilizando o RAISEERROR você não consegue capturar o retorno do comando, mas também consegue criar esse histórico inserindo os registros na tabela e capturando as mensagens utilizando as funções ERROR_%, como a ERROR_MESSAGE())

Para se aprofundar mais sobre as vantagens do THROW, não deixe de conferir o artigo Aplicando tratamento de erros no microsoft sql server 2012 e 2014 – utilizando o comando throw.

Bastante conhecida por programadores, a função IIF foi introduzida no SQL Server 2012 e seu objetivo é simplificar operações lógicas simples, que possuem apenas 2 resultados possíveis.

Exemplo simples com CASE

Exemplo simples com IIF

Bem mais simples, né ? Entretanto, se você precisa de vários resultados, recomendo continuar utilizando o CASE, pois o IIF acaba ficando muito complexo de dar manutenção quando ele é aninhado com vários outros IIF’s.

Outra função disponível a partir do SQL Server 2012 é a função de lógica CHOOSE. Essa função permite recuperar um valor baseado na posição numérica (índice) de uma lista de valores. Essa função pode ser utilizada para simplificar alguns cenários em que você teria que colocar várias condições CASE.

Exemplo de uso:

Resultado:

Outro exemplo, agora recuperando o índice a partir do resultado de uma função:

Resultado:

Exemplos utilizando índices fora da lista e índices com decimais

Resultado:

Como podemos ver acima, quando o índice da função CHOOSE não está no intervalo da lista, a função irá retornar NULL. E quando utilizamos um índice com casas decimais, o índice será convertido (truncado) para um número inteiro.

Observação: Internamente, o CHOOSE é um atalho para o CASE, então os 2 tem o mesmo desempenho no que se refere à performance.

Funções bastante utilizadas durante o dia a dia de desenvolvedores de Query, as funções de conversão de tipos de dados tiveram uma grande evolução no SQL Server 2012 com as novas funções PARSE, TRY_PARSE, TRY_CONVERT e TRY_CAST.

A função PARSE (disponível para datas e números) é muito útil para conversões de alguns formatos diferentes do padrão, que o CAST e o CONVERT não conseguem converter, como o exemplo abaixo:

Resultado:

Como evolução da função PARSE, temos a função TRY_PARSE, que possui basicamente o mesmo comportamento da função PARSE, mas com o diferencial que quando a conversão não é possível, ao invés de retornar uma exceção durante o processamento, vai apenas retornar NULL.

Exemplo:

A mesma coisa ocorre com as funções TRY_CONVERT e TRY_CAST, que possuem o mesmo comportamento das funções originais, mas não geram exceção ao não conseguir realizar uma conversão.

Exemplos de uso:

Resultado:

Para saber mais sobre tratamentos de dados e conversões, veja o meu artigo SQL Server – Como identificar erros de conversão de dados utilizando TRY_CAST, TRY_CONVERT, TRY_PARSE, ISNUMERIC e ISDATE.

Função muito interessante para quem trabalha com BI e cálculos envolvendo datas e final de mês. Basicamente, essa função retorna o último dia do mês da data informada.

Exemplos de uso

Resultado

A partir do SQL Server 2012, temos 6 novas funções para geração de datas a partir de números inteiros, o que era bem chato de se fazer nas versões anteriores do SQL Server:

• DATEFROMPARTS ( year, month, day)
• DATETIME2FROMPARTS ( year, month, day, hour, minute, seconds, fractions, precision )
• DATETIMEFROMPARTS ( year, month, day, hour, minute, seconds, milliseconds )
• DATETIMEOFFSETFROMPARTS ( year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, precision )
• SMALLDATETIMEFROMPARTS ( year, month, day, hour, minute )
• TIMEFROMPARTS ( hour, minute, seconds, fractions, precision )

Script para geração dos dados de teste:

Exemplo da função DATEFROMPARTS e como fazíamos isso antes do SQL Server 2012:

Resultado:

Exemplo utilizando as 6 funções juntas

Resultado:

Uma das funções mais úteis no dia a dia de quem trabalha criando consultas e rotinas de BI, a função FORMAT permite aplicar máscara personalizadas em datas e números, bem similar ao método string.ToString() do C#.

Alguns exemplos do uso de FORMAT – Números

Resultado

Outros exemplos com números:

Resultado:

Preenchendo número com zeros à esquerda:

Alguns exemplos do uso de FORMAT – Datas

Resultado:

Formatação personalizada de Data:

Resultado:

Se quiser saber mais sobre a função FORMAT, leia o meu artigo SQL Server – Utilizando a função FORMAT para aplicar máscaras e formatações em números e datas.

Disponível a partir do SQL Server 2012, a função CONCAT permite concatenar variáveis, strings e/ou colunas de uma forma mais prática, a qual vou demonstrar suas vantagens logo abaixo:

Geração dos dados para os exemplos

Exemplos

Resultado:

Como vocês podem observar, a função CONCAT faz a conversão dos tipos de dados para varchar automaticamente e ainda faz o tratamento de valores NULL e os converte para strings vazias. Ou seja, mais simples, o CONCAT é, mas e a performance? Como fica ?

Pelo menos nos testes que eu fiz, o CONCAT se mostrou mais rápido que a concatenação tradicional (utilizando “+”). Mais simples e mais rápido.

Funções analítica bem interessantes, a função FIRST_VALUE retorna o primeiro valor de um resultset, onde você pode definir uma quebra opcional (PARTITION BY) e uma regra para ordenar os resultados (ORDER BY). Já a função LAST_VALUE, faz o inverso, retornando o último valor desse conjunto de dados.

Script T-SQL para a criação dos dados para o exemplo:

Exemplo 1 – Identificando a maior e menor idade e o nome dessas pessoas

Resultado:

Bem, o código ficou bem mais simples e limpo. Falando em performance, existem várias formas de atingir esse objetivo, com consultas mais performáticas que a que utilizei no primeiro exemplo, mas quis colocar o código mais simples possível. Se você realmente utiliza esse tipo de programação no seu código, é bom revisar suas consultas, pois elas tem uma performance bem ruim.

Veja como é o plano de execução da primeira consulta e da consulta utilizando a função FIRST_VALUE:

E agora vamos utilizar a função LAST_VALUE para retornar os maiores registros do conjunto de dados:

Resultado:

Ué.. A função LAST_VALUE não trouxe o último valor e sim o valor da linha atual.. Isso acontece devido ao conceito de frame. O frame permite que você especifique um conjunto de linhas para a “janela”, que é ainda menor que a partição. O frame padrão contém as linhas que começam com a primeira linha e até a linha atual. Para a linha 1, a “janela” é apenas linha 1. Para a linha 3, a janela contém as linhas 1 a 3. Ao usar FIRST_VALUE, a primeira linha é incluída por padrão, para que você não precise se preocupar com isso para obter os resultados esperados.

Para que a função LAST_VALUE retorne realmente o último valor de todo o conjunto de dados, vamos utilizar os parâmetros ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING junto com a função LAST_VALUE, o que faz com que a janela inicie na linha atual até a última linha da partição.

Novo script utilizando a função LAST_VALUE:

Resultado:

A partir do SQL Server 2012, temos disponíveis mais duas funções analíticas para acessar o registro anterior à um registro (LAG) e o registro subsequente (LEAD).

Script T-SQL para gerar os dados de teste:

Imaginem que eu queira criar um ponteiro e acessar quem é o próximo ID e o ID anterior ao registro atual. Antes do SQL Server 2008, precisaríamos criar self-joins para completar essa tarefa. A partir do SQL Server 2012, podemos utilizar as funções LAG e LEAD para essa necessidade:

Resultado:

Código bem mais simples e limpo. Vamos analisar a performance das duas consultas agora:

É, embora o LAG e o LEAD sejam bem mais simples e legíveis, a performance deles acaba sendo um pouco abaixo dos Self-Joins, provavelmente devido à ordenação que é feita para aplicar as funções. Vou inserir um volume maior de registros (3 milhões de registros) para verificar se a performance continua sendo pior que os Self-Joins, a qual eu acredito não fazer sentido, já que o Self-Join realiza várias leituras na tabela e as funções, em teoria, deveriam fazer apenas 1.

Resultados dos testes com 3 milhões de registros (me surpreenderam):

Plano de execução com SELF-JOIN:

Plano de execução com LED e LEAD:

Imaginei que por ter um plano mais simples e fazer menor leituras na tabela, as funções teriam um desempenho bem melhor que os self-joins, mas por causa de um operador de SORT bem pesado, a performance utilizando as funções acabou sendo pior, conforme podemos observar no plano de execução e no warning do operador sort:

Em resumo, se for trabalhar com pequenos conjuntos de dados, pode usar as funções LAG e LEAD tranquilamente, pois o código fica mais legível. Caso precise utilizar isso em grandes volumes de dados, teste o self-join para avaliar como será a performance no seu cenário.

Cenário que é um pouco comum no dia a dia dos profissionais de dados, capturar dados retornados por Stored Procedures pode acabar sendo um pouco trabalho quando é necessário realizar conversões de tipos dados. Nesse caso, é necessário utilizar uma tabela intermediária para realizar a conversão dos dados para depois utilizar os dados necessários.

A partir do SQL Server 2012, agora podemos utilizar a cláusula WITH RESULT SETS na execução de Stored Procedures, de modo que podemos mudar o nome e o tipo dos campos retornados por Stored Procedures, de forma muito simples e prática.

Exemplo 1:

Resultado:

Caso a Stored Procedure retorne mais de um conjunto de dados, você pode utilizar a cláusula WITH RESULT SETS dessa forma:

Resultado:

Um recurso novo que foi adicionado ao SQL Server 2012, é a possibilidade de retornar x% dos dados de uma tabela, utilizando o comando TOP. Isso é muito útil quando se quer retornar uma amostragem medida dos dados na forma de percentual.

A sua utilização é praticamente igual ao tradicional e já conhecido TOP:

Resultado:

Até a versão 2008, o SQL Server possuía 2 funções para cálculos com logaritmos: LOG (base padrão) e LOG10. Caso você necessitasse utilizar uma base personalizada em seus cálculos matemáticos, teria que implementar o cálculo manualmente.

A partir do SQL Server 2012, a função LOG agora possui um segundo parâmetro opcional, onde você pode informar qual a base que deseja utilizar no seu cálculo de logaritmo:
Exemplo: