Skapa klustrade och icke-klustrade index i SQL Server

I en SQL Server finns två typer av index; Klusterade och icke-klusterade index. Både klusterindex och icke-klusterindex har samma fysiska struktur. Dessutom lagras båda i SQL Server som en B-Tree-struktur.

Clustered index:

En grupperad lista är en viss typ av index som ordnar om den fysiska lagringen av poster i tabellen. Inom SQL Server används index för att påskynda databasåtgärder, vilket leder till hög prestanda. Tabellen kan därför endast ha ett klusterindex, vilket vanligtvis görs på primärnyckeln. Ett klusterindex bladnoder innehåller ”Datasidor”. En tabell kan bara ha ett klusterindex.

Låt oss skapa ett grupperat index för att få en bättre förståelse. Först och främst måste vi skapa en databas.

Skapa databas

För att skapa en databas. Högerklicka på “Databaser” i objektutforskaren och välj “Ny databas” alternativ. Skriv namnet på databasen och klicka på ok. Databasen har skapats enligt bilden nedan.

Nu skapar vi en tabell med namnet "Anställd" med huvudnyckeln genom att använda designvyn. Vi kan se på bilden nedan vi har tilldelat främst det arkiverade namnet “ID” och vi har inte skapat något index på bordet.

Du kan också skapa en tabell genom att köra följande kod.

