SQL Query Optimization. Why is it so hard to get right?

Zmienne tabelaryczne i funkcje niedeterministyczne.

Wykorzystanie w zapytaniu zmiennych tabelarycznych oraz funkcji niedeterministycznych może dawać ciekawe, niespodziewane wyniki.

Poniższe zapytania są właściwie identyczne. Pierwsze bazuje na zwykłej tabeli, drugie na tabeli tymczasowej, a trzecie na zmiennej tabelarycznej. Idea jest jednak taka sama w każdym z 3 przypadków. Kluczowe jest tu podzapytanie, które ma zwrócić dowolną wartość (TOP 1) z tabeli. Wartość ta będzie za każdym razem inna ze względu na użycie w ORDER BY funkcji NEWID().

W przypadku zwykłej tabeli oraz tabeli tymczasowej podzapytanie zwraca za każdym razem inną wartość (przy ponownym wywołani zapytania), ale wartość jest taka sama dla każdego z rekordów, które zwraca zapytanie zewnętrzne. Tego bym się spodziewał.
W przypadku zmiennej tabelaryczne sytuacja się zmienia. Wartość podzapytania jest różna dla każdego rekordu zapytania zewnętrznego. Tego właśnie się nie spodziewałem i zrobiłem duże oczy.

Przykład nr 1 (tabela):

USE TEMPDB;
GO

IF OBJECT_ID('T') IS NOT NULL
DROP TABLE T

CREATE TABLE T (NUMBER INT);

WITH NUMBERS
AS
(
SELECT 1 AS NUMBER
UNION ALL
SELECT NUMBER + 1 FROM NUMBERS WHERE NUMBER < 10
)
INSERT INTO T
SELECT NUMBER
FROM NUMBERS;

SELECT NUMBER
, (SELECT TOP 1 NUMBER FROM T ORDER BY NEWID()) AS J
FROM T;
GO

Przykładowe wyniki:
NUMBER J
1 2
2 2
3 2
4 2
5 2
6 2
7 2
8 2
9 2
10 2

Przykład nr 2 (tabela tymczasowa):
USE TEMPDB;
GO

IF OBJECT_ID('#T') IS NOT NULL
DROP TABLE #T

CREATE TABLE #T (NUMBER INT);

WITH NUMBERS
AS
(
SELECT 1 AS NUMBER
UNION ALL
SELECT NUMBER + 1 FROM NUMBERS WHERE NUMBER < 10
)
INSERT INTO #T
SELECT NUMBER
FROM NUMBERS;

SELECT NUMBER
, (SELECT TOP 1 NUMBER FROM #T ORDER BY NEWID()) AS J
FROM #T;
GO

Przykładowe wyniki:
NUMBER J
1 6
2 6
3 6
4 6
5 6
6 6
7 6
8 6
9 6
10 6

Przykład nr 3 (zmienna tabelaryczna):

USE TEMPDB;

GO

DECLARE @T TABLE(NUMBER INT);

WITH NUMBERS

AS

(

SELECT 1 AS NUMBER

UNION ALL

SELECT NUMBER + 1 FROM NUMBERS WHERE NUMBER < 10

)

INSERT INTO @T

SELECT NUMBER

FROM NUMBERS;

SELECT NUMBER

, (SELECT TOP 1 NUMBER FROM @T ORDER BY NEWID()) AS J

FROM @T;

GO

Przykładowe wyniki:

NUMBER J

1 4

2 8

3 2

4 8

5 5

6 5

7 7

8 3

9 4

10 8

Kluczowe w przypadku zmiennej tabelarycznej jest brak operatora Table Spool.

Generowanie skryptów w SSMS.

W opcjach SQL Management Studio mamy możliwość ustawień skryptowania. Mamy np. możliwość ustawić 'Script for Server Version'. Nie wiem czemu, ale wydawało mi się, że jest to ustawienie globalne dla SSMS i że niezależnie gdzie będę wygenerowany skrypt będzie on zgodny z wersją jaką ustawiłem w opcjach. Okazało się, że się myliłem.

Gdy generujemy skrypt dla jakiegoś obiektu klikając na niego prawym klawiszem myszy i wybierając np. CREATE TO to wygenerowany skrypt będzie zgodny z naszymi ustawieniami. Jeżeli jednak będziemy chcieli wygenerować tzw. 'Change Script' z okna, w którym pracujemy na diagramie to wówczas nasze ustawienia odnośnie zgodności wersji skryptu do wersji SQL ma się nijak. Co ciekawe, w moim przypadku, nawet strona kodowa jest inna. Normalnie mam ANSII, a skrypt generowany z okna pracy na diagramach jest w UCS-2.

Hmmm... Ktoś może wie dlaczego tak?

FROM VALUES

Jakoś ostatnio nie było czasu ani pomysłu na wpis blogaskowy. Pomysłów dalej brak :), ale dziś natrafiłem na artykuł, który warto przeczytać. Moim zdaniem pokazuje piękno pisania wydajnego kodu SQL. Rozwiązań jest dużo, ale znalezienie tego właściwego ...

