Wer sich jemals mit der Problematik beschäftigen musste, wie man ein Array bzw. Recordset an eine Stored Procedure (SP) oder Function (F) übergeben kann, wird hoffentlich bei Erland Sommarskog http://www.sommarskog.se/arrays-in-sql.html vorbeigeschaut und Lösung gefunden haben. Erland hat natürlich auch über TVP geschrieben wie über vieles andere auch, so dass ein Besuch seiner Seite mehr als empfehlenswert ist.

Vor einem Besuch besteht aber natürlich auch die Möglichkeit, diesen Artikel weiterzulesen…

Es ist keine große Angelegenheit, mit TVP zu arbeiten:

• Einen Type vom Type Table erzeugen,
• einer Variable mit diesem Type deklarieren,
• diese Variable mit Daten befüllen (alle DML Befehle)
• an eine SP oder F übergeben, die als INPUT-Parameter Definition den vorher definierten Table Type enthält

Hier wird der Table Type erzeugt (Beispiel lauffähig)

create type dbo.tabletype_param as table
(
     ID         int            
    ,Product    nvarchar(100)
    ,BBD        date default(current_timestamp) not NULL
);

die Variable deklariert

declare @param dbo.tabletype_param;

ein paar Daten eingefügt, geändert und gelöscht

insert into @param(ID, Product) values
(1, 'Corn'), (2, 'Meat'), (3, 'Beef'), (4, 'Butter'), (5, 'Olive Oil');

update @param set 
    BBD = dateadd(YY, 1, BBD);

delete @param
where
    ID = 5;

und mal nachgeschaut, ob auch alles wie erwartet vorhanden ist

select ID, Product, BBD
from @param;

Die Übergabe an eine Stored Procedure (SP) / Function (F) ist ähnlich trivial, jedoch lassen sich hier Features vermissen, die man eigentlich erwartet hätte.

SP

create proc dbo.proc_Test
    @Param dbo.tabletype_param readonly
as
...< code >;

F

create function dbo.func_Test
(
    @Param_in dbo.tabletype_param readonly
)
returns @Param_out table
(
     ID         int            
    ,Product    nvarchar(100)
    ,BBD        date default(current_timestamp) not NULL
)
as
begin
...< code >
end;

Der TVP ist readonly (muss mit diesem Schlüsselwort definiert werden) und der Table Type kann nicht als Definition der aus der Table Valued Function zurückgegebenen Variable von Typ Table verwendet werden. Der Begriff „halbherzig“ umgesetzt trifft hier meiner Meinung nach sehr gut…

So wird man bei Bedarf den Typen wieder los (Beispiel lauffähig)

if exists
(select * from sys.types where name = 'tabletype_param' and is_table_type = 1) 
drop type dbo.tabletype_param;

Als Mengengerüste für die Verwendung von TVP empfiehlt Microsoft als Faustregel:

• Bulk Insert für formatierte Daten auf dem Server mit Ausnahme weniger als 1000 Datensätze oder komplexer Weiterverarbeitung.
• Weniger als 1000 Datensätze von einem Remote Client mit TVP.
  (siehe hierzu auch tabellarische Aufstellung BOL)

Jetzt geht‘s ein bisschen ans Eingemachte, man sollte sich mit dem Thema Optimierung schon mal beschäftigt haben…

Testen ist besser als jede Faustregel, so dass man durchaus auch mit mehreren Millionen Datensätzen experimentieren kann. Dies liegt daran, dass - im Gegensatz zu einer Variablen vom Typ Table - der Optimizer nicht mit der geschätzten Anzahl von 1 Datensatz, sondern beim TVP nach der Anzahl der enthaltenen Datensätze optimiert, aber nicht nach der Verteilung der Daten. Dies ist dadurch möglich, das der Optimizer bei der erstmaligen Ausführung der SP / F den TVP „sniffen“ kann, daher gilt dies nicht für eine Variable deklariert mit Table Type/Table.

Voraussetzung für eine schnelle Weiterverarbeitung ist ein unique clustered Index oder clustered Primary Key des Table Types, auf den in der weiteren Verarbeitung zugegriffen wird.

create type dbo.tabletype_param as table
(
     ID         int primary key clustered       not NULL
    ,Product    nvarchar(100)                       NULL
    ,BBD        date default(current_timestamp) not NULL
);

Vorsicht ist jedoch geboten, wenn man versucht die Ausführung durch weitere Indizes - bei Variablen vom Typ Table und Table Types umgesetzt durch Unique Constraints - zu optimieren:

create type dbo.tabletype_param as table
(
     ID         int                             not NULL
    ,Product    nvarchar(100)                       NULL
    ,BBD        date default(current_timestamp) not NULL
    ,primary key clustered(ID) 
    ,unique(Product, ID)
    ,unique(BBD, ID)
);

Da der Optimizer den TVP statistisch nur nach Anzahl der enthaltenden Datensätze betrachtet, nicht aber nach der Verteilung der Daten, wird ein Zugriff auf solche Indizes mit einem Wert, der oft im Index vorkommt, alles andere als optimal sein.

Immer mit set statistics io on und set statistics times on die Anzahl der Reads und insbesondere die Prozessorzeit prüfen und Bedenken, in welcher Verteilung die Daten im TVP bei mehrmaliger Verwendung enthalten sein werden. Mögliche Probleme des „Parameter Sniffings„ gelten auch hier!

Im geringsten Zweifel lieber auf solche „Optimierungen“ verzichten.

Weiter geht es im nächsten Artikel mit GROUPING SETS.