Erstellen von Clustered- und Non-Clustered-Indizes in SQL Server

In einem SQL Server gibt es zwei Arten von Indizes. Clustered- und Non-Clustered-Indizes. Sowohl Clustered-Indizes als auch Nicht-Clustered-Indizes haben dieselbe physische Struktur. Darüber hinaus werden beide in SQL Server als B-Tree-Struktur gespeichert.

Clustered-Index:

Eine Clusterliste ist ein bestimmter Indextyp, der die physische Speicherung von Datensätzen in der Tabelle neu ordnet. In SQL Server werden Indizes verwendet, um Datenbankvorgänge zu beschleunigen, was zu einer hohen Leistung führt. Die Tabelle kann daher nur einen Clustered-Index haben, was normalerweise für den Primärschlüssel erfolgt. Die Blattknoten eines Clustered-Index enthalten "Datenseiten". Eine Tabelle kann nur einen Clustered-Index besitzen.

Lassen Sie uns einen Clustered-Index erstellen, um ein besseres Verständnis zu erhalten. Zunächst müssen wir eine Datenbank erstellen.

Datenbankerstellung

Um eine Datenbank zu erstellen. Klicken Sie mit der rechten Maustaste auf "Datenbanken" im Objekt-Explorer und wählen Sie "Neue Datenbank" Möglichkeit. Geben Sie den Namen der Datenbank ein und klicken Sie auf OK. Die Datenbank wurde wie in der folgenden Abbildung gezeigt erstellt.

Jetzt erstellen wir eine Tabelle mit dem Namen "Mitarbeiter" mit dem Primärschlüssel mithilfe der Entwurfsansicht. In der Abbildung unten sehen wir, dass wir hauptsächlich dem Feld mit dem Namen "ID" zugewiesen haben und keinen Index für die Tabelle erstellt haben.

Sie können auch eine Tabelle erstellen, indem Sie den folgenden Code ausführen.

