MS SQL Server

Zadanie: znalezienie klucza symetrycznego, którym zaszyfrowano określone dane (SQL Server 2005).

Załóżmy, że w tabeli t:

CREATE TABLE t
(
data varbinary(500)
)

znajduje się 1 rekord z zaszyfrowaną informacją:

-- SELECT * FROM t
0x00F4938C160E8543BBE859267A7748CE01000000B748(..)

Aby wskazać klucz symetryczny, którym została zaszyfrowana informacja, trzeba posłużyć się pewną sztuczką - należy zrzutować zaszyfrowaną informację (typ varbinary) na typ uniqueidentifier:

DECLARE @guid uniqueidentifier
SELECT @guid = CAST(data as uniqueidentifier) FROM t

Po wykonaniu powyższego kodu, zmienna @guid będzie przechowywała tzw. key guid, czyli guid klucza symetrycznego. Teraz wystarczy w widoku sys.symmetric_keys (lub widokach - jeżeli w grę wchodzi kilka baz/serwerów) odnaleźć rekord ze znalezionym guidem:

SELECT name FROM sys.symmetric_keys WHERE key_guid = @guid

Zadanie rozwiązane.

Podczas migracji do wersji SQL Server 2005 trzeba zwrócić uwagę na całą masę zagadnień i często drobne, acz istotne szczegóły umykają uwadze deweloperów. Różnic, które mają wpływ na skuteczność działania kodu T-SQL, jest całkiem sporo - część podana jest w oficjalnych źródłach (m.in. tutaj). Są jednak pewne kwestie, które nie zostały przez Microsoft wyartykułowane, a mogą sprawiać problemy. Chciałbym tutaj przedstawić kilka z nich.

1. sortowanie w widokach

Mimo, że w wersji SQL Server 2000 w widokach klauzula ORDER BY także była teoretycznie niedozwolona (Msg 1033, Level 15, State 1: The ORDER BY clause is invalid in views (..)), co sprytniejsi radzili sobie z sortowaniem w taki sposób:

CREATE VIEW widok AS
SELECT TOP 100 PERCENT *
FROM tabela
ORDER BY kolumna

Taki widok enkapsulował określoną kolejność wierszy. W wersji SQL Server 2005 powyższa konstrukcja nie przyniesie pożądanego efektu w postaci posortowania recordseta (są natomiast inne sposoby - może o nich później napiszę). Jedynym prawidłowym miejscem na definiowanie klauzuli ORDER BY jest w nowej wersji zewnętrzny SELECT z widoku. Może to i uciążliwe, zwłaszcza jeżeli ktoś widokami budował logikę kolejek, ale przynajmniej spójne z dokumentacją.

2. brak możliwości sortowania po aliasie kolumny, jeżeli zastosowano także alias tabeli

Taki oto kod:

SELECT kolumna as moj_alias
FROM tabela t
ORDER BY t.moj_alias

nie zadziała w wersji 2005, a zadziała w wersji 2000. Zadziała także w wersji 2005, jeżeli pozbędziemy się aliasu t tabeli:

SELECT kolumna as moj_alias
FROM tabela
ORDER BY moj_alias

To zagadnienie może wydawać się banalne i bezproblemowe. Tak jednak nie jest - wystarczy sobie wyobrazić sytuację skomplikowanego zapytania, gdzie aliasy były używane w sposób z pierwszego przykładu. Po migracji do SQL Servera 2005 nagłe podczas wywołania zapytania pojawia się błąd (Msg 207, Level 16, State 1, Line 2 Invalid column name '[alias]'.). Ciekawe po jakim czasie przeciętny deweloper domyśli się, że chodzi o aliasy w klauzuli sortującej.

3. funkcja ISNUMERIC()

Zwróćmy uwagę na poniższe wywołanie funkcji ISNUMERIC(), która zwraca 1 dla ciągów numerycznych, a 0 - dla nienumerycznych:

DECLARE @string nvarchar(100)
SET @string = '\1982'
PRINT ISNUMERIC(@string)

W SQL Serverze 2000 wynikiem jest:

0

a w Yukonie:

1

I problem gotowy, jeżeli ktoś uznał, że string - dla którego funkcja zwróci 1 - składa się tylko z cyfr i znaków +/- lub kropki/przecinka. Dopiero w aktualizacji BOL do wersji 2005 Microsoft uzupełnił, że funkcja ISNUMERIC() w SQL Serverze 2005 zwraca 1 także dla ciągów znakowych zawierających "$", "\", "+", "-", ".", "," czy znak tabulacji. O ile uzasadnione jest, że string '22,4' czy nawet '$3' (typ money) uznany jest za numeryczny, o tyle przypadek np.'.,' lub taki, jak w powyższym przykładzie - jest już mocno dyskusyjny. Niestety, trzeba na to uważać.

Na koniec ciekawostka. Poniższy kod:

SELECT col, col
FROM ( SELECT RAND() col ) t

na SQL Serverze 2000 zwróci przykładowo:

0.45805486236289 0.65146429330668276

a na SQL Serverze 2005:

0,970028871101527 0,970028871101527

Jak widać rezultaty powstały w oparciu o zupełnie różne algorytmy procesora kwerend. I rzeczywiście tak jest - ciekawskim polecam sprawdzenie planów zapytań (SET SHOWPLAN_ALL ON) i dostrzeżenie ile razy i w jaki sposób wywoływana jest funkcja RAND().

Są dwa sensowne sposoby na pobranie resultseta zwracanego przez procedurę składowaną:

1. przez tabelę (zwykłą lub tymczasową - jak niżej w przykładzie)

CREATE TABLE #temp (kolumna typ_kolumny...)
INSERT INTO #temp EXEC procedura @parametr
SELECT kolumna FROM #temp
-- (!) w SQL Server 2005 zamiast tabeli tymczasowej można też użyć zmiennej tabelarycznej

2. przez OPENQUERY (data access musi być dozwolony)

EXEC sp_serveroption 'server', 'data access', true
SELECT kolumna FROM OPENQUERY(server, 'EXEC procedura @parametr')

Pierwszy sposób jest zdecydowanie bardziej rozpowszechniony. Ma on jednak swoje wady - podczas tworzenia tabeli tymczasowej (deklarowania zmiennej tabelarycznej) trzeba enumeratywnie zdefiniować wszystkie kolumny zwracane przez procedurę.