neste artigo exploramos as cláusulas de OFFSET e FETCH. OFFSET e FETCH são usados em conjunto com a ordem de instrução selecionada por cláusula para fornecer um meio de recuperar uma gama de registros. A linha inicial a retornar é determinada pelo valor de deslocamento e pelo número máximo de linhas a retornar a partir desse ponto por busca.

Todos os exemplos para esta lição são baseados no Microsoft SQL Server Management Studio e na Base de dados AdventureWorks2012. Você pode começar a usar estas ferramentas gratuitas usando o meu guia começar a usar o servidor SQL.

usando OFFSET e FETCH com a ordem pela cláusula

retornando linhas de uma declaração SQL pode ser um caso de tudo ou nada. Em muitos casos, o número de linhas retornadas é muito grande e isso pode causar problemas se você só precisa fazer parte do conjunto de resultados.

Quando os resultados são ordenados usando a cláusula ordem por cláusula, então algumas opções entram em jogo para limitar o número de linhas devolvidas:

1. Você pode usar TOP para devolver um número especificado de linhas.
2. pode usar o deslocamento e a obtenção.

neste artigo mergulhamos para saber mais sobre deslocamento e busca. Para saber mais sobre TOP, leia o artigo começando com o servidor SQL: 2. Ordene Os Resultados Da Sua Consulta.

deslocamento

o argumento de deslocamento é usado para identificar o ponto inicial para retornar linhas de um resultado. OFFESET é chamado de argumento, uma vez que é tecnicamente parte da ordem por cláusula. O deslocamento é o número de linhas a pular antes de incluí-las no resultado.

a forma geral para o argumento de compensação é:

SELECT columnsFROM tableORDER BY columns OFFSET rows-to-skip ROWS

onde as linhas-a-saltar são um valor maior ou igual a zero.

Por exemplo, para mostrar a todos, mas o primeiro de 10 funcionários, ordenados por HireDate você poderia escrever

SELECT NationalIDNumber, JobTitle, HireDateFROM HumanResources.EmployeeORDER BY HireDate OFFSET 10 ROWS

Aqui estão algumas coisas a considerar sobre o DESLOCAMENTO

• DESLOCAMENTO é parte da cláusula ORDER BY. Não pode ser utilizado isoladamente.os valores de desvio
• devem ser iguais ou superiores a zero. Um número negativo resulta em erro.
• Quando o deslocamento é 0, então nenhuma linha é ignorada.
• Se o deslocamento for maior do que o número de linhas nos resultados ordenados, então nenhuma linha é retornada.

FETCH

o argumento FETCH é usado para devolver um conjunto de linhas. FETCH não pode ser usado por si só, ele é usado em conjunto com OFFSET.continuando com o nosso exemplo, podemos mostrar o 11.º a 15. º empregados contratados usando esta declaração

SELECT NationalIDNumber, JobTitle, HireDateFROM HumanResources.EmployeeORDER BY HireDate OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY

no diagrama seguinte pode ver deslocamento e busca no trabalho. O OFFSET está a ser usado para saltar as primeiras 10 linhas e o FETCH é então usado para mostrar os próximos 5.

A combinação de OFFSET e FETCH facilitar a obtenção de um “deslizar” janela de linhas. O início da janela é determinado por deslocamento e a altura por obtenção.

usa para compensar e obter

Paging

uma das utilizações mais populares para compensar e obter é a chamada de pessoas. Sem dúvida você visitou o site onde você vê uma lista de itens e no fundo há uma lista de números de páginas ou um botão próximo.

usamos paging o tempo todo na web. O exemplo mais conhecido que eu posso pensar é o Google:

Os números abaixo do Google representam números de página. Uma parte dos resultados da pesquisa são retornados com cada clique.de forma semelhante, suponha que tenhamos uma página web exibindo funcionários por contratada. Se quiséssemos exibir 20 funcionários em uma página ,e estivéssemos exibindo a terceira página (funcionários 21-30), poderíamos usar a seguinte consulta:

SELECT NationalIDNumber, JobTitle, HireDateFROM HumanResources.EmployeeORDER BY HireDate OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY

Esta consulta instrui o SQL para:

1. Seleccione as informações do funcionário
2. Ordem das informações, HireDate
3. Ignorar 20 linhas e começar a mostrar resultados a partir de 21
4. Exibir o próximo 10 linhas de resultados.

obter os registos de topo

Se desejar obter as dez linhas de topo de uma consulta, então poderá fazê-lo se definir o deslocamento para 0. Lembre-se que o deslocamento indica o número de linhas a ignorar. Ao ajustá-lo para zero, estamos a dizer ao SQL para começar na primeira fila.

Uma vez definido o deslocamento, a próxima ordem de trabalho é definir a obtenção. Já que estamos à procura do Top 10, temos o FETCH ao lado do 10.

SELECT NationalIDNumber, JobTitle, HireDateFROM HumanResources.EmployeeORDER BY HireDate OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY

Se estiver familiarizado com a cláusula de topo, pode ter notado que existem semelhanças entre estes dois métodos. Por exemplo, para o exemplo acima usando TOP ficaria

SELECT TOP 10 NationalIDNumber, JobTitle, HireDateFROM HumanResources.EmployeeORDER BY HireDate

Existem aspectos a TOP que não se aplicam ao OFFSET e FETCH; como sendo permitido em declarações sem uma ORDEM, mas como você pode ver por este exemplo, eles retornam resultados equivalentes.

obtendo registros de fundo

para obter os registros de fundo em um conjunto de resultados, há duas maneiras. O primeiro é ordenar o resultado em ordem descendente em vez de ascendente. Isto coloca os resultados de baixo para o topo. Então você pode usar o fetch como normal.

SELECT NationalIDNumber, JobTitle, HireDateFROM HumanResources.EmployeeORDER BY HireDate DESC OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY

Se você não deseja alterar a ordem dos resultados, você pode usar uma sub consulta para obter a contagem de registros. Isto pode então ser usado para definir o offset em conformidade.

SELECT NationalIDNumber, JobTitle, HireDateFROM HumanResources.EmployeeORDER BY HireDate OFFSET (SELECT COUNT(*) FROM HumanResources.Employee)-10 ROWS FETCH NEXT 10 ROWS ONLY

existe um certo perigo neste método, uma vez que a expressão para calcular o desvio pode resultar num valor inferior a zero. No nosso exemplo, isto poderia acontecer se o número total de linhas fosse inferior a dez. Para se defender contra esta condição você gostaria de incorporar uma declaração de caso em sua lógica para verificar contra esta condição.

amostragem de dados no meio de um conjunto de resultados

uma característica agradável de obtenção e deslocamento você é capaz de escrever SQL para amostra ou testar dados a partir do meio do resultado. Este é realmente útil se você precisa ver dar uma olhada no meio de um conjunto de resultados, talvez um contendo milhões de linhas, sem mostrar todas as linhas até o ponto que você deseja rever.

para amostrar no meio você usaria a mesma lógica que você usaria para paging. Claro, o número de linhas que você obtém neste caso pode ser muito maior.

efeito da ordem por OFFSET e FETCH

OFFSET e FETCH apenas trabalho em conjunto com uma ordem por cláusula. Com efeito, SQL primeiro recupera os dados que você especificou, como colunas, em seguida, ordenar os dados em ordem ascendente ou descendente.

apenas depois deste passo ter terminado são as linhas saltadas e os resultados produzidos.

Limite Casos

uma vez que alguns valores podem resultar em um erro como um DESVIO negativo, vamos explorar as várias combinações de valores que podem exceder o número de linhas em uma tabela para entender quais valores são seguros de usar, e quais os valores lançaria um erro de SQL.basearemos os nossos exemplos nas fontes humanas.Tabela de empregados, que contém 290 linhas.

O único caso que resulta em um erro é quando o DESVIO é negativo. Há casos que não retornam linhas, ou podem retornar menos linhas do que você pensa, mas esses casos não lançam erros. Para a maior parte, essas situações ocorrem quando o valor OFFSET é maior do que o número de linhas no resultado (todas as linhas são ignoradas), ou você está tentando obter “passado” então fim do resultado.