Geclusterde en niet-geclusterde indexen maken in SQL Server

In een SQL Server bestaan ​​twee soorten indexen; Geclusterde en niet-geclusterde indexen. Zowel geclusterde indexen als niet-geclusterde indexen hebben dezelfde fysieke structuur. Bovendien worden ze beide als B-Tree-structuur in SQL Server opgeslagen.

Geclusterde index:

Een geclusterde lijst is een bepaald type index dat de fysieke opslag van records in de tabel opnieuw rangschikt. Binnen SQL Server worden indexen gebruikt om databasebewerkingen te versnellen, wat leidt tot hoge prestaties. De tabel kan daarom slechts één geclusterde index hebben, wat meestal wordt gedaan op de primaire sleutel. De bladknooppunten van een geclusterde index bevatten "Datapagina's". Een tabel kan slechts één geclusterde index hebben.

Laten we een geclusterde index maken voor een beter begrip. Allereerst moeten we een database aanmaken.

Database maken

Om een ​​database aan te maken. Klik met de rechtermuisknop op "Databases" in objectverkenner en selecteer "Nieuwe database" keuze. Typ de naam van de database en klik op ok. De database is gemaakt zoals weergegeven in de onderstaande afbeelding.

Nu gaan we een tabel maken met de naam "Werknemer" met de primaire sleutel door de ontwerpweergave te gebruiken. We kunnen in de onderstaande afbeelding zien dat we primair zijn toegewezen aan het bestand met de naam "ID" en dat we geen index op de tafel hebben gemaakt.

U kunt ook een tabel maken door de volgende code uit te voeren.

GEBRUIK [test] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo]. [Employee] ([ID] [int] IDENTITY (1,1) NOT NULL, [Dep_ID] [int] NULL, [Name] [ varchar] (200) NULL, [e-mail] [varchar] (250) NULL, [stad] [varchar] (250) NULL, [adres] [varchar] (500) NULL, BEPERKING [Primary_Key_ID] PRIMAIRE SLEUTEL GECLUSTERD ([ID ] ASC) MET (PAD_INDEX = UIT, STATISTICS_NORECOMPUTE = UIT, IGNORE_DUP_KEY = UIT, ALLOW_ROW_LOCKS = AAN, ALLOW_PAGE_LOCKS = AAN) AAN [PRIMAIR]) AAN [PRIMAIR] GO

De output is als volgt.

De bovenstaande code heeft een tabel gemaakt met de naam "Werknemer" met een ID-veld, een unieke identificatie als primaire sleutel. Nu in deze tabel wordt automatisch een geclusterde index gemaakt op kolom-ID vanwege primaire sleutelbeperkingen. Als u alle indexen op een tabel wilt zien, voert u de opgeslagen procedure uit "sp_helpindex". Voer de volgende code uit om alle indexen in een tabel met de naam "Werknemer". Deze opslagprocedure heeft een tabelnaam als invoerparameter.

GEBRUIK test UITVOEREN sp_helpindex Medewerker

De output is als volgt.

Een andere manier om tabelindexen te bekijken is door naar: "Tafels" in objectverkenner. Selecteer de tafel en gebruik hem. In de map indexen ziet u alle indexen die relevant zijn voor die specifieke tabel, zoals weergegeven in de onderstaande afbeelding.

Omdat dit de geclusterde index is, zullen de logische en fysieke volgorde van de index hetzelfde zijn. Dit betekent dat als een record een id van 3 heeft, deze wordt opgeslagen in de derde rij van de tabel. Evenzo, als het vijfde record een ID van 6 heeft, wordt het opgeslagen in het 5^th locatie van de tafel. Om de volgorde van records te begrijpen, moet u het volgende script uitvoeren.

