Oprettelse af klyngede og ikke-klyngede indekser i SQL Server

I en SQL Server findes to typer indekser; Klyngede og ikke-klyngede indekser. Både klyngede indekser og ikke-klyngede indekser har samme fysiske struktur. Desuden er begge gemt i SQL Server som en B-Tree-struktur.

Klynget indeks:

En klyngeliste er en bestemt type indeks, der omarrangerer den fysiske lagring af poster i tabellen. Inden for SQL Server bruges indekser til at fremskynde databasefunktioner, hvilket fører til høj ydeevne. Tabellen kan derfor kun have et klynget indeks, hvilket normalt udføres på den primære nøgle. Et klynget indeks indeholder bladknudepunkter “Datasider”. En tabel kan kun have et klynget indeks.

Lad os oprette et grupperet indeks for at få en bedre forståelse. Først og fremmest skal vi oprette en database.

Oprettelse af database

For at oprette en database. Højreklik på “Databaser” i objektudforsker, og vælg “Ny database” mulighed. Skriv navnet på databasen, og klik på ok. Databasen er oprettet som vist i nedenstående figur.

Nu opretter vi en tabel med navnet “Medarbejder” med den primære nøgle ved hjælp af designvisningen. Vi kan se på billedet nedenfor, som vi primært har tildelt det arkiverede navn "ID", og vi har ikke oprettet noget indeks på bordet.

Du kan også oprette en tabel ved at udføre følgende kode.

