Witaj na Zine.net online Zaloguj się | Rejestracja | Pomoc

[PL] CTE – fajne to jest, ale…

Common Table Expression (CTE) to bardzo przydatna składnia w SQL Server 2005/2008. Podzapytanie na sterydach... Pewnie i prawda. A jak ładnie działa rekursywnie! Hmmm, działa, a czasem i nie działa :-)

Czy próbowaliście kiedyś opakować rekurencyjne CTE w widok albo funkcję tabelaryczną? Udać się pewnie i uda, ale tylko pod warunkiem, że rekurencja nie wyjdzie ponad 100 iteracji. A wszystko dlatego, że nie można użyć w widokach i funkcjach klauzuli OPTION. Czyli takie coś nie pójdzie:

CREATE VIEW dbo.VV
AS
WITH CTE AS
(
SELECT 1 AS a
UNION ALL
SELECT (a + 1)
FROM CTE
WHERE a <= 180
)
SELECT * FROM CTE
OPTION (MAXRECURSION 180)
GO

Msg 156, Level 15, State 1, Procedure VV, Line 12
Incorrect syntax near the keyword 'OPTION'.

Dlaczego? BOL mówi, że:

The SELECT clauses in a view definition cannot include the following:
...
- The OPTION clause
...

I wszystko w zasadzie jasne :-) Czyli że się nie da i tyle. Fajnie. To co nam z takiego CTE rekurencyjnego, co nie może ponad 100 iteracji wykonać? W zasadzie niewiele nam...

Jeżeli komuś oprócz mnie jeszcze to przeszkadza, to spieszę powiadomić, że Steve Kass (SQL Server MVP) zgłosił tę niedoróbkę w 2005 roku (sic!): https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124653.

A przy okazji, po porannej konwersacji i wymianie inwektyw na temat powyższego buga z kolegą z firmy (credits to Michał Lasota) znaleźliśmy pseudo-workaround dla problemu. Ale uwaga – działa tylko wtedy, gdy znamy maksymalną liczbę iteracji / maksymalną wartość wyjściową dla iteracji. Wystarczy wówczas podzielić iteracje na porcje po 100 (maksymalna liczba iteracji dla CTE nie używającego MAXRECURSION):

CREATE VIEW dbo.VV
AS
WITH CTE1 AS
(
SELECT 1 AS a
UNION ALL
SELECT (a + 1)
FROM CTE1
WHERE a <= 100
), CTE2 AS
(
SELECT MAX(a) AS a
FROM CTE1
UNION ALL
SELECT (a + 1)
FROM CTE2
WHERE a <= 200
), CTE3 AS
(
SELECT MAX(a) AS a
FROM CTE2
UNION ALL
SELECT (a + 1)
FROM CTE3
WHERE a < 260 -- maks. wartość to 260
)
SELECT * FROM CTE1
UNION
SELECT * FROM CTE2
UNION
SELECT * FROM CTE3
GO

Brzydkie i do zastosowania dla ograniczonej klasy przypadków.

Panowie i Panie z MS, czekamy na cud (bo jak inaczej nazwać reakcję na buga zgłoszonego 4 lata temu?).

opublikowano przez brejk | 5 komentarzy
Filed under: , ,

[PL] 37. spotkanie PLSSUG Warszawa

37spotkanie W imieniu swoim oraz prelegentów mam przyjemność zaprosić na 37. spotkanie warszawskiego oddziału Polskiej Grupy Użytkowników SQL Server (PLSSUG). Spotkanie rozpocznie się w czwartek, 6 sierpnia, o godzinie 18:00 w siedzibie firmy ABC Data Centrum Edukacyjne w Warszawie (ul. Zelna 39, 1. piętro).

Wstęp na spotkanie wolny.

Prelegentem na spotkaniu będzie Michał Krużel, programista pracujący dla Asseco Business Solutions S.A. (a więc mój kolega z firmy), zawodnik ze ścisłego TOP 10 konkursu "Co to za query". Michał przedstawi wybrane, ocenione przez niego jako bardzo przydatne, elementy języka T-SQL. I tak będzie trochę o CTE, trochę o PIVOTach, a trochę o hintach. Nie zabraknie też kursorów :-)

Po prezentacji Michała – godzinna dyskusja o pokazanych składniach i ich zastosowaniach (tu prośba – jeżeli masz ciekawe zastosowanie dla omawianych elementów T-SQL, nie wahaj się nam o tym powiedzieć!).

Szczegółowa agenda 37. spotkania PLSSUG Warszawa

Nieobowiązkowa rejestracja na 37. spotkanie PLSSUG Warszawa

Zapraszam w imieniu Polskiej Grupy Użytkowników SQL Server!

opublikowano przez brejk | 0 komentarzy

[PL] Nowi polscy MVP, renominacje i takie tam

Dzień 1 lipca kojarzy się niektórym z początkiem wakacji. Dla społeczności MS to dzień, w którym ogłaszane są nominacje i renominacje do tytułu Microsoft Most Valuable Professional (MVP). Tym razem 1 lipca okazał się szczęśliwy dla czterech dobrze znanych pasjonatów.

  1. Marek Adamczuk (kategoria: SQL Server) - zacznę od osoby mi najbliższej z całej czwórki. Marek to specjalista, jakich mało. SQL Server zna na wylot, a że do tego ma znakomity zmysł architekta, jest niezrównany w rozwiązywaniu wszelkich problemów bazodanowych. Mam wielkie szczęście mieć tego człowieka w pokoju w pracy :-) I korzystam codziennie z jego wiedzy i doświadczenia. Nie ukrywam, że osobiście wysłałem do MS kandydaturę Marka do tytułu MVP i mam wielką satysfakcję z powodu jego nominacji. Nie można też pominąć faktu, że Marek jest kolejnym bloggerem Zine, który został MVP. To chyba nie przypadek ;-)
  2. Ziemek Borowski (kategoria: Exchange Server) – członek tylu grup pasjonackich, że chyba nikt nie ma tak szerokich zainteresowań. Na WSS.pl praktycznie od zawsze. I od dawna wszyscy zadawali sobie pytanie, kiedy Ziemek będzie MVP. Ziemek – jesteś MVP i zdecydowanie Ci się ta nagroda należała. Liczę, że nadal będziesz miał tyle siły, by być jednym z filarów społeczności MS w Polsce. I nadal opiekować się hostingiem Zine ;-)
  3. Jacek Doktór (kategoria: System Center Configuration Manager) – człowiek orkiestra w tematach System Center. MCT. W kwestii zakresu zainteresowań w zasadzie to taki drugi Ziemek, tylko trochę inny (a może to Ziemek jest takim trochę innym Jackiem?) :-) Angażuje się w praktycznie każde przedsięwzięcie ze świata IT Pro skupionego wokół technologii MS.
  4. Paweł Pławiak (Directory Services) – założyciel grupy Windowsowej w Warszawie (w zespole z Łukaszem Foksem – zapewne przyszłym MVP). Doktor i MCT w jednym. Szef trenerów w firmie Compendium. Wie wszystko o certyfikacji MS.