GEBRUIK [test] GO SET IDENTITY_INSERT [dbo]. [Employee] ON INSERT [dbo]. [Employee] ([ID], [Dep_ID], [Name], [email], [city], [address]) WAARDEN ( 8, 6, N'Humbaerto Acevedo ', N'[email protected]', N'SAINT PAUL ', N'895 E 7th St Saint Paul Mn 551063852') INSERT [dbo]. [Medewerker] ([ID ], [Dep_ID], [Naam], [e-mail], [plaats], [adres]) WAARDEN (9, 6, N'Humbaerto Acevedo', N'[email protected]', N'SAINT PAUL' , N'895 E 7th St Saint Paul Mn 551063852 ') INSERT [dbo]. [Employee] ([ID], [Dep_ID], [Name], [email], [city], [address]) VALUES (10, 7, N'Pilar Ackaerman ', N'[email protected]', N'ATLANTA ', N'5813 Eastern Ave Hyattsville Md 207822201') INSERT [dbo]. [Werknemer] ([ID], [Dep_ID] , [Naam], [e-mail], [stad], [adres]) VALUES (11, 1, N'Aaaronboy Gutierrez ', N'[email protected]', N'HILLSBORO ', N'5840 Ne Cornell Rd Hillsboro Of 97124 ') INSERT [dbo]. [Werknemer] ([ID], [DepartementID], [Naam], [e-mail], [stad], [adres]) WAARDEN (12, 2, N'Aabdi Maghsoudi' , N'[email protected] ', N'BRENTWOOD ', N'987400 Nebraska Medical Center Omaha Ne 681987400') INSERT [dbo]. [Employee] ([ID], [Dep_ID], [Name], [email], [city], [address]) WAARDEN (13, 3, N'Aabharana, Sahni ', N'[email protected]', N'HYATTSVILLE ', N'2 Barlo Circle Suite A Dillsburg Pa 170191') INSERT [dbo]. [Medewerker] ([ ID], [Departement ID], [naam], [e-mail], [stad], [adres]) WAARDEN (14, 3, N'Aabharana, Sahni ', N'[email protected]', N'HYATTSVILLE ', N'2 Barlo Circle Suite A Dillsburg Pa 170191') INSERT [dbo]. [Employee] ([ID], [Dep_ID], [Name], [email], [city], [address]) VALUES (1 , 1, N'Aaaronboy Gutierrez', N'[email protected]', N'HILLSBORO', N'5840 Ne Cornell Rd Hillsboro Of 97124') INSERT [dbo].[Werknemer] ([ID], [ Dep_ID], [naam], [e-mail], [stad], [adres]) WAARDEN (2, 2, N'Aabdi Maghsoudi ', N'[email protected]', N'BRENTWOOD ', N'987400 Nebraska Medical Centre Omaha Ne 681987400 ') INSERT [dbo]. [Medewerker] ([ID], [Dep_ID], [Name], [email], [city], [address]) VALUES (3, 3, N'Aabharana, Sahni ' , N'[email protected] ', N'HYATTSVILLE', N'2 Barlo Circle Suite A Dillsburg Pa 170191 ') INSERT [dbo]. [Werknemer] ([ID], [Dep_ID], [Naam], [email], [stad], [adres]) VALUES (4, 3, N'Aabharana, Sahni ', N'[email protected]', N'HYATTSVILLE ', N'2 Barlo Circle Suite A Dillsburg Pa 170191 ') INSERT [dbo]. [Employee] ([ID], [Dep_ID], [Name], [email], [city], [address]) VALUES (5, 4, N'Aabish Mughal', N ' [email protected]', N'OMAHA', N'2975 Crouse Lane Burlington Nc 272150000') INSERT [dbo].[Werknemer] ([ID], [Dep_ID], [Naam], [e-mail], [plaats] , [adres]) VALUES (6, 5, N'Aabram Howell ', N'[email protected]', N'DILLSBURG ', N'868 York Ave Atlanta Ga 303102750') INSERT [dbo]. [Medewerker ] ([ID], [Dep_ID], [naam], [e-mail], [stad], [adres]) WAARDEN (7, 5, N'Aabram Howell ', N'[email protected]', N 'DILLSBURG', N'868 York Ave Atlanta Ga 303102750 ') INSERT [dbo]. [Employee] ([ID], [Dep_ID], [Name], [email], [city], [address]) VALUES (15 , 4, N'Aabish Mughal ', N'abish_mughal @ gmail.com ', N'OMAHA', N'2975 Crouse Lane Burlington Nc 272150000 ') INSERT [dbo]. [Employee] ([ID], [Dep_ID], [Name], [email], [city], [ adres]) WAARDEN (16, 5, N'Aabram Howell ', N'[email protected]', N'DILLSBURG ', N'868 York Ave Atlanta Ga 303102750') INSERT [dbo]. [Medewerker] ( [ID], [Dep_ID], [naam], [e-mail], [stad], [adres]) VALUES (17, 5, N'Aabram Howell ', N'[email protected]', N'DILLSBURG ', N'868 York Ave Atlanta Ga 303102750') INSERT [dbo]. [Employee] ([ID], [Dep_ID], [Name], [email], [city], [address]) VALUES (18, 6 , N'Humbaerto Acevedo ', N'[email protected]', N'SAINT PAUL ', N'895 E 7th St Saint Paul Mn 551063852') INSERT [dbo]. [Medewerker] ([ID], [ Dep_ID], [naam], [e-mail], [stad], [adres]) WAARDEN (19, 6, N'Humbaerto Acevedo ', N'[email protected]', N'SAINT PAUL ', N' 895 E 7th St Saint Paul Mn 551063852 ') INSERT [dbo]. [Employee] ([ID], [Dep_ID], [Name], [email], [city], [address]) VALUES (20, 7, N 'Pilar Ackaerman', N'[email protected] ', N'ATLANTA ', N'5813 Eastern Ave Hyattsville Md 207822201') SET IDENTITY_INSERT [dbo]. [Employee] OFF

