Warum Konventionen SQL Code besser machen

Es ist nachvollziehbar, wenn man nach der hundertsten Query nicht mehr geflissentlich allen Konventionen oder Regeln nachgeht. Jedoch dienen diese nicht nur der Lesbarkeit des Codes, sondern auch der Korrektheit. Im folgenden Artikel wollen wir auf genau diese Unterschiede eingehen und erklären warum manche Konventionen unserer Meinung nach tatsächlich sinnvoll sind.

Wie sieht ein schlechtes Beispiel aus

Um häufige Fehler im Code aufzuzeigen, haben wir das folgende Skript geschrieben. Dieses bezieht sich auf die Adventureworks Datenbank. Aufgabe war es, die Namen und IDs der Locations auszugeben, die mehr als 500 verschiedene Produkte beinhalten. Dabei werden zudem die ProduktIDs ausgegeben, von denen es weniger als die vorgegebenen Einheiten gibt. Unser Skript sieht so aus:

create procedure get_locations(@paramIntQuantity INT)                                        
as                                                                                           
  --ein Produkt weniger als x-mal vorhanden                                                  
  select locationID, name, productID                                                         
  from Production.Location a (nolock), ProductInventory b (nolock)                           
  where a.locationid = b.locationid
  and b.Quantity < @paramIntQuantity and (select locationId from productInventory (nolock)   
  where count(productID)>=500 group by locationID)

Verbesserungen des Skriptes

Zeile 1

create procedure get_locations(@paramIntQuantity INT)

Als Erstes möchten wir anmerken, dass alles in lower-case geschrieben ist. Wenn etwas schnell geschrieben wird, achtet man nicht darauf. Hat man am Ende jedoch ein großes kompliziertes Skript, wird das Lesen erschwert. Darum ist es durchaus sinnvoll zumindest die Schlüsselworte von SQL groß zu schreiben, damit man erkennen kann, wo die Struktur der Syntax aufhört und der tatsächliche Inhalt beginnt.

Die nächste ratsame Änderung ist das Hinzufügen des Schema-Präfixes. Wenn das System nur in dbo läuft, wird es zunächst kein Problem sein. Fügt man jedoch ein weiteres Schema hinzu, ist die Zuordnung nicht mehr eindeutig. Will man eine Prozedur aus dem dbo Schema aufrufen, kommt es zum Problem wenn man sich mit dem madafa Schema eingeloggt hat. Befindet sich darin eine Prozedur mit demselben Namen, so wird diese ausgeführt. Auch aus Sicht der Performance hat es Vorteile, den Präfix hinzuzufügen. So kann derselbe Query Plan erst dann von unterschiedlichen Usern genutzt werden, wenn das Schema vorher spezifiziert wurde.

Des Weiteren sollte auf eine aussagekräftige Namensgebung geachtet werden. Abhängig ist das natürlich davon, ob es bereits Vorgaben für das Projekt gibt. Generell ist in meinen Augen ‘entity_action' eine gute Struktur. Also lieber ‘Locations_GetRandomInfo’, statt ‘GetRandomInfo_Locations’. Denn es wird eher nach allen Funktionen für ‘location’ gesucht, anstatt nach allen Dingen, die man mit einer 'get’ Funktion erhalten kann.

Das Letzte, das wir zu der Zeile bemerken, ist die Parameterdeklaration. Kurz anzumerken ist, dass die Klammern um die Parameter herum nicht notwendig sind. Daher ist hier die Struktur besser, die Sie übersichtlicher finden. Die Namensgebung ist auch hier wieder anpassungsfähig. Im Namen müssen nicht offensichtliche Dinge wiederholt werden. In diesem Beispiel ist es klar, dass es sich um einen Parameter ('param') des Typs Integer ('Int') handelt. Zudem sollte darauf geachtet werden, dass die Großschreibung stimmt. Dies kann besonders wichtig bei Case-sensitiven Datenbanken sein. Außerdem sieht eine einheitliche Schreibweise von Variablen und Datentypen besser aus. Der letzte Punkt ist die Formatierung der Parameter. In diesem Beispiel ist es noch nicht schlimm - hat man aber mehrere Parameter, ist es unpraktisch nach rechts scrollen zu müssen, um alles lesen zu können. Hier empfiehlt es sich, unter dem Namen der Prozedur zu beginnen und jeden Parameter eingerückt in eine neue Zeile zu schreiben.

Zeile 2

as

