Sichten - EDV

SQLServ2005.book Seite 221 Mittwoch, 2. August 2006 3:24 15
221
K A P I T E L
7
Sichten
Eine Sicht ist einfach eine SELECT-Anweisung, die einen Namen trägt und in Microsoft SQL
Server gespeichert wird. Sichten dienen als virtuelle Tabellen und bieten mehrere Vorteile.
Eine Sicht bietet Entwicklern einen standardisierten Weg, um Abfragen auszuführen. Entwickler werden in die Lage versetzt, bestimmte allgemeine Abfragen einmal als Sicht zu
schreiben und die Sichten anschließend in den Anwendungscode einzuschließen, sodass alle
Anwendungen dieselbe Version einer Abfrage verwenden. Auch kann eine Sicht eine Sicherheitsschicht bilden, da die Benutzer nur auf eine Teilmenge der in den Basistabellen der Sicht
enthaltenen Daten zugreifen können. Außerdem kann eine Sicht eine benutzerfreundlichere,
logischere Ansicht der Daten in einer Datenbank bieten. Zudem kann eine Sicht, für die Indizes erstellt wurden, insbesondere für bestimmte Arten komplexer Abfragen drastische Leistungsverbesserungen bieten. Die meisten Sichten gestatten nur Lesevorgänge für die zugrunde
liegenden Daten. Sie können aber auch aktualisierbare Sichten erstellen, in denen die Benutzer
Daten ändern dürfen. Dieses Kapitel zeigt Ihnen, wie die Leistung und Flexibilität von regulären, aktualisierbaren und indizierten Sichten genutzt werden kann.
Prüfungsziele in diesem Kapitel
„
Sichten
„
Erstellen einer indizierten Sicht
„
Erstellen einer aktualisierbaren Sicht
„
Zuweisen von Berechtigungen zu einer Rolle oder einem Schema für eine Sicht
Die Lektionen in diesem Kapitel
„
Lektion 1: Erstellen von Sichten . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
224
„
Lektion 2: Bearbeiten von Daten mithilfe von Sichten. . . . . . . . . . . . . . . . . . . . . . . .
229
„
Lektion 3: Erstellen von indizierten Sichten . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
232
Solid Quality Learning: Implementieren und Warten von Microsoft SQL Server 2005 - Original Microsoft Training für Examen 70-431. Microsoft Press 2006 (ISBN 978-3-86645-900-7)
SQLServ2005.book Seite 222 Mittwoch, 2. August 2006 3:24 15
222
Kapitel 7
Sichten
Bevor Sie beginnen
Damit Sie die Lektionen in diesem Kapitel bearbeiten können, müssen folgende Voraussetzungen erfüllt sein:
„
SQL Server 2005 muss installiert sein.
„
Eine Kopie der Beispieldatenbank AdventureWorks muss in der Instanz installiert sein.
Aus der Praxis
Michael Hotek
Vor einigen Jahren hatte ich ein zweiwöchiges Projekt bei einem Kunden, der Leistungsprobleme zu beklagen hatte. Als ich begann, einen Blick in die Datenbank zu werfen,
wusste ich, dass ich einer großen Herausforderung gegenüberstand. Aufgeteilt auf fast
2.000 gespeicherte Prozeduren, Funktionen und Trigger gab es Zehntausende von Codezeilen sowie über 350 Tabellen. Was auf den ersten Blick wirklich hervorstach, waren die
mehr als 800 Sichten in der Datenbank.
Eine große Anzahl von Sichten in einer Datenbank ist nicht unbedingt ein Problem. Aber
die Tatsache, dass es mehr als doppelt so viele Sichten wie Tabellen gab, sagte mir, dass
entweder die Tabellen schlecht entworfen waren oder die Sichten nicht richtig genutzt
wurden. Leider traf in diesem Fall beides zu – aber dies ist eine andere Geschichte.
Bei meinen Untersuchungen entdeckte ich Sichten, deren einzige Aufgabe darin bestand,
mithilfe einer einfachen WHERE-Klausel eine Hand voll Spalten aus einer einzigen
Tabelle auszuwählen. Nachdem ich ungefähr die fünfzigste Sicht betrachtet hatte, stellte ich
fest, dass etwas nicht stimmt. Auf die Sichten zurückblickend, die ich bereits betrachtet
hatte, fand ich ein Duplikat. Dann fand ich ein weiteres und ein weiteres und ein weiteres.
In einem Fall fand ich 23 Sichten, die alle dasselbe taten.
Es stellte sich heraus, dass die Entwickler in Eile Anwendungen schrieben und neue
Features bereitstellten. An einem bestimmten Punkt schrieb einer der Datenbankadministratoren vor, dass der gesamte Datenzugriff durch Sichten erfolgen muss. Er dachte fälschlicherweise, dass eine Sicht zu Leistungsverbesserungen führt. Mehrere Jahre später hatte
das Unternehmen Hunderte von Sichten in die Anwendungen eingebettet. Die Suche gestaltete sich so schwierig, dass die Entwickler immer dann, wenn sie etwas benötigten, einfach
neue Sichten erstellten. Eine schlechte Situation wurde dadurch noch schlimmer.
Glücklicherweise griffen die Anwendungen nicht direkt auf Tabellen oder Sichten zu. Der
Datenzugriff erfolgte über gespeicherte Prozeduren. Der erste Schritt in dem Prozess bestand darin, den Code für gespeicherte Prozeduren, Funktionen und Trigger durchzuackern,
um nach Verweisen auf doppelte Sichten zu suchen. Durch das Entfernen aller Duplikate
konnten wir mehr als 400 Sichten löschen.
`
Solid Quality Learning: Implementieren und Warten von Microsoft SQL Server 2005 - Original Microsoft Training für Examen 70-431. Microsoft Press 2006 (ISBN 978-3-86645-900-7)
SQLServ2005.book Seite 223 Mittwoch, 2. August 2006 3:24 15
Kapitel 7
Sichten
223
Dann unternahmen wir den zweiten Schritt und entfernten all das, was niemals eine Sicht
hätte werden dürfen. Als unnötig grenzten wir solche Sichten ab, die durch eine einfache
WHERE-Klausel nur auf eine Tabelle zugriffen, solche die Dinge implementierten, die
nicht in eine Sicht gehörten – zum Beispiel hartcodierte Zustandslisten –, und solche, die
eine einfache Logik enthielten, die jeder Entwickler verstehen sollte.
Als Endergebnis erhielten wir eine Datenbank, die nur 34 Sichten enthielt. Die einzigen Sichten, die übrig blieben, enthielten komplexe Berechnungen oder komplexe Verknüpfungen,
die gekapselt werden mussten, um entweder die Konsistenz sicherzustellen oder um in der
Zukunft einen beträchtlichen Aufwand zum Erstellen der korrekten Abfrage zu vermeiden.
Die Entwickler lernten durch diese Lektion, dass SQL Server zahlreiche Werkzeuge bereitstellt, um eine Aufgabe auszuführen. Nur weil Sie etwas tun können, bedeutet das jedoch
nicht notwendigerweise, dass Sie es tun sollten. Ehe Sie ein Objekt in einer Datenbank
erstellen, müssen Sie verstehen, wie es die Anwendung verbessert, und das Erstellen des
Objekts die beste Methode rechtfertigen können.
Solid Quality Learning: Implementieren und Warten von Microsoft SQL Server 2005 - Original Microsoft Training für Examen 70-431. Microsoft Press 2006 (ISBN 978-3-86645-900-7)
SQLServ2005.book Seite 224 Mittwoch, 2. August 2006 3:24 15
224
Kapitel 7
Sichten
Lektion 1: Erstellen von Sichten
Bestimmte Objekte von SQL Server sind notwendig oder allgemein empfehlenswert. Sie benötigen beispielsweise Datenbanktabellen, um Daten zu speichern, und Sie sollten bestimmte
Indizes für Ihre Tabellen erstellen, um die Leistung zu verbessern. Sichten sollten Sie jedoch
nur dann anlegen, wenn sie einen eindeutigen Vorteil mit sich bringen. Sichten, die keinen
deutlichen Nutzen aufweisen, verschlingen einfach nur Platz. Nehmen Sie an, dass Sie den
Namen eines Kunden zurückgeben müssen, der einen Kreditrahmen von über 10.000 Euro hat.
Eine Sicht würde in diesem Fall keinen Vorteil bieten, da die SELECT-Anweisung zum
Generieren diese Ergebnisses einfach und unkompliziert ist. Wenn Sie jedoch den Namen
eines Kunden mit der Hauptadresse und der jüngsten Zahlung zurückgeben möchten, während
die Ausgabe aller Kunden, die keine Zahlung geleistet haben, beibehalten werden soll, ist eine
Sicht vielleicht nützlich, da dieses Ergebnisses eine Kombination von inneren und äußeren
Verknüpfungen für mindestens fünf verschiedene Tabellen erfordert. In dieser Lektion sehen
Sie, wie eine Sicht über eine oder mehrere Tabellen definiert wird. Auch lernen Sie, warum es
wichtig ist, für die Sicht und alle zugrunde liegenden Tabellen, auf denen sie basiert, passende
Berechtigungen zuzuweisen.
Am Ende dieser Lektion werden Sie in der Lage sein, die folgenden Aufgaben
auszuführen:
„
Erstellen einer Sicht
„
Hinzufügen von Berechtigungen für eine Sicht zu einer Rolle oder einem Schema
Veranschlagte Zeit für die Lektion: 20 Minuten
Erstellen einer Sicht
Sie verwenden den Transact-SQL-Befehl CREATE VIEW, um eine Sicht über eine oder mehrere
Tabellen zu erstellen. Die Syntax für den Befehl lautet wie folgt:
CREATE VIEW [ Schemaname . ] Sichtname [ (Spalte [ ,...n
] ) ]
[ WITH <Sichtattribut> [ ,...n ] ]
AS Select-Anweisung [ ; ]
[ WITH CHECK OPTION ]
<Sichtattribut> ::=
{
[ ENCRYPTION ]
[ SCHEMABINDING ]
[ VIEW_METADATA ] }
Sie beginnen, indem Sie Ihre Sicht benennen. Wie bei allen Objekten muss eine Sicht einen
Namen haben, der den Regeln für Objektbezeichner entspricht.
Die erste WITH-Klausel des Befehls ermöglicht es Ihnen, drei verschiedene Optionen für die
Sicht anzuwenden: ENCRYPTION, SCHEMABINDING und VIEW_METADATA.
Solid Quality Learning: Implementieren und Warten von Microsoft SQL Server 2005 - Original Microsoft Training für Examen 70-431. Microsoft Press 2006 (ISBN 978-3-86645-900-7)
SQLServ2005.book Seite 225 Mittwoch, 2. August 2006 3:24 15
Lektion 1: Erstellen von Sichten
225
ENCRYPTION gibt an, dass SQL Server die Definition der Sicht verschlüsseln sollte, wenn
diese in der Datenbank gespeichert wird. Die Definition einer verschlüsselten Sicht ist für niemanden sichtbar, auch nicht für ein Mitglied der festen Serverrolle sysadmin. Wenn Sie eine
Sicht verschlüsseln, müssen Sie daher sicherstellen, dass Sie irgendwo den ursprünglichen
Quellcode aufbewahren, da Sie die Definition nicht entschlüsseln können.
Wenn Sie die Option SCHEMABINDING angeben, können Sie Tabellen, Sichten oder Funktionen, auf die von der Sicht verwiesen wird, nicht löschen, ohne als Erstes die Sicht zu löschen.
Empfohlene Vorgehensweise: Der Trick mit der Schemabindung
Ein alter Trick, den viele Datenbankadministratoren in einer Produktivumgebung nutzen,
besteht darin, für jede Tabelle eine Sicht zu erstellen, die alle Spalten in der Tabelle auswählt
und die Option SCHEMABINDING verwendet. Diese Sichten werden niemals von einer
Anwendung oder von einem Benutzer verwendet. Ihr einziger Zweck besteht darin, einen
Datenbankadministrator davon abzuhalten, versehentlich eine Tabelle oder eine Spalte innerhalb einer Tabelle zu löschen. Dieser Trick verhindert nicht, eine Tabelle zielgerichtet zu
löschen, da der Datenbankadministrator auch die Sicht und anschließend die Tabelle löschen
kann. Aber ein Objekt absichtlich zu löschen, das nicht gelöscht werden sollte, ist ein Sicherheitsproblem.
Die Option VIEW_METADATA gibt Metadaten über eine Sicht an clientbasierte Datenzugriffsbibliotheken zurück.
Sie verwenden die AS-Klausel des Befehls, um die SELECT-Anweisung anzugeben, die die
Sicht definiert. Die SELECT-Anweisung kann von beliebiger Komplexität sein, solange die
Abfrage gültig ist, und kann auf Tabellen, Sichten, benutzerdefinierte Funktionen und Systemfunktionen verweisen. Die einzigen Einschränkungen bestehen darin, dass die SELECTAnweisung einer Sicht folgende Dinge nicht tun kann:
„
Verwenden der COMPUTE- oder COMPUTE BY-Klausel
„
Verwenden des Schlüsselworts INTO
„
Verwenden der OPTION-Klausel
„
Verweisen auf eine temporäre Tabelle oder eine Tabellenvariable
„
Verwenden der ORDER BY-Klausel, es sei denn, sie gibt auch den TOP-Operator an
Die letzte Option des Befehls, WITH CHECK OPTION, wird verwendet, um eine aktualisierbare Sicht zu erstellen. Lektion 2, „Bearbeiten von Daten mithilfe von Sichten“, behandelt
diese Option.
Nachdem Sie eine Sicht erstellt haben, können Sie diese wie eine Tabelle in einer Datenbank
verwenden. Eine Sicht enthält jedoch keine Daten, sondern ist einfach eine SELECT-Anweisung mit einem Namen. Wenn daher in einer SELECT-Anweisung auf eine Sicht verwiesen
wird, ersetzt der Abfrageoptimierer den Verweis durch die Sichtdefinition, ehe ein Ausführungsplan generiert wird.
Betrachten Sie beispielsweise den folgenden Code:
Solid Quality Learning: Implementieren und Warten von Microsoft SQL Server 2005 - Original Microsoft Training für Examen 70-431. Microsoft Press 2006 (ISBN 978-3-86645-900-7)
SQLServ2005.book Seite 226 Mittwoch, 2. August 2006 3:24 15
226
Kapitel 7
Sichten
CREATE VIEW v_CustomerAddress
AS
SELECT a.CustomerID, a.CustomerName, c.AddressLine1,
c.AddressLine2, c.AddressLine3,
c.City, d.StateProvince, c.PostalCode, e.Country
FROM dbo.Customer a INNER JOIN
dbo.CustomerToCustomerAddress b ON a.CustomerID =
b.CustomerID
INNER JOIN dbo.CustomerAddress c ON
b.CustomerAddressID = c.CustomerAddressID
INNER JOIN dbo.StateProvince d ON
c.StateProvinceID = d.StateProvinceID
INNER JOIN dbo.Country e ON c.CountryID =
e.CountryID;
SELECT a.CustomerName, b.CreditLine FROM
v_CustomerAddress a INNER JOIN dbo.Customer b
ON a.CustomerID = b.CustomerID;
Der Optimierer findet den Verweis auf die Sicht v_CustomerAddress und ersetzt ihn durch die
Sichtdefinition, wobei die gesendete Abfrage ähnlich wie im folgenden Beispiel neu
geschrieben wird:
SELECT a.CustomerName, f.CreditLine
FROM dbo.Customer a INNER JOIN
dbo.CustomerToCustomerAddress b ON a.CustomerID =
b.CustomerID
INNER JOIN dbo.CustomerAddress c ON
b.CustomerAddressID = c.CustomerAddressID
INNER JOIN dbo.StateProvince d ON
c.StateProvinceID = d.StateProvinceID
INNER JOIN dbo.Country e ON c.CountryID =
e.CountryID
INNER JOIN dbo.Customer f ON a.CustomerID =
f.CustomerID;
Grundlegendes zur Besitzverkettung
Da eine Sicht auf andere Objekte verweist, sind Leistungsprobleme möglich. Betrachten Sie
die Objekte und Objektbesitzer, die das Diagramm in Abbildung 7.1 zeigt.
Abbildung 7.1
Definieren der Besitzverkettung
Nehmen wir an, dass BenutzerA dem BenutzerD eine SELECT-Berechtigung für die Sicht
v_CustomerAddress gewährt. Obwohl BenutzerD die Berechtigung hat, eine SELECT-
Solid Quality Learning: Implementieren und Warten von Microsoft SQL Server 2005 - Original Microsoft Training für Examen 70-431. Microsoft Press 2006 (ISBN 978-3-86645-900-7)
SQLServ2005.book Seite 227 Mittwoch, 2. August 2006 3:24 15
Lektion 1: Erstellen von Sichten
227
Anweisung an der Sicht auszuführen, wird er beim Versuch, die Sicht zu benutzen, einen
Fehler erhalten. Dies passiert, weil die Sicht auf der Grundlage der Tabellen Customer und
CustomerAddress definiert ist, die nicht im Besitz von BenutzerA bzw. BenutzerD, sondern
eines anderen Benutzers sind. Wenn der Besitz über eine Kette abhängiger Objekte einen
Fehler aufgrund unzureichender Berechtigungen verursacht, haben Sie eine unterbroche
Besitzkette.
Damit BenutzerD eine SELECT-Anweisung an der Sicht v_CustomerAddress ausführen
kann, muss Folgendes geschehen:
„
BenutzerA gewährt BenutzerD die SELECT-Berechtigung für die Sicht.
„
BenutzerB gewährt BenutzerD die SELECT-Berechtigung für dbo.Customer.
„
BenutzerC gewährt BenutzerD die SELECT-Berechtigung für dbo.CustomerAddress.
Weitere Informationen: Besitzverkettung
Weitere Informationen zur Besitzverkettung finden Sie im Thema „Besitzketten“ der SQL
Server 2005-Onlinedokumentation. Diese Dokumentation wird als Teil von SQL Server 2005
installiert. Aktualisierungen stehen unter der Adresse www.microsoft.com/technet/
prodtechnol/sql/2005/downloads/books.mspx zum Herunterladen zur Verfügung.
Schnelltest
„
Welchen Einschränkungen unterliegt eine SELECT-Anweisung innerhalb einer Sicht?
Antworten
„
COMPUTE- oder COMPUTE BY-Klauseln sind nicht erlaubt. Sie können nicht das
Schlüsselwort INTO oder die OPTION-Klausel verwenden. Es kann nicht auf temporäre Tabellen und Tabellenvariablen verwiesen werden. Es kann keine ORDER BYKlausel angegeben werden, es sei denn, es wird auch der TOP-Operator verwendet.
Übung: Erstellen einer Sicht
In dieser Übung verwenden Sie die Datenbank, die die in Kapitel 3, „Erstellen von Tabellen,
Einschränkungen und benutzerdefinierten Typen“, rstellten Tabellen enthält. Es soll eine Sicht
erstellt werden, um Informationen über Kunden zurückzugeben, die in Kanada leben.
1. Starten Sie SQL Server Management Studio (SSMS), stellen Sie eine Verbindung mit
Ihrer Instanz her, öffnen Sie ein neues Abfragefenster und ändern Sie den Kontext auf die
Datenbank, die die von Ihnen in Kapitel 3 erstellten Tabellen enthält.
2. Erstellen Sie eine Sicht, um Informationen über Kunden zurückzugeben, die in Kanada
leben, indem Sie die folgende Anweisung ausführen:
CREATE VIEW v_CanadaCustomerAddress
AS
SELECT a.CustomerID, a.CustomerName, c.AddressLine1,
c.AddressLine2, c.AddressLine3,
Solid Quality Learning: Implementieren und Warten von Microsoft SQL Server 2005 - Original Microsoft Training für Examen 70-431. Microsoft Press 2006 (ISBN 978-3-86645-900-7)
SQLServ2005.book Seite 228 Mittwoch, 2. August 2006 3:24 15
228
Kapitel 7
Sichten
c.City, d.StateProvince, c.PostalCode, e.Country
FROM dbo.Customer a INNER JOIN
dbo.CustomerToCustomerAddress b ON a.CustomerID =
b.CustomerID
INNER JOIN dbo.CustomerAddress c ON
b.CustomerAddressID = c.CustomerAddressID
INNER JOIN dbo.StateProvince d ON
c.StateProvinceID = d.StateProvinceID
INNER JOIN dbo.Country e ON c.CountryID =
e.CountryID
WHERE e.Country = 'Canada'
AND PrimaryAddressFlag = 1;
3. Konstruieren Sie eine SELECT-Anweisung, um zu überprüfen, dass die Sicht nur Kunden
aus Kanada zurückgibt.
Zusammenfassung der Lektion
„
Eine Sicht ist einfach eine SELECT-Anweisung, die Sie benennen und in SQL Server als
eine Art „virtuelle Tabelle“ speichern. Sie ermöglicht es Ihnen, Benutzern Zugriff auf
lediglich eine Teilmenge der Daten zu gewähren und insbesondere für komplexe Abfragen
die Leistung zu verbessern.
„
Nachdem die Sicht definiert wurde, kann wie bei einer Tabelle in einer SELECT-Anweisung darauf verwiesen werden, obwohl die Sicht keine Daten enthält.
„
Wenn Berechtigungen für eine Sicht erteilt werden, müssen Sie sorgfältig auf die Besitzverkettung achten. Dies ist erforderlich, um sicherzustellen, dass der Benutzer sowohl auf
die Sichten als auch auf die Objekte Zugriff hat, auf deren Grundlage sie erstellt wurde.
Lernzielkontrolle
Die folgenden Fragen dienen dazu, dass Sie sich die in dieser Lektion vermittelten Schlüsselinformationen besser einprägen.
Hinweis: Antworten
Antworten auf diese Fragen und Erklärungen darüber, warum eine bestimmte Antwort falsch
oder richtig ist, finden Sie im Anhang „Antworten“ am Ende dieses Buchs.
1. Welche der folgenden Optionen kann verhindern, dass eine Tabelle gelöscht wird?
a. CHECK OPTION
b. SCHEMABINDING
c. UNION
d. QUOTED_IDENTIFIER
Solid Quality Learning: Implementieren und Warten von Microsoft SQL Server 2005 - Original Microsoft Training für Examen 70-431. Microsoft Press 2006 (ISBN 978-3-86645-900-7)
SQLServ2005.book Seite 229 Mittwoch, 2. August 2006 3:24 15
Lektion 2: Bearbeiten von Daten mithilfe von Sichten
229
Lektion 2: Bearbeiten von Daten mithilfe von Sichten
Wie bereits erwähnt ist eine Sicht lediglich eine benannte SELECT-Anweisung. Folglich ist
eine Sicht ein Pass-Through-Verweis auf eine oder mehrere Basistabellen. Obwohl die meisten Sichten nur Lesezugriff auf die zugrundeliegenden Daten bereitstellen, können Sie
aufgrund der Tatsache, dass eine Sicht eine „Weiterleitung“ ist, darin auch Daten bearbeiten.
Eine Sicht, die es Ihnen ermöglicht, Daten zu bearbeiten, wird aktualisierbare Sicht genannt.
Diese Lektion erläutert, wie Sie INSERT-, UPDATE-, DELETE-, BCP- und BULK INSERTVorgänge in einer Sicht ausführen können.
Am Ende dieser Lektion werden Sie in der Lage sein, die folgenden Aufgaben
auszuführen:
„
Erstellen einer aktualisierbaren Sicht
Veranschlagte Zeit für die Lektion: 20 Minuten
Erstellen von aktualisierbaren Sichten
Obwohl Sie eine Sicht definieren können, die auf mehreren Tabellen basiert, beschränkt SQL
Server Datenänderungen, die Sie durch die Sicht ausführen, auf eine einzelne Tabelle. Außerdem müssen alle Änderungen direkt auf Spalten und nicht auf Ableitungen einer Spalte
verweisen.
Folglich können Sie keine Spalten bearbeiten, die durch eine Aggregatfunktion abgeleitet
sind, wie beispielsweise AVG, COUNT, SUM, MIN oder MAX, oder durch eine Berechnung,
die andere Spalten oder Operationen an einer Spalte einschließt, wie zum Beispiel SUBSTRING. Änderungen können nicht auf Spalten verweisen, die mithilfe von Operatoren wie
UNION, CROSSJOIN und INTERSECT generiert wurden. Außerdem kann die Sichtdefinition keine GROUP BY-, HAVING- oder DISTINCT-Klausel enthalten. Des weiteren können
Sie TOP nicht verwenden, wenn Sie WITH CHECK OPTION angeben.
Empfohlene Vorgehensweise: Datenbearbeitung mit Sichten
Obwohl Sie Sichten verwenden können, um Daten einzufügen, zu aktualisieren und zu
löschen, werden Sichten fast nie für diesen Zweck verwendet. Gespeicherte Prozeduren sind
immer die bessere Wahl, da Sie mit ihrer Hilfe Änderungen einfacher überprüfen können.
Auch sind gespeicherte Prozeduren flexibler.
Sie können in Ihrer Sichtdefinition eine WHERE-Klausel einschließen, um den Bereich der
Zeilen einzuschränken, den die Sicht zurückgibt. Die WHERE-Klausel schränkt jedoch nicht
die Änderungen ein, die Benutzer durch die Sicht vornehmen können. Dazu verwenden Sie bei
der Definition der Sicht die WITH CHECK OPTION-Klausel des Befehls CREATE VIEW.
Lassen Sie uns ein kurzes Beispiel betrachten, um zu sehen, wie die CHECK OPTION-Klausel
funktioniert. Nehmen Sie an, dass Sie eine Sicht definieren, die Kunden mit einem Kredit-
Solid Quality Learning: Implementieren und Warten von Microsoft SQL Server 2005 - Original Microsoft Training für Examen 70-431. Microsoft Press 2006 (ISBN 978-3-86645-900-7)
SQLServ2005.book Seite 230 Mittwoch, 2. August 2006 3:24 15
230
Kapitel 7
Sichten
rahmen von über 1.000 Euro zeigt. Ein Benutzer kann einen neuen Kunden einfügen, der einen
Kreditrahmen von 500 Euro hat, ohne dabei einen Fehler zu verursachen. Diese Vorgehensweise könnte jedoch für Verwirrung sorgen, denn obwohl das Einfügen erfolgreich war, kann
die Sicht die eingefügten Daten nicht anzeigen. Ein Benutzer könnte denken, dass die Daten
verloren gegangen sind. Um die von den Benutzern durchführbaren Änderungen so einzuschränken, dass die Daten immer in der Sicht angezeigt werden, sollten Sie die Sicht mithilfe
der WITH CHECK OPTION-Klausel definieren. Wenn Sie die vorhergehende Sicht mit dieser
CHECK OPTION definieren, führt der Versuch eines Benutzers, einen Kunden mit einem Kreditrahmen von 1.000 Euro oder weniger einzufügen, zur Rückgabe eines Fehlers.
Sie können auch Trigger für eine Sicht erstellen, die nützlich sind, um Vorgänge zum Ändern
von Daten auszuführen. Für Sichten erstellen Sie eine besondere Art von Triggern, INSTEAD
OF-Trigger genannt. Sie funktionieren genauso, wie Sie es erwarten würden: SQL Server führt
nicht den Vorgang an der Sicht aus, sondern den Trigger, um einen alternativen Vorgang
durchzuführen.
Weitere Informationen: INSTEAD OF-Trigger
Weitere Informationen zu Triggern finden Sie in Kapitel 9, „Erstellen von Funktionen, gespeicherten Prozeduren und Triggern“.
Schnelltest
„
Welche Klausel sollten Sie verwenden, um Datenänderungen in der Sicht sichtbar zu
machen?
Antworten
„
Die WITH CHECK OPTION-Klausel gibt eine Einschränkung für INSERT-, UPDATE, DELETE-, BCP- und BULK INSERT-Anweisungen vor. Die Operationen können nur
für Zeilen durchgeführt werden, die mit den Kriterien in der WHERE-Klausel der Sicht
übereinstimmen.
Übung: Erstellen einer aktualisierbaren Sicht
In dieser Übung erstellen Sie eine Sicht, die Sie verwenden können, um für jeden Kunden, der
einen Kreditrahmen von über 1.000 Euro besitzt, Änderungen an der Tabelle Customer vorzunehmen.
1. Starten Sie, falls nötig, SSMS, stellen Sie die Verbindung mit Ihrer Instanz her, öffnen
Sie ein neues Abfragefenster und ändern Sie den Kontext für die Datenbank, die die in
Kapitel 3 erstellten Kundentabellen enthält.
2. Erstellen Sie eine Kundensicht aufgrund der Tabelle Customer, indem Sie die folgende
Anweisung ausführen:
CREATE VIEW dbo.v_Customer
AS
Solid Quality Learning: Implementieren und Warten von Microsoft SQL Server 2005 - Original Microsoft Training für Examen 70-431. Microsoft Press 2006 (ISBN 978-3-86645-900-7)
SQLServ2005.book Seite 231 Mittwoch, 2. August 2006 3:24 15
Lektion 2: Bearbeiten von Daten mithilfe von Sichten
231
SELECT CustomerID, CustomerName, CreditLine,
AvailableCredit
FROM dbo.Customer
WHERE CreditLine > 1000
WITH CHECK OPTION;
3. Führen Sie die folgende INSERT-Anweisung aus und beobachten Sie die Ergebnisse:
INSERT INTO dbo.Customer
(CustomerName, CreditLine)
VALUES('Customer1',5000);
4. Führen sie die folgende INSERT-Anweisung aus und beobachten Sie die Ergebnisse:
INSERT INTO dbo.v_Customer
(CustomerName, CreditLine)
VALUES('Customer2',300);
Zusammenfassung der Lektion
„
Auch wenn gespeicherte Prozeduren die geeignetere Alternative darstellen, um Änderungen an Daten vorzunehmen, können sie Sichten verwenden, um Daten mithilfe der Anweisungen INSERT, UPDATE, DELETE, BCP und BULK INSERT zu bearbeiten.
„
Die Sicht wird als Weiterleitung genutzt, um die Änderungen direkt auf eine einzelne Basistabelle anzuwenden.
„
Um die Änderungen auf die Zeilen zu beschränken, die mit der WHERE-Klausel der Sicht
übereinstimmen, verwenden Sie beim Erstellen der Sicht die WITH CHECK OPTIONKlausel.
Lernzielkontrolle
Die folgenden Fragen dienen dazu, dass Sie sich die in dieser Lektion vermittelten Schlüsselinformationen besser einprägen.
Hinweis: Antworten
Antworten auf diese Fragen und Erklärungen darüber, warum eine bestimmte Antwort falsch
oder richtig ist, finden Sie im Anhang „Antworten“ am Ende dieses Buchs.
1. Welche der folgenden Optionen schränkt Änderungen an Daten ein, um den Auswahlkriterien einer Sicht zu entsprechen?
a. SCHEMABINDING
b. CHECKOPTION
c. ANSI_NULLS
d. QUOTED_IDENTIFIER
Solid Quality Learning: Implementieren und Warten von Microsoft SQL Server 2005 - Original Microsoft Training für Examen 70-431. Microsoft Press 2006 (ISBN 978-3-86645-900-7)
SQLServ2005.book Seite 232 Mittwoch, 2. August 2006 3:24 15
232
Kapitel 7
Sichten
Lektion 3: Erstellen von indizierten Sichten
Wenn eine Abfrage auf eine reguläre Sicht verweist, ersetzt der Abfrageoptimierer, wie Sie
weiter vorn in diesem Kapitel gesehen haben, den Verweis durch die gespeicherte Sichtdefinition, ehe die SELECT-Anweisung ausgeführt wird. Zur Ausführungszeit berechnet SQL
Server jedoch noch Verknüpfungen oder Aggregationen für die Abfrage. Indizierte Sichten
stellen eine Möglichkeit zur Verfügung, das von einer Sicht zurückgegebene Resultset im
Voraus zu berechnen. Die Verwendung indizierter Sichten ist wertvoll, wenn Daten geändert
werden und die für SQL Server durch das ständige Ausführen der Abfrage entstehenden
Kosten die für die Wartung der Ergebnisse der SELECT-Anweisung in einer Sicht erforderlichen Kosten bei weitem übersteigen. Diese Lektion erläutert, wie eine indizierte Sicht erstellt
wird, und stellt einige geeignete Situationen für indizierte Sichten vor.
Am Ende dieser Lektion werden Sie in der Lage sein, die folgenden Aufgaben
auszuführen:
„
Erstellen einer indizierten Sicht
Veranschlagte Zeit für die Lektion: 20 Minuten
Voraussetzungen für eine indizierte Sicht
Theoretisch ist das Erstellen einer indizierten Sicht einfach ein Vorgang, bei dem eine Sicht
und anschließend ein gruppierter Index für die Sicht erstellt werden. In der Praxis ist der
Prozess nicht so einfach.
Um eine indizierte Sicht zu erstellen, müssen ihre Basistabellen viele Kriterien erfüllen. Die
Sicht hat dann zusätzliche Einschränkungen. Für den Index gelten schließlich noch weitere
Bedingungen.
Weitere Informationen: Einschränkungen für das Erstellen indizierter Sichten
Details zu allen Anforderungen und Einschränkungen beim Erstellen indizierter Sichten finden
Sie im Thema „Erstellen von indizierten Sichten“ der SQL Server 2005-Onlinedokumentation.
Ziel all dieser Einschränkungen ist es, sicherzustellen, dass SQL Server eine konsistente
Berechnung ausführen kann. Eine indizierte Sicht, auch materialisierte Sicht genannt, veranlasst SQL Server, die SELECT-Anweisung in der Sichtdefinition auszuführen. SQL Server
erstellt anschließend einen gruppierten Index für die Ergebnisse der Sicht und speichert die
Daten und den Index innerhalb der Datenbank. Wenn Sie Daten in den Basistabellen ändern,
gibt SQL Server diese Änderungen an die indizierte Sicht weiter. Wenn sich das Ergebnis der
Sicht von einer Ausführung zur nächsten oder bei der Verwendung verschiedener Abfrageoptionen ändern könnte, würde die gesamte Datenmenge, die SQL Server berechnet und
gespeichert hat, für ungültig erklärt werden. Daher sind alle Operatoren oder Funktionen, die
unterschiedliche Ergebnisse verursachen können, unzulässig.
Solid Quality Learning: Implementieren und Warten von Microsoft SQL Server 2005 - Original Microsoft Training für Examen 70-431. Microsoft Press 2006 (ISBN 978-3-86645-900-7)
SQLServ2005.book Seite 233 Mittwoch, 2. August 2006 3:24 15
Lektion 3: Erstellen von indizierten Sichten
233
Es folgen einige Beispiele für diese Einschränkungen:
„
Die SELECT-Anweisung kann nicht auf andere Sichten verweisen.
„
Alle Funktionen müssen deterministisch sein. Sie können beispielsweise nicht getdate()
verwenden, da bei jeder Ausführung ein anderes Datum als Ergebnis zurückgegeben wird.
„
AVG, MIN, MAX und STDEV sind nicht zulässig.
Sie verwenden den Transact-SQL-Befehl CREATE INDEX, um für eine Sicht einen gruppierten Index zu erstellen. Details zu diesem Befehl finden Sie in Kapitel 4, „Erstellen von Indizes“.
Sie können auch nicht gruppierte Indizes für eine Sicht erstellen, um dem Abfrageoptimierer
für die Bearbeitung einer Abfrage mehr Optionen zur Verfügung zu stellen. Den CREATE
INDEX-Befehl verwenden Sie auch, um nicht gruppierte Indizes für eine Sicht zu erstellen.
Abfragenersetzung
In Lektion 1, „Erstellen von Sichten“, wurde die Abfragenersetzung erläutert, die eintritt, wenn
eine SELECT-Anweisung auf eine reguläre Sicht verweist. Indizierte Sichten funktionieren
anders, da es sich bei ihnen in Wirklichkeit um eine Tabelle handelt. Abfragen, die auf die indizierte Sicht verweisen, geben daher die Daten direkt aus der Sicht zurück. Der Abfrageoptimierer ersetzt nicht die Sichtdefinition durch die Abfrage.
Obwohl Sie in jeder Version von SQL Server 2005 eine indizierte Sicht erstellen können, enthält die Enterprise Edition ein interessantes Optimierer-Feature. Wenn der Optimierer feststellt, dass für die Bearbeitung einer Abfrage eine indizierte Sicht effizienter ist als eine Basistabelle, schreibt er die Abfrage neu, um stattdessen die indizierte Sicht zu verwenden. Sie
müssen nicht einmal die indizierte Sicht in der Abfrage angeben. Die Abfrage muss nur eine
Tabelle vorgeben, für die Sie eine indizierte Sicht definiert haben. Die Übung in dieser Lektion
verdeutlicht dieses Ersetzungsverhalten, das nur zur Verfügung steht, wenn Sie SQL Server
2005 Enterprise Edition oder die SQL Server 2005 Developer Edition verwenden. Um eine
indizierte Sicht in einer anderen Edition von SQL Server zu verwenden, müssen Sie in der
Abfrage explizit darauf verweisen.
Schnelltest
„
Was ist der Unterschied zwischen einer regulären und einer indizierten Sicht?
Antworten
„
Eine reguläre Sicht ist eine SELECT-Anweisung, auf die durch einen Namen verwiesen
wird und die in SQL Server gespeichert wird. Sie enthält keine Daten. Eine indizierte
Sicht ist eine Sicht, für die ein gruppierter Index erstellt worden ist. Dies veranlasst SQL
Server, die in der Sicht definierten Ergebnisse der Abfrage zu materialisieren und auf
dem Datenträger zu speichern. Eine indizierte Sicht muss sehr strenge Anforderungen
an sich selbst, an die Basistabellen, auf die sie verweist, und an ihren Index erfüllen.
Solid Quality Learning: Implementieren und Warten von Microsoft SQL Server 2005 - Original Microsoft Training für Examen 70-431. Microsoft Press 2006 (ISBN 978-3-86645-900-7)
SQLServ2005.book Seite 234 Mittwoch, 2. August 2006 3:24 15
234
Kapitel 7
Sichten
Übung: Erstellen einer indizierten Sicht
In dieser Übung erstellen Sie eine indizierte Sicht in der Datenbank AdventureWorks.
1. Starten Sie, falls nötig, SSMS, stellen Sie die Verbindung mit Ihrer Instanz her, öffnen Sie
ein neues Abfragefenster und ändern Sie den Kontext der Datenbank AdventureWorks.
2. Erstellen Sie eine indizierte Sicht namens Orders, indem Sie den folgenden Code ausführen:
--Setzen der Optionen, um indizierte Sichten zu
--unterstützen.
SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING, ANSI_WARNINGS,
CONCAT_NULL_YIELDS_NULL, ARITHABORT,
QUOTED_IDENTIFIER, ANSI_NULLS ON;
GO
--Erstellen einer Sicht mit Schemabindung.
IF OBJECT_ID ('Sales.vOrders', 'view') IS NOT NULL
DROP VIEW Sales.vOrders ;
GO
CREATE VIEW Sales.vOrders
WITH SCHEMABINDING
AS
SELECT SUM(UnitPrice*OrderQty*(1.00UnitPriceDiscount)) AS Revenue, OrderDate,
ProductID, COUNT_BIG(*) AS COUNT
FROM Sales.SalesOrderDetail AS od,
Sales.SalesOrderHeader AS o
WHERE od.SalesOrderID = o.SalesOrderID
GROUP BY OrderDate, ProductID;
GO
--Erstellen eines Index für die Sicht.
CREATE UNIQUE CLUSTERED INDEX IDX_V1
ON Sales.vOrders (OrderDate, ProductID);
GO
3. Führen Sie die folgenden Abfragen aus, die die indizierte Sicht verwenden, auch wenn in
den Abfragen nicht explizit auf die Sicht verwiesen wird:
SELECT SUM(UnitPrice*OrderQty*(1.00UnitPriceDiscount)) AS Rev, OrderDate, ProductID
FROM Sales.SalesOrderDetail AS od
JOIN Sales.SalesOrderHeader AS o ON
od.SalesOrderID=o.SalesOrderID
AND ProductID BETWEEN 700 and 800
AND OrderDate >= CONVERT(datetime,'05/01/2002',101)
GROUP BY OrderDate, ProductID
ORDER BY Rev DESC;
SELECT OrderDate, SUM(UnitPrice*OrderQty*(1.00UnitPriceDiscount)) AS Rev
FROM Sales.SalesOrderDetail AS od
JOIN Sales.SalesOrderHeader AS o ON
Solid Quality Learning: Implementieren und Warten von Microsoft SQL Server 2005 - Original Microsoft Training für Examen 70-431. Microsoft Press 2006 (ISBN 978-3-86645-900-7)
SQLServ2005.book Seite 235 Mittwoch, 2. August 2006 3:24 15
Lektion 3: Erstellen von indizierten Sichten
235
od.SalesOrderID=o.SalesOrderID
AND DATEPART(mm,OrderDate)= 3
AND DATEPART(yy,OrderDate) = 2002
GROUP BY OrderDate
ORDER BY OrderDate ASC;
Zusammenfassung der Lektion
„
Sie erstellen eine indizierte Sicht, indem Sie einen gruppierten Index dafür anlegen.
„
Durch das Erstellen eines gruppierten Index für eine Sicht speichert SQL Server das bei der
Abfrage der Sicht erhaltene Resultset auf dem Datenträger. Dies kann die Leistung entscheidend verbessern, insbesondere für Abfragen, die Aggregationen oder Berechnungen
ausführen.
„
Wenn Sie SQL Server 2005 Enterprise Edition verwenden, schreibt der Abfrageoptimierer
automatisch eine Abfrage neu, um eine indizierte Sicht zu verwenden, wenn er feststellt,
dass die indizierte Sicht zum Bearbeiten der Abfrage effizienter ist als die Basistabelle.
Lernzielkontrolle
Die folgenden Fragen dienen dazu, dass Sie sich die in dieser Lektion vermittelten Schlüsselinformationen besser einprägen.
Hinweis: Antworten
Antworten auf diese Fragen und Erklärungen darüber, warum eine bestimmte Antwort falsch
oder richtig ist, finden Sie im Anhang „Antworten“ am Ende dieses Buchs.
1. Welche der folgenden Einstellungen sind erforderlich, um eine indizierte Sicht zu erstellen? (Wählen Sie alle zutreffenden Antworten aus.)
a. QUOTED_IDENTIFIER ON
b. Dreiteilige Namen
c. SCHEMABINDING
d. ANSI_NULLS OFF
Wiederholung des Kapitels
Um die Fähigkeiten, die Sie sich in diesem Kapitel angeeignet haben, weiter zu üben und zu
vertiefen, können Sie Folgendes tun:
„
Die Zusammenfassung des Kapitels durcharbeiten.
„
Die Liste der in diesem Kapitel vorgestellten Schlüsselbegriffe durcharbeiten.
Solid Quality Learning: Implementieren und Warten von Microsoft SQL Server 2005 - Original Microsoft Training für Examen 70-431. Microsoft Press 2006 (ISBN 978-3-86645-900-7)
SQLServ2005.book Seite 236 Mittwoch, 2. August 2006 3:24 15
236
Kapitel 7
Sichten
„
Die Fallstudie durcharbeiten. Sie enthält eine praxisnahe Aufgabenstellung zu den in
diesem Kapitel behandelten Themen.
„
Die vorgeschlagenen Übungen durchführen.
„
Eine Testprüfung ablegen.
Zusammenfassung des Kapitels
„
Sichten sind einfach eine benannte SELECT-Anweisung, die in SQL Server gespeichert
wird.
„
Sie können eine Sicht so wie eine Tabelle verwenden, ohne sich über die Komplexität der
zugrunde liegenden SELECT-Anweisung Gedanken machen zu müssen.
„
Da Sichten von den zugrunde liegenden Basistabellen abhängig sind, um auf Daten zuzugreifen, müssen Sie der Kette von Berechtigungen, die für die Rückgabe der Daten
erforderlich sind, Aufmerksamkeit widmen.
„
Um Sichten sicher zu nutzen und Daten in eine einzelne Basistabelle einzufügen, zu aktualisieren und zu löschen, verwenden Sie die Klausel WITH CHECK OPTION für den
Befehl CREATE VIEW. Damit werden die Änderungen auf die Zeilen beschränkt, die mit
der WHERE-Klausel der Sicht übereinstimmen.
„
Sie können die Leistung verbessern, indem Sie einen gruppierten Index für eine Sicht
erstellen. Indizierte Sichten veranlassen, dass die zurückgegebenen Daten, einschließlich
der Aggregationen und Berechnungen, auf dem Datenträger materialisiert und nicht zur
Ausführungszeit berechnet werden. SQL Server 2005 Enterprise Edition kann eine indizierte Sicht auch dann verwenden, wenn in einer SELECT-Anweisung nicht direkt darauf
verwiesen wird.
Schlüsselbegriffe
Kennen Sie die Bedeutung der folgenden Schlüsselbegriffe? Sie können Ihre Antworten überprüfen, indem Sie im Glossar am Ende des Buches nachschlagen.
„
Unterbrochene Besitzkette
„
Indizierte Sicht
„
Besitzverkettung
„
Aktualisierbare Sicht
„
Sicht
Fallstudien
In den folgenden Fallstudien wenden Sie an, was Sie in diesem Kapitel gelernt haben. Die Antworten auf die Fragen finden Sie im Anhang „Antworten“ am Ende dieses Buches.
Solid Quality Learning: Implementieren und Warten von Microsoft SQL Server 2005 - Original Microsoft Training für Examen 70-431. Microsoft Press 2006 (ISBN 978-3-86645-900-7)
SQLServ2005.book Seite 237 Mittwoch, 2. August 2006 3:24 15
Kapitel 7
Wiederholung des Kapitels
237
Fallstudie: Erstellen von Sichten
Contoso Limited, ein Versicherungsunternehmen mit Sitz in Bothell, Washington, verarbeitet
Versicherungspolicen und Schadensfälle von Privatkunden. Die Entwicklungsgruppe hat ausgewählte Codeteile innerhalb der Anwendungen untersucht, die dieselbe Funktion ausführen,
aber unterschiedliche Ergebnisse zurückgeben. Außerdem hat die Gruppe mehrere komplexe
Abfragen identifiziert, die aufgrund der großen Anzahl von Tabellen, die sie miteinander verknüpfen, eine schlechte Leistung bringen.
Um die Probleme zu beheben, muss das Entwicklungsteam die Abfragen standardisieren und
die Leistung der Schlüsselabfragen verbessern. Wie sollte die Gruppe diese Probleme lösen?
Empfohlene Übungen
Damit Sie die in diesem Kapitel vorgestellten Prüfungsziele meistern können, sollten Sie die
folgenden Übungsaufgaben durchführen.
Erstellen einer Sicht
Nehmen Sie mehrere Ihrer komplexeren Abfragen und wandeln Sie sie in Sichten um. Ersetzen Sie diese neuen Sichten wieder durch Ihren Code.
Erstellen einer indizierten Sicht
Nehmen Sie eine der in Übung 1 erstellten Sichten und wandeln Sie sie in eine indizierte Sicht
um. Vergleichen Sie die Leistung der indizierten Sicht mit der Leistung der zugrunde liegenden SELECT-Anweisung.
Ablegen einer Testprüfung
Die Prüfungsfragen auf der Begleit-CD bieten Ihnen viele Möglichkeiten. Sie können z.B.
überprüfen, ob Sie mit dem Inhalt dieses Kapitels vertraut sind, sich aber auch am gesamten
Inhalt der Zertifizierungsprüfung 70-431 versuchen. Dabei haben Sie die Möglichkeit, den
Test so einzurichten, dass die Zertifizierungsprüfung genau nachgeahmt wird. Sie können aber
auch in den Lernmodus schalten, bei dem Sie die nach der Beantwortung einer Frage die richtigen Antworten und die Erklärungen sehen.
Weitere Informationen: Prüfungsfragen
Einzelheiten über alle verfügbaren Optionen der Prüfungsfragen finden Sie im Abschnitt
„Verwenden der Prüfungsfragen“ in der Einleitung zu diesem Buch.
Solid Quality Learning: Implementieren und Warten von Microsoft SQL Server 2005 - Original Microsoft Training für Examen 70-431. Microsoft Press 2006 (ISBN 978-3-86645-900-7)