Hoewel records worden opgeslagen in de kolom "Id" in een willekeurige volgorde van waarden. Maar vanwege de geclusterde index op de ID-kolom. Records worden fysiek opgeslagen in oplopende volgorde van waarden in de id-kolom. Om dit te verifiëren, moeten we de volgende code uitvoeren.

Selecteer * uit test.dbo.Employee

De output is als volgt.

We kunnen in de bovenstaande afbeelding zien dat records zijn opgehaald in oplopende volgorde van waarden in de id-kolom.

Aangepaste geclusterde index

U kunt ook een aangepaste geclusterde index maken. Omdat we slechts één geclusterde index kunnen maken, moeten we de vorige verwijderen. Voer de volgende code uit om de index te verwijderen.

GEBRUIK [test] GO ALTER TABLE [dbo]. [Employee] DROP CONSTRAINT [Primary_Key_ID] MET (ONLINE = OFF) GO

De output is als volgt.

Voer nu de volgende code uit in een zoekvenster om de index te maken. Deze index is gemaakt op meer dan één kolom en wordt daarom een ​​samengestelde index genoemd.

USE [test] GO CREATE CLUSTERED INDEX [ClusteredIndex-20191128-173307] AAN [dbo].[Medewerker] ( [ID] ASC, [Dep_ID] ASC )MET (PAD_INDEX = UIT, STATISTICS_NORECOMPUTE = UIT, SORT_IN_DROPDB_EXISTING = UIT, UIT UIT, ONLINE = UIT, ALLOW_ROW_LOCKS = AAN, ALLOW_PAGE_LOCKS = AAN) AAN [PRIMAIR] GO

De output is als volgt

We hebben een aangepaste geclusterde index gemaakt op ID en Dep_ID. Dit zal rijen sorteren op Id en vervolgens op Dep_Id. Om dit te zien, voert u de volgende code uit. Het resultaat is oplopende volgorde van ID en vervolgens door Dep_id.

SELECTEER [ID], [Dep_ID], [Naam], [e-mail], [stad], [adres] VAN [test]. [Dbo]. [Werknemer]

De output is als volgt.

Niet-geclusterde index:

Een niet-geclusterde index is een bepaald indextype waarin de logische volgorde van de index niet overeenkomt met de fysieke volgorde van de rijen die op de schijf is opgeslagen. Het bladknooppunt van de niet-geclusterde index bevat geen gegevenspagina's, maar bevat informatie over indexrijen. Een tabel kan maximaal 249 indexen bevatten. Standaard creëert een unieke sleutelbeperking een niet-geclusterde index. Bij het lezen zijn niet-geclusterde indexen langzamer dan geclusterde indexen. Bij een niet-geclusterde index wordt een kopie van de gegevens uit de geïndexeerde kolommen op volgorde gehouden, samen met verwijzingen naar de feitelijke gegevensrijen; verwijzingen naar de geclusterde lijst, indien aanwezig. Daarom is het een goed idee om alleen die kolommen te selecteren die in de index worden gebruikt in plaats van * te gebruiken. Op deze manier kunnen gegevens rechtstreeks uit de dubbele index worden opgehaald. Een anderszins geclusterde index wordt ook gebruikt om de resterende kolommen te selecteren als deze is gemaakt.

De syntaxis die wordt gebruikt om een ​​niet-geclusterde index te maken, is vergelijkbaar met de geclusterde index. Het trefwoord "NIET-CLUSTERED" wordt gebruikt in plaats van “GECLUSTERD” in het geval van de niet-geclusterde index. Voer het volgende script uit om een ​​niet-geclusterde index te maken.

USE [test] GO SET ANSI_PADDING ON GO CREATE NONCLUSTERED INDEX [NonClusteredIndex-20191129-104230] ON [dbo].[Medewerker] ( [Name] ASC )MET (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, DROP SORT_IN_TEMP SORT. , ONLINE = UIT, ALLOW_ROW_LOCKS = AAN, ALLOW_PAGE_LOCKS = AAN) AAN [PRIMAIR] GO

De output is als volgt.

De tabelrecords worden gesorteerd op een geclusterde index als deze is gemaakt. Deze nieuwe niet-geclusterde index sorteert de tabel volgens de definitie en wordt opgeslagen op een afzonderlijk fysiek adres. Het bovenstaande script maakt de index aan in de kolom "NAAM" van de tabel Werknemer. Deze index sorteert de tabel in oplopende volgorde van kolom "Naam". De tabelgegevens en index worden op verschillende locaties opgeslagen, zoals we eerder zeiden. Voer nu het volgende script uit om de impact van een nieuwe niet-geclusterde index te bekijken.

selecteer Naam van werknemer

De output is als volgt.

We kunnen in de bovenstaande afbeelding zien dat de kolom Naam van de tabel Medewerker in oplopende volgorde van naamkolom is weergegeven, hoewel we de clausule “Order by ASC” niet hebben genoemd met de clausule select. Dit komt door de niet-geclusterde index in de kolom "Naam" die is gemaakt in de tabel Werknemer. Als er nu een vraag is geschreven om de naam, het e-mailadres, de stad en het adres van de specifieke persoon op te halen. De database zoekt eerst naar die specifieke naam in de index en haalt vervolgens relevante gegevens op, waardoor de ophaaltijd voor zoekopdrachten wordt verkort, vooral wanneer de gegevens enorm zijn.

selecteer naam, e-mail, stad, adres van werknemer waar naam = 'Aaaronboy Gutierrez'

Conclusie

Uit de bovenstaande discussie kwamen we te weten dat de geclusterde index er maar één kan zijn, terwijl de niet-geclusterde index er veel kan zijn. De geclusterde index is sneller in vergelijking met de niet-geclusterde index. De geclusterde index verbruikt geen extra opslagruimte, terwijl de niet-geclusterde index extra geheugen nodig heeft om ze op te slaan. Als we een primaire sleutelbeperking toepassen op de tabel, wordt de geclusterde index er automatisch op gemaakt. Als we bovendien een unieke sleutelbeperking toepassen op een kolom, wordt er automatisch een niet-geclusterde index op gemaakt. Niet-geclusterde index is sneller in vergelijking met geclusterde indexen voor invoegen en bijwerken. Een tabel mag geen niet-geclusterde index hebben.