To już trzeci wpis z serii “Skryptowanie w SQL Server 2008″. Dwa poprzednie dostępne są tu:

• [PL] Skryptowanie w SQL Server 2008 – Obiekty proceduralne
• [PL] Skryptowanie w SQL Server 2008 – Proste typy danych użytkownika

Tym razem przedstawiam sposób na skryptowanie kluczy obcych. Kiedy takie skryptowanie może się przydać? Na przykład wtedy, gdy chcemy usunąć, a następnie odtworzyć owe klucze (typowy scenariusz: import wyczyszczonych i zwalidowanych danych). W podsumowaniu podam, jakie rozwinięcia mojego kodu można zaimplementować, by za pomocą praktycznie tego samego kodu wykonywać inne zadania związane z kluczami obcymi.

Skryptowanie kluczy obcych

Zadanie: zeskryptować wszystkie klucze obce w bazie danych.

Rozwiązanie:

USE AdventureWorks2008R2;
GO
SET NOCOUNT ON;

-- tu będziemy trzymać generowany kod
DECLARE @SQL TABLE (
  LineId int IDENTITY(1,1) NOT NULL PRIMARY KEY,
  Line nvarchar(4000) NOT NULL
);

DECLARE
  @object_name sysname,
  @object_id int,
  @object_schema_name sysname,
  @parent_object_name sysname,
  @parent_object_schema_name sysname,
  @is_not_for_replication bit,
  @delete_referential_action int,
  @update_referential_action int,
  @referenced_object_schema_name sysname,
  @referenced_object_name sysname,
  @column_name sysname,
  @referenced_column_name sysname,
  @columns nvarchar(4000),
  @referenced_columns nvarchar(4000);

-- kursor do wyciągania pojedynczych kluczy obcych
DECLARE CursorFK CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR
  SELECT
    name,
    [object_id],
    SCHEMA_NAME([schema_id]),
    OBJECT_SCHEMA_NAME(parent_object_id),
    OBJECT_NAME(parent_object_id),
    OBJECT_SCHEMA_NAME(referenced_object_id),
    OBJECT_NAME(referenced_object_id),
    is_not_for_replication,
    delete_referential_action,
    update_referential_action
  FROM sys.foreign_keys
  WHERE is_ms_shipped = 0;
OPEN CursorFK;
FETCH NEXT FROM CursorFK
INTO @object_name, @object_id, @object_schema_name,
     @parent_object_schema_name, @parent_object_name,
     @referenced_object_schema_name, @referenced_object_name,
     @is_not_for_replication,
     @delete_referential_action, @update_referential_action;

WHILE @@FETCH_STATUS = 0 BEGIN
  SELECT @columns = '', @referenced_columns = '';

  -- generujemy sprawdzenie, czy klucz nie istnieje
  INSERT INTO @SQL (Line)
  SELECT 'IF OBJECT_ID(N''' +
        REPLACE(
          QUOTENAME(@object_schema_name) + '.' +
          QUOTENAME(@object_name), '''', ''''''
        ) + ''', N''F'') IS NULL';

  -- generujemy polecenie ALTER TABLE ... ADD CONSTRAINT
  INSERT INTO @SQL (Line)
  SELECT '  ALTER TABLE ' +
    QUOTENAME(@parent_object_schema_name) + '.' +
    QUOTENAME(@parent_object_name);
  INSERT INTO @SQL (Line)
  SELECT '  ADD CONSTRAINT ' + QUOTENAME(@object_name);

  -- kursor do wyciągania kolejnych kolumn klucza obcego
  DECLARE CursorFKColumns CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY
  FOR
    SELECT
      pc.name,
      rc.name
    FROM sys.foreign_key_columns AS f
    INNER JOIN sys.columns AS pc
    ON f.parent_object_id = pc.[object_id]
    AND f.parent_column_id = pc.column_id
    INNER JOIN sys.columns AS rc
    ON f.referenced_object_id = rc.[object_id]
    AND f.referenced_column_id = rc.column_id
    WHERE f.constraint_object_id = @object_id
    ORDER BY f.constraint_column_id
  OPEN CursorFKColumns;
  FETCH NEXT FROM CursorFKColumns
  INTO
    @column_name,
    @referenced_column_name;
  WHILE @@FETCH_STATUS = 0 BEGIN
    -- doklejamy do zestawów kolumn
    SET @columns = @columns + ', ' + QUOTENAME(@column_name);
    SET @referenced_columns = @referenced_columns + ', ' + QUOTENAME(@referenced_column_name);

    FETCH NEXT FROM CursorFKColumns
    INTO
      @column_name,
      @referenced_column_name;
  END;
  CLOSE CursorFKColumns;
  DEALLOCATE CursorFKColumns;

  -- generujemy kod z listami kolumn
  INSERT INTO @SQL (Line)
  SELECT '  FOREIGN KEY (' + STUFF(@columns, 1, 2, '') + ')';
  INSERT INTO @SQL (Line)
  SELECT '  REFERENCES ' +
    QUOTENAME(@referenced_object_schema_name) + '.' +
    QUOTENAME(@referenced_object_name) + '(' +
    STUFF(@referenced_columns, 1, 2, '') + ')';

  -- akcje klucza przy usuwaniu wierszy
  IF @delete_referential_action <> 0 BEGIN
    INSERT INTO @SQL (Line)
    SELECT '  ON DELETE ' +
      CASE @delete_referential_action
        WHEN 1 THEN 'CASCADE'
        WHEN 2 THEN 'SET NULL'
        WHEN 3 THEN 'SET DEFAULT'
      END;
  END;

  -- akcje klucza przy modyfikacji wierszy
  IF @update_referential_action <> 0 BEGIN
    INSERT INTO @SQL (Line)
    SELECT '  ON UPDATE ' +
      CASE @update_referential_action
        WHEN 1 THEN 'CASCADE'
        WHEN 2 THEN 'SET NULL'
        WHEN 3 THEN 'SET DEFAULT'
      END;
  END;

  -- czy wymuszamy regułę klucza w replikacjach
  IF @is_not_for_replication = 1
    INSERT INTO @SQL (Line)
    SELECT '  NOT FOR REPLICATION';

  UPDATE @SQL
  SET Line = Line + ';'
  WHERE LineId = SCOPE_IDENTITY();

  INSERT INTO @SQL (Line) SELECT 'GO';

  FETCH NEXT FROM CursorFK
  INTO @object_name, @object_id, @object_schema_name,
     @parent_object_schema_name, @parent_object_name,
     @referenced_object_schema_name, @referenced_object_name,
     @is_not_for_replication,
     @delete_referential_action, @update_referential_action;
END;
CLOSE CursorFK;
DEALLOCATE CursorFK;
SELECT Line AS ' ' FROM @SQL ORDER BY LineId;
GO

Fragment wyniku:

Uwagi: w Management Studio ustaw Results to Text, sugeruję też zwiększenie maksymalnej długości tekstu w jednej kolumnie zwracanej w wynikach zapytań (w menu głównym kliknij Tools – Options – Query Results – SQL Server – Results to Text i w polu Maximum number of characters displayed in each column wpisz 8192, a następnie uruchom ponownie Management Studio).

Komentarz do kodu:

• metadane kluczy obcych zwracają widoki sys.foreign_keys (metadane tabeli, w której tworzymy klucz, opcje dla poleceń DELETE / UPDATE, opcja wyłączenia klucza dla replikacji) oraz sys.foreign_key_columns (metadane kolumn oraz tabeli, do której odwołuje się klucz obcy),
• funkcja QUOTENAME jak zwykle służy mi do otaczania identyfikatorów nawiasami kwadratowymi i apostrofami,
• fragmenty generowanego skryptu T-SQL są dodawane w kursorze (iteracja = definicja jednego obiektu) do zmiennej tabelarycznej @SQL (linie są numerowane autonumeracją w kolumnie LineId, zakładam maksymalną długość jednej linii kodu w obiekcie na 4000 znaków),
• rozwiązanie oparłem o dwa kursory: CursorFK – do iterowania po kluczach obcych oraz zagnieżdżony CursorFKColumns – do iterowania po kolumnach bieżącego klucza,
• ponieważ nie jestem zwolennikiem wyłączania więzów, moje rozwiązanie nie uwzględnia kolumny is_disabled z widoku sys.foreign_keys (nie generuję klauzuli WITH NOCHECK nawet, gdy klucz jest wyłączony).

Słowo podsumowania

Powyższy kod można rozwinąć:

• można dołożyć filtrowanie po tabelach (czy to tabeli, w której klucz jest utworzony, czy po tabeli, do której klucz się odwołuje),
• można generować tylko składnie ALTER TABLE … DROP CONSTRAINT, jeżeli zależy nam tylko na usunięciu wybranych kluczy,
• można wykorzystać kolumnę is_system_named z widoku sys.foreign_keys, jeżeli chcemy odnaleźć te klucze, które otrzymały nazwy wygenerowane systemowo (jeżeli nie chcemy mieć w bazie danych obiektów nazywanych dość przypadkowo).