In dieser Zeile ist nicht direkt ein Fehler. Jedoch fehlt uns ein BEGIN und an späterer Stelle ein END. Mit diesen kleinen Worten lässt sich der Körper der Funktion gut eingrenzen und es sagt dem Programm eindeutig, wo die Funktion endet.

Zeile 3

--ein Produkt weniger als x-mal vorhanden

Hier geht es um die Art und Weise wie kommentiert wurde. Häufig werden Skripte von Monitoring Tools oder diagnostischen Views in einer Zeile ausgegeben. Kommentiert man nun mit den doppelten Bindestrichen ist hinten nicht zu erkennen, wann der Kommentar aufhört. Deswegen ist /diese/ Struktur übersichtlicher. Am Ende ist es jedoch eine Gewohnheitssache.

Zeile 4

select locationID, name, productID

Auch hier sollte darauf geachtet werden, dass die Schreibweise der Namen den Metadaten entsprechen. Das gilt hierbei nicht nur für die Spaltennamen und tritt an späteren Stellen wieder auf - zudem fehlen die Präfixe, aus welchen Tabellen die Daten kommen sollen.

Zeile 5

from Production.Location a (nolock), ProductInventory b (nolock)

An dieser Stelle merken wir mehrere Dinge an: Zum einen ist der Code wieder inkonsistent. Bei der ersten Tabelle wird das Schema hinzugefügt, bei der zweiten nicht. Wenn man es auf eine Weise macht, sollte man das für den Rest des Codes ebenso behandeln. Außerdem sind die Aliase nicht aussagekräftig. Wird später im Code darauf verwiesen, ist erstmal nicht ersichtlich wofür ‘a' und 'b’ stehen. Auch die alte Join Schreibweise sollte nicht mehr genutzt werden. Durch eine explizite Beschreibung des Joins kann er zum einen schneller geändert werden, die Ordnung muss stimmen und die Join Bedingung kann direkt angegeben werden, ohne sie in das WHERE zu schreiben.

Der wichtigste Punkt, den wir hier aufführen, ist der Gebrauch von NOLOCK. Dieser Einstellung wird nachgesagt, dass sie Queries schneller machen soll. Für manche Szenarien ist das auch der Fall, hat aber bei dauerhafter Verwendung auch Nebeneffekte. Ohne zu sehr ins Detail zu gehen - wenn NOLOCK verwendet wird, lässt sich das mit ‘READ UNCOMITTED’ vergleichen. Beim Einlesen der Daten werden Locks von anderen Prozessen ignoriert, was zu einer schnelleren Ausführung beitragen soll. Das Problem ist, dass dies die Korrektheit der Daten beeinflusst. Denn es kann passieren, dass Änderungen eingelesen werden, die wieder verworfen wurden, dass Spalten doppelt oder gar nicht eingelesen werden oder sogar verschiedene Versionen von Reihen entstehen. Wenn also der Fokus nicht auf die Richtigkeit der Daten liegt, sondern die Laufzeit wichtiger ist, kann in diesem Fall NOLOCK benutzt werden. In allen anderen Fällen, braucht es erst einen anderen guten Grund, bevor ein NOLOCK in Erwägung gezogen wird.

Zeile 7