Zapraszam do lektury : klik

Automatyczny szablon skryptu SQL.

Miałem o tym napisać, ale skoro właśnie o tym czytam na jednym z moich ulubionych blogasków to daję tylko linka.

Nieudokumentowane procedury.

Jakiś czas temu natknąłem się na dwie fajne procedurki :

    SP_MSFOREACHDB

    SP_MSFOREACHTABLE

Są to dwie nieudokumentowane przez MS procedury SQL.

Za pomocą pierwszej z nich można wykonać kod SQL na wszystkich bazach danej instancji. Za pomocą drugiej na wszystkich tabelach danej bazy.

Postanowiłem zwrócić uwagę na te dwie procedurki, bo ułatwiają pracę. Zwłaszcza moją :).

Ostatnio miałem przypadek, że musiałem porównać wartości w jednej z tabelek we wszystkich bazach na danej instacji. Baz było dokładnie 358.

Prosty skrypcik i wynik był gotowy :

EXECUTE SP_MSFOREACHDB 'SELECT ''?'', * FROM ?.DBO.TABELA'

? – Zastępuje w tym przypadku nazwę bazy. W procedurze SP_MSFOREACHTABLE byłaby to nazwa tabeli.

Taki prosty kod, który zwraca nam już potrzebne informacje ze wszystkich baz, można rozbudować według własnych potrzeb. Można wyniki wrzucić do tabeli tymczasowej i już dalej na niej operować. Co istotne, błędy nie są istotne w tej procedurze ;). Tzn. jeżeli danej tabeli nie na bazie to zgłoszony zostanie błąd wykonania zapytania, ale cała procedura pojedzie dalej i zwróci nam wyniki dla baz, dla których tabela istnieje.

Szkoda tylko, że procedury nie są udokumentowane i w sumie nie wiadomo czy będą rozwijane. Może się okazać, że znikną w kolejnych wersjach MS SQL. Oby nie.

Jeden ze znajomych powiedział mi, że ważne jest na koniec posta zadać czytelnikom pytanie :). Pytam więc, czy też czujecie nieocenioną radość przy korzystaniu z  powyższych procedur?

Przeniesienie bazy z SQL 2008 na SQL 2005 (downgrade).

Ostatnio uczestniczyłem w przenoszeniu bazy z SQL 2008 na SQL 2005. Proste backup i restore w tym przypadku nie zadziała. Do przeniesienia struktury wykorzystałem narzędzie GENERATE SCRIPTS, a do transferu danych EXPORT DATA.

Cały proces przeszedł bez problemu... (tak mi się tylko wydawało).

Napotkałem w sumie na dwa problemy. Pierwszy polegał na tym, że niektóre obiekty, głównie klucze i defaulty. W tych miejscach gdzie te obiekty nie miały nadanej konkretnej nazwy, a została ona wygenerowana przez SQL serwer (dostaje ona wtedy dziwny numerek na końcu nazwy), na nowym serwerze nazwa ta też została wygenerowana. Czyli na kolumnie była zdefiniowana wartość domyślna ale constraint miał inny numerek w nazwie.