Na koniec informacja – w repozytorium kodu umieściłem kod pseudo-systemowej procedury sp_dropforeignkeys, która może posłużyć do wygenerowania kodu usuwającego klucze obce wskazujące na wybraną tabelę. W procedurze tej wykorzystałem te same obiekty systemowe, co w powyższym kodzie, więc komentarz do niej wydaje mi się zbędny. Po stworzeniu w bazie master i oznaczeniu jako obiekt systemowy procedura powinna działać w kontekście dowolnej bazy danych.

Przyjemnej zabawy z danymi geograficznymi :-)

Pobierz kopię zapasową bazy Spatials (1 MB)

Jednym z działów BeyondRelational.com (oprócz licznych blogów i forum) jest dział TSQL Challenges. Czym są TSQL Challenges (dalej używam skrótu TC)? Są to konkursy polegające na rozwiązywaniu problemów za pomocą zapytań w języku T-SQL (nowe zadanie jest ogłaszane raz na dwa tygodnie). Udział może wziąć każdy, kto ma na to ochotę (i zarejestruje się na portalu BeyondRelational.com). Zazwyczaj zadania wymagają rozwiązania postawionego problemu jednym zapytaniem SELECT (choć na ogół dopuszczalne jest używanie podzapytań i CTE), bez użycia tabel tymczasowych i dynamicznego kodu T-SQL. Zwycięża ten uczestnik, który podeśle rozwiązanie generujące poprawny wynik, spełniające podane wymagania i o najlepszych statystykach (liczy się nie tylko czas wykonania, ale także zużycie CPU oraz ilość odczytów i zapisów). Więcej o samych TSQL Challenges i o możliwości nadsyłania własnych propozycji zadań konkursowych czytaj tu: Submit a TSQL Challenge Idea.

Zabawa jest przednia. Sam spróbowałem. Wysłałem nawet ze trzy rozwiązania zadań, ale z uwagi na to, że jak dotąd nie dałem się wciągnąć na dobre w zabawę (co by to było, jakbym jeszcze w TC dał się wciągnąć :-P), moje próby kończą się w przedbiegach po wysłaniu pierwszego rozwiązania, które uznam za “good enough” (a to jest zdecydowanie za mało na takich zawodników, jacy wysyłają swoje rozwiązania na TC) :-) Co lepsi zawodnicy nadsyłają po kilka rozwiązań wymyślając takie rozwiązania, z których można się uczyć wszystkiego, co potrzebne programiście SQL Servera: od algorytmów, przez optymalizację, aż po wymyślne chwyty do zastosowania w kodzie T-SQL. Co ciekawe, jest też wersja “light” konkursu przeznaczona dla początkujących adeptów sztuki programowania w T-SQL – TSQL Beginners Challenges.

Wśród najlepszych uczestników zabawy w “dorosłe” TC znaleźć można kilku Polaków. Dwóch z nich zaprosiłem do rozmowy o TC. Zgodzili się (dzięki, chłopaki!), a zapis wywiadu znajdziesz poniżej.

–

Moimi rozmówcami są Leszek Gniadkowski (w wywiadzie jako LG) oraz Marek Powichrowski (w wywiadzie jako MP).

Leszek zawodowo pracuje jako członek zespołu administrującego dosyć dużą domeną Active Directory. Specjalizuje się w systemach opartych na MS Windows, choć ma do czynienia również z innymi OS przy okazji integracji AD z usługami opartymi na *NIX/Linux.

Marek zawodowo pracuje jako programista i konsultant systemów ERP. Specjalizuje się w projektowaniu baz danych, głównie SQL Server. Pisze aplikacje w .NET, nie unika ASP.NET. Lubi projektować sałatki śledziowe i piec chleb na własnym zakwasie.

–

PP: Witajcie. Na początek gratulacje na okazję Waszych sukcesów w konkursach TSQL Challenges!

LG: Witam, dziękujemy, szczęśliwie trwa passa (przyp. PP – Leszek jest ex aequo pierwszy w rankingu TC!) , największym sukcesem jest jednak sama wiedza nabyta przy udziale w TC.

MP: Dzięki, to miłe. Na razie mam 13 SQL Stars i mogę już sobie zrobić T-Shirt’a z logo „TSQL Challenges Winner” :-) A i jeszcze mogę sobie wydrukować certyfikat potwierdzający ten fakt. To takie drobiazgi, ale miłe.

PP: Skąd dowiedzieliście się o konkursach TSQL Challenges i witrynie beyondrelational.com?

LG: W moim przypadku z forum portalu WSS. Dokładniej stąd. Od czasu umieszczenia tego wątku (trwał wtedy TC20), rozpocząłem naukę TSQL i próbowałem sił w następnych TC.

MP: W moim przypadku było dokładnie tak samo. Tamto zadanie zelektryzowało całe forum SQL Server na WSS.pl. Interesujące było to, że trzeba to rozwiązać w jednym zapytaniu. Pamiętam, że podobny problem rzucił kiedyś Maciej Pilecki. Piękne wyzwanie. Wysłałem swoje rozwiązanie i niejakim szokiem było to, że trzeba długo czekać na wyniki rywalizacji. Ale przeszedłem wszystkie fazy (basic test, tricky data i load test) i ostatecznie byłem sklasyfikowany poza pierwszą 10-tką, ale jak na początek to i tak było coś. Potem była przerwa i Leszek odezwał się do mnie przy zadaniu TC28, które miało bardzo mało zgłoszonych rozwiązań. Mi poszło gładko i przeszło dwie pierwsze fazy testów i kiedy już byłem w „ogródku i już witałem się z gąską” przepadłem w load test. To sparzenie się nakazuje mi teraz przyglądać się baczniej wydajności. Ale co ciekawe, Leszek który wygrał tę odsłonę był tym kompletnie zaskoczony bo wstępne porównania statystyk na naszych danych testowych dawały mi przewagę. Ale dane do load test odwróciły tę relację. Także emocje są do samego końca. Od zadania TC28 wciągnąłem się. Wykonałem jeszcze dwa zadania w tył i byłem wśród zwycięzców i od tej pory nie odpuszczam żadnego zadania.

PP: Co takiego jest w TSQL Challenges, że poświęcacie czas na podejmowanie kolejnych „wyzwań”?

MP: To głód tego pięknego momentu olśnienia, gdy znajduje się rozwiązanie proste, z pięknymi statystykami. To głód tych endorfin, które zalewają człowieka powodując, że wszystko wokoło nagle staje się piękne i nic nie jest w stanie tego zakłócić. A poza tym to świetne zajęcie dla umysłu aby przypadkiem nie zachciałoby mu się spuścić z tonu.

LG: Dobra rozrywka umysłowa, sprawdzanie się, rywalizacja. TC w pewnym stopniu uzależniają. Mix samego języka TSQL i zakręconych zadań to mieszanka gwarantująca intelektualną ucztę. Co do samego czasu, jego większość nie jest spędzona bezpośrednio przy komputerze. Nad zadaniem często myślę przy okazji, w czasie wykonywania dowolnej czynności: jazdy samochodem, posiłku, przed snem itd. Samo kodowanie zajmuje już zwykle mniej czasu.

PP: Jak dużo czasu poświęcacie średnio na stworzenie rozwiązania gotowego do zgłoszenia w konkursie i czy zdarza się, że wysyłacie więcej niż jedno rozwiązanie?

LG: Różnie, od 30 minut do dwóch tygodni na stworzenie rozwiązania, które „działa”. Najczęściej parę godzin. Później pracuje się nad ulepszeniem bądź innym podejściem do problemu podkręcającym statystyki. Zdarzało się, że były TC, do których z braku czasu podchodziłem w ostatni dzień przed terminem zakończenia, poświęcając niedzielę. Czasami wysyłam jedną wersję rozwiązania, czasami dwie i więcej. Natomiast prawie zawsze wysyłam poprawione rozwiązania spamując trochę upload.

MP: Różnie z tym bywa. Niektóre pozornie trudne rozwiązują się błyskawicznie w kilku wersjach. Inne kosztują sporo czasu. Jeżeli mam kilka rozwiązań to oczywiście je wysyłam. Trzeba pamiętać, że każde rozwiązanie zadania przechodzi przez trzy fazy weryfikacji: basic test – czyli weryfikacja rozwiązania danymi podanymi przy zadaniu, tricky data – weryfikacja danymi „pokręconymi” w celu testowania odporności rozwiązania na zakłócenia no i na koniec load test, który może wyeliminować z gry. Więc każda dodatkowa wersja rozwiązania to szansa na „przyżycie” w turnieju.

PP: Który TSQLChallenge z dotychczasowych najbardziej przypadł Wam do gustu i dlaczego?

MP: Bezwzględnie TC37! To zadanie najbardziej mnie zmęczyło ale też dało najwięcej radości gdy się wykluło wreszcie z poszukiwań mego umysłu. W samo rozwiązanie mogę się gapić i podziwiać jakie sprytne i szybkie rzeczy można robić w T-SQL-u (nawet bez użycia indeksów). Mam porównanie bo moja pierwsza wersja rozwiązania tego zadania była tragiczna pod względem wydajnościowym i to o kilka rzędów wielkości. Obserwując rozwiązania konkursowe widać, jaki power ma T-SQL, jak bardzo elastycznym jest językiem.

