Transact-SQL: T-SQL e a cláusula TOP

O Transact-SQL (Transact-Structure Query Language) é uma extensão para a linguagem de programação de banco de dados SQL. Uma linguagem poderosa que oferece muitas características, além de uma grande variedade de tipos de dados, objetos temporários​, controle de transações, exceção e tratamento de erros, dentre outros recursos.

Trabalhando com a cláusula TOP

A cláusula TOP permite controlar o número de linhas a serem devolvidas ou que são afetadas por uma consulta. Percebemos melhor a utilização dessa cláusula quando desejamos retornar um subconjunto de linhas de um conjunto de resultados. A cláusula TOP nos permite uma maneira fácil de satisfazer a esta necessidade. Alguns dos exemplos que podemos citar para a utilização dessa cláusula são quando:

• Queremos retornar apenas um subconjunto de registros para validar nossos códigos ou;
• Quando precisamos determinar que pelo menos uma linha atenda a uma condição WHERE especial.

Para que possamos explicar como esta cláusula funciona, deixe-nos dar-lhe a sintaxe básica e alguns exemplos. Desta forma, será mais fácil para entendermos e vermos as diferentes nuanças de se utilizar a cláusula TOP que podem afetar o que é retornado das nossas consultas.

Sintaxe da cláusula TOP

A sintaxe para a cláusula TOP é relativamente simples, como apresentada pela Listagem 1. Podemos usar a cláusula TOP colocando esta sintaxe em qualquer instrução SELECT, DELETE, INSERT ou UPDATE.

Listagem 1. Sintaxe da cláusula TOP.

TOP (expression) [PERCENT]
      [WITH TIES]

O valor de "expression" é um valor numérico. Este valor numérico será convertido para um tipo de dados de ponto flutuante se a opção PERCENT for usada, ou de um tipo de dados BIGINT se a opção PERCENT não for utilizada. O valor de "expression" pode ser representado como um número ou pode ser especificado como uma variável local.

A opção PERCENT, quando especificada, identifica que o SQL Server retornará uma porcentagem de linhas do conjunto de resultados com base no número representado pelo valor "expression". Quando a opção PERCENT for omitida, o SQL Server irá retornar o número de linhas representadas pelo valor "expression".

A opção WITH TIES, que foi introduzida na versão 2005 do SQL Server, é usada para dizer ao SQL Server para retornar todas as linhas que têm os mesmos valores para o ORDER BY colunas como a última linha. Ao usar a opção WITH TIES, pode ser que o SQL Server retorne mais linhas do que o especificado pelo valor da "expression". A opção WITH TIES só é válida para instruções SELECT que contenham uma cláusula ORDER BY.

Criando um exemplo básico

Antes que possamos mostrar um exemplo das diferentes formas de usarmos a cláusula TOP, será necessário criarmos primeiramente uma tabela de exemplo e então inserir alguns dados. Na Listagem 2 criamos uma tabela, a qual chamaremos de dbo.DevmediaVendas para ser usada em todos os exemplos utilizados aqui.

Listagem 2. Criando a tabela dbo.DevmediaVendas.

SET NOCOUNT ON;
      USE tempdb;
      CREATE TABLE dbo.DevmediaVendas ( 
                      AgenteVendas varchar(30), 
                      Regiao varchar(10), 
                      AcumuladoVendas decimal(10,2));
      INSERT INTO dbo.DevmediaVendas VALUES 
          ('João Vasconcelos', 'Oeste', 78931.01),
          ('Maria João', 'Oeste', 862341.63),
          ('Mariana Correia', 'Oeste', 20001121.67),
          ('Martin Amélio', 'Oeste', 120845.80),
          ('Edson Dionisio', 'Leste', 12008500.60), 
          ('Olga Meyer', 'Oeste', 519021.48),
          ('Samuel Guerreiro', 'Leste', 862341.63);

Nossa tabela exemplo contém informações de vendas de uma empresa fictícia chamada Devmedia. A Companhia Devmedia possui residências para vendas em duas regiões: Leste e Oeste. Cada região tem cinco agentes de vendas. Na Listagem 2 que criamos, temos informações de AcumuladoVendas (valor total das vendas) para cada agente e em cada região.