O aktywnościach wymienionych Panów można poczytać tutaj: http://www.microsoft.com/poland/communities/mvp/20090701.aspx. Ja ze swojej strony mogę tylko napisać: Panowie, kawał dobrej roboty robicie i z pewnością jeszcze będziecie robić.

Jednocześnie parę osób zostało renominowanych (nominowanych na następny rok):

Marcin Szeliga (SQL Server)
Grzegorz Tworek (Enterprise Security)
Kazimierz Kuta (Visual C#)
Wiktor Zychla (Visual C#)
Karol Stilger (Setup & Deployment)

Serdecznie gratuluję wszystkim renominowanym.

Także i ja otrzymałem e-mail przyjemnej treści:

Dear Pawel Potasinski,
Congratulations! We are pleased to present you with the 2009 Microsoft® MVP Award! This award is given to exceptional technical community leaders who actively share their high quality, real world expertise with others. We appreciate your outstanding contributions in SQL Server technical communities during the past year.

Cieszę się, że MS docenia to co robimy dla społeczności. To pokrzepia i dodaje chęci do dalszych działań. Moc jest z nami i miejmy nadzieję, że będzie jeszcze przez długi czas :-)

opublikowano przez brejk | 6 komentarzy
Filed under:

[PL] MTS 2009 Speaker Idol – powód nr 6

MTS2009_BANER_137_200_SIPisałem już o konkursie Speaker Idol (SI) organizowanym przy okazji konferencji MTS 2009. Wymieniłem 5 powodów, dla których warto wziąć udział w tym konkursie. Ale ostatnio pojawił się powód nr 6. A mianowicie…

… prowadzącym konkurs będzie… TADA!!! Paweł Potasiński :-) Tak, tak, poprowadzę SI w tym roku na MTS :-) I mało tego, dam z siebie wszystko! Zapowiada się całkiem ciekawie. Nigdy nie prowadziłem takiego konkursu, ale widziałem sporo SI na wszelkiej maści Techedach i wydaje mi się, że dam radę :-) I tak będę miał mniejszy stres niż ci, co wezmą udział w samym konkursie :-)

A zatem, zgłaszajcie swoje prezentacje! Komitet organizacyjny zastrzegł, że obejrzy zaledwie 30 pierwszych nadesłanych prezentacji, z czego 10 wejdzie do konkursu. Potem już tylko półfinał (pierwszy dzień konferencji), finał (5 zawodników drugiego dnia konferencji) i mamy nowego prelegenta na MTS 2010!

Stay tuned, będę na bieżąco informował o rozwoju sytuacji (m.in. o składzie Jury).

opublikowano przez brejk | 0 komentarzy
Filed under: , ,

[PL] 36. spotkanie PLSSUG Warszawa

36spotkanie

 

W imieniu swoim oraz prelegentów mam przyjemność zaprosić na 36. spotkanie warszawskiego oddziału Polskiej Grupy Użytkowników SQL Server (PLSSUG). Spotkanie rozpocznie się 2 lipca o godzinie 18:00 w siedzibie firmy ABC Data Centrum Edukacyjne w Warszawie (ul. Zelna 39, 1. piętro). Czas trwania spotkania: mniej więcej dwie godziny, a potem... O tym za chwilę :-)

Wstęp na spotkanie wolny.

Prelegentem na spotkaniu będzie Radek Kępa, wieloletni pracownik firmy Microsoft, zaprawiony w bojach DBA, specjalista od zagadnień HA, replikacji i szeroko pojętej administracji bazami danych. Radek pokaże w użyciu dwa darmowe narzędzia diagnostyczne mogące ułatwić pracę administratora: SQLDiag i Nexus.

Po spotkaniu, około godz. 20:00, zapraszamy do brazylijskiej restauracji Churrasco przy ul. Próżnej 8 na część nieoficjalną spotkania. Przy znakomitej południowo-amerykańskiej kuchni i trunkach będzie okazja do rozmów niekoniecznie o SQL Serverze ;-)

*** Ważne. Osoby, które wybierają się na część nieoficjalną spotkania, proszę o kontakt mailowy na adres pawelp [ at ] plssug.org.pl. ***

*** Równie ważne. W restauracji każdy płaci za siebie (acz gwarantuję, że ceny nie są dla miliarderów) ***

Szczegółowa agenda 36. spotkania PLSSUG Warszawa

Nieobowiązkowa rejestracja na 36. spotkanie PLSSUG Warszawa

Zapraszam w imieniu Polskiej Grupy Użytkowników SQL Server!

Więcej o PLSSUG można przeczytać na witrynie grupy: http://plssug.org.pl.

W razie pytań proszę o kontakt na adres pawelp [ at ] plssug.org.pl.

opublikowano przez brejk | 0 komentarzy

[PL] MTS 2009 Speaker Idol – 5 powodów dlaczego warto wystartować

Na witrynie MTS 2009 właśnie ogłoszono rozpoczęcie konkursu Speaker Idol. Konkurs polega w pierwszej kolejności na nagraniu 5-minutowej prezentacji i wysłaniu jej do organizatorów konferencji. Finał konkursu odbędzie się już na MTS w obecności setek uczestników konferencji.

MTS2009_BANER_480_90_SI

Dlaczego warto w ogóle zawracać sobie głowę tym konkursem?

  1. Zwycięzca ma zapewnioną możliwość poprowadzenia własnej prezentacji na MTS 2010.
  2. Trzech finalistów otrzymuje ciekawe nagrody.
  3. Pokonujesz stres, własne słabości i udowadniasz sobie, że i Ty możesz być prelegentem :-)
  4. Z miejsca stajesz się rozpoznawalny(-a).
  5. To świetna zabawa, nawet jeśli nie uda Ci się wygrać czy dotrzeć do finału konkursu.

Jaka powinna być prezentacja w takim konkursie?

  • Wyrazista – to musi być coś, co zapadnie ludziom w pamięć.
  • Dynamiczna – nie można stanąć na scenie i wyrecytować swoją kwestię.
  • Silna merytorycznie – nawet, jeśli prezentacja jest na poziomie 100 musi pokazywać coś interesującego w sposób nie pozostawiający wątpliwości, że prelegent na dany temat wie "wszystko".
  • Z humorem, ale bez kabaretu – to nie jest konkurs na komedianta roku, więc nie ma co przesadzać z humorem, acz inteligentna wstawka humorystyczna może być plusem prezentacji.

W tym roku obiecałem sobie, że obejrzę przynajmniej finał konkursu. Zapewne będzie ubaw po pachy i walka do upadłego w Wielkim Finale :-) Do zobaczenia zatem i trzymam kciuki za wszystkich śmiałków, którzy wezmą udział w tegorocznym Speaker Idolu.

opublikowano przez brejk | 9 komentarzy
Filed under: ,

[PL] Impresje na temat XML w SQL Server