LG: Trudno wybrać, ale wybrałbym TC28 (przyp. PP – Leszek wygrał ten TC!). Za kompletną pustkę w głowie po zapoznaniu się z zadaniem i wiele prób wypełnienia tej pustki kierunkiem zmierzającym do utworzenia sensownej solucji.

PP: Wspominaliście mi, że wzajemnie się „nakręcacie” w czasie konkursów. W jaki sposób?

LG: Wymieniamy się statystykami, uwagami na temat TC, możliwymi „tricky data”. Jeżeli wiem, że Marek zadanie zrobił z lepszymi statystykami niż uzyskałem, mam powód do szukania innego rozwiązania. Korzystamy też z forum, jako źródła statystyk, jak i wielu dodatkowych uwag, czy omawiania samych założeń do TC.

MP: Tak to prawda. Wymieniamy się ze sobą statystykami i powoduje to sportową żyłkę rywalizacyjna między nami. A poza tym przez te kilka miesięcy kontaktów mogliśmy się lepiej poznać nie tylko na polu T-SQL’a ale również zainteresowań w innych technologiach, na przykład w robieniu własnego chleba na własnym zakwasie, piwa, jogurtu, przyrządzaniu sałatek ze śledzi. I to jest ta wartość dodana uczestnictwa w tych zawodach.

PP: Gdybyście mieli mnie zachęcić do udziału w konkursach TSQLChallenge, co byście mi powiedzieli?

MP: Poza zdobywaniem doświadczenia w trudnych wyzwaniach oraz podglądaniem i uczeniem się efektownych technik innych uczestników nie mniej istotna jest żyłka sportowa towarzysząca turniejowi. Co prawda same emocje są rozłożone w czasie tak bardzo, że można je porównać do emocji towarzyszących turniejowi szachowemu, ale jeżeli masz żyłkę sportową, która drga w Tobie, to niewątpliwie Twoje miejsce jest na TC!

LG: TC to dobre miejsce do nauki i rozwijania umiejętności w kodowaniu TSQL. W odróżnieniu od kursów i książek, które często przekazują wiedzę w sposób nudny, schematyczny, TC ukazują problem do rozwiązania, nie podają wiedzy na tacy, tylko zmuszają do samodzielnego jej poszukiwania, intensyfikują proces myślenia i umożliwiają dużo szybsze wchłanianie wiedzy. TC są również znakomitym workiem treningowym dla szarych komórek (ostatni raz ten specyficzny poziom endorfin uzyskałem w szkole średniej rozwiązując trudniejsze całki ;-) ).

PP: Dziękuję za wywiad i powodzenia w kolejnych „wyzwaniach”!

LG: A ja dziękuję za zaproszenie, miłą rozmowę i poparcie w dalszych zmaganiach z tsql’owym puzzle.

MP: Dzięki i do zobaczenia przy okazji kolejnych wyzwań T-SQL z również Twoim udziałem.

–

A zatem, jeżeli kręci Cię zdrowe współzawodnictwo, chcesz poćwiczyć szare komórki i przy okazji potrenować pisanie kodu T-SQL, spróbuj swoich sił w TSQL Challenges. Obserwując Marka i Leszka muszę Cię jedynie ostrzec, że to wciąga jak diabli :-) Ale jeśli jeszcze nie masz swojego ulubionego uzależnienia… ;-)

I na koniec – chcę wyrazić szacunek dla osób prowadzących portal BeyondRelational.com i konkursy TC. Publikować co dwa tygodnie nowe zadanie, czuwać nad rozwojem sytuacji w każdym konkursie, robić testy nadesłanych rozwiązań i panować nad tym wszystkim, to naprawdę duże wyzwanie dla organizatorów. I jak na razie im się to udaje! Stworzyli atmosferę zdrowej rywalizacji i swego rodzaju społeczność uczestników zabawy. Brawo za ideę i jej realizację.

–

PS. A już zupełnie na koniec pragnę nadmienić, że ten wywiad jest swego rodzaju zapowiedzią mojego własnego, jednorazowego konkursu a la TC, który zorganizuję na blogu. Szczegóły w drugiej połowie września. Stay tuned!

Klauzula FOR XML EXPLICIT jest bodaj najrzadziej używaną z klauzul FOR XML. O wiele częściej decydujemy się na użycie FOR XML PATH (od SQL Server 2005) lub FOR XML AUTO (SQL Server 2000), ponieważ obie klauzule wydają się prostsze w użyciu niż FOR XML EXPLICIT (jak to okreslił jeden z moich kolegów – “FOR XML EXPLICIT przy FOR XML PATH wygląda jak neanderltalczyk przy homo sapiens”). Ale mało kto bierze pod uwagę, że FOR XML EXPLICIT daje – jako jedna z dwóch klauzul FOR XML na SQL Server 2005 i nowszych oraz jako jedyna klauzula na SQL Server 2000 – możliwość zwracania dokumentów XML, w których znajdą się zarówno elementy, jak i atrybuty. A już prawie nikt nie wie, że jedynie FOR XML EXPLICIT umożliwia generowanie dokumentów XML zawierających sekcje CDATA. I to właśnie owo niedocenianie i unikanie klauzuli FOR XML EXPLICIT skłoniło mnie do napisania tego tekstu.

Generujemy poligon

Załóżmy, że mamy dwie, uproszczone do granic możliwości, powiązane ze sobą logicznie tabele – #Faktury i #Pozycje. Tabela #Faktury zawiera typowe dane nagłówkowe faktur – numer faktury, datę wystawienia i nazwę kontrahenta. Tabela #Pozycje zawiera pozycje (linijki) faktury, w których znajdują się: numer faktury (aby było łatwo powiązać pozycje z fakturami), numer pozycji na fakturze (takie typowe Lp), nazwę produktu, cenę (dla uproszczenia bez rozgraniczenia na netto i brutto oraz z pominięciem VAT-u) oraz ilość towaru. Kod do utworzenia tabel i wypełnienia ich danymi dwóch przykładowych faktur:

IF OBJECT_ID(N'tempdb.dbo.#Faktury') IS NOT NULL
  DROP TABLE #Faktury;
CREATE TABLE #Faktury (
  NrFaktury varchar(10),
  DataWystawienia datetime,
  Kontrahent varchar(100)
);

IF OBJECT_ID(N'tempdb.dbo.#Pozycje') IS NOT NULL
  DROP TABLE #Pozycje;
CREATE TABLE #Pozycje (
  NrFaktury varchar(10),
  NrPozycji int,
  Produkt varchar(50),
  Ilosc int,
  Cena money
);

INSERT INTO #Faktury (NrFaktury, DataWystawienia, Kontrahent)
SELECT '04/07/2010','20100716','FirmaA'
UNION ALL
SELECT '05/07/2010','20100716','FirmaB';

INSERT INTO #Pozycje (NrFaktury, NrPozycji, Produkt, Ilosc, Cena)
SELECT '04/07/2010',1,'Produkt1',10,100.00
UNION ALL
SELECT '04/07/2010',2,'Produkt2',2,10.00
UNION ALL
SELECT '04/07/2010',3,'Produkt3',1,10.50
UNION ALL
SELECT '05/07/2010',1,'Produkt1',5,10.00
UNION ALL
SELECT '05/07/2010',2,'Produkt3',1,10.50;

FOR XML EXPLICIT krok po kroku

Postawmy sobie pierwsze zadanie.

Zadanie: użyć klauzuli FOR XML EXPLICIT w zapytaniu odwołującym się do powyższych dwóch tabel, by uzyskać w wyniku dokument XML o następującej postaci:

<Faktura Numer="04/07/2010">
  <DataWystawienia>2010-07-16T00:00:00</DataWystawienia>
  <Kontrahent>FirmaA</Kontrahent>
  <Pozycja NrPozycji="1" Produkt="Produkt1" Cena="100.0000" Ilosc="10" />
  <Pozycja NrPozycji="2" Produkt="Produkt2" Cena="10.0000" Ilosc="2" />
  <Pozycja NrPozycji="3" Produkt="Produkt3" Cena="10.5000" Ilosc="1" />
</Faktura>
<Faktura Numer="05/07/2010">
  <DataWystawienia>2010-07-16T00:00:00</DataWystawienia>
  <Kontrahent>FirmaB</Kontrahent>
  <Pozycja NrPozycji="1" Produkt="Produkt1" Cena="10.0000" Ilosc="5" />
  <Pozycja NrPozycji="2" Produkt="Produkt3" Cena="10.5000" Ilosc="1" />
</Faktura>

Rozwiązanie:

SELECT
  1 AS Tag,
  NULL AS Parent,
  F.NrFaktury AS [Faktura!1!Numer],
  F.DataWystawienia AS [Faktura!1!DataWystawienia!ELEMENT],
  F.Kontrahent AS [Faktura!1!Kontrahent!ELEMENT],
  NULL AS [Pozycja!2!NrPozycji],
  NULL AS [Pozycja!2!Produkt],
  NULL AS [Pozycja!2!Cena],
  NULL AS [Pozycja!2!Ilosc]