Drugi problem polegał na przeniesieniu danych. Okazało się, że nie przeniosły się one jeden w jeden. W moim przypadki problem był w kolumnach, które miały ustawioną wartość domyślną, ale dopuszczały NULL. Jeżeli na bazie źródłowej w kolumnie takiej był NULL to w bazie docelowej w tym samym polu pojawiła się wartość domyślna.

Napisałem skrypciorka, który pokazał mi w jakich tabelach zmieniły się dane.

USE ADVENTUREWORKSLT2008;

-- DEKLARACJA ZMIENNYCH
DECLARE @TABLE_ID INT -- ID TABELI
    , @TABLE_NAME VARCHAR(50) -- NAZWA TABELI
    , @SCHEMA_NAME VARCHAR(50) -- NAZWA SCHEMATU
    , @COLUMN_NAME VARCHAR(MAX) -- NAZWY KOLUMN
    , @S_SERVER_NAME VARCHAR(100) -- SERWER ŹRÓDŁOWY
    , @D_SERVER_NAME VARCHAR(100) -- SERWER DOCELOWY
    , @S_DB_NAME VARCHAR(100) -- BAZA ŹRÓDŁOWA
    , @D_DB_NAME VARCHAR(100) -- BAZA DOCELOWA
    , @MYSQL VARCHAR(MAX)
    , @MYSQL_EXT VARCHAR(MAX); 

-- PRZYPISANIE WARTOŚCI ZMIENNYM
SET @S_SERVER_NAME = '[SQL2008]';
SET @D_SERVER_NAME = '[SQL2005]';
SET @S_DB_NAME = 'ADVENTUREWORKSLT2008';
SET @D_DB_NAME = 'ADVENTUREWORKSLT2005';
    
DECLARE TABLE_CURSOR CURSOR FOR -- DEKLARACJA KURSORA DO POBIERANIA INFORMACJI O TABELACH
SELECT [OBJECT_ID]
    , SCHEMA_NAME(SCHEMA_ID) AS SCH_NAME
    , NAME
FROM SYS.TABLES
ORDER BY NAME ASC;

OPEN TABLE_CURSOR;

FETCH NEXT FROM TABLE_CURSOR 
INTO @TABLE_ID
    , @SCHEMA_NAME
    , @TABLE_NAME;

