Tag: "t-sql"

Wie kann ich Startup Prozeduren identifizieren?

Posted on Aug 31, 2007 von in SQL Server

Startup Prozeduren werden beim Start von SQL Server ausgeführt. Typische Verwendungszwecke sind zum Beispiel:

  • das Befüllen von globalen Parameter Tabellen

Um eine Prozedur als Startup Procedure festzulegen, muß man die Systemprozedur sp_procoption ausführen. Nähere Erläuterungen zur Verwendung dieser Prozedur stehen in BOL.

Ganze Geschichte »

Tags: , ,

Wie kann ich programmatisch IDENTITY Spalten in meinen Tabellen identifizieren?

Posted on Aug 31, 2007 von in SQL Server

In SQL Server 2000 und früher funktioniert folgendes:

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
  FROM INFORMATION_SCHEMA.COLUMNS
 WHERE COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA+'.'+TABLE_NAME), COLUMN_NAME,'IsIdentity')=1;
SELECT su.name AS TABLE_NAME, so.name AS TABLE_NAME, sc.name AS COLUMN_NAME
  FROM dbo.syscolumns AS sc
  JOIN dbo.sysobjects AS so
    ON sc.id = so.id
    JOIN dbo.sysusers AS su
      ON so.uid = su.uid
 WHERE sc.status & 0x80 = 0x80;

Im SQL Server 2005 sollte man folgendes verwenden:

SELECT su.name AS SCHEMA_NAME, so.name AS TABLE_NAME, sc.name AS COLUMN_NAME
  FROM sys.columns AS sc
  JOIN sys.objects AS so
    ON sc.object_id = so.object_id
    JOIN sys.schemas su
      ON su.schema_id = so.schema_id
 WHERE sc.is_identity = 1; 

Wie kann ich Funktionen in SQL Server 2005 identifizieren?

Posted on Aug 31, 2007 von in SQL Server

SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'FUNCTION'
AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(SPECIFIC_SCHEMA) + '.' +
QUOTENAME(SPECIFIC_NAME)),'IsMSShipped') = 0

oder alternativ dazu:

SELECT *
FROM sys.sysobjects -- oder sys.objects
WHERE type IN ('FN', 'IF', 'TF')


SELECT *
FROM sys.sql_modules
WHERE OBJECTPROPERTY(OBJECT_ID,'IsScalarFunction') = 1
OR OBJECTPROPERTY(OBJECT_ID,'IsTableFunction') = 1
OR OBJECTPROPERTY(OBJECT_ID,'IsInlineFunction') = 1

SQL Server rundet falsch oder doch nicht

Posted on Jun 7, 2007 von in SQL Server

Multipliziert man zwei Zahlen, die in Addition sowohl bei den Vorkomma- als auch bei den Nachkommastellen, den derzeit "maximalen" Datentyp DECIMAL(38,10) überschreitet, kann man unter Umstände eine Überraschung erleben. Das Ergebnis weicht ab von dem, was man vielleicht erwarten würde.

Allerdings nur oberflächlich betrachtet. Es gibt klar definierte Regeln, die man jedoch kennen muß.

Beispiel:

DECLARE @Multiplikant AS DECIMAL(38, 10)
DECLARE @Multiplikator AS DECIMAL(38, 10)
DECLARE @Result AS DECIMAL(38, 10)
SET @Multiplikant = .1235589123
SET @Multiplikator = 0.55456
SET @Result = @Multiplikant * @Multiplikator
SELECT @Multiplikant, @Multiplikator, @Result

Bevor wir das Ergebnis betrachten, erst einmal ein paar Erläuterungen zu diesem Skript. Wir wollen 2 Zahlen miteinander multiplizieren. Dafür haben wir 3 Variablen vom Typ DECIMAL(38,10) deklariert. Als Ergebnis (in Excel auf 10 Nachkommastellen gerundet) würde man 0,0685208304 erwarten. Läßt man nun das Skript laufen, erhält man folgendes Ergebnis:

                                                                                
------------- ------------- -------------
0.1235589123 0.5545600000 0.0685210000