FROM #Faktury F
UNION ALL
SELECT
  2,
  1,
  P.NrFaktury,
  NULL,
  NULL,
  P.NrPozycji,
  P.Produkt,
  P.Cena,
  P.Ilosc
FROM #Pozycje P
ORDER BY [Faktura!1!Numer], Tag, [Pozycja!2!NrPozycji]
FOR XML EXPLICIT;

Komentarz (krok po kroku):

Aby zrozumieć, jak działa FOR XML EXPLICIT, uruchom najpierw kod powyższego zapytania bez klauzuli FOR XML EXPLICIT. Wynik powinien wyglądać następująco:

Kolorami żółtym i zielonym zaznaczyłem obie faktury i ich pozycje (wiersze zawierające dane nagłówkowe są oznaczone ciemniejszymi odcieniami).

Zwróć uwagę na kolejność wierszy. Nieprzypadkowo wiersze pozycji znajdują się tuż za wierszem nagłówka odpowiadającej pozycjom faktury. Sortowanie jest kluczem do sukcesu w zapytaniach z FOR XML EXPLICIT. W tym przypadku dane zostały posortowane względem kolejno: numeru faktury (tu bardzo ważna uwaga – zauważ, że w drugim zapytaniu SELECT wybierającym pozycje faktur także wybieram numer faktury – zapewniam w ten sposób wspólną kolumnę dla wszystkich wierszy wynikowych zapytania), kolumny Tag (o której za chwilę, a dzięki której wiersze nagłówków znajdują się w wyniku przed wierszami pozycji) oraz numeru pozycji (dzięki temu w dokumencie XML pozycje dla każdej faktury będą umieszczone zgodnie z kolejnością na fakturze). To uporządkowanie rekordów jest ważne, ponieważ FOR XML EXPLICIT najpierw buduje taką tabelę, a następnie działa na niej niczym kursor – buduje dokument XML doklejając odpowiednio spreparowane dane z kolejnych wierszy ujęte w elementy i atrybuty. To od kolejności wierszy zależy postać wynikowego dokumentu XML.

Zapytanie składa się z dwóch zapytań SELECT połączonych operatorem UNION ALL. W każdym z dwóch zapytań liczba kolumn jest taka sama (to oczywiste, bo takie są wymagania operatora UNION). Pierwsze dwie kolumny – Tag i Parent - są kolumnami specjalnymi umożliwiającymi poprawne działanie klauzuli FOR XML EXPLICIT. Każde zapytanie otrzymuje swój numer (Tag, wcale nie musi być unikalny) oraz numer “rodzica” (Parent, jeżeli jest NULL, rodzica nie ma). Numery te decydują, gdzie w strukturze wynikowego dokumentu XML zostaną umieszczone dane z poszczególnych wierszy.

Prześledźmy na podstawie dwóch pierwszych wierszy pokazanych poniżej, jak budowany jest wynikowy dokument XML.

Na początek brany jest pierwszy rekord. Dla niego w kolumnie Tag widnieje wartość 1. SQL Server szuka w tym wierszu, gdzie znajdują się wartości w kolumnach, które mają znacznik 1 w nazwie (np. Faktura!1!Numer). Nazwy kolumn decydują o tym, do którego Tag-a zalicza się wartość oraz jak będą nazywały się elementy / atrybuty. Jeżeli na końcu nazwy kolumny pojawi się słowo ELEMENT, wartość będzie umieszczona w elemencie (inaczej znajdzie się w atrybucie). Dla pierwszego wiersza SQL Server buduje więc coś takiego:

<Faktura Numer="04/07/2010">
  <DataWystawienia>2010-07-16T00:00:00</DataWystawienia>
  <Kontrahent>FirmaA</Kontrahent>

Nazwy kolumn zdecydowały, że elementem nadrzędnym jest <Faktura>, numer faktury znalazł się w atrybucie Numer, data wystawienia faktury w elemencie <DataWystawienia>, a nazwa kontrahenta w elemencie <Kontrahent>.

Następnie brany jest drugi wiersz. W nim w kolumnie Tag SQL Server znajduje wartość 2, zatem uwzględnia w tym wierszu tylko te kolumny, w których nazwach występuje znacznik 2 (np, Pozycja!2!NrPozycji). Ponieważ w kolumnie Parent znajduje się wartość 1, a poprzedni wiersz miał Tag równy 1 (patrz czerwona strzałka na rysunku obrazującym wiersze), SQL Server wie, że XML powstały z bieżącego wiersza należy zagnieździć w XML-u powstałym z poprzedniego wiersza. Dzięki temu dokument zbudowany z dwóch wierszy wygląda tak:

<Faktura Numer="04/07/2010">
  <DataWystawienia>2010-07-16T00:00:00</DataWystawienia>
  <Kontrahent>FirmaA</Kontrahent>
  <Pozycja NrPozycji="1" Produkt="Produkt1" Cena="100.0000" Ilosc="10" />

I tak kolejne pozycje będą doklejane wewnątrz znacznika <Faktura>, aż zostanie napotkany wiersz, w którym w kolumnie Tag będzie znajdowała się wartość 1 (będzie to początek następnej faktury). Wówczas element <Faktura> otwarty przy tworzeniu XML-a z pierwszego wiersza zostanie zamknięty, czyli pojawi sie koniec elementu – </Faktura>.

FOR XML EXPLICIT – tips & tricks

A teraz spróbujmy zmodyfikować nieco zadanie.

Zadanie: użyć klauzuli FOR XML EXPLICIT w zapytaniu odwołującym się do tabel #Faktury i #Pozycje, by uzyskać w wyniku dokument XML o następującej postaci:

<Faktura Numer="04/07/2010">
  <DataWystawienia>2010-07-16T00:00:00</DataWystawienia>
  <Kontrahent>FirmaA</Kontrahent>
  <Pozycje>
    <Pozycja NrPozycji="1" Produkt="Produkt1" Cena="100.0000" Ilosc="10" />
    <Pozycja NrPozycji="2" Produkt="Produkt2" Cena="10.0000" Ilosc="2" />
    <Pozycja NrPozycji="3" Produkt="Produkt3" Cena="10.5000" Ilosc="1" />
  </Pozycje>
</Faktura>
<Faktura Numer="05/07/2010">
  <DataWystawienia>2010-07-16T00:00:00</DataWystawienia>
  <Kontrahent>FirmaB</Kontrahent>
  <Pozycje>
    <Pozycja NrPozycji="1" Produkt="Produkt1" Cena="10.0000" Ilosc="5" />
    <Pozycja NrPozycji="2" Produkt="Produkt3" Cena="10.5000" Ilosc="1" />
  </Pozycje>
</Faktura>

Różnica w stosunku do poprzedniego zadania polega na tym, że teraz chcemy pozycje mieć otoczone znacznikami <Pozycje> i </Pozycje>.

Rozwiązanie:

SELECT
  1 AS Tag,
  NULL AS Parent,
  F.NrFaktury AS [Faktura!1!Numer],
  F.DataWystawienia AS [Faktura!1!DataWystawienia!ELEMENT],
  F.Kontrahent AS [Faktura!1!Kontrahent!ELEMENT],
  NULL AS [Pozycje!2],
  NULL AS [Pozycja!3!NrPozycji],
  NULL AS [Pozycja!3!Produkt],
  NULL AS [Pozycja!3!Cena],
  NULL AS [Pozycja!3!Ilosc]
FROM #Faktury F
UNION ALL
SELECT
  2,
  1,
  F.NrFaktury,
  NULL,
  NULL,
  NULL,
  NULL,
  NULL,
  NULL,
  NULL
FROM #Faktury F
UNION ALL
SELECT
  3,
  2,
  P.NrFaktury,
  NULL,
  NULL,
  NULL,
  P.NrPozycji,
  P.Produkt,
  P.Cena,
  P.Ilosc
FROM #Pozycje P
ORDER BY [Faktura!1!Numer], Tag, [Pozycja!3!NrPozycji]
FOR XML EXPLICIT;

I teraz zadanie dla Ciebie – spróbuj prześledzić działanie FOR XML EXPLICIT w powyższym kodzie (kieruj się wskazówkami podanymi przeze mnie w pierwszym zadaniu – zakomentuj na przykład klauzulę FOR XML EXPLICIT i uruchom zapytanie ponownie, by zobaczyć, jak układają się wiersze). Powyższy trick możesz stosować wszędzie tam, gdzie chodzi o wygenerowanie dodatkowego elementu, w którym zostaną osadzone elementy z danymi (tak, to jest metoda na wygenerowanie elementu ROOT na SQL Server 2000!).

Podsumowanie

Wszystkich tych, którzy dotąd omijali FOR XML EXPLICIT szerokim łukiem, zachęcam do poeksperymentowania. Sugeruję obejrzeć różne warianty, oglądać, jak zmienia się wynikowy XML w zależności od sortowania i wartości w kolumnach Tag i Parent. Zabawę polecam zwłaszcza osobom, którym przychodzi w pracy generować XML-a na SQL Server 2000. Tam klauzula FOR XML EXPLICIT może być niekiedy jedynym sensownym ratunkiem w sytuacji, gdy mamy wygenerować dokument XML o z góry narzuconej strukturze.