Ostatnimi czasy naobiecywałem paru osobom, że coś napiszę na temat XML-a w SQL Server. Ponieważ ostatnio jakoś ten temat powraca do mnie jak bumerang, postanowiłem sklecić coś korzystając z długiego weekendu (mniam!).

Impresja nr 1 – Zagnieżdżenia

Zacznę od czegoś w miarę prostego (acz nie do końca banalnego). Często zdarza się, że muszę w T-SQL wydobywać dane z dokumentów XML. Sprawa jest prosta, jeśli a) znam strukturę dokumentu, b) struktura dokumentu jest w miarę "płaska" (tzn. elementy zawierające dane znajdują się na jednym poziomie hierarchii dokumentu XML). Punktem a) zajmę się w impresji nr 2. A punkt b)?

Przyjrzyjmy się dokumentowi:

DECLARE @doc xml;
SET @doc = '<orders>
<order orderid="1">
<orderdate>20090610</orderdate>
<orderdetails>
<orderetail>
<product>Mac</product>
<unitprice>10.00</unitprice>
<quantity>1</quantity>
</orderdetail>
<orderetail>
<product>PC</product>
<unitprice>10.00</unitprice>
<quantity>1</quantity>
</orderdetail>
</orderdetails>
</order>
<order orderid="2">
<orderdate>20090610</orderdate>
<orderdetails>
<orderetail>
<product>Mac</product>
<unitprice>10.00</unitprice>
<quantity>3</quantity>
</orderdetail>
<orderetail>
<product>PC</product>
<unitprice>10.00</unitprice>
<quantity>5</quantity>
</orderdetail>
</orderdetails>
</order>
</orders>'
;

Ot, dane zamówień. Jak wydobyć z takiego dokumentu wszystkie dane i zrobić z tego na początek tabelę zdenormalizowaną? Wiadomo, że przejście od XML-a do tabeli od SQL Server 2005 można zrealizować za pomocą metod typu danych XML (w szczególności za pomocą metody nodes()). I w takim, zagnieżdżonym przypadku, też można poradzić sobie w ten sam sposób. Tyle, że metody nodes() trzeba użyć wielokrotnie. Na początek wydobywamy ogólne dane zamówienia, a potem dane szczegółów zamówień.

SELECT 
orders_detailed.orderid,
orders_detailed.orderdate,
order_details.order_detail.value('product[1]', 'varchar(20)') AS product,
order_details.order_detail.value('unitprice[1]', 'varchar(20)') AS unitprice,
order_details.order_detail.value('quantity[1]', 'varchar(20)') AS quantity
FROM (
SELECT
orders.single_order.value('@orderid', 'int') AS orderid,
orders.single_order.value('orderdate[1]', 'smalldatetime') AS orderdate,
orders.single_order.query('orderdetails') AS order_details
FROM @doc.nodes('/orders/order') AS orders(single_order)
) AS orders_detailed
CROSS APPLY orders_detailed.order_details.nodes('/orderdetails/orderdetail') AS order_details(order_detail)

Wynik:

orderid orderdate           product unitprice quantity
------- ------------------- ------- --------- --------
1       2009-06-10 00:00:00 Mac     10.00     1
1       2009-06-10 00:00:00 PC      10.00     1
2       2009-06-10 00:00:00 Mac     10.00     3
2       2009-06-10 00:00:00 PC      10.00     5

Impresja nr 2 – Parsowanie XML-a

Co jednak, jeśli postawimy przed sobą trudniejsze zadanie? Na przykład: nie znając struktury dokumentu XML zróbmy z niego tabelę. To już wymaga nie lada wysiłku. Zastanawiałem się wiele razy, jak do tego podejść. Niezłym rozwiązaniem wydało mi się przerobienie dokumentu XML na tabelę zawierającą informacje o węzłach dokumentu. Spróbuję zaprezentować to na przykładzie. Mamy dokument:

DECLARE @doc xml;
SET @doc = '<employees>
<employee empid="1">
<firstname>Pawel</firstname>
<lastname>Potasinski</lastname>
</employee>
<employee empid="2">
<firstname>Jan</firstname>
<lastname>Kowalski</lastname>
</employee>
</employees>'

Spróbuję za pomocą kodu T-SQL zbudować tabelę pokazującą hierarchię tego dokumentu. Ale jednocześnie zapytanie będzie na tyle uniwersalne, że poradzi sobie z innym dokumentem XML (nie gwarantuję, że z każdym, bo pewnie tak nie jest).

;WITH CTE AS (
SELECT
Q.a.value('local-name(.)', 'varchar(100)') AS node,
CASE WHEN Q.a.exist('child::*') = 0
THEN Q.a.value('text()[1]', 'varchar(8000)') ELSE Q.a.query('child::*') END AS children_or_text,
CASE WHEN Q.a.exist('child::*') = 1 THEN 1 ELSE 0 END AS has_children,
0 AS level,
CAST('/' AS varchar(8000)) AS Path,
1 AS is_element
FROM @doc.nodes('//*[count(parent::*) = 0]') AS Q(a)
UNION ALL
SELECT
Q.a.value('local-name(.)', 'varchar(100)'),
CASE WHEN Q.a.exist('child::*') = 0
THEN Q.a.value('text()[1]', 'varchar(8000)') ELSE Q.a.query('child::*') END,
CASE WHEN Q.a.exist('child::*') = 1 THEN 1 ELSE 0 END,
level + 1,
CAST(Path + CONVERT(varchar(20), ROW_NUMBER() OVER (ORDER BY Q.a)) + '/' AS varchar(8000)),
1
FROM CTE
CROSS APPLY CTE.children_or_text.nodes('//*[count(parent::*) = 0]') AS Q(a)
UNION ALL
SELECT
Q.a.value('local-name(.)', 'varchar(100)'),
Q.a.value('.', 'varchar(8000)'),
0,
level + 1,
CAST(Path + CONVERT(varchar(20), ROW_NUMBER() OVER (ORDER BY Q.a)) + '/' AS varchar(8000)),
0
FROM CTE
CROSS APPLY CTE.children_or_text.nodes('//*[count(parent::*) = 0]/@*') AS Q(a)
)
SELECT *
FROM CTE
ORDER BY Path
OPTION (MAXRECURSION 0)

Wynik zapytania:

image

Mała legenda:

  • node - nazwa elementu lub atrybutu,
  • children_or_text - zawartość elementu lub atrybutu (elementy "dzieci" lub tekst),
  • has_children - czy węzeł ma "dzieci" (elementy zagnieżdżone),
  • level – poziom zagnieżdżenia węzła w dokumencie XML,
  • Path – ścieżka węzła (idealna do rzutowania na hierarchyid),
  • is_element – czy element (jeśli 0, to węzeł jest atrybutem).