USE [test] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo]. [Mitarbeiter] ([ID] [int] IDENTITY (1,1) NICHT NULL, [Dep_ID] [int] NULL, [Name] [ varchar] (200) NULL, [E-Mail] [varchar] (250) NULL, [Stadt] [varchar] (250) NULL, [Adresse] [varchar] (500) NULL, CONSTRAINT [Primary_Key_ID] PRIMARY KEY CLUSTERED ([ID ] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY] GO

Die Ausgabe wird wie folgt sein.

Der obige Code hat eine Tabelle mit dem Namen erstellt "Mitarbeiter" mit einem ID-Feld eine eindeutige Kennung als Primärschlüssel. In dieser Tabelle wird nun aufgrund von Primärschlüsseleinschränkungen automatisch ein Clustered-Index für die Spalten-ID erstellt. Wenn Sie alle Indizes einer Tabelle anzeigen möchten, führen Sie die gespeicherte Prozedur aus "Sp_helpindex". Führen Sie den folgenden Code aus, um alle Indizes für eine Tabelle mit dem Namen anzuzeigen "Mitarbeiter". Diese Speicherprozedur verwendet einen Tabellennamen als Eingabeparameter.

USE test EXECUTE sp_helpindex Mitarbeiter

Die Ausgabe wird wie folgt sein.

Eine andere Möglichkeit zum Anzeigen von Tabellenindizes ist das Wechseln zu "Tabellen" im Objekt-Explorer. Wählen Sie die Tabelle aus und geben Sie sie aus. Im Indexordner sehen Sie alle Indizes, die für diese bestimmte Tabelle relevant sind (siehe Abbildung unten).

Da dies der Clustered-Index ist, ist die logische und physische Reihenfolge des Index identisch. Das heißt, wenn ein Datensatz eine ID von 3 hat, wird er in der dritten Zeile der Tabelle gespeichert. Wenn der fünfte Datensatz eine ID von 6 hat, wird er in ähnlicher Weise im 5 . gespeichert^th Lage des Tisches. Um die Reihenfolge der Datensätze zu verstehen, müssen Sie das folgende Skript ausführen.

USE [test] GO SET IDENTITY_INSERT [dbo]. [Mitarbeiter] ON INSERT [dbo]. [Mitarbeiter] ([ID], [Dep_ID], [Name], [E-Mail], [Stadt], [Adresse]) VALUES ( 8, 6, N'Humbaerto Acevedo ', N'[email protected]', N'SAINT PAUL ', N'895 E 7. St. Paul Mn 551063852') INSERT [dbo]. [Mitarbeiter] ([ID ], [Dep_ID], [Name], [E-Mail], [Stadt], [Adresse]) WERTE (9, 6, N'Humbaerto Acevedo ', N'[email protected]', N'SAINT PAUL ' , N'895 E 7th St Saint Paul Mn 551063852 ') INSERT [dbo]. [Mitarbeiter] ([ID], [Dep_ID], [Name], [E-Mail], [Stadt], [Adresse]) WERTE (10, 7, N'Pilar Ackaerman ', N'[email protected]', N'ATLANTA ', N'5813 Eastern Ave Hyattsville Md 207822201') INSERT [dbo]. [Mitarbeiter] ([ID], [Dep_ID] , [Name], [E-Mail], [Stadt], [Adresse]) WERTE (11, 1, N'Aaaronboy Gutierrez ', N'[email protected]', N'HILLSBORO ', N'5840 Ne Cornell Rd Hillsboro oder 97124 ') INSERT [dbo]. [Mitarbeiter] ([ID], [Dep_ID], [Name], [E-Mail], [Stadt], [Adresse]) WERTE (12, 2, N'Aabdi Maghsoudi' , N'[email protected] ', N'BRENTWOOD ', N'987400 Nebraska Medical Center Omaha Ne 681987400') INSERT [dbo]. [Mitarbeiter] ([ID], [Dep_ID], [Name], [E-Mail], [Stadt], [Adresse]) WERTE (13, 3, N'Aabharana, Sahni ', N'[email protected]', N'HYATTSVILLE ', N'2 Barlo Circle Suite A Dillsburg Pa 170191') INSERT [dbo]. [Mitarbeiter] ([ ID], [Dep_ID], [Name], [E-Mail], [Stadt], [Adresse]) WERTE (14, 3, N'Aabharana, Sahni ', N'[email protected]', N'HYATTSVILLE ', N'2 Barlo Circle Suite A Dillsburg Pa 170191') EINFÜGEN [dbo]. [Mitarbeiter] ([ID], [Dep_ID], [Name], [E-Mail], [Stadt], [Adresse]) WERTE (1 , 1, N'Aaaronboy Gutierrez ', N'[email protected]', N'HILLSBORO ', N'5840 Ne Cornell Rd Hillsboro oder 97124') INSERT [dbo]. [Mitarbeiter] ([ID], [ Dep_ID], [Name], [E-Mail], [Stadt], [Adresse]) WERTE (2, 2, N'Aabdi Maghsoudi ', N'[email protected]', N'BRENTWOOD ', N'987400 Nebraska Medical Center Omaha Ne 681987400') EINFÜGEN [dbo].[Mitarbeiter] ([ID], [Dep_ID], [Name], [Email], [Stadt], [Adresse]) WERTE (3, 3, N'Aabharana, Sahni ' , N'[email protected]', N'HYATTSVILLE', N'2 Barlo Circle Suite A Dillsburg Pa 170191') EINFÜGEN [dbo].[Mitarbeiter] ([ID], [Dep_ID], [Name], [E-Mail], [Stadt], [Adresse]) WERTE (4, 3, N'Aabharana, Sahni ', N'[email protected]', N'HYATTSVILLE ', N'2 Barlo Circle Suite A Dillsburg Pa 170191 ') INSERT [dbo]. [Mitarbeiter] ([ID], [Dep_ID], [Name], [E-Mail], [Stadt], [Adresse]) WERTE (5, 4, N'Aabish Mughal', N ' [email protected] ', N'OMAHA', N'2975 Crouse Lane Burlington Nc 272150000 ') INSERT [dbo]. [Mitarbeiter] ([ID], [Dep_ID], [Name], [E-Mail], [Stadt] , [Adresse]) WERTE (6, 5, N'Aabram Howell', N'[email protected]', N'DILLSBURG', N'868 York Ave Atlanta Ga 303102750') EINFÜGEN [dbo].[Mitarbeiter ] ([ID], [Dep_ID], [Name], [E-Mail], [Stadt], [Adresse]) WERTE (7, 5, N'Aabram Howell ', N'[email protected]', N. 'DILLSBURG', N'868 York Ave Atlanta Ga 303102750 ') INSERT [dbo]. [Mitarbeiter] ([ID], [Dep_ID], [Name], [E-Mail], [Stadt], [Adresse]) WERTE (15 , 4, N'Aabish Mughal ', N'abish_mughal @ gmail.com ', N'OMAHA', N'2975 Crouse Lane Burlington Nc 272150000 ') INSERT [dbo]. [Mitarbeiter] ([ID], [Dep_ID], [Name], [E-Mail], [Stadt], [ Adresse]) WERTE (16, 5, N'Aabram Howell ', N'[email protected]', N'DILLSBURG ', N'868 York Ave Atlanta Ga 303102750') INSERT [dbo]. [Mitarbeiter] ( [ID], [Dep_ID], [Name], [E-Mail], [Stadt], [Adresse]) WERTE (17, 5, N'Aabram Howell ', N'[email protected]', N'DILLSBURG ', N'868 York Ave Atlanta Ga 303102750') INSERT [dbo]. [Mitarbeiter] ([ID], [Dep_ID], [Name], [E-Mail], [Stadt], [Adresse]) WERTE (18, 6 , N'Humbaerto Acevedo ', N'[email protected]', N'SAINT PAUL ', N'895 E 7. St. Paul Mn 551063852') INSERT [dbo]. [Mitarbeiter] ([ID], [ Dep_ID], [Name], [E-Mail], [Stadt], [Adresse]) WERTE (19, 6, N'Humbaerto Acevedo ', N'[email protected]', N'SAINT PAUL ', N' 895 E 7th St Saint Paul Mn 551063852 ') INSERT [dbo]. [Mitarbeiter] ([ID], [Dep_ID], [Name], [E-Mail], [Stadt], [Adresse]) WERTE (20, 7, N. 'Pilar Ackaerman', N'[email protected] ', N'ATLANTA ', N'5813 Eastern Ave Hyattsville Md 207822201') SET IDENTITY_INSERT [dbo]. [Mitarbeiter] AUS

Obwohl Datensätze in einer zufälligen Reihenfolge von Werten in der Spalte „Id“ gespeichert sind. Aber aufgrund des gruppierten Indexes in der ID-Spalte. Datensätze werden physisch in aufsteigender Reihenfolge der Werte in der ID-Spalte gespeichert. Um dies zu überprüfen, müssen wir den folgenden Code ausführen.

Wählen Sie * aus test.dbo.Employee

Die Ausgabe wird wie folgt sein.

Wir können in der obigen Abbildung sehen, dass Datensätze in aufsteigender Reihenfolge der Werte in der ID-Spalte abgerufen wurden.

Angepasster Clustered-Index

Sie können auch einen benutzerdefinierten Clustered-Index erstellen. Da wir nur einen Clustered-Index erstellen können, müssen wir den vorherigen löschen. Führen Sie den folgenden Code aus, um den Index zu löschen.

USE [test] GO ALTER TABLE [dbo]. [Mitarbeiter] DROP CONSTRAINT [Primary_Key_ID] WITH (ONLINE = OFF) GO

Die Ausgabe wird wie folgt sein.

Um nun den Index zu erstellen, führen Sie den folgenden Code in einem Abfragefenster aus. Dieser Index wurde für mehr als eine Spalte erstellt und wird daher als zusammengesetzter Index bezeichnet.

USE [test] GO CREATE CLUSTERED INDEX [ClusteredIndex-20191128-173307] ON [dbo]. [Mitarbeiter] ([ID] ASC, [Dep_ID] ASC) MIT (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO

Die Ausgabe wird wie folgt sein

Wir haben einen benutzerdefinierten Clustered-Index für ID und Dep_ID erstellt. Dadurch werden die Zeilen nach ID und dann nach Dep_Id sortiert. Um dies anzuzeigen, führen Sie den folgenden Code aus. Das Ergebnis ist aufsteigend nach ID und dann nach Dep_id.

SELECT [ID], [Dep_ID], [Name], [E-Mail], [Stadt], [Adresse] FROM [Test]. [Dbo]. [Mitarbeiter]

Die Ausgabe wird wie folgt sein.

Nicht gruppierter Index:

Ein nicht gruppierter Index ist ein bestimmter Indextyp, bei dem die logische Reihenfolge des Index nicht mit der auf der Festplatte gespeicherten physischen Reihenfolge der Zeilen übereinstimmt. Der Blattknoten des nicht gruppierten Index enthält keine Datenseiten, sondern Informationen zu Indexzeilen. Eine Tabelle kann bis zu 249 Indizes besitzen. Standardmäßig erstellt eine Einschränkung für eindeutige Schlüssel einen nicht gruppierten Index. Beim Lesevorgang sind nicht gruppierte Indizes langsamer als gruppierte Indizes. Ein nicht gruppierter Index enthält eine Kopie der Daten aus den indizierten Spalten, die in der richtigen Reihenfolge gehalten werden, sowie Verweise auf die tatsächlichen Datenzeilen. Zeiger auf die Clusterliste, falls vorhanden. Daher empfiehlt es sich, nur die Spalten auszuwählen, die im Index verwendet werden, anstatt * zu verwenden. Auf diese Weise können Daten direkt aus dem doppelten Index abgerufen werden. Ein ansonsten gruppierter Index wird auch verwendet, um verbleibende Spalten auszuwählen, wenn er erstellt wird.

Die Syntax zum Erstellen eines nicht gruppierten Index ähnelt dem gruppierten Index. Allerdings das Schlüsselwort "NICHT CLUSTERED" wird anstelle von verwendet "CLUSTERED" im Fall des nicht gruppierten Index. Führen Sie das folgende Skript aus, um einen nicht gruppierten Index zu erstellen.

USE [test] GO SET ANSI_PADDING ON GO CREATE NONCLUSTERED INDEX [NonClusteredIndex-20191129-104230] ON [dbo]. [Mitarbeiter] ([Name] ASC) MIT (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, OFF = , ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO

Die Ausgabe wird wie folgt sein.

Die Tabellendatensätze werden nach einem Clustered-Index sortiert, wenn er erstellt wurde. Dieser neue nicht gruppierte Index sortiert die Tabelle gemäß ihrer Definition und wird in einer separaten physischen Adresse gespeichert. Das obige Skript erstellt den Index für die Spalte „NAME“ der Tabelle „Mitarbeiter“. Dieser Index sortiert die Tabelle in aufsteigender Reihenfolge der Spalte "Name". Die Tabellendaten und der Index werden, wie bereits erwähnt, an verschiedenen Orten gespeichert. Führen Sie nun das folgende Skript aus, um die Auswirkungen eines neuen nicht gruppierten Index anzuzeigen.

Wählen Sie Name von Mitarbeiter

Die Ausgabe wird wie folgt sein.

In der obigen Abbildung sehen wir, dass die Spalte Name der Tabelle Employee in aufsteigender Reihenfolge der Namensspalte angezeigt wurde, obwohl wir die Klausel „Order by ASC“ mit der select-Klausel nicht erwähnt haben. Dies liegt an dem nicht gruppierten Index für die Spalte "Name", die in der Employee-Tabelle erstellt wurde. Wenn nun eine Abfrage geschrieben wird, um den Namen, die E-Mail-Adresse, den Ort und die Adresse der jeweiligen Person abzurufen. Die Datenbank sucht zuerst nach diesem bestimmten Namen im Index und ruft dann relevante Daten ab, wodurch die Abrufzeit für Abfragen verkürzt wird, insbesondere wenn die Datenmenge sehr groß ist.

Wählen Sie Name, E-Mail, Stadt, Adresse des Mitarbeiters aus, wobei Name = 'Aaaronboy Gutierrez'

Fazit

Aus der obigen Diskussion haben wir erfahren, dass der Clustered-Index nur einer sein kann, während der Nicht-Clustered-Index viele sein kann. Der Clustered-Index ist schneller als der Nicht-Clustered-Index. Der Clustered-Index belegt keinen zusätzlichen Speicherplatz, während der Nicht-Clustered-Index zusätzlichen Speicher benötigt, um sie zu speichern. Wenn wir eine Primärschlüsseleinschränkung auf die Tabelle anwenden, wird automatisch ein Clustered-Index erstellt. Wenn wir eine eindeutige Schlüsselbeschränkung auf eine Spalte anwenden, wird automatisch ein nicht gruppierter Index darauf erstellt. Nicht gruppierter Index ist schneller als gruppierter Index für Einfüge- und Aktualisierungsvorgänge. Eine Tabelle darf keinen nicht gruppierten Index haben.