BRUG [test] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo]. [Medarbejder] ([ID] [int] IDENTITY (1,1) IKKE NULL, [Dep_ID] [int] NULL, [Navn] [ varchar] (200) NULL, [email] [varchar] (250) NULL, [city] [varchar] (250) NULL, [address] [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

Outputtet vil være som følger.

Ovenstående kode har oprettet en tabel med navnet “Medarbejder” med et ID-felt, en unik identifikator som en primær nøgle. Nu i denne tabel oprettes et grupperet indeks automatisk på kolonne-id på grund af primære nøglebegrænsninger. Hvis du vil se alle indekserne på en tabel, skal du køre den lagrede procedure “Sp_helpindex”. Udfør følgende kode for at se alle indekserne på en tabel med navnet “Medarbejder”. Denne butiksprocedure tager et tabelnavn som en inputparameter.

BRUGSTEST UDFØR sp_helpindex Medarbejder

Outputtet vil være som følger.

En anden måde at se tabelindekser er at gå til “Borde” i objektudforsker. Vælg tabellen, og brug den. I indeksmappen kan du se alle de indekser, der er relevante for den specifikke tabel, som vist i figuren nedenfor.

Da dette er det klyngede indeks, vil den logiske og fysiske rækkefølge af indekset være den samme. Dette betyder, at hvis en post har en Id på 3, gemmes den i den tredje række i tabellen. Tilsvarende, hvis den femte post har en id på 6, vil den blive gemt i 5^th placeringen af ​​bordet. For at forstå rækkefølgen af ​​poster skal du udføre følgende script.

BRUG [test] GO SET IDENTITY_INSERT [dbo]. [Medarbejder] PÅ INSERT [dbo]. [Medarbejder] ([ID], [Dep_ID], [Navn], [e-mail], [by], [adresse]) VÆRDIER ( 8, 6, N'Humbaerto Acevedo ', N'[email protected]', N'SAINT PAUL ', N'895 E 7. St Saint Paul Mn 551063852') INSERT [dbo]. [Medarbejder] ([ID ], [Dep_ID], [Navn], [e-mail], [by], [adresse]) VÆRDIER (9, 6, N'Humbaerto Acevedo ', N'[email protected]', N'SAINT PAUL ' , N'895 E 7. St Saint Paul Mn 551063852 ') INSERT [dbo]. [Medarbejder] ([ID], [Dep_ID], [Navn], [e-mail], [by], [adresse]) VÆRDIER (10, 7, N'Pilar Ackaerman ', N'[email protected]', N'ATLANTA ', N'5813 Eastern Ave Hyattsville Md 207822201') INDSÆT [dbo]. [Medarbejder] ([ID], [Dep_ID] , [Navn], [e-mail], [by], [adresse]) VÆRDIER (11, 1, N'Aaaronboy Gutierrez ', N'[email protected]', N'HILLSBORO ', N'5840 Ne Cornell Rd Hillsboro Eller 97124 ') INSERT [dbo]. [Medarbejder] ([ID], [Dep_ID], [Navn], [e-mail], [by], [adresse]) VÆRDIER (12, 2, N'Aabdi Maghsoudi' , N'[email protected] ', N'BRENTWOOD ', N'987400 Nebraska Medical Center Omaha Ne 681987400') INDSÆT [dbo]. [Medarbejder] ([ID], [Dep_ID], [Navn], [e-mail], [by], [adresse]) VÆRDIER (13, 3, N'Aabharana, Sahni ', N'[email protected]', N'HYATTSVILLE ', N'2 Barlo Circle Suite A Dillsburg Pa 170191') INSERT [dbo]. [Medarbejder] ([ ID], [Dep_ID], [Navn], [email], [by], [adresse]) VÆRDIER (14, 3, N'Aabharana, Sahni ', N'[email protected]', N'HYATTSVILLE ', N'2 Barlo Circle Suite A Dillsburg Pa 170191') INSERT [dbo]. [Medarbejder] ([ID], [Dep_ID], [Navn], [e-mail], [by], [adresse]) VÆRDIER (1 , 1, N'Aaaronboy Gutierrez ', N'[email protected]', N'HILLSBORO ', N'5840 Ne Cornell Rd Hillsboro Eller 97124') INDSÆT [dbo]. [Medarbejder] ([ID], [ Dep_ID], [Navn], [email], [by], [adresse]) VÆRDIER (2, 2, N'Aabdi Maghsoudi ', N'[email protected]', N'BRENTWOOD ', N'987400 Nebraska Medical Center Omaha Ne 681987400 ') INSERT [dbo]. [Medarbejder] ([ID], [Dep_ID], [Navn], [e-mail], [by], [adresse]) VÆRDIER (3, 3, N'Aabharana, Sahni '' , N'[email protected] ', N'HYATTSVILLE', N'2 Barlo Circle Suite A Dillsburg Pa 170191 ') INDSÆT [dbo]. [Medarbejder] ([ID], [Dep_ID], [Navn], [e-mail], [by], [adresse]) VÆRDIER (4, 3, N'Aabharana, Sahni ', N'[email protected]', N'HYATTSVILLE ', N'2 Barlo Circle Suite A Dillsburg Pa 170191 ') INSERT [dbo]. [Medarbejder] ([ID], [Dep_ID], [Navn], [e-mail], [by], [adresse]) VÆRDIER (5, 4, N'Aabish Mughal', N ' [email protected] ', N'OMAHA', N'2975 Crouse Lane Burlington Nc 272150000 ') INDSÆT [dbo]. [Medarbejder] ([ID], [Dep_ID], [Navn], [e-mail], [by] , [adresse]) VÆRDIER (6, 5, N'Aabram Howell ', N'[email protected]', N'DILLSBURG ', N'868 York Ave Atlanta Ga 303102750') INDSÆT [dbo]. [Medarbejder ] ([ID], [Dep_ID], [Navn], [e-mail], [by], [adresse]) VÆRDIER (7, 5, N'Aabram Howell ', N'[email protected]', N 'DILLSBURG', N'868 York Ave Atlanta Ga 303102750 ') INDSÆT [dbo]. [Medarbejder] ([ID], [Dep_ID], [Navn], [e-mail], [by], [adresse]) VÆRDIER (15 , 4, N'Aabish Mughal ', N'abish_mughal @ gmail.com ', N'OMAHA', N'2975 Crouse Lane Burlington Nc 272150000 ') INDSÆT [dbo]. [Medarbejder] ([ID], [Dep_ID], [Navn], [e-mail], [by], [ adresse]) VÆRDIER (16, 5, N'Aabram Howell ', N'[email protected]', N'DILLSBURG ', N'868 York Ave Atlanta Ga 303102750') INDSÆT [dbo]. [Medarbejder] ( [ID], [Dep_ID], [Navn], [e-mail], [by], [adresse]) VÆRDIER (17, 5, N'Aabram Howell ', N'[email protected]', N'DILLSBURG ', N'868 York Ave Atlanta Ga 303102750') INSERT [dbo]. [Medarbejder] ([ID], [Dep_ID], [Navn], [e-mail], [by], [adresse]) VÆRDIER (18, 6 , N'Humbaerto Acevedo ', N'[email protected]', N'SAINT PAUL ', N'895 E 7. St. Saint Paul Mn 551063852') INDSÆT [dbo]. [Medarbejder] ([ID], [ Dep_ID], [Navn], [email], [by], [adresse]) VÆRDIER (19, 6, N'Humbaerto Acevedo ', N'[email protected]', N'SAINT PAUL ', N' 895 E 7. St Saint Paul Mn 551063852 ') INSERT [dbo]. [Medarbejder] ([ID], [Dep_ID], [Navn], [e-mail], [by], [adresse]) VÆRDIER (20, 7, N 'Pilar Ackaerman', N'[email protected] ', N'ATLANTA ', N'5813 Eastern Ave Hyattsville Md 207822201') SET IDENTITY_INSERT [dbo]. [Medarbejder] FRA

Selvom poster gemmes i kolonnen "Id" i en tilfældig rækkefølge af værdier. Men på grund af klyngede indeks på id-kolonnen. Records gemmes fysisk i stigende rækkefølge af værdier i id-kolonnen. For at bekræfte dette skal vi udføre følgende kode.

Vælg * fra test.dbo. Medarbejder

Outputtet vil være som følger.

Vi kan se i ovenstående figur er registreringer hentet i stigende rækkefølge af værdier i id-kolonnen.

Tilpasset grupperet indeks

Du kan også oprette et tilpasset grupperet indeks. Da vi kun kan oprette et klynget indeks, skal vi slette den forrige. For at slette indekset skal du udføre følgende kode.

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

Outputtet vil være som følger.

For at oprette indekset skal du udføre følgende kode i et forespørgselsvindue. Dette indeks er oprettet i mere end en kolonne, så det kaldes et sammensat indeks.

BRUG [test] GO CREATE CLUSTERED INDEX [ClusteredIndex-20191128-173307] ON [dbo]. [Medarbejder] ([ID] ASC, [Dep_ID] ASC) MED (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROG = OFF FRA, ONLINE = FRA, ALLOW_ROW_LOCKS = TIL, ALLOW_PAGE_LOCKS = TIL) TIL [PRIMÆR] GO

Outputtet vil være som følger

Vi har oprettet et tilpasset klyngede indeks på ID og Dep_ID. Dette sorterer rækker efter Id og derefter efter Dep_Id. For at se dette udfør følgende kode. Resultatet vil være stigende rækkefølge efter ID og derefter Af Dep_id.

VÆLG [ID], [Dep_ID], [Navn], [e-mail], [by], [adresse] FRA [test]. [Dbo]. [Medarbejder]

Outputtet vil være som følger.

Ikke-grupperet indeks:

Et ikke-grupperet indeks er en bestemt indekstype, hvor indeksets logiske rækkefølge ikke svarer til rækkernes fysiske rækkefølge, der er gemt på disken. Bladknudepunktet for det ikke-grupperede indeks indeholder ikke datasider, men det indeholder oplysninger om indeksrækker. En tabel kan have op til 249 indekser. Som standard opretter en unik nøglebegrænsning et ikke-grupperet indeks. I læseoperationen er ikke-klyngede indekser langsommere end klyngede indekser. Et ikke-grupperet indeks har en kopi af dataene fra de indekserede kolonner, der holdes i rækkefølge sammen med referencer til de faktiske datarækker; peger på den klyngede liste, hvis nogen. Derfor er det en god ide at kun vælge de kolonner, der bruges i indekset i stedet for at bruge *. På denne måde kan data hentes direkte fra duplikatindekset. Et ellers grupperet indeks bruges også til at vælge resterende kolonner, hvis det oprettes.

Syntaksen, der bruges til at oprette et ikke-grupperet indeks, ligner det grupperede indeks. Dog nøgleordet “IKKE KLUSTERET” bruges i stedet for “KLUSTERET” i tilfælde af det ikke-grupperede indeks. Udfør følgende script for at oprette et ikke-grupperet indeks.

BRUG [test] GO SET ANSI_PADDING ON GO CREATE NONCLUSTERED INDEX [NonClusteredIndex-20191129-104230] ON [dbo]. [Medarbejder] ([Navn] ASC) MED (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_INEXTEMP = OFF, SORT_INEXTEMPD = OFF , ONLINE = FRA, ALLOW_ROW_LOCKS = TIL, ALLOW_PAGE_LOCKS = TIL) PÅ [PRIMÆR] GO

Outputtet vil være som følger.

Tabelregistreringerne sorteres efter et klynget indeks, hvis det er oprettet. Dette nye ikke-grupperede indeks sorterer tabellen i henhold til dens definition og gemmes i en separat fysisk adresse. Ovenstående script opretter indekset i kolonnen "NAVN" i medarbejdertabellen. Dette indeks sorterer tabellen i stigende rækkefølge i kolonnen "Navn". Tabeldataene og indekset gemmes forskellige steder, som vi sagde tidligere. Udfør nu følgende script for at se virkningen af ​​et nyt ikke-grupperet indeks.

vælg Navn fra Medarbejder

Outputtet vil være som følger.

Vi kan se i figuren ovenfor, at kolonnen Navn i tabellen Medarbejder er vist i stigende rækkefølge efter navnekolonnen, selvom vi ikke har nævnt "Bestil efter ASC" -klausulen med selektionsklausulen. Dette skyldes det ikke-grupperede indeks i kolonnen “Navn” oprettet i tabellen Medarbejder. Nu hvis der skrives en forespørgsel for at hente navn, e-mail, by og adresse på den specifikke person. Databasen vil først søge efter det specifikke navn inde i indekset og derefter hente relevante data, hvilket vil reducere forespørgslens hentetid, især når data er enorme.

vælg Navn, e-mail, by, adresse fra medarbejder, hvor navn = 'Aaaronboy Gutierrez'

Konklusion

Fra ovenstående diskussion lærte vi, at det klyngede indeks kun kan være et, mens det ikke-klyngede indeks kan være mange. Det klyngede indeks er hurtigere sammenlignet med ikke-klynget indeks. Det klyngede indeks bruger ikke ekstra lagerplads, mens det ikke-klyngede indeks har brug for ekstra hukommelse for at gemme dem. Hvis vi anvender en primær nøglebegrænsning på tabellen, oprettes der automatisk et grupperet indeks på det. Desuden, hvis vi anvender en unik nøglebegrænsning på en hvilken som helst kolonne, oprettes der automatisk et ikke-grupperet indeks på den. Ikke-klynget indeks er hurtigere sammenlignet med klyngede til indsætnings- og opdateringsoperation. En tabel har muligvis ikke noget ikke-grupperet indeks.