Opprette grupperte og ikke-grupperte indekser i SQL Server

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

Klynget indeks:

En klynget liste er en bestemt type indeks som omorganiserer den fysiske lagringen av poster i tabellen. Innen SQL Server brukes indekser for å øke hastigheten på databasedriften, noe som fører til høy ytelse. Tabellen kan derfor bare ha en gruppert indeks, som vanligvis gjøres på primærnøkkelen. En klynget indeks inneholder bladnoder “Datasider”. En tabell kan bare ha én gruppert indeks.

La oss lage en gruppert indeks for å få en bedre forståelse. Først og fremst må vi lage en database.

Databaseoppretting

For å opprette en database. Høyreklikk på “Databaser” i objektutforsker, og velg “Ny database” alternativ. Skriv inn navnet på databasen og klikk på ok. Databasen er opprettet som vist i figuren nedenfor.

Nå skal vi lage en tabell med navnet "Ansatt" med hovednøkkelen ved å bruke designvisningen. Vi kan se på bildet nedenfor, vi har primært tilordnet den arkiverte navnet "ID", og vi har ikke opprettet noen indeks på bordet.

Du kan også opprette en tabell ved å utføre følgende kode.

BRUK [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, [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

Resultatet blir som følger.

Ovennevnte kode har opprettet en tabell som heter "Ansatt" med et ID-felt, en unik identifikator som primærnøkkel. Nå i denne tabellen vil en klynget indeks automatisk bli opprettet på kolonne-ID på grunn av primære nøkkelbegrensninger. Hvis du vil se alle indeksene på et bord, kjører du den lagrede prosedyren “Sp_helpindex”. Utfør følgende kode for å se alle indeksene på en tabell som heter "Ansatt". Denne butikkprosedyren tar et tabellnavn som inndataparameter.

BRUK-test EXECUTE sp_helpindex Ansatt

Resultatet blir som følger.

En annen måte å se tabellindekser på er å gå til “Bord” i objektutforsker. Velg tabellen og bruk den. I indeksmappen kan du se alle indeksene som er relevante for den spesifikke tabellen, som vist i figuren nedenfor.

Siden dette er den klyngede indeksen, vil den logiske og fysiske rekkefølgen på indeksen være den samme. Dette betyr at hvis en post har en Id på 3, vil den bli lagret i tredje rad i tabellen. Tilsvarende, hvis den femte platen har en ID på 6, vil den bli lagret i 5^th plassering av bordet. For å forstå rekkefølgen på poster, må du utføre følgende skript.

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

Selv om poster er lagret i "Id" -kolonnen i en tilfeldig rekkefølge av verdier. Men på grunn av gruppert indeks på id-kolonnen. Postene lagres fysisk i stigende rekkefølge av verdier i id-kolonnen. For å bekrefte dette må vi utføre følgende kode.

Velg * fra test.dbo.medarbeider

Resultatet blir som følger.

Vi kan se i figuren over er poster hentet i stigende rekkefølge av verdier i id-kolonnen.

Tilpasset gruppert indeks

Du kan også opprette en tilpasset gruppert indeks. Da vi bare kan opprette en gruppert indeks, må vi slette den forrige. For å slette indeksen, utfør følgende kode.

BRUK [test] GÅ ALTER-TABELL [dbo]. [Ansatt] DROP KONSTRAINT [Primary_Key_ID] WITH (ONLINE = OFF) GO

Resultatet blir som følger.

For å opprette indeksen, utfør følgende kode i et spørringsvindu. Denne indeksen er opprettet i mer enn en kolonne, så den kalles en sammensatt indeks.

BRUK [test] GO CREATE CLUSTERED INDEX [ClusteredIndex-20191128-173307] ON [dbo]. [Medarbeider] ([ID] ASC, [Dep_ID] ASC) MED (PAD_INDEX = AV, STATISTICS_NORECOMPUTE = AV, SORT_IN_TEMPDB = AV, DROG = AV AV, ONLINE = AV, ALLOW_ROW_LOCKS = PÅ, ALLOW_PAGE_LOCKS = PÅ) PÅ [PRIMÆR] GO

Resultatet blir som følger

Vi har opprettet en tilpasset gruppert indeks på ID og Dep_ID. Dette vil sortere rader etter Id og deretter etter Dep_Id. For å se dette, utfør følgende kode. Resultatet vil være stigende rekkefølge på ID og deretter Av Dep_id.

VELG [ID], [Dep_ID], [Navn], [e-post], [by], [adresse] FRA [test]. [Dbo]. [Ansatt]

Resultatet blir som følger.

Ikke-gruppert indeks:

En ikke-gruppert indeks er en bestemt indekstype der indeksens logiske rekkefølge ikke samsvarer med radenes fysiske rekkefølge lagret på disken. Bladnoden til den ikke-grupperte indeksen inneholder ikke datasider, men inneholder informasjon om indeksrader. En tabell kan ha opptil 249 indekser. Som standard oppretter en unik nøkkelbegrensning en ikke-sammenlagt indeks. I leseoperasjonen er ikke-grupperte indekser langsommere enn grupperte indekser. En ikke-gruppert indeks har en kopi av dataene fra de indekserte kolonnene holdt i orden sammen med referanser til de faktiske dataradene; pekere til den klyngede listen hvis noen. Derfor er det lurt å bare velge de kolonnene som brukes i indeksen i stedet for å bruke *. På denne måten kan data hentes direkte fra duplikatindeksen. En ellers gruppert indeks brukes også til å velge gjenværende kolonner hvis den opprettes.

Syntaksen som brukes til å lage en ikke-gruppert indeks, ligner den grupperte indeksen. Imidlertid nøkkelordet “IKKE KLUSTERT” brukes i stedet for “KLUSTERT” i tilfelle den ikke-grupperte indeksen. Utfør følgende skript for å opprette en ikke-gruppert indeks.

BRUK [test] GO SET ANSI_PADDING ON GO CREATE NONCLUSTERED INDEX [NonClusteredIndex-20191129-104230] ON [dbo]. [Employee] ([Name] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_INEXTEMPD = OFF , ONLINE = AV, ALLOW_ROW_LOCKS = PÅ, ALLOW_PAGE_LOCKS = PÅ) PÅ [PRIMÆR] GO

Resultatet blir som følger.

Tabellposten er sortert etter en gruppert indeks hvis den er opprettet. Denne nye ikke-grupperte indeksen vil sortere tabellen i henhold til definisjonen og lagres i en egen fysisk adresse. Ovennevnte skript oppretter indeksen i kolonnen “NAVN” i medarbeidertabellen. Denne indeksen vil sortere tabellen i stigende rekkefølge i kolonnen "Navn". Tabelldataene og indeksen vil bli lagret på forskjellige steder, som vi sa tidligere. Utfør nå følgende skript for å se effekten av en ny ikke-gruppert indeks.

velg Navn fra ansatt

Resultatet blir som følger.

Vi kan se i figuren over at kolonnen Navn i tabellen Ansatt er vist i stigende rekkefølge etter navnekolonnen, selv om vi ikke har nevnt "Orden etter ASC" -paragrafen med valgt ledd. Dette er på grunn av den ikke-grupperte indeksen i kolonnen "Navn" opprettet i tabellen medarbeidere. Nå hvis et spørsmål er skrevet for å hente navn, e-postadresse, by og adresse til den spesifikke personen. Databasen vil først søke etter det spesifikke navnet i indeksen, og deretter hente relevante data som vil redusere spørringens hentetid, spesielt når dataene er enorme.

velg Navn, e-postadresse, by, adresse fra ansatt hvor navn = 'Aaaronboy Gutierrez'

Konklusjon

Fra ovennevnte diskusjon ble vi kjent med at den klyngede indeksen bare kan være en, mens den ikke-klyngede indeksen kan være mange. Den klyngede indeksen er raskere sammenlignet med den ikke-klyngede indeksen. Den klyngede indeksen bruker ikke ekstra lagringsplass, mens den ikke-klyngede indeksen trenger ekstra minne for å lagre dem. Hvis vi bruker en primær nøkkelbegrensning på tabellen, blir det automatisk opprettet indeks på den. Videre, hvis vi bruker en unik nøkkelbegrensning på en hvilken som helst kolonne, opprettes det automatisk en ikke-gruppert indeks på den. Ikke-gruppert indeks er raskere sammenlignet med grupperte for innsettings- og oppdateringsoperasjon. En tabell kan ikke ha noen ikke-gruppert indeks.