and b.Quantity < @paramIntQuantity and (select locationId from productInventory (nolock)

Neben den bereits angesprochenen Besserungen, will sprechen wir nun über die Subquery. An dieser Stelle wird nach der Quantity gefiltert und gleichzeitig soll das Ergebnis ebenfalls in der Subquery sein. Jedoch fehlt an dieser Stelle, was diese Bedingung eigentlich erfüllen soll. Dies kann zu einem Fehler, bzw. zu einem falschen Ergebnis führen. Aus diesem Grund sollte auf jeden Fall ein IN oder EXIST spezifiziert werden. Eine weitere Sache ist eine fehlende strukturelle Unterscheidung zwischen äußerer und innerer Query. Hier einen kleinen Absatz mit Einrückung einbauen und schon ist das Ganze wieder schön übersichtlich.

Verbesserungsmöglichkeiten

Wendet man nun alle oben aufgeführten Verbesserungen an, kann das Skript folgendermaßen aussehen. Es ist nicht nur übersichtlicher - sondern mit den zusätzlichen Angaben kann man sicherstellen, dass das System genau das macht, was man von ihm will.

CREATE PROCEDURE dbo.Locations_GetSomeRandomInfo
  @CategoryID int
AS
BEGIN
  SET NOCOUNT ON;
  /*ein Produkt weniger als x-mal vorhanden*/
  SELECT l.LocationID, l.Name, pi.ProductID
  FROM Production.Location AS l
  JOIN Production.ProductInventory AS pi
  ON l.LocationID = pi.LocationID
  WHERE pi.Quantity < 5 AND l.LocationID in (
    /*500 verschiedene Produkte*/
    SELECT LocationID
    FROM Production.ProductInventory
    GROUP BY LocationID
    HAVING COUNT(ProductID) >= 500);
END

Wir fügen zu Beginn des Funktionskörpers SET NOCOUNT ON; hinzu. Wenn Prozesse auf der Datenbank ausgeführt werden, schickt diese immer wieder kleine Bestätigungsnachrichten. Wenn man nicht mit diesen Nachrichten arbeiten will, sind sie in den meisten Fällen nutzlos. Darum wird häufig diese Zeile hinzugefügt, damit dieser Verkehr an Nachrichten gestoppt wird. Auch wenn der Effekt auf die Leistung kaum merklich ist, so bleiben im Terminal die Nachrichten, wie viele Reihen geändert wurden, erspart.

Weitere häufige Fehler

Nachdem wir nun über sinnvolle Konventionen gesprochen haben, wollen wir uns mit häufigen Fehlern darüber hinaus beschäftigen.

BETWEEN Operator
Häufig wird dieser verwendet, um einen Zeitraum anzugeben. Zum Beispiel möchten wir alle Namen der Kunden einsehen, die im Monat Mai eine Bestellung getätigt haben. Alternativ würde man BETWEEN folgendermaßen benutzen:

SELECT Name
FROM Kunden
WHERE OrderDate BETWEEN '2023-05-01' AND '2023-06-01'

Das Problem hierbei ist, dass BETWEEN eine einschließende Menge beschreibt. Das heißt, wir erhalten in diesem Fall eine Ausgabe zum Monat Mai und den ersten Tag im Juni. Wenn man diese Besonderheit nicht beachtet, erhält man falsche Ergebnisse. Ändern kann man dies, indem man entweder die obere Schranke auf den letzten Tag im Mai setzt, oder das BETWEEN komplett weglässt und die Schranken über Vergleichsoperatoren beschreibt.

SELECT Name
FROM Kunden
WHERE OrderDate >= '2023-05-01'
  AND OrderDate < '2023-06-01'

Wichtig anzumerken ist, dass die Reihenfolge der Schranken essentiell ist. Wenn wir einer andere Person sagen ‘zwischen 7 und 3’, so wird sie wissen, was gemeint ist. Bei Werten zwischen 1 und 10 würden wir die Menge {3,4,5,6,7} erwarten. Das Programm macht aber eine Unterscheidung zwischen unterer und oberer Schranke. Bei dem ersten Wert, also der unteren Schranke wird geprüft, ob die Werte größer oder gleich der angegebenen Schranke sind. Bei einer unteren Schranke von 7, fallen alle Werte über der 7 und die Schranke selbst in den Wertebereich. Bei der oberen Schranke, in unserem Beispiel die 3, wird geprüft, ob alle Werte kleiner oder gleich sind. Hier werden also alle Werte kleiner oder gleich 3 zum Wertebereich hinzugefügt. Die Menge der unteren Schranke ist damit {7,8,9,10} und die der Oberen {1,2,3}. Der Schnitt dieser beiden Mengen ist leer, weshalb wir eine leere Menge als Ergebnis haben und somit nicht das Ergebnis, dass wir erwartet haben.

Datetime Datentyp

In dem Typen Datetime werden sowohl das Datum, als auch die Zeit gespeichert. Aus diesem Grund wird dieser Typ sehr häufig verwendet. Jedoch sollte man wissen, wie man damit umzugehen hat. Will man naiv nach einem Tag suchen, sieht die Query möglicherweise so aus:

SELECT *
FROM SomeLogs
WHERE time = '2023-05-01'

Das Problem hierbei ist, dass nicht alle Daten ausgegeben werden, wenn es denn überhaupt welche gibt. Denn der Eintrag der abgefragt wird, ist im Format '2023-05-01T00:00:00.000'. Gibt es keinen Eintrag zu dieser genauen Uhrzeit, wird auch nichts ausgegeben. Aus diesem Grund muss immer eine Abfrage über einen Zeitraum gemacht werden. Es bietet sich wieder die Verwendung von BETWEEN an, jedoch sollte auf die oben angesprochenen Punkte geachtet werden. Eine weitere Möglichkeit wäre, sich eigene Prozeduren zu schreiben. Ein Beispiel, in dem man sowohl Start als auch Ende bestimmen kann, sieht so aus:

CREATE PROCEDURE dbo.LogsByTimeRange
    @StartDate smalldatetime,
    @EndDate   smalldatetime
AS
BEGIN
    SET NOCOUNT ON;
 
    SELECT *
        FROM SomeLogs
        WHERE DateColumn >= @StartDate
        AND DateColumn < @EndDate;
END

`UNION` und `UNION ALL`

UNION wird dafür verwendet, Ergebnismengen miteinander zu verbinden. Zum Beispiel sucht man Mitarbeiter die im Juni Geburtstag haben oder deren Geburtstag am 7. Tag des Monats sind. Würde man hier UNION ALL verwenden, wird es dazu kommen, dass die Mitarbeiter die in beiden Mengen vorkommen, doppelt aufgeführt werden. Das ist bei UNION nicht der Fall, da hier alle doppelten Einträge ignoriert werden. Dies schlägt sich im Gegenzug auf die Performance aus. Darum sollte für jeden Fall entschieden werden, ob solch eine Unterscheidung notwendig ist oder nicht.

Typen Konversionen
Wenn man in einem großen Projekt arbeitet oder Daten abfragen will, von denen man nicht die genauen Datentypen kennt - kann es ungewollt dazu kommen, dass die Typen umgewandelt werden. Das kann passieren, wenn man eine Prozedur schreibt und einen Parameter als NVARCHAR bekommen will. Nun war mir nicht bewusst, dass mein Kollege die entsprechende Spalte als VARCHAR abgespeichert hat. Es kommt zu keinem Fehler, da das System automatisch die gebrauchten Daten in den gewünschten Typen umwandelt. Jedoch muss das für jeden Eintrag gemacht werden und kostet daher sehr viel Laufzeit. Diese unnötige Berechnung kann man sich sehr leicht sparen.

Besonders problematisch ist dies hinsichtlich von Indizierung. Der Index soll eigentlich dabei helfen, dass Einträge sehr schnell durchsucht und gefiltert werden können. Wendet man nun eine Funktion auf die Daten an, die diese umwandelt, macht man sich diesen Nutzen zunichte. Denn bevor die Daten irgendwie gefiltert werden können, muss für jede Reihe die Funktion ausgeführt werden. Erst dann kann entschieden werden, ob der Wert die Bedingung erfüllt. Aus diesem Grund sollte man sehr vorsichtig sein, ob man wirklich Funktionen auf Werte anwenden will, um sie danach zu vergleichen.

NOT IN mit NULL Werten
Hierfür machen wir am besten ein Beispiel. Wir haben zwei Tabellen. Eine mit allen Filmen, die gespielt werden und eine andere, in der gespeichert wird, welcher Film in welchem Saal läuft.

Das ist die Tabelle aller Filme:

Und hier haben wir die Tabelle der Kinosäle:

Das Ziel ist nun herauszufinden, welcher Film in keinem Saal gespielt wird. Diese Query könnte so aussehen

SELECT FilmID
FROM Filme
WHERE FilmID NOT IN (
  SELECT k.FilmID
  FROM Kinosäle AS k)

Man würde erwarten, dass der ‘F2’ ausgegeben wird. Stattdessen kommt nichts zurück. Der Grund dafür liegt an dem NULL. Der Term, den die Query prüft, sieht so aus

NOT(FilmID=F3 OR FilmID=F1 OR FilmID=NULL)

Da FilmID=NULL den Wert UNKNOWN hat, wertet der gesamte Term zu UNKNOWN aus und es wird nichts zurückgegeben. Lösen kann man das Ganze, indem man stattdessen EXISTS benutzt oder in der Subquery nur alle Werte ausgibt, die nicht NULL sind.

SELECT FilmID
FROM Filme
WHERE NOT EXISTS (
  SELECT k.FilmID
  FROM Kinosäle AS k)

Fazit

In diesem Artikel sind wir nun auf alle häufigen Probleme hinsichtlich Konventionen und SQL Code eingegangen.
Sollten Sie weitere Fragen oder Anmerkungen zu diesem Thema haben, stehen Ihnen unsere Expert:innen mit Rat und Tat zur Seite. Vereinbaren Sie dafür ein unverbindliches Erstgespräch über unser Kontaktformular und lernen Sie uns kennen.