Exemplo simples da cláusula TOP

Para o nosso primeiro exemplo, temos a exigência de encontrar a parte superior dos dois Agentes de Vendas da Companhia com base no valor acumulativo de suas vendas. O código apresentado na Listagem 3 contém a solução com a cláusula TOP.

Listagem 3. Encontrando os dois agentes com maior acúmulo de vendas

USE tempdb;
      GO
      SELECT TOP(2) AgenteVendas, Regiao, AcumuladoVendas
      FROM dbo.DevmediaVendas
      ORDER BY AcumuladoVendas DESC;

No exemplo da Listagem 3 incluímos a cláusula ORDER BY, pois ela se faz necessário para garantir que a cláusula TOP selecione o Agente de vendas que possua o maior valor acumulado de vendas. Quando usamos a cláusula TOP sem a cláusula ORDER BY, o SQL Server retorna um conjunto aleatório de informações. Para um teste simples, removam a cláusula ORDER BY e executem novamente a consulta da Listagem 3 para ver quais linhas aleatórias serão devolvidas.

Exemplo Usando a opção PERCENT da cláusula TOP

Se quisermos retornar um percentual dos principais registros em um conjunto, então precisaremos utilizar a cláusula TOP com a opção PERCENT. Vejamos no código da Listagem 4 como será a visualização de um percentual de 50%.

Listagem 4. Retornando o Top 50% das linhas

      USE tempdb;
      GO
      SELECT TOP(50) PERCENT AgenteVendas, Regiao, AcumuladoVendas
      FROM dbo.DevmediaVendas
      ORDER BY AcumuladoVendas DESC;

Usando uma variável para controlar a cláusula TOP

Há momentos em que não sabemos com antecedência o número de linhas que desejamos retornar. Se este for o caso, podemos usar uma variável para controlar a identificação do número de linhas que serão retornadas. Este exemplo será de acordo com o exposto pela Listagem 5.

Listagem 5. Usando uma variável para identificar o número de linhas para retornar

USE tempdb;
      GO
      DECLARE @Number INT = 2;
      SELECT TOP(@Number) AgenteVendas, Regiao, AcumuladoVendas
      FROM dbo.DevmediaVendas
      ORDER BY AcumuladoVendas DESC;
       
      SET @Number = 50;
      SELECT TOP(@Number) PERCENT AgenteVendas, Regiao, AcumuladoVendas
      FROM dbo.DevmediaVendas
      ORDER BY AcumuladoVendas DESC;

No exemplo apresentado na Listagem 5 declaramos uma variável chamada @Number para controlar o número de linhas retornadas a partir das duas consultas SELECT distintas. Na primeira instrução SELECT que definimos, a variável @Number é 2. Definimos, então, a variável @Number na segunda instrução SELECT para 50 para que ele retorne os mesmos resultados que estão presentes na Figura 1.

Figura 1. Resultado das consultas com @Number

Usando a opção WITH TIES

Para nos certificarmos de que nossa consulta TOP sempre retorne todas as linhas que têm valores de colunas que são iguais para a última linha, devemos então incluir a opção WITH TIES (“Com laços”). Ao usar esta opção, é possível trazer de volta mais linhas do que o número que especificarmos com a cláusula TOP. Vejamos, de acordo com a Listagem 6, o resultado obtido com essa opção.

Listagem 6. Usando a opção WITH TIES

USE tempdb;
      GO
      SELECT TOP(2) WITH TIES AgenteVendas, Regiao, AcumuladoVendas
      FROM dbo.DevmediaVendas
      ORDER BY AcumuladoVendas DESC;

Usando a cláusula TOP com uma instrução UPDATE

Podemos limitar o número de linhas afetadas por uma instrução UPDATE usando a cláusula TOP. O código apresentado na Listagem 7 atualizará duas linhas aleatórias na nossa tabela DevmediaVendas.