Samo zapytanie to CTE rekurencyjne. W pierwszej iteracji CTE wybiera elementy nie mające "rodzica" (tu użyłem wyrażenia XPath - //*[count(parent::*) = 0]). W następnych iteracjach wybierane są elementy z kolejnych poziomów zagnieżdżeń, a także atrybuty. CTE kończy iteracje po dojściu do najbardziej zagnieżdżonego elementu. Kluczem do "zwycięstwa" jest użycie metody local-name (funkcja ze specyfikacji XQuery). Metoda ta umożliwia wydobycie z dokumentu XML nazw elementów i atrybutów.

Ok, ale teraz pytanie, jak z tego zrobić tabelę np. takiej postaci:

empid firstname lastname
----- --------- --------
1     Pawel     Potasinski
2     Jan       Kowalski

Odpowiedź: PIVOT :-) A w zasadzie - dynamiczny PIVOT. Proponuję się pobawić samemu – można użyć np. procedury pivot_sp autorstwa Erlanda Sommarskoga (SQL Server MVP). Wystarczy odfiltrować zbędne wiersze (takie, gdzie has_children  = 1) i skupić się na kolumnach node oraz children_or_text.

Impresja nr 3 – Operacje na napisach

Zaskakujące, w jakich momentach przydaje się typ danych XML w SQL Server. Jednym z takich zastosowań są operacje na napisach – konkatenacja oraz rozdzielanie wartości oddzielonych wybranym separatorem.

Konkatenację realizuje się używając klauzuli FOR XML PATH:

SELECT DISTINCT
collation_name,
STUFF(
(
SELECT ', ' + name
FROM sys.databases
WHERE collation_name = d.collation_name
ORDER BY name
FOR XML PATH('')
),1,2,'') AS databases
FROM sys.databases AS d

Powyższe zapytanie pokaże listę collation użytych w bazach danych oraz – w drugiej kolumnie – listę baz, w których dane collation jest używane, np.

collation_name              databases
--------------------------- --------------------------------------
Latin1_General_CI_AS_KS_WS  ReportServer, ReportServerTempDB
Polish_CI_AS                master, model, msdb, Northwind, tempdb

Dodanie literału (tu – przecinek i spacja) eliminuje zbędny element XML w podzapytaniu, a pusty napis w klauzuli FOR XML PATH powoduje, że nie pojawia się element pochodzący od nazwy tabeli. Dzięki temu powstaje odpowiedni łańcuch tekstowy. Funkcja STUFF pozwala w prosty sposób pozbyć się pierwszego przecinka i spacji (są zbędne).

O ile użycie typu danych XML do wykonywania konkatenacji jest dość znane (można je znaleźć choćby w książce Itzika Ben-Gana "Inside SQL Server 2005 Programming" w kodzie procedury do tworzenia dynamicznego PIVOT-a), to już wykorzystanie tego typu danych do rozdzielania wielu wartości względem wybranego separatora (tzw. split) jest wg mnie naprawdę nieszablonowym rozwiązaniem. Zobaczmy taką funkcję:

 

IF OBJECT_ID('dbo.ufn_Split') IS NOT NULL
DROP FUNCTION dbo.ufn_Split;
GO
CREATE FUNCTION dbo.ufn_Split(@Input nvarchar(max), @Separator nchar(1))
RETURNS TABLE
AS
RETURN (
WITH CTE AS
(
SELECT
CAST(
N'<Value>' +
REPLACE(@Input, @Separator, N'</Value><Value>') +
N'</Value>' AS XML
) AS Items
)
SELECT
Split.a.value('.', 'nvarchar(max)') AS Item
FROM CTE
CROSS APPLY Items.nodes('/Value') Split(a)
);
GO

-- Przykład użycia
SELECT * FROM dbo.ufn_Split('1,2,3,4',',')

Ciekawe, prawda? To rozwiązanie podejrzałem kiedyś na Experts Exchange. Pewnie, można się przyczepić, że rozwiązanie bazuje na założeniu, że w podanym na wejściu łańcuchu nie wystąpi napis "<Value>" lub "</Value>", ale to chyba niewielki problem (nazwę elementu XML można zmienić / skomplikować). Od razu zaznaczam, że nie testowałem też, jak powyższe rozwiązanie wypada wydajnościowo przy odpowiedniku z użyciem pętli WHILE lub z wykorzystaniem CLR.

Suma sumarum

Jak widać, XML-em w systemie SQL Server można się fajnie "bawić". Znajomość typu danych XML oraz jego metod niejednokrotnie daje szansę na stworzenie ciekawego rozwiązania dla problemu. Następnym razem, gdy będę pisał o XML-u w SQL Server, zapewne poruszę temat indeksów XML. Ale to już zapewne po sezonie urlopowym ;-)

opublikowano przez brejk | 9 komentarzy
Filed under: ,

[PL] MTS 2009 – pierwsza zajawka

mts2009_blog_btnl_3o_180_200[1]

Ha! Wiedziałem, że tak będzie! Kimberly L. Tripp i jej mąż Paul S. Randal będą prelegentami na Microsoft Technology Summit 2009! Nareszcie, chciałoby się rzec.

Właśnie wystartowała witryna konferencji. Tym razem konferencja odbędzie się we wrześniu (29-30 września). Miejsce bez zmian – PKiN w Warszawie. Póki co, rejestracja kosztuje 450 zł. No nic, ja poczekam, może mi się poszczęści i znów będę miał zaszczyt wystąpić tam w roli prelegenta ;-)

W każdym razie będę prawie na pewno na MTS 2009 :-)

opublikowano przez brejk | 6 komentarzy
Filed under:

[PL] Konkurs "Co to za query" - finał

Voila! Szczęśliwie dotarliśmy do końca konkursu "Co to za query". To była przednia zabawa, przyznaję. Układanie zadań było dla mnie sporą frajdą i wyzwaniem zarazem. Wy – uczestnicy, spisaliście się na medal. Tak, wiem, że trzeci etap konkursu pewnie był najłatwiejszy, ale sam fakt, że większość zawodników ugrała w nim 70 punktów (max), mówi chyba coś więcej. Po prostu wpadliście we właściwy rytm i sądzę, że każdy następny etap byłby dla większości z Was prosty ;-)

Chylę czoła przed wszystkimi uczestnikami konkursu. Każdy z Was ma u mnie "szacun += 10" :-)

TOP 10

Pora na listę najlepszych dziesięciu zawodników. Gratuluję każdemu z osobna. Dobra robota, Panowie!

Miejsce Nr uczestnika Imię i nazwisko Punkty Czas (dni) *
1 5 Tomek Przeliorz 210 4,25
2 3 Marek Powichrowski 210 4,50
3 12 Jakub Plusczok 190 11,62
4 2 Michał Krużel 185 1,32
5 13 Krzysztof Bąk 185 17,61
6 16 Artur Stanaszek 185 20,78
7 10 Leonard Górowski 175 17,52
8 11 Marek Konitz 170 15,56
9 4 Paweł Baranowski 165 10,31
10 7 Ryszard Adamczyk 150 8,16

