Dlaczego warto korzystać z Select Top 100 Percent?

Rozumiem, że przed SQL Server 2005 można było „oszukać” SQL Server, aby zezwolił na użycie zamówienia w definicji widoku, włączając TOP 100 PERCENTw to klauzulę SELECT . Ale widziałem inny kod, który odziedziczyłem, który używa SELECT TOP 100 PERCENT... w dynamicznych instrukcjach SQL (używanych w ADO w aplikacjach ASP.NET itp.). Czy jest jakiś powód? Nie jest to wynik taki sam, jak nie w tym TOP 100 PERCENT?

Był używany do „ materializacji pośredniej (wyszukiwarka Google) ”

Dobry artykuł: Adam Machanic: Odkrywanie tajemnic pośredniej materializacji

Podniósł nawet MS Connect, aby można było to zrobić w czystszy sposób

Mój pogląd jest „nie z natury zły”, ale nie używaj go, chyba że masz 100% pewności. Problem w tym, że działa tylko wtedy, gdy to robisz i prawdopodobnie nie później (poziom poprawek, schemat, indeks, liczba wierszy itp.) ...

Przykład praktyczny

Może się to nie powieść, ponieważ nie wiesz, w jakiej kolejności rzeczy są oceniane

SELECT foo From MyTable WHERE ISNUMERIC (foo) = 1 AND CAST(foo AS int) > 100

Może się to również nie udać, ponieważ

SELECT foo
FROM
    (SELECT foo From MyTable WHERE ISNUMERIC (foo) = 1) bar
WHERE
    CAST(foo AS int) > 100

Jednak nie miało to miejsca w SQL Server 2000. Wewnętrzne zapytanie jest oceniane i buforowane:

SELECT foo
FROM
    (SELECT TOP 100 PERCENT foo From MyTable WHERE ISNUMERIC (foo) = 1 ORDER BY foo) bar
WHERE
    CAST(foo AS int) > 100

Uwaga, to nadal działa w SQL Server 2005

SELECT TOP 2000000000 ... ORDER BY...

TOP (100) PERCENT jest całkowicie bez znaczenia w ostatnich wersjach SQL Server i (wraz z odpowiednim ORDER BY, w przypadku definicji widoku lub tabeli pochodnej) jest ignorowane przez procesor zapytań.

Masz rację, że kiedyś można było to wykorzystać jako sztuczkę, ale nawet wtedy nie było wiarygodne. Niestety, niektóre narzędzia graficzne Microsoftu zawierają tę bezsensowną klauzulę.

Nie mam pojęcia, dlaczego może się to pojawić w dynamicznym SQL. Masz rację, że nie ma ku temu powodu, a wynik jest taki sam bez tego (i znowu, w przypadku definicji widoku lub tabeli pochodnej, bez klauzul TOP i ORDER BY).

... zezwalaj na użycie ORDER BY w definicji widoku.

To nie jest dobry pomysł. Widok nigdy nie powinien mieć zdefiniowanego ORDER BY.

ORDER BY ma wpływ na wydajność - używając go widok oznacza, że ​​ORDER BY pojawi się w planie wyjaśniania. Jeśli masz zapytanie, w którym widok jest przyłączony do czegokolwiek w bezpośrednim zapytaniu lub odwołuje się do widoku wbudowanego (faktoring CTE / podzapytania) - ORDER BY jest zawsze uruchamiany przed ostatecznym ORDER BY (zakładając, że został zdefiniowany). Nie ma korzyści z porządkowania wierszy, które nie są końcowym zestawem wyników, gdy zapytanie nie używa TOP (lub LIMIT dla MySQL / Postgres).

Rozważać:

CREATE VIEW my_view AS
    SELECT i.item_id,
           i.item_description,
           it.item_type_description
      FROM ITEMS i
      JOIN ITEM_TYPES it ON it.item_type_id = i.item_type_id
  ORDER BY i.item_description

...

  SELECT t.item_id,
         t.item_description,
         t.item_type_description
    FROM my_view t
ORDER BY t.item_type_description

