Dataprogramering

Skapa ett index med DROP_EXISTING = ON i SQL Server

Skapa ett index i SQL Server är en måttlig enkel uppgift som förbättrar sökprestandan. Vi kan skapa ett grupperat såväl som ett icke-grupperat index som kan vara antingen unikt eller icke-unikt enligt krav.

Om vi ​​redan har skapat ett index på ett bord, måste vi dock lägga till / ta bort en eller flera kolumner till ett befintligt index på grund av en kravändring. I det här fallet kan vi använda DROP_EXISTING alternativet utan att skriva den extra raden av kod för att släppa det befintliga indexet först och sedan skapa det igen. (Det här är en gammaldags kodningsstil: Släpp om det finns och skapa ett nytt.)

Med DROP_EXISTING = ON, Kommer SQL Server att släppa och bygga om det befintliga grupperade / icke-grupperade indexet med ändrade indexspecifikationer, vilket håller indexnamnet samma som det existerar. Vi kan lägga till / ta bort kolumner, ändra sorteringsordningen eller ändra en filgrupp också.

DROP_EXISTING är som standard OFF, och det fungerar inte om du inkluderar detta alternativ medan du skapar ett nytt index för första gången. DROP_EXISTING = PÅ i SQL Server fungerar bara om du redan har ett index med det angivna namnet på en tabell.

Med DROP_EXISTING kan vi göra följande:

1. Ändring av ett icke-grupperat index till ett klusterindex är tillåtet.

Med DROP_EXISTING kan vi inte göra följande:

1. Ändring av ett klusterindex till någon annan typ av index är inte tillåtet.

Fördelar med att använda DROP_EXISTING = ON


Om vi ​​skapar ett klusterindex på ett bord, som redan har ett icke-grupperat index, använder vi DROP_EXISTING = ON i SQL Server medan du skapar ett klusterindex har följande fördelar:

• Icke-grupperade index återuppbyggs inte igen. Om vi ​​skapar ett klusterindex med DROP_EXISTING = ON i SQL Server vet vi att klustringsnyckeln inte har ändrats i det icke-klusterade indexet. Detta sparar en betydande tid. Varje icke-grupperat index har en klustringsnyckel som refererar till det klusterade indexet. Om ett klusterindex återuppbyggs måste SQL Server också ändra det icke-klusterade indexet. Det är dock fördelaktigt att använda DROP_EXISTING = ON eftersom vi inte behöver bygga upp de icke-grupperade indexen igen.

• Om vi ​​använder den gamla metoden för att släppa och skapa ett klusterindex, gör det att alla icke-grupperade index på den tabellen ska byggas om igen för att matcha klustringsnyckeln. Detta kan sparas med DROP_EXISTING = ON med klusterindex.

Syntax för Index Creation med DROP_EXISTING


CREATE [Unique] [Clustered | NonClustered] INDEX Index_Name ON (kolumnnamn / s) MED DROP_EXISTING = {ON | AV};


exempel


Skapa ett provbord, Medarbetare.

IF OBJECT_ID ('Employee', 'U') ÄR INTE NULL DROP TABLE Employee; CREATE TABLE Anställd (Id INT PRIMÄR KEY, Namn VARCHAR (50), Telefon VARCHAR (10), Kön CHAR (1), Land VARCHAR (100), Lön DECIMAL (18,2));


1. DROP_EXISTING = ON misslyckas när du skapar ett nytt index


I följande exempel försöker alternativet DROP_EXISTING = ON att hitta ett existerande index med NCI_Employee_TelphoneGender namn, eftersom det inte går ut, uppstår indexbildning med följande felmeddelande.

SKAPA NONCLUSTERED INDEX NCI_Employee_TelphoneGender ON Medarbetare (Telefon, Kön) MED (DROP_EXISTING = ON);


Msg 7999, Level 16, State 9, Line 1
Det gick inte att hitta något index som heter "NCI_Employee_TelphoneGender" för tabellen "Employee".


Skapa ett index


SKAPA NONCLUSTERED INDEX NCI_Employee_TelphoneGender ON Medarbetare (Telefon, Kön);



Fråga för att se lista över kolumner i ett index


I frågan nedan visas telefon- och genkolumnerna i det skapade icke-grupperade indexet.

välj namn som ColumnListInIndex från sys.index_columns som ic inre ansluta sys.columns c på ic.column_id = c.column_id och ic.object_id = c.object_id där ic.object_id = (välj object_id från sys.indexes där namn = 'NCI_Employee_TelphoneGender' och type_desc = 'NONCLUSTERED') och ic.index_id = 2;



2. DROP_EXISTING = PÅ i SQL Server fungerar på befintligt index


I ovanstående exempel har vi skapat en INDEX, NCI_Employee_TelphoneGender på telefon- och könskolumnen. Nu på grund av en förändring i kravet måste vi också inkludera en kolumn Land även i det befintliga icke-grupperade indexet. Vi kan uppnå detta med alternativet DROP_EXISTING = ON.

I exemplet nedan, med DROP_EXISTING = ON, släpper SQL Server först det angivna indexet och återskapar ett index med samma namn och Landskolumn som ingår.

SKAPA NONCLUSTERED INDEX NCI_Employee_TelphoneGender ON Medarbetare (Telefon, Kön, Land) MED (DROP_EXISTING = ON);



Gammal metod för att skriva koden ovan


Koden ovan med DROP_EXISTING = ON är en ersättning för en gammal metod att släppa och skapa ett index igen. Om vi ​​använder koden nedan för att släppa / återskapa klusterindex, kommer det också att bygga upp alla icke-grupperade index så att det kan vara en stor prestationsfrekvens.

OM EXISTER (VÄLJ * FRÅN SYSSEKVENSER SOM VÄRDER si.name = 'NCI_Employee_TelphoneGender') DROP INDEX NCI_Employee_TelphoneGender ON Medarbetare; GÅ CREATE INDEX NCI_Employee_TelphoneGender ON Medarbetare (Telefon, Kön, Land) GO


Sammanfattning


Vi har sett hur DROP_EXISTING = ON i SQL Server hjälper oss med grupperade index för att förbättra prestanda med några exempel och DROP_EXISTING = ON kan bara användas med befintligt skapat index för att ändra deras specifikationer.