* Czas w dniach będący różnicą między danym uczestnikiem a uczestnikiem, który najszybciej nadesłał odpowiedzi do etapu

Odpowiedzi do zadań z 1. etapu

Zadanie 1 - "Podatki w US a w Polsce"

Opis: Które stawki VAT z tych znanych w Polsce, są w US :-) Co ciekawe, wynik nie jest w procentach :D

Kod:

SELECT DISTINCT TaxRate
FROM Sales.SalesTaxRate
WHERE TaxRate IN (0, 7, 22)

Komentarz:

W zasadzie jedyny błąd, jaki można było zrobić, polegał na pominięciu słowa DISTINCT. Na tym poległa jedna osoba. Cała reszta poradziła sobie. Jeden zawodnik ominął DISTINCT, ale nadrobił GROUP BY (też jest ok) :-) W planach zauważalna jest cicha i bezśladowa konwersja wartości 0, 7 i 22 na typ smallmoney (takiego typu jest kolumna TaxRate).

Zadanie 2 - "Your Vista Has Expired"

Opis: Numery i daty wygasania kart Vista (?).

Kod:

SELECT 
CardNumber,
CAST(ExpYear AS char(4)) + '-' +
RIGHT('00' + CAST(ExpMonth AS varchar(2)),2) AS Expiration
FROM Sales.CreditCard
WHERE CardType = 'Vista'

Komentarz: Proste zapytanie, w którym chciałem pokazać dwie rzeczy – zastępowalność CAST funkcją CONVERT oraz cichą i bezszelestną konwersję literału 'Vista' do typu nvarchar. Nie było chyba osoby, która by zawaliła to zadanie.

Zadanie 3 - "Size and color matter"

Opis: Ilość produktów wg kolorów i rozmiarów.

Kod:

SELECT 
COALESCE(Color, '<Unknown>') AS Color,
COALESCE(Size, '<Unknown>') AS Size,
COUNT(*) AS Cnt
FROM Production.Product
GROUP BY GROUPING SETS ((Color, Size), (Color))
ORDER BY Color, Size

Komentarz: Dałem tu za dużą dowolność :-) Miało być pokazanie nowej funkcjonalności GROUPING SETS, a wyszło, że można było użyć operatorów ROLLUP lub CUBE i też plan był identyczny. Jeden zawodnik użył GROUPING SETS, za co mu chwała, bo przy dodaniu jeszcze jednej kolumny do listy kolumn wybieranych raczej już nie dałoby się uniknąć użycia tej właśnie nowinki z SQL Server 2008. Nie mieliście z tym zadaniem problemów.

Zadanie 4 - "Ostatnie zamówienia"

Opis: Dwa ostatnio dokonane zamówienia dla każdego klienta.

Kod:

;WITH CTE AS (
SELECT
CustomerID,
SalesOrderID,
DENSE_RANK() OVER(PARTITION BY CustomerID ORDER BY OrderDate DESC) AS n
FROM Sales.SalesOrderHeader
)
SELECT CustomerID, SalesOrderID
FROM CTE
WHERE n < 3
ORDER BY CustomerID, SalesOrderID

Komentarz: To miało być zadanie na użycie funkcji rankingu. Z DENSE_RANK poradziliście sobie dość dobrze. Chociaż zdarzyło się, że ktoś umieścił CustomerID w klauzuli ORDER BY zamiast w PARTITION BY, co diametralnie zmieniało i plan, i wynik zapytania. Zamiast CTE (chciałem być "fancy" ;-)) spokojnie można było użyć podzapytania. W podzapytaniu na liście wybieranych kolumn mogło pojawić się OrderDate i plan się nie zmieniał. Mała uwaga do jednego z uczestników: unikałbym nadawania kolumnom aliasów będących nazwami funkcji (RANK).

Post mortem

Mam nadzieję, że konkurs przypadł Wam do gustu. Może nie był to majstersztyk na miarę quizów organizowanych z wielkim rozmachem dla tysięcy uczestników i z nagrodami w stylu XBox 360, ale taki miałem budżet (blah, nie lubię tego słowa) :-) Niemniej jednak bardzo cieszy, że są zapaleńcy, którzy chętnie biorą udział w takiej zabawie. Jak tylko będę miał możliwość (czytaj – jak będę dysponował nagrodami), znów coś wymyślę :-) Zachęcam też Was do organizowania własnych konkursów na blogach czy w ramach portali WSS i CodeGuru. Chętnie pomogę w organizacji i wezmę udział w ciekawym przedsięwzięciu (także jako uczestnik, a co!).

Na koniec trochę statystyk:

  • w konkursie wzięło udział 18 zawodników,
  • 15 zawodników nadesłało rozwiązania zadań we wszystkich etapach,
  • średnie ilości punktów z etapów: [1] – 41,1, [2] – 41,1, [3] – 55,2,
  • w konkursie nie wziął udziału żaden SQL MVP ;-),
  • najszybsza odpowiedź na jeden etap – 8h 25min (etap 1, zawodnik nr 1),
  • odpowiedź najbardziej "last minute" – poniedziałek, 18 czerwca, godz. 23:55 (zawodnik nr 13 w etapie 1).

That’s all folks! Jeszcze raz dzięki za pyszną zabawę i do następnego takiego (lepszego!) konkursu :-)

PS. Nagrody powoli będą docierały do zawodników z TOP 10. Czekajcie na kuriera ;-)

opublikowano przez brejk | 9 komentarzy

[PL] Z galerii mitów: Ownership Chains

W niedzielę, 31 maja, miałem przyjemność poprowadzić prezentację pt. "Błogosławieństwa i przekleństwa procedur składowanych" na konferencji CodeCamp w Krakowie. W czasie prezentacji okazało się, że nadal w środowisku programistów i ludzi pracujących z SQL Serverem żyje mit, który mówi: "uprawnienie do wykonywania procedury składowanej umożliwia dostęp do tabeli używanej w procedurze bez nadawania uprawnienia bezpośrednio na samej tabeli wtedy, gdy tabela i procedura znajdują się w tym samym schemacie". Mit mówi o szczególnym przypadku tego, co nazywa się po angielsku ownership chains. Sama nazwa mechanizmu sugeruje, jakie pojęcie jest kluczem działania. Pojęciem tym jest "właściciel" (ang. owner).

W SQL Server 2005 Microsoft wprowadził na dobre pojęcie schematu (ang. schema) jako logicznego pojemnika na obiekty w bazach danych. Co prawda pojęcie to istniało w SQL Server 2000, ale w tamtej wersji w zasadzie pozostawało bezużyteczne i użytkownicy operowali bezpośrednio pojęciem "właściciela obiektu". W SQL Server 2005 i 2008 pojęcie właściciela obiektu zostało niejako zniesione. Pojawił się za to "właściciel schematu". I tu jest pies pogrzebany. Zobaczmy to na przykładzie (przykład z mojej prezentacji na CodeCamp):

USE AdvdentureWorks;
GO