Listagem 7. UPDATE com a cláusula TOP sem ordenar dados

USE tempdb;
      GO
      -- Registros antes do UPDATE
      SELECT * FROM dbo.DevmediaVendas
      WHERE Regiao = 'Oeste';
      -- Update usando a cláusula TOP
      UPDATE TOP (2) dbo.DevmediaVendas
      SET AcumuladoVendas = 100000.00
      FROM dbo.DevmediaVendas
      WHERE Regiao = 'Oeste'
      -- Registros após Update
      SELECT * FROM dbo.DevmediaVendas
      WHERE Regiao = 'Oeste';

Ao analisar o código da Listagem 7 podemos ver que são exibidos os registros na tabela dbo.DevmediaVendas antes e depois da instrução de atualização. Os resultados com base nessa atualização estão sendo exibidos na Figura 2.

Figura 2. Registros antes e depois do update.

Ao analisar os resultados da Listagem 7, podemos observar que a instrução UPDATE atualizou o valor AcumuladoVendas para João Vasconcelos e Maria João. Se compararmos as linhas atualizadas para suas linhas originais antes de serem atualizadas, podemos ver que o valor AgenteVendas, que teve o maior e o menor AcumuladoVendas na região Oeste, foram os que foram atualizados. Este não é o resultado que queríamos! A razão pela qual isso aconteceu é porque a nossa declaração UDPATE atualizou aleatoriamente a nossa tabela para os registros que tiveram um valor para a região Oeste.

Desde que o que realmente queremos seja restringir o número de instruções da atualização usando a cláusula TOP e queiramos ter certeza de que atualizaremos os registros com base no AcumuladoVendas, precisamos escrever nosso código de forma diferente, de acordo com o que fizemos na Listagem 8.

Listagem 8. UPDATE com a cláusula TOP dos dados.

USE tempdb;
      GO
      UPDATE dbo.DevmediaVendas  
      SET AcumuladoVendas = 6666666.00
      FROM 
      (SELECT TOP(2) AgenteVendas FROM dbo.DevmediaVendas
       WHERE Regiao = 'Oeste'
       ORDER BY AcumuladoVendas DESC) TS
      WHERE dbo.DevmediaVendas.AgenteVendas = TS.AgenteVendas
      -- Records after Update
      SELECT * FROM dbo.DevmediaVendas
      WHERE Regiao = 'Oeste';

Ao analisarmos a saída, de acordo com a Figura 3, e compararmos os valores da coluna para AgenteVendas Mariana Correia e Olga Meyer, com os valores apresentados na Figura 2, para o mesmo valor AgenteVendas, veremos que esses dois valores AgenteVendas tem o top de vendas para a região Oeste. A consulta na Listagem 8 conseguiu isso através da criação de uma sub-consulta que continha os dois principais valores AgenteVendass, que foram representados pelo alias (“apelido”) TS. Os valores AgenteVendas retornados por essa subconsulta foram então usados ​​na cláusula WHERE para restringir a instrução UPDATE, a fim de que apenas esses dois valores AgenteVendas tivessem o AcumuladoVendas atualizado para 6.666.666,00.

Figura 3. Update com a cláusula TOP.

Usando a cláusula TOP com uma instrução INSERT

Haverá momentos em que desejaremos inserir o subconjunto de linhas do resultado de uma consulta em uma tabela. Isto pode ser conseguido usando a cláusula TOP em conjunto com um INSERT. Cuidados devem ser tomados nos casos onde colocamos esta cláusula, caso contrário, não será possível à inserção dos registros onde desejamos.

Para o nosso exemplo, queremos inserir os dois valores principais AgenteVendas e AcumuladoVendas em uma tabela chamada dbo.TopVendas. A Listagem 9 tem a nossa primeira tentativa em satisfazer as nossas necessidades.

Listagem 9. Cláusula TOP na instrução INSERT

