sobota, 13 grudnia 2008

MCTS: Microsoft SQL Server 2008, Database Development

Wczoraj otrzymałem miłego maila z Microsoftu. Mój wynik beta egzaminu 71-433 został oceniony pozytywnie. Tak więc od 9 paźniernika posiadam tytuł MCTS SQL Server 2008, Database Development.

piątek, 5 grudnia 2008

Czy ten łańcuch znaków jest liczbą?

Problem z pozoru banalny. Sprawdzenie czy łańcuch znaków jest liczbą. Jak to zrobić? Większość osób z pewnością od razu skorzystałaby z funkcji ISNUMERIC. Problem polega na tym, że ta funkcja sprawdza czy dany łańcuch jest typu numerycznego, a nie czy jest liczbą. Funkcja ISNUMERIC zwróci wartość 1 dla następujących wartości '+', '-', '101.5E9', '$10', oraz innych wartości pieniężnych zawierających odpowiednie znaki reprezentujące waluty (http://msdn.microsoft.com/en-us/library/ms188688.aspx).

Jakie mogą być konsekwencje sprawdzania łańcucha znaków tylko za pomocą ISNUMERIC? Ano takie, że funkcja, procedura czy tam inny kod napisany w SQL się wysypie. Poniżej kilka przykładów, w których ISNUMERIC za każdym razem zawraca wartość 1.

DECLARE @STRING AS VARCHAR(1)
SET @STRING = '+'
IF ISNUMERIC(@STRING) = 1
SELECT CONVERT(DECIMAL,@STRING) + 10
GO


Msg 8115, Level 16, State 6, Line 5
Arithmetic overflow error converting varchar to data type numeric.


DECLARE @STRING AS VARCHAR(3)
SET @STRING = '$10'
IF ISNUMERIC(@STRING) = 1
SELECT CONVERT(INT, @STRING) + 10
GO


Msg 245, Level 16, State 1, Line 5
Conversion failed when converting the varchar value '$10' to data type int.


DECLARE @STRING AS VARCHAR(10)
SET @STRING = '101.5E9'
IF ISNUMERIC(@STRING) = 1
SELECT CONVERT(INT, @STRING) + 10
GO


Msg 245, Level 16, State 1, Line 5
Conversion failed when converting the varchar value '101.5E9' to data type int.


Jak więc sprawdzić czy łańcuch znaków jest wartością numeryczną, oraz jest typu, który pożądany jest w dalszych obliczeniach?

Ano można skorzystać w tym celu np. z wyrażeń regularnych. Jako, że wyrażenia regularne nie występują póki co w SQL Server, potrzebna będzie funkcja napisana w CLR.

W poprzednim poście (
http://updatedev.blogspot.com/2008/11/transformacja-wyniku-zapytania-do.html) zaprezentowałem bibliotekę CLTToolsSuite. Rozbuduję ją teraz o kolejną funkcję, która będzie weryfikowała łańcuch znaków za pomocą wyrażeń regularnych.

<SqlFunction(isdeterministic:=True, dataaccess:=DataAccessKind.None)> _
Public Shared Function RegEx(ByVal input As SqlString, ByVal pattern As SqlString) As SqlBoolean
Dim RegExpr As New Regex(pattern)
Dim match As Match

If input.IsNull Or pattern.IsNull Then
Return False
End If

match = RegExpr.Match(input)
If match.Success Then
Return True
Else
Return False
End If
End Function

Funkcja ta akceptuje dwa parametry. Łańcuch znaków do zweryfikowania oraz szablon wyrażenia regularnego. W przypadku gdy łańcuch znaków jest poprawny funkcja zwraca 1, w każdym innym przypadku 0.

Po skompilowaniu biblioteki pora ja zarejestrować i utworzyć nową funkcję.

IF OBJECT_ID('fn_RegEx') IS NOT NULL
drop function fn_RegEx

IF EXISTS(SELECT * FROM sys.assemblies WHERE name = 'CLRToolsSuite')
drop assembly CLRToolsSuite
go

CREATE ASSEMBLY CLRToolsSuite
from 'C:\CLRToolsSuite\CLRToolsSuite\bin\Release\CLRToolsSuite.dll'
WITH PERMISSION_SET = SAFE;
GO

CREATE FUNCTION dbo.fn_RegEx(@input as nvarchar(max), @pattern as nvarchar(max))
RETURNS BIT
EXTERNAL name CLRToolsSuite.[CLRToolsSuite.CLRToolsSuite].RegEx;
GO

I teraz krótki test, w którym funkcja ISNUMERIC została zastąpiona przez nową funkcję fn_RegEx.

DECLARE @STRING AS VARCHAR(5)
SET @STRING = '+'
IF dbo.fn_RegEx(@STRING, '^[0-9]+(\.*[0-9]*)$') = 1
SELECT CONVERT(DECIMAL,@STRING) + 10
GO

DECLARE @STRING AS VARCHAR(5)
SET @STRING = '$10'
IF dbo.fn_RegEx(@STRING, '^[0-9]+$') = 1
SELECT CONVERT(INT,@STRING) + 10
GO

DECLARE @STRING AS VARCHAR(10)
SET @STRING = '101.5E9'
IF dbo.fn_RegEx(@STRING, '^[0-9]+$') = 1
SELECT CONVERT(INT,@STRING) + 10
GO

Jak widać z powyższych fragmentów kodu coś co może na pierwszy rzut oka wydawać się banalne, wcale takim w rzeczywistości być nie musi. Warto pamiętać więc co funkcja ISNUMERIC robi i jakie są jej ograniczenia.

wtorek, 11 listopada 2008

Transformacja wyniku zapytania do innego formatu

Dzisiaj zajmę się tematem formatowania wyniku zapytania. Czasami zachodzi potrzeba aby wynik zapytania był w jakimś formacie, który łatwiej umożliwia wizualizację danych. Tym formatem może być HTML, XHTML. W poście tym przedstawię dwie techniki formatowania / transformowania wyniku zapytania.

HTML w T-SQL

Pierwszą z technik, którą przedstawię zaprezentował na swoim blogu Tony Rogerson. Bardzo ciekawa technika umożliwiająca otrzymanie wyniku zapytania jako dokumentu HTML. Poniższe zapytanie korzysta z bazy danych Northwind.

declare @body varchar(max)

set @body = cast( (
select 'company_name' as "
td/@class",td = CompanyName + '</td><td>' + ContactName + '</td><td>' + ContactTitle + '</td><td>' + Address +'</td><td>' + City + '</td><td>' + PostalCode + '</td><td>' + Country
from (
select CompanyName, ContactName, ContactTitle, Address, City, PostalCode, Country


from customers
) as d
for xml path( 'tr' ), type ) as varchar(max) )


set @body = '<html><head><style>'
+ 'TABLE {border: 1px; width:600px}'
+ 'TH {border-bottom: 5px solid red; border-top: 5px solid black; white-space:nowrap}'
+ 'TD {font-size: 12px; font-family:verdana; white-space: nowrap}'
+ '.table_header {font-family: Verdana; font-size: 14px;}'
+ '.company_name {color:black; font-weight:bold;}'
+ '</style></head><body>'
+ '<h2>Northwind Customers</h2><table cellspacing="0">'
+'<trclass="table_header"><th>CompanyName</th><th>ContactName</th><th>Title</th><th>Address</th>' +'<th>City</th><th>PostalCode</th><th>Country</th></tr>' + replace( replace( @body, '&lt;', '<' ), '&gt;', '>' ) + '</table></body></html>'

Powyższe zapytanie tworzy łańcuch znaków, będący dokumentem HTML. Jak widać poza znacznikami, można też utworzyć style CSS, które formatują dokument.

Aby zobaczyć dokument wynikowy wystarczy wykonać poniższe zapytanie.

select CONVERT(xml, @body)

Technika ta wykorzystuje tylko i wyłącznie T-SQL. Wszystkie znaczniki oraz style są umieszczone w łańcuchach znaków. Nie ma potrzeby odwołań do zewnętrznych komponentów. Minusem tej techniki jest to, że w przypadku bardziej skomplikowanego dokumentu można bardzo łatwo zapomnieć o domknięciu jakiego znacznika. Poza tym czytelność zapytania także ulega degradacji wraz ze wzrostem jego objętości.

HTML z użyciem CLR, XML, XSLT

Ten sam rezultat można otrzymać korzystając z innej techniki, a mianowicie zwrócić wynik zapytania jako dokument XML i wykonać na nim transformaty za pomocą XSLT. Jako, że SQL Server nie umożliwia wykonania tego typu operacji należy skorzystać z możliwości jakie niesie ze sobą CLR.

W celu wykonania transformacji utworzę funkcję, która jako parametry będzie przyjmowała dokument XML, dokument XSLT. Funkcja ta zwracać będzie dokument w innym formacie. Celowo użyłem sformułowania „w innym formacie”, gdyż wartością zwróconą przez tą funkcję nie musi być dokument HTML. Kilka słów więcej na ten temat będzie na zakończenie artykułu.

Tak więc korzystając z Visual Studio, tworzę nowy projekt, którym jest nowa klasa.

Poniżej cały kod klasy, napisanej w VB.NET, zawierającej funkcję, która transformuje wynik zapytania.
Imports System
Imports System.Data.SqlTypes
Imports System.IO

Imports System.Xml
Imports System.Xml.xsl
Imports Microsoft.SqlServer.Server

Partial Public Class CLRToolsSuite
<SqlFunction(isdeterministic:=True, DataAccess:=DataAccessKind.None)> _

Public Shared Function XSLTransform(ByVal xmlDoc As SqlXml, ByVal xslDoc As SqlXml) As SqlXml

Dim memory_stream As New MemoryStream()
Dim lxmlDoc As New XmlDocument
Dim lxslTransform As New XslCompiledTransform
Dim lxmlWriter As New XmlTextWriter(memory_stream, System.Text.Encoding.UTF8)
Dim xmlReader As XmlTextReader

lxmlDoc.InnerXml = xmlDoc.Value
lxslTransform.Load(xslDoc.CreateReader)
lxslTransform.Transform(lxmlDoc, lxmlWriter)
memory_stream.Seek(0, SeekOrigin.Begin)
xmlReader = New XmlTextReader(memory_stream)

Return New SqlXml(xmlReader)

End Function
End Class

Teraz potrzeba tylko skompilować projekt. Mając utworzoną bibliotekę .dll możemy ją zarejestrować w SQL Server.

Na początek jednak trzeba włączyć możliwość korzystania z CLR. Domyślnie ta usługa, zresztą tak samo jak wiele innych rzeczy, w SQL Server jest wyłączona.

exec sp_configure 'clr enabled', 1
RECONFIGURE
GO

Mając włączony CLR można już zarejestrować naszą bibliotekę a także zadeklarować nową funkcję.

CREATE ASSEMBLY CLRToolsSuite
from 'C:\CLRToolsSuite\CLRToolsSuite\bin\Release\CLRToolsSuite.dll'
WITH PERMISSION_SET = SAFE;
GO

CREATE FUNCTION dbo.fn_XSLTransform(@xmldoc as xml, @xsldoc as xml)
RETURNS XML
EXTERNAL name CLRToolsSuite.[CLRToolsSuite.CLRToolsSuite].XSLTransform;
GO

Ostatnim elementem, no poza samym zapytaniem, tej układanki jest dokument zawierający style potrzebne do przekształcenia wyniku zapytania.

Nie chcę w tym miejscu wgłębiać się w XSLT. Temat ten mógłby zająć prawdopodobnie kilkanaście artykułów. Poniżej kod dokumentu XSLT, który przekształci wynik zapytania do dokumentu HTML, takiego samego jak zaprezentowany został w poprzedniej technice.

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0"xmlns:xsl="
http://www.w3.org/1999/XSL/Transform">


<xsl:template match="/">


<html>
<head>
<style>
TABLE {border: 1px; width:600px; padding: 2px;}
TH {border-bottom: 5px solid red; border-top: 5px solid black; white-space: nowrap}
TD {font-size: 12px; font-family:verdana; white-space: nowrap}
.table_header {font-family: Verdana; font-size: 14px;}
.company_name {color:black; font-weight:bold;}
</style>
</head>


<body>
<h2>Northwind Customers</h2>
<table cellspacing="0">
<tr class="table_header">
<th>Company Name</th>
<th>Contact Name</th>
<th>Title</th>
<th>Address</th>
<th>City</th>
<th>Postal Code</th>
<th>Country</th></tr>


<xsl:for-each select="xmldoc/row">
<tr>
<td class="company_name">
<xsl:value-of select="CompanyName"/>
</td>
<td>
<xsl:value-of select="ContactName"/>
</td>
<td>
<xsl:value-of select="ContactTitle"/>
</td>
<td>
<xsl:value-of select="Address"/>
</td>
<td>
<xsl:value-of select="City"/>
</td>
<td>
<xsl:value-of select="PostalCode"/>
</td>
<td>
<xsl:value-of select="Country"/>
</td>
</tr>
</xsl:for-each>
</table>
</body>
</html>
</xsl:template>
</xsl:stylesheet>

Tak oto stworzony dokument zapiszę do pliku .xslt. Równie dobrze można by go zapisać gdzieś w jakiejś tabeli w bazie danych, jednakże na potrzeby tego artykułu skorzystam z pliku, który później będzie załadowany do zmiennej.

I teraz już pora przejść do SQL-a i wykonać zapytanie.

DECLARE @XMLDOC AS XML
SELECT @XMLDOC = (SELECT CompanyName, ContactName, ContactTitle, Address, City, PostalCode, CountryFROM Customers FOR XML PATH('row'), ROOT('xmldoc'))


DECLARE @xsldoc AS XMLselect @xsldoc = xd from openrowset(BULK 'c:\xsl.xsl', SINGLE_BLOB) as import(xd)


I ponownie, aby zobaczyć rezultat wystarczy wykonać jeszcze jedno zapytanie.

select dbo.fn_XSLTransform(@xmldoc,@xsldoc)

Podsumowanie

Zarówno pierwsza jak i druga zaprezentowana technika pozwala nam uzyskać zapytanie sformatowane w postaci kodu HTML. O ile w pierwszej technice wykorzystany był tylko T-SQL o tyle druga wykorzystuje CLR i XSLT. Tak jak już wspomniałem w przypadku wykorzystania techniki tylko z T-SQL nie trudno jest o dopuszczenie się błędu w trakcie pisania znaczników HTML. W przypadku drugiej techniki, pomimo tego, że dokument XSLT ma znacznie większą objętość, to korzystając z edytora, takiego jak np. Visual Studio, znacznie trudniej o popełnienie błędu w samym dokumencie.

Inną bardzo dużą zaletą XSLT jest to, że korzystając ze stylów można utworzyć praktycznie dowolny dokument. Sam XSLT jest bardzo potężnym narzędziem, umożliwiającym skomplikowane operacji transformacji danych.


MCP x 8

No i udało mi się pozytywnie zapoczątkować kolejny rozdział pt. "certyfikacja Microsoftu". Dzisiaj zdałem 70-441 PRO: Designing Database Solutions by Using Microsoft SQL Server 2005. Pierwszy z trzech egzaminów wymaganych do uzyskania tytułu MCITP: Database Developer. Mam nadzieję, że czas będzie mi sprzyjał i do końca roku zamknę całą ścieżkę.

Tak na marginesie to był już 8 zdany egzamin.

poniedziałek, 27 października 2008

SSMS 2008

Jak to często bywa podczas udzielania pomocy innym, musiałem wygenerować trochę danych testowych. Jako, że porównywałem dwa rozwiązanie problemu, wykonałem kwerendy generując jednocześnie ich plany wykonania. I tutaj SSMS w wersji 2008 przychodzi z pomocą. W przypadku gdy wykryje brakujące indeksy, podpowiada aby je założyć.

Bardzo przyjemny dodatek, który czasami może ułatwić pracę.

sobota, 18 października 2008

Kolejność robi różnicę

Pisząc kwerendy w T-SQL bardzo często decyzja na temat optymalizacji zapytań pozostawiana jest optymalizatorowi. Prawdopodobnie w 9 na 10 przypadków optymalizator wybierze odpowiedni plan wykonania, dzięki któremu zapytanie wykona się w zadowalającym czasie.

Tak jest prawdopodobnie w 9 na 10 przypadków. Jednakże od każdej reguły są wyjątki, z którymi optymalizator póki co sobie nie radzi.


Jednym z takich wyjątków jest kolejność predykatów. Zademonstruje to na przykładzie.
Na początek utworzę funkcje, która będzie miała zauważalny czas wykonania.


CREATE FUNCTION dbo.usn_waitfor()
RETURNS INT
AS
BEGIN
DECLARE @i INT
SET @i = 1
WHILE @i < 10000000
BEGIN
SET @i = @i + 1
END
RETURN 1
END
GO


Funkcja ta zwraca wartość 1, jednakże zanim to zrobi inkrementuje 10000000 razy wartość zmiennej @i. Naturalnie funkcja ta mogłaby wykonywać każdą inną czynność, np. odczytywać dane z kilku tabel czy wykonywać operacje arytmetyczne.

Wykorzystajmy teraz tę funkcję w zapytaniu. Na początek włączę jednakże trochę statystyk.

SET STATISTICS TIME ON

W celu demonstracji potrzebna będzie jeszcze jedna zmienna.

DECLARE @i INT
SET @i = 1


A teraz bardzo proste zapytanie.

SELECT 1
WHERE @i = 1 OR dbo.usn_waitfor() = 0


Wykonując to zapytanie wynik otrzymany jest natychmiast. Statystyki w tym przypadku nic nie wykazały.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

Teraz to samo zapytanie, jednakże ze zmienioną kolejnością predykatów. Najpierw będzie sprawdzany wynik funkcji a później wartość zmiennej.

SELECT 1
WHERE dbo.usn_waitfor() = 0 OR @i = 1


Wynik zwrócony przez to zapytanie jest taki sam jak w poprzednim przypadku, natomiast czas jaki upłynął aby go otrzymać już nie. W moim przypadku na drugie zapytanie serwer potrzebował 18 sekund. Poniżej trochę statystyk.

SQL Server Execution Times:
CPU time = 12657 ms, elapsed time = 17921 ms.


Skąd więc taka różnica pomiędzy czasami wykonania obu zapytań? Otóż różnica te leży właśnie w kolejności sprawdzania predykatów. Aby nasz warunek był prawdziwy, i zapytanie zwróciło wynik, przynajmniej jeden predykatów musi być prawdziwy. W pierwszym przypadku najpierw sprawdzona została wartość zmiennej, i warunek @i = 1 był prawdziwy. Jako, że przynajmniej jeden z warunków musi być prawdą, drugi z warunków nie musiał być już sprawdzany.

W przypadku drugiego zapytania to funkcja, która potrzebuje trochę czasu aby została wykonana, brała udział w pierwszym warunku. Dopiero po jej wykonaniu sprawdzany był kolejny warunek, który zwraca prawdę, dzięki czemu otrzymany został wynik zapytania.

Jak widać warto czasami pomyśleć samemu i nie zdawać się na łaskę optymalizatora. Zmienienie kolejności predykatów może mieć kolosalne znaczenie dla czasu w jakim wykonane zostanie zapytanie.

niedziela, 12 października 2008

Beta 71-433

W mijającym tygodniu miałem okazję zdawać beta egzamin Microsoft® SQL Server® 2008, Database Development. Egzamin ten planowałem zdawać tydzień wcześniej, jednakże centrum egzaminacyjne, które wybrałem podczas rejestracji, skutecznie mi to uniemożliwiło. Ale czego można oczekiwać po firmie, która ma jedno stanowisko, na którym można zdawać egzaminy, a jednocześnie jest partnerem zarówno Prometrica jak i Pearson VUE. Czasami warto by było wziąć pod uwagę zadowolenie klientów, a nie tylko ilość potencjalnych złotówek wpływających na konto.

Ostatecznie do egzaminu przystępowałem w Wielkiej Brytanii. Jako, że wcześniej miałem już okazję zdawać kilka egzaminów Microsoftu, nic mnie nie zaskoczyło na 71-433. Jedyne czego żałuje to to, że nie spędziłem więcej czasu z SQL Server 2008. Dla mnie ten egzamin był bardziej rozgrzewką przed planowanymi 3 egzaminami z SQL Server 2005, a niżeli chęcią zdobycia nowych kwalifikacji. Mimo wszystko myślę, że było warto i także Microsoft będzie zadowolony z moich komentarzy do pytań. Teraz pozostaje tylko czekać kolejne 8 tygodni na wyniki.

czwartek, 25 września 2008

Dynamiczne obracanie danych

Początkowo miałem pisać dalej o optymalizacji w wykorzystaniem indeksów. Kontynuacja tematu rozpoczętego wcześniej. Dość długo się do tego zabierałem, a w między czasie pojawił się ciekawy problem na forum na stronie wss.pl.

Zanim jednak przejdę do meritum sprawy, naświetlę trochę temat, wykorzystując banalny przykład obracania danych w SQL 2005/2008 (PIVOT) oraz technikę która umożliwa osiągnięcie tego samego celu w poprzedniej wersji SQL Servera.

Na początek trochę danych.

USE TEMPDB
GO
IF OBJECT_ID('tempdb..#T1') IS NOT NULL
DROP TABLE #T1

CREATE TABLE #T1 (
CLUB NVARCHAR(40) NOT NULL,
ATTRIBUTE NVARCHAR(40) NOT NULL,
VALUE INT
)

ALTER TABLE #T1 WITH NOCHECK
ADD CONSTRAINT PK PRIMARY KEY CLUSTERED(CLUB, ATTRIBUTE)

INSERT INTO #T1
SELECT 'Diving Experience', '# members', 123 UNION ALL
SELECT 'Diving Experience', '# branches', 8 UNION ALL
SELECT 'Diving Experience', '# equipment', 100 UNION ALL
SELECT 'Global Diving', '# members', 125 UNION ALL
SELECT 'Global Diving', '# branches', 9 UNION ALL
SELECT 'My Scuba', '# members', 127 UNION ALL
SELECT 'My Scuba', '# equipment', 128 UNION ALL
SELECT 'Sharks Diving', '# members', 50 UNION ALL
SELECT 'Sharks Diving', '# equipment', 50 UNION ALL
SELECT 'Sharks Diving', '# branches', 2 UNION ALL
SELECT 'Sharks Diving', '# boats', 1


Tabela #T1 zawiera dane w następującej formie:

CLUB ATTRIBUTE VALUE
----------------- ----------- -----------
Diving Experience # branches 8
Diving Experience # equipment 100
Diving Experience # members 123
Global Diving # branches 9
Global Diving # members 125
My Scuba # equipment 128
My Scuba # members 127
Sharks Diving # boats 1
Sharks Diving # branches 2
Sharks Diving # equipment 50
Sharks Diving # members 50


Naszym celem jest przekształcenia tak danych aby każdy z atrybutów był osobą kolumną. Aby osiągnąć zamierzony cel musimy obrócić dane wokół osi, którą będzie kolumna atrybut.

Od SQL Servera w wersji 2005 mamy do dyspozycji klauzulę PIVOT.

Jako kolumny w zapytaniu, podajemy klub dla którego chcemy otrzymać nasze dane, oraz wszystkie dostępne wartości parametrów, a raczej te które nas interesują. Te same wartości parametrów muszą także występować w dalszej części zapytania.

PIVOT wykorzystuje także funkcję agregującą. W tym poniższym przykładzie będzie to MAX.

Wykonując zapytanie:

SELECT
CLUB,
ISNULL([# MEMBERS], 0) AS MEMBERS,
ISNULL([# BRANCHES], 0) AS BRANCHES,
ISNULL([# EQUIPMENT], 0) AS EQUIPMENT,
ISNULL([# BOATS], 0) AS BOATS
FROM #T1
PIVOT (MAX(VALUE) FOR ATTRIBUTE IN ([# MEMBERS], [# BRANCHES], [# EQUIPMENT], [# BOATS])) AS P

Otrzymamy następujący wynik:

CLUB MEMBERS BRANCHES EQUIPMENT BOATS
----------------- ----------- ----------- ----------- -----
Diving Experience 123 8 100 0
Global Diving 125 9 0 0
My Scuba 127 0 128 0
Sharks Diving 50 2 50 1


Każdy z atrybutów stał się teraz osobą kolumną. Teraz w razie potrzeby dane mogą być dalej przetwarzane. Wykorzystanie tabeli w postaci otwartego schematu, gdzie mamy atrybuty i ich wartości w jednym wierszu jest bardzo przydatne w przypadku gdy często zmienia się schemat danych, lub gdy też różne obiekty mają różne wartości parametrów. W SQL Server 2005/2008 ten sam cel można osiągnąć poprzez przechowywanie atrybutów obiektu w postaci XML. Jednakże to temat na inną dyskusję.

W SQL Server 2000 nie mamy do dyspozycji klauzuli PIVOT, jednakże osiągnięcie tego samego celu nie jest bardzo skomplikowane.

Poniższe zapytanie zwróci nam ten sam zbiór wyników

SELECT
CLUB,
ISNULL(MAX(CASE WHEN ATTRIBUTE = '# MEMBERS' THEN VALUE END),0) AS MEMBERS,
ISNULL(MAX(CASE WHEN ATTRIBUTE = '# BRANCHES' THEN VALUE END),0) AS BRANCHES,
ISNULL(MAX(CASE WHEN ATTRIBUTE = '# EQUIPMENT' THEN VALUE END),0) AS EQUIPMENT,
ISNULL(MAX(CASE WHEN ATTRIBUTE = '# BOATS' THEN VALUE END),0) AS BOATS
FROM #T1
GROUP BY CLUB
ORDER BY CLUB


Warto zwrócić tutaj uwagę na fakt, że w przypadku PIVOT grupowanie elementów jest ukryte. Jeżeli spojrzymy co robi optymalizator to zauważymy, że plan wykonania w obu przypadkach jest jednakowy. Co więcej zapytanie kompatybilne z SQL Server 2000 ma nieznacznie mniejszy koszt.

Może trochę nudnawe i przydługie te wprowadzenie do tematu, ale myślę, że warto.

Przejdźmy więc do meritum problemu. Jeden z użytkowników forum zapytał jak przekształcić dane z następującej postaci:

a 123
a 124
a 64
b 125
b 126
c 127
c 128
d 129
d 130
d 131

do

a 123 124 64
b 125 126
c 127 128
d 129 130 131


Na pierwszy rzut oka mogłoby to wyglądać na prosty przykład obracania danych. Jednakże na samym początku pojawią się pierwszy problem. Która kolumna będzie naszą osią. Pierwsza kolumna to unikalne elementy a druga to jakieś wartości. Brak w tym wszystkim jednej kolumny umożliwiającej nam obrócenie danych.

Dugi problem to ilość kolumn w wyniku. Z powyższego przykładu danych nie można wywnioskować ilości docelowych kolumn. Jedną z wad PIVOT jest to, że z góry musimy znać wszystkie możliwe kolumny. PIVOT nie pozwala nam tworzyć ich dynamicznie.

Na początek utwórzmy zbiór danych.

USE TEMPDB
GO

IF OBJECT_ID('tempdb..#T1') IS NOT NULL
DROP TABLE #T1

CREATE TABLE #T1 (
ID CHAR(1),
VAL INT
)

INSERT INTO #T1
SELECT 'a', 123 UNION ALL
SELECT 'a', 124 UNION ALL
SELECT 'a', 64 UNION ALL
SELECT 'b', 125 UNION ALL
SELECT 'b', 126 UNION ALL
SELECT 'c', 127 UNION ALL
SELECT 'c', 128 UNION ALL
SELECT 'd', 129 UNION ALL
SELECT 'd', 130 UNION ALL
SELECT 'd', 131


Na początek musimy rozwiązać problem kolumny, wokół której będziemy obracać nasze dane.

W tym celu z pomocą przychodzi nowa w SQL 2005 funkcja ROW_NUMBER.

IF OBJECT_ID('tempdb..#T2') IS NOT NULL
DROP TABLE #T2

;WITH CTE AS (
SELECT
ID,
VAL,
ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ID) AS RN,
'ATTR' + CONVERT(VARCHAR(2),ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ID)) AS ATTR
FROM #T1
)


Partycjonujemy nasze dane, dzięki czemu uzyskujemy maksymalną liczbę różnych parametrów. Tworzymy też dodatkową kolumnę, ATTR, która będzie naszą osią. Unikalne parametry tworzymy korzystając ponownie z ROW_NUMBER, tym razem doklejając uzyskaną wartość do attr.

Tak zmodyfikowane dane wstawiamy do tabeli tymczasowej.

SELECT * INTO #T2 FROM CTE

Zawartość naszej tabeli przedstawia się w następujący sposób.

ID VAL RN ATTR
--- ---- --- ------
a 123 1 ATTR1
a 124 2 ATTR2
a 64 3 ATTR3
b 125 1 ATTR1
b 126 2 ATTR2
c 127 1 ATTR1
c 128 2 ATTR2
d 129 1 ATTR1
d 130 2 ATTR2
d 131 3 ATTR3


Mając teraz kolumnę ATTR możemy przejść do obracania danych. Od razu napotykamy na nasz drugi problem. Ile tak naprawdę będziemy mieli różnych rodzajów atrybutów? W powyższym przykładzie jest to 3 ale może równie dobrze być ich znacznie więcej. Tak więc zwykły PIVOT nam tutaj na niewiele się zda. Musimy wykonać go dynamicznie, a raczej skonstruować zapytanie dynamicznie.

Na początek musimy zdobyć maksymalną ilość atrybutów.

W tym celu deklarujemy zmienną i pobieramy maksymalną wartość z kolumny RN

DECLARE @MAXATTR AS INT
SELECT @MAXATTR = MAX(RN) FROM #T2


Deklarujemy także kolejnych kilka zmiennych, które będą wykorzystane przy tworzeniu dynamicznego zapytania.

DECLARE @I AS int
DECLARE @SQL AS NVARCHAR(200)
DECLARE @PIVOTSQL AS NVARCHAR(200)
DECLARE @TABLESQL AS NVARCHAR(400)
DECLARE @FINALSQL AS NVARCHAR(400)


Mając to wszystko możemy wygenerować nasz dynamiczny kod.

WHILE @I <= @MAXATTR
BEGIN

SET @SQL = @SQL + ', attr' + CONVERT(NVARCHAR(4),@I)
SET @PIVOTSQL = @PIVOTSQL + '[ATTR' + CONVERT(NVARCHAR(2),@I) + '],'
SET @TABLESQL = @TABLESQL + 'ALTER TABLE #T3 ADD ATTR' + CONVERT(NVARCHAR(4),@I) + ' INT '
SET @FINALSQL = @FINALSQL + ', MAX(ISNULL(ATTR' + CONVERT(NVARCHAR(4),@I) + ',0)) AS COLUMN' + CONVERT(NVARCHAR(4),@I)
SET @I = @I + 1

END


Kilka słów o tym co dzieję się w pętli, która wykonywana jest tyle razy ile mamy unikalnych atrybutów.

Zmienna SQL przechowuje pierwszą część naszego zapytania, tzn wszystkie kolumny, które będą występowały po SELECT.

Zmienna PIVOTSQL przechowuje wszystkie wartości atrybutów, które docelowo będą naszymi kolumnami.

Zmienna TABLESQL, tworzy kod który będzie modyfikował jedną z tabel docelowych. Kilka słów więcej o niej w dalszej części.

Zmienna FINALSQL przechowuje zapytanie, które zwróci nam ostateczny wynik. Zapytanie utworzone w tej zmiennej grupuje nasze dane.

W pętli tej jednakże tworzymy tylko fragmenty zapytań, z wyjątkiem TABLESQL, które po zakończeniu pętli jest już kompletne.

Tworzymy teraz nasze ostateczne zapytania.

SET @PIVOTSQL = STUFF(@PIVOTSQL,LEN(@PIVOTSQL),1,'')
SET @SQL = @SQL + ' FROM #T2 PIVOT(MAX(VAL) FOR ATTR IN(' + @PIVOTSQL + ')) AS P '
SET @SQL = 'INSERT INTO #T3 SELECT * FROM (' + @SQL + ') AS DSQL'
SET @FINALSQL = @FINALSQL + ' FROM #T3 GROUP BY ID ORDER BY ID'


Tworzymy także kolejną tabelę tymczasową, która będzie przechowywać wynik obracania danych.

IF OBJECT_ID('tempdb..#T3') IS NOT NULL
DROP TABLE #T3

CREATE TABLE #T3 (
ID CHAR(1)
)


Tabela ta na początek posiada tylko jedną kolumnę, która zawiera unikalne elementy. W momencie kiedy ją tworzymy nie wiemy ile będziemy mieli docelowo różnych atrybutów (kolumn). Kolumny te dodamy po uruchomianiu dynamicznie wygenerowanego kodu. W każdej iteracji naszej pętli dodawana była jedna kolumna odpowiadająca wartości naszego atrybutu.

Teraz wykonujemy nasze dynamicznie utworzone zapytania.

EXEC SP_EXECUTESQL @TABLESQL

EXEC SP_EXECUTESQL @SQL

EXEC SP_EXECUTESQL @FINALSQL


Ostatnie z nich zwraca nam nasz pożądany wynik.

ID COLUMN1 COLUMN2 COLUMN3
---- ----------- ----------- -------
a 123 124 64
b 125 126 0
c 127 128 0
d 129 130 131


Jak widać nie ograniczenie jakie niesie za sobą PIVOT idzie łatwo ominąć generując kod dynamicznie.

PS. Po napisaniu oryginalnego rozwiązania postanowiłem je jeszcze trochę zoptymalizować. Oryginalny kod wymagał użycia trzech tabel tymczasowych, a jedna z nich miała dynamicznie dodawane kolumny. Wszystko to za sprawą tego, że tabela #T2 poza danymi wynikowymi posiadała także kolumnę RN, która przechowywała maksymalne wartości atrybutów dla każdej z unikalnych grup. W wyniku przekształcania danych dużo wierszy posiadało wartości NULL. Eliminowane one były dopiero w trzeciej tabeli tymczasowej.

Poniżej cały kod, delikatnie zmodyfikowany. Wynik oczywiście taki sam.

USE TEMPDB
GO

IF OBJECT_ID('tempdb..#T1') IS NOT NULL
DROP TABLE #T1

CREATE TABLE #T1 (
ID CHAR(1),
VAL INT
)

INSERT INTO #T1
SELECT 'a', 123 UNION ALL
SELECT 'a', 124 UNION ALL
SELECT 'a', 64 UNION ALL
SELECT 'b', 125 UNION ALL
SELECT 'b', 126 UNION ALL
SELECT 'c', 127 UNION ALL
SELECT 'c', 128 UNION ALL
SELECT 'd', 129 UNION ALL
SELECT 'd', 130 UNION ALL
SELECT 'd', 131
IF OBJECT_ID('tempdb..#T2') IS NOT NULL
DROP TABLE #T2
;WITH CTE AS (
SELECT
ID,
VAL,
'ATTR' + CONVERT(VARCHAR(2),ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ID)) AS ATTR
FROM #T1
)

SELECT * INTO #T2 FROM CTE

DECLARE @MAXATTR AS INT
SELECT @MAXATTR = MAX(RN) FROM (
SELECT ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ID) AS RN
FROM #T1
) AS MAXATTR
DECLARE @I AS int
DECLARE @SQL AS NVARCHAR(400)
DECLARE @PIVOTSQL AS NVARCHAR(200)
SET @SQL = 'SELECT ID '
SET @PIVOTSQL = ''
SET @I = 1
WHILE @I <= @MAXATTR
BEGIN
SET @SQL = @SQL + ', ISNULL(attr' + CONVERT(NVARCHAR(4),@I) + ', 0) AS COLUMN' + CONVERT(NVARCHAR(4),@I)
SET @PIVOTSQL = @PIVOTSQL + '[ATTR' + CONVERT(NVARCHAR(2),@I) + '],'
SET @I = @I + 1
END


SET @PIVOTSQL = STUFF(@PIVOTSQL,LEN(@PIVOTSQL),1,'')
SET @SQL = @SQL + ' FROM #T2 PIVOT(MAX(VAL) FOR ATTR IN(' + @PIVOTSQL + ')) AS P '
EXEC SP_EXECUTESQL @SQL
DROP TABLE #T1
DROP TABLE #T2

środa, 3 września 2008

Optymalizacja cz.1 – Indeksy

Wstęp
Pierwszą część dotyczącą optymalizacji baz danych rozpocznę od indeksów. Indeks to uporządkowany zbiór danych. Przykładem indeksu jest książka telefoniczna, która zawiera numery telefonów uporządkowana wg nazwiska czy nazwy firmy.


Korzystając z odpowiednich indeksów można w znaczący sposób skrócić czas wykonywania zapytania do bazy danych. Nic jednak za darmo. Indeksy mają swój koszt i muszą one być odpowiednio przemyślane. Kosztem indeksu jest miejsce na dysku oraz czas związany z operacjami modyfikacji danych (INSERT, UPDATE, DELETE).

Tak jak każda baza danych jest inna tak też nie ma uniwersalnej reguły dotyczącej indeksów. Mając na uwadze fakt, że w przypadku dodania, usunięcia lub modyfikacji danych poza aktualizacją tabeli aktualizowane są także indeksy, byłoby nie rozsądnym dodawanie zbyt wielu indeksów w bazie danych, która jest często aktualizowana. Natomiast w przypadku bazy danych, która jest tylko odczytywana i służy do produkcji raportów większa liczba indeksów niewątpliwie będzie korzystna.

A jak to wygląda w praktyce...
Nie zanudzając teorią przyjdźmy do praktycznego zastosowania. We wszystkich przykładach wykorzystywać będę bazę danych Northwind.
Aby pokazać korzyści płynące z indeksów, usunąłem z tabel istniejące już wcześniej indeksy.
W przypadku braku jakiegokolwiek indeksu skanowana musi być cała tabela.

select orderid, customerid, orderdate from orders
where OrderDate > '19970101'


Powyższe zapytanie generuje następujący plan zapytania
Zapytanie to także wymagało 20 logicznych operacji odczytu.
Utwórzmy więc indeks, który pokryje wszystkie kolumny biorące udział w zapytaniu. W zależności od tego czy baza danych jest na SQL Server 2000 czy na SQL Server 2005 mamy do dyspozycji dwa typy indeksów. W przypadku SQL Server 2000, jeżeli byśmy chcieli utworzyć indeks, który pokryłby wszystkie kolumny biorące udział w zapytaniu, wszystkie te kolumny muszą być kluczami indeksu.
Poniższy kod utworzy nam pożądany indeks.
create index idx_ncl_od on orders(orderdate, orderid, customerid)
Wadą tego indeksu jest to, że w przypadku aktualizacji wszystkie klucze muszą być odpowiednio aktualizowane.
Znacznie bardziej optymalne rozwiązanie umożliwia nam SQL Server 2005, gdzie kluczem indeksu będzie kolumna, która używana jest jako predykat, natomiast pozostałe kolumny dodamy w klauzuli include. W przypadku modyfikacji danych tylko jedna kolumna będzie powodowała dodatkowy koszt. Naturalnie wielkość indeksu na dysku będzie taka sama w przypadku obu rozwiązań. Poniżej zapytanie, które utworzy nam indeks, który pokryje wszystkie kolumny w zapytaniu.
create index idx_ncl_in_od on orders(orderdate) include(orderid, customerid)

Wywołując ponownie nasze zapytanie otrzymujemy poniższy plan wykonania.

Jak widać w tym przypadku nie skanowaliśmy już całej tabeli, a tylko część indeksu.

W przypadku pierwszego zapytania bez indeksu koniecznych było 20 logicznych operacji odczytu. W drugim przypadku było ich tylko 5.
Z uwagi na fakt, że indeks ten nie będzie już nam potrzebny w kolejnych przykładach zostanie on teraz usunięty.
drop index idx_ncl_in_od on orders.
Dotychczas korzystaliśmy tylko z indeksu nieklastrowego. Indeks nieklastrowy charakteryzuje się tym, że posiada on dane tylko z tych kolumn, które są jego kluczami lub kolumn dołączonych za pomocą instrukcji include.
Kolejnym indeksem jaki założymy na naszej tabeli będzie indeks klastrowy. Indeks klastrowy poza danymi pochodzącymi z kolumny, która jest kluczem posiada wszystkie pozostałe dane z tabeli. Indeks klastrowy to tak na prawdę dane. Dane w takim indeksie będą posortowane wg. kolumny klucza.
Poniższe zapytanie utworzy nam indeks klastrowy oraz klucz podstawowy na kolumnie OrderID.
ALTER TABLE dbo.Orders ADD CONSTRAINT
PK_Orders PRIMARY KEY CLUSTERED (OrderID)
Wykonując ponownie nasze zapytanie otrzymamy poniższy plan wykonania.
Jak widać w tym przypadku nie skanujemy już zawartości tabeli a indeks klastrowy, który zawiera wszystkie dane posortowane wg. kolumny OrderID. Przed założeniem tego typu indeksu należy się poważnie zastanowić, która z kolumn będzie jego kluczem. W tabeli możemy mieć tylko jeden indeks klastrowy. SQL Server pozwala nam ponadto utworzyć 249 indeksów nieklastrowych.

W przypadku gdy założymy na tabeli także indeks nieklastrowy, nieunikalny, klucz indeksu klastrowego będzie dodatkowym kluczem indeksu nieklastrowego. Niezależnie od tego czy czy indeks nieklastrowy jest unikalny czy nie, kolumna klucza klastrowego będzie także częścią tego indeksu. Ma to związek z faktem z tym, że w przypadku kiedy w tabeli znajduje się indeks klastrowy indeks nieklastrowy posiada do niego wskaźnik. W przypadku braku indeksu klastrowego indeks nieklastrowy zawiera wskaźnik do wiersza i pliku gdzie znajdują się dane.

Aby to zademonstrować założę teraz na tabeli indeks nieklastrowy, którego kluczem będzie kolumna OrderDate.

CREATE INDEX OrderDate ON Orders(OrderDate)

Wykonajmy teraz poniższe zapytanie i przeanalizujmy plan wykonania.

select orderid, orderdate from orders
where OrderDate > '19970101'

Mimo tego, że zakładając indeks nieklastrowy, jako klucz podaliśmy tylko kolumnę OrderDate nasze zapytanie nie wymagało skanowania całej tabeli, a raczej indeksu klastrowego. Zamiast tego wykonany został częściowy skan tabeli poprzedzony odnalezieniem pierwszego wiersza spełniającego predykat.

Dotychczasowe zapytania wykorzystywały tylko jeden z indeksów, choć tak na prawdę w ostatnim przypadku gdybyśmy nie mieli indeksu klastrowego optymalizator wybrałby skan całej tabeli, lub w przypadku wysokiej selektywności zapytania częściowy skan indeksu nieklastrowego w połączeniu z przeszukaniem wszystkich wskaźników do wierszy i plików zawierających dane.

W ten sposób doszedłem do selektywności zapytań. Czym więc jest selektywność zapytań i jak ona wpływa na wydajność? Selektywność jest to iloraz oczekiwanej liczby wierszy do wszystkich jakie znajdują się w tabeli. 

Wysoka selektywność oznacza relatywnie małą ilość wyników, natomiast niska selektywność dużą ilość wierszy w wyniku zapytania.

Poniższe zapytanie cechuje się wysoką selektywnością.

select orderid, customerid from orders
where OrderDate > '19980505'

Rezultatem tego zapytania będą tylko 4 wiersze, co stanowi 0,48% wszystkich wierszy w tabeli.

Przyjrzyjmy się teraz planowi wykonania.

Z uwagi na fakt, że nasz indeks nieklastrowy nie pokrywa wszystkich kolumn zapytania optymalizator zdecydował, najpierw o wyszukaniu rekordów spełniających nasz predykat, a później dla każdego ze znalezionych wierszy wykonał przeszukanie indeksu klastrowego w celu pobrania danych z kolumny CustomerID. Wyszukanie danych z kolumny CustomerID jest bardzo kosztowną operacją. Dla każdego ze znalezionych wierszy przeprowadzony musi być skan indeksu. Statystycznie indeks skanowany jest w 50%.

Zmienimy teraz nieznacznie zakres danych

select orderid, customerid from orders
where OrderDate > '19980504'

W wyniku tego zapytania otrzymamy 8 wierszy co stanowi 0,96% wszystkich wierszy w tabeli. Przyjrzyjmy się ponownie planowi wykonania zapytania.

Jak wspomniałem wcześniej przeszukanie całego indeksu klastrowego, lub też tabeli w przypadku jego braku, dla każdego wiersza jest bardzo kosztowną operacją. Dlatego też optymalizator zdecydował, że bardziej korzystne będzie zeskanowanie tylko raz całego indeksu klastrowego.
Na dowód tego wykonam kolejne zapytanie, które wykorzysta indeks nieklastrowy.

select orderid, customerid from orders with (index = 3)
where OrderDate > '19980504'

W wyniku tego zapytania otrzymamy poniższy, znajomy już nam plan.

W moim przypadku koszt tego zapytania to 0,023443, podczas gdy poprzedniego, w którym optymalizator sam wybrał najlepsze rozwiązanie to 0,0182691.

Zakończenie
Indeksy mogą w znacznym stopniu zoptymalizować zapytania do bazy danych. W powyższych zapytaniach nie było może to tak bardzo widoczne, gdyż głównie koncentrowałem się na tym by przedstawić jak działają indeksy i jak optymalizator je wykorzystuje. Temat indeksów poruszę ponownie w przyszłości, wtedy też o wiele bardziej widoczne będą korzyści z nich płynące a także koszt, czego w tym artykule nie poruszyłem.

poniedziałek, 4 sierpnia 2008

Usuwanie duplikatów

Czasami zdarza się tak, że w naszej bazie dancyh występują duplikaty. Nie powinno się to zdarzyc jeżeli są dobre klucze podstawowe w tabelach. Jedkaże kiedy ich nie mamy, lub kiedy dane są importowane z pliku płaskiego, który nie ma żadnej kontroli poprawności danych, musimy sie pozbyć tych nadmiernych informacji.

Istnieje kilka sposobów na usuwanie duplikatów. W zależności od tego czy uważamy za duplikat wiersz, który ma wszystkie wartości w kolumnach takie same jak inny wiersz w tabeli czy duplikatem będzie dla nas wiersz którego jedna kolumna ma taką samą wartość w innym wierszu, trzeba rozważyć różne techiniki usuwania nadmiernych danych.

Na początek stworzę przykładową tabelę, którą wypełnię danymi.

USE TEMPDB
GO


IF OBJECT_ID('tempdb..#KLIENCI') IS NOT NULL
DROP TABLE #KLIENCI

CREATE TABLE #KLIENCI (
ID INT,
NAZWA VARCHAR(50),
TELEFON VARCHAR(9)
)


INSERT INTO #KLIENCI (ID, NAZWA, TELEFON)
SELECT '1', 'CONTOSO LTD', '123456789'
UNION ALL
SELECT '2', 'MERCURY LTD', '987456321'
UNION ALL
SELECT '3', 'NOWY SWIAT', '564789321'
UNION ALL
SELECT '1', 'CONTOSO LTD', '123456789'
UNION ALL
SELECT '2', 'MERCURY LTD', '111111111'


Pobierzmy teraz zawartość tabeli.

SELECT * FROM #KLIENCI

ID NAZWA TELEFON
-------- ------------ ---------
1 CONTOSO LTD 123456789
2 MERCURY LTD 987456321
3 NOWY SWIAT 564789321
1 CONTOSO LTD 123456789
2 MERCURY LTD 111111111


Jak widać z powyższego listingu mamy dwa rodzaje duplikatów. Pierwszy dla klienta Contoso Ltd ma wszystkie wartości w kolumnach w obu wierszah identyczne. Drugi typ to klient Mercury Ltd (id = 2), który ma dwie różne wartości w kolumnie telefon.

Pierwszy typ duplikatów możemy usunąć korzystając z tabeli tymczasowej, którą wypełniamy unikalnymi wierszami. Następnie usuwamy wszystkie dane z naszej orginalnej tabeli a później kopiujemy zawartość tabeli tymczasowej do naszej tabeli pierwotnej.

SELECT DISTINCT * INTO #DUPLIKATY FROM #KLIENCI

TRUNCATE TABLE #KLIENCI

INSERT INTO #KLIENCI(ID, NAZWA, TELEFON) SELECT ID, NAZWA, TELEFON FROM #DUPLIKATY
DROP TABLE #DUPLIKATY


Pobierając zawartość naszej tabeli otrzymamy teraz następujący rezultat:

ID NAZWA TELEFON
----------- ------------ ---------
1 CONTOSO LTD 123456789
2 MERCURY LTD 111111111
2 MERCURY LTD 987456321
3 NOWY SWIAT 564789321


Jak widać pozbyliśmy się jednego wiersza dla klienta Contoso Ltd. Technika ta będzie działać zarówno na SQL Server 2000 jak i nowszych wersjach.

Znacznie bardziej efektywną metodą i pozwalającą nam usuwać duplikaty, gdzie tylko kilka kolumn ma takie same wartości dla różnych wierszy jest technika z wykorzystaniem nowej funkcji, dostępnej od SQL Server 2005, ROW_NUMBER oraz CTE.

Korzystając z funkcji ROW_NUMBER tworzymy numery kolejnych wierszy, jednocześnie partycjonując nasze wyniki. W naszym przypadku uznajemy za duplikat wiersz który ma taką samą wartość identyfikatora klienta w dwóch lub więcej wierszach. Skorzystamy więc z funkcji ROW_NUMBER oraz bedziemy partycjonować nasze wyniki w oparciu o kolumnę id.

WITH DUPLIKATY AS (
SELECT ID, NAZWA, TELEFON, ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ID) AS RN FROM #KLIENCI
)


Jeżeli byśmy wyświetlili zawartość naszego CTE, otrzymalibyśmy następujące rezultaty

ID NAZWA TELEFON RN
----------- ------------ --------- --
1 CONTOSO LTD 123456789 1
2 MERCURY LTD 111111111 1
2 MERCURY LTD 987456321 2
3 NOWY SWIAT 564789321 1


Teraz jedyne co musimy zrobić to usunąć nadmiarowe wiersze.

DELETE DUPLIKATY WHERE RN > 1

Pobieramy zawartość naszej tabeli

SELECT * FROM #KLIENCI

ID NAZWA TELEFON
----------- ------------ ---------
1 CONTOSO LTD 123456789
2 MERCURY LTD 111111111
3 NOWY SWIAT 564789321


Teraz mając usunięte duplikaty, możemy założyć klucz podstawowy w naszej tabeli.

niedziela, 3 sierpnia 2008

Importowanie danych z XML do bazy danych.

WstępAplikacje biznesowe coraz częściej wykorzystują dane pochodzących z róznych źródeł. Coraz częsniej dane te dostarczane są w formacie XML. SQL Server 2005 oferuje wiele funkcji, które umożliwiają przetwarzanie danych w formacie XML.
SQL Server 2005 potrafi przechowywać dane jako dokumenty XML, lub też przetwarzać je i przechowywac zachowując model relacyjnej bazy danych.


Praktyczne zastosowanie
Nie zanudzając dalej teorią pora przejść do konkretnego zastowanie XML w powiązaniu z SQL Server 2005. Poniższy przykład pokaże z jaką łatwością dane mogą zostać zaimportowane z pliku, lub łancucha znaków w formacie XML do tabeli bazy danych.
Poniższy przykład jako dokument XMl będzie wykorzystywał tabelę kursów walut publikowaną na stronach Narodowego Banku Polskiego.
Fragment dokumentu XML, z którego będę korzystał w dalszej częsci tego artykułu:


<tabela_kursow typ="A">
<numer_tabeli>150/A/NBP/2008</numer_tabeli>
<data_publikacji>2008-08-01</data_publikacji>
<pozycja>
<nazwa_waluty>dolar australijski</nazwa_waluty>
<przelicznik>1</przelicznik>
<kod_waluty>AUD</kod_waluty>
<kurs_sredni>1,9339</kurs_sredni>
</pozycja>
<pozycja>
<nazwa_waluty>dolar Hongkongu</nazwa_waluty>
<przelicznik>1</przelicznik>
<kod_waluty>HKD</kod_waluty>
<kurs_sredni>0,2649</kurs_sredni>
</pozycja>
<pozycja>
<nazwa_waluty>dolar kanadyjski</nazwa_waluty>
<przelicznik>1</przelicznik>
<kod_waluty>CAD</kod_waluty>
<kurs_sredni>2,0138</kurs_sredni>
</pozycja>
<pozycja>
<nazwa_waluty>dolar nowozelandzki</nazwa_waluty>
<przelicznik>1</przelicznik>
<kod_waluty>NZD</kod_waluty>
<kurs_sredni>1,5061</kurs_sredni>
</pozycja>
</tabela_kursow>


Potrzebne będą jeszcze tabele które będą przechowywać dane.

USE tempdb
GO
CREATE TABLE dbo.WALUTY (
KOD_WALUTY CHAR(3) PRIMARY KEY NOT NULL,
NAZWA_WALUTY VARCHAR(100) NOT NULL
)

CREATE TABLE dbo.KURSY_WALUT (
KOD_WALUTY CHAR(3) NOT NULL,
KURS_SREDNI MONEY NOT NULL,
DATA DATETIME NOT NULL,
PRZELICZNIK SMALLINT NOT NULL,
PRIMARY KEY (KOD_WALUTY, DATA),
FOREIGN KEY (KOD_WALUTY) REFERENCES dbo.WALUTY (KOD_WALUTY)
)


Musimy jeszcze zadeklarować zmienną, która będzie przechowywała nasz dokument XML. Zmienna ta jest typu XML.

DECLARE @XMLDATA AS XML

Aby załadować zawartość pliku XMl do naszej zmeinnej skorzystamy z funkcji OPENROWSET. W SQL Server 2005 funkcja OPENROWSET umożliwia czytanie zawartości pliku bez konieczności korzystania z tabeli. Dzięki korzystamy tylko z jednej istrukcji SELECT.

SELECT @XMLDATA = X FROM OPENROWSET(BULK 'C:\NBP.xml', SINGLE_BLOB) AS IMPORT(X).

Mając utworzone tabele oraz zadeklarowaną zmienna przechowującą zawartość pliku XML możemy przejść do przetwarzania tych danych. W tym celu wykorzystamy procedure, która jako parametr będzie przyjmowała naszą zmienną XML, a raczej dokument XML, który załadowaliśmy wcześniej do zmiennej.
Procedura przyjmuje dokument XML jako swój parametr a nie scieżkę do pliku. Jest to o tyle istotne, że procedura ta może być wykorzystana w dowolnej aplikacji, która będzie przekazyła dokument XML jako łańcuch znaków. Aplikacja taka może np codziennie pobierać dane bezpośrednio ze stron NBP, i przekazywać zawarośc dokumentu do procedury, bez konieczności materializowania pliku na dysku. O tym jak łatwo można zamienić Dataset w aplikacji .NET do łańcucha znaków kilka słów poświęcę na koniec tego dokumentu.
Nazwijmy naszą procedurę IMPORT_NBP

CREATE PROCEDURE IMPORT_NBP(
@XMLDATA XML
)
AS


Na początek musimy zadeklarować zmienną oraz tymczasową tabelę, które pomogą nam w przetwarzaniu danych.

DECLARE @DATA_PUBLIKACJI AS DATETIME

CREATE TABLE #KURSY_WALUT (
NAZWA_WALUTY NVARCHAR(100),
PRZELICZNIK SMALLINT,
KOD_WALUTY CHAR(3),
KURS_SREDNI MONEY
)


Data publikacji występuje w naszym dokumencie XMl tylko raz. Aby kod był bardziej czytelny będziemy tę wartość przechowywać w zmiennej DATA_PUBLIKACJI.
Tymczasowa tabela KURSY_WALUT będzie przechowywała tymczasowo zawartość naszych elementów XML.
W celu przypisania wartości zmeinnej DATA_PUBLIKACJI skorzystamy z funkcji value.

SELECT @DATA_PUBLIKACJI = X.value('(/tabela_kursow/data_publikacji/text())[1]', 'DATETIME') FROM @XMLDATA.nodes('.') AS IMPORT(X)

Proszę zwrócić uwagę na fakt, że funkcja value jak i wszystkiego pozostałe fukcje oraz elementy XML są pisane małymi literami. XML rozróżnia wielkość liter. Funkcja text() odczytuje zawartość elementu data_publikacji. Proszę zwrócić uwagę na index elementu [1]. Oznacza to, że funkcja value pobiera pierwszy element data_publikacji w dokumencie XML. Indeksowanie elementów rozpoczyna się od wartości 1, inaczej jak ma to miejsce w .NET Framework, gdzie indeksowanie rozpoczyna się domyślnie od 0. Jeżeli w naszym dokumecie występowałoby więcej elementów data_publikacji moglibyśmy użyć innej wartości indeksu. Zawartość naszego elementu data_publikacji odczytujemy jako DATETIME. Uzywając funkcji value musimy podać jakiego typu jest nasz element.
Mając już datę publikacji przejdźmy do odczytywanie kolejnych danych. Teoretycznie moglibyśmy to zrobić w ten sam sposób jak odczytaliśmy datę, zmieniając tylko element XML z /tabela_kursow/data_publikacji na /tabela_kursow/pozycja/nazwa_valuty. Z praktycznego punktu widzenia jest to jednak zadanie bardzo karkołomne gdyz najpierw musielibyśmy znać ilość elementów w naszym dokumencie XML a później pobierać każdy z nich z osobna.
Aby sprawić, żeby nasz kod był bardziej uniwersalny skorzystamy z dwóch dodatkowych funkcji, służących do przetwarzania dokumentów XML. Będą to funkcje query i nodes.
Poniższe zapytanie zwróci nam wszystkie elementy pozycja z osobna.

SELECT X.query('.') AS NODE FROM @XMLDATA.nodes('tabela_kursow/pozycja') AS IMPORT(X)

Teraz korzystając z kolejnego, zewnętrznego zapytania możemy pobrać wartości każdego z elementów naszego dokumentu XML. Wszystkie te dane wstawimy do naszej tabeli tymczasowej KURSY_WALUT.

INSERT INTO #KURSY_WALUT (NAZWA_WALUTY, PRZELICZNIK, KOD_WALUTY, KURS_SREDNI) (
SELECT
NODE.value('(/pozycja/nazwa_waluty/text())[1]', 'NVARCHAR(100)'),
NODE.value('(/pozycja/przelicznik/text())[1]', 'SMALLINT'),
NODE.value('(/pozycja/kod_waluty/text())[1]', 'CHAR(3)'),
REPLACE(NODE.value('(/pozycja/kurs_sredni/text())[1]', 'NVARCHAR(15)'), ',', '.')
FROM (
SELECT X.query('.') AS NODE FROM @XMLDATA.nodes('tabela_kursow/pozycja') AS IMPORT(X)
) AS ELEMENTY
)


W tym miejscu poświęcę kilka słów średniemu kursowi wymiany. Nasz dokument XML przechowuje tę wartość formie liczby zmienno-pozcyjnej, używając jako separatora dziesiętnego przecinka. SQL Server 2005 domyślnie używa jako separatora kropki. Gdybyśmy odczytali wartośc kurs_sredni jako typ MONEY w naszej tabeli znaleźlibyśmy nie 1,9339 a 19339.00. Dlatego też odczytujemy tę wartość jako typ NVARCHAR, a funkcja REPLACE zatępuje znaki [,] na [.].
W ten sposób mamy już nasze dane z pliku XML w tabeli tymczasowej.
Na koniec pozostaje nam zapisanie wszystkich dancyh do naszych tabel docelowych.
Najpierw zapisujemy dane do tabeli WALUTY, sprawdzając przy okazji czy podane waluta już nie istnieje w naszej bazie danych.

INSERT INTO dbo.WALUTY (KOD_WALUTY, NAZWA_WALUTY) (
SELECT KOD_WALUTY, NAZWA_WALUTY FROM #KURSY_WALUT
WHERE KOD_WALUTY NOT IN (SELECT KOD_WALUTY FROM dbo.WALUTY)
)


Mając uzupełnioną tabelę z walutami możemy zapisać kursy walut, przy okazji sprawdzając czy kurs waluty dla danego dnia już nie istnieje.

INSERT INTO dbo.KURSY_WALUT (KOD_WALUTY, KURS_SREDNI, DATA, PRZELICZNIK) (
SELECT KOD_WALUTY, KURS_SREDNI, @DATA_PUBLIKACJI, PRZELICZNIK FROM #KURSY_WALUT AS KW
WHERE NOT EXISTS (
SELECT KOD_WALUTY, DATA FROM KURSY_WALUT WHERE KOD_WALUTY = KW.KOD_WALUTY AND DATA = @DATA_PUBLIKACJI
)
)


Na koniec pozostaje nam usunięcie tabeli tymczasowej

DROP TABLE #KURSY_WALUT

A także ostatnie

GO

Pora teraz sprawidzić naszą procedurę.

DECLARE @XMLDATA AS XML
SELECT @XMLDATA = X FROM OPENROWSET(BULK 'C:\NBP.xml', SINGLE_BLOB) AS IMPORT(X)

EXEC IMPORT_NBP @XMLDATA

SELECT * FROM dbo.KURSY_WALUT
SELECT * FROM dbo.WALUTY


Jak już wspomniałem wcześniej procedura ta może zostać wykorzystana w aplikacji, która pobiera dane ze strony www, a później przekazuje cały dataset jako parametr w procedurze.
Zamiana całego dataset w .NET na łańcuch znaków w formacie XML to tylko jedna instrukcja:
Dataset1.GetXml


Podsumowanie
Jak widać na powyższym przykładzie w SQL Server 2005 bardzo łatwo możemy pzretwarzać dane w formacie XML. Dane nie muszą być przetwarzane w aplikacji, tylko wszystko przekazane zostaje na ‘barki’ serwera bazy danych.


Na koniec kompletny kod naszej procedury.

CREATE PROCEDURE IMPORT_NBP(
@XMLDATA XML
)
AS

DECLARE @DATA_PUBLIKACJI AS DATETIME

CREATE TABLE #KURSY_WALUT (
NAZWA_WALUTY NVARCHAR(100),
PRZELICZNIK SMALLINT,
KOD_WALUTY CHAR(3),
KURS_SREDNI MONEY
)