Dziękuję wszystkim, którzy poświęcili swój czas i zagłosowali. Dało mi to przegląd sytuacji. Decyzja, jaką podejmuję, jest podyktowana pragmatyzmem. A zatem… Tada!

Posty na SQLGeek.pl będą wyświetlane w całości.

Dlaczego?

• Bo w razie czego jest archiwum, w którym tytuły wszystkich wpisów są wypisane wg miesiąca publikacji.
• Bo nie trzeba nigdzie klikać, by czytać najnowsze posty.
• Bo nie muszę edytować postów historycznych i wstawiać w nie sekcji Read More.
• Bo sekcja Read More jest kompletnie “niekastomizowalna” w skórce Mystique (a nie chcę dogrywać kolejnego plugina WordPressa).
• Bo “wielcy bloggerzy SQL-owi” też tak mają ;-)
• Bo ci, co głosowali na opcję pełnej treści wpisów, głosowali najgłośniej (komentowali żywo wpis ogłaszający ankietę).
• Bo ci, co głosowali na opcję “pół na pół” w zasadzie po części też głosowali za pełną treścią wpisów na głównej stronie bloga :-)

Jeszcze raz dzięki za pomoc w podjęciu decyzji i miłego czytania!

CREATE TRIGGER trg_LogSysadminLogon
ON ALL SERVER
FOR LOGON
AS
BEGIN
SET NOCOUNT ON
IF IS_SRVROLEMEMBER('sysadmin') = 1
  INSERT INTO master.dbo.SysadminLogon (Sysadmin, LogonDate)
  SELECT SUSER_SNAME(), GETDATE()
END
GO

Komentarz do kodu: logon trigger dość prosty, używając funkcji IS_SRVROLEMEMBER sprawdzamy, czy bieżący login (ten, który próbuje się logować), jest sysadminem i jeżeli jest, zapisujemy jeden wiersz (nazwę logina i datę logowania) w tabeli dbo.SysadminLogon w bazie master.

Już po fakcie zacząłem się jednak zastanawiać, czy nie pospieszyłem się ze swoją poradą i doradzaniem logon triggera. Swego czasu napisałem wspólnie z Markiem Adamczukiem artykuł o logon triggerach dla polskiej strony Technet. W artykule tym wyraźnie zaznaczyliśmy, że istnieje co najmniej kilka przyczyn, z których działanie logon triggera może skutecznie uniemożliwić zalogowanie się użytkownika do serwera baz danych. I tu mnie tknęło. Chcemy mieć mechanizm monitorowania. Coś, co nie ingeruje w sam proces logowania, a jedynie zapisuje informację o tym, że jakiś sysadmin się zalogował. A skoro logon trigger przez swą naturę może skutecznie uniemożliwić owe logowanie (albo na przykład opóźnić je na skutek problemów wydajnościowych operacji w nim zapisanych), niespecjalnie nadaje się na mechanizm bezpiecznego (bezinwazyjnego, jak bym to nazwał) logowania. Wystarczy jeden błąd, jeden brak uprawnień, jeden wykonany w ciele triggera SELECT “w powietrze” (tak, tak, wykonanie zapytania SELECT w logon triggerze też powoduje błąd logowania!) i użytkownik zobaczy komunikat nieciekawej treści:

I tu Extended Events (w skrócie XE) są jednak mechanizmem o wiele bezpieczniejszym i już wiem, o czym będzie jeden z następnych wpisów na moim blogu. Cierpliwości, XE nadchodzą :-)

[EDYCJA: 2010-09-03]

A jednak wygląda na to, że Extended Events nie nadają się do śledzenia logowania (nie udało mi się znaleźć zdarzenia XE, które dałoby możliwość takiego śledzenia). Słuszną opcją za to wydaje się użycie Event Notifications, które oferują asynchroniczność i brak ingerencji w sam proces logowania.

[/EDYCJA]

Dzień 23 sierpnia 2010 roku – ależ to był poniedziałek! Najpierw z samego rana awaria bloga. Przez chwilę myślałem, że to jakiś atak DDOS albo serwer nie wytrzymał naporu czytelników (czy to możliwe, żeby blog po jednym wpisie stał się aż tak popularny?). Ale kolega z firmy hostującej bloga wyjaśnił mi, że to nie był czynnik ludzki. Grunt, ze blog odżył i duskusja pod najbardziej obleganym wpisem na moim blogu trwała w najlepsze :-) 

Zajawka na temat – użytkownik zasiewa niepewność

Najlepsze jednak nadeszło po południu. Na forum WSS.pl użytkownik lechuCC zapytał o limit pamięci RAM w SQL Server 2008 R2 Express Edition. Udzieliliśmy z Krzyśkiem Stachyrą standardowej odpowiedzi wyczytanej na oficjalnej stronie produktu SQL Server 2008 (ale nie na stronie SQL Server 2008 R2! tam nic na ten temat nie ma, ale przed udzieleniem odpowiedzi koledze nawet nie szukałem…), że limit pamięci wynosi 1GB dla bufora danych. Kolega sprawdzał na wiele sposobów, ale zawsze wychodziło mu blisko 1,4GB, a to jednak trochę więcej niż rzekome 1GB… 

Postanowiłem się przekonać, jaka jest szara rzeczywistość :-) Opis wykonanych czynności przedstawiam poniżej. 

Test szarej rzeczywistości

Na instancji SQL Server 2008 R2 Express Edition, w której wynik zapytania: 

SELECT @@VERSION;

wyglądał tak: 

założyłem sobie bazę danych o nazwie Test. Powiększyłem plik danych tej bazy do 2GB i plik dziennika transakcji do 100MB (asekuracyjnie). W tejże bazie puściłem w ruch taki kawałek kodu: 

USE Test;
GO
IF OBJECT_ID(N'dbo.t', N'U') IS NOT NULL
  DROP TABLE dbo.t;
GO
CREATE TABLE dbo.t (a char(8000));
GO
INSERT INTO dbo.t (a)
SELECT REPLICATE('a', 8000)
FROM sys.all_objects WHERE is_ms_shipped = 1; -- 1981 wierszy
CHECKPOINT;
GO 100

Po 100 wykonaniach wsadu z poleceniem INSERT mam sporą tabelę – z liczbą wierszy równą 198100, a każdy wiersz rezyduje na osobnej stronie danych (bo wstawiłem tyle danych w jeden wiersz, że drugi już na tych samych 8 kilobajtach się nie zmieści). 

Teraz opróżniam cały bufor danych: 

DBCC DROPCLEANBUFFERS;
GO

I wykonuję zapytanie, którego celem jest wrzucenie do bufora danych wszystkich danych z tabeli dbo.t: 

SELECT COUNT(*) FROM dbo.t; -- wynik: 198100, wykonany został skan tabeli

Zaglądam do bufora zapytaniami, które już prezentowałem na blogu. Na początek informacja, która baza ile megabajtów zajmuje w buforze: 

SELECT
  CASE
    WHEN database_id = 32767 THEN 'mssqlsystemresource'
    ELSE DB_NAME(database_id)
  END AS [Database],
  CONVERT(numeric(38,2),(8.0 / 1024) * COUNT(*)) AS [In buffer cache (MB)]
FROM sys.dm_os_buffer_descriptors
GROUP BY database_id
ORDER BY 2 DESC;
GO

Wynik: 

Database            In buffer cache (MB)
------------------- --------------------
Test                1383.83
tempdb              0.24
mssqlsystemresource 0.19
master              0.06

Czyli że baza Test zajmuje blisko 1,4GB… 

Skoro tak, to przekonajmy się, który obiekt tyle miejsca zajął spośród obiektów z bazy Test: 

USE Test;
GO
SELECT
  QUOTENAME(OBJECT_SCHEMA_NAME(p.object_id)) + '.' +
  QUOTENAME(OBJECT_NAME(p.object_id)) AS Object,
  CONVERT(numeric(38,2),(8.0 / 1024) * COUNT(*)) AS [In buffer cache (MB)]
FROM sys.dm_os_buffer_descriptors AS d
INNER JOIN sys.allocation_units AS u
ON d.allocation_unit_id = u.allocation_unit_id
INNER JOIN sys.partitions AS p
ON (u.type IN (1,3) AND u.container_id = p.hobt_id)
OR (u.type = 2 AND u.container_id = p.partition_id)
WHERE d.database_id = DB_ID()
GROUP BY QUOTENAME(OBJECT_SCHEMA_NAME(p.object_id)) + '.' +
  QUOTENAME(OBJECT_NAME(p.object_id))
ORDER BY 2 DESC;
GO

Wynik: 

Object                   In buffer cache (MB)
------------------------ --------------------
[dbo].[t]                1382.40
[sys].[sysobjvalues]     0.12
[sys].[syscolpars]       0.07
[sys].[sysschobjs]       0.02
[sys].[syssingleobjrefs] 0.02
[sys].[sysiscols]        0.02
[sys].[sysallocunits]    0.01
[sys].[sysrowsets]       0.01
[sys].[sysidxstats]      0.01

