T-SQL-Programmierstandards
Veröffentlicht: 25. Apr 2005
Von Brian Walker
Dieser Artikel kann URLs enthalten, die bei der Originalveröffentlichung gültig waren, jetzt jedoch auf Sites oder Seiten verweisen, die nicht mehr existieren. Um den Lesefluss des Artikels nicht zu stören, haben wir diese URLs beibehalten, die Links jedoch deaktiviert.
.gif "SQL Server Professional")
So überraschend es auch sein mag, es gibt scheinbar keine "offiziellen" T-SQL-Programmierstandards. 1999 fand ich zu meiner Freude heraus, dass John Hindmarsh einen Entwurf für SQL Server 7.0-Standards entwickelt hatte, von denen er einige im Februar 2000 in einem Artikel zusammenfasste. (Die ursprünglichen Standards von John waren in den Downloads vom Februar 2000 enthalten und werden auch in diesem Monat zur Verfügung gestellt.) Unter den neueren Veröffentlichungen sind einige Artikel zu bewährten Verfahren von Ron Talmage und natürlich der vom SQL Server-Team veröffentlichte Best Practices Analyzer für SQL Server (SQLBPA) zu erwähnen. Brian Walker, ein Datenbankadministrator und Anwendungsentwickler mit über 25-jähriger Arbeitserfahrung, steuert jetzt einige Tipps und Ratschläge zu diesem Thema bei.
Bei der T-SQL-Programmierung werden Standards häufig vernachlässigt. Programmierstandards bilden jedoch die Grundlage für ein reibungslos arbeitendes Entwicklungsteam. Die Programmierstandards, die ich hier vorstellen möchte, habe ich in langjähriger Praxis zusammengetragen. Sie sind keinesfalls allgemein anerkannt und einige davon sind sehr auf meine Bedürfnisse zugeschnitten. Ich strebe mit dieser Veröffentlichung keine Ernennung zum Sachverständigen der T-SQL-Programmierung an. Es geht mir vielmehr darum, das Bewusstsein für die Notwendigkeit solcher Standards zu verstärken: Die Ausarbeitung eines angemessenen Programmierstandards und dessen Einhaltung ist die wichtigste Grundlage für eine effektive Entwicklung. In diesem Artikel finden Sie eine Reihe verschiedener Programmierstandards, Hinweise und Tipps für die T-SQL-Programmierung. Die Anordnung der Informationen unterliegt jedoch keiner bestimmten Sortierung nach Wichtigkeit oder anderen Prioritäten.
Fangen wir zunächst mit der Formatierung an. Auf den ersten Blick scheint die Formatierung des T-SQL-Codes keine große Rolle zu spielen. Ein einheitliches Format macht es für Ihre Kollegen (gleichgültig ob aus Ihrem Team oder der weltweiten T-SQL-Community) jedoch leichter, den Programmcode zu analysieren und zu verstehen. T-SQL-Anweisungen sind strukturiert, und die visuelle Hervorhebung dieser Struktur vereinfacht eine Suche und Überprüfung bestimmter Teile der Anweisungen. Außerdem lassen sich bei einer einheitlichen Formatierung Codebereiche zu Debug-Zwecken ohne große Probleme zu komplexen T-SQL-Anweisungen hinzufügen oder aus diesen entfernen. Im Folgenden finden Sie ein Beispiel für die Formatierung einer SELECT-Anweisung:
SELECT C.Name
, E.NameLast
, E.NameFirst
, E.Number
, ISNULL(I.Description,'NA') AS Description
FROM tblCompany AS C
JOIN tblEmployee AS E
ON C.CompanyID = E.CompanyID
LEFT JOIN tblCoverage AS V
ON E.EmployeeID = V.EmployeeID
LEFT JOIN tblInsurance AS I
ON V.InsuranceID = I.InsuranceID
WHERE C.Name LIKE @Name
AND V.CreateDate > CONVERT(smalldatetime,
'01/01/2000')
ORDER BY C.Name
, E.NameLast
, E.NameFirst
, E.Number
, ISNULL(I.Description,'NA')
SELECT @Retain = @@ERROR, @Rows = @@ROWCOUNT
IF @Status = 0 SET @Status = @Retain
Verwenden Sie für Anweisungen innerhalb eines geschachtelten Codeblocks Einrückungen mit vier Leerzeichen. (Die mehrzeilige SELECT-Anweisung im vorhergehenden Code ist eine einzige SQL-Anweisung). Richten Sie SQL-Schlüsselwörter an der rechten Seite aus, wenn Sie eine neue Zeile innerhalb derselben Anweisung beginnen. Konfigurieren Sie den Code-Editor so, dass Tabstoppzeichen anstelle von Leerzeichen verwendet werden. Dadurch bleibt die Formatierung einheitlich, unabhängig vom Programm, das zum Anzeigen des Codes verwendet wird.
Schreiben Sie alle T-SQL-Schlüsselwörter und T-SQL-Funktionen in Großbuchstaben. Verwenden Sie für Variablen- und Cursornamen Groß- und Kleinschreibung. Für Datentypen sollten Sie Kleinschreibung verwenden.
Die Aliase von Tabellennamen sollten kurz, jedoch möglichst aussagekräftig sein. Verwenden Sie, sofern möglich, den ersten Buchstaben eines Tabellennamens als Alias und legen Sie die Aliase für Tabellen und Felder mit dem AS-Schlüsselwort fest.
Kennzeichnen Sie einen Feldnamen immer mit einem Tabellennamenalias, wenn eine T-SQL-Anweisung mehrere Tabellen enthält. Dies dient der Übersichtlichkeit und verhindert mehrdeutige Verweise.
Richten Sie zusammenhängende Zahlenwerte in Spalten aus, wenn diese in aufeinander folgenden Codezeilen vorkommen, z. B bei mehreren SUBSTRING-Funktionsaufrufen. Dadurch kann die Liste der Zahlenwerte leichter durchsucht werden.
Verwenden Sie einzelne (keine doppelten) Leerzeilen zur logischen Unterteilung der T-SQL-Codeabschnitte und gehen Sie dabei nicht zu sparsam vor.
Verwenden Sie geeignete Datentypdeklarationen und einheitliche Groß- und Kleinschreibung, um lokale T-SQL-Variablen (z. B. @lngTableID) zu deklarieren.
Geben Sie die Länge eines Zeichendatentyps immer an und stellen Sie sicher, dass die maximale, von Benutzern benötigte Zeichenanzahl verfügbar ist, da Zeichen, die die maximale Länge überschreiten, verloren gehen.
Legen Sie die Dezimalstellen und die Genauigkeit von Dezimaldatentypen immer fest, da andernfalls die Standardeinstellungen mit nicht festgelegter Genauigkeit und Ganzzahlen verwendet werden.
Wenden Sie Fehlerbehandlung an. Denken Sie jedoch daran, dass die Beispiele zur Fehlerüberprüfung in den Onlinedokumentationen nie so funktionieren, wie sie es sollten. Beim Überprüfen der @@ERROR-Systemfunktion mit der T-SQL-Anweisung IF wird der @@ERROR-Wert gelöscht. Somit erhalten Sie immer den Wert 0. (Auch wenn die Beispiele funktionieren, wird anstatt des ersten Fehlers (der meistens die wichtigeren Informationen enthält) immer nur der zuletzt aufgetretene Fehler abgefragt). Der Fehlercode muss unmittelbar nach dem Auftreten des Fehlers mithilfe von SET oder SELECT erfasst werden (wie im vorherigen Beispiel gezeigt). Er sollte anschließend an eine Statusvariable übermittelt werden, wenn die Statusvariable immer noch 0 beträgt.
Vermeiden Sie die Verwendung von "undokumentierten" Features, wie undokumentierte Spalten in Systemtabellen, undokumentierte Funktionen in T-SQL-Anweisungen oder undokumentierte Systemprozeduren bzw. erweiterte gespeicherte Prozeduren.
Verlassen Sie sich nicht auf implizite Datentypkonvertierungen. So sollten Sie einer Zahlenvariablen keinen Zeichenwert in der Annahme zuordnen, dass T-SQL die notwendigen Konvertierungen vornimmt. Verwenden Sie stattdessen die geeignete CONVERT-Funktion, um die Datentypen vor einer Variablenzuweisung oder einem Wertevergleich anzugleichen. Noch ein Beispiel: Auch wenn T-SQL vor einem Vergleich für Zeichenausdrücke einen impliziten und automatischen RTRIM durchführt, sollten Sie sich auch auf dieses Verfahren nicht verlassen, da Kompatibilitätsgradeinstellungen den Vorgang erschweren.
Führen Sie keinen direkten Vergleich zwischen Variablenwerten und NULL mit Vergleichsoperatoren (Symbolen) durch. Wenn die Variable null betragen kann, sollten Sie für den Vergleich IS NULL oder IS NOT NULL bzw. die Funktion ISNULL verwenden.
Verwenden Sie die Funktion STR nicht für Rundungen, sondern nur für Ganzzahlen. Verwenden Sie vor der Konvertierung in eine Zeichenfolge die CONVERT-Funktion (andere Dezimalstellen) oder die ROUND-Funktion, wenn ein dezimales Zeichenfolgenformat benötigt wird. Sie können auch CEILING und FLOOR verwenden.
Gehen Sie bei der Nutzung mathematischer Formeln mit Bedacht vor, da T-SQL möglicherweise für einen Ausdruck einen unerwünschten Datentyp erzwingt. Fügen Sie bei ganzzahligen Konstanten eine Kommastelle (.0) hinzu, wenn es sich bei der Ausgabe um eine Dezimalzahl handeln soll.
Verlassen Sie sich nicht darauf, dass eine SELECT-Anweisung Zeilen in einer bestimmten Reihenfolge ausgibt, wenn die gewünschte Reihenfolge nicht durch eine ORDER BY-Klausel festgelegt wurde.
In der Regel sollten Sie in Verbindung mit einer SELECT-Anweisung immer eine ORDER BY-Klausel verwenden. Eine vorhersehbare Reihenfolge, auch wenn es sich dabei nicht um die optimale Reihenfolge handelt, ist immer besser als eine nicht vorhersehbare Reihenfolge, dies gilt besonders für die Entwicklung und das Debugging. (Unter Umständen sollten Sie die ORDER BY-Klausel vor der Weitergabe in den Produktionszyklus entfernen). In Situationen, in denen die Reihenfolge der ausgegebenen Zeilen jedoch keine Rolle spielt, sollten Sie sich nicht mit dem zusätzlichen Aufwand belasten, den eine ORDER BY-Klausel verursacht.
Verwenden Sie in Ihrem T-SQL-Code keine doppelten Anführungszeichen. Verwenden Sie stattdessen einfache Anführungszeichen. Wenn es notwendig ist, einen Objektnamen zu kennzeichnen, setzen Sie den Namen in Klammern (nicht ANSI-SQL-Standard).
Verwenden Sie in SQL Server 2000 so häufig wie möglich Tabellenvariablen anstelle von temporären Tabellen. Beachten Sie dabei, dass nur eine sehr eingeschränkte Indizierung möglich ist, wenn die Tabellenvariable große Datenmengen enthält (nur Primärschlüsselindizierung).
Erstellen Sie temporäre Tabellen nur in der Routine und löschen Sie sie explizit am Ende. Wahllos eingeschobene DDL- und DML-Anweisungen können dazu führen, dass der Code häufig neu zu kompilieren ist.
Sie sollten jetzt jedoch nicht davon ausgehen, dass temporäre Tabellen gänzlich unbrauchbar sind. Eine gezielte Verwendung kann wesentlich zur Effizienz einiger Routinen beitragen – wenn beispielsweise wiederholt auf einen bestimmten Datensatz aus einer großen oder häufig verwendeten Tabelle verwiesen wird. Für die einzelne Verwendung ist eine abgeleitete Tabelle jedoch besser geeignet.
Gehen Sie auch bei mit Tabellenwerten versehenen User Defined Functions (UDF – benutzerdefinierte Funktionen) mit Bedacht vor, da hierbei die Übergabe eines Parameters mit einer Variablen anstatt einer Konstanten zu Tabellensuchvorgängen führen kann, wenn der Parameter in einer WHERE-Klausel verwendet wird. Vermeiden Sie außerdem die mehrmalige Verwendung derselben mit Tabellenwerten versehenen UDF in einer einzelnen Abfrage. UDFs mit Tabellenwerten verfügen jedoch über einige sehr nützliche dynamische Kompilierungsfunktionen. [Verweis: Sehen Sie sich in diesem Zusammenhang auch an, wie Tom Moreau in seinem Artikel zur Erstellung von Sequenznummern vom November 2003 eine UDF verwendet, um eine Tabellenvariable zu füllen. – Hrsg.]
Bei fast jeder gespeicherten Prozedur sollte am Anfang SET NOCOUNT ON gesetzt sein. Es zeichnet einen guten Programmierstil aus, am Ende SET NOCOUNT OFF zu setzen. [Durch SET NOCOUNT ON wird verhindert, dass SQL Server für jede Anweisung in einer gespeicherten Prozedur DONE_IN_PROC-Meldungen an den Client sendet. – Hrsg.]. Dieser Standard gilt auch für Trigger.
Wenn eine Routine mehr als eine Datenbankänderungsanweisung enthält, sollten Sie in Erwägung ziehen, eine explizite Transaktion zu deklarieren. Dies beinhaltet die mehrfache Ausführung einer einzelnen Anweisung in einer Schleife.
Versuchen Sie immer, eine datensatzbasierte Lösung für ein Problem zu finden, bevor Sie auf eine cursorbasierte Herangehensweise oder temporäre Tabellen zurückgreifen. Eine datensatzbasierte Lösung ist in den meisten Fällen effizienter.
Cursor sind, wie auch temporäre Tabellen, nicht vollständig unbrauchbar. Bei kleinen Datenmengen ist ein FAST_FORWARD-Cursor oftmals schneller und wirkungsvoller als andere zeilenbasierte Verarbeitungsmethoden. Dies gilt insbesondere, wenn auf mehrere Tabellen verwiesen werden muss, um die erforderlichen Daten abzurufen. Eine Routine, die im Ergebnissatz akkumulierte Gesamtwerte enthält, wird in vielen Fällen am schnellsten ausgeführt, wenn sie mithilfe eines Cursors implementiert wird. Wenn der Zeitraum für die Entwicklung es zulässt, sollten Sie eine cursorbasierte und eine datensatzbasierte Lösung testen, um festzustellen, welche Lösung die besseren Ergebnisse erzielt.
Eine Sequenznummerntabelle (1 bis N) kann sehr nützlich sein.
Machen Sie sich mit der Funktionsweise und den Vorteilen von CROSS JOIN vertraut. Ein CROSS JOIN eignet sich hervorragend zur Verwendung zwischen einer Tabelle mit Arbeitsdaten und einer Sequenznummerntabelle. Der Ergebnissatz enthält dadurch einen Datensatz für jede Kombination von Arbeitsdaten und Sequenznummern.
Zum Abschluss fasse ich diese Punkte folgendermaßen zusammen: T-SQL-Code ist im Allgemeinen sehr präzise und kurz. Wenn demnach ein Codeabschnitt kompliziert und voller Wiederholungen erscheint, lässt sich mit hoher Wahrscheinlichkeit eine einfachere und bessere Methode finden.
Schlussbemerkung
Sie können mir gern per E-Mail Kommentare, Anregungen und Verbesserungen zu diesen Empfehlungen senden. Dieser Artikel sollte wie die Eröffnungsrede zu einer anregenden Diskussion gesehen werden.
Randbemerkung: Aus Karens Veröffentlichung im Februar 2000
Es gibt viele neue und unabhängige Versuche zur Ausarbeitung eines Standards. Bei diesen Versuchen heben sich besonders die Veröffentlichungen von John Hindmarsh (SQL Server-Datenbankadministrator, MCT, MCSE, MCDBA) hervor. Es empfiehlt sich, diese Artikel zu lesen. John hat ein ausführliches Whitepaper verfasst, in dem er seine Empfehlungen zu verschiedenen Standards in Verbindung mit SQL Server ausführt. Eine ähnlich wichtige Veröffentlichung ist im Buch Transact-SQL Programming (ISBN 1-56592-401-0) im Kapitel "Format and Style" von Andrew Zanevsky zu finden. Andrew hat zum Whitepaper von John beigetragen, wie auch Johns Freund und Kollege Stephen James sowie Tom Moreau und Paul Munkenbeck, die zudem an SQL Server Professional mitgewirkt haben. Im Folgenden finden Sie einige Empfehlungen von John zur Entwicklung von gespeicherten Prozeduren:
Verwenden Sie SQL-92-Standardverknüpfungssyntax.
Verwenden Sie für eine bessere Leistung bevorzugt Verknüpfungen gegenüber Unterabfragen oder geschachtelten Abfragen.
Stellen Sie sicher, dass Variablen und Parameter in Typ und Größe mit den Tabellendatenspalten übereinstimmen.
Vergewissern Sie sich, dass alle Variablen und Parameter verwendet oder andernfalls gelöscht werden.
Temporäre Objekte sollten nach Möglichkeit nur im lokalen Codeabschnitt verwendet werden.
Schränken Sie die Verwendung temporärer Tabellen auf gespeicherte Prozeduren ein.
Überprüfen Sie die Eingabeparameter auf Gültigkeit.
Verwenden Sie bevorzugt INSERT…SELECT gegenüber SELECT…INTO, da andernfalls häufiger Sperren auftreten.
Halten Sie den Arbeitsaufwand auf einem angemessenen Niveau, d. h. erstellen Sie nach Möglichkeit keine umfangreichen Prozesse mit einer langen Ausführungszeit.
Verwenden Sie in keinem Code SELECT *.
Verwenden Sie ein Layout mit Einrückungen, Blöcken, Tabstoppzeichen und Leerstellen. Beachten Sie dazu die Skriptbeispiele.
Verwenden Sie für T-SQL-Anweisungen Großbuchstaben.
Fügen Sie den Prozeduren ausführliche Kommentare hinzu, um die verschiedenen Vorgänge zu verdeutlichen. Verwenden Sie Zeilenkommentare an Stellen, an denen dies zur Verdeutlichung eines Verfahrensschrittes dient.
Verwenden Sie Transaktionsverwaltung, wenn die Prozedur nicht von MTS-Prozessen aufgerufen wird. (Erstellen Sie für MTS-Prozesse eigene Prozeduren.)
Überwachen Sie @@TRANCOUNT, um die Zuständigkeitsebene für Transaktionen festzulegen.
Verwenden Sie GOTO möglichst nur zur Fehlerbehandlung.
Vermeiden Sie geschachtelte Prozeduren.
Vermeiden Sie die implizite Auflösung von Objektnamen. Stellen Sie sicher, dass alle Objekte den Besitzer dbo haben.
Link zu
www.microsoft.com/downloads/details.aspx?displayla%20ng=en&familyid=B352EB1F-D3CA-44EE-893E-9E07339C1F22&displaylang=en (in Englisch)
Weitere Informationen zu SQL Server Professional und Pinnacle Publishing finden Sie auf der Website http://www.pinpub.com/ (in Englisch).
Hinweis: Diese Website wird nicht von der Microsoft Corporation betrieben. Microsoft ist nicht für deren Inhalte verantwortlich.
Dieser Artikel ist bereits in der SQL Server Professional-Ausgabe vom Dezember 2004 erschienen. Copyright 2004, Pinnacle Publishing, Inc., sofern nicht anders angegeben. Alle Rechte vorbehalten. SQL Server Professional ist eine unabhängige Publikation von Pinnacle Publishing, Inc. Der Artikel oder Teile des Artikels dürfen nicht ohne vorherige Zustimmung von Pinnacle Publishing reproduziert werden (ausgenommen kurze Zitate in Artikeln und Rezensionen). Sie erreichen Pinnacle Publishing, Inc. unter der Telefonnummer 1-800-788-1900.