USE tempdb;
      GO
      CREATE TABLE dbo.TopVendas ( 
                      AgenteVendas varchar(30), 
                      Regiao varchar(10), 
                      AcumuladoVendas decimal(10,2));
      INSERT TOP(2) INTO dbo.TopVendas
      SELECT * FROM dbo.DevmediaVendas
      ORDER BY AcumuladoVendas DESC;
      SELECT * FROM dbo.TopVendas;

Na Listagem 9 vemos que não foram atendidos exatamente os requisitos referidos acima. Ao analisarmos a saída produzida a partir desta consulta, podemos observar que não foram inseridos os dois melhores valores de AcumuladoVendas. Isto porque, quando colocamos a cláusula TOP, mesmo ao lado da cláusula INSERT, ele disse ao SQL Server para inserir aleatoriamente duas linhas a partir dos resultados da subconsulta SELECT. Para inserirmos corretamente os dois primeiros valores AcumuladoVendas, precisamos escrever uma subconsulta SELECT para retornar apenas as duas linhas que queremos inserir, como fizemos na Listagem 10.

Listagem 10. Inserindo dados corretamente na tabela Two AgenteVendas com Top

USE tempdb;
      GO
      DROP TABLE dbo.TopVendas;
      CREATE TABLE dbo.TopVendas ( 
                      AgenteVendas varchar(30), 
                      Regiao varchar(10), 
                      AcumuladoVendas decimal(10,2));
      INSERT INTO dbo.TopVendas
      SELECT TOP(2) * FROM dbo.DevmediaVendas
      ORDER BY AcumuladoVendas DESC;
      SELECT * FROM dbo.TopVendas;

Ao analisarmos a saída encontrada na Figura 4, que foi produzida pela instrução SELECT final da Listagem 10, podemos ver que esta instrução INSERT colocou as duas linhas de forma correta na tabela dbo.TopVendas.

Figura 4. Inserção dos Tops da venda na tabela auxiliar.

Usando a cláusula TOP com uma instrução DELETE

Como dito anteriormente, podemos usar a cláusula TOP também em instruções do tipo DELETE. Claro que alguns cuidados devem ser tomados quando tratamos dessa instrução, já que ela pode excluir registros que não gostaríamos. Se realmente desejarmos excluir linhas aleatórias, então podemos escrever uma cláusula TOP, como feita na Listagem 11.

Listagem 11. Código para excluir aleatoriamente duas fileiras

USE tempdb;
      GO
      BEGIN TRANSACTION;
      SELECT * FROM dbo.DevmediaVendas;
      DELETE TOP (2) FROM dbo.DevmediaVendas
      WHERE AcumuladoVendas < 6666667.00;
      SELECT * FROM dbo.DevmediaVendas;
      ROLLBACK;

Observem que usamos neste caso uma instrução ROLLBACK na listagem 11, assim, nenhuma linha foi realmente excluída da tabela de exemplo. Caso queiramos realmente excluir as duas linhas que tenham o menor valor em AcumuladoVendas, precisaríamos usar uma subconsulta para selecionar um valor de chave única que iria identificar as linhas que continham os dois valores mais baixos dos totais de vendas. Assim, temos o exemplo feito na Listagem 12.

Listagem 12. Excluindo dados da tabela DevmediaVendas com TOP.

USE tempdb;
      GO
      BEGIN TRANSACTION;
      DELETE FROM dbo.DevmediaVendas
      WHERE AgenteVendas in (SELECT TOP(2) AgenteVendas FROM dbo.DevmediaVendas
                           ORDER by AcumuladoVendas ASC);
      SELECT * FROM dbo.DevmediaVendas;
      ROLLBACK;

Desta forma, onde ocorrerem os dois valores AgenteVendas que contenham os valores mais baixos associados a AcumuladoVendas, eles serão apagados. Se quisermos remover também todos os registros que tenham o mesmo valor que o último registro identificado pela cláusula TOP, precisaremos adicionar a opção WITH TIES. Agora ao executarmos o código da Listagem 13, teremos também excluído o registro dbo.DevmediaVendas de "João Vasconcelos", além do "Jason Ramos" e "Maria João".

Listagem 13. Corretamente Inserindo tabela em Top Two AgenteVendas

