Akademia SQL - Część 11: Typy i parametry tabelaryczne

Autor: Damian Widera

Opublikowano: 28 października 2008

Zawartość strony

	Typy i parametry tabelaryczne – informacje podstawowe
	Typy i parametry tabelaryczne – laboratorium
	Typy i parametry tabelaryczne – referencje

Typy i parametry tabelaryczne – informacje podstawowe

Parametry tabelaryczne są nowymi parametrami w serwerze SQL 2008, które są deklarowane i używane na bazie typów tabelarycznych. Typy tabelaryczne są z kolei nowymi typami, które może definiować użytkownik.

Parametry typu tabelarycznego pozwalają przesłać wiele rekordów w postaci tabeli do procedury składowanej lub funkcji bez konieczności przesyłania do nich wielu parametrów, tworzenia tabeli tymczasowej czy wykonywania konwersji z typu XML.

Procedury składowane, które wstawiają podczas jednego uruchomienia tylko jeden wiersz do tabeli są nieefektywne, ponieważ do wstawienia kilku – kilkudziesięciu nowych informacji muszą się uruchomić wielokrotnie. Efektem tego jest wzmożony ruch pomiędzy serwerem a aplikacją kliencką. Poza tym, każda zmiana struktury tabeli powodowałaby konieczność dokonania zmian w aplikacji klienckiej, która musiałaby (w większości przypadków) wskazywać odpowiednią liczbę parametrów oczekiwaną przez procedurę składowaną lub funkcję.

Inna metoda wykorzystywana w podobnej sytuacji polegała na utworzeniu tablicy tymczasowej, zapisaniu do niej wszystkich koniecznych informacji i przesłaniu do serwera SQL. Również i to podejście ma wiele wad:

1. Tablica tymczasowa jest tworzona po stronie aplikacji klienckiej w trakcie wykonywania programu. Serwer baz danych nie może uruchomić procedury składowanej, dopóki aplikacja kliencka nie utworzy tablicy tymczasowej, ponieważ wtedy jej wykonanie zakończyłoby się błędem.
2. Tablice tymczasowe po stronie serwera baz danych tworzone są bazie danych tempdb i są podatne na zakleszczanie czy blokowanie.
3. Tablice tymczasowe muszą być usuwane po wykonaniu wszystkich operacji w procedurze składowanej.
4. Użycie tabel tymczasowych w procedurach składowanych powoduje konieczność częstszej ich rekompilacji, co przekłada się bezpośrednio na zmniejszenie wydajności serwera.

Metoda polegająca na utworzeniu zmiennej typu XML i zapisaniu w niej całej informacji jest najbardziej elastyczna ze wszystkich omówionych dotychczas istniejących metod. Użycie je powoduje konieczność wykonania konwersji dokumentu XML tak, aby można go było użyć do wykonywania operacji DML z tablicą, do której należy zapisać informacje.

Parametry typu tablicowego wyróżniają się tym spośród omówionych metod, że są najbardziej elastyczne w użyciu. Składają się na to następujące własności parametrów tablicowych, które najważniejsze wymienione są poniżej:

1. Ich zasięg ogranicza się do procedury czy funkcji, w której są wywoływane, a po opuszczeniu której są automatyczne czyszczone.
2. Nie powodują zakleszczeń podczas inicjalizacji, czyli w trakcie wypełniania danymi.
3. Nie powodują rekompilacji procedur składowanych czy funkcji.
4. Pozwalają określić porządek sortowania czy unikalne klucze.

 Do początku strony

Typy i parametry tabelaryczne – laboratorium

Celem laboratorium jest praktyczne zapoznanie się z typami i parametrami tablicowymi. W pierwszym zadaniu pokazane zostaną metody znane ze starszych wersji systemu, w drugim zaprezentowane zostaną typy i parametry tablicowe oraz sposoby ich użycia.