SELECT @DATA_PUBLIKACJI = X.value('(/tabela_kursow/data_publikacji/text())[1]', 'DATETIME') FROM @XMLDATA.nodes('.') AS IMPORT(X)

INSERT INTO #KURSY_WALUT (NAZWA_WALUTY, PRZELICZNIK, KOD_WALUTY, KURS_SREDNI) (
SELECT
NODE.value('(/pozycja/nazwa_waluty/text())[1]', 'NVARCHAR(100)'),
NODE.value('(/pozycja/przelicznik/text())[1]', 'SMALLINT'),
NODE.value('(/pozycja/kod_waluty/text())[1]', 'CHAR(3)'),
REPLACE(NODE.value('(/pozycja/kurs_sredni/text())[1]', 'NVARCHAR(15)'), ',', '.')
FROM (
SELECT X.query('.') AS NODE FROM @XMLDATA.nodes('tabela_kursow/pozycja') AS IMPORT(X)
) AS ELEMENTY
)

INSERT INTO dbo.WALUTY (KOD_WALUTY, NAZWA_WALUTY) (
SELECT KOD_WALUTY, NAZWA_WALUTY FROM #KURSY_WALUT
WHERE KOD_WALUTY NOT IN (SELECT KOD_WALUTY FROM dbo.WALUTY)
)

INSERT INTO dbo.KURSY_WALUT (KOD_WALUTY, KURS_SREDNI, DATA, PRZELICZNIK) (
SELECT KOD_WALUTY, KURS_SREDNI, @DATA_PUBLIKACJI, PRZELICZNIK FROM #KURSY_WALUT AS KW
WHERE NOT EXISTS (
SELECT KOD_WALUTY, DATA FROM KURSY_WALUT WHERE KOD_WALUTY = KW.KOD_WALUTY AND DATA = @DATA_PUBLIKACJI
)
)

DROP TABLE #KURSY_WALUT

GO