ANVÄND [test] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo]. [Anställd] ([ID] [int] IDENTITET (1,1) INTE NULL, [Dep_ID] [int] NULL, [Namn] [ 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

Produktionen blir följande.

Ovanstående kod har skapat en tabell med namnet "Anställd" med ett ID-fält, en unik identifierare som en primär nyckel. Nu i denna tabell skapas ett klusterindex automatiskt i kolumn-ID på grund av primära nyckelbegränsningar. Om du vill se alla index på en tabell kör du den lagrade proceduren “Sp_helpindex”. Kör följande kod för att se alla index i en tabell med namnet "Anställd". Denna lagringsprocedur tar ett tabellnamn som inmatningsparameter.

ANVÄNDNINGSTEST EXECUTE sp_helpindex Anställd

Produktionen blir följande.

Ett annat sätt att visa tabellindex är att gå till “Bord” i objektutforskaren. Välj tabellen och förbrukar den. I indexmappen kan du se alla index som är relevanta för den specifika tabellen som visas i figuren nedan.

Eftersom detta är det klustrade indexet så kommer den logiska och fysiska ordningen på indexet att vara densamma. Detta innebär att om en post har Id 3 kommer den att sparas i den tredje raden i tabellen. På samma sätt, om den femte posten har ett ID på 6, kommer den att lagras i 5^th placeringen av tabellen. För att förstå ordningen av poster måste du köra följande skript.

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

Även om poster lagras i kolumnen "Id" i en slumpmässig ordningsföljd. Men på grund av klustrade index på id-kolumnen. Poster lagras fysiskt i stigande ordning av värden i id-kolumnen. För att verifiera detta måste vi köra följande kod.

Välj * från test.dbo.Employee

Produktionen blir följande.

Vi kan se i figuren ovan har poster hämtats i stigande ordning av värden i id-kolumnen.

Anpassat klusterindex

Du kan också skapa ett anpassat grupperat index. Eftersom vi bara kan skapa ett klustrat index så måste vi ta bort det föregående. För att radera indexet, kör följande kod.

ANVÄND [test] GO ALTER TABLE [dbo]. [Anställd] DROP CONSTRAINT [Primary_Key_ID] WITH (ONLINE = OFF) GO

Produktionen blir följande.

Nu för att skapa index, kör följande kod i ett frågefönster. Detta index har skapats i mer än en kolumn så det kallas ett sammansatt index.

ANVÄND [test] GO CREATE CLUSTERED INDEX [ClusteredIndex-20191128-173307] PÅ [dbo]. [Anställd] ([ID] ASC, [Dep_ID] ASC) MED (PAD_INDEX = AV, STATISTICS_NORECOMPUTE = AV, SORT_IN_TEMPDB = AV, DROTNING = AV AV, ONLINE = AV, ALLOW_ROW_LOCKS = PÅ, ALLOW_PAGE_LOCKS = PÅ) PÅ [PRIMÄR] GO

Produktionen blir följande

Vi har skapat ett anpassat klusterindex på ID och Dep_ID. Detta sorterar rader efter Id och sedan efter Dep_Id. För att se detta, kör följande kod. Resultatet blir stigande ordning på ID och sedan Av Dep_id.

VÄLJ [ID], [Dep_ID], [Namn], [e-post], [stad], [adress] FRÅN [test]. [Dbo]. [Anställd]

Produktionen blir följande.

Icke-grupperat index:

Ett icke-klustrat index är en viss indextyp där indexets logiska ordning inte matchar radenas fysiska ordning lagrad på disken. Bladnoden i det icke-klustrade indexet innehåller inte datasidor utan den innehåller information om indexrader. En tabell kan innehålla upp till 249 index. Som standard skapar en unik nyckelbegränsning ett icke-klusterat index. I läsoperationen är icke-klustrade index långsammare än klustrade index. Ett icke-klustrat index har en kopia av data från de indexerade kolumnerna som hålls i ordning tillsammans med referenser till de faktiska dataraderna; pekare till den klustrade listan om någon. Därför är det en bra idé att bara välja de kolumner som används i indexet istället för att använda *. På detta sätt kan data hämtas direkt från duplikatindexet. Ett annars grupperat index används också för att välja återstående kolumner om det skapas.

Syntaxen som används för att skapa ett icke-grupperat index liknar det klustrade indexet. Men nyckelordet “EJ KLUSTERAD” används istället för “KLUSTERAD” i fallet med det icke-klustrade indexet. Kör följande skript för att skapa ett icke-grupperat index.

ANVÄNDNING [test] GO SET ANSI_PADDING ON GO CREATE NONCLUSTERED INDEX [NonClusteredIndex-20191129-104230] ON [dbo]. [Anställd] ([Namn] ASC) MED (PAD_INDEX = AV, STATISTICS_NORECOMPUTE = AV, SORT_INEXTEMPD = OFF , ONLINE = AV, ALLOW_ROW_LOCKS = PÅ, ALLOW_PAGE_LOCKS = PÅ) PÅ [PRIMÄR] GO

Produktionen blir följande.

Tabellposterna sorteras efter ett klusterindex om det har skapats. Detta nya icke klustrade index sorterar tabellen enligt dess definition och kommer att lagras i en separat fysisk adress. Ovanstående skript skapar indexet i kolumnen "NAMN" i tabellen Anställd. Detta index sorterar tabellen i stigande ordning i kolumnen "Namn". Tabelldata och index kommer att lagras på olika platser, som vi sa tidigare. Kör nu följande skript för att se effekten av ett nytt icke-grupperat index.

välj Namn från anställd

Produktionen blir följande.

Vi kan se i figuren ovan att Namnkolumnen i tabellen Anställd har visats i stigande ordning på namnkolumnen, även om vi inte har nämnt "Order by ASC" -satsen med select-satsen. Detta beror på det icke-klustrade indexet i kolumnen "Namn" som skapats i tabellen Anställd. Nu om en fråga skrivs för att hämta namn, e-postadress, ort och adress för den specifika personen. Databasen kommer först att söka efter det specifika namnet i indexet och sedan hämta relevanta data som minskar hämtningstiden för frågan, särskilt när data är enorma.

välj Namn, e-postadress, ort, adress från anställd där namn = 'Aaaronboy Gutierrez'

Slutsats

Från ovanstående diskussion fick vi veta att det klustrade indexet bara kan vara ett medan det icke-klusterade indexet kan vara många. Det klustrade indexet är snabbare jämfört med det icke-klustrade indexet. Det klustrade indexet förbrukar inte extra lagringsutrymme medan det icke-klustrade indexet behöver extra minne för att lagra dem. Om vi ​​tillämpar en primär nyckelbegränsning på tabellen skapas klusterindex automatiskt på den. Dessutom, om vi tillämpar en unik nyckelbegränsning på någon kolumn skapas automatiskt ett icke-klustrat index på den. Icke-klustrade index är snabbare jämfört med klustrade för infoga och uppdatera drift. En tabell kanske inte har något icke-grupperat index.