(1 row(s) affected)

Was ist passiert?
Nun, die deklarierten Variablen Multiplikant und Multiplikator sind jeweils vom Typ DECIMAL(38,10). SQL Server "addiert" Vorkomma- und Nachkommastellen. Das Ergebnis der obigen Rechenoperation müßte also vom Typ DECIMAL(76,20) sein. Dies übersteigt jedoch sowohl die Result Variable als auch den maximal zulässigen DECIMAL Datentyp. Das Ergebnis ist nun, daß SQL Server den Nachkommastellenbereich rundet, um zu vermeiden, daß der Integralpart des Ergebnisses abgeschnitten wird. Dies heißt dann in aller Regel, daß nach 6 Nachkommastellen gerundet wird.

Um nun aus dem obigen Skript ein "korrektes" Ergebnis mit einer hinreichenden Anzahl von Nachkommastellen zu erhalten, muß man die verwendeten Datentypen modifizieren, unter Beachtung der SQL Server-spezifischen Regeln für Precision und Scale.

Bei Multiplikation wird Precision nach der Formel p1 + p2 + 1 errechnet. Scale wird s1 + s2 gerechnet.

Auf das Skript angewendet, könnte man dies folgendermaßen umformulieren:

DECLARE @Multiplikant AS DECIMAL(19, 10)
DECLARE @Multiplikator AS DECIMAL(19, 5)
DECLARE @Result AS DECIMAL(38, 10)
SET @Multiplikant = .1235589123
SET @Multiplikator = 0.55456
SET @Result = @Multiplikant * @Multiplikator
SELECT @Multiplikant, @Multiplikator, @Result

------------- -------- -------------
0.1235589123 0.55456 0.0685208304

(1 row(s) affected)

Wie man sieht, gleich das Ergebnis nun dem mit Excel errechneten. Vom tatsächlich "richtigen" Ergebnis von 0,068520830405088 wurde nur ein nicht signifikanter Anteil an Nachkommastellen abgeschnitten.

Fazit: Manchmal ist weniger mehr. Und gerade wenn man meint, durch Datentypen von vermeindlich hoher Genauigkeit ein hohes Maß an Exaktheit zu erreichen, sollte man sich über die Implikationen bewußt sein.

Pro SQL Server 2005 T-SQL Recipes

Posted on Mär 6, 2006 von in Vermischtes

Die Online Hilfe des SQL Servers (Books Online, aka BOL) ist eine technische Dokumentation, die an Umfang und Ausführlichkeit kaum Wünsche offenläßt. Nahezu alles, was man zum SQL Server wissen muß und will, wird in BOL erklärt. Man muß "nur" wissen, wie man an diese Informationen gelangt.

Ganze Geschichte »

Nominalzins aus Effektivzins errechnen

Posted on Jan 30, 2006 von in SQL Server

Wie man die effektive Verzinsung aus der Nominalverzinsung errechnet, haben wir in diesem Beitrag gesehen. Neulich hat mich aber ein Posting in den MSDN Newsgroups auf die Excel Funktion NOMINAL() aufmerksam gemacht, die genau das Gegenteil macht. Also die Errechnung der Nominalverzinsung bei gegebenem Effektivzins. Um es leichter nachvollziehbar zu machen, verwenden wir die Gegebenheiten aus dem oben bereits referenzierten Beitrag.
Zur Erinnerung: Der von uns errechnete jährliche effektive Zinssatz lag bei 20,27% bei 12 Zinszeitpunkten im Jahr. Also, monatlicher Zins. Diese beiden Angaben (Effektivzins, Perioden) benötigen wir, um daraus den Nominalzinssatz errechnen zu können. Dies geschieht nun folgendermassen:

DECLARE @apr FLOAT
DECLARE @frequency FLOAT

SET @apr = 20.270504548765487/100
SET @frequency = 12