I proszę. Jedna duża tabela zajmuje w buforze rzeczone blisko 1,4GB. 

Niespodzianka? Pomyślałem, że może moje zapytania są błędnie napisane. Zatem pora zajrzeć do liczników monitora wydajności (perfmona): 

SELECT
  object_name,
  counter_name,
  cntr_value / 1024 AS cntr_value_MB
FROM sys.dm_os_performance_counters
WHERE object_name LIKE '%Memory Manager%'
AND counter_name IN ('Total Server Memory (KB)', 'Target Server Memory (KB)');
GO

Wynik: 

object_name                           counter_name              cntr_value_MB
------------------------------------- ------------------------- -------------
MSSQL$SQL2008R2EXPRESS:Memory Manager Target Server Memory (KB) 1410
MSSQL$SQL2008R2EXPRESS:Memory Manager Total Server Memory (KB)  1410

Zatem Perfmon mówi, że instancja zajęła 1410 megabajtów na bufor danych i – co ciekawe – tyle właśnie zamierzała… 

Dla pewności jeszcze: 

DBCC MEMORYSTATUS;
GO

Gdzie w wyniku znalazłem między innymi: 

Buffer Pool Value
----------- -----------
Committed   180480
Target      180480

Kalkulator (T-SQL) w ręce i liczymy ile wychodzi z pomnożenia 180480 przez 8 (tyle kilobajtów ma strona danych) i podzielenia wyniku przez 1024 (żeby wynik wyszedł w megabajtach): 

SELECT 8 * 180480 / 1024 AS [Buffer pool in MB]; -- 1410

I w tym momencie już nie mam wątpliwości, że ta instancja wzięła 1,4GB pamięci RAM na bufor danych! A napisano, że miała wziąć nie więcej niż 1GB… 

Jak jedno się nie zgadza…

… to trzeba sprawdzić, czy inny limit opisany w reklamowych folderach jest prawdziwy (do osiągnięcia i nie do przekroczenia). Tym limitem jest 10GB na pliki danych w pojedynczej bazie danych w SQL Server 2008 R2 Express Edition. 

Pierwszy ruch – spróbujmy zwiększyć wielkość pliku do 1MB powyżej owych 10GB: 

USE master;
GO
ALTER DATABASE Test
MODIFY FILE (
  NAME = N'Test',
  SIZE = 10486784KB
);
GO

Odpowiedź serwera: 

Ok, tak się nie da :-) 

Test kolejny – co będzie, jak wstawimy tyle danych, że plik sam będzie zmuszony urosnąć ponad wymienione 10240MB. Tu po prostu wielokrotnie kopiuję moją dużą tabelę dbo.t z testu bufora danych: 

SELECT * INTO dbo.t1 FROM dbo.t; -- tu jedynie zmieniam numerki na kolejne
GO

W końcu serwer odpowiada: 

Z tego wynika, że 10GB jest prawdziwym ograniczeniem rozmiaru danych w pojedynczej bazie danych w SQL Server 2008 R2 Express Edition. 

To jak to w końcu jest?

Jest tak, że jak sami nie sprawdzimy, to może się okazać, że informacje przekazywane drogą poczty pantoflowej mają się nijak do szarej rzeczywistości (i ja przyłożyłem w tym przypadku rękę do powielania, jak się okazuje niekoniecznie prawdziwej, informacji). Jest też tak, że nawet sam Microsoft nie zna ograniczeń swojego produktu, bo ludzie z Redmond proszeni o komentarze do zademonstrowanego tu testu nie bardzo wiedzą, jak sensownie to wytłumaczyć (próbują wymyślać wersje zeznań mówiące, że niby jakieś tam systemowe obiekty i tajemnicze pule systemowe zajmują dodatkowe megabajty w buforze), ale obstają przy wersji, że limit dla bufora nadal wynosi 1GB! Może by nie było o co robić szumu, ale… Ale w edycji Express liczy się każdy megabajt, a tu najpewniej mamy nieścisłość rzędu 40% :-) A to już podstawa do zadania sobie pytania – czy mogę wykorzystać te oddane do dyspozycji “za darmo” 400MB bufora? Skoro w Microsoft o nich nie wiedzą, to pewnie mogę, bo skąd będą wiedzieli, że wykorzystuję coś, o istnieniu czego nie wiedzą ;-) 

Acha, i ciekawostka na koniec – dla SQL Server 2008 R2 Express Edition można śmiało ustawić ‘max server memory (MB)’ na 2048 ;-) Ani SQL Server, ani Management Studio ani pisną, choć taki limit jest bez sensu, bo instancja nie użyje więcej niż 1GB… tfu, wróć… 1,4GB pamięci na bufor danych :-) 

PS. Za chwilę instaluję SQL Server 2008 Express Edition i powtarzam test. Dopiszę obserwacje do tego wpisu. Kto wie, może i na poprzedniej wersji rzeczony limit nie wynosi 1GB ;-) 

[EDYCJA: 2010-08-24]  

Ponieważ Łukasz Grala już zrobił test na SQL Server 2005 Express Edition (patrz jego komentarz do tego wpisu) i wygląda na to, że sytuacja jest analogiczna, nie będę testował, jak to jest na SQL Server 2008 Express Edition. 

[/EDYCJA]

Postanowiłem troche poszperać w temacie.

Spróbujmy uruchomić taki kawałek kodu T-SQL:

DECLARE @t TABLE (c1 int PRIMARY KEY);
DECLARE @x TABLE (c2 int PRIMARY KEY);
INSERT INTO @t SELECT DISTINCT number FROM master.dbo.spt_values;
INSERT INTO @x SELECT TOP 1 number FROM master.dbo.spt_values;
SELECT * FROM @t t INNER JOIN @x x ON t.c1 = x.c2 OPTION (RECOMPILE); --(1)
SELECT * FROM @t t INNER JOIN @x x ON t.c1 = x.c2;                    --(2)

Zapytania (1) i (2) są złączeniami dwóch zmiennych tabelarycznych – @x – zawierającej 1 wiersz oraz @t – zawierającej ponad 2000 wierszy.

Dla zapytań (1) i (2) oglądamy dwa różne plany wykonania (ale nie plany estymowane, a plany rzeczywiste) o tym samym koszcie:

Pierwsza obserwacja - w obu przypadkach kolejność zmiennych tabelarycznych w złączeniu była różna (w przypadku zapytania (1) z opcją RECOMPILE kolejność była “prawidłowa” – skanowanie zmiennej zawierającej mniejszą ilość wierszy i wyszukanie rekordów w zmiennej “większej”).

Zobaczmy w planach wykonania szczegóły operatorów użytych w stosunku do zmiennej @t.

W zapytaniu (2) w operatorze Clustered Index Scan liczba przy pozycji Actual Number of Rows (rzeczywista liczba wierszy)  zgadza się z rzeczywistością (2164), zaś w przypadku zapytania (1) używającego opcji RECOMPILE (“rekompiluj zapytanie i nie używaj planu z cache’u”) w operatorze Clustered Index Seek liczba obok Acutal Number of Rows wynosi 1. Skąd w drugim planie wzięła się poprawna informacja o rzeczywistej liczbie wierszy? Odpowiedź przychodzi, gdy spojrzymy na całość planu wykonania. W takich sytuacjach liczba wierszy z tabeli “zewnętrznej” (Outer) jest równa ilości iteracji pętli Nested Loops – 2164 to właśnie ilość “obrotów pętli”.  Nadal jednak estymowana ilość wierszy dla tych zmiennych wynosi 1 (słownie: jeden). A z tego wynika, że – z klauzulą OPTION (RECOMPILE) czy bez niej – optymalizator zakłada, że w zmiennej tabelarycznej jest zawsze 1 rekord. Swego rodzaju ciekawostką są różne plany wykonania obu przedstawionych zapytań, ale równie dobrze może to być przypadek lub bug (dość powtarzalny).

W tym miejscu przyznaję, że gdyby nie Maciek Pilecki, chodząca księga wiedzy o zagadnieniach optymalizacji SQL Servera, nie wpadłbym na oczywistą pomyłkę, którą popełniłem pisząc ten wpis w jego pierwotnej postaci. Co się mianowicie stało? Ano, jakimś cudem pomyliłem plany wykonania obu zapytań i tak się tym zasugerowałem, że pomyślałem, iż klauzula OPTION (RECOMPILE) umożliwia dokładne policzenie rekordów w zmiennej tabelarycznej SQL Serverowi. Oczywiście mój błąd. Dobrze, że Maciek był na posterunku i mogłem dzięki niemu poprawić ten wpis oraz swój krzywy światopogląd, a przy okazji jeszcze czegoś się nauczyć (dzięki, Maćku).

