Zarówno TOP, jak i OFFSET & FETCH można wykorzystać do ogranicz liczbę zwracanych wierszy. OFFSET i FETCH mogą zwracać podobne wyniki na początek, ale istnieją różnice, które mogą wpłynąć na to, która metoda jest najlepsza dla Ciebie w danej sytuacji.
Wszystkie przykłady w tej lekcji są oparte na Microsoft SQL Server Management Studio i baza danych AdventureWorks2012. Możesz zacząć korzystać z tych bezpłatnych narzędzi, korzystając z mojego Przewodnika Pierwsze kroki z SQL Server.
Jaka jest różnica między TOP i OFFSET & Pobrać?
W kolejnych sekcjach omówimy TOP i OFFSET & FETCH. Myślę, że kiedy przeczytasz obie sekcje, zrozumiesz różnice między nimi. Są one również podsumowane w tabeli na końcu tego artykułu.
Góra
TOP służy do zwrócenia określonej liczby wierszy z wyniku zapytania.
Klauzula TOP jest dostępna od jakiegoś czasu w starszych wersjach serwera SQL, takich jak SQL 2005. TOP nie jest zgodny z ANSI i jego użycie ogranicza się głównie do produktów firmy Microsoft, takich jak SQL Server i MS-Access.
Klauzula TOP jest używana na liście kolumn w instrukcji SELECT i może być używana z lub bez ORDER BY.
Na przykład, aby wybrać dziesięć najmniej kosztownych produktów, użyj
SELECT TOP 10 Name, ProductNumber, StandardCostFROM Production.ProductORDER BY StandardCost
Chociaż ORDER BY nie jest wymagane, jest to najlepsza praktyka, ponieważ bez niej nie masz gwarancji, które wiersze zostaną zwrócone.
Jedna pozycja unikalna dla TOP to możliwość zwrócenia procentu wierszy. Instrukcja
SELECT TOP 10 PERCENT Name, ProductNumber, StandardCostFROM Production.ProductORDER BY StandardCost
zwraca 51 najlepszych produktów o najniższym koszcie standardowym (tj. 504 wiersze x 10% = 50,4, zaokrąglone w górę do 51).
Możesz również wybrać określoną liczbę rekordów u dołu wyniku zapytania. Aby to zrobić, możesz użyć sztuczki, aby uporządkować rekordy w porządku malejącym:
SELECT TOP 10 Name, ProductNumber, StandardCostFROM Production.ProductORDER BY StandardCost DESC
Pamiętaj, że gdy wyniki są sortowane w porządku malejącym, największe kwoty zwracane są w pierwszej kolejności. Z tego powodu TOP zwraca produkty o najwyższym standardowym koszcie.
TOP może być bardzo przydatne w przypadku dużych wyników, ponieważ zwracanie tysięcy wierszy może wpłynąć na wydajność. W wielu przypadkach użycie będzie po prostu przeglądać początkową część wyników.
TOP może wyświetlać wartość powiązań – WITH TIES pozwala również wyświetlić dodatkowe rekordy, które mają taką samą wartość jak ostatni wiersz z wyniku podstawowego.
OFFSET i FETCH
OFFSET i FETCH są używane do zwrócenia okna rekordów ze zbioru wyników. OFFSET określa, ile wierszy ma zostać pominiętych w wyniku, a FETCH określa, ile wierszy od tego punktu do przodu ma zwrócić wynik.
OFFSET i FETCH zostały niedawno wprowadzone w SQL Server 2012 i są zgodne z ANSI.
Możesz użyć OFFSET bez FETCH, ale FETCH nie może być użyte samodzielnie. Niezależnie od tego, OFFSET musi być używany z klauzulą ORDER BY. Przyczyna jest prosta, ponieważ OFFSET i FETCH są częścią klauzuli ORDER BY.
W tym przykładzie pierwsze dziesięć wierszy wyniku jest pomijanych, a następnych 10 jest wyświetlanych w wyniku.
SELECT Name, ProductNumber, StandardCostFROM Production.ProductORDER BY StandardCost OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY
Możesz replikować wiersze zwrócone z TOP za pomocą funkcji OFFSET i FETCH, pod warunkiem że wyniki są uporządkowane. Następująca instrukcja jest taka sama, jak zwracanie TOP 10:
SELECT Name, ProductNumber, StandardCostFROM Production.ProductORDER BY StandardCost OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY
Gdy OFFSET jest ustawione na 0, żadne wiersze nie są pomijane.
W przeciwieństwie do TOP, z OFFSET nie ma wbudowanych środków zwracających procent wierszy. Możesz jednak rzucić własną, obliczając procent w następujący sposób:
SELECT Name, ProductNumber, StandardCostFROM Production.ProductORDER BY StandardCost OFFSET 0 ROWS FETCH NEXT (SELECT CAST(CEILING(COUNT(*) * .1) as INT) FROM Production.Product) ROWS ONLY
Sztuczka tkwi w podzapytaniu. Obliczając liczbę wierszy w wyniku, jesteśmy w stanie uzyskać wartość procentową. Oto podzapytanie. Jeśli chcesz, możesz to uruchomić w SQL Server Management Studio.
SELECT CAST(CEILING(COUNT(*) * .1) as INT)FROM Production.Product
Jest kilka elementów uwaga:
- CEILING zaokrągla liczby w górę. W naszym przykładzie CEILING (50.4) zwraca 51,0. To naśladuje zachowanie TOP.
- FETCH wymaga wartości całkowitej, więc do wykonania tej konwersji jest używane CAST. W ten sposób 51.0 staje się 51
Aby zwrócić 10 ostatnich rekordów wyniku, istnieją dwie metody. Możemy odwrócić kolejność sortowania, tak jak to zrobiliśmy dla TOP:
SELECT Name, ProductNumber, StandardCostFROM Production.ProductORDER BY StandardCost DESC OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY
Lub możemy zachować kolejność sortowania i obliczyć przesunięcie tak, aby wszystkie wiersze do ostatniego dziesięć jest pomijanych. Omówimy tę technikę bardziej szczegółowo w lekcji SQL dotyczącej OFFSET i FETCH.
Podsumowanie różnic
Oto podsumowanie niektórych głównych różnic między TOP i OFFSET & FETCH.
Ogólnie rzecz biorąc, jeśli celem jest zwrócenie tylko pierwszych kilku wierszy, myślę, że TOP jest prostym rozwiązaniem; jeśli szukasz rozwiązania do stronicowania lub chcesz wybrać wiersze w środku lub na dole wyników zapytania bez wpływu na oryginalną kolejność sortowania, to myślę, że OFFSET & FETCH są najlepsze.