SELECT @frequency * POWER(1+@apr, 1/@frequency)-@frequency AS Nominal_Jahreszins

Nominal_Jahreszins
--------------------
0.18599999999999994

(1 row(s) affected)

In Excel würde das Ganze so

=NOMINAL(A1;A2)

aussehen, wobei in Zelle A1 der Zinssatz und in Zelle A2 die Anzahl der Perioden stehen würde.

2 Kommentare »

JOIN Hints

Posted on Dez 26, 2005 von in SQL Server

JOIN Hints können in einer Abfrage verwendet werden, um den JOIN Typ festzulegen, den der Query Optimiser für den Ausführungsplan verwenden soll: Folgende JOIN Optionen stehen zur Verfügung:
- Loop
- Merge
- Hash

Die Syntax für einen JOIN Hint ist (am Beispiel eines INNER JOINs):

FROM tabelle_1 INNER (LOOP | MERGE | HASH) JOIN tabelle_2

Hier ist ein Beispiel:

FORM header_tabelle INNER LOOP JOIN detail_tabelle

Wie man sehen kann, wird der JOIN Hint zwischen der Angabe des JOIN Typs (hier der INNER) und dem JOIN Schlüsselwort. Nur ein JOIN Hint kann zu einer Zeit pro JOIN in einer Abfrage verwendet werden. Ferner können JOIN Hints nur verwenden werden zusammen mit der ANSI JOIN Syntax, nicht mit der älteren Microsoft JOIN Syntax.

Die obige Syntax ist nicht die einzige Möglichkeit, um einen JOIN Hint in einer Abfrage zu verwenden. Man kann auch die OPTION Klausel verwenden. Diese bewirkt, daß der Hint während der gesamten Abfrage verwendet wird. Es können mehrere Hints in der OPTION Klausel auftauchen, jeder Hint jedoch nur genau 1x. Es gibt nur eine OPTION Klausel pro Abfrage.

Hier ist ein Beispiel der Verwendung der OPTION Klausel:
OPTION (INNER) oder OPTION (MERGE) oder OPTION (HASH)

Welche Methode SQL Server verwendet, um Tabellen zu joinen, hängt von zahlreichen Einzelfaktoren ab. In der Mehrzahl der Fälle wird man aber einen Nested Loop Join beobachten. Bevor man nun aber hingeht, und versucht, den JOIN durch Einsatz eines Hints zu beeinflußen, sollte man immer erst die zugrundeliegende Abfrage und die Indexes auf den zugrundeliegenden Tabellen betrachten und versuchen, diese zu optimieren, bevor man SQL Server durch den Hint in seinem Spielraum einengt und die Verarbeitungsmethode starr vorgibt. Unter Umständen kann man durch den Einsatz des Hints die Performance in den Keller ziehen, wenn z.B. unter geänderten Rahmenbedingungen der Hint nicht mehr angebracht ist; SQL Server jedoch gezwungen ist, diesen zu beachten. Von daher sollte man stets vorher unter realistischen Bedingungen testen, ob der Hint das bringt, was man erwartet.

*****

Die deutsche 30/360 Zinsberechnungsmethode

Posted on Nov 3, 2005 von in SQL Server

In den guten alten Tagen als Computer noch kaum vorhanden und noch weniger leistungsfähig waren, entwickelte sich die deutsche Zinsberechnungsmethode. Die Hauptbesonderheit dieser Methode besteht darin, daß jeder Monat fiktiv 30 Tage hat. Egal, wie viele Tage dieser Monat tatsächlich hat. Das Jahr hatte 360 Tage, egal ob Schaltjahr oder nicht. Dies vereinfachte natürlich die Zinsberechnungen, die damals manuell vorgenommen wurden, enorm. Die Ungleichmäßigkeiten dieser Methode sind mehr oder weniger signifikant. Gerade bei kürzeren Laufzeiten von Wertpapieren wirken sie sich jedoch deutlicher aus als bei längeren.