Po lekturze wielu wpisów na wielu blogach (m.in. na blogach Mariusza Kędziory i Maćka Aniserowicza) na temat pracy w branży IT, specjalizacji, planowania kariery i podobnych spraw postanowiłem, że i ja dorzucę do tego zestawu coś od siebie. Okazja nadarza się niezła, jako że w tym roku minęło 10 lat odkąd pracuję w branży IT (mało to i dużo – mało, bo znam osoby, które ponad 10 lat pracują w jednej firmie, a dużo – bo można w tym czasie przejść długą drogę po drabince kompetencji), a poza tym jestem właśnie na etapie zmiany pracodawcy. Być może to, co napiszę wyda Ci się stekiem bzdur, kolekcją banałów, z których nic nie wynika lub zestawem kompletnie bezużytecznych “złotych myśli”. Ale jeśli znajdzie się choć jedna osoba, której moje wynurzenia się przydadzą w życiu, będzie to mój sukces.

Autorytet?

Pierwsze pytanie – kim jestem, że uzurpuję sobie prawo do pisania tego wpisu? Obecnie jestem starszym programistą w dużej korporacji oferującej wachlarz aplikacji wspierających pracę firm. Wcześniej byłem trenerem, administratorem, kierownikiem grupy konsultantów i trenerów. Rekrutowałem ludzi i byłem rekrutowany. Widziałem wiele rozmów kwalifikacyjnych (także w języku angielskim oraz wywiadów telefonicznych), byłem świadkiem licznych wpadek HR i kandydatów na różne stanowiska pracy. Obserwowałem rozwój kilku firm i korporacji, w których dane mi było pracować, poznałem smak prowadzenia własnej działalności gospodarczej oraz uroki pracy dla korporacji. Stąd mój tupet w tym wpisie :-)

Do dzieła!

A zatem, poniżej prezentuję zbiór moich przemyśleń na temat pracy, kariery, rozwoju i wszystkiego tego, co związane z zarabianiem pieniędzy w IT. Podkreślam, że mogą to być wyjątkowo nieobiektywne przemyślenia :-)

Część 1: Co zrobić, by łatwiej było zdobyć pracę?

1. Dbaj o swoje CV. Przede wszystkim zawsze miej aktualne CV, bo nigdy nie wiesz, kiedy będzie potrzebne. Po drugie – dbaj o treść i formę swojego CV. Pokazuj CV znajomym i proś ich o opisanie wrażeń (przede wszystkim czy jest czytelne). Nie wpisuj do niego nieprawdziwych informacji, koloryzowanych kompetencji, a nawet wymyślonych zainteresowań (ale zainteresowania koniecznie wpisz – a może to dzięki nim uda Ci się zdobyć nową pracę). Banał? Było sto razy? To powtórzmy po raz sto pierwszy! CV to często pierwsza informacja o Tobie, jaką ewentualny przyszły pracodawca otrzyma. Niech pierwsze wrażenie będzie jak najlepsze. Wersje polska i angielska zawierające dokładnie tę samą treść to “must have” każdego człowieka pracy.
2. Nie wstydź się swojej pracy i nie mów o niej źle. Ile to razy słuchałem na rozmowach kwalifikacyjnych człowieka, który otwarcie podważał wagę tego, co robił w życiu zawodowym. A może to, co robiłeś jest tym, co najbardziej interesuje Twojego przyszłego pracodawcę? Na rozmowach kwalifikacyjnych celem jest wyciągnięcie na światło dzienne tego, co było w dotychczasowej pracy najlepsze, a nie najgorsze. O złych stronach pracy mów tylko na wyraźne żądanie rozmówcy i nigdy więcej. To zapewnia pozytywny odbiór Twojej osoby.
3. Buduj swoje kontakty. Dasz wiarę, że w życiu da się uniknąć brnięcia przez sito rozmów kwalifikacyjnych i testów? Twoje kontakty z ludźmi mogą mieć wielką siłę sprawczą. Dlatego szanuj kontakty z ludźmi z branży, z którymi się stykasz na gruncie zawodowym, ale i nie tylko (o społecznościach będę jeszcze pisał). Nie pal mostów kończąc z kimś współpracę (rozstanie z pracodawcą nie musi być bolesne i pełne wzajemnej niechęci, o wiele lepiej pozostawać w dobrym kontakcie i rozstawać się w atmosferze zrozumienia – zadbaj, by eks-pracodawca poznał logiczne wyjaśnienie Twojego odejścia). Tu nie obowiązuje powiedzenie mówiące, że “nie wchodzi się dwa razy do tej samej rzeki”. A teraz wyobraź sobie, że dostajesz ofertę na portalu GoldenLine.pl czy LinkedIn.com (a dostaję tam ofert naprawdę sporo). Headhunter podesłał Ci coś, co kompletnie nie pasuje do Twojego profilu zawodowego. Co robisz? Jeżeli puszczasz człowieka kantem i ignorujesz jego wiadomość, wg mnie popełniasz błąd. Zawsze odpowiadam na oferty pracy, jakie dostaję. I zawsze zachowuję kontakt do osoby, która ową ofertę mi podesłała. Czasem zdarza mi się, że podsyłam headhunterowi znajomego, który w mojej ocenie lepiej pasuje do oferty (i być może akurat rozgląda się za nowymi wyzwaniami). Kontakty z “łowcami głów” są nierzadko szansą na ciekawą pracę w przyszłości.
4. Bądź w społecznościach związanych z branżą. Sprawdź, czy nie warto zbudować kontaktów w społecznościach, np. w grupach pasjonackich, w ramach portali tematycznych albo grup dyskusyjnych. Z moich obserwacji wynika, że takie kontakty mogą okazać się niezwykle cenne. Dzięki udziałowi w spotkaniach Polskiej Grupy Użytkowników SQL Server czy dzięki uczestnictwu w dyskusjach na forum portalu WSS.pl niejednokrotnie zdobywałem klientów oraz poznawałem osoby, dzięki którym łatwiej było myśleć o przyszłości i planować rozwój mojej kariery.
5. Specjalizuj się. Czasy, kiedy człowiek w IT był od wszystkiego, minęły bezpowrotnie. Dzisiaj wygląda na to, że o wiele łatwiej jest zdobyć pracę będąc specjalistą w jednej / dwóch technologiach. Ja wybrałem bazy danych, a dotychczasowa kariera w IT skierowała moje zainteresowania w stronę systemu Microsoft SQL Server. Ale nie czuję się specjalistą z zakresu SQL Servera jako produktu, a jedynie wybranych elementów tego systemu. I tak już jest – systemy są coraz bardziej skomplikowane i ogarnięcie nawet pojedynczego “potwora” jest praktycznie niewykonalne. W czym się specjalizować? Jeśli jeszcze nie masz swojej specjalizacji, porozmawiaj o tym ze znajomymi z branży. Ja sam chętnie też doradzam swoim studentom, jeżeli kierują do mnie pytania o to, w czym warto się specjalizować. Idealnie jest wpasować się w jakąś niszę – znaleźć technologię, która ma przyszłość, a jest w niej jeszcze miejsce dla Ciebie. Choć tak naprawdę nawet mając specjalizację uznaną za dość powszechną, możesz nie mieć problemu ze znalezieniem pracy, ponieważ rynek może potrzebować wielu specjalistów z danej dziedziny. Tu złotego środka nie ma.
6. Miej otwarte horyzonty. Nie zamykaj się na technologie. Używasz Windows? Poznaj Linuksa. Pracujesz z bazami Oracle? Zobacz, jak analogiczne rzeczy można robić w PostgreSQL. Oczywiście, chodzi o ogólne pojęcie, co oferują systemy czy technologie inne niż te, z którymi na co dzień przyszło Ci pracować.
7. Bądź na bieżąco. Staraj się śledzić rozwój technologii i systemów. Obserwuj trendy, kierunki rozwoju produktów. To da Ci szansę znalezienia nisz, w których specjalizacja może być przyczynkiem do zdobycia ciekawej pracy. Czytaj aktualności na portalach, blogach, w gazetach. Na przykład, doskonałą okazją do dokonania przeglądu są konferencje typu Microsoft Technology Summit.
8. Rozwijaj się nie tylko technologicznie. Szalenie ważne są dziś tzw. “miękkie umiejętności”. Umiejętność komunikacji z drugim człowiekiem, kultura osobista, zdolności w zakresie prowadzenia negocjacji i przeprowadzania rozmów z klientami są w cenie. Jeżeli tylko masz okazję do pracy na styku Twoja firma – klient, korzystaj z okazji i rozwijaj właśnie te zdolności. Jeśli masz taką możliwość, porozmawiaj z trenerem lub osobą, która w pracy używa takich “miękkich umiejętności”.
9. Wyznaczaj sobie cele. Nie ma nic gorszego niż brak celu w życiu zawodowym. Znam ludzi, którzy już w wieku dwudziestu paru lat wiedzieli, co chcą robić za 5-10 lat. Duży szacunek, jeżeli człowiek myśli o przyszłości i stara się dążyć do czegoś w życiu. Wyznacz sobie wiele celów i dla każdego załóż jakiś czas realizacji. Gdy czas upłynie, spróbuj podsumować, co udało się zrealizować, a co nie (i dlaczego nie!). Przy czym celem nie musi być – “za 5 lat będę project managerem”. To może być coś zupełnie innego – np. “za 5 lat będę miał wyrobioną markę w kraju i 2-3 stałych klientów, dla których będę wykonywał projekty”.
10. Zdobywaj udokumentowane kompetencje. Certyfikaty, referencje, listy polecające, szkolenia. Niezależnie od obiegowych opinii to one są często podstawą do tego, czy masz szansę na zdobycie atrakcyjnej i rozwojowej pracy. Nie lekceważ zwłaszcza potęgi certyfikatów. Mówi się o nich dużo, niekoniecznie dobrze, ale ja już dzisiaj wiem, że na pewnym poziomie w branży IT bez nich po prostu człowiek nie istnieje. Co do referencji – jeśli czujesz, że udało Ci się wykonać kawał dobrej roboty dla klienta, poproś o pisemne potwierdzenie satysfakcji klienta. Ważne zwłaszcza wtedy, gdy klient ma uznaną markę – to może być kiedyś przepustka do “lepszego świata” :-)

