Datumsarithmetik
Da sitzt man abends völlig entspannt am Schreibtisch, blättert in alten Unterlagen aus der Studienzeit weil man sich an gewisse mathematische Zusammenhänge nicht mehr so direkt aus dem Kopf erinnern kann, und - zack - stolpert man über Integer Arithmetik. Die (durchaus verworrene) Assoziationskette zu SQL Server und DATETIMEs führt jedenfalls dann dazu, daß das Mathebuch erst einmal Mathebuch ist und bleibt und wir einen Selbstversuch in Datumsarithmetik unternehmen.
Stored Procedures mit mehr als einem Output Parameter
Im SQL Server 2000 kann eine Stored Procedure bis zu 2.100 Parameter haben. Jeder einzelne dieser Parameter kann entweder Input- oder Output Parameter sein.
FLOAT Daten in VARCHAR konvertieren
Die Konvertierung von FLOAT Daten in CHAR oder VARCHAR im SQL Server 2000 kann unter Umständen für Überraschungen sorgen, die mehr oder weniger unangenehm sind. Mal angenommen wir finden einen gültigen Grund, warum wir FLOAT in VARCHAR umwandeln wollen und haben folgende Basisdaten
CREATE TABLE t1
(
Preis FLOAT
)
INSERT INTO t1 SELECT 0.99
UNION ALL SELECT 9.99
UNION ALL SELECT 99.99
UNION ALL SELECT 999.99
UNION ALL SELECT 9999.99
Die Abfrage, die diese Daten umwandeln soll, sieht folgendermaßen aus:
SELECT
CAST(Preis AS VARCHAR(10)) Preis
FROM t1
Preis
----------
0.99
9.99
99.99
999.99
9999.99
(5 row(s) affected)
So, kein Problem bisher. Nun betrachten wir einmal folgende Basisdaten:
INSERT INTO t1 SELECT 10000
UNION ALL SELECT 10000.49
UNION ALL SELECT 10000.5
UNION ALL SELECT 10000.51
UNION ALL SELECT 10000.99
SELECT
CAST(Preis AS VARCHAR(10)) Preis
FROM t1
Preis
----------
10000
10000.5
10000.5
10000.5
10001
(5 row(s) affected)
Es sieht ganz danach aus, als ob SQL Server 2000 ab 10000 intern eine Rundung vornimmt. Etwas in der Form.
SELECT
CAST(Preis AS VARCHAR(10)) Preis
, CONVERT(VARCHAR, Preis,0) FROM t1 Preis ---------- ------------------------------ 10000 10000 10000.5 10000.5 10000.5 10000.5 10000.5 10000.5 10001 10001 (5 row(s) affected)
So, wie kriegt man es nun hin, daß auch die Daten heraus kommen, die eingegeben wurden?
Eine Möglichkeit besteht darin, die Daten erst explizit in DECIMAL umzuwandeln und anschließend zurück in VARCHAR. Etwa so:
SELECT
CAST(Preis AS VARCHAR(10)) Preis
, CONVERT(VARCHAR, Preis,0)
, CAST(CAST(PREIS AS DECIMAL(8,2)) AS VARCHAR(20)) FROM t1 Preis ---------- ------------------------------ -------------------- 10000 10000 10000.00 10000.5 10000.5 10000.49 10000.5 10000.5 10000.50 10000.5 10000.5 10000.51 10001 10001 10000.99 (5 row(s) affected)
Eine weitere Möglichkeit wäre:
SELECT
CAST(Preis AS VARCHAR(10)) Preis
, CONVERT(VARCHAR, Preis,0)
, CAST(CAST(PREIS AS DECIMAL(8,2)) AS VARCHAR(20))
, LTRIM(RTRIM(STR(ROUND(Preis,2),10,2)))
FROM t1
Preis
---------- ------------------------------ -------------------- ----------
10000 10000 10000.00 10000.00
10000.5 10000.5 10000.49 10000.49
10000.5 10000.5 10000.50 10000.50
10000.5 10000.5 10000.51 10000.51
10001 10001 10000.99 10000.99
(5 row(s) affected)
Diese Methode geht nicht den Umweg über die explizite Konvertierung in einen anderen Datentypen. STR() wandelt FLOAT direkt um. Das RTRIM() ist zwar nicht zwingend notwendig, aber schadet auch nicht wirklich.
Warum zeigt SQL Server dieses Verhalten? Ehrlich gesagt, habe ich darauf keine Antwort. Eventuell könnte Single und Double Precision beim FLOAT Datentyp eine Rolle spielen, aber dies ist nur eine Vermutung. Falls jemand eine schlüssige Begründung für dieses Verhalten hat, würde ich micht freuen, diese zu hören.
Auf volle Stunden skalieren
Die Anforderung, Auswertungen zu erstellen, die die Daten auf stündlicher Basis aufbereiten und auswerten, findet sich in vielen Bereichen. Zum Beispiel, Anzahl der Telefonate pro Mitarbeiter pro Stunde, Durchschnittswerte irgendwelcher Meßwerte pro Stunde usw... Hier ist eine einfache, aber effektive Methode, solche Anforderungen umzusetzen:
Access Indexverhalten imitieren
Microsoft Access bietet die Option "Nullwerte ignorieren" bei Indices an. Wie kann man das auf den SQL Server übertragen?...
Die schlechte Nachricht gleich vorweg: Ein direktes Äquivalent existiert im SQL Server nicht! Erstellt man einen Index als UNIQUE, kann dieser genau einmal einen NULL Marker enthalten. Beispiel:
CREATE TABLE Table1
(cID INT PRIMARY KEY
, c1 INT NULL)
CREATE UNIQUE NONCLUSTERED INDEX ix_test ON Table1(c1)
INSERT INTO Table1
SELECT 1,1
UNION ALL
SELECT 2,NULL
UNION ALL
SELECT 3,NULL
DROP TABLE Table1
Server: Msg 2601, Level 14, State 3, Line 4
Cannot insert duplicate key row in object 'Table1' with unique index 'ix_test'.
The statement has been terminated.
Einen Index kann man also nicht nutzen. Auch nicht besser sieht es mit einer UNIQUE Einschränkung aus:
CREATE TABLE Table1
(cID INT PRIMARY KEY
, c1 INT NULL
CONSTRAINT c_c1 UNIQUE)
INSERT INTO Table1
SELECT 1,1
UNION ALL
SELECT 2,NULL
UNION ALL
SELECT 3,NULL
DROP TABLE Table1
Server: Msg 2627, Level 14, State 2, Line 4
Violation of UNIQUE KEY constraint 'c_c1'. Cannot insert duplicate key in object 'Table1'.
The statement has been terminated.
Um es abzukürzen - und nochmals zu wiederholen - eine eingebaute Möglichkeit gibt es nicht. Man muß also selber tätig werden. 4 Möglichkeiten stehen hierfür zur Verfügung.
1. Einen Indexed View benutzen.
SET NOCOUNT ON
CREATE TABLE Table1
(cID INT PRIMARY KEY
, c1 INT NULL)
GO
CREATE VIEW Table1_Unique_Non_NULL
WITH SCHEMABINDING
AS
SELECT c1
FROM dbo.Table1
WHERE c1 IS NOT NULL
GO
CREATE UNIQUE CLUSTERED INDEX uc1 ON Table1_Unique_Non_NULL(c1)
INSERT INTO Table1
SELECT 1,1
UNION ALL
SELECT 2,NULL
UNION ALL
SELECT 3,NULL
SET NOCOUNT OFF
SELECT * FROM Table1_Unique_Non_NULL
DROP VIEW dbo.Table1_Unique_Non_NULL
DROP TABLE Table1
c1
-----------
1
(1 row(s) affected)
Wie man sieht, wird der 2.te NULL Marker widerspruchslos in die Tabelle eingefügt. Versucht man jedoch:
INSERT INTO Table1 SELECT 4,1
Server: Msg 2601, Level 14, State 3, Line 1
Cannot insert duplicate key row in object 'Table1_Unique_Non_NULL' with unique index 'uc1'.
The statement has been terminated.
2. Eine Einschränkung auf eine berechneten Spalte verwenden.
SET NOCOUNT ON
CREATE TABLE Table1
(cID INTEGER PRIMARY KEY
, c1 INTEGER NULL
, foolme AS (CASE WHEN c1 IS NULL THEN cID END)
, CONSTRAINT cc1 UNIQUE (c1,foolme)
)
INSERT INTO Table1
SELECT 1, 1
UNION ALL
SELECT 2, NULL
UNION ALL
SELECT 3, NULL
SET NOCOUNT OFF
SELECT * FROM Table1
DROP TABLE Table1
cID c1 foolme
----------- ----------- -----------
2 NULL 2
3 NULL 3
1 1 NULL
(3 row(s) affected)
Und auch hier führt:
INSERT INTO Table1 SELECT 4, 1
Server: Msg 2627, Level 14, State 2, Line 1
Violation of UNIQUE KEY constraint 'cc1'. Cannot insert duplicate key in object 'Table1'.
The statement has been terminated.
3. Einen Trigger verwenden:
SET NOCOUNT ON
CREATE TABLE Table1
(cID INTEGER PRIMARY KEY
, c1 INTEGER NULL
)
GO
CREATE TRIGGER IgnoreNulls
ON Table1 FOR UPDATE, INSERT
AS
IF EXISTS
(SELECT *
FROM Inserted AS I
JOIN Table1 AS t1
ON I.c1 = t1.c1 AND I.cID<>t1.cID)
BEGIN
ROLLBACK TRAN
RAISERROR('Nene...is nicht!',16,1)
END
GO
INSERT INTO Table1
SELECT 1, 1
UNION ALL
SELECT 2, NULL
UNION ALL
SELECT 3, NULL
SET NOCOUNT OFF
SELECT * FROM Table1
DROP TABLE Table1
cID c1
----------- -----------
1 1
2 NULL
3 NULL
(3 row(s) affected)
Und auch hier der vollständigkeithalber die Probe
INSERT INTO Table1 SELECT 4, 1
Server: Msg 50000, Level 16, State 1, Procedure IgnoreNulls, Line 11
Nene...is nicht!
Vereinzelt findet man auch noch eine vierte Methode erwähnt. Die entsprechende Spalte in eine eigene Tabelle zu überführen, sie zum Primärschlüssel dieser Tabelle machen und eine 1:1 Beziehung zwischen beiden Tabelle einrichten. Diese Methode möchte ich hier nicht weiter besprechen. IMHO ist dies nur ein gangbarer Weg, wenn man dies direkt beim Design der Datenbank und der Applikation berücksichtigt. Eine nachträgliche Änderung, um dieses Indexverhalten zu erzielen, ist mit einem nicht zu unterschätzenden Aufwand verbunden.
Die Reihenfolge, in welcher die verschiedenen Ansätze erwähnt wurden, stellt gleichzeitig eine gewisse Wertung dar. IMHO erscheint der 1. Ansatz mit dem Indexed View am geeignetsten. Der Index sollte das Ganze recht schnell machen und ausserdem läßt sich der View wiederverwenden. Der Index auf der berechneten Spalte sollte ebenfalls noch eine recht gute Performance bringen. Das Schlußlicht wird der Trigger sein.
Kleines Beispiel über COUNT()
Unter den diversen Aggregatfunktionen des T-SQL Arsenals nimmt COUNT() einen einmaligen Platz ein, weil dies die einzige Funtion ist, die "NULL-aware" ist. Das heißt, je nach Verwendung werden NULL Marker berücksichtigt oder nicht.
Integer in Datum umwandeln
Angenommen man hat Integer Daten in der Form 20050623 vorliegen, die ein Datum repräsentieren sollen und deshalb in einen DateTime Wert konvertiert werden sollen. Wie schön einfach wäre es nun, wenn eine direkte Konvertierung funktionieren würde:
DECLARE @i INT
SET @i = 20050623
SELECT CAST(@i AS DATETIME)
Server: Msg 8115, Level 16, State 2, Line 3
Arithmetic overflow error converting expression to data type datetime.
Ein Weg nun doch noch zum gewünschten Ergebnis zu kommen, ist die Konvertierung zuerst in eine Zeichenfolge und dann anschließend in DateTime. Etwas so:
SELECT CAST(CAST(@i AS CHAR(8)) AS DATETIME)
------------------------------------------------------
2005-06-23 00:00:00.000
(1 row(s) affected)
Funktioniert einwandfrei!
Nun kann man sich aber noch einige Tastaturanschläge sparen, wenn man sich eines kleinen Tricks bedient:
SELECT CAST(LTRIM(@i) AS DATETIME)
------------------------------------------------------
2005-06-23 00:00:00.000
(1 row(s) affected)
Funktioniert ebenfalls einwandfrei. Aber warum?
Der "Trick" dabei ist, daß man sich den Rückgabetyp mancher Stringfunktionen zunutze macht und diese Funktionen die Konvertierung in eine Zeichenfolge durchführen läßt. So lassen sich, zum Beispiel, LTRIM(), RTRIM() und STR(@i,8) in obiges Statement einbauen und all produzieren den gewünschten Effekt.
Vorsicht bei der Verwendung von ISNUMERIC()
Liest man sich die Onlinehilfe von SQL Server durch und gelangt an das Thema ISNUMERIC(), erhält man den Eindruck, daß dies eine einfache, schnelle und sichere Methode ist, um zu überprüfen, ob ein gegebener Ausdruck in einen von SQL Server unterstützten numerischen Datentypen umgewandelt werden kann. Also, in einen der Datentypen: INTEGER, FLOAT (REAL), DECIMAL (NUMERIC) und MONEY.