... jest odpowiednikiem użycia:

  SELECT t.item_id,
         t.item_description,
         t.item_type_description
    FROM (SELECT i.item_id,
                 i.item_description,
                 it.item_type_description
            FROM ITEMS i
            JOIN ITEM_TYPES it ON it.item_type_id = i.item_type_id
        ORDER BY i.item_description) t
ORDER BY t.item_type_description

To jest złe, ponieważ:

1. Przykładem jest porządkowanie listy początkowo według opisu pozycji, a następnie porządkowanie listy na podstawie opisu typu pozycji. To zmarnowane zasoby w pierwszym rzędzie - działanie bez zmian nie oznacza, że ​​działa:ORDER BY item_type_description, item_description
2. Ze względu na hermetyzację nie jest oczywiste, według jakiej kolejności jest wyświetlany widok. Nie oznacza to, że należy tworzyć wiele widoków z różnymi porządkami sortowania ...

Jeśli nie ma ORDER BYklauzuli, to TOP 100 PERCENTjest zbędne. (Jak wspomniałeś, to była „sztuczka” z widokami)

[Mam nadzieję, że optymalizator to zoptymalizuje.]

Widziałem inny kod, który odziedziczyłem, który używa SELECT TOP 100 PERCENT

Powód jest prosty: Enterprise Manager starał się być pomocny i formatował Twój kod, aby zawierał to za Ciebie. Nigdy nie było sensu próbować go usuwać, ponieważ tak naprawdę niczego nie boli, a następnym razem, gdy szedłeś, aby go zmienić, EM wstawiał go ponownie.

Chyba bez powodu, ale z obojętnością.

Takie ciągi zapytań są zwykle generowane przez graficzne narzędzie do zapytań. Użytkownik łączy kilka tabel, dodaje filtr, porządek sortowania i testuje wyniki. Ponieważ użytkownik może chcieć zapisać zapytanie jako widok, narzędzie dodaje TOP 100 PROCENTÓW. W tym przypadku jednak użytkownik kopiuje kod SQL do swojego kodu, parametryzuje klauzulę WHERE i ukrywa wszystko w warstwie dostępu do danych. Z głowy, poza zasięgiem wzroku.

Prosimy spróbować poniżej, mam nadzieję, że to zadziała.

      SELECT TOP
              ( SELECT COUNT(foo) 
                  From MyTable 
                 WHERE ISNUMERIC (foo) = 1) * 
                  FROM bar WITH(NOLOCK) 
              ORDER BY foo
                 WHERE CAST(foo AS int) > 100
               )

Błąd mówi wszystko ...

Msg 1033, poziom 15, stan 1, procedura TestView, wiersz 5 Klauzula ORDER BY jest niepoprawna w widokach, funkcjach wbudowanych, tabelach pochodnych, podzapytaniach i typowych wyrażeniach tabel, chyba że określono również TOP, OFFSET lub FOR XML.

Nie używaj TOP 100 PERCENT, używaj TOP n, gdzie N jest liczbą

TOP 100 PERCENT (z powodów, których nie znam) jest ignorowane przez SQL Server VIEW (wersje po 2012), ale myślę, że MS zachowało je ze względów składniowych. TOP n jest lepsze i będzie działać wewnątrz widoku i sortować go tak, jak chcesz, gdy widok jest używany początkowo, ale bądź ostrożny .

Przypuszczam, że możesz użyć zmiennej w wyniku, ale oprócz uzyskania w widoku KOLEJNOŚCI według kawałków, nie zobaczysz korzyści, pośrednio podając „TOP 100 PROCENT”:

declare @t int
set @t=100
select top (@t) percent * from tableOf

Po prostu spróbuj tego, to wyjaśnia to prawie samo. Nie możesz utworzyć widoku z ORDER BY, chyba że ...

CREATE VIEW v_Test
         AS
           SELECT name
             FROM sysobjects
         ORDER BY name
        GO

Msg 1033, poziom 15, stan 1, procedura TestView, wiersz 5 Klauzula ORDER BY jest niepoprawna w widokach, funkcjach wbudowanych, tabelach pochodnych, podzapytaniach i typowych wyrażeniach tabel, chyba że określono również TOP, OFFSET lub FOR XML.