Zadanie 1

  1.  Uruchom SQL Server Management Studio (SSMS) i połącz się z serwerem EVALUATION używając uwierzytelnienia WINDOWS.

  2.  Pierwszy przykład tego zadania będzie pokazywał, jak za pomocą wielokrotnej procedury składowanej dodawać informacje o nowych pracownikach. Jedno uruchomienie procedury powoduje dodanie tylko jednego pracownika do tabeli Pracownicy. Dodatkowo, po zmianie struktury tej tabeli procedura składowana musi zostać przebudowana tak, aby uwzględniała nowe informacje.

  3.  Otwórz nowe okno zapytania (skrót klawiszowy CTRL+N) i wpisz poniższy kod, który utworzy testową bazę danych i tabelę Pracownicy:

CREATE DATABASE AkademiaSQL2008

GO



USE AkademiaSQL2008

GO

CREATE TABLE dbo.Pracownik

(

    ID int NOT NULL

    ,Nazwisko nvarchar(100) NOT NULL

    ,Email nvarchar(100) NOT NULL

);

GO

  4.  Wykonaj procedurę składowaną DodajPracownika, która wstawi informacje do tabeli Pracownik:

CREATE PROCEDURE DodajPracownika

(

    @ID int

    ,@Nazwisko nvarchar(100)

    ,@Email nvarchar(100)

)

AS

BEGIN

    INSERT INTO dbo.Pracownik   

    VALUES( @ID,@Nazwisko,@Email)

END;

GO

  5.  Dodaj kilku nowych pracowników używając wykonanej wcześniej procedury i sprawdź dane zapisane w tabeli Pracownik. Zwróć uwagę, że dodanie nowego pracownika wiąże się z wykonanie procedury DodajPracownika

EXEC DodajPracownika 1,'Jan Kowalski','Jan.K@adventureworks.com'

EXEC DodajPracownika 2,'Zenon Nowak', 'Zenon.N@adventureworks.com'

EXEC DodajPracownika 3,'Piotr Wajda','Piotr.W@contoso.com'



SELECT * FROM Pracownik;

GO



ID          Nazwisko       Email

----------- -------------  -------------------------

1           Jan Kowalski   Jan.K@adventureworks.com

2           Zenon Nowak    Zenon.N@adventureworks.com

3           Piotr Wajda    Piotr.W@contoso.com



(3 row(s) affected)

  6.  Drugi przykład pokazuje, w jaki sposób można dodawać nowych pracowników przy pomocy tabeli tymczasowej. Najpierw należy wykonać nową procedurę składowaną:

CREATE PROCEDURE DodajPracownika_Tab

AS

BEGIN

    INSERT INTO dbo.Pracownik   

    SELECT * FROM #PracownikTempTable

END;

GO

  7.  Tabela tymczasowa jest wykonywana w aplikacji klienckiej, w trakcie jej działania:

CREATE TABLE dbo.#PracownikTempTable

(

    ID int NOT NULL

    ,Nazwisko nvarchar(100) NOT NULL

    ,Email nvarchar(100) NOT NULL

);

GO

Uwaga: Struktura tabeli nie jest znana w serwerze baz danych dopóki nie zostanie utworzona. Wykonanie procedury składowanej DodajPracownika_Tab zanim struktura tabeli tymczasowej #PracownikTempTable będzie znana w serwerze baz danych spowoduje błąd, jak pokazaliśmy poniżej:

Msg 208, Level 16, State 0, Procedure DodajPracownika_Tab, Line 4

Invalid object name '#PracownikTempTable'.

  8.  Dodaj nowych pracowników do tabeli tymczasowej:

INSERT INTO #PracownikTempTable

VALUES(10,'Jan Kowalski','Jan.K@adventureworks.com')

,(20,'Zenon Nowak', 'Zenon.N@adventureworks.com')

  ,(30,'Piotr Wajda','Piotr.W@contoso.com')

  9.  Uruchom procedurę składowaną DodajPracownika_Tab, która wstawi informacje do tabeli źródłowej. Sprawdź uzyskany wynik. Informacje zapisane w tablicy tymczasowej miały identyfikatory 10, 20 oraz 30, a więc tablica Pracownik będzie miała 6 wpisów:

EXEC DodajPracownika_Tab;

GO

SELECT * FROM Pracownik;

GO

ID          Nazwisko       Email

----------- -------------  ---------------

1           Jan Kowalski   Jan.K@adventureworks.com

2           Zenon Nowak    Zenon.N@adventureworks.com

3           Piotr Wajda    Piotr.W@contoso.com

10          Jan Kowalski   Jan.K@adventureworks.com

20          Zenon Nowak    Zenon.N@adventureworks.com

30          Piotr Wajda    Piotr.W@contoso.com



(6 row(s) affected)

  10.  Ostatnią metodą przedstawioną w zadaniu nr 1 jest przechowanie informacji o nowych pracownikach w zmiennej typu XML i odpowiednim jej przetworzeniu w procedurze składowanej wpisującej dane do tablicy źródłowej. Utwórz nową procedurę składowaną:

CREATE PROCEDURE DodajPracownika_XML

(

    @xml_doc XML

)

AS

BEGIN

    INSERT INTO dbo.Pracownik   

    SELECT  C.value('@ID','int') as ID

        ,C.value('@Nazwisko','nvarchar(100)') as Nazwisko

        ,C.value('@Email','nvarchar(100)') as Email

    FROM @xml_doc.nodes('//*') T(C)

END;

GO

  11.  Do zainicjalizowania zmiennej XML posłużymy się następującym kodem T-SQL:

DECLARE @xml_tab XML



SELECT @xml_tab = (

    SELECT * 

    FROM 

    (

   SELECT 100 as ID,'Jan Kowalski' as Nazwisko,'Jan.K@adventureworks.com' as Email

        UNION

        SELECT 200,'Zenon Nowak', 'Zenon.N@adventureworks.com'

        UNION

        SELECT 300,'Piotr Wajda','Piotr.W@contoso.com'

    )D  

    FOR XML RAW

);

  12.  Wykonaj procedurę składowaną podając jako parametr zmienną typu XML zadeklarowaną w punkcie powyżej i sprawdź uzyskany wynik. Informacje zapisane w zmiennej @xml_tab miały identyfikatory 100, 200 oraz 300, a więc tablica Pracownik będzie miała 9 wpisów:

EXEC DodajPracownika_XML @xml_tab;

GO

SELECT * FROM Pracownik;

GO

ID          Nazwisko            Email

----------- ---------------------------------------------------

1           Jan Kowalski        Jan.K@adventureworks.com

2           Zenon Nowak         Zenon.N@adventureworks.com

3           Piotr Wajda         Piotr.W@contoso.com

10          Jan Kowalski        Jan.K@adventureworks.com

20          Zenon Nowak         Zenon.N@adventureworks.com

30          Piotr Wajda         Piotr.W@contoso.com

100         Jan Kowalski        Jan.K@adventureworks.com

200         Zenon Nowak         Zenon.N@adventureworks.com

300         Piotr Wajda         Piotr.W@contoso.com



(9 row(s) affected)

Zadanie 2

Celem zadania jest zaprezentowanie w jaki sposób należy tworzyć i używać typów oraz parametrów tablicowych.

  1.  Uruchom SQL Server Management Studio (SSMS) i połącz się z serwerem EVALUATION używając uwierzytelnienia WINDOWS.

  2.  Otwórz nowe okno zapytania (skrót klawiszowy CTRL+N) i wpisz poniższy kod, który utworzy typ tablicowy, który będzie następnie wykorzystany jako parametr w procedurze składowanej.

CREATE TYPE Pracownik_TypTablicowy AS TABLE

(

    ID int NOT NULL

    ,Nazwisko nvarchar(100) NOT NULL

    ,Email nvarchar(100) NOT NULL

);

