<\/p>\n
„Die Drei von der Tankstelle“, „Drei Engel für Charlie“ und „Die drei Fragezeichen“ haben eines gemeinsam: Es sind immer Drei. Drei, von denen jeder seine besonderen Fähigkeiten hat, und Drei, die sich perfekt ergänzen. So ist es auch beim SQL Server. Neben den gespeicherten Prozeduren und Triggern bietet der SQL Server mit den benutzerdefinierten Funktionen eine dritte Komponente, mit der Programmlogik gespeichert und wiederverwendet werden kann. Und weil aller guten Dinge nun mal drei sind, gibt es die benutzerdefinierten Funktionen auch gleich in drei verschiedenen Typen.<\/b><\/p>\n
Diese drei verschiedenen Typen heißen Inlinefunktionen, Tabellenfunktionen und Skalarfunktionen. <\/p>\n
Skalarfunktionen sind weitestgehend mit VBA-Funktionen in Access vergleichbar. Anhand einer optionalen Parameterübergabe wird ein Wert ermittelt und zurückgegeben. Die Inline- und Tabellenfunktionen gehen dort noch einen Schritt weiter, denn sie liefern nicht nur einen Wert, sondern komplette Ergebnismengen.<\/p>\n
Die ähnlichkeit zu VBA-Funktionen liegt aber nicht nur in der Ermittlung der Daten, sondern auch in ihrer Anwendung. Gerade die Verwendung eigener geschriebener Funktionen in SELECT<\/b>-Anweisungen, Access-Abfragen, Gültigkeitsregeln und Modulen ist einer der Vorteile von Access. Dieser Vorteil gilt auch für benutzerdefinierte Funktionen, denn diese sind in der SQL Server-Entwicklung universell einsetzbar. Sie können in SELECT<\/b>-Anweisungen, in Sichten, in gespeicherten Prozeduren, in Triggern und in anderen benutzerdefinierten Funktionen wie auch in Standardwerten und Einschränkungen verwendet werden und stehen somit für die Realisierung verschiedenster Anforderungen zur Verfügung.<\/p>\n
Einsatzmöglichkeiten<\/b><\/p>\n
Der Grund für den Einsatz von benutzerdefinierten Funktionen ist derselbe wie bei gespeicherten Prozeduren: Die Kapselung von Programmlogik und somit die Kapselung von Geschäftsregeln und von Regeln zur Datenkonsistenz. <\/p>\n
Zwar können Sie mit benutzerdefinierten Funktionen keine Daten manipulieren, aber im Vergleich zu gespeicherten Prozeduren sind benutzerdefinierte Funktionen flexibler einsetzbar. Im Gegensatz zu gespeicherten Prozeduren können diese nämlich auch in Sichten, SELECT<\/b>-Anweisungen oder in anderen benutzerdefinierten Funktionen verwendet werden. Insofern ist der Einsatz benutzerdefinierter Funktionen in manchen Fällen sinnvoller als der Einsatz gespeicherter Prozeduren.<\/p>\n
Für die beispielsweise oft benötigte Ermittlung der Produktbezeichnung anhand einer Produktnummer können Sie eine Skalarfunktion erstellen, die immer genau den einen Wert – die Produktbezeichnung – anhand der übergebenen Produktnummer liefert.<\/p>\n
Eine andere Skalarfunktion könnte die in der Northwind-Datenbank immer wieder notwendige Summe einer Bestellposition anhand der Spalten Einzelpreis<\/b>, Menge <\/b>und Discount <\/b>berechnen.<\/p>\n
Die Werte solcher Skalarfunktionen können Sie als Spalte in einer SELECT<\/b>-Anweisung, als Filterkriterium in einer WHERE<\/b>-Bedingung oder in einer INSERT<\/b>– oder UPDATE<\/b>-Anweisung verwenden.<\/p>\n
In SELECT<\/b>-Anweisungen und bei Datenmanipulationen stehen auch die Tabellen- und Inlinefunktionen zur Verfügung. Allerdings liefern diese Typen nicht einzelne Werte, sondern Ergebnismengen und sind somit ein guter Ersatz für Sichten oder Tabellen. <\/p>\n
Tabellen- und Inlinefunktionen bieten gerade Sichten gegenüber einen großen Vorteil: Sie unterstützen Parameter. <\/p>\n
Eine Sicht liefert immer alle Daten der in der Sicht angegebenen SQL-Anweisung. Die Datenermittlung ist starr und kann nicht durch Parameter beeinflusst werden. In Tabellen- und Inlinefunktionen hingegen sind Parameter erlaubt.<\/p>\n
Dadurch können die Daten bereits bei der Ermittlung anhand der übergebenen Parameterwerte gefiltert werden. <\/p>\n
Es ist schon ein Unterschied, ob in einer Tabellenverknüpfung von Bestellkopf und Bestellpositionen alle Bestellpositionen verknüpft werden oder dabei nur die Bestellpositionen berücksichtigt werden, die dem Wert eines übergebenen Parameters entsprechen.<\/p>\n
Sie sehen, die Einsatzmöglichkeiten sind so vielfältig wie die von VBA-Funktionen in Access. Die Verwendung benutzerdefinierter Funktionen ist für Sie insofern eigentlich nichts Neues. Bis auf die Entwicklung dieser Funktionen, denn die schreiben Sie natürlich nicht in VBA, sondern in T-SQL. Dabei sind Art und Umfang der Entwicklungsmöglichkeit abhängig vom jeweiligen Typ. <\/p>\n
Inline- und Tabellenfunktionen unterscheiden sich in der Werterückgabe gegenüber den Skalarfunktionen und die Skalar- und Tabellenfunktionen unterscheiden sich anhand der Möglichkeiten zur Ermittlung des Werts beziehungsweise der Werte von den Inlinefunktionen.<\/p>\n
Inlinefunktionen<\/b><\/p>\n
Inlinefunktionen liefern eine Ergebnismenge und können insofern wie Tabellen und Sichten – und somit fast überall – verwendet werden. Aber nur fast, denn bei der Ermittlung von Standardwerten oder der Einhaltung von Gültigkeitsregeln wird dieser Funktionstyp nicht unterstützt.<\/p>\n
Der große Vorteil einer Inlinefunktion liegt darin, dass bei der Ermittlung der Daten übergebene Parameterwerte als Filterkriterien verwendet werden können. Damit ist eigentlich auch bereits die Definition einer Inlinefunktion beschrieben. Sie besteht aus einer einzigen SELECT<\/b>-Anweisung, die anhand eines Parameterwerts gefiltert werden kann. Mehr außer dieser gibt es dort nicht. Von der Programmierung her ist die Inlinefunktion daher die einfachste Variante.<\/p>\n
Eine neue Inlinefunktion können Sie mit dem SQL Server Management Studio anlegen. Sie finden die benutzerdefinierten Funktionen unter Programmierbarkeit<\/b> innerhalb der jeweiligen Datenbank. Dort sehen Sie unter Gespeicherte Prozeduren<\/b> auch den Knoten Funktionen<\/b>, der Ihnen nach einem Klick auf den Knoten weitere Verzweigungen anzeigt. <\/p>\n
Inlinefunktionen liefern eine Ergebnismenge über eine virtuelle Tabelle und gehören somit zur Gruppe der Tabellenwertfunktionen. <\/p>\n
Durch einen Klick mit der rechten Maustaste auf den Knoten Tabellenwertfunktionen <\/b>können Sie den Befehl Neue Inline-Tabellenwertfunktion <\/b>(siehe Bild 1) auswählen. Sie erhalten auch hier – wie bei den gespeicherten Prozeduren und Triggern – ein neues Abfragefenster mit einem Vorlageskript.<\/p>\n
![\"pic01.TIF\"](\"..\/fileadmin\/_temp_\/2009_01\/UDF-web-images\/pic01_opt.jpeg\")
<\/p>\n
Bild 1: Eine neue Inlinefunktion<\/span><\/b><\/p>\n Die Definition einer neuen Funktion beginnt mit dem Befehl CREATE FUNCTION<\/b>, gefolgt vom Funktionsnamen, der immer das entsprechende Schema enthalten muss. Wie bereits bei den gespeicherten Prozeduren beschrieben, werden über das Schema eines Objekts unter anderem die Zugriffsrechte verwaltet. Sofern Sie kein detailliertes Berechtigungskonzept einsetzen, ist das Schema in der Regel dbo<\/b>. Sie sollten für eine einheitliche Basis für die Weiterentwicklung und eine spätere Konfiguration eines Berechtigungskonzepts immer dbo<\/b> angeben.<\/p>\n Eine genauere Erklärung der Berechtigungsvergabe anhand der Schemata würde allerdings den Rahmen dieses Artikels sprengen.<\/p>\n Der oder die Parameter, die in der SELECT<\/b>-Anweisung der Inlinefunktion verwendet werden, geben Sie durch Komma getrennt in Klammern hinter dem Namen der Funktion an. Dabei verwenden Sie das @-<\/b>Zeichen zur Kennzeichnung jedes einzelnen Parameters sowie den Datentyp. <\/p>\n Nach den Parametern definieren Sie mit RETURNS TABLE<\/b>, welchen Datentyp das Ergebnis der Funktion aufweist. Eine Inlinefunktion liefert eine Ergebnismenge anhand einer virtuellen Tabelle über den Datentyp TABLE<\/b>. Die Struktur dieser virtuellen Tabelle ergibt sich aus der SELECT<\/b>-Anweisung. Nach RETURNS TABLE <\/b>leiten Sie mit AS <\/b>den eigentlichen Programmcode ein, der in Klammern angegeben werden muss. Der Programmcode besteht wie bereits erwähnt nur aus einer einzelnen SELECT<\/b>-Anweisung. Eine weitere Spezifikation zur Kennzeichnung der Tabellenfunktion als Inlinefunktion ist nicht notwendig. <\/p>\n Als Beispiel sehen Sie in Listing 1 die Inlinefunktion dbo.ifArtikelEinerKategorie<\/b>, die alle aktiven oder inaktiven Artikel einer bestimmten Kategorie als Ergebnismenge liefert. <\/p>\n Listing 1: Beispiel für eine Inlinefunktion<\/p>\n Die Erklärung dieser Inlinefunktion ist recht einfach. Die SELECT<\/b>-Anweisung ermittelt alle Artikeldaten zuzüglich der Bezeichnung der Kategorie, zu der die Artikel gehören, sowie den Firmennamen des Lieferanten. Diese Ergebnismenge wird bei der Ermittlung auf eine Kategorie gefiltert, die über den Parameter @CategoryId <\/b>an die SELECT<\/b>-Anweisung übergeben wird. Zusätzlich wird über den Parameter @Discontinued <\/b>bestimmt, ob die Ergebnismenge nur die aktiven Artikel (Wert 0<\/b>) oder nur die inaktiven Artikel (Wert 1<\/b>) enthält.<\/p>\n Bei der SELECT<\/b>-Anweisung müssen Sie darauf achten, dass alle Spalten einen Namen bekommen. Dies gilt insbesondere bei der Verwendung von Konstanten oder berechneten Spalten. Hier muss zwingend ein Alias vergeben werden. Diese Inlinefunktion können Sie nun wie eine „parametrisierte Sicht“ verwenden. Folgender Aufruf liefert alle aktiven Artikel der Kategorie 1<\/b>:<\/p>\n Sie können die Inlinefunktion auch in einer Tabellenverknüpfung verwenden. Die folgende Abfrage etwa liefert alle Bestellpositionen, die inaktive Artikel der Kategorie 1 beinhalten:<\/p>\n Für einfache SELECT<\/b>-Anweisungen ist die Inlinefunktion die erste Wahl, da sie schnell programmiert und flexibel einsetzbar ist. Flexibel einsetzbar sind auch die Tabellenfunktionen – und sie sind auch flexibel in der Ermittlung der Ergebnismenge.<\/p>\n Tabellenfunktionen<\/b><\/p>\n Im Unterschied zu einer Inlinefunktion können in einer Tabellenfunktion mehrere SQL-Anweisungen zur Ermittlung der Ergebnismenge gespeichert werden. <\/p>\n Dies sagt bereits der Kontextmenübefehl des Knotens Tabellenwertfunktionen<\/b> aus: Neue Tabellenwertfunktion mit mehreren Anweisungen (siehe Bild 1). Auch dieser Befehl liefert Ihnen eine Skriptvorlage in einem neuen Abfragefenster. Diese unterscheidet sich in einigen Punkten von der der Inlinefunktion.<\/p>\n Bei einer Tabellenfunktion wird die Struktur der virtuellen Tabelle nicht anhand einer SELECT<\/b>-Anweisung bestimmt, sondern muss durch eine TABLE<\/b>-Variable explizit definiert werden. Insofern gibt es hier kein RETURNS TABLE<\/b>, sondern ein RETURNS <\/b>gefolgt von der Definition der TABLE<\/b>-Variablen.<\/p>\n Die einzelnen SQL-Anweisungen zur Ermittlung der Ergebnismenge werden in einer Tabellenfunktion im BEGIN…END<\/b>-Block zusammengefasst. Mit diesen SQL-Anweisungen ermitteln Sie nach und nach die Daten und füllen mit diesen die TABLE<\/b>-Variable. Dabei stehen Ihnen alle Möglichkeiten der T-SQL-Programmierung zur Verfügung, bis auf wenige Ausnahmen:<\/p>\n <\/p>\n Die Programmlogik wird wie bei gespeicherten Prozeduren durch den Befehl RETURN <\/b>beendet und dadurch die ermittelte Ergebnismenge an die aufrufende Instanz geliefert.<\/p>\n In Listing 2 sehen Sie die Tabellenfunktion dbo.tfMitarbeiterHierarchie<\/b>, die die Hierarchie der Mitarbeiter im Unternehmen auflistet. Als Ergänzung wird pro Mitarbeiter das Alter hinzugefügt.<\/p>\n Listing 2: Beispiel für eine Tabellenfunktion<\/p>\n Der Quellcode dieser Tabellenfunktion sieht auf den ersten Blick kompliziert aus, aber auf den zweiten Blick ist zu erkennen, dass hier lediglich Daten ermittelt und in die Rückgabe-Tabelle geschrieben werden, um danach noch per Update ergänzt zu werden. Doch der Reihe nach:<\/p>\n Am Anfang, noch vor dem einleitenden AS<\/b>, steht die Definition der TABLE<\/b>-Variablen, über die die Ergebnismenge zurückgeliefert wird. Die Definition einer TABLE<\/b>-Variablen ist identisch mit der des T-SQL-Befehls CREATE TABLE<\/b>. Innerhalb des BEGIN…END<\/b>-Blocks wird die TABLE<\/b>-Variable mit Daten gefüllt. Dabei wird die Hierarchie der Mitarbeiter über eine rekursive CTE <\/b>ermittelt und per INSERT<\/b>-Anweisung in die TABLE<\/b>-Variable geschrieben. CTE <\/b>– die Common Table Expression – gibt es seit SQL Server 2005. <\/p>\n Anschließend müssen die Datensätze in der TABLE<\/b>-Variablen noch um das Alter des jeweiligen Mitarbeiters ergänzt werden. Die Berechnung des Alters erfolgt pro Mitarbeiter in einer UPDATE<\/b>-Anweisung. Mittels RETURN <\/b>wird die benutzerdefinierte Funktion beendet und die Ergebnismenge an die aufrufende Instanz übermittelt.<\/p>\n Die in diesem Beispiel erstellte Tabellenfunktion benötigt keine Parameter. Beim Aufrufen der Funktion müssen trotzdem die Klammern mit angegeben werden, da sonst die Funktion nicht erkannt wird.<\/p>\n Die folgende Abfrage liefert die Hierarchie der Mitarbeiter inklusive des Alters des jeweiligen Mitarbeiters:<\/p>\n Sie sehen, in einer Tabellenfunktion kann einiges an Logik zur Ermittlung der Ergebnismenge implementiert werden. Dies gilt auch für die Ermittlung von nur einem Wert – und dafür gibt es die Skalarfunktionen.<\/p>\n Skalarfunktion<\/b><\/p>\n Skalar Was ist nochmal ein Skalar Nun, wozu gibt es Wikipedia: „Eine skalare Variable ist im Kontext von Programmiersprachen eine Variable, die einen einzelnen Wert speichert.“ Also eine doch durchaus treffende Bezeichnung für eine benutzerdefinierte Funktion, die als Ergebnis nur einen einzelnen Wert liefert.<\/p>\n Die Ermittlung eines solchen Werts kann ein einzelnes SELECT<\/b>-Statement, eine Konstante oder auch mehrere Zeilen Code wie bei der Tabellenfunktion sein. Art und Umfang zur Ermittlung dieses einzelnen Wertes ist Ihnen überlassen. Bei der Programmierung haben Sie dieselben Möglichkeiten, aber auch dieselben Einschränkungen, wie bei den Tabellenfunktionen.<\/p>\n Skalarfunktionen werden gerne als „gekapselte Konstanten“ verwendet, da hier lediglich eine Konstante zurückgegeben wird. Sollte sich diese Konstante mal ändern, muss sie nur an einer Stelle – in der Skalarfunktion – geändert werden.<\/p>\n Die Einsatzmöglichkeiten von Skalarfunktionen sind sehr vielfältig. Sie können als Spalten in einer SELECT<\/b>-Anweisung, in WHERE<\/b>-Bedingungen oder auch als Initialisierung von Variablen in T-SQL verwendet werden, wie auch in gespeicherten Prozeduren, Sichten, Triggern und in anderen benutzerdefinierten Funktionen.<\/p>\n Auch in Einschränkungen und Standardwerten einer Tabelle können Skalarfunktionen genutzt werden. Bevor Sie also einen Trigger für etwas ausgefallenere Standardwerte beziehungsweise Einschränkungen verwenden, sollten Sie zunächst den Einsatz einer Skalarfunktion prüfen.<\/p>\n Um eine neue Skalarfunktion zu erstellen, müssen Sie im Knoten Funktionen<\/b> aus dem Kontextmenü des Knotens Skalarwertfunktionen<\/b> den Eintrag Neue Skalarwertfunktion<\/b> anklicken. Sie erhalten auch hier wieder eine neue Abfrage mit einem Vorlageskript.<\/p>\n Die Struktur ähnelt der einer Tabellenfunktion. Auch hier definieren Sie mit der Anweisung RETURNS <\/b>den Datentyp des Rückgabewerts. Nur, dass es sich hier nicht um eine Definition einer virtuellen Tabelle, sondern um einen Datentyp für einen einzelnen Wert handelt. Dabei können Sie alle Datentypen nutzen, die der SQL Server im Bereich einer Tabellendefinition zur Verfügung stellt, mit Ausnahme der Datentypen text<\/b>, ntext<\/b>, image <\/b>oder timestamp<\/b>. Es gibt noch einen weiteren Datentyp, den Sie nicht verwenden können. Dieser steht aber auch nicht als Datentyp für Tabellen zur Verfügung, sondern nur innerhalb der T-SQL-Programmierung: der Datentyp TABLE<\/b>. So leicht ist der SQL Server nun auch wieder nicht auszutricksen.<\/p>\n In einer Skalarfunktion entwickeln Sie die Logik wie auch bei der Tabellenfunktion innerhalb eines BEGIN…END<\/b>-Blocks. Den ermittelten Wert übergeben Sie dann am Ende mit dem Befehl RETURN<\/b>. Bei einer Skalarfunktion muss die Variable, die den ermittelten Wert enthält, zwingend hinter der Anweisung RETURN <\/b>angegeben werden.<\/p>\n Im folgenden Beispiel wird eine bereits eingangs erwähnte Anforderung umgesetzt: In einer Skalarfunktion soll die Produktbezeichnung anhand der übergebenen Produkt-ID ermittelt werden. Die Umsetzung dieser Anforderung sehen Sie in Listing 3.<\/p>\n Listing 3: Eine Skalarfunktion zur Ermittlung von Produktbezeichnungen<\/p>\n Mit dieser Skalarfunktion können Sie nun in SELECT<\/b>-Anweisungen anhand der Produkt-ID die Bezeichnung ohne aufwendige Tabellenverknüpfungen ausgeben. In Bild 2 sehen Sie eine solche Anweisung einschließlich des Ergebnisses. <\/p>\n Bild 2: Eine Skalarfunktion im Einsatz<\/span><\/b><\/p>\n Dort sehen Sie auch, dass die Skalarfunktion mit dem Zusatz dbo<\/b> aufgerufen wurde. Im Gegensatz zu Tabellen- und Inlinefunktionen müssen Sie bei einer Skalarfunktion immer das Schema mit angeben, sonst wird die Funktion vom SQL Server nicht erkannt.<\/p>\n Auch das zweite eingangs erwähnte Beispiel – die Berechnung der Positionssumme – ist mit einer Skalarfunktion schnell realisiert. Listing 4 zeigt eine solche Skalarfunktion. <\/p>\n Listing 4: Berechnung der Positionssumme<\/p>\n Wenn Sie nun die Abfrage in Bild 2 mit dieser Funktion erweitern, erhalten Sie pro Bestellposition direkt die Summe. Sollte sich an der Berechnung in Zukunft etwas ändern – beispielsweise durch eine Rabattstaffel abhängig von der Menge – müssen Sie nur die Berechnung der Skalarfunktion erweitern. Abbildung 3 zeigt die erweiterte SELECT<\/b>-Anweisung inklusive Ergebnis.<\/p>\n Bild 3: Die erweiterte Skalarfunktion<\/span><\/b><\/p>\n Anhand dieser beiden doch recht bescheidenen Beispiele erkennen Sie bereits das Potenzial von Skalarfunktionen.<\/p>\n Es gibt noch eine klassische Skalarfunktion, die bei fast jedem Projekt zum Einsatz kommen sollte, in dem benutzerdefinierte Funktionen eingesetzt werden: das Ermitteln des aktuellen Datums.<\/p>\n Der SQL Server liefert anhand der Systemfunktion getdate() <\/b>den aktuellen Zeitpunkt im Format Datum und Uhrzeit<\/b>. Bei SQL-Anweisungen, die nur Daten bis zum heutigen Tag berücksichtigen sollen, kann die Funktion getdate() <\/b>aber nicht ohne Weiteres genutzt werden. Immerhin ist es ein Unterschied, ob die Daten bis zum 06.11.2008<\/b> oder bis zum 06.11.2008 23:00:00<\/b> verglichen werden. Um es genau zu nehmen, ist das ein Unterschied von 23 Stunden. Bei einer Umsatzauswertung wäre dies schlichtweg falsch, da der Umsatz des fast kompletten Tags in der Auswertung enthalten wäre. Wenn schon also ein Zeitpunkt des heutigen Tages, dann bitte den jüngsten, also 06.11.2008 00:00:00<\/b>.<\/p>\n Was also liegt näher, als in einer Skalarfunktion den Wert der SQL Server-Funktion getdate() <\/b>zu ermitteln und diesen Wert so aufzubereiten, dass er die Uhrzeit 00:00:00<\/b> enthält. Dies ist mit folgender SELECT<\/b>-Anweisung realisierbar:<\/p>\n Eine gute Idee, aber nicht umsonst hieß es eben „zum Einsatz kommen sollte“. Die Umsetzung scheitert an einer Einschränkung der Skalarfunktionen, denn diese können nur „deterministische Funktionen“ verarbeiten.<\/p>\n Eine deterministische Funktion liefert immer dasselbe Ergebnis, wenn sie mit bestimmten Parameterwerten aufgerufen wird. So liefert die Beispielfunktion dbo.sfProduktBezeichnung<\/b> immer die Produktbezeichnung zur Produkt-ID 1<\/b>, wenn 1<\/b> als Parameter übergeben wird. <\/p>\n Eine nicht deterministische Funktion liefert bei jeder Ausführung unterschiedliche Werte, auch wenn die Parameterwerte bei den Ausführungen immer dieselben sind.<\/p>\n Genau das ist bei getdate() <\/b>der Fall: Es wird pro Aufruf immer der aktuelle Zeitpunkt zurückgegeben. Und der aktuelle Zeitpunkt hat genau jetzt schon einen anderen Wert. Und jetzt. Und jetzt. Jetzt wieder …<\/p>\n Um die Anforderung – die Aufbereitung des aktuellen Datums zum Zeitpunkt 00:00 Uhr <\/b>– trotzdem mit einer Skalarfunktion zu realisieren, muss der Wert von getdate() <\/b>vor dem Aufruf der Skalarfunktion ermittelt und als Parameterwert an die Skalarfunktion übergeben werden.<\/p>\n Listing 5 zeigt Ihnen nun das korrekte Ermitteln des aktuellen Datums zum Zeitpunkt 00:00 Uhr. <\/p>\n Listing 5: Das aktuelle Datum zum Zeitpunkt 0:00<\/p>\n Der folgende Aufruf dieser Skalarfunktion liefert Ihnen beispielsweise am 06.11.2008 <\/b>um 23:15 Uhr <\/b>den Wert 06.11.2008 00:00:00<\/b>:<\/p>\n Diese Funktion können Sie nun in WHERE<\/b>-Bedingungen verwenden, um Werte bis zum heutigen Tag zu ermitteln. Mehr noch, durch die Parameterübergabe können Sie ein x-beliebiges Datum übergeben. Dieses Datum wird immer mit der Uhrzeit 00:00 Uhr <\/b>ausgegeben.<\/p>\n Haben Sie sich schon gefragt, warum die Funktion getdate() <\/b>in der Beispielfunktion dbo.tfMitarbeiterHierarchie<\/b> ohne Probleme funktioniert<\/p>\n Die Antwort ist ganz einfach: Tabellen- und Inlinefunktionen haben keine Probleme mit nicht deterministischen Funktionen. Dafür aber können Sie die Tabellen- und Inlinefunktionen nicht direkt in WHERE<\/b>-Bedingungen oder als Spalte verwenden.<\/p>\n Trotz einiger kleinerer Einschränkungen sind die Skalar-, Inline- oder Tabellenfunktionen doch eine große Stütze bei der SQL Server-Programmierung. Außerdem haben sie durchaus auch Einfluss auf die Ausführungszeiten Ihrer SQL-Abfragen. Wobei dieser Einfluss nicht immer ein positiver sein muss.<\/p>\n Performance<\/b><\/p>\n Die gute Nachricht zum Anfang: benutzerdefinierte Funktionen werden vom SQL Server wie gespeicherte Prozeduren oder Trigger behandelt, das heißt, dass auch bei der Ausführung einer benutzerdefinierten Funktion der bereits erstellte Ausführungsplan wiederverwendet wird.<\/p>\n Benutzerdefinierte Funktionen nutzen also dieselben Performancevorteile wie gespeicherte Prozeduren und Trigger. <\/p>\n Die gute Nachricht kann auch gleichzeitig eine schlechte sein. Der Ausführungsplan für die benutzerdefinierte Funktion wird ohne Rücksicht darauf erstellt, wo und in welchem Kontext sie verwendet wird. <\/p>\n Der Abfrageoptimierer erstellt somit für eine gespeicherte Prozedur, in der eine benutzerdefinierte Funktion verwendet wird, keinen gemeinsamen Ausführungsplan, sondern einen für die benutzerdefinierte Funktion und einen für die gespeicherte Prozedur. Dadurch werden eventuell einige Optimierungsmöglichkeiten nicht berücksichtigt.<\/p>\n Wäre der Quellcode der benutzerdefinierten Funktion in der gespeicherten Prozedur enthalten, würde der Abfrageoptimierer nur einen und wohl auch einen komplett anderen Ausführungsplan erstellen.<\/p>\n Bei der Verwendung von benutzerdefinierten Funktionen sollte noch ein weiterer Aspekt beachtet werden. Skalarfunktionen, die als Spalte oder in WHERE<\/b>-Bedingungen verwendet werden, werden pro Datensatz ausgeführt.<\/p>\n Dies gilt auch dann, wenn der Skalarfunktion immer derselbe Parameterwert übergeben wird und sie demzufolge immer dasselbe Ergebnis liefert. Dies mag sich bei geringer Datenmenge noch nicht bemerkbar machen, bei zunehmender Datenmenge jedoch wird die Ausführung der SQL-Anweisung immer langsamer.<\/p>\n Sie sollten also vor Verwendung einer benutzerdefinierten Funktion darauf achten, welche Verarbeitungen in der Funktion vollzogen werden, und diese gegebenenfalls auf eine andere Art und Weise nutzen. <\/p>\n Bei folgender Anweisung wird die Ermittlung des aktuellen Datums pro Datensatz der Tabelle dbo.Order Details<\/b> ausgeführt. <\/p>\n Dies ist eigentlich unnötig, denn die Skalarfunktion liefert in diesem Fall immer denselben Wert. Das aktuelle Datum kann genauso gut vorab ermittelt und in einer Variablen gespeichert werden, die wiederum in der SELECT<\/b>-Anweisung verwendet wird.<\/p>\n Besonders vorsichtig sollten Sie sein, wenn Sie benutzerdefinierte Funktionen auf der Seite der Spalten innerhalb einer WHERE<\/b>-Bedingung verwenden. <\/p>\n Mit folgender Anweisung werden die Bestellungen anhand einer Artikelbezeichnung über die Skalarfunktion dbo.sfProduktBezeichnung<\/b> ermittelt:<\/p>\n Die benutzerdefinierte Funktion wird pro Datensatz der Tabelle dbo.Order Details<\/b> ausgeführt. Auch hier ist es wie bei dem vorangegangenen Beispiel sinnvoller, den Wert vor der Abfrage zu ermitteln und in einer Variablen zu speichern, die dann in der Abfrage verwendet wird.<\/p>\n Dabei wird auf den Einsatz der Skalarfunktion verzichtet und der Quellcode dieser benutzerdefinierten Funktion zur Ermittlung des Werts verwendet:<\/p>\n Besonders drastisch werden die Performanceeinbußen, wenn Sie auf beiden Seiten, also auf der Seite der Spalten wie auf der Seite der Filterbedingung Skalarfunktionen verwenden. Beide Funktionen werden pro Datensatz ausgeführt, was zu drastischen Performanceeinbußen führen kann.<\/p>\n Die Beispiele zeigen, dass es in manchen Fällen sinnvoll ist, benutzerdefinierte Funktionen nicht direkt in SELECT<\/b>-Anweisungen zu verwenden oder komplett auf ihren Einsatz zu verzichten. <\/p>\n Natürlich heißt das jetzt nicht, dass Sie keine benutzerdefinierten Funktionen einsetzen sollen, jedoch sollten Sie sich bei ihrem Einsatz darüber im Klaren sein, welche Datenmengen ermittelt und welche Verarbeitungen durch die Funktionen ausgeführt werden.<\/p>\n So weit zu den benutzerdefinierten Funktionen mit ihren positiven und negativen Auswirkungen bei der SQL Server-Programmierung. Doch wie sieht es aus mit Access Wie können die benutzerdefinierten Funktionen in Access verwendet werden Immerhin bieten sich die Tabellen- und Inlinefunktionen als „parametrisierbare“ Sichten für einen Einsatz in Access geradezu an.<\/p>\n Benutzerdefinierte Funktionen und Access<\/b><\/p>\n Die Möglichkeiten, wie benutzerdefinierte Funktionen in Access verwendet werden können, hängen vom Format der Access-Datenbank ab. <\/p>\n Bei einer ADP verwenden Sie die Objekte der SQL Server-Datenbank nicht in Access, sondern über Access, das heißt, Sie sprechen die SQL Server-Objekte direkt an. Aus diesem Grund können Sie die benutzerdefinierten Funktionen wie die gespeicherten Prozeduren und Sichten in den Access-Objekten – Formulare, Berichte, Controls – unmittelbar verwenden. In Bild 4 sehen Sie die Beispielfunktion dbo.ifArtikelEinerKategorie<\/b> als Datenherkunft für ein Formular.<\/p>\n Bild 3: Die erweiterte Skalarfunktion<\/span><\/b><\/p>\n Bild 4: Eine Inlinefunktion als Datenherkunft<\/span><\/b><\/p>\n In einer MDB dagegen ist dies nicht so einfach möglich, da die Jet-Engine die benutzerdefinierten Funktionen nicht als Tabellen einbinden kann. <\/p>\n Auch der Weg über die Pass-Through-Abfrage ist nicht wirklich zufriedenstellend. Dort können Sie die Tabellen- und Inlinefunktionen nur als Tabellen beziehungsweise Sichten und die Skalarfunktionen nur als Spalten oder in WHERE<\/b>-Bedingungen verwenden. Aber eben nicht direkt, sondern immer nur in Verbindung mit einer SQL-Anweisung.<\/p>\n Natürlich ist das Speichern von SQL-Anweisungen in einer Pass-Through-Abfrage eine Variante, aber denken Sie an die Empfehlung zu Pass-Through-Abfragen aus dem Beitrag Gespeicherte Prozeduren<\/b> (s. Shortlink 626):<\/p>\n In Pass-Through-Abfragen sollten Sie aus Gründen der Performance keine SQL-Anweisungen, sondern gespeicherte Prozeduren verwenden. <\/p>\n Insofern packen Sie Ihre SQL-Anweisungen, die die benutzerdefinierten Funktionen verwenden, in gespeicherte Prozeduren und rufen diese per Pass-Through-Abfragen auf. <\/p>\n Fazit<\/b><\/p>\n Benutzerdefinierte Funktionen ergänzen die Möglichkeiten der SQL Server-Programmierung optimal. Sie sind flexibel einsetzbar: in einfachen SQL-Anweisungen und Sichten über gespeicherte Prozeduren und Trigger bis hin zu Einschränkungen und Standardwerten. Außerdem bieten die drei Typen unterschiedliche Möglichkeiten, Programmlogik zu kapseln und wiederzuverwenden. <\/p>\n Ob als „parametrisierte“ Sicht per Tabellen-, Inline- oder als Skalarfunktion, in WHERE<\/b>-Bedingungen beziehungsweise als Spalte in SELECT<\/b>-Anweisungen oder zur Ermittlung von Standardwerten oder Prüfung von Einschränkungen:Es gibt kaum einen Bereich, für den Sie benutzerdefinierte Funktionen nicht verwenden können – also: Probieren Sie es aus!<\/p>\n Enthaltene Beispieldateien:<\/p>\n tfMitarbeiterHierarchie.sql<\/p>\n ifArtikelEinerKategorie.sql<\/p>\n sfAktuellerTag.sql<\/p>\n sfBerechnePositionSumme.sql<\/p>\n sfProduktBezeichnung.sql<\/p>\n Suedsturm_Backup4.sql<\/p>\nCREATE FUNCTION dbo.ifArtikelEinerKategorie \r\n( \r\n @CategoryId int,\r\n @Discontinued bit\r\n)\r\nRETURNS TABLE \r\nAS\r\nRETURN \r\n(\r\n SELECT \r\n dbo.Products.ProductId, dbo.Products.ProductName, \r\n dbo.Products.SupplierID, dbo.Suppliers.CompanyName, \r\n dbo.Products.CategoryID, dbo.Categories.CategoryName, \r\n dbo.Products.QuantityPerUnit, dbo.Products.UnitPrice, \r\n dbo.Products.UnitsInStock, dbo.Products.UnitsOnOrder, \r\n dbo.Products.ReorderLevel, dbo.Products.Discontinued\r\n FROM \r\n dbo.Products \r\n INNER JOIN dbo.Categories \r\n ON dbo.Products.CategoryID = dbo.Categories.CategoryID \r\n INNER JOIN dbo.Suppliers \r\n ON Products.SupplierID = Suppliers.SupplierID\r\n WHERE\r\n dbo.Products.CategoryId = @CategoryId\r\n AND\r\n dbo.Products.Discontinued = @Discontinued\r\n)<\/pre>\n
SELECT * FROM dbo.ifArtikelEinerKategorie (1, 0)<\/pre>\n
SELECT * FROM dbo.[Order Details] OD\r\nINNER JOIN dbo.ifArtikelEinerKategorie(1, 1) AEK\r\nON OD.ProductId = AEK.ProductId<\/pre>\n
\n
CREATE FUNCTION dbo.tfMitarbeiterHierarchie ()\r\nRETURNS @Mitarbeiter TABLE \r\n(\r\n HierarchieEbene int, ManagerId int, \r\n MitarbeiterId int, Titel nvarchar(30), \r\n Mitarbeiter nvarchar(30), [Alter] int\r\n)\r\nAS\r\nBEGIN\r\n -- Hierarchie per CTE ermitteln\r\n WITH cteHierarchie (Ebene, MgrId, EmpId, Titel, Mitarbeiter) AS \r\n (\r\n SELECT 1, E.ReportsTo, E.EmployeeId, E.Title, \r\n E.LastName + €š, €š + E.FirstName \r\n FROM dbo.Employees As E\r\n WHERE ReportsTo Is Null\r\n UNION ALL\r\n SELECT Ebene+1, E.ReportsTo, E.EmployeeId, E.Title, \r\n E.LastName + €š, €š + E.FirstName\r\n FROM dbo.Employees As E INNER JOIN cteHierarchie \r\n ON cteHierarchie.EmpId = E.ReportsTo\r\n )\r\n INSERT INTO @Mitarbeiter (HierarchieEbene, ManagerId, \r\n MitarbeiterId, Titel, Mitarbeiter)\r\n SELECT * FROM cteHierarchie ORDER BY Ebene, MgrId; \r\n -- Alter ergänzen\r\n UPDATE @Mitarbeiter\r\n SET [Alter] = \r\n CASE \r\n WHEN DateAdd(Year, Datediff(Year, BirthDate, \r\n getdate()), BirthDate) > getdate() \r\n THEN Datediff(Year, BirthDate, getdate()) - 1\r\n ELSE DateDiff(Year, BirthDate, getdate())\r\n END \r\n FROM @Mitarbeiter As Mitarbeiter \r\n INNER JOIN dbo.Employees \r\n ON Mitarbeiter.MitarbeiterId = dbo.Employees.EmployeeId \r\n RETURN \r\nEND<\/pre>\n
SELECT * FROM tfMitarbeiterHierarchie()<\/pre>\n
CREATE FUNCTION dbo.sfProduktBezeichnung\r\n(\r\n@ProductId int\r\n)\r\nRETURNS nvarchar(40)\r\nAS\r\nBEGIN\r\nDECLARE @ProductName nvarchar(40)\r\nSELECT @ProductName = ProductName\r\nFROM Products WHERE ProductId = @ProductId\r\nRETURN @ProductName\r\nEND<\/pre>\n
![\"pic02.TIF\"](\"..\/fileadmin\/_temp_\/2009_01\/UDF-web-images\/pic02_opt.jpeg\")
<\/p>\nCREATE FUNCTION dbo.sfBerechnePositionSumme\r\n(\r\n@OrderId int,\r\n@ProductId int\r\n)\r\nRETURNS decimal(18,2)\r\nAS\r\nBEGIN\r\nDECLARE @PosSum as float\r\nSELECT @PosSum =\r\nSum((UnitPrice*Quantity)*\r\n((1-Discount)\/100)*100)\r\nFROM dbo.[Order Details]\r\nWHERE OrderId = @OrderId\r\nAND ProductId = @ProductId\r\nRETURN @PosSum\r\nEND<\/pre>\n
![\"pic03.TIF\"](\"..\/fileadmin\/_temp_\/2009_01\/UDF-web-images\/pic03_opt.jpeg\")
<\/p>\nSELECT Cast(Convert(varchar(10), getdate(), 112) As datetime)<\/pre>\n
CREATE FUNCTION dbo.sfAktuellerTag\r\n(\r\n@parGetdate datetime\r\n)\r\nRETURNS datetime\r\nAS\r\nBEGIN\r\nDECLARE @Tag datetime\r\nSET @Tag = cast(convert(varchar(10),\r\n@parGetdate, 112) as datetime)\r\nRETURN @Tag\r\nEND<\/pre>\n
SELECT dbo.sfAktuellerTag(getdate())<\/pre>\n
SELECT * FROM dbo.Orders\r\nWHERE OrderDate <= dbo.sfAktuellerTag(getdate())<\/pre>\n
DECLARE @AktuellerTag datetime\r\nSET @AktuellerTag = dbo.sfAktuellerTag(getdate())\r\nSELECT * FROM dbo.Orders WHERE OrderDate <= @AktuellerTag<\/pre>\n
SELECT * FROM dbo.[Order Details]\r\nWHERE dbo.sfProduktBezeichnung(dbo.[Order Details].ProductId) = €šChai€<\/pre>\n
DECLARE @ProduktId int\r\nSELECT @ProduktId = ProductId FROM dbo.Products WHERE ProductName = €šChai€\r\nSELECT * FROM dbo.[Order Details] WHERE ProductId = @ProduktId<\/pre>\n
<\/p>\n![\"pic04.TIF\"](\"..\/fileadmin\/_temp_\/2009_01\/UDF-web-images\/pic04_opt.jpeg\")
<\/p>\nDownloads zu diesem Beitrag<\/h3>\n