|
|
-
Mogę już używać tej oto odznaki:
W 26 TSQL Challenger uzyskałem pierwsze 8 punktów (8 SQL Stars). Leszek Gniadkowski, z którym "nakręcamy" się co do rozwiązań zdobył w tym rozdaniu 9 punktów (gratulacje!). W "drodze" są jeszcze zaakceptowane rozwiązania od 27 do 33 challengera czekające na testy logiczne i wydajnościowe. Może jeszcze coś się uda zdobyć. PS. Znaczek jak znaczek. Bardziej mi się kojarzy z jakimś klubem koszykarskim NBA :)
|
-
Portal wiedzy VirtualStudy to przedsięwzięcie niezwykle cenne. Pozwala na bezpłatne poszerzenie swojej wiedzy o najnowszych technologiach informatycznych. Wystarczy tylko zarejestrować się na wybraną sesję. Podobnie będzie ze najnowszą inicjatywą portalu: VirtualStudy Conference 2010. Na uczestników czeka doborowy oddział prelegentów. Wystarczy usiąść z laptopem w cieniu drzewa i popijając chłodne piwo chłonąć to co mają do powiedzenia uznani wymiatacze. Oczywiście osobistych spotkań w czasie takich konferencji nie da się niczym zastąpić ale to już widocznie taki przewrotny signum temporis, że będąc blisko jesteśmy daleko od siebie. Tak więc nie czekaj! Zarejestruj się jeszcze dziś!
|
-
Portal Beyond Relational założył SQL Server MVP Jacob Sebastian a jego sztandarowym "produktem" jest zdobywający coraz większe zainteresowanie nieustający konkurs TSQL Challenge, czyli zadania będące rzeczywistymi wyzwaniami w tworzeniu zapytań T-SQL. Wyzwania o tyle trudne, że muszą mieć postać jednej instrukcji języka T-SQL rozpoczynającej się od SELECT lub WITH. Zapytania mają się poprawnie wykonać głównie w nowszych wersjach SQL Server (2005 i 2008) ale nie brakuje również zadań wyłącznie w wersji 2000 (czyli żegnajcie "wodotryski" z nowszych wersji). Pierwszy raz TSQL Challenge w szerszym odbiorze pojawiło się na portalu wss.pl przy okazji 20 chellenger'a dotyczącego identyfikacji powtarzających się cyfr w wyrazach ciągu Fibonacciego. Na wyniki klasyfikacji trzeba było trochę poczekać. Z naszego kraju było sklasyfikowanych kilka osób a zwyciężył Maciej Pilecki, którego rozwiązanie było banalnie proste. A przy tym niezwykle pomysłowe. Byłem pod wrażeniem. Po krótkim spotkaniu przy okazji 20 challenger'a na jakiś czas o nim zapomniałem, ale głównie za sprawą Leszka Gniadkowskiego (leszek_g na portalu wss.pl) wróciłem do tych wyzwań. I od numeru 26 jestem na bieżąco. Jakie refleksje? - Przekonuje się, że wcale nie jest trudno dojść do satysfakcjonujących wyników (jedne zadania wymagają "błysku" inne wymagają większego "fizycznego" nakładu pracy ale wszystkie wymagają dobrej znajomości T-SQL'a).
- Można to samo zadanie rozwiązać na wiele sposobów (cały czas z użyciem jednej instrukcji -SQL).
- T-SQL jest sumie bardzo elastycznym językiem (chociaż jak się te zadania rozwiązuje to chciałoby się mieć jeszcze więcej możliwości).
- Rozwiązywanie tych zadań to świetna metoda na doskonalenie warsztatu. To jak opanowywanie piłki przez piłkarza poprzez trening żonglerki (pamiętając jednak o tym, że najlepsi w tej materii nie byli najlepszymi piłkarzami).
- Można się uczyć dobrych rozwiązań od zwycięzców - bezcenne.
- Za najlepsze 10 rozwiązań przyznawane są gwiazdy. Zwycięzca dostaje "10 SQL Stars" plus ewentualnie jakieś nagrody. Dziesiąty w klasyfikacji dostaje "1 SQL Star". jest więc element sportowej rywalizacji.
Minusy? Długie oczekiwanie na klasyfikację. Minus pozorny, bo dokąd tu się spieszyć? Podsumowując. Watro ten portal odwiedzić i spróbować zmierzyć się z kolejnym wyzwaniem.
|
-
Zapytanie z opcją FOR XML generujące obiekt XML i metoda nodes() zastosowana do niego mogą stanowić wzajemnie odwrotne transformacje. Zobaczmy to na przykładzie. Najpierw przygotujemy tablicę z danymi testowymi: declare @t table(x int)
insert into @t values
(3),(2),(1),(0),(3)
|
i teraz wykonamy proste zapytania: declare @xml xml
-- 1. Zapytanie podstawowe ----------------------------------
select * from @t
-- 2. xsinil ------------------------------------------------
set @xml = (select * from @t for xml raw, elements xsinil)
select t.c.value('./x[1]','int') x
from @xml.nodes('/row') t(c)
-- 3. absent ------------------------------------------------
set @xml = (select * from @t for xml raw, elements absent)
select t.c.value('./x[1]','int') x
from @xml.nodes('/row') t(c)
|
Pierwsze zapytanie zwraca po prostu zawartość tabeli @t. W drugim zapytaniu tworzymy obiekt XML poprzez najprostszą formę klauzuli FOR XML czyli RAW, ale tworzymy go z opcją ELEMENTS XSINIL. Opcja ta w przypadku gdy wyrażenie tworzące element ma wartość NULL tworzy element z atrybutem xsi:nil="true" i z pustą zawartością. Następnie na obiekt XML działamy metodą nodes() aby przywrócić danym formę tabelaryczną. W trzecim zapytaniu tworzymy obiekt XML poprzez najprostszą formę klauzuli FOR XML czyli
RAW, ale tworzymy go z opcją ELEMENTS ABSENT. Opcja ta w przypadku gdy
wyrażenie tworzące element ma wartość NULL pomija tworzenie elementu XML z nim związanego. Następnie na obiekt XML działamy metodą nodes()
aby przywrócić danym formę tabelaryczną. Jak łatwo się przekonać, wszystkie zapytania zwrócą ten sam rezultat (czyli z obiektu XML wygenerowanego zapytaniem z klauzulą FOR XML otrzymaliśmy ponownie tablicę dzięki zastosowaniu metody nodes() na tym obiekcie - czyli stanowią wzajemnie odwrotne operacje):  Skoro nam tak dobrze idzie to wprowadzimy zaburzenie do naszych danych testowych w sposób następujący: declare
@t table(x int)
insert into @t values
(3),(2),(1),(0),(null)
|
Wykonanie tych samych zapytań da jednak zaskakujący efekt:  Widać z tego, że drugie zapytanie zwróciło wartość zero zamiast oczekiwanej wartości NULL. Czy można temu zachowaniu zapobiec? Tak, wystarczy inaczej zapisać drugie zapytanie, rzutując element zwracany przez metodę value() na typ xs:int? set @xml = (select * from @t for xml raw, elements xsinil)
select t.c.value('./x[1] cast as xs:int?','int') x
from @xml.nodes('/row') t(c)
|
I wszystko wydawałoby się już poukładane, do momentu gdy nie zastosujemy kolumny typu varchar: declare @t table(x int, y varchar(100))
insert into @t values
(3,'a'),(2,'b'),(1,'c'),(0,'d'),(null,null)
declare @xml xml
set @xml = (select * from @t for xml raw, elements xsinil)
select
t.c.value('./x[1] cast as xs:int?','int') x,
t.c.value('./y[1] cast as xs:string?','varchar(100)') y
from @xml.nodes('/row') t(c)
|
W tym wypadku zapytanie zwróci nam wynik:  i widzimy, że w piątym wierszu jedynie w kolumnie y zamiast oczekiwanej wartości NULL mamy pusty string. Nawet zastosowanie rzutowania na xs:string? nie daje spodziewanego rezultatu jak przy rzutowaniu na typ xs:int? przy kolumnie typu int. W rzutowaniu cast as xs:int? oraz cast as xs:string? istotne są znaki zapytania na końcu definicji typu docelowego. Mówią one, że wartość atomowa uzyskiwana przez zapytanie XQuery w metodzie value() a będąca przedmiotem rzutowania może przyjmować wartość null. Dlaczego jednak działa to dla typu int a nie działa dla typu varchar pozostaje dla mnie tajemnicą. Wydaje mi się, że za tym wszystkich gdzieś przebija .NET-owe podejście do XML-a.
|
-
Jak działa ta instrukcja SELECT TOP wie każdy programista SQL. Ale ja chcę się skupić na pewnym jego przypadku użycia. Na początek przygotujmy dane testowe: declare @t table(x varchar(100))
insert into @t values
('x'),('z'),('b'),('a')
select top 100 percent * from @t order by x
|
Instrukcja SELECT TOP zwraca rekordy uporządkowane w sposób następujący: I tu wszystko jest OK. Ale ja pomyślałem o zastosowaniu opcji SELECT TOP szczególnie w subquery, gdzie jak wiadomo użycie ORDER BY nie jest dopuszczalne. Sprawdźmy to: select * from (select * from @t order by x) q
|
Wykonanie takiego zapytania kończy się wyjątkiem: The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.
|
Ale skoro mogę zastosować TOP to spróbujmy zastosować najbardziej nasuwający się sposób użycia zwracający wszystkie dane uporządkowane w subquery czyli SELECT TOP 100 PERCENT: select * from (select top 100 percent * from @t order by x) q
|
Ale ze zdziwieniem stwierdzamy, że zapytanie zwraca rekordy w porządku: Czyli w porządku, w jakim dane te występują w tabeli @t. Zobaczmy wobec tego jak wygląda plan zapytania dla tego przypadku:  Czyli operacja sortowania wcale nie zachodzi! Zróbmy w takim razie niewielka zmianę w zapytaniu: | select * from (select top 99.99 percent *
from @t order by x) q |
ale tym razem otrzymujemy zgodnie z oczekiwaniami uporządkowanie i plan zapytania wyraźnie pokazuje zastosowanie sortowania  i sortowanie zachodzi aż do wartości granicznej dla klauzuli TOP:
| select * from (select top 99.9999999999999 percent
*
from @t order by x) q |
 Z tego planu wykonania wynika, że "dołożenie" kolejnej 9-tki po przecinku do wielkości TOP ... PERCENT spowoduje zaokrąglenie do 100 i ponowny "zanik" sortowania w subquery. Czy można jednak zapewnić, że sortowanie będzie miało jednak miejsce w subquery i jednocześnie zostaną zwrócone wszystkie rekordy? Można. Wystarczy zamiast stosować PERCENT podać maksymalna ilość wierszy zwracanych przez @@ROWCOUNT_BIG czyli zastosować następujące zapytanie:
select * from (select top 9223372036854775807 * from @t order by x) q
|
Wszystkie powyższe rozważania odnoszą się również to wyrażeń CTE, wewnątrz których nie można również stosować ORDER BY (bez użycia klauzuli TOP). Zastosowanie klauzuli TOP 100 PERCENT zachowuje się podobnie jak w subquery czyli nie sortuje danych: ;with cte as
(
select top 100 percent x
from @t
order by x
)
select * from cte
|
Lecz mają zastosowanie wszystkie pozostałe opisane powyżej sposoby na uzyskanie sortowania wewnątrz wyrażenia CTE z zastosowaniem klauzuli TOP (w rozwinięciu dziesiętnym i opcją PERCENT oraz z podaniem maksymalnej liczby bigint w serwerze SQL).
|
-
Przygotowując przykłady do sesji “XML w SQL Server 2008” na portalu Virtual Study w cyklu SQL nie tylko dla orłów organizowanym wspólnie z Polish SQL Server User Group trafiłem na ciekawy przypadek z użyciem konstrukcji ‘every … satisfies’ w metodzie query zastosowanej do obiektu typu XML.
Ale aby dojść do sedna sprawy trzeba zdefiniować dwa pojęcia:
- sekwencje,
- sposób wartościowania logicznego w XQuery.
I. Sekwencje
Na początek definicja pustej sekwencji. Wygląda to tak:
()
Sekwencję tworzy się poprzez użycie operatora przecinkowego do: pustej sekwencji, typów atomowych oraz węzłów. Przykładowe poprawne sekwencje mogą wyglądać tak:
(1, ”dwa”, 3.0,0000004, 5e+0, ())
((), /, /x)
Pusta sekwencja jest pomijana w wynikowej sekwencji.
Nie można w jednej sekwencji użyć typów atomowych i węzłów (generuje to błąd heterogenicznych sekwencji):
(1, ”dwa”, 3.0,0000004, 5e+0, /x)
II. Wartościowanie logiczne w XQuery
Wartościowanie logiczne w XQuery w SQL Server jest zawężone (nie można na przykład wartościować logicznie wartości typu xs:string oraz wartości typu numerycznego, chociaż specyfikacja XQuery przywiduje taką możliwość). Wartościowanie w SQL Server ma zastosowanie do:
- atomowej wartości logicznej (funkcje true() i false() generują odpowiednie wartości logiczne odpowiadające wartości prawy i fałszu logicznego, funkcja not() generuje zaprzeczenie logiczne)
- pustej sekwencji
- sekwencji węzłów
W SQL Server wartościowanie logiczne zwraca true gdy: - sekwencja zawiera węzły lub gdy jest wartość logiczna true
- false w przeciwnym wypadku
III. Przećwiczmy to na przykładach. Na początek zobaczmy jak funkcja not wartościuje pustą sekwencję: select CAST('' as xml).query('not(())') W wyniku dostajemy wynik true, co świadczy, że pusta sekwencja jest wartościowana jak false. Konstrukcja 'every ... satisfies' zwraca true gdy wszystkie elementy z sekwencji są wartościowane na true.
Aby się o tym przekonać zobaczmy jak zadziała takie zapytanie: select
CAST('' as xml).query('every $x in (true(), true()) satisfies $x')
select
CAST('' as xml).query('every $x in (false(), true()) satisfies $x')
select
CAST('' as xml).query('every $x in (true(), false()) satisfies $x')
select
CAST('' as xml).query('every $x in (false(), false()) satisfies $x')
Wyniki są jak najbardziej poprawne, czyli true, false, false,
false. W takim razie zobaczmy czy puste elementy sekwencji za instancjami
węzłów:
select CAST('' as xml).query('every $x in (()
instance of node(), () instance of
node()) satisfies ($x)')
Nie są, zapytanie zwraca false.
To może jeszcze jedno zapytanie sprawdzające z ilu węzłów składa się
pusta sekwencja:
select CAST('' as xml).query('every $x in (count(()), count(()))
satisfies $x > 0')
Jest OK, nie zawiera żadnych węzłów. W takim razie zobaczmy jaki rezultat będzie takiego zapytania: select CAST('' as xml).query('every $x in (()) satisfies $x') O dziwo, pomimo, że sekwencja zawiera sekwencję pustą to powyższe zapytanie zwraca również true. Hmm, dziwne. OK, wobec tego zobaczmy co zwróci to zapytanie: select CAST('' as xml).query('every $x in ((), ()) satisfies $x') True! Dlaczego? Wobec tego zróbmy zapytanie z zaprzeczeniem select CAST('' as xml).query('every $x in ((), ()) satisfies not($x)') Znowu true! Ale przecież zaprzeczyliśmy warunek dla satisfies!
Czy gdzieś popełniam błąd? Dwa powyższe zapytania są równoważne tym zapytaniom: select CAST('' as xml).query('every $x in () satisfies ($x)') select CAST('' as xml).query('every $x in () satisfies not($x)') ponieważ SQL Server pomija pute sekwencje występujące w sekwencji i w takim wypadku nie dochodzi do wartościowania warunku po satisfies ponieważ sekwencja nie zawiera żadnego elementu. Ale czy w takim wypadku nie powinna być zwraca wartość false? Morze to rzecz gustu albo tak po prostu ma być "by design"?
|
-
Interesujący problem został przedstawiony na wss.pl. To klasyczny problem labiryntu. Zmieniłem nieco definicję danych wejściowych problemu zastępując tablicę tymczasową tablicą pamięciową.
declare @t table (n int, r char(10)) values
( 1, ' '),
( 2, ' S '),
( 3, 'xxxxxx '),
( 4, ' '),
( 5, ' '),
( 6, ' xxxxxx'),
( 7, ' '),
( 8, ' E'),
( 9, ' '),
(10, ' '),
|
Rozwiązaniem problemu jest znalezienie najkrótszej drogi pomiędzy dwoma punktami z uwzględnieniem punktów zabronionych (murów). Nie można stosować zmiennych i pętli, można stosować jedynie instrukcje SELECT i CTE.
Rozwiązanie powinno sie dać uzyskać poprzez wygenerowanie wszystkich możliwych ciągów permutacji dwuelementowych bez powtórzeń ze zbioru {1,10} rozpoczynających się od permutacji S(w,k) do permutacji E(w,k) (z wyłączeniem permutacji określających punkty muru) gdzie kolejne permutacje różnią się jednym elementem i różnią się wyłącznie o wartość 1. Wskaźniki permutacji są to wiersz i kolumna macierzy.
Rozwiązanie nie powinno zależeć od istnienia murów. Zawsze można usunąć dowolne punkty ze zbioru możliwych permutacji. Maksymalna ilość dopuszczalnych permutacji (poza punktem startowym) to N x N-1 bez istnienia murów i N x N-M-1 gdzie M jest całkowitą długością murów). Wygenerowanie wszystkich takowych ciągów permutacji dla tablicy 10 x 10 wydaje sie być zadaniem karkołomnym czasowo. W związku z tym postanowiłem działać indukcyjnie – od macierzy mniejszych rozmiarów.
Zbudowałem macierz o rozmiarze 6 x 6:
|
declare @t table (n int, r char(6))
insert into @t values
(1, ' '),
(2, ' S '),
(3, 'xxxx '),
(4, ' '),
(5, ' xxx'),
(6, ' E')
declare @n int = (select DATALENGTH(r) from @t where n =1 )
|
powstałą przez zredukowanie wierszy i kolumn przy zachowaniu ogólnego wyglądu danych podstawowych. Aby ułatwić sobie generowanie rozwiązań dodałem zmienną @n określającą rozmiar macierzy (jest to jedyna zmienna w rozwiązaniu ale nie jest ona wykorzystywana do przechowywania wyników a jedynie wykorzystywana zamiast stałej).
Moje rozwiązanie będzie sie składało z szeregu wywołań CTE, które będę prezentował kolejno. Na koniec podam kompletne rozwiązanie.
I. Zapytanie (algorytm)
1. Współrzędne punktu startu:
|
;with start as
(
select n w, CHARINDEX('S',r) k
from @t
where r like '%S%'
)
|
2. Współrzędne punktu docelowego:
|
, stop as
(
select n w, CHARINDEX('E',r) k
from @t
where r like '%E%'
)
|
3. Uzyskanie współrzędnych “cegieł w murze”. CTE wall wyciąga wiersze zawierające znak X, CTE bricks wyciąga “cegły”, CTE brickPos podaje współrzędne położenia “cegły” w murze.
|
, wall as
(
select n w
from @t
where r like '%X%'
)
, bricks as
(
select t.n w, 1 as k, SUBSTRING(r,1,1) b, r
from @t t
inner join wall w on w.w = t.n
union all
select b.w, b.k+1, SUBSTRING(b.r,b.k+1,1), r
from bricks b
where b.k < @n
)
, brickPos as
(
select b.w, b.k
from bricks b
where b.b = 'X'
)
|
4. Utworzenie tabeli wierzy numerów wierszy (allRows) i kolumn (allCols):
|
, allRows as
(
select 1 as r
union all
select a.r+1 r
from allRows a
where a.r < @n)
, allCols as
(
select 1 as c
union all
select a.c+1 c
from allCols a
where a.c < @n)
|
5. Utworzenie tabeli możliwych do wykorzystania permutacji. Każdy element tabeli zawiera permutację składającą sie z numeru wiersza i kolumny tabeli danych wejściowych. Z tabeli permutacji usunięty został punkt startowy oraz wszystkie “cegły”.
|
, points as
(
select r.r w, c.c k
from allRows r, allCols c
except
select b.w, b.k
from brickPos b
except
select s.w, s.k
from start s
)
|
6. Generowanie ciągów permutacji bez powtórzeń z permutacji zawartych w tabeli generowanej przez CTE points. CTE routes ma jedną kotwicę będącą permutacją określającą punkt startowy. Nie musimy sie martwić o przeszkody na drodze ponieważ permutacje je określające zostały już przez nas wyeliminowane w pkt. 5. CTE routes musi mieć cztery elementy rekursywne określające cztery możliwe do wyboru następne permutacje (odpowiadające czterem kierunkom ruchu).
|
, routes as
(
select w,k,
cast('('+CAST(w as varchar(2))+','+
CAST(k as varchar(2))+')' as varchar(max)) path,
1 level
from start
-------------
union all
select r.w, r.k+1,
cast(r.path+',('+CAST(r.w as varchar(2))+','+
CAST(r.k+1 as varchar(2))+')' as varchar(max)),
r.level+1
from routes r
inner join points p on p.k = r.k+1 and p.w = r.w
where not(r.k+1 = (select k from stop) and r.w = (select w from stop))
and r.path not like '%('+CAST(r.w as varchar(2))+','+
CAST(r.k+1 as varchar(2))+')%'
---------------
union all
select r.w, r.k-1,
cast(r.path+',('+CAST(r.w as varchar(2))+','+
CAST(r.k-1 as varchar(2))+')' as varchar(max)),
r.level+1
from routes r
inner join points p on p.k = r.k-1 and p.w = r.w
where not(r.k-1 = (select k from stop) and r.w = (select w from stop))
and r.path not like '%('+CAST(r.w as varchar(2))+','+
CAST(r.k-1 as varchar(2))+')%'
---------------
union all
select r.w-1, r.k,
cast(r.path+',('+CAST(r.w-1 as varchar(2))+','+
CAST(r.k as varchar(2))+')' as varchar(max)),
r.level+1
from routes r
inner join points p on p.k = r.k and p.w = r.w-1
where not(r.w-1 = (select w from stop) and r.k = (select k from stop))
and r.path not like '%('+CAST(r.w-1 as varchar(2))+','+CAST(r.k as varchar(2))+')%'
-----------------
union all
select r.w+1, r.k,
cast(r.path+',('+CAST(r.w+1 as varchar(2))+','+
CAST(r.k as varchar(2))+')' as varchar(max)),
r.level+1
from routes r
inner join points p on p.k = r.k and p.w = r.w+1
where not(r.w+1 = (select w from stop) and r.k = (select k from stop))
and r.path not like '%('+CAST(r.w+1 as varchar(2))+','+CAST(r.k as varchar(2))+')%'
)
|
7. Prezentacja wszystkich permutacji prowadzących od punktu startowego do punktu końcowego (czyli po prostu drgi z punktu startowego do punktu końcowego):
|
select r.level+1,
r.path+',('+CAST((select w from stop) as varchar(2))+','+
CAST((select k from stop) as varchar(2))+')'
from routes r
where (r.k = (select k-1 from stop) and r.w = (select w from stop)) or
(r.k = (select k+1 from stop) and r.w = (select w from stop)) or
(r.k = (select k from stop) and r.w = (select w+1 from stop)) or
(r.k = (select k from stop) and r.w = (select w-1 from stop))
order by r.level
option (maxrecursion 100)
|
Dla podanych danych uzyskałem jedną najkrótszą trasę o długości 13:
Co prowadzi do rozwiązania graficznego:
Dla tych danych istnieją również dwie najdłuższe drogi o długości 29:
I jego reprezentacja graficzna dla rozwiązania numer 924:
oraz dla rozwiązania numer 923:
Poszukiwanie najdłuższych tras jest moim zdaniem ciekawsze niż znajdowanie najkrótszych. Przypomina trochę problem wypełnienia płaszczyzny krzywą. I nie jest łatwe do uzyskania metodą “na oko” co jest łatwiejsze w przypadku poszukiwania drogi najkrótszej.
II. Relacje czasowe
Rozwiązania przetestowałem dl dla następujących rozmiarów macierzy:
Rozmiar macierzy
|
Czas wykonania [sek]
|
5x5
|
1
|
6x6
|
8
|
7x7
|
923
|
8x8
|
w momencie pisania tego tekstu 4 godziny i nie było widać końca |
Z zestawienia widać, że wzrost nakładów obliczeniowych następuje lawinowo (postaram się wytrwać w poszukiwaniu rozwiązania 8x8, ). Poszukiwanie rozwiązania dla macierzy 10x10 pewnie trwałoby “wieczność”.
III. Kompletne rozwiązanie
|
declare @t table (n int, r char(6))
insert into @t values
(1, ' '),
(2, ' S '),
(3, 'xxxx '),
(4, ' '),
(5, ' xxx'),
(6, ' E')
declare @n int = (select DATALENGTH(r) from @t where n =1 )
;with start as
(
select n w, CHARINDEX('S',r) k
from @t
where r like '%S%'
)
, stop as
(
select n w, CHARINDEX('E',r) k
from @t
where r like '%E%'
)
, wall as
(
select n w
from @t
where r like '%X%'
)
, bricks as
(
select t.n w, 1 as k, SUBSTRING(r,1,1) b, r
from @t t
inner join wall w on w.w = t.n
union all
select b.w, b.k+1, SUBSTRING(b.r,b.k+1,1), r
from bricks b
where b.k < @n
)
, brickPos as
(
select b.w, b.k
from bricks b
where b.b = 'X'
)
, allRows as
(
select 1 as r
union all
select a.r+1 r
from allRows a
where a.r < @n)
, allCols as
(
select 1 as c
union all
select a.c+1 c
from allCols a
where a.c < @n)
, points as
(
select r.r w, c.c k
from allRows r, allCols c
except
select b.w, b.k
from brickPos b
except
select s.w, s.k
from start s
)
, routes as
(
select w,k,
cast('('+CAST(w as varchar(2))+','+
CAST(k as varchar(2))+')' as varchar(max)) path,
1 level
from start
-------------
union all
select r.w, r.k+1,
cast(r.path+',('+CAST(r.w as varchar(2))+','+
CAST(r.k+1 as varchar(2))+')' as varchar(max)),
r.level+1
from routes r
inner join points p on p.k = r.k+1 and p.w = r.w
where not(r.k+1 = (select k from stop) and r.w = (select w from stop))
and r.path not like '%('+CAST(r.w as varchar(2))+','+
CAST(r.k+1 as varchar(2))+')%'
---------------
union all
select r.w, r.k-1,
cast(r.path+',('+CAST(r.w as varchar(2))+','+
CAST(r.k-1 as varchar(2))+')' as varchar(max)),
r.level+1
from routes r
inner join points p on p.k = r.k-1 and p.w = r.w
where not(r.k-1 = (select k from stop) and r.w = (select w from stop))
and r.path not like '%('+CAST(r.w as varchar(2))+','+
CAST(r.k-1 as varchar(2))+')%'
---------------
union all
select r.w-1, r.k,
cast(r.path+',('+CAST(r.w-1 as varchar(2))+','+
CAST(r.k as varchar(2))+')' as varchar(max)),
r.level+1
from routes r
inner join points p on p.k = r.k and p.w = r.w-1
where not(r.w-1 = (select w from stop) and r.k = (select k from stop))
and r.path not like '%('+CAST(r.w-1 as varchar(2))+','+CAST(r.k as varchar(2))+')%'
-----------------
union all
select r.w+1, r.k,
cast(r.path+',('+CAST(r.w+1 as varchar(2))+','+
CAST(r.k as varchar(2))+')' as varchar(max)),
r.level+1
from routes r
inner join points p on p.k = r.k and p.w = r.w+1
where not(r.w+1 = (select w from stop) and r.k = (select k from stop))
and r.path not like '%('+CAST(r.w+1 as varchar(2))+','+CAST(r.k as varchar(2))+')%'
)
select r.level+1,
r.path+',('+CAST((select w from stop) as varchar(2))+','+
CAST((select k from stop) as varchar(2))+')'
from routes r
where (r.k = (select k-1 from stop) and r.w = (select w from stop)) or
(r.k = (select k+1 from stop) and r.w = (select w from stop)) or
(r.k = (select k from stop) and r.w = (select w+1 from stop)) or
(r.k = (select k from stop) and r.w = (select w-1 from stop))
order by r.level
option (maxrecursion 100)
|
Miłej zabawy w poszukiwaniu właściwej drogi!
|
-
SQL Server Event Notifications (EN) to właściwość wprowadzona już w wersji 2005 ale niedoceniona przeze mnie wówczas i myślę, że przez wielu entuzjastów SQL-a. Funkcjonalnie działanie opiera się na SQL Server Service Broker i dlatego implementując EN warto wesprzeć się na moim add-in do SSMS czyli Service Broken, którego źródła są tutaj. Wersja tam znajdująca działa z wersją SQL Server 2008 (również Express). Dlaczego warto mieć takie narzędzie do wsparcia? Dlatego, że Service Broker bywa bardzo kapryśny w implementowaniu. Kiedy wydaje się, że już wszystko zostało zrobione i następuję moment końcowego odliczania do uruchomienia implementacji nagle okazuje się, że po naciśnięciu klawisza F5 następuje głucha cisza. Wówczas niezbędne okazuje się sprawne przejrzenie właściwości i infrastruktury Service Broker'a aby odkryć możliwą przyczynę braku działania. Szczególnie przydatna w takich momentach jest grupa opcji Show oraz Other options, która w prosty i szybki sposób dostarcza szeregu informacji w postaci okienkowej bez konieczności znajomości wywołań widoków systemowych związanych z Service Broker. Z moich doświadczeń wynika, że najlepszym rozwiązaniem jest posiadanie zaimplementowanego jednego bazowego rozwiązania, które tworzyłoby środowisko nasłuchujące zdarzenia generowane przez SQL Server. Taka implementacja powinna mieć dosyć szeroką klasę event'ów do nasłuchiwania. Dosyć szeroką ale nie za szeroką. W moich doświadczeniach skupiłem się głównie na klasie TRC_ERRORS_AND_WARNINGS, która obejmuje głownie sytuacje wyjątkowe oraz informacje o zachowaniu się SQL Server (interesowała mnie głownie ta klasa a nie klasa DDL_EVENTS ponieważ interesowały mnie głównie błędy generowane prze implementację posadowione na SQL Server). Napisałem, że nie powinna być za szeroka ponieważ w doświadczeniach posunąłem się do zastosowania klasy TRC_ALL_EVENTS co pokazało, że tabela do przechowywania komunikatów "pompowanych" przez SQL Server w błyskawicznym tempie zaczęła się zapełniać masą rekordów z komunikatami zdarzeń. Ta implementacja bazowa byłaby wznawiana na czas uruchomień nowych implementacji EN nasłuchując możliwych sytuacji wyjątkowych, w tym związanych z tworzoną właśnie nową implementacją. Poz zakończeniu wdrażania nowej implementacji EN tę bazową należałoby wyłączyć do czasu uruchomienia następnej. Nie chcę tu szczegółowo opisywać jak zaimplementować EN. Jest to niezwykle proste. Przykład jest tutaj. Na co jednak warto zwrócić szczególną uwagę? - Najważniejsze jest aby baza danych, której dotyczy EN miała włączony Service Broker. Bez tego nie mam mowy o działaniu EN. Tworzenie nowej bazy danych przez GUI z SSMS domyślnie wyłącza Service Broker. Więc jest to punkt krytyczny. Mój add-in pozwala na włączenie i wyłączenie go z zakładki Brokers, która pokazuje wszystkie Service Broker's aktualnie dostępnego servera SQL. Odblokowanie i zablokowanie odbywa się poprzez naciśnięcie kontrolki check box.
- Tworząc subskrypcje zdarzeń przez CREATE EVENT NOTIFICATION należy bezwzględnie pamiętać, że nazwa serwisu jest literałem a nie sysname i do tego jest case sesitive i jest to błąd czasami trudny do zauważenia (dlatego tworząc tę subskrypcję należy do nazwy serwisu zastosować metodę Copy-Paste nazwy serwisu z polecenia CREATE SERVICE).
- Jeżeli w tworzeniu subskrypcji EN korzystamy ze składni 'current database' informującą, że subskrypcja odnosi się do bieżącej bazy danych to literał ten jest również case sensitive.
- Poza stosowaniem metody Copy-Paste do nazwy serwisu zdecydowanie odradzam stosowanie tej metody to tworzenia nowej implementacji na podstawie już istniejącej. Wbrew pozorom trzeba dokonać zmian w wielu miejscach i pominięcie choćby jednej zmiany owocuje w braku działania implementacji EN. Schemat tworzenia EN jest prosty i można go szybko opanować: tworzenie kolejki, tworzenie serwisu w oparciu o kontrakt http://schemas.microsoft.com/SQL/Notifications/EventNotification, utworzenie implementacji EN w oparciu o wcześniej utworzony serwis z wyborem określonych klas zdarzeń, utworzenie procedury przetwarzającej komunikaty z kolejki, aktywowanie kolejki z utworzoną wcześniej procedurą. Korzystając z Service Broken do SSMS można to zrobić bez znajomości poleceń T-SQL, korzystając głównie z GUI tego dodatku (poza oczywiście tworzeniem procedury aktywującej, którą trzeba napisać dostosowując do do konkretnej implementacji).
- EN generuje zdarzenia w postaci dokumentów XML i tworząc tabelę rejestrującą komunikaty warto utworzyć kolumnę, której zawartość będzie pobierana elementu z EVENT_INSTANCE/EventType oraz kolumnę na moment powstania zdarzenia, której zawartość pochodzi od elementu EVENT_INSTANCE/PostTime. Pierwsza informacja pozwala nam szybko zorientować się z jakim zdarzeniem mamy do czynienia a druga pozwala zgrupować szereg rekordów dotyczących tej samej sytuacji wyjątkowej. No i oczywiście kolumna na sam dokumenty XML. Jak może wyglądać jego konkretny wygląd dla poszczególnych zdarzeń można przeczytać ten dokument (jeśli ktoś lubi czytać dokumenty XSD).
- W przypadku problemów zobaczyć co zwraca zapytanie: SELECT * FROM sys.server_event_notifications. Może się okazać, że w zestawie odpowiedzi nie będzie naszego EN. Wówczas należy przyjrzeć się całej strukturze tworzonej w pkt. 4 czy nie zawiera błędów.
Podsłuchowywując :) SQL Server, szczególnie poprzez TRC_ALL_EVENTS można si wiele dowiedzieć o tym jak funkcjonuje SQL Server. Widać jak ważna jest baza master oraz jak często używany jest SHOWPLAN_XML. W SSMS wpisując polecenie: RAISERROR('Uuuuuuuuuus!", 16, 1)dowiadujemy się, że idzie za tym zdarzenie USER_ERROR_MESSAGE. Natomiast polecenie DELETE FROM [blablabla]generuje najpierw zdarzenie EXCEPTION (w SSMS, na zakładce Messages jest pierwsza linia komunikatu zawierająca treść Msg 208, Level 16, State 1, Line 1), natomiast drugim zdarzeniem jest USER_ERROR_MESSAGE i odpowiada to drugiej linii komunikatu na zakładce Messages: Invalid object name 'blablabla'. Jak to z podsłuchowywaniem bywa można się dowiedzieć rzeczy niekoniecznie fajnych. Pisałem już o tym, że postawienie bazy w tryb offline powoduje dziwne komunikaty przy generowaniu skryptów z bazy danych. Okazuje się, że ten wyjątek jest generowany stale w EN przy każdej sytuacji gdy server używa używa do jakichś celów listy baz podpiętych do server'a. Błąd jest chyba w metodzie ConnectionInfo. EN są świetnym narzędziem, które pozwala na lekką implementacje systemu wczesnego ostrzegania o sytuacjach wyjątkowych dziejących się na bazie danych obsługiwanych przez nasza aplikację. EN nie generują dodatkowego narzutu na działanie server'a ponieważ są one generowane stale niezależnie czy mamy zaimplementowane EN czy nie. Jeżeli log zdarzeń postawimy na odrębnym wolumenie niż baza produkcyjna to może to być rozwiązanie bardzo optymalne. Dla zainteresowanych podaję linki do najbardziej interesujących przypadków śledzenia sytuacji wyjątkowych przy pomocy EN: - Alertowanie zdarzenia DEADLOCK_GRAPH poprzez e-mail opisane na blogu ReSQueL.
- Alertowanie zdarzenia BLOCKED_PROCESS_REPORT opisane na blogu Tony Rogerson'a.
Hmm, udało sie zrobić notkę na blogu bez obrazków :)
|
-
Używanie aparatu danych Microsoft.Jet.OLEDB.4.0 do łączenia linked servers będących plikami DBF lub plikami Excel niejednemu dodało siwych włosów. Czasami mam wrażenie, że aparat ten ma jedną konstrukcję try-catch wyglądającą mniej-więcej tak: using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace jet
{
class Program
{
static void Main(string[] args)
{
try
{
flyByJet4();
}
catch
{
Console.WriteLine("Unknown error!");
}
}
}
}
|
Szereg doświadczeń z tym aparatem oraz poszukiwanie możliwych rozwiązań problemów przy uruchomieniu dolinkowanego serwera do instancji SQL Server ostatecznie doprowadziło do zbioru zasad, którymi należy się kierować przy korzystaniu z niego. - Oczywiste. Microsoft.Jet.OLEDB.4.0 musi być zainstalowany na komputerze, na którym stoi SQL Server.
- Mniej oczywiste. Jeżeli mamy konstrukcję tworzącą linked server: EXEC master.dbo.sp_addlinkedserver @server = N'DBFFOLDER', @srvproduct=N'Jet 4.0', @provider=N'Microsoft.Jet.OLEDB.4.0', @datasrc=N'd:\mp\mrk', @provstr=N'dBASE IV' i katalog w którym znajdują się pliki wskazany zmienną @datasrc jest lokalny na komputerze, na którym zainstalowana jest instancja SQL Server odwołująca się do niego to katalog ten musi mieć dodane pełne prawa dostępu dla wszystkich użytkowników.
- Zupełnie nieoczywiste. Po spełnieniu dwóch powyższych warunków należy zrestartować usługę SQL Server. Bez tej czynności każda próba odwołania do Tables zawartych w tym linked server zakończy sie komunikatem Unknown error...
Jak już się przebijemy przez to to nareszcie można stare tabele DBF śmigać na SQL Server 2008. Ale jak znam życie to pewnie niejednym mnie jeszcze Jet zaskoczy...
|
-
Ostatnio na forum wss.pl było sporo wątków na temat niejawnych konwersji dokonywanych przez SQL Server i zagrożeniach z tego płynących. Problemów związanych z niejawną konwersją dotyczyło również moje zgłoszenie na microsoft.connect.com dotyczące wyrażeń zawierających wartości typu datetime i datetime2. Ale robiąc wpis na moim blogu dotyczącym przeniesienia danych tabelarycznych ze strony HTML do tabeli SQL Server natknąłem się na problem jawnej konwersji (kolumna z poprzednim miejsce w rankingu była liczbą ujętą w nawiasy lub skrótem ' N.Rkd' gdy drużyna nie była dotąd notowana). Na początek zasadnicze pytanie: co to jest? (1)Czy to jest wyrażenie numeryczne? Tak mi się wydawało. Ale: select CAST('(1)' as numeric)zwraca zaskakujący wynik: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.
W takim razie spróbujmy zastosować funkcję ISNUMERIC do określenia czy według SQL Server jest to wyrażenie numeryczne. W BOL dla tej funkcji jest wyraźnie napisane: Determines whether an expression is a valid numeric typeKiedyś przeanalizowałem cały zestaw znaków szukając tych dla których funkcja ta zwróci wartość 1. Więc wiem, że niekoniecznie musi być to liczba. Ale: select ISNUMERIC('(1)')zwraca zero. Czyli po prostu to nie jest wyrażenie numeryczne według SQL Server. Dziwne.
|
-
Na wss.pl pojawił się wątek dotyczący przekształcenia danych tabelarycznych ze strony HTML na tabelę w bazie danych. W dyskusji wyraziłem wątpliwość co do trywialności rozwiązania ze względu na fakt, że zawartość stron HTML odbiega znacznie od poprawnych dokumentów XML (a do zapisu do bazy danych chciałem wykorzystać możliwości XML w SQL Server). Jednak jak się okazało istnieje świetny helper do dokumentów HTML, który znajduje się pod tym adresem: htmlagilitypack. Jako cel swego przekształcenia wybrałem ranking FIVB drużyn męskich. Dzięki zastosowaniu helpera przekształcenie response HTML w XML jest banalnie proste: WebClient wc = new WebClient();
byte[] bytes = wc.DownloadData("http://www.fivb.org/en/volleyball/Rankings/Rank_men_2009_11.asp");
UTF8Encoding utf8 = new UTF8Encoding();
string response = utf8.GetString(bytes);
HtmlDocument doc = new HtmlDocument();
doc.LoadHtml(response);
doc.OptionOutputAsXml = true;
doc.Save(@"d:\wymiana\fivb.xml");
|
Po przekształceniu w dokument XML i po zwinięciu nieistotnych elementów strony docieramy do istotnych danych tabelarycznych zawierających ranking:  Teraz tylko jeszcze jedno "wygładzające" przekształcenie w zupełnie czysty i czytelny dokument zawierający wyłącznie dane rankingowe: nazwa drużyny, aktualne miejsce, poprzednie miejsce oraz ilość punktów (nie jest to konieczne, SQL Serer poradziłby sobie i z takim dokumentem): HtmlDocument doc = new HtmlDocument();
doc.Load(@"d:\wymiana\fivb.xml");
HtmlNodeCollection nodes = doc.DocumentNode.SelectNodes("/span/html/body/center/table[4]/tr[1]/td[2]/table[1]/tr[5]/td[1]/table[1]/tr");
HtmlDocument output = new HtmlDocument();
HtmlNode ranking = output.CreateElement("ranking");
int index = 0;
foreach (HtmlNode node in nodes)
{
if (index > 3)
{
HtmlNode n = output.CreateElement("team");
HtmlNode name = output.CreateElement("name");
HtmlNode current = output.CreateElement("current");
HtmlNode previous = output.CreateElement("previous");
HtmlNode points = output.CreateElement("points");
name.InnerHtml = node.ChildNodes[7].InnerText;
current.InnerHtml = node.ChildNodes[3].InnerText;
previous.InnerHtml = node.ChildNodes[5].InnerText;
points.InnerHtml = node.ChildNodes[9].InnerText;
n.AppendChild(name);
n.AppendChild(current);
n.AppendChild(previous);
n.AppendChild(points);
ranking.AppendChild(n);
}
index++;
}
output.DocumentNode.AppendChild(ranking);
File.Delete(@"d:\wymiana\ranking.xml");
output.Save(@"d:\wymiana\ranking.xml");
|
I teraz możemy przepompować ranking z dokumentu XML do tabeli SQL Server: CREATE TABLE #t (html nvarchar(max))
BULK INSERT #t FROM 'D:\Wymiana\ranking.xml'
DECLARE @txt nvarchar(max) = ''
SELECT @txt = @txt + ' ' + ISNULL(html, '') FROM #t
SET @txt = SUBSTRING(@txt,2,2147483647)
DECLARE @xml xml = CAST(@txt AS xml)
DECLARE @t table(current_rank int, previous_rank int, team nvarchar(100), points numeric)
INSERT INTO @t
SELECT t.c.value('(./current)[1]', 'int') current_rank,
replace(
replace(
replace(t.c.value('(./previous)[1]', 'nvarchar(10)'),'(','')
,')',''),
'N.Rkd','') previous_rank,
t.c.value('(./name)[1]', 'nvarchar(100)') team,
t.c.value('(./points)[1]', 'numeric') total
FROM @xml.nodes('/ranking/team') t(c)
UPDATE @t SET previous_rank = (SELECT MAX(current_rank)+1 FROM @t) WHERE previous_rank = 0
SELECT previous_rank-current_rank, team from @t order by 1
DROP TABLE #t
|
Zrobiłem ten ranking i okazało się, że największy spadek w rankingu zanotowała reprezentacja Kenii, bo spadła aż o 73 miejsca. Ale nic nie byłoby w tym dziwnego gdyby nie fakt, że obecnie ma zero punktów. W takim razie ile miała w poprzednim notowaniu gdy była na 43 miejscu? Czyżby jakaś kara czy może tylko błąd wprowadzania danych? Edit:Pisałem powyżej, że kod XML otrzymany z helpera można bezpośrednio wykorzystać w kodzie T-SQL. Aby nie być gołosłownym podaje ten kod: INSERT INTO @t
SELECT t.c.value('(./td)[2]', 'int') current_rank,
replace(
replace(
replace(t.c.value('(./td)[3]', 'nvarchar(10)'),'(','')
,')',''),
'N.Rkd','') previous_rank,
t.c.value('(./td)[4]', 'nvarchar(100)') team,
t.c.value('(./td)[5]', 'numeric') total
FROM @xml.nodes('/span/html/body/center/table[4]/tr[1]/td[2]/table[1]/tr[5]/td[1]/table[1]/tr[fn:position() gt 4]') t(c)
|
Jak widać po tych przykładach przekształceń przetworzenie kodu HTML zawierającego dane tabelaryczne wcale nie jest trudne.
|
-
Właściwie to powinien być kolejny wpis do mojego dziennika pokładowego MSSQL 2008 ale ostatecznie uznałem, że dziennik pokładowy będzie pokazywał raczej dziwne przypadki użycia ;-) Wersja 2008 ma bardzo fajną nową funkcjonalność w postaci generatora listy dodawanych wierszy poprzez INSERT. Więcej na ten temat można znaleźć tutaj. Chciałem zastosować tę funkcjonalność do przetworzenia przecinkowej listy w tablicę zawierającą poszczególne elementy tej listy. Elementy tej listy miałby być wyrażeniami dowolnych typów. Skroiłem więc procedurę składowaną, która będzie przekształcała tę listę w postać dogodną do użycia przez tę funkcjonalność oraz która będzie ostatecznie wstawiała elementy tej listy do tabeli wynikowej (zakładam, że na innym poziome będzie się odbywała kontrola czy lista nie zawiera przypadkiem SQL Injection): | CREATE PROCEDURE List2Table
(
@list nvarchar(max)
)
AS BEGIN CREATE TABLE #t(value SQL_VARIANT) SET @list = '('+REPLACE(@list, ',','),(')+')' DECLARE @sql nvarchar(max) = N'INSERT INTO #t VALUES '+@list EXECUTE sp_executesql @stmt = @sql SElECT value FROM #t DROP TABLE #t END | Ale uruchomienie procedury dla przykładowych danych testowych: | DECLARE @list nvarchar(max) = N'3,getdate(),234.56,newid(),''text''' EXECUTE List2Table @list = @list | kończy sie komunikatem błędu: | Msg 206, Level 16, State 2, Line 1
Operand type clash: uniqueidentifier is incompatible with datetime (0 row(s) affected) | Co ma piernik do wiatraka? Przecież utworzyłem tabelę z kolumną typu SQL_VARIANT, która powinna “łyknąć” wszystko jak leci. W takiej sytuacji trzeba zajrzeć do planu wykonania. Ale do tego celu musimy wykorzystać przykład bez dynamicznego SQL ponieważ plan zapytania procedury z dynamicznym SQL nie daje satysfakcjonującej odpowiedzi na przyczynę takiego zachowania. Czyli na przykład dla takiego zapytania: | declare @t table (x sql_variant) insert into @t values (2),( 'abc'), (GETDATE()), (2.345) | Robiąc różne kombinacje z typami na liście doszedłem do wniosku, że działa to w ten sposób: - parser analizuje listę wyszukując w niej wyrażenie o typie z najwyższym priorytetem,
- następnie tworzy rzutowania wszystkich elementów listy na ten typ,
- a na zakończenie rzutuje uzyskane powyżej wyniki poszczególnych wartości z listy na docelowy typ kolumny w tabeli.
przy takim podejściu to rzeczywiście będą błędy. Moim zdaniem powinno być tak, że generator planu zapytania buduje rzutowania poszczególnych wartości z listy ale na typ SQL_VARIANT a dopiero potem te wartości próbuje zapisać do tabeli. SQL_VARIANT ma najwyższy priorytet w związku z tym błąd może wystąpić na próbie zapisu do tabeli a nie na etapie przygotowania wartości do zapisu ale to już zupełnie inna sytuacja. W wyniku tego jeżeli wszystkie elementy listy będą miały jednakowy typ (jakikolwiek) i będą zgodne z typem tabeli docelowej lub mające implikowany operator rzutowania wszystko działałoby “po staremu” a jednocześnie można byłoby mieć bardziej “otwartego” INSERT’a możliwością wykorzystania nowej funkcjonalności generatora rekordów.
|
-
Zgłosiłem pierwszy raz usterkę na https://connect.microsoft.com. Zgłoszenie jest tutaj. Zgłoszenie dotyczyło sytuacji gdy zapytanie ma zwrócić rezultat będący różnicą wyrażenia typu datetime2 oraz wyrażenia typu datetime. Ponieważ datetime2 ma wyższy priorytet od wyrażenia datetime to wynik powinien być typu datetime2 a szczególnie co do dokładności wyniku zapytania. Że tak nie jest wystarczy uruchomić to zapytanie: | select cast('2009-11-20 19:03:17.4030123' as datetime2) - cast(1 as datetime) | Paweł Potasiński uzupełnił mój wpis o usterce celną uwagą, że wynik tego zapytania wcale nie jest typu datetime2 lecz datetime co łatwo sprawdzić za pomocą tego zapytania: | select sql_variant_property(cast('2009-11-20 19:03:17.4030123' as datetime2) - cast(1 as datetime), 'BaseType'); | Niestety ale usterka została została zamknięta ze statusem “Według projektu”. Jeżeli uważasz, że tak nie jest to zagłosuj na “Ocenione jako ważne” dla tej usterki.
|
-
Na wss.pl pojawił się wątek dotyczący działań na typach datetime.
Jeden z uczestników dyskusji stwierdził, że operacja odejmowania dwóch wartości datetime daje w wyniku czas.
Jak pokazałem, zapytanie
SELECT GETDATE() - GETDATE()
|
daje w wyniku 1900-01-01 00:00:00.000 czyli nadal typ datetime (należało się tego spodziewać) a dopiero po jawnym zrzutowaniu na float widzimy numeryczną reprezentację z różnicy dat (dlaczego nie na time, o tym mała uwaga na końcu notki). Ale pomyślałem sobie, że w pewnych warunkach wynik może być dodatni lub ujemny i to niedeterministycznie. Postanowiłem sprawdzić jak jest naprawdę.
Zrobiłem sobie funkcję:
ALTER FUNCTION retard
(
)
RETURNS datetime
AS
BEGIN
DECLARE @i int = 0
WHILE (@i < 10000000) set @i = @i+1
RETURN '1900-01-01 00:00:00.000'
END
|
i zrobiłem zapytanie:
select GETDATE()
select dbo.retard()
select GETDATE()
|
Na moim komputerze opóźnienie było kilkusekundowe. Wtedy zrobiłem kolejne zapytanie:
| SELECT GETDATE() - dbo.retard() - GETDATE() |
Ale tym razem wynik był zerowy (czyli 1900-01-01 00:00:00.000).
Hmm, dziwne. Po obejrzeniu planu zapytania widać, że dwa odwołania
do funkcji getdate() generują ConstExpr1001 i ConstExpr1002 ale są one
wartościowane w bliskim sobie czasie i server taktuje je jako stałe
znane mu w czasie wykonania i w związku z tym wartościowane przed
wartościowaniem innym składników zapytania.
Skoro tak to opakujmy funkcję getdate() o tak:
CREATE FUNCTION myGetdate
(
)
RETURNS datetime
AS
BEGIN
RETURN getdate()
END
|
i zróbmy zapytanie:
select dbo.mygetdate() - dbo.retard() - dbo.mygetdate()
|
Oooo i teraz jest efekt!
I widać z tego, że przy prostych operatorach arytmetycznych
wartościowanie jest w kolejności wystąpienia (bo wynik jest mniejszy od
1900-01-01 00:00:00.000) . Hmm, skoro tak to zróbmy tak:
| select dbo.mygetdate() - cast((dbo.retard() + dbo.mygetdate() ) as int)
|
Tym razem wynik jest większy od 1900-01-01 00:00:00.000 czyli
drugi człon odejmowania jest wartościowany wcześniej. Ale nie
ustając w poszukiwaniach zróbmy jeszcze takie zapytanie:
select dbo.mygetdate() - cast((dbo.retard() + dbo.mygetdate() ) as datetime)
|
Ale tym razem mimo, że spodziewalibyśmy się że drugi człon odejmowania będzie
wartościowany wcześniej to wynik odejmowania jest mniejszy od
1900-01-01 00:00:00.000.
Zaglądając do planu zapytania widzimy, że
mimo zastosowaliśmy jawne rzutowanie to parser zignorował je ponieważ
wiedział, że nie ma takiej potrzeby skoro wszystkie składniki są typu
datetime.
Jak widać po tych rozważaniach wynik zapytania będącego treścią tego wpisu jest trudny do przewidzenia. A przy okazji widać, że
XML plan prawdę Ci powie ;-)Rzutowanie różnicy dat na typ time.Typ time ma dziedzinę wartości (dla maksymalnego rozmiaru time(7)) od 00:00:00.0000000 do 23:59:59.9999999 wobec tego nie istnieje coś takiego jak ujemny czas. Rzutowanie typu datetime na time zwraca z rezultatu datetime jedynie część informującą o czasie.Wobec tego zapytanie
SELECT cast(getdate() - (getdate()+1) AS time(7))
|
Zwróci wynik 00:00:00.0000000, podobnie jak zapytanie SELECT cast((getdate()+1) - getdate() AS time(7))
|
Ale zapytanie SELECT cast(getdate() - (getdate()+0.3333333333333) AS time(7))
|
zwróci wynik 16:00:00.0030000 podczas gdy zapytanie SELECT cast((getdate()+0.3333333333333) - getdate() AS time(7))
|
zwróci wynik 07:59:59.9970000. Dlatego operując na interwałach daty i czasu należy jednak polegać na funkcji DATEDIFF. Aby się przekonać co do następstwa dat (czyli wiedzieć czy tyle czasu upłynęło czy tyle czasu upłynie) wystarczyłoby wiedzieć jaki jest znak wyniku tej funkcji dla największej granulacji skali czasowej, czyli dla mikrosekund. Czyli możemy zbudować zapytanie: DECLARE @interval float = -0.000001
DECLARE @d1 datetime = getdate()
DECLARE @d2 datetime = getdate()+@interval
DECLARE @diff int = DATEDIFF(MICROSECOND, @d1, @d2)
IF (@diff > 0) print 'Trzeba czekać...'
ELSE print 'Minęło...'
|
I wszystko było pięknie dopóki nie ustawimy absolutnej wartości zmiennej @interwal na większą od 0.02485517 (tak wyszło z moich doświadczeń chociaż teoretycznie powinno to być więcej niż 0.0248551348032). Wówczas cała zabawa kończy się komunikatem Msg 535, Level 16, State 0, Line 12
The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.
|
Co jest oczywiste ze względu na fakt, że funkcja DATEDIFF jest typ int a zwracany wynik jest w mikrosekundach (dzień zawiera 86400000000 mikrosekund co jest poza zakresem tego typu).
|
-
Gdy zaistnieje potrzeba zeskryptowania uprawień określonego użytkownika to jednego można być pewnym, co już opisałem w poprzednim wpisie. Nie ma co liczyć na opcję Generate script. Bo nawet gdyby działała poprawnie to i tak niema takiej możliwości. Nic tylko pisać własny skrypt (albo jako proponował Paweł Potasiński sięgnąć po PowerShell'a, ale dla kogoś kto go nie zna jest to wyzwanie). A skrypt T-SQL dla tego zadania wcale nie jest trudny. DECLARE @user sysname = 'fajny_user'
DECLARE @tekst varchar(max)
SET NOCOUNT ON
SET @tekst = (
SELECT CHAR(10)+
dp.state_desc COLLATE Latin1_General_CI_AS+' '+
dp.permission_name + ' ON ' +
ISNULL(QUOTENAME(s.name)+'.','')+ QUOTENAME(o.name) + ' TO ' +
QUOTENAME(su.name)+';'
FROM sys.database_permissions dp
INNER JOIN sys.sysusers su on dp.grantee_principal_id = su.uid
INNER JOIN sys.objects o on dp.major_id = o.object_id
INNER JOIN sys.schemas s on o.schema_id = s.schema_id
WHERE su.name = @user
ORDER BY o.name
FOR XML PATH ('' )
)
PRINT @tekst
|
Przy okazji można zobaczyć jak użyteczna jest klauzula "FOR XML PATH". I na zakłdace Messages mamy wszystko co trzeba :-) Edit:Po uwzględnieniu słusznej uwagi Pawła Potasińskiego kod będzie uboższy o jeden INNER JOIN: DECLARE @user sysname = 'fajny_user'
DECLARE @tekst varchar(max)
SET NOCOUNT ON
SET @tekst = (
SELECT CHAR(10)+
dp.state_desc COLLATE Latin1_General_CI_AS+' '+
dp.permission_name + ' ON ' +
ISNULL(QUOTENAME(OBJECT_SCHEMA_NAME(o.object_id))+'.','')+ QUOTENAME(o.name) + ' TO ' +
QUOTENAME(su.name)+';'
FROM sys.database_permissions dp
INNER JOIN sys.sysusers su on dp.grantee_principal_id = su.uid
INNER JOIN sys.objects o on dp.major_id = o.object_id
WHERE su.name = @user
ORDER BY o.name
FOR XML PATH ('' )
)
PRINT @tekst
|
Teraz wystarczy dodać to do własnego SSMS addin'a i jedno skryptowanie mamy "z głowy" ;-)
|
|
|
|