Classic filters in SQL like ON, WHERE, and HAVING are based on predicates. TOP e OFFSET-FETCH são filtros baseados em um conceito diferente: você indica a ordem e quantas filas filtrar com base nessa ordem. Muitas tarefas de filtragem são definidas com base na ordem e no número de filas necessárias. É certamente bom ter suporte a linguagem no T-SQL que lhe permite formular o pedido de uma forma semelhante à forma como pensa sobre a tarefa.
Este capítulo começa com os aspectos lógicos do design dos filtros. Depois utiliza um cenário de paging para demonstrar sua otimização. O capítulo também cobre o uso do TOP com instruções de modificação. Finalmente, o capítulo demonstra o uso de TOP e OFFSET-FETCH na resolução de problemas práticos como o top N por grupo e mediana.
Os filtros TOP e OFFSET-FETCH
Você usa os filtros TOP e OFFSET-FETCH para implementar os requisitos de filtragem em suas consultas de uma forma intuitiva. O filtro TOP é uma característica proprietária no T-SQL, enquanto que o filtro OFFSET-FETCH é uma característica padrão. O T-SQL começou a suportar OFFSET-FETCH com o Microsoft SQL Server 2012. A partir do SQL Server 2014, a implementação de OFFSET-FETCH no T-SQL ainda falta um par de elementos padrão – interessantemente, aqueles que estão disponíveis com o TOP. Com a implementação actual, cada um dos filtros tem capacidades que não são suportadas pelo outro.
I’ll começar por descrever os aspectos lógicos de design do TOP e depois cobrir os do OFFSET-FETCH.
O filtro TOP
O filtro TOP é uma construção comumente usada no T-SQL. Sua popularidade provavelmente pode ser atribuída ao fato de seu projeto estar tão bem alinhado com a forma como muitos requisitos de filtragem são expressos – por exemplo, “Retornar as três encomendas mais recentes”. Neste pedido, a ordem para o filtro é baseada na data da ordem, descendente, e o número de linhas que você quer filtrar baseado nesta ordem é 3.
Você especifica a opção TOP na lista SELECT com um valor de entrada digitado como BIGINT indicando quantas linhas você quer filtrar. Você fornece a especificação do pedido na cláusula clássica ORDER BY. Por exemplo, você usa a seguinte consulta para obter as três ordens mais recentes.
USE TSQLV3;SELECT TOP (3) orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY orderdate DESC;
I obteve a seguinte saída desta consulta:
orderid orderdate custid empid----------- ---------- ----------- -----------11077 2015-05-06 65 111076 2015-05-06 9 411075 2015-05-06 68 8
Em vez de especificar o número de linhas que você quer filtrar, você pode usar TOP para especificar a porcentagem (do número total de linhas no resultado da consulta). Você faz isso fornecendo um valor no intervalo de 0 a 100 (digitado como FLOAT) e adiciona a palavra-chave PERCENT. Por exemplo, na seguinte consulta você pede para filtrar um por cento das linhas:
SELECT TOP (1) PERCENT orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY orderdate DESC;
SQL Server arredonda para cima o número de linhas computadas com base na porcentagem de entrada. Por exemplo, o resultado de 1 por cento aplicado a 830 linhas na tabela de Pedidos é 8.3. Arredondando este número para cima, você obtém 9. Aqui está a saída que obtive para esta consulta:
orderid orderdate custid empid----------- ---------- ----------- -----------11074 2015-05-06 73 711075 2015-05-06 68 811076 2015-05-06 9 411077 2015-05-06 65 111070 2015-05-05 44 211071 2015-05-05 46 111072 2015-05-05 20 411073 2015-05-05 58 211067 2015-05-04 17 1
Nota que para traduzir a porcentagem de entrada para um número de linhas, o SQL Server tem que primeiro calcular a contagem de linhas no resultado da consulta, e isto normalmente requer trabalho extra.
Interessantemente, a especificação de ordenação é opcional para o filtro TOP. Por exemplo, considere a seguinte consulta:
SELECT TOP (3) orderid, orderdate, custid, empidFROM Sales.Orders;
Eu obtive a seguinte saída desta consulta:
orderid orderdate custid empid----------- ---------- ----------- -----------10248 2013-07-04 85 510249 2013-07-05 79 610250 2013-07-08 34 4
A seleção das três linhas a retornar é não-determinística. Isto significa que se você executar a consulta novamente, sem que os dados subjacentes mudem, teoricamente você poderia obter um conjunto diferente de três linhas. Na prática, a seleção de linhas dependerá de condições físicas como opções de otimização, opções de mecanismos de armazenamento, layout de dados e outros fatores. Se você realmente executar a consulta várias vezes, desde que essas condições físicas não mudem, há alguma probabilidade de que você continue obtendo os mesmos resultados. Mas é fundamental entender o princípio da “independência de dados físicos” do modelo relacional e lembrar que, no nível lógico, você não tem garantia de resultados repetíveis. Sem especificação de pedido, você deve considerar a ordem como sendo arbitrária, resultando em uma seleção de linha não determinística.
Even quando você fornece especificação de pedido, isso não significa que a consulta é determinística. Por exemplo, uma consulta TOP anterior usava orderdate, DESC como a especificação do pedido. A coluna data do pedido não é única; portanto, a seleção entre linhas com a mesma data do pedido é não determinística. Então, o que fazer nos casos em que é necessário garantir o determinismo? Existem duas opções: usando COM TIES ou encomenda única.
A opção COM TIES faz com que os laços sejam incluídos no resultado. Aqui está como você aplica ao nosso exemplo:
SELECT TOP (3) WITH TIES orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY orderdate DESC;
Aqui está o resultado que obtive desta consulta:
orderid orderdate custid empid----------- ---------- ----------- -----------11077 2015-05-06 65 111076 2015-05-06 9 411075 2015-05-06 68 811074 2015-05-06 73 7
SQL Server filtra as três linhas com as datas de ordem mais recentes, mais inclui todas as outras linhas que têm a mesma data de ordem que na última linha. Como resultado, você pode obter mais linhas do que o número que você especificou. Nesta consulta, você especificou que queria filtrar três linhas mas acabou obtendo quatro. O que é interessante notar aqui é que a seleção de linhas agora é determinística, mas a ordem de apresentação entre linhas com a mesma data de ordem é não determinística.
O segundo método para garantir um resultado determinístico é tornar a especificação de ordenação única, adicionando um desempate. Por exemplo, você poderia adicionar ordenid, DESC como o desempate no nosso exemplo. Isto significa que, no caso de empates nos valores de data da ordem, uma linha com um valor de ID de ordem mais alto é preferível a uma linha com um valor mais baixo. Aqui está nossa consulta com o desempate aplicado:
SELECT TOP (3) orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY orderdate DESC, orderid DESC;
Esta consulta gera a seguinte saída:
orderid orderdate custid empid----------- ---------- ----------- -----------11077 2015-05-06 65 111076 2015-05-06 9 411075 2015-05-06 68 8
O uso de ordenação única faz tanto a seleção da linha quanto a ordenação da apresentação determinística. O conjunto de resultados assim como a ordenação da apresentação das linhas é garantidamente repetível desde que os dados subjacentes não mudem.
Se você tem um caso em que precisa filtrar um certo número de linhas mas realmente não se importa com a ordem, pode ser uma boa idéia especificar ORDER BY (SELECT NULL), assim:
SELECT TOP (3) orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY (SELECT NULL);
Desta forma, você deixa todos saberem que sua escolha de ordem arbitrária é intencional, o que ajuda a evitar confusão e dúvida.
Como um lembrete do que expliquei no Capítulo 1, “Processamento de consulta lógica”, sobre os filtros TOP e OFFSET-FETCH, a ordem de apresentação só é garantida se a consulta externa tiver uma cláusula ORDER BY. Por exemplo, na seguinte apresentação da consulta, a ordenação não é garantida:
SELECT orderid, orderdate, custid, empidFROM ( SELECT TOP (3) orderid, orderdate, custid, empid FROM Sales.Orders ORDER BY orderdate DESC, orderid DESC ) AS D;
Para fornecer uma garantia de ordenação de apresentação, você deve especificar uma cláusula ORDER BY na consulta externa, como assim:
SELECT orderid, orderdate, custid, empidFROM ( SELECT TOP (3) orderid, orderdate, custid, empid FROM Sales.Orders ORDER BY orderdate DESC, orderid DESC ) AS DORDER BY orderdate DESC, orderid DESC;
O filtro OFFSET-FETCH
O filtro OFFSET-FETCH é uma funcionalidade padrão projetada similar ao TOP, mas com um elemento extra. Você pode especificar quantas linhas você quer saltar antes de especificar quantas linhas você quer filtrar.
Como você poderia ter adivinhado, este recurso pode ser útil na implementação de soluções de paging – ou seja, retornar um resultado para o usuário um pedaço de cada vez, mediante solicitação, quando o conjunto de resultados completo é muito longo para caber em uma tela ou página web.
O filtro OFFSET-FETCH requer uma cláusula ORDER BY para existir, e é especificado logo após. Você começa indicando quantas linhas a saltar numa cláusula OFFSET, seguido de quantas linhas a filtrar numa cláusula FETCH. Por exemplo, com base na ordem indicada, a seguinte consulta salta as primeiras 50 linhas e filtra as próximas 25 linhas:
SELECT orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY orderdate DESC, orderid DESCOFFSET 50 ROWS FETCH NEXT 25 ROWS ONLY;
Em outras palavras, a consulta filtra as linhas 51 a 75. Em termos de paginação, assumindo um tamanho de página de 25 linhas, esta consulta retorna a terceira página.
Para permitir linguagem declarativa natural, você pode usar a palavra-chave FIRST em vez de NEXT se quiser, embora o significado seja o mesmo. Usando FIRST poderia ser mais intuitivo se você não estiver pulando nenhuma linha. Mesmo se não quiser saltar nenhuma linha, o T-SQL torna obrigatório especificar a cláusula OFFSET (com 0 ROWS) para evitar ambiguidade de análise. Da mesma forma, em vez de usar a forma plural da palavra-chave ROWS, pode usar a forma singular ROW tanto na cláusula OFFSET como na cláusula FETCH. Isto é mais natural se você precisar ignorar ou filtrar apenas uma linha.
Se você estiver curioso sobre qual é o propósito da palavra-chave SOMENTE, significa não incluir laços. SQL padrão define a alternativa COM TIES; no entanto, T-SQL ainda não a suporta. Da mesma forma, SQL padrão define a opção PERCENT, mas o T-SQL também ainda não a suporta. Estas duas opções em falta estão disponíveis com o filtro TOP.
Como mencionado, o filtro OFFSET-FETCH requer uma cláusula ORDER BY. Se você quiser usar ordem arbitrária, como TOP sem uma cláusula ORDER BY, você pode usar o truque com ORDER BY (SELECT NULL), como assim:
SELECT orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY (SELECT NULL)OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY;
A cláusula FETCH é opcional. Se você quiser pular um certo número de linhas mas não limitar quantas linhas a retornar, simplesmente não indique uma cláusula FETCH. Por exemplo, a seguinte consulta salta 50 linhas mas não limita o número de linhas retornadas:
SELECT orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY orderdate DESC, orderid DESCOFFSET 50 ROWS;
A ordem de apresentação é a mesma que com o filtro TOP; nomeadamente, com OFFSET-FETCH também, a ordem de apresentação é garantida apenas se a consulta mais externa tiver uma cláusula ORDER BY.