Mit Einführung des Euros hat man sich entschlossen, auf die ISMA-Regel 251 umzustellen. Diese Regel bezeichnet man auch als act/act Zinsberechnungsmethode. Sie berücksichtigt die tatsächliche Anzahl der Tage. Sowohl im Zähler als auch im Nenner. Mit Beginn der Doppelwährungsphase wurden viele der alten Wertpapieremissionen, die noch mit 30/360 berechnet wurden, Zug um Zug auf die neue Methode umgestellt. Neuemissionen auf dem Kapitalmarkt im Euroland finden in aller Regel heute nur noch mit act/act Regelung statt. Nichtsdestotrotz ist die 30/360 Regel in vielen (ausländischen) Märkten und Marktsegmenten noch präsent. Der amerikanische Kapitalmarkt wendet auch heute noch diese Regeln an. Allerdings mit einer Abweichung zur deutschen Regelung. In den USA wird der 31. eines Tages unter bestimmten Umständen mitgezählt. Die Amerikaner behandeln auch den Februar etwas anders, aber da wir im folgenden diese amerikanische Variante komplett aussen vor lassen interessieren uns diese Besonderheiten jetzt nicht. Wir sprechen hier nur von der deutschen Variante.

Frage: Wie viele Tage liegen zwischen dem 01.11.2005 und dem 01.12.2005?
Antwort: 30

Frage: Wie viele Tage liegen zwischen dem 01.11.2005 und dem 01.01.2006?
Antwort: 60

Frage: Wie viele Tage liegen zwischen dem 28.02.2005 und dem 01.03.2005?
Antwort: 3

Bevor jetzt jemand aufschreit. Stop, wir rechnen jeden Tag fiktiv mit 30 Tagen ab. Also hat auch der Februar 30 fiktive Tage. Somit liegen der 28.02.2005, der 29.02.2005 und der 30.02.2005 gemäß 30/360 Regelung zwischen beiden Tagen und somit ist 3 die richtige Antwort.

Dies ist etwas gewöhnungsbedürftig, aber korrekt. Vielleicht noch ein abschließendes Beispiel.

Frage: Wie viele Tage liegen zwischen dem 30.12.2005 und dem 31.12.2005?
Antwort: 0

Auch hier erst einmal in Ruhe nachdenken, bevor man protestiert. Gemäß unserer Konvention hat jeder Monat 30 Tage. Bei den Monaten mit 31 Tagen wird der 31. künstlich eliminiert. Somit reduziert sich diese Frage auf die Anzahl der Tage, die zwischen dem 30.12.2005 und dem 30.12.2005 liegen. Offensichtlich lautet die richtige Antwort 0.

Genug der Beispiele. Wir suchen nun also einen Algorithmus der dies abbildet.

DECLARE @dt_Start DATETIME
DECLARE @dt_Ende DATETIME
SELECT @dt_Start = '20051101', @dt_Ende = '20051201'
SELECT
DATEDIFF(MONTH, @dt_Start, @dt_Ende) * 30
+ DAY(@dt_Ende)
- DAY(@dt_Start)
- CASE WHEN DAY(@dt_Ende) = 31 THEN 1 ELSE 0 END AS [Tage30/360]

Tage30/360
-----------
30

(1 row(s) affected)

Betrachten wir einmal etwas näher dieses Statement.

Wir multiplizieren die Anzahl der Monate zwischen Ausgangs- und Enddatum mit 30 und eliminieren damit nahezu sämtliche Probleme mit den tatsächlichen Monatstagen. Anschließend addieren wir einfach die Tage des Enddatums hinzu und ziehen die Tage des Startdatums wieder ab. Auf diese Weise eliminieren wir das Problem, wenn das Startdatum nicht auch gleich einem Monatsanfang ist. Zum Schluß müssen wir noch das Problem adressieren wenn das Enddatum auf einen 31. fällt. Dies erfolgt durch den CASE Ausdruck, der bei Bedarf nochmal einen Tag subtrahiert.