Część 2: Jak pracować i nie zwariować?

1. Po pierwsze – pracuj uczciwie. Niech praca nie będzie przykrym obowiązkiem. Pamiętaj, że zazwyczaj pracujesz za takie pieniądze, jakie samemu udało Ci się wynegocjować. Nie narzekaj więc, że “słabo płacą”, tylko rób swoje. Pokaż, że z Twojej strony wszystko jest w porządku, że swoje obowiązki wypełniasz z właściwym profesjonalizmem. Nie dostosowuj się poziomem do innych. Pracownik wypełniający sumiennie swoje obowiązki jest cenniejszy od znudzonego “wielkiego eksperta”, który już na nic w pracy nie ma ochoty i buduje w firmie atmosferę błogiego lenistwa. Takiego marazmu nie znoszę, bo bywa zaraźliwy :-)
2. Nie patrz innym na ręce. Jeżeli nie jesteś zwierzchnikiem współpracownika, nie staraj się odgrywać tej roli. Widząc, że ktoś popełnia błąd, zwróć mu uwagę, pomóż. Ale nie besztaj, bo błędów nie popełnia tylko ten, który nic nie robi. Ponadto, to, że inny pracownik firmy nie wykonuje właściwie swoich obowiązków, nie upoważnia Cię do dorównywania mu w niedbalstwie czy nieróbstwie. Inni nie robią dokumentacji swoich modułów kodu? Jeżeli nie ma przeciwwskazań, Ty dokumentuj swój kod. Powtórzę raz jeszcze – bądź dobrym przykładem dla innych.
3. Ucz się. W każdej firmie zgromadzona jest wiedza. Rzadko kiedy udokumentowana, ale ona tam jest. Znajdź w firmie ludzi, od których możesz się uczyć i korzystaj w miarę możliwości z ich doświadczenia. Miałem w życiu to szczęście, że pracowałem w każdej firmie z ludźmi, rzekłbym, wybitnymi. I to jest bezcenne. Zbieraj doświadczenia, rób podsumowanie po każdym projekcie (a nawet po każdej fazie projektu). W miarę możliwości spisuj wiedzę (notes, baza wiedzy, blog – każdy sposób jest dobry). Staraj się uczyć głównie rzeczy przydatnych w codziennej pracy.
4. Bądź otwarty na pomysły współpracowników. Nawet, jeśli jesteś już w pozycji przełożonego i zarządzasz zespołem, słuchaj swoich współpracowników. Oni naprawdę też mogą mieć dobre pomysły. Znam sytuacje, kiedy bardzo silne zespoły rozpadały się, a pracownicy odchodzili z firmy, ponieważ lider zespołu narzucał swój – niekoniecznie słuszny – punkt widzenia.
5. Nigdy nie przestawaj ulepszać swojego modelu pracy. Zawsze możesz coś poprawić w pracy swojej czy swojego zespołu. Staraj się myśleć konstruktywnie o tym, co można poprawić w modelu pracy. Zapoznaj się z metodykami prowadzenia projektów, tam często są gotowe rozwiązania, które mogą sprawić, że praca będzie przyjemniejsza, bardziej zorganizowana, zorientowana na współpracę grupy ludzi. Wprowadzaj w swojej pracy uznane powszechnie dobre praktyki (tu chodzi o dowolne “best practices” – zarówno w odniesieniu do technologii, jak i pracy zespołowej).
6. Automatyzuj. To szczególnie dotyczy pracy w IT. Staraj się wyeliminować ze swojego dnia pracy czynności, które może za Ciebie wykonywać automat. Jeżeli widzisz, że codziennie robisz powtarzalną czynność inną niż przeglądanie logów czy czytanie dokumentacji, być może dałoby się coś zautomatyzować. To nie jest lenistwo, to pragmatyzm. Mniej powtarzalnych czynności = mniejsze znużenie pracą + więcej czasu na inne czynności (np. na naukę).
7. Miej zdrowe priorytety. Praca jest ważna. Ale ważniejsze są zdrowie czy rodzina. Zadbaj o to, by praca wpasowała się w Twoje życie bezboleśnie. Jeżeli czujesz, że praca psuje Twoje relacje z bliskimi lub rujnuje Ci zdrowie (stres, nadgodziny), pomyśl, co można z tym zrobić. Nie pracuj ponad swoje możliwości fizyczne (pracujesz po 12-14 godzin dziennie? ten punkt napisałem specjalnie dla Ciebie – opamiętaj się!).
8. Szanuj swój czas i swoją pracę. Wiele się mówi o tym, że ludzie z IT bywają niedoceniani, a przez to słabo opłacani. Ale spróbuj odpowiedzieć sobie na pytanie – co robię, by pracodawca miał podstawy do płacenia mi pieniędzy zgodnych z moimi oczekiwaniami? Jeśli Ty nie jesteś w stanie logicznie uzasadnić swoich oczekiwań, nikt nie będzie w stanie tego zrobić za Ciebie. Ceń siebie i swoją pracę, nie pracuj za darmo (nadgodziny, weekendy). I ważna rzecz – na rozmowach o pracę zazwyczaj wypada podawać oczekiwaną kwotę nieco zawyżoną, ponieważ najprawdopodobniej Twoi rozmówcy będą negocjować w dół :-)
9. Okazuj ludziom szacunek. Najbliższym współpracownikom, starszym pracownikom, podwładnym i przełożonym. Pamiętaj, że kiedyś ktoś może okazać brak szacunku wobec Ciebie. Traktuj ludzi tak, jak chcesz, by Ciebie traktowano. To naprawdę działa w dwie strony.
10. Spróbuj odnaleźć swoje miejsce w firmie czy w zespole. Zawsze jest coś, co robisz lepiej od swoich koleżanek i kolegów. Staraj się, by w tym względzie Twój wkład w firmie był widoczny. Jeżeli to możliwe, dziel się wiedzą. Wiem, że czasem może to spowodować, że poczujesz zagrożenie Twojej pozycji w firmie/zespole. Ale dzielenie się wiedzą może dać Ci wielką satysfakcję, wdzięczność współpracowników i szanse na to, że ktoś inny podzieli się z Tobą swoją wiedzą.

Veni, vidi, vici

Jeżeli udało Ci się dotrzeć aż tutaj, to znaczy, że dałem radę i udało mi się nie zepsuć tego wpisu :-) Oczywiście, to nie wszystkie moje przemyślenia. Niektóre zatrzymuję dla siebie ;-) Te, które wymieniłem, zawierają zasady, jakimi kieruję się ja. I jak dotąd wydaje mi się, że te zasady działają na moją korzyść. To dzięki nim jestem dzisiaj w tym miejscu mojej kariery. W miejscu, w którym – przynajmniej na razie – nie martwię się o swoją przyszłość i widzę swoje ścieżki rozwoju. Robię to, co lubię, daje mi to satysfakcję i pieniądze, mam czas dla rodziny i dla siebie, a i ludzie, którzy mają okazję ze mną pracować na moje oko są z tej współpracy zadowoleni. I o to w tym wszystkim chyba chodzi :-)

PS. Czekam na komentarze i przemyślenia. Tylko dyskutując możemy się czegoś od siebie nauczyć!

Do tego potrzebny mi jest feedback czytelników mojego bloga. A zatem…

1) Po prawej stronie znajduje się ankieta – prosta, by nie rzec banalna. Chodzi o układ strony głównej bloga. Czy lepiej się go czyta, gdy na stronie głównej są tylko “zajawki” wpisów, czy może jednak lepiej było, gdy wyświetlała się pełna treść wpisów? Bardzo proszę o pomoc w tym temacie. To dla Waszej wygody! Ankieta będzie aktywna do końca sierpnia.

2) Tematy. Ja mogę jeszcze długo i namiętnie smarować o SQL Serverze ;-) Ale może są jakieś tematy, o których poczytałbyś / poczytałabyś chętniej niż o innych? Jeżeli tak jest, daj znać – czy to w komentarzu do tego wpisu, czy na adres mailowy podany na stronie o autorze bloga.

Przypinam ten wpis, żeby wisiał póki co na wierzchu :-) I czekam na Twój feedback. Dla najbardziej kreatywnych i pomocnych – “ustne gratulacje ojca prowadzącego” :-)