-- Tworzymy nowego usera w bazie
IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N'Jasio')
DROP USER Jasio;
GO
CREATE USER Jasio WITHOUT LOGIN;
GO

-- Próba wykonania zapytania przez usera kończy się błędem:
-- Msg 229, Level 14, State 5, Line 1
-- The SELECT permission was denied on the object 'SalesOrderDetail',
-- database 'AdventureWorks', schema 'Sales'.
EXECUTE AS USER='Jasio';
GO
SELECT *
FROM Sales.SalesOrderDetail
WHERE ProductID = 710;
GO
REVERT;

-- Tworzymy procedurę składowaną wykonującą zapytanie z parametrem
-- WAŻNE: procedura jest w schemacie dbo, a tabela w schemacie Sales
IF OBJECT_ID('dbo.usp_GetOrderDetailsByProduct', 'P') IS NOT NULL
DROP PROC dbo.usp_GetOrderDetailsByProduct;
GO
CREATE PROC dbo.usp_GetOrderDetailsByProduct
@ProductID int
WITH RECOMPILE
AS
SET NOCOUNT ON;
SELECT *
FROM Sales.SalesOrderDetail
WHERE ProductID = @ProductID;
GO

-- Dajemy userowi uprawnienia do wykonywania procedury
GRANT EXECUTE ON OBJECT::dbo.usp_GetOrderDetailsByProduct TO Jasio;
GO

-- User może dzięki procedurze dostać się do tabeli
-- mimo, że tabela i procedura są w innych schematach!
EXECUTE AS USER='Jasio';
GO
EXEC dbo.usp_GetOrderDetailsByProduct 710;
GO
REVERT;

-- Tworzymy nowego usera o nazwie Stasio
IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N'Stasio')
DROP USER Stasio;
GO
CREATE USER Stasio WITHOUT LOGIN;
GO

-- User Stasio nowym właścicielem schematu Sales
ALTER AUTHORIZATION ON SCHEMA::Sales TO Stasio;
GO

-- Jasio nie ma dostępu do tabeli Sales.SalesOrderDetail
-- przez procedurę dbo.usp_GetOrderDetailsByProduct,
-- ponieważ schematy dbo i Sales mają innych właścicieli
EXECUTE AS USER='Jasio';
GO
EXEC dbo.usp_GetOrderDetailsByProduct 710;
GO
REVERT;

-- Sprzątamy
ALTER AUTHORIZATION ON SCHEMA::Sales TO dbo;
DROP USER Stasio;
DROP USER Jasio;
DROP PROC dbo.usp_GetOrderDetailsByProduct;
GO


A zatem, by obalić mit, podsumujmy: uprawnienie do wykonywania procedury składowanej umożliwia dostęp do tabeli używanej w procedurze bez nadawania uprawnienia bezpośrednio na samej tabeli wtedy, gdy tabela i procedura znajdują się w schematach, których właścicielem jest ten sam user. Taka sama zasada dotyczy oczywiście widoków i tabel w nich wykorzystywanych.

Jeżeli macie propozycję jakiegoś mitu, chętnie pochylę się nad każdą obiegową opinią czy stwierdzeniem. A może za jakiś czas Maciek Pilecki zrobi sesję Mity w SQL Server cz. II ;-)

[EDIT] Tak się skupiłem na technikaliach, że zapomniałem napisać o CodeCamp :D W dwóch słowach: super konferencja! Świetna organizacja i znakomicie prowadzący imprezę Szymon Kobalczyk odbierający każdemu prelegentowi slajd "About me" ;-) Za rok też tam pewnie pojadę, tym razem może na nieco dłużej. [/EDIT]

opublikowano przez brejk | 2 komentarzy

[PL] Konkurs "Co to za query" – po 2. etapie

Drugi etap konkursu "Co to za query" przechodzi do historii. Podobno było trudniej niż w początkowej fazie konkursu. Hmmm, czy ja wiem :-)

Hall of Fame

Dwóch śmiałków utrzymało wyborną formę z 1. etapu i ma na koncie "maksa". Cieszy mnie to, że prawie nikt nie zrezygnował po 1. etapie. Dajecie radę!

[EDIT] Ha! Nie jestem nieomylny. W wyniki po 2. etapie wkradł się błąd. Dziękuję zawodnikowi nr 4 za uczciwość. Zrewidowałem wyniki i jeszcze raz przejrzałem skrypty. Teraz tabelka się zgadza. Mamy zwycięzcę w klasyfikacji fair-play :-) [/EDIT]

Miejsce ID uczestnika Punkty Czas (dni)*
1 5 140 3,90
2 3 140 4,20
3 12 120 11,18
4 2 115 1,32
5 13 115 11,15
6 10 115 12,05
7 16 115 13,34
8 11 100 12,26
9 4 95 9,21
10 7 80 5,87
11 9 80 6,97
12 15 70 12,45
13 14 55 12,82
14 6 45 9,91
15 17 45 12,14
16 8 30 10,21
17 1 10 6,07
18 18 10 13,37

* Czas w dniach będący różnicą między danym uczestnikiem a uczestnikiem, który najszybciej nadesłał odpowiedzi do etapu

Odpowiedzi do zadań z 2. etapu

Zadanie 1 - "Tylko dla snobów"

Opis: Lista 10 najdroższych produktów będących w sprzedaży (ex aequo).

Kod:

SELECT TOP 10 WITH TIES Name, ListPrice
FROM Production.Product
WHERE DiscontinuedDate IS NULL
ORDER BY ListPrice DESC

Komentarz: W zasadzie żadnych problemów, choć chyba zdarzyło się komuś wsadzić za dużo o jedną kolumnę do wyniku. Kilka osób kombinowało niepotrzebnie z podzapytaniami (ale plany się zgadzały, więc tragedii nie ma).

Zadanie 2 - "Najbliżej do Jumping Street"

Opis: Lista adresów w kolejności od najbliższego do 9178 Jumping St. w Dallas :-)

Kod:

SELECT 
a2.AddressLine1,
a2.City,
a1.SpatialLocation.STDistance(a2.SpatialLocation)/1000 AS Distance
FROM Person.Address AS a1
CROSS JOIN Person.Address AS a2
WHERE a1.AddressID <> a2.AddressID
AND a1.AddressLine1 = '9178 Jumping St.' AND a1.City = 'Dallas'
ORDER BY 3 ASC

Komentarz: Stworzyłem tu małego potworka :-) Użycie CROSS JOIN w miejsce INNER JOIN zmienia co najwyżej (a i chyba nie zawsze) jeden atrybut w elemencie NestedLoops w planie XML. Dlatego odpowiedzi z INNER JOIN przyjmowałem jako prawidłowe. Zdarzało się też, że zapominaliście o podzieleniu STDistance przez 1000 (a tylko wtedy wynik jest w kilometrach).

Zadanie 3 - "Klienci trzej"

Opis: Zestawienie ilości zamówień trzech wybranych klientów per rok kalendarzowy.

Kod:

SELECT CalendarYear, [29825], [29826], [29827]
FROM (
SELECT YEAR(OrderDate) AS CalendarYear, CustomerID
FROM Sales.SalesOrderHeader
WHERE CustomerID IN (29825, 29826, 29827)
) AS Q
PIVOT
(
COUNT(CustomerID) FOR CustomerID IN ([29825], [29826], [29827])
) AS P

Komentarz: Prościutkie (chyba) ćwiczenie z klasyki PIVOTa :-) Opcjonalnie można było przepisać z planu XML wymyślne COUNTy z CASE WHEN, dodać GROUP BY po roku i też było dobrze. Daliście radę bez problemu.

Zadanie 4 - "Money, money, money"

Opis: Wyciąg informacji o zarobkach osób z danych demograficznych trzymanych w dokumentach XML.

Kod:

WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey')
SELECT
p.FirstName,
p.LastName,
T.x.value('YearlyIncome[1]', 'varchar(100)') AS YearlyIncome
FROM Person.Person AS p
CROSS APPLY p.Demographics.nodes('/IndividualSurvey') AS T(x)
WHERE T.x.exist('YearlyIncome[1]') = 1

Komentarz: Tak, to było trudne :-) W planie były liczne odwołania do indeksów XML i do tabel wewnętrznych tworzonych na potrzeby owych indeksów. Dokładne okiełznanie tego planu wymagało choć minimalnej znajomości metod typu danych XML (nodes, value i exist). Dodatkowo użycie przestrzeni nazw (można było ją deklarować jak powyżej albo w metodach XQuery za pomocą declare default element namespace) i powstało naprawdę wymyślne zadanie. Szacunek dla tych, którzy podołali (4 osoby). Największymi przegranymi tego zadania są ci, którzy wpadli na samą składnię, a polegli na typie danych użytym w metodzie value (wpisywali coś innego niż varchar(100)). Wiecie co? Chyba napiszę na blogu jakiś artykulik o zastosowaniach typu XML :-)

Podsumowanie

Tak, to prawdopodobnie był najtrudniejszy etap konkursu ;-) A przynajmniej tak wynika z Waszych wypowiedzi. Tym bardziej cieszą wyniki, które są naprawdę niezłe. Wiele osób już nadesłało rozwiązania do zadań z 3. etapu. Wyniki ogłoszę w środę – 3 czerwca. Rozdanie nagród (części) - na spotkaniu PLSSUG Warszawa.

Natomiast już dzisiaj proszę, by każdy z uczestników zajrzał na stronę z listą nagród i wysłał mi na maila (pawelp[at]plssug[dot]org[dot]pl) listę 5 książek w kolejności "chcenia" :-) O tym, kto dostanie książkę i jaki będzie tytuł, zadecyduje oczywiście końcowa klasyfikacja (trzeba być w TOP 10, żeby załapać się na literaturę).

Trzymam kciuki za Was w 3. etapie. Finiszujemy :-)

opublikowano przez brejk | 5 komentarzy

[PL] Konkurs "Co to za query" – etap 3 z 3

Dotarliśmy do ostatniego etapu konkursu "Co to za query". Ostatnia prosta – 4 zadania z 3. etapu możecie pobrać poniżej.

Przypominam, że rozwiązania zadań z drugiego etapu można nadsyłać do dzisiaj (poniedziałek, 25.05) do końca dnia.

Wyniki konkursu po drugim etapie już w najbliższą środę (27.05)!

Zadania do 3. etapu

Zadanie 1 - "Podatki w US a w PL" – 10 punktów
Zadanie 2 - "Your Vista Has Expired" – 15 punktów
Zadanie 3 - "Size and color matter" – 20 punktów
Zadanie 4 - "Ostatnie zamówienia" – 25 punktów

Powodzenia! Na rozwiązania powyższych zadań czekam do końca następnego poniedziałku (01.06).


[ Regulamin konkursu ] | [ Lista nagród ]

opublikowano przez brejk | 8 komentarzy

[PL] 35. spotkanie PLSSUG Warszawa

35. spotkanie PLSSUG Warszawa

 

W imieniu swoim oraz prelegentów mam przyjemność zaprosić na 35. spotkanie warszawskiego oddziału Polskiej Grupy Użytkowników SQL Server (PLSSUG). Spotkanie rozpocznie się 4 czerwca o godzinie 18:00 w siedzibie firmy Microsoft (Al. Jerozolimskie 195A) w Warszawie. Czas trwania spotkania: mniej więcej trzy godziny. Wstęp na spotkanie wolny.

Tematy przewodnie spotkania to: SQL Server Profiler Tips&Tricks oraz administracja wieloma instancjami SQL Server. A więc tym razem skupiamy się bardziej na administracji.

Prelegentami na spotkaniu będą:

Łukasz Grala (niezależny konsultant i trener) – człowiek po przejściach z systemami ERP, certyfikowany trener Microsoft oraz aktywista PLSSUG (lider PLSSUG Poznań)

oraz

Krzysztof Stachyra (Compendium CE) – także trener Microsoft, aktywny użytkownik forum SQL Server na portalu WSS.pl, stały bywalec spotkań PLSSUG Warszawa.

Oprócz wspomnianych dwóch gentlemanów, Hubert Kobierzewski (tym razem w duecie z Markiem Adamczukiem) będzie kontynuował cykl comiesięcznych mini-prezentacji "Indeksy od A do Z".

Jak do tego dodam, że sponsor – firma Microsoft – zapewni nam jedzonko i gadżety, to nawet ci, którzy cenią "suweniry" nad kawał porządnej wiedzy, nie powinni być rozczarowani :-)

Szczegółowa agenda 35. spotkania PLSSUG Warszawa

Nieobowiązkowa rejestracja na 35. spotkanie PLSSUG Warszawa

Zapraszam w imieniu Polskiej Grupy Użytkowników SQL Server!

Więcej o PLSSUG można przeczytać na witrynie grupy: http://plssug.org.pl.

W razie pytań proszę o kontakt na adres pawel.potasinski [ at ] plssug.org.pl.

opublikowano przez brejk | 0 komentarzy

[PL] Konkurs "Co to za query" – po 1. etapie

Tada! Jesteśmy po pierwszym etapie konkursu "Co to za query". Na wstępie dziękuję wszystkim, którzy wykazują zainteresowanie moim pomysłem. Mam nadzieję, że bawicie się nieźle, a jeszcze sporo przed nami. A teraz pora na…

Hall of Fame

Każdy z uczestników otrzymał swój identyfikator (jakby ktoś zapomniał swój numerek, niech pisze maila). Będę się posługiwał tymi numerkami, żeby nie używać Waszych danych osobowych do ostatniej chwili i ujawnić Wasze nazwiska dopiero za Waszą zgodą :-) Więc jak się trafi jakiś MVP, co nie ustrzeli ani punktu, to może mu się upiec :-P