Vielleicht noch einige Anmerkungen zur generellen Methodik. Vielfach kann man die wirresten Aussagen lesen, welche Tage nun verzinst werden und welche nicht mehr mitverzinst werden. Wird direkt der 1 Tag inklusive gerechnet oder nicht? Und was ist mit dem letzten Tag? Fakt ist, daß auf keinen Fall beide Tage eingerechnet werden.

Und zumindest im Wertpapiergeschäft ist es üblich, den 1. Tag einer Zinsperiode mitzuverzinsen, während der letzte Tag nicht mitverzinst wird. Dies mag in anderen Bereichen, wie bei der Berechnung von Verzugszinsen oder KK Krediten anders sein, wer aber dies auch für den Wertpapierbereich behauptet, mag sich mal in Ruhe Wertpapierurkunden von Banken durchlesen. Diese sind selbst für juristische Laien unzweideutig. Beginnend mit der Valutierung  (einschließlich) bis zum Tag der nächsten Kuponzahlung (ausschließlich) werden Zinsen gerechnet.

Eigentlich ganz einfach und auch logisch, aber immer wieder ein beliebtes Thema bei der Ermittlung von Zinsabgrenzungen im Rahmen des Jahresabschlusses. Wir hatten jahrelang Spaß mit unserer Wirtschaftsprüfungsgesellschaft. Da mit Ausnahme des Prüfungsleiters das Team fast jedes Jahr wechselte, konnte man dies den neuen Prüfern immer wieder erklären.

Warum dieser Beitrag, die deutsche Zinsberechnungsmethode spielt doch, gerade im europäischen Wertpapierhandel, kaum noch eine Rolle?

Nun, in den letzten Tagen habe ich mich entschlossen, meine T-SQL Implementierung der Yield-to-maturity (YTM) Berechnung als Beitrag zu veröffentlichen. Ein Faktor bei der YTM Bestimmung ist die anzusetzende Zinsberechnungsmethode, zu der dieser Beitrag das grundlegende Verständnis bilden soll. Die demnächst veröffentlichte YTM Variante wird allerdings etwas "abgespeckt" sein. Sie wird nur die YTM auf Basis 30/360 beinhalten. Die Implementierung der anderen Zinsberechnungsmethode, wie act/act, act/360 oder act/365 überlasse ich dem interessierten Leser.

Aus den Reaktionen auf meine ersten Beiträgen aus der Reihe "Finanzmathematik" weiß ich, daß es einige Leute gibt, die anscheinend mächtig scharf darauf sind, einen solchen Algorithmus in die Finger zu bekommen. Da ich aber keine Lust habe, sämtliche Arbeit diesen Leuten abzunehmen, werde ich nur das Skelett liefern, das Filet Mignon wird sich dann jeder selber basteln müssen. Ich halte es dennoch für einen äußerst fairen Deal. ;-)

Man sollte auch in der Lage sein, die Beispiel mit der Excel Funktion TAGE360 nachzurechnen. So sollte

=TAGE360(Startdatum;Enddatum;WAHR)

identische Ergebnisse liefern. Ich selber arbeite mit einer finanzmathematischen Formelsammlung und habe meine Ergebnisse mit den Ergebnissen der entsprechenden Formel aus dieser Bibliothek verglichen. Diese Ergebnisse stimmen überein. Und liest man sich die Referenzliste dieses Softwarelieferanten durch, rechnet fast die gesamte Finanzwelt Deutschlands mit diesem Programm. Zu diesem Who-is-who gehören die Deutsche Bundesbank, das Bundesfinanzministerium und nahezu sämtliche Großbanken. Zwar verifiziert dies nicht die Richtigkeit des Algorithmus, aber es ist zumindest tröstlich, das im Falle eines Falles alle gleichermaßen falsch rechnen würden. :-)

Warum diese besondere Betonung auf die Korrektheit der Ergebnisse? Nun, gerade in Excel Communities stößt man häufig auf inkorrekte, falsche und/oder unvollständige Aussagen zu der TAGE360 Formel.

1 Kommentar »