WHILE @@FETCH_STATUS = 0
BEGIN
    
    SET @COLUMN_NAME = '';
    SET @MYSQL = '';
    SET @MYSQL_EXT = '';
    
    -- ZŁOŻENIE KOLUMN TABELI
    SELECT @COLUMN_NAME = @COLUMN_NAME + ',[' + C.NAME + ']'
    FROM SYS.COLUMNS AS C
        INNER JOIN SYS.TYPES AS T ON C.SYSTEM_TYPE_ID = T.SYSTEM_TYPE_ID
    WHERE C.[OBJECT_ID] = @TABLE_ID
        AND T.NAME NOT IN ('IMAGE', 'TEXT', 'NTEXT', 'XML') -- NIE WSZYSTKIE DANE ZA POMOCĄ EXCEPT DA SIĘ PORÓWNAĆ
    ORDER BY C.COLUMN_ID ASC;

    SET @COLUMN_NAME = STUFF(@COLUMN_NAME, 1, 1, '') -- USUNIĘCIE PRZECINKA Z POCZĄTKU STRINGA

    -- ZŁOŻENIE ZAPYTANIE
    SET @MYSQL_EXT = @SCHEMA_NAME + '.' + @TABLE_NAME;
    
    SET @MYSQL =    'SELECT ' + @COLUMN_NAME 
                    + ' FROM ' + @S_SERVER_NAME + '.' + @S_DB_NAME + '.' + @SCHEMA_NAME + '.' + @TABLE_NAME
                    + ' EXCEPT '
                    + 'SELECT ' + @COLUMN_NAME 
                    + ' FROM ' + @D_SERVER_NAME + '.' + @D_DB_NAME + '.' + @SCHEMA_NAME + '.' + @TABLE_NAME;
    
    EXEC ('SELECT ''' + @MYSQL_EXT + '''') -- TABELA ANALIZOWANA
    EXEC (@MYSQL); -- ANALIZA DANYCH
        
    FETCH NEXT FROM TABLE_CURSOR 
    INTO @TABLE_ID
        , @SCHEMA_NAME
        , @TABLE_NAME;
    
END

CLOSE TABLE_CURSOR;
DEALLOCATE TABLE_CURSOR;

Aby nie mieć problemów z wartościami NULL w kolumnach z wartością domyślną nie należy generować ich za pomocą GENERATE SCRIPTS przed wstawieniem danych.

Co do pierwszego problemu to obejścia dalej szukam. ;))

Jak narysować obraz za pomocą T-SQL.

Dziś zobaczyłem coś co mnie powaliło na kolana. Musicie sami to zobaczyć.

http://michaeljswart.com/2010/02/more-images-from-the-spatial-results-tab/

Ciężko to w ogóle nazwać.

Identifiers

Do tematu pisania poprawnego kodu SQL podchodziłem już kilkukrotnie. Pisząc ‘poprawnego’ nie mam na myśli tego  czy sam kod działa i czy zwraca poprawne wyniki, ale to czy jest napisany zgodnie ze sztuką. Czy przed nazwami tabel powinny być nazwy schematów, czy przed nimi znajduje się nazwa bazy danych, czy polecenie SQL kończy się średnikiem (;)…? Do tego dochodzi kwestia formatowania kodu, odpowiednich wcięć, itp.

Piszę o tym, bo miałem ostatnio ciekawy przypadek ze zwykłym poleceniem USE.

Z reguły piszą USE, a nazwę bazy przeciągam z okna ‘Object Explorer’ do okna ‘Query’, lub, jeżeli nazwa jest krótka, po prostu ją piszę. Efekt jest ten sam:

USE MOJA_BAZA;

Taki fragment kodu działa bez zarzutu. Problem pojawi się gdy nieodpowiednio nazwiemy bazę. Nieodpowiednio to znaczy jak? A choćby tak : MOJA-BAZA.

Jeżeli będziemy to robić z kodu poleceniem CREATE DATABASE MOJA-BAZA to otrzymamy błąd

Msg 170, Level 15, State 1, Line 1

Line 1: Incorrect syntax near '-'.

Jednakże robiąc to z MANAGEMENT STUDIO nie dostaniemy żadnych błędów. Wszystko przebiegnie bez problemu. Teraz stosując metodę DRAG&DROP przeciągam sobie nazwę bazy danych do okna QUERY dodając ją do słówka USE, odpalam i dostaję :

Msg 911, Level 16, State 1, Line 1

Database 'moja' does not exist. Make sure that the name is entered correctly.

Ale o co chodzi? Przecież ja wpisałem USE MOJA-BAZA. Pewnie, że nie ma bazy MOJA. Wszystko się zgadza, bo przecież jest baza MOJA-BAZA.

Najprostszym rozwiązaniem tego problemu są nawiasy kwadratowe.

USE [MOJA-BAZA];

I wszystko działa bez problemu.

Myślnik (-) nie jest może dobrym rozwiązaniem jako element składowy w nazwie bazy, czy w nazwie każdego innego obiektu, ale nie jest też zabroniony.

Wracając więc do początku mojego wpisu. Jak powinno wyglądać zapytanie, żeby uniknąć takich niespodzianek?

Czy tak :

SELECT KOL1
    , KOL2
    , KOL3
FROM MOJA_TABELA

A może tak :

SELECT [KOL1]
    , [KOL2]
    , [KOL3]
FROM [MOJA_BAZA].[DBO].[MOJA_TABELA];

Muszę chyba jeszcze raz zmierzyć się z tematem :)

Profiler i Performance Monitor

Dziś przeczytałem świetny artykuł na temat połączenia wyników profilera z wynikami monitora wydajności w celu znalezienia "wąskich gardeł" systemu. Polecam wszystkim, którzy zajmują się badaniem wydajności SQL serwera.

Correlating SQL Server Profiler with Performance Monitor by Brad McGehee

DISABLE TRIGGERS

Nie lubię Triggerów...

Poniżej prosty skrypt do wyłączania wszystkich Triggerów DML na tabelach użytkownika.

DECLARE @NAME VARCHAR(8000)

DECLARE TR_CURSOR CURSOR STATIC FOR
    SELECT DISTINCT SCHEMA_NAME(T.[SCHEMA_ID]) + '.' + T.NAME 
    FROM SYS.TRIGGERS AS TR
        INNER JOIN SYS.TABLES AS T ON TR.PARENT_ID = T.[OBJECT_ID]
    WHERE TR.PARENT_CLASS = 1

OPEN TR_CURSOR

FETCH NEXT FROM TR_CURSOR 
INTO @NAME

WHILE @@FETCH_STATUS = 0
    BEGIN

        EXECUTE('DISABLE TRIGGER ALL ON ' + @NAME)

        FETCH NEXT FROM TR_CURSOR 
        INTO @NAME
    END 

CLOSE TR_CURSOR
DEALLOCATE TR_CURSOR

CHECKSUM - ciekawostka

Ciekawe zachowanie się funkcji CHECKSUM pokazał mi wczoraj jeden ze znajomych. Otóż CHECKSUM(10.00) = CHECKSUM(100.00). Zaobserwował On taki zachowanie gdy sprawdzał jakie rekordy zmieniły się w tabelce, a zmiana polegała na zwiększeniu ceny 10-krotnie. Korzystając z funkcji CHECKSUM okazało się, że nic się nie zmieniło. Przykład poniżej :

DECLARE @T1 TABLE
(
    ID INT,
    NAZWA VARCHAR(50),
    CENA DECIMAL(10,2)
);

DECLARE @T2 TABLE
(
    ID INT,
    NAZWA VARCHAR(50),
    CENA DECIMAL(10,2)
);

INSERT INTO @T1 (ID, NAZWA, CENA)
VALUES (1, 'TEST1', 125.23)
    , (2, 'TEST2', 10.00)
    , (3, 'TEST3', 23.23);

INSERT INTO @T2 (ID, NAZWA, CENA)
VALUES (1, 'TEST1', 125.23)
    , (2, 'TEST2', 10.00)
    , (3, 'TEST3', 23.23);

-- DANE W OBU TABELKACH SĄ IDENTYCZNE
SELECT * FROM @T1
SELECT * FROM @T2

-- SPRAWDZENIE CZY SĄ JAKIEŚ REKORDY, KTÓRE SIĘ RÓŻNIĄ
SELECT *
FROM @T1 AS T1
    INNER JOIN @T2 AS T2 ON T1.ID = T2.ID
WHERE CHECKSUM(T1.NAZWA, T1.CENA) <> CHECKSUM(T2.NAZWA, T2.CENA)

-- ZWIĘKSZENIE CENY W TABELI @T1
UPDATE @T1
SET CENA = CENA * 10

-- DANE W TABELACH RÓŻNIĄ SIĘ
SELECT * FROM @T1
SELECT * FROM @T2

-- SPRAWDZENIE CZY REKORDY RÓŻNIĄ SIĘ ZA POMOCĄ CHECKSUM DAJE WYNIK NEGATYWNY
-- WEDŁUG CHECKSUM NIC SIĘ NIE ZMIENIŁO
SELECT *
FROM @T1 AS T1
    INNER JOIN @T2 AS T2 ON T1.ID = T2.ID
WHERE CHECKSUM(T1.NAZWA, T1.CENA) <> CHECKSUM(T2.NAZWA, T2.CENA)

-- OBIE SUMY KONTROLNE SĄ TAKIE SAME
SELECT CHECKSUM(T1.NAZWA, T1.CENA) AS CHECKSUM_T1
    , CHECKSUM(T2.NAZWA, T2.CENA) AS CHECKSUM_T2
FROM @T1 AS T1
    INNER JOIN @T2 AS T2 ON T1.ID = T2.ID

W helpie do funkcji CHECKSUM jest fajne stwierdzenie, które może tłumaczyć takie zachowanie :

However, there is a small chance that the checksum will not change. For this reason, we do not recommend using CHECKSUM to detect whether values have changed, unless your application can tolerate occasionally missing a change.

Jak poczytałem na necie to problem jest znany. Jedni piszą swoje funkcje sprawdzające sumę kontrolną np. w C# i podłączają je jako CLR. Inni korzystają z funkcji  HashBytes, ale tu elementem wejściowym musi być tekst.
Do powyższej sytuacji rozwiązaniem wydaje się "skastowanie" wartości numerycznej na VARCHARa. Wówczas osiągamy to o co nam chodziło.

A może jest inny sposób ...?

Azure UNICODE Friendly

Jeszcze jedna ciekawostka ze świata Azure jest taka, że jest on bardzo UNICODE. Jeżeli w tabelce mamy pole typu VARCHAR, a w nim np. słówko 'PAWEŁ' to szukając prostym zapytaniem :

SELECT *
FROM TABELKA
WHERE NAZWA = 'PAWEŁ';

... otrzymamy zero wyników. Azure sprytnie zamienia sobie "Ł" na "L" i szuka 'PAWEL' i oczywiście nie znajduje. Rozwiązania na znalezienie interesujących nas rekordów są dwa : zmiana pola z VARCHAR na NVARCHAR lub napisanie zapytania w taki sposób :

SELECT *
FROM TABELKA
WHERE NAZWA = N'PAWEŁ';

Azure

Ostatnio miałem okazję przyglądać się pracom przenoszenia aplikacji do chmury. Ciekawe był sposób sprawdzania poprawności przenoszonego kodu SQL. Jednej z procedur serwerowych nie można było przenieść do bazy w chmurze. Pojawiał się błąd (dokładnie nie pamiętam angielskiej wersji), że tworzenie globalnej tabeli tymczasowej nie jest dozwolone. Zajrzeliśmy do wnętrza procedury, ale nie znaleźliśmy kodu odpowiedzialnego za tworzenie globalnej tabeli tymczasowej. Była tam deklaracja lokalnej tabeli. Myśleliśmy, że to w tym problem. Zmieniliśmy kod tworzenia tabeli tymczasowej na tworzenie zmiennej tabelarycznej. Niestety nie pomogło.

Po kolejnym okrążeniu kodu procedury okazało się, że w jednym z zapytań (zwykłe polecenie SELECT) w klauzuli WHERE było coś takiego :

WHERE NAZWA LIKE 'XYZ ##%##%'

Zakomentowaliśmy tą linijkę kodu i proces zakładania procedury przeszedł bez błędów.

Z tego wynika, że tak naprawdę nie było szukane tworzenie globalnej tabeli tymczasowej, a dwóch #. Okazuje się, że dwa # są zawsze oznaczenie globalnej tabeli tymczasowej ;).

Co Wy na to, bo dla mnie taka walidacja kodu to trochę nie fajnie?

UPDATE CTE

Ostatnio, jeden z moich znajomych miał do napisania skrypt, który do istniejącej tabeli doda kolumnę ID i uzupełni ją danymi (ponumeruje wiersze). Pierwsza myśl jaka mi przyszła do głowy to wykorzystanie funkcji ROW_NUMBER() do ponumerowania wierszy. Przykładowa tabelka, już z dodaną kolumną ID poniżej.

USE TEMPDB;
GO

CREATE TABLE T
(
      ID INT NULL,
      VAL VARCHAR(10)
);

INSERT INTO T (ID, VAL)
VALUES (NULL, 'A')
      , (NULL, 'B')
      , (NULL, 'C');

SELECT *
FROM T; 

Mamy więc już tabelkę z kolumną ID, ale jeszcze wartość w kolumnie to NULL. Jak ponumerować wiersze? O tym poniżej.

W pierwszej kolejności przygotowujemy sobie CTE, w którym dodajemy do tabelki nową kolumnę (u mnie to jest TEMPID), która powstaje z wykorzystania funkcji ROW_NUMBER(). Mając już tak przygotowane CTE robimy prosty UPDATE, który przepisze nam wartość z TEMPID do naszej kolumny ID do tabelki T. Dziwne jest to, że UPDATE robimy na CTE, a nie bezpośrednio na tabeli. Mimo to w magiczny sposób kolumna ID w naszej tabli zostaje zmieniona.

WITH TCTE
AS
(
      SELECT ROW_NUMBER() OVER (ORDER BY VAL ASC) AS TEMPID
            , ID
            , VAL
      FROM T
)
UPDATE TCTE
SET ID = TEMPID;

SELECT *
FROM T; 

Poniżej jeszcze skrypt w całości.

USE TEMPDB;
GO

CREATE TABLE T
(
      ID INT NULL,
      VAL VARCHAR(10)
);

INSERT INTO T (ID, VAL)
VALUES (NULL, 'A')
      , (NULL, 'B')
      , (NULL, 'C');

SELECT *
FROM T;

WITH TCTE
AS
(
      SELECT ROW_NUMBER() OVER (ORDER BY VAL ASC) AS TEMPID
            , ID
            , VAL
      FROM T
)
UPDATE TCTE
SET ID = TEMPID;

SELECT *
FROM T;

DROP TABLE T; 

Błąd w pytaniach do egzaminu 70-433

Przygotowując się do egzaminu 70-433 przerabiałem pytania dołączone do książki. W jednym z nich znalazłem taki o to błąd :)

Zadanie (pytanie) polegało na ułożeniu zapytania z dostępnego do wyboru kodu. Zapytanie miało zwracać zwierzątka właścicieli, którzy mieszkają w Seatlle i mają powyżej 50-tki (dokładny opis na obrazku poniżej).

Niestety z dostępnego kodu nie dało się napisać zapytania. Zaciekawiło mnie więc jaka jest odpowiedź. Napisałem więc błędny kod i poprosiłem o sprawdzenie i wyświetlenie odpowiedzi. Oto co otrzymałem :

Jak można zauważyć poprawna odpowiedź jest błędna. W podzapytaniu nie ma kolumny OWNERNAME, po której następuje JOIN.
Poniżej dowód :

USE TEMPDB;

DECLARE @PETS AS TABLE
(
    PETNAME VARCHAR(20),
    OWNERNAME VARCHAR(20)
);

DECLARE @OWNERS AS TABLE
(
    OWNERNAME VARCHAR(20),
    AGE TINYINT,
    LOCATION VARCHAR(20)
);

INSERT INTO @PETS (PETNAME, OWNERNAME)
VALUES ('REX', 'BOB')
    , ('PIMPEK', 'ALA')
    , ('ŚLINIAK', 'OLA');

INSERT INTO @OWNERS (OWNERNAME, AGE, LOCATION)
VALUES ('BOB', 55, 'SEATTLE')
    , ('ALA', 10, 'WARSZAWA')
    , ('OLA', 12, 'SZCZECIN');


SELECT PET.PETNAME
FROM @PETS AS PET
JOIN
(SELECT AGE FROM @OWNERS WHERE LOCATION = 'SEATTLE') AS OWN
ON PET.OWNERNAME = OWN.OWNERNAME
WHERE OWN.AGE > 50

Jak można się domyślić zapytanie zwraca błąd :

 

Prawidłowe zapytanie powinno wyglądać tak :

SELECT PET.PETNAME
FROM @PETS AS PET
JOIN
(SELECT OWNERNAME, AGE FROM @OWNERS WHERE LOCATION = 'SEATTLE') AS OWN
ON PET.OWNERNAME = OWN.OWNERNAME
WHERE OWN.AGE > 50

Ciekawy jestem czy Wy też trafiliście na przypadki błędnych odpowiedzi do testów dołączonych to książek przygotowujących do egzaminu.

Przykład użycia ROW_NUMBER()

Jakiś czas temu jeden z moich kolegów poprosił mnie o pomoc w napisaniu zapytania gdyż sam przechodził tzw. "niemoc twórczą". A chodziło o to, że ...

Mamy tabelkę, w której jest id, nazwa, wartość. Nazwa i wartość mogą się powtarzać, id jest unikalne. Chodzi jednak o to, żeby z z tabelki pobrać tylko te wiersze (id, nazwa, wartość), które dla danej nazwy mają największą wartość. Jeżeli dla danej nazwy mamy kilka wpisów z tą samą wartością to pokazać obojętnie który.
Po chwili namysłu naskrobałem coś takiego :

USE TEMPDB;

DECLARE @T AS TABLE
(
    ID INT,
    NAZWA VARCHAR(10),
    WARTOSC INT
);

INSERT INTO @T (ID, NAZWA, WARTOSC)
VALUES (1, 'A', 10)
    , (2, 'B', 17)
    , (3, 'C', 23)
    , (4, 'A', 15)
    , (5, 'D', 33)
    , (6, 'B', 22)
    , (7, 'B', 22);

WITH CTE
AS
(
    SELECT ROW_NUMBER() OVER (PARTITION BY NAZWA ORDER BY WARTOSC DESC) AS I
        , ID
        , NAZWA
        , WARTOSC
    FROM @T
)
SELECT ID
    , NAZWA
    , WARTOSC
FROM CTE
WHERE I = 1;

Użyłem funkcji ROW_NUMBER(), aby w obrębie nazwy nadać unikalne id (PARTITION BY NAZWA). Pierwsze id nadawane jest dla największej wartości (ORDER BY WARTOSC DESC).

Samo CTE wygląda tak :

Jakieś pomysły jak można to napisać prościej, inaczej ?

Zamiana kilku wierszy w jeden za pomocą FOR XML

Jak kilka wierszy zamienić w jeden, a wartości rozdzielić przecinkiem? Odpowiedź poniżej.

DECLARE @T AS TABLE
(
    TEKST VARCHAR(50)
);

INSERT INTO @T (TEKST) 
VALUES ('ALA')
    , ('OLA')
    , ('KRZYŚ');
    
SELECT *
FROM @T;

SELECT STUFF((    SELECT ', ' + TEKST 
                FROM @T 
                FOR XML PATH('')), 1, 2, '') AS TEKST;

 

PS.

Całość odpalać na SQL 2008 i wyżej.

CTE i odwrócenie STRINGa

Dość często słyszę, że na rozmowach kwalifikacyjnych na stanowisko programisty dostaje się zadanie p.t. odwrócenie STRINGa. Oczywiście nie chodzi tu o użycie istniejącej funkcji, ale o napisanie swojej. I tu w ramach fascynacji CTE pomyślałem, że może zrobić to w T-SQL ale za pomocą pojedynczego zapytania. No i wyszło mi coś takiego :

DECLARE @STRING AS VARCHAR(8000);
SET @STRING = '123456789';

WITH CTE
AS 
(
    SELECT CAST(RIGHT(@STRING, 1) AS VARCHAR(8000)) AS REVERSE_STRING
        , DATALENGTH(@STRING) - 1 AS I
    
    UNION ALL
    
    SELECT REVERSE_STRING + RIGHT(LEFT(@STRING, I), 1)
        , I - 1
    FROM CTE
    WHERE I > 0
)
SELECT REVERSE_STRING
FROM CTE
WHERE I = 0
OPTION(MAXRECURSION 8000);

GO LOOP

Prawie nigdy nie rozdzielam zapytań w SSMS słówkiem GO. Jakoś nie jest to mi do niczego potrzebne. Dziś jednak odkryłem, że GO mam fajną właściwość. Jako parametr przyjmuje ilość wywołań danej sekwencji zapytań. Np. coś takiego :

SELECT 'WYŚWIETL MNIE 5 RAZY'
GO 5

SELECT 'A MNIE 3'
GO 3

... daje takie o to wyniki :

 

Do czego tego użyć ? Może proste wstawienie kliku testowych rekordów do tabeli, bez pisania np. pętli WHILE ?