GO

  3.  Typ tablicowy zostaje na stałe zapisany w bazie danych AkademiaSQL2008 i jest dostępny w konsoli SSMS, jak pokazaliśmy na rysunku poniżej. W konsoli SSMS można również zaznaczyć sekcję User-Defined Table Type i z menu kontekstowego wybrać opcję New User-Defined Table Type, co pozwoli na dodanie typu tablicowego za pomocą kreatora:

  4.  Utwórz procedurę składowaną DodajPracownika_2008, która wstawi informacje o pracowniku do tabeli Pracownik. Procedura ta przyjmuje jeden parametr typu tablicowego, który musi być zadeklarowany jako READONLY:

CREATE PROCEDURE DodajPracownika_2008(@Pracownik Pracownik_TypTablicowy READONLY)

AS

BEGIN

  INSERT INTO dbo.Pracownik

  SELECT * FROM @Pracownik

END;

GO

  5.  Zadeklaruj oraz wypełnij zmienną typu tablicowego oraz uruchom procedurę składowana DodajPracownika_2008:

DECLARE @Pracownik Pracownik_TypTablicowy

INSERT INTO @Pracownik

VALUES(1000,'Jan Kowalski','Jan.K@adventureworks.com')

,(2000,'Zenon Nowak', 'Zenon.N@adventureworks.com')

,(3000,'Piotr Wajda','Piotr.W@contoso.com');



EXECUTE DodajPracownika_2008 @Pracownik;

GO

  6.  Sprawdź uzyskany wynik. Informacje zapisane w zmiennej typu tabelarycznego miały identyfikatory 1000, 2000 oraz 3000, a wiec tablica Pracownik będzie miała teraz 12 wpisów:

SELECT * FROM Pracownik;

GO

ID          Nazwisko            Email

----------- ------------------- -------

1           Jan Kowalski        Jan.K@adventureworks.com

2           Zenon Nowak         Zenon.N@adventureworks.com

3           Piotr Wajda         Piotr.W@contoso.com

10          Jan Kowalski        Jan.K@adventureworks.com

20          Zenon Nowak         Zenon.N@adventureworks.com

30          Piotr Wajda         Piotr.W@contoso.com

100         Jan Kowalski        Jan.K@adventureworks.com

200         Zenon Nowak         Zenon.N@adventureworks.com

300         Piotr Wajda         Piotr.W@contoso.com

1000        Jan Kowalski        Jan.K@adventureworks.com

2000        Zenon Nowak         Zenon.N@adventureworks.com

3000        Piotr Wajda         Piotr.W@contoso.com



(12 row(s) affected)

 Do początku strony

Typy i parametry tabelaryczne – referencje

Dodatkowe informacje na temat parametrów tabelarycznych można znaleźć w Internecie:

[1] Witryna SQL Server 2008 Jumpstart

[2] Pliki pomocy Books Online na temat: Table-Valued Parameters

Damian Widera, Project Manager & Team Lead (MCT, MCITP – DBA, MCSD.NET) Od 8 lat zajmuje się projektowaniem, tworzeniem i wdrażaniem aplikacji wykorzystujących platformę .NET, SQL Server oraz Oracle. Obecnie pracuje jako project manager dla LGBS Polska. Pracował także jako trener, programista, administrator baz danych, twórca domumentacji oraz analityk biznesowy. Aktywnie współpracuje z polskim oddziałem Microsoft publikując atykuły, webcasty oraz porady z zakresu SQL Server na stronach TechNet. Jest współautorem książki „Serwer SQL 2008. Administracja i programowanie”. Speaker na wielu konferencjach, m.in. Microsoft Heroes Happen Here, C2C, European PASS Conference, Microsoft Technology Summit, Energy Launch, TechED. Od 2004 r. posiada certyfikaty firmy Microsoft: MCT, MCITP–DBA oraz MCSD.NET. Jest współtwórcą oraz liderem jednej z najwiekszych grup pasjonatów SQL Server w Polsce – Śląskiej Regionalnej Grupy Microsoft (PLSSUG Katowice). Od listopada 2008 jest prezesem Polish SQL Server Users Group (PLSSUG) w Polsce. W styczniu 2009 nagrodzony tytułem MVP w kategorii SQL Server.

Do początku strony