Dies ist der vierte Artikel in einer Reihe von lernen Sie die CREATE VIEW SQL-Anweisung. Bisher haben wir mit T-SQL sehr viel erstellt und geändert. In diesem letzten Teil möchte ich einen großen Blick auf die Arbeit mit indizierten Ansichten werfen.
Wie immer, um mit der Serie zu folgen, wird dringend empfohlen, zuerst die vorherigen Teile und dann diesen zu lesen., Dies liegt in erster Linie daran, dass wir unsere eigene Beispieldatenbank und Objekte darin von Grund auf neu erstellt haben, die wir auch in diesem Teil verwenden werden, aber auch, weil es viel einfacher ist, das Gesamtbild zu sehen.
Hier sind die vorherigen drei Teile der SQL-Reihe „ANSICHT ERSTELLEN“:
- Erstellen von Ansichten in SQL Server
- Ändern von Ansichten in SQL Server
- Einfügen von Daten über Ansichten in SQL Server
Lesen Sie diese, bevor Sie mit dieser fortfahren.
Einführung
Als erstes erstellen wir eine indizierte Ansicht., Wir werden natürlich die SQL-Anweisung CREATE VIEW dafür verwenden, wie wir es oft in der Serie getan haben. Wie der Titel schon sagt, besteht die allgemeine Idee darin, zu sehen, wie mit indizierten Ansichten gearbeitet wird, welche Anforderungen an das Hinzufügen eines Index zu einer Ansicht gestellt werden und wie dies programmgesteuert durchgeführt wird. Um die Vorteile indizierter Ansichten zu erläutern, werden wir uns außerdem die Ausführungspläne in SQL Server ansehen. Sie sind ein großartiges Werkzeug für DBAs und Entwickler, wenn es darum geht, einen Engpass in der langsam laufenden Abfrage zu finden und zu beheben.,
Erstellen wir ohne weiteres eine Ansicht mit der SQL-Anweisung CREATE VIEW von unten und sehen, was sie tut:
|
1
2
div> 3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
BENUTZE SQLShackDB;
GEHE
ERSTELLE VIEW dbo.,vEmployeeSalesOrders
MIT SCHEMABINDING
ALS
Mitarbeiter AUSWÄHLEN.EmployeeID
Produkte.ProductID,
SUMME (Preis * Menge) ALS SaleTotal,
SaleDate
VON dbo.Mitarbeiter
TRETEN dbo BEI.Umsatz AUF Mitarbeiter.EmployeeID = Verkauf.EmployeeID
JOIN dbo.Produkte im Verkauf.ProductID = – Produkte.ProductID
GRUPPE NACH Mitarbeitern.EmployeeID
Produkte.ProductID,
Umsatz.,SaleDate
GO
|
Beachten Sie, dass diese Ansicht eine option WITH SCHEMABINDING auf Sie. Der Grund, warum diese Option aktiviert ist, liegt darin, dass sie beim Erstellen von Indizes für Ansichten tatsächlich physisch in der Datenbank gespeichert werden. Mit anderen Worten, alles, worauf sich diese Ansicht stützt, was die Tabellen betrifft, kann sich die Struktur nicht von dem ändern, worauf wir verweisen.
Daher muss es an die zugrunde liegenden Tabellen gebunden sein, damit wir sie nicht auf eine Weise ändern können, die sich auf die Ansichtsdefinition auswirkt., Wenn wir versuchen, sie in irgendeiner Weise zu ändern, gibt SQL Server einen Fehler aus, der besagt, dass diese Ansicht von etwas abhängt. Betrachten Sie es also als eine schwierige Anforderung zum Erstellen eines Index in einer Ansicht.
Sobald der Befehl erfolgreich ausgeführt wurde, sollten Sie die Ansicht vEmployeeSalesOrders im Ordner Ansichten im Objekt-Explorer wie unten gezeigt anzeigen:
Die Definition der Ansicht ist eine etwas komplexere Abfrage. Wir haben ein Aggregat in der SELECT Anweisung gefolgt von der GROUP BY Klausel., Denken Sie daran, wenn wir ein Aggregat in der Abfrage haben, addiert es die Zahlen zusammen, also müssen wir die GROUP BY Klausel haben.
Wenn es in einer Abfrage eine GROUP BY gibt, müssen wir grundsätzlich nach allem gruppieren, was sich in der Auswahlliste befindet, mit Ausnahme des Aggregats.,
Jetzt habe ich die Ansicht bereits erstellt, aber denken Sie daran, dass es immer eine gute Idee ist, die Definition der Ansicht zu testen, indem Sie nur den AUSGEWÄHLTEN Teil der SQL-Anweisung CREATE VIEW ausführen, um zu sehen, was sie zurückgibt:
So können Sie überprüfen, ob die schemagebundene Option aktiviert oder deaktiviert ist., Gehen Sie zum Objekt-Explorer, erweitern Sie Ansichten, klicken Sie mit der rechten Maustaste auf die Ansicht und wählen Sie Eigenschaften aus:
Unter allen anderen Informationen im Fenster Eigenschaften anzeigen sehen Sie, ob die schemagebundene Option auf der Seite Allgemein auf True oder False gesetzt ist.
Indizierte Ansichten erstellen
Fahren wir fort und erstellen einen Index für unsere Ansicht., Consider the script from below for creating a clustered index on the vEmployeeSalesOrders view:
|
1
2
3
4
5
6
|
USE SQLShackDB;
GO
CREATE UNIQUE CLUSTERED INDEX CAK_vEmployeesSalesOrders
ON dbo.,vEmployeeSalesOrders(EmployeeID, ProductID, SaleDate);
GO
|
Wenn wir in SSMS auf die Schaltfläche Ausführen klicken, gibt SQL Server einen Fehler aus, der besagt, dass der Index nicht erstellt werden kann:
Hier ist die vollständige Fehlermeldung, die in der obigen Aufnahme nicht zu sehen ist:
Kann keinen Index in der Ansicht ‘SQLShackDB erstellen.dbo.vEmployeeSalesOrders‘, da die Auswahlliste keine ordnungsgemäße Verwendung von COUNT_BIG enthält. Erwägen Sie, COUNT_BIG(*) zur Auswahlliste hinzuzufügen.,
In diesem Fall benötigen wir COUNT_BIG, da wir GROUP BY in unserer Ansicht verwenden.
Im Allgemeinen, wenn wir Aggregate wie COUNT, SUM, AVG usw. verwenden. in der Auswahlliste des Index müssen wir auch COUNT_BIG einschließen, um einen Index darauf zu erstellen.
Genau das werden wir tun.,verwenden Sie die SQL-Anweisung CREATE VIEW VIEW und fügen Sie COUNT_BIG mithilfe des folgenden Skripts zur Auswahlliste hinzu:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
div> |
BENUTZE SQLShackDB;
GEHE
ALTER VIEW dbo.,vEmployeeSalesOrders
MIT SCHEMABINDING
ALS
Mitarbeiter AUSWÄHLEN.EmployeeID
Produkte.ProductID,
SUMME (Preis * Menge) ALS SaleTotal,
SaleDate,
COUNT_BIG (*) ALS RecordCount
VON dbo.Mitarbeiter
TRETEN dbo BEI.Umsatz AUF Mitarbeiter.EmployeeID = Verkauf.EmployeeID
JOIN dbo.Produkte im Verkauf.ProductID = – Produkte.ProductID
GRUPPE NACH Mitarbeitern.EmployeeID
Produkte.ProductID,
Umsatz.,SaleDate;
GO
|
Wenn Sie sich fragen, warum dies in diesem Fall geschieht, liegt die Antwort darin, dass SQL Server eine Möglichkeit benötigt, die Anzahl des Datensatzes zu verfolgen, den wir für den Index verwenden, und dies ist auch eine von einer Reihe von Einschränkungen beim Erstellen indizierter Ansichten.
Bisher sieht alles gut aus. Wir haben die Definition unserer Ansicht erfolgreich geändert:
Jetzt können wir wieder einen Index für die Ansicht erstellen, indem wir das zuvor verwendete Skript noch einmal ausführen., Dieses Mal wird der Vorgang reibungslos abgeschlossen. Wenn wir zum Objekt-Explorer gehen und den Indexordner unserer Ansicht erweitern, wird der neu erstellte Index angezeigt:
Wenn wir von hier aus mit der rechten Maustaste auf den Index klicken und Eigenschaften auswählen, sehen Sie auf der Seite Allgemein Ansichtsname, Indexname, Typ, Schlüsselspalten usw.,:
Wenn wir zum Speicher wechseln, werden Sie sehen, dass es eine Dateigruppe hat, weil es physisch in einer Datenbank gespeichert ist:
Wenn wir zur Fragmentierung wechseln, sollte es sagen, dass die Fragmentierung ist null Prozent, da wir nur wenige Datensätze in unseren Tabellen haben:
Auf der Suche nach einer detaillierten, aber unterhaltsamen und leicht zu lesenden Grundierung zur Pflege und Überwachung von SQL-Indizes? Schauen Sie sich SQL Index Wartung.,
Indizes löschen
Bevor wir weiter gehen, wollen wir sehen, wie wir einen Index löschen können. Am einfachsten klicken Sie im Objekt-Explorer mit der rechten Maustaste auf den Index und verwenden die Option Löschen. Falls Sie jedoch mehrere Indizes gleichzeitig löschen müssen, ist die DROP INDEX-Anweisung praktisch. Das werden wir tun, denn schließlich ist dies eine T-SQL-Serie über das Erlernen der SQL-Anweisung CREATE VIEW.,
Use the script from below to drop the CAK_vEmployeesSalesOrders index:
|
1
2
3
4
5
|
USE SQLShackDB;
GO
DROP INDEX CAK_vEmployeesSalesOrders ON dbo.,vEmployeeSalesOrders;
GO
|
Wenn Sie mehrere Indizes löschen müssen, geben Sie einfach alle Namen durch ein Komma getrennt.
Generieren zufälliger Daten
Nachdem wir den Index entfernt haben, generieren wir einige zufällige Daten in unserer Tabelle, damit wir uns den Ausführungsplan ansehen und sehen können, wie SQL Server Daten unter der Haube abruft. Die Analyse des Ausführungsplans zeigt den Unterschied, wie sich die Leistung auswirkt, wenn die Abfrage mit und ohne Index in der Ansicht ausgeführt wird.,
Verwenden Sie das Skript von unten, um 50000 zufällige Datensätze in die Verkaufstabelle einzufügen:
Ich werde Sie nicht detailliert durch das Skript führen, aber es ist im Grunde eine Schleife, die 50000 Mal ausgeführt wird und zufällige Daten in die Verkaufstabelle einfügt., um zu überprüfen, ob die Datensätze erfolgreich eingefügt wurden, führen Sie die folgende SELECT-Anweisung aus, die alle Datensätze aus der Verkaufstabelle zählt:
|
1
2
3
4
|
BENUTZE SQLShackDB;
GEHE
SELECT COUNT(*) FROM Sales
|
Die zurückgegebene Zahl zeigt, dass sich in der Verkaufstabelle 50006 Zeilen befinden., Dies ist die Anzahl der Datensätze, die wir gerade generiert haben + 6, die wir ursprünglich hatten:
Ausführungspläne analysieren
Nachdem wir nun einige Daten in unserer Tabelle haben, können wir die Verwendung eines Index in der Ansicht wirklich demonstrieren. Lassen Sie uns die Ansicht vEmployeeSalesOrders abfragen und sehen, wie SQL Server die Daten abruft., Before executing the SELECT statement from below, make sure to include the Actual Execution Plan as shown below:
|
1
2
3
4
|
USE SQLShackDB;
GO
SELECT * FROM dbo.,vEmployeeSalesOrders;
|
Dieses Skript zurückgegeben 23814 Zeilen, aber was noch wichtiger ist, es erzeugt den Ausführungsplan der Abfrage. Denken Sie daran, dass wir den Index zuvor in unserer Ansicht fallen gelassen haben. Im Moment gibt es also keinen Index für unsere Ansicht. Daher führt SQL Server einige Tabellenscans durch, wie unten gezeigt:
Dies ist so ziemlich das Schlimmste in der Datenbankwelt, insbesondere bei Tabellen mit einer großen Datenmenge., Es ist in Ordnung, Tabellenscans mit einer kleinen Datenmenge durchzuführen, z. B. den Fall mit unseren Mitarbeiter-und Produkttabellen, aber es ist schlecht für die Verkaufstabelle, da sie mehr als 50 Datensätze enthält.
Der einfachste Weg, die Tabellenscans loszuwerden, besteht darin, einen Index darauf zu erstellen, da dies die Dinge dramatisch beschleunigt. Um dieses Problem zu beheben, führen wir das Skript zum Erstellen des eindeutigen gruppierten Index in der Ansicht vEmployeeSalesOrders erneut aus.
Wenn wir nun die SELECT-Anweisung erneut ausführen, gibt es keine Unterschiede, obwohl wir gerade den Index erstellt haben. Warum ist das so?, Da ich die SQL Server Express Edition für diese Serie verwende und nur in Enterprise-und Developer-Editionen von SQL Server der Abfrageoptimierer den Index tatsächlich berücksichtigt.
Keine Sorge, da wir SQL Server tatsächlich zwingen können, beim Generieren von Ausführungsplänen einen Index zu verwenden. Dies geschieht mit der Option NOEXPAND., NOEXPAND gilt nur für indizierte Ansichten:
|
1
|
WÄHLEN SIE * AUS dbo.vEmployeeSalesOrders WITH (NOEXPAND)
|
Einfach so haben wir SQL Server gezwungen, den clustered Index zu verwenden, was im Grunde bedeutet, dass die zugrunde liegenden Tabellen beim Abrufen von Daten nicht verwendet werden., Wie unten zu sehen ist, haben wir einige Fortschritte erzielt, indem wir eine Reihe von Operationen eliminiert haben:
Tatsächlich können wir ausführenboth SELECT Anweisungen gleichzeitig und vergleichen Sie die Ergebnisse, indem Sie sich die Ausführungspläne ansehen:
Würden Sie sich das ansehen? Wenn wir die Abfragekosten der ersten SELECT-Anweisung (ohne Index 95%) mit der zweiten SELECT-Anweisung (ohne Index 5%) vergleichen, würde ich sagen, dass dies ein großer Leistungsgewinn mit einem einzelnen Index ist.,
Fazit
Indizes sind großartig, weil sie die Leistung beschleunigen und mit einem Index in einer Ansicht die Leistung wirklich beschleunigen sollten, da der Index in der Datenbank gespeichert ist. Die Indizierung von Ansichten und Tabellen ist eine der effizientesten Methoden, um die Leistung von Abfragen und Anwendungen, die sie verwenden, zu verbessern.
Ich möchte die Dinge abschließen und diese Reihe des Lernens der SQL-Anweisung CREATE VIEW mit diesem Artikel abschließen. Wir haben so ziemlich alles über das Erstellen und Ändern von Ansichten mit T-SQL abgedeckt., Wir begannen mit der SQL-Anweisung CREATE VIEW, erstellten einige Ansichten, änderten sie, löschten sie und vieles mehr.
Ich hoffe, dass diese Reihe des Lernens der SQL-Anweisung CREATE VIEW für Sie informativ war und ich danke Ihnen, dass Sie sie gelesen haben.,
ANSICHT ERSTELLEN SQL: Einfügen von Daten über Ansichten in SQL Server
ANSICHT ERSTELLEN SQL: Arbeiten mit indizierten Ansichten in SQL Server