JOIN Stolperfallen Teil 1
Gelegentlich fragt man sich, ob man wirklich so oft auf die Tastatur hämmern muß, oder ob man sich nicht das eine oder andere Zeichen oder Wort sparen kann. Klingt bekannt? Nun, zumindest mir geht es so. Generell ist das auch so in Ordnung, nur manchmal kann man dabei auch auf die Nase fallen. Wenn dann noch eine merkwürdige Syntaxauslegung ins Spiel kommt, wird es richtig interessant. Beispiel:
SELECT r.royalty , t.title , t.type , t.price FROM roysched r INNER JOIN titles t ON r.title_id = t.title_id ORDER BY r.royalty
Die Ergebnismenge wird hier nicht wiedergegeben. Das obige Beispiel läßt sich folgendermaßen "vereinfachen":
SELECT r.royalty , t.title , t.type , t.price FROM roysched r JOIN titles t ON r.title_id = t.title_id ORDER BY r.royalty
Das INNER Schlüsselwort kann man weglassen, da der INNER JOIN der Standard JOIN Type des SQL Servers ist. So weit, so gut!
Schaut man sich den Ausführungsplan an, sieht man, daß SQL Server NESTED LOOPS verwendet. Nun sind NESTED LOOPS gerade bei umfangreichen Abfragen nicht gerade optimal und darum kommt man vielleicht auf die Idee, den Server einen kleinen Hinweis mit auf den Weg zu geben, wie er den JOIN verarbeiten soll. Also schreibt man:
SELECT r.royalty , t.title , t.type , t.price FROM roysched r MERGE JOIN titles t ON r.title_id = t.title_id ORDER BY r.royalty
Was passiert?
Server: Nachr.-Nr. 170, Schweregrad 15, Status 1, Zeile 8 Zeile 8: Falsche Syntax in der Nähe von 'MERGE'.
Was soll das denn jetzt? Sieht doch syntaktisch einwandfrei aus. Ein Blick in BOL bestätigt dies. Warum also wird das Statement nicht ausgeführt???
Der Grund liegt darin, daß SQL Server bei der Verwendung des MERGE Hinweises (oder jedes anderen JOIN Hints) zwingend auf das INNER Schlüsselwort besteht. Ohne dem geht hier gar nichts. SQL Server ist hier nicht in der Lage, den INNER JOIN als Standard JOIN Typ anzuwenden. Also doch wieder ein paar Mal mehr auf die Tastatur hämmern
SELECT r.royalty , t.title , t.type , t.price FROM roysched r INNER MERGE JOIN titles t ON r.title_id = t.title_id ORDER BY r.royalty
Ah, unsere 86 Zeilen kommen zurück und der Ausführungsplan zeigt den MERGE JOIN an. "By design", also nicht wundern oder nachfragen.
Maximale Anzahl von Verweisen auf Fremdschlüsseltabellen pro Tabelle
Was soll denn diese Frage? Ein Blick in BOL in die Spezifikationen der maximalen Kapazität sagt einem sofort, daß SQL Server 2000 253 Verweise auf Fremdschlüsseltabellen pro Tabelle haben kann. So, damit wäre diese Frage ja schon beantwortet. Fertig, Neues Thema...Halt
Nichtnumerische Zeichen aus einem String entfernen
Das Problem hat wahrscheinlich jeder schon einmal gehabt. Man stellt Importanforderungen auf, und die Anwender kümmern sich nicht darum und liefern anstelle von sauber getrennten Strings und Zahlen einen bunten Mischmasch aus beidem.
n Datensätze pro Gruppierung anzeigen
Zugegeben ist das Subjekt nicht sehr treffend, aber im Moment fällt mir kein Besseres ein.
Gestern stellte jemand auf SQL Server Central.com die Frage, wie man zu jedem Namen mehr als ein Datum anzeigen kann. Genauer gesagt, die beiden aktuellsten Daten. Diese Frage kann man leicht auf die Northwind Beispieldatenbank übertragen: Zeige mir zu jedem Kunden die beiden letzten Bestelldaten ein.
Foreign Key Constraints löschen
Das nachfolgende Skript löscht alle existierenden Einschränkungen für die angegebene Tabelle. Will man alle Einschränkungen der aktuellen Datenbank löschen, muss man einfach nur die WHERE Zeile auskommentieren oder entfernen.
Trigger umgehen
Gleich vorwegschicken möchte ich, daß dieser Beitrag nicht zur Nachahmung oder zum Einsatz in Produktivsystemen gedacht ist. Vielmehr eher als reine Spielerei mit dem, was möglich ist.
Also, kann die Ausführung eines Triggers umgangen werden? Einfache Antwort: Ja! Und zwar auf mehreren Wegen, von denen ich hier nur einen anführen will.
Die Ausgangssituation:
CREATE TABLE dbo.test_trigger
(
c1 INT
, c2 CHAR(10)
)
GO
CREATE TRIGGER fool_me ON dbo.test_trigger FOR INSERT
AS
BEGIN
UPDATE dbo.test_trigger SET c2 = 'Hallo Welt'
END
GO
Dieser Trigger soll also bei jedem INSERT den Wert 'Hallo Welt' in die Spalte c2 schreiben. Das das funktioniert, kann man einfach feststellen:
INSERT INTO dbo.test_trigger (c1) VALUES(1)
INSERT INTO dbo.test_trigger (c1, c2) VALUES(1, 'wtf')
SELECT * FROM dbo.test_trigger
c1 c2
----------- ----------
1 Hallo Welt
1 Hallo Welt
So, egal,ob man nun explizit einen Wert in die Spalte c2 eintragen will oder nicht, der Wert wird durch den Trigger überschrieben.
Die Manipulation:
Und zwar nutzen wir die Spalte context_info in der master.dbo.sysprocesses Tabelle. Diese kann durch die Einstellung SET CONTEXT_INFO zusätzliche 128 Bytes pro Session oder Connection speichern. Zunächst ändern wir mal unseren Trigger ab.
ALTER TRIGGER fool_me ON dbo.test_trigger FOR INSERT AS
IF NOT (SELECT context_info FROM master.dbo.sysprocesses
WHERE spid = @@SPID) = 0xA
BEGIN
UPDATE dbo.test_trigger SET c2 = 'Hallo Welt'
END
GO
So, nun können wir SET CONTEXT_INFO verwenden:
SET CONTEXT_INFO 0xA
INSERT INTO dbo.test_trigger (c1, c2) VALUES(2, 'q.e.d')
SET CONTEXT_INFO 0
Das Ergebnis:
SELECT * FROM dbo.test_trigger
c1 c2
----------- ----------
1 Hallo Welt
1 Hallo Welt
2 q.e.d
Nochmals der Hinweis: Dies ist kein Skript, was in Produktionssystemen verwendet werden sollte. Die Manipulation von SET CONTEXT_INFO kann zu Nebeneffekten führen bei anderen DB Objekten, die ebenfalls auf diese Einstellung zugreifen.
Danke an Jonathan van Houtte!
Unterschiede zwischen ISNULL und COALESCE
Es gibt keine Unterschiede zwischen ISNULL und COALESCE. Diese Meinung kann man recht häufig in Online Communities lesen. Der einzige Unterschied zwischen beiden ist, daß ISNULL SQL Server spezifisch ist, während COALESCE ANSI-SQL Standard ist. Auch dies kann man recht häufig lesen. Beide beschäftigen sich mit der Umwandlung von NULL und damit fehlenden Informationen. Wozu also einen eigenen Beitrag?
In welcher Reihenfolge gibt SELECT * die Spalten zurück
Vielleicht hat sich der Eine oder Andere schon mal gefragt, wieso und warum SQL Server bei einem SELECT * Statement die Spalte in der Art und Weise zurückgibt, in der es geschieht. Zum Beispiel:
CREATE TABLE #test
(
col1 CHAR
, col2 INT
, col3 FLOAT
, col4 VARCHAR
)
GO
INSERT INTO #test VALUES('a',1,0.9,'A')
SELECT * FROM #test
col1 col2 col3 col4
---- ----------- ----------------------------------------------------- ----
a 1 0.90000000000000002 A
Die Reihenfolge der Spalten entspricht bei einem SELECT * immer stets der Reihenfolge, in der sie beim CREATE TABLE Statement angegeben wurden. Das heißt, im Grunde werden die Spalten in aufsteigender Reihenfolge der colid in der Tabelle syscolumns zurückgegeben. Hier ist ein Ausschnitt aus
EXEC sp_help #test
Column_name
------------
col1
col2
col3
col4
Und ist der entsprechende Ausschnitt aus syscolumns:
SELECT
CAST(name AS CHAR(30)) AS [name]
, colid
FROM
syscolumns
WHERE
id=OBJECT_ID('#test')
name colid
------------------------------ ------
col1 1
col2 2
col3 3
col4 4
Erwähnt werden sollte auch, daß ein SELECT * Statement nichts in Produktionscode zu suchen hat, IMHO. Und das die physikalische Speicherung der Storage Engine wiederum eine komplett andere ist.
