- Problem: kolumna nvarchar sortuje kody znak po znaku, więc 10 stoi przed 2.
- Wzorzec: ORDER BY z TRY_CAST do decimal(38,0) układa kody liczbowo, a nieliczbowe trafiają na koniec.
- DISTINCT: zapytanie z SELECT DISTINCT wymaga tabeli pochodnej, bo wyrażenie z ORDER BY musi być na liście wyników.
- Test: wynik jest taki sam na poziomach zgodności 130 i 160.
Kolumna nvarchar, w której kody są numerami porządkowymi, sortuje się znak po znaku, więc lista wyboru pokazuje kody w kolejności 1, 10, 100, 101, 11, 2. Programista T-SQL, który widzi taką listę, chce kolejności liczbowej bez zmiany typu kolumny. Pokazujemy wzorzec sortowania liczbowego kodów zapisanych jako tekst: ORDER BY z TRY_CAST do decimal(38,0) ustawia kolejność liczbową i odsuwa kody nieliczbowe na koniec. Nazwy tabel i kolumn w kodzie są przykładowe.
Sortowanie liczbowe kodów zapisanych jako tekst wymaga ORDER BY z TRY_CAST do decimal(38,0). Funkcja zwraca NULL zamiast błędu, flaga w pierwszym członie odsuwa takie kody na koniec, a kod jako tekst rozstrzyga remisy. Gdy zapytanie ma SELECT DISTINCT, sortowanie trzeba przenieść poza tabelę pochodną.
Wzorzec wymaga trzech decyzji, które opisują kolejne sekcje.
- Zamiana tekstu na liczbę: TRY_CAST zwraca NULL zamiast błędu.
- Kod, którego nie da się zamienić: flaga w pierwszym członie ORDER BY odsuwa go na koniec.
- Zapytanie z SELECT DISTINCT: wyrażenie z ORDER BY musi być na liście wyników, więc zapytanie trafia do tabeli pochodnej.
Tabela w kodzie nazywa się artykul_przyklad, a jej kolumna kod ma typ nvarchar(50). Kody w przykładzie to liczby, jeden zapis nieliczbowy (A-5) i jeden zapis ze spacjami po obu stronach. Te kody pokazują obie strony wzorca: sortowanie liczb i obsługę wartości, które liczbami nie są.
Tekstowy ORDER BY układa kody znak po znaku
Pierwsze zapytanie sortuje kolumnę kod bez żadnej konwersji. Kolumna jest tekstowa, więc porównanie idzie znak po znaku i kod 10 stoi przed kodem 2, bo cyfra 1 poprzedza cyfrę 2. Kod ze spacjami po obu stronach trafia na początek, bo spacja sortuje się przed cyframi. Kolejność tekstowa zależy od sortowania bazy, a w teście było to Polish_CI_AS.
Blok poniżej tworzy tabelę z ośmioma kodami i zawiera dwa zapytania: tekstowe i liczbowe. Wszystkie osiem wierszy wstawia jedno polecenie INSERT, a kolumna kod ma typ nvarchar(50), więc każdy kod, także A-5, mieści się w tej samej kolumnie. Zapytanie liczbowe opisujemy w następnej sekcji.
CREATE TABLE dbo.artykul_przyklad (kod nvarchar(50) NOT NULL, nazwa nvarchar(100) NOT NULL);
INSERT INTO dbo.artykul_przyklad (kod, nazwa) VALUES
(N'1', N'a'), (N'2', N'b'), (N'10', N'c'), (N'100', N'd'),
(N'101', N'e'), (N'11', N'f'), (N'A-5', N'g'), (N' 3 ', N'h');
SELECT kod FROM dbo.artykul_przyklad ORDER BY kod;
SELECT x.kod
FROM (SELECT DISTINCT kod FROM dbo.artykul_przyklad) AS x
ORDER BY
CASE WHEN TRY_CAST(NULLIF(LTRIM(RTRIM(x.kod)), N'') AS decimal(38,0)) IS NULL THEN 1 ELSE 0 END,
TRY_CAST(NULLIF(LTRIM(RTRIM(x.kod)), N'') AS decimal(38,0)),
x.kod;Przykład ilustracyjny, nazwy wymyślone. Tabela z ośmioma kodami i dwa zapytania: tekstowe, potem liczbowe.
- Kody liczbowe: sześć kodów: 1, 2, 10, 11, 100, 101.
- Kody specjalne: dwa kody: A-5 jako zapis nieliczbowy i „ 3 ” ze spacjami po obu stronach.
ORDER BY z TRY_CAST układa kody liczbowo i odsuwa nieliczbowe na koniec
Drugie zapytanie ma w ORDER BY trzy człony, które działają po kolei. Pierwszy rozdziela liczby i kody nieliczbowe, drugi porządkuje liczby, a trzeci rozstrzyga kody o tej samej wartości.
- Flaga nieliczbowych: wyrażenie CASE zwraca 1 dla kodu nieliczbowego i 0 dla liczby, więc kody nieliczbowe lądują na końcu.
- Wartość liczbowa: TRY_CAST do decimal(38,0) porządkuje liczby według wartości.
- Kod jako tekst: sam kod rozstrzyga remisy.
Funkcje LTRIM i RTRIM usuwają spacje z obu stron, a NULLIF zamienia pusty tekst na NULL. Kod ze spacjami po obu stronach jest więc traktowany jak liczba 3, a pusty tekst nie powoduje błędu konwersji. Wynik w teście to kolejność 1, 2, 3 (ze spacjami), 10, 11, 100, 101, A-5. Tabela zestawia obie kolejności na tych samych ośmiu kodach.
| Pozycja | Tekstowy ORDER BY | Wzorzec z TRY_CAST |
|---|---|---|
| 1 | „ 3 ” | 1 |
| 2 | 1 | 2 |
| 3 | 10 | „ 3 ” |
| 4 | 100 | 10 |
| 5 | 101 | 11 |
| 6 | 11 | 100 |
| 7 | 2 | 101 |
| 8 | A-5 | A-5 |
Kolejność ośmiu kodów w obu zapytaniach.
Schemat pokazuje tę samą zależność w układzie dwóch kolumn. Po lewej stoi wynik tekstowy, po prawej liczbowy, a strzałka łączy te same kody. Kod A-5 stoi w obu kolejnościach na końcu, ale z innego powodu: w kolejności tekstowej wypada ostatni, a we wzorcu odsuwa go flaga.
SELECT DISTINCT wymaga tabeli pochodnej, gdy sortuje po wyrażeniu
Pierwsza wersja zapytania liczbowego, która łączy SELECT DISTINCT z ORDER BY po wyrażeniu, kończy się błędem 145. Silnik wymaga, żeby każde wyrażenie z ORDER BY występowało na liście wyników, gdy zapytanie ma DISTINCT. Wyrażenie z TRY_CAST nie jest kolumną z listy wyników, więc zapytanie nie przechodzi. Na obu poziomach zgodności komunikat był taki sam.
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
Rozwiązaniem jest tabela pochodna: zapytanie z DISTINCT stoi w nawiasie w klauzuli FROM, a ORDER BY stoi na zewnątrz i sortuje już kolumnę kod z listy wyników tabeli pochodnej. Tak zbudowane jest zapytanie 2 z pierwszego bloku. Drugi blok pokazuje wersję, która kończy się błędem.
SELECT DISTINCT kod
FROM dbo.artykul_przyklad
ORDER BY TRY_CAST(NULLIF(LTRIM(RTRIM(kod)), N'') AS decimal(38,0));Przykład ilustracyjny. To zapytanie kończy się błędem 145.
- Wersja z błędem: SELECT DISTINCT i ORDER BY po wyrażeniu w jednym zapytaniu.
- Wersja poprawna: SELECT DISTINCT w tabeli pochodnej, a ORDER BY na zewnątrz.
TRY_CAST zamienia część zapisów na NULL, a inne zaokrągla
TRY_CAST do decimal(38,0) nie zgłasza błędu dla zapisów, których nie rozumie. Zwraca NULL, więc takie kody trafiają na koniec listy. Liczby z ułamkiem zaokrągla do całości, a nie odrzuca. Zapis 1.5 daje 2, a trzy rodzaje zapisów dają NULL: z przecinkiem, z notacją wykładniczą i ze spacją w środku.
Trzeci blok sprawdza osiem zapisów jednym zapytaniem. Tabela po bloku zawiera wynik. Zapisy 007 i 7 dają tę samą wartość 7, a kolejność między nimi rozstrzyga trzeci człon ORDER BY, czyli kod jako tekst. Liczba z 39 cyfr przekracza zakres decimal(38,0) i także daje NULL.
SELECT v.kod,
TRY_CAST(NULLIF(LTRIM(RTRIM(v.kod)), N'') AS decimal(38,0)) AS liczba
FROM (VALUES (N'007'), (N'7'), (N'1.5'), (N'2,5'), (N'1e3'), (N'1 000'), (N''),
(REPLICATE(N'9', 39))) AS v(kod);Przykład ilustracyjny. Osiem zapisów w jednym zapytaniu.
| Zapis | Wynik TRY_CAST |
|---|---|
| 007 | 7 |
| 7 | 7 |
| 1.5 | 2 |
| 2,5 | NULL |
| 1e3 | NULL |
| 1 000 | NULL |
| tekst pusty | NULL |
| 39 cyfr | NULL |
Wynik konwersji dla ośmiu zapisów.
Kody o tej samej wartości rozstrzyga człon tekstowy
Zapisy 007 i 7 mają tę samą wartość liczbową, więc dwa pierwsze człony ORDER BY nie odróżniają ich od siebie. Rozstrzyga trzeci człon, czyli kod jako tekst. Bez niego kolejność dwóch takich kodów nie byłaby określona i mogłaby się zmieniać między wykonaniami zapytania.
- Remis wartości: zapisy 007 i 7 mają wartość 7.
- Rozstrzygnięcie: trzeci człon ORDER BY, czyli kod jako tekst.
Człon tekstowy jest więc tanim zabezpieczeniem przed niestabilną kolejnością. Gdy kody o tej samej wartości liczbowej są wykluczone, człon można pominąć. Gdy są możliwe, zostaje potrzebny. Pułapką jest też założenie, że decimal(38,0) obsługuje ułamki: konwersja zaokrągla je do całości, więc dwa różne zapisy mogą trafić do tej samej wartości.
Wynik jest identyczny na poziomach zgodności 130 i 160
Test wykonano w jednorazowej bazie na dwóch poziomach zgodności. Kolejność tekstowa i kolejność liczbowa wyszły identyczne, podobnie jak granice TRY_CAST, a błąd 145 pojawił się na obu poziomach z tym samym komunikatem. Różnić może się tylko kolejność tekstowa, bo zależy od sortowania bazy, a nie od poziomu zgodności.
| Sprawdzenie | Poziom 130 | Poziom 160 |
|---|---|---|
| Kolejność tekstowa | „ 3 ”, 1, 10, 100, 101, 11, 2, A-5 | „ 3 ”, 1, 10, 100, 101, 11, 2, A-5 |
| Kolejność liczbowa | 1, 2, „ 3 ”, 10, 11, 100, 101, A-5 | 1, 2, „ 3 ”, 10, 11, 100, 101, A-5 |
| SELECT DISTINCT z ORDER BY po wyrażeniu | błąd 145 | błąd 145 |
| Zapis 1.5 w TRY_CAST | 2 | 2 |
Wyniki są takie same na obu poziomach.
Sposób sprawdzenia jest prosty. Trzy bloki uruchamia się w tej kolejności w jednorazowej bazie, porównuje wyniki z tabelami i usuwa bazę po teście. Zapytanie z błędem 145 kończy się komunikatem, a pozostałe bloki dają tabele z wynikami. Pokrewne wzorce, które także dotyczą ustawień i zapisu w T-SQL, opisujemy w artykułach o QUOTED_IDENTIFIER w procedurach i o aktualizacji nagłówka bez zerowania pozycji.
Przykład (dane wymyślone): lista wyboru artykułów pokazuje kody w kolejności 1, 10, 100, 101, 11, 2, więc osoba szukająca kodu 2 przewija listę do końca. Programista zmienia ORDER BY na wzorzec z TRY_CAST, tabelę z DISTINCT zamyka w tabeli pochodnej i dostaje kolejność 1, 2, 10, 11, 100, 101. Kod A-5 trafia na koniec, bo nie jest liczbą. Kod ze spacjami po obu stronach sortuje się jak liczba, bo funkcje LTRIM i RTRIM go oczyszczają.
Pytania do analizy
Przed wdrożeniem takiego sortowania odpowiedz na cztery pytania.
- Kiedy wartość 1.5 zapisana w kolumnie kodu zmieni miejsce na liście przez zaokrąglenie do decimal(38,0)?
- Dlaczego SELECT DISTINCT zgłasza błąd 145 i czy tabela pochodna jest jedynym wyjściem?
- Jakie kody trafią na koniec listy i czy to jest zachowanie, którego oczekuje użytkownik?
- Czy człon tekstowy na końcu ORDER BY jest potrzebny, gdy kody o tej samej wartości liczbowej są niemożliwe?