Niech nie łamią rąk ci, którym nie poszło. Ponoć było wcale nie tak łatwo. Ale jeszcze będzie okazja, żeby się "odkuć". Niech nie cieszą się za mocno ci, którym się udało zmaksować. To nie koniec zabawy :-)

Miejsce ID uczestnika Punkty Czas (dni)*
1 2 70 0,34
2 3 70 1,28
3 5 70 3,90
4 10 70 6,06
5 13 70 6,32
6 16 70 7,28
7 11 55 6,19
8 4 50 3,74
9 12 50 5,20
10 7 35 4,01
11 6 35 4,26
12 9 35 4,97
13 14 25 6,76
14 15 25 6,93
15 1 10 0,00
16 8 0 4,68
17 17 0 7,29

* Czas w dniach będący różnicą między danym uczestnikiem a uczestnikiem, który najszybciej nadesłał odpowiedzi do etapu

Odpowiedzi do zadań z 1. etapu

Jak się powiedziało "A", to trzeba powiedzieć "B". Poniżej odpowiedzi do zadań z 1. etapu wraz z krótkim komentarzem.

Zadanie 1 - "Weterani"

Opis: Lista pracowników (login + ilość lat pracy), którzy przepracowali co ponad 10 lat (no, z grubsza…).

Kod:

SELECT 
e.LoginID,
DATEDIFF(year,HireDate,GETDATE()) AS YearsInCompany
FROM HumanResources.Employee AS e
WHERE HireDate < DATEADD(year,-10,GETDATE())

Komentarz:

Tu raczej nie było problemów, choć kilku uczestników poległo na tym, że czytając postać XML planu za bardzo wzięli do siebie CONVERT_EXPLICIT (niejawna konwersja) i używali jawnie funkcji CONVERT. Ciekawe, że w powyższym wywołaniu wynik GETDATE jest konwertowany na DATETIMEOFFSET(3), chociaż kolumna HireDate jest typu date :-)

Zadanie 2 - "Złap vendora"

Opis: Lista vendorów z najwyższym CreditRating.

Kod:

SELECT 
v.AccountNumber,
v.Name
FROM Purchasing.Vendor AS v
WHERE v.CreditRating = (
SELECT MAX(v1.CreditRating)
FROM Purchasing.Vendor AS v1
)

Komentarz:

W zasadzie równie proste jak zadanie 1. Alternatywnym rozwiązaniem jest użycie JOINa. Wpadki notowaliście głównie na braku aliasu v1 (tak, jeśli takowy jest w planie, to należy go umieścić w zapytaniu) oraz na zbyt dużej liczbie kolumn zwracanej przez zapytanie (zwracaliście czasem CreditRating).

Zadanie 3 - "Piramida"

Opis: Lista pracowników – login, login przełożonego, poziom zagnieżdżenia w hierarchii firmy.

Kod:

SELECT 
e1.LoginID AS Employee,
e2.LoginID AS Boss,
e1.OrganizationNode.GetLevel() AS Lvl
FROM HumanResources.Employee AS e1
LEFT OUTER JOIN HumanResources.Employee AS e2
ON e1.OrganizationNode.GetAncestor(1) = e2.OrganizationNode
ORDER BY e1.OrganizationNode

Komentarz:

Przykład użycia typu hierarchyid nie sprawił większości z Was problemów. Dwa miejsca, w których się myliliście – nieprawidłowa kolumna w ORDER BY (roztargnienie?) i poziom wybierany przy użyciu kolumny wyliczanej OrganizationLevel zamiast przy użyciu jawnego odwołania do metody GetLevel (różnicę w planie widać dość wyraźnie – o jeden Expr więcej jest w przypadku GetLevel). Niektórym zdarzyło się pomylić rodzaj złączenia (INNER zamiast LEFT).

Przy okazji tego zapytania wyszło na jaw parę ciekawostek. SQL Server wylicza wartości w kolumnach obliczanych (tu – kolumna OrganizationLevel) przy skanie indeksu clustered, ale mimo to wartości te są liczone drugi raz przy jawnym wywołaniu metody GetLevel. Jeden z uczestników powielił jedną z kolumn na liście kolumn wybieranych przez zapytanie, a mimo to plan się nie zmienił.

Zadanie 4 - "Los szczęścia"

Opis: Duży Lotek oczywiście (6 liczb z 49) :-)

Kod:

;WITH CTE AS (
SELECT 1 AS number
UNION ALL
SELECT c.number + 1
FROM CTE AS c
WHERE number < 49
)
SELECT TOP 6 number AS Lotto
FROM CTE
ORDER BY RAND(BINARY_CHECKSUM(NEWID()))

Komentarz:

W pierwszej chwili z planu nie wynika praktycznie nic :-) Ale jak się przyjrzeć temu, co jest w XML-u, to w parę minut można dojść do powyższego CTE. Jeden z uczestników odruchowo wpisał w ORDER BY samo NEWID…

Przy okazji analizowania tego planu można zauważyć, że jeśli sortujemy po jakimś wyrażeniu czy kolumnie, to owo wyrażenie lub owa kolumna znajdą się w OutputList.

I wreszcie – czy zauważyliście, w jaki sposób w tym planie zostały zawarte informacje o MAXRECURSION? :-) Jest w planie XML taki fragment:

CASE WHEN [Expr1007]&gt;(100) THEN (0) ELSE NULL END

Powyższy warunek zapewne sprawdza, czy dane rekursywne CTE ma w ogóle prawo się wykonać (czy nie został przekroczony maksymalny dopuszczany poziom rekursywności – domyślnie 100). Sprawdźcie sami, dopisując na koniec zapytania OPTION(MAXRECURSION 101).

Podsumowanie

To co, bardzo trudne było (bardzo łatwe być nie może, w końcu ma być wysiłek intelektualny!)? Myślę, że nie, ale poprawcie mnie, jeśli się mylę :-)

Powodzenia w 2. etapie, którego wyniki opublikuję za tydzień.

opublikowano przez brejk | 11 komentarzy

[PL] Konkurs "Co to za query" – etap 2 z 3

Witam wszystkich oczekujących na zadania drugiego etapu konkursu "Co to za query". Bez zbędnego owijania w bawełnę – zadania do drugiego etapu poniżej. Nie sądzę, by były trudniejsze od tych z pierwszego etapu.

Przypominam, że rozwiązania zadań z pierwszego etapu można nadsyłać do dzisiaj do końca dnia.

Wyniki konkursu po pierwszym etapie już w najbliższą środę (20.05)!

Zadania do 2. etapu

Zadanie 1 - "Tylko dla snobów" - 10 punktów

Zadanie 2 - "Najbliżej do Jumping Street" - 15 punktów

Zadanie 3 - "Klienci trzej" - 20 punktów

Zadanie 4 - "Money, money, money" - 25 punktów

Powodzenia! Na rozwiązania powyższych zadań czekam do końca następnego poniedziałku (25.05). 


[ Regulamin konkursu ] | [ Lista nagród ]

opublikowano przez brejk | 14 komentarzy