DELETE FROM dbo.DevmediaVendas
      WHERE AgenteVendas in (SELECT TOP(2) WITH TIES AgenteVendas FROM dbo.DevmediaVendas
                           ORDER by AcumuladoVendas ASC);
      SELECT * FROM dbo.DevmediaVendas;

Depois de executarmos este código, podemos usar a instrução SELECT para mostrar que ambas as linhas que tiveram um AcumuladoVendas abaixo de 1000000.00 foram excluídas.

Cuidados ao usar a cláusula TOP com UNION, EXCEPT e INTERSECT

Agora, indo um pouco mais além, precisamos ser cuidadosos ao utilizar a cláusula TOP quando se trata de gravações de consultas com tipos UNION, UNION ALL, INTERSECT ou EXCEPT. Se suas consultas individuais não contêm a instrução ORDER BY, elas poderão não obter os resultados esperados. Para esta demonstração estaremos usando o código da Listagem 14 para criar os dados de teste.

Listagem 14. Criação da tabela Vendas.

USE tempdb;
      CREATE TABLE dbo.Vendas ( 
                      NomeAgente varchar(30), 
                      Regiao varchar(10), 
                      AcumuladoVendas decimal(10,2));
      INSERT INTO dbo. Vendas VALUES 
          ('João Vasconcelos', 'Oeste', 1100000.00),
          ('Maria João', 'Oeste', 2453252.90),
          ('Mariana Correia', 'Oeste', 99101.45),
          ('Samuel Guerreiro', 'Leste', 862341.63),
          ('Nicolas Tavares', 'Leste', 983422.87),
          ('Jason Ramos', 'Leste', 135424.51);

Na tabela criada na Listagem 14 temos um conjunto de registros de vendas que seguem o valor da quantidade total de vendas para vários agentes de vendas. Queremos então escrever uma consulta que retorne o agente de vendas que mais vendeu em cada região. A fim de fazer isso, vamos usar a cláusula TOP, a cláusula WHERE e o operador UNION. Para demonstrar como a cláusula TOP em uma instrução UNION pode não retornar os resultados esperados, executaremos o código apresentado na Listagem 15.

Listagem 15. Cláusula TOP e os retornos imprevisíveis.

USE tempdb;
      GO
      SELECT TOP(1) NomeAgente, Regiao, AcumuladoVendas 
                      FROM dbo.Vendas
                      WHERE Regiao = 'Leste'
      UNION
      SELECT TOP(1) NomeAgente, Regiao, AcumuladoVendas
                      FROM dbo.Vendas
                      WHERE Regiao = 'Oeste'
      ORDER BY AcumuladoVendas DESC;

Melhores Práticas para trabalhar com a cláusula TOP

Ao utilizarmos a cláusula TOP, devemos considerar alguns pontos importantes, que são:

• A fim de garantir resultados consistentes, sempre usar uma cláusula ORDER BY quando se usa uma cláusula TOP. Isso fará com que suas pesquisas retornem corretamente as linhas superiores de um conjunto ordenado de dados. Se não usarmos a cláusula ORDER BY, o SQL Server retornará um conjunto aleatório de linhas. Isso ocorre porque um conjunto de dados produzido sem a cláusula ORDER BY não tem fim.
• Não usem a cláusula TOP para executar uma função de paginação ou de janelas para a sua aplicação. É mais fácil implementar uma função de janelas ou paginação usando as opções de offset e FETCH da cláusula ORDER BY.
• Certifiquem-se de que será usada uma cláusula ORDER BY em cada instrução individual de SELECT ao usar TOP com operadores UNION, UNION ALL, INTERSECT e EXCEPT. Se não fizermos isso, então nossas instruções SELECT podem retornar linhas aleatórias que nos darão resultados diferentes do que os esperados.

Então é isso pessoal, nesse artigo apresentamos uma maneira de como trabalharmos com a cláusula TOP em nossos projetos para que, dessa forma, não tenhamos problemas futuros em nossas aplicações. Nos vemos na próxima, até mais!