ALTER INDEX (Transact-SQL)

gäller för:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)SQL-databas i Microsoft Fabric

Ändrar en befintlig tabell eller ett befintligt visningsindex (radarkiv, kolumnarkiv eller XML) genom att inaktivera, återskapa eller ordna om indexet. eller genom att ange alternativ för indexet.

Transact-SQL syntaxkonventioner

Syntax

Syntax för SQL Server, Azure SQL Database och Azure SQL Managed Instance.

ALTER INDEX { index_name | ALL } ON <object>
{
      REBUILD {
            [ PARTITION = ALL [ WITH ( <rebuild_index_option> [ , ...n ] ) ] ]
          | [ PARTITION = partition_number [ WITH ( <single_partition_rebuild_index_option> [ , ...n ] ) ] ]
      }
    | DISABLE
    | REORGANIZE  [ PARTITION = partition_number ] [ WITH ( <reorganize_option>  ) ]
    | SET ( <set_index_option> [ , ...n ] )
    | RESUME [ WITH (<resumable_index_option> [ , ...n ] ) ]
    | PAUSE
    | ABORT
}
[ ; ]

<object> ::=
{
    { database_name.schema_name.table_or_view_name | schema_name.table_or_view_name | table_or_view_name }
}

<rebuild_index_option> ::=
{
      PAD_INDEX = { ON | OFF }
    | FILLFACTOR = fillfactor
    | SORT_IN_TEMPDB = { ON | OFF }
    | IGNORE_DUP_KEY = { ON | OFF }
    | STATISTICS_NORECOMPUTE = { ON | OFF }
    | STATISTICS_INCREMENTAL = { ON | OFF }
    | ONLINE = { ON [ ( <low_priority_lock_wait> ) ] | OFF }
    | RESUMABLE = { ON | OFF }
    | MAX_DURATION = <time> [ MINUTES ]
    | ALLOW_ROW_LOCKS = { ON | OFF }
    | ALLOW_PAGE_LOCKS = { ON | OFF }
    | MAXDOP = max_degree_of_parallelism
    | DATA_COMPRESSION = { NONE | ROW | PAGE | COLUMNSTORE | COLUMNSTORE_ARCHIVE }
        [ ON PARTITIONS ( { <partition_number> [ TO <partition_number> ] } [ , ...n ] ) ]
    | XML_COMPRESSION = { ON | OFF }
        [ ON PARTITIONS ( { <partition_number> [ TO <partition_number> ] } [ , ...n ] ) ] }

<single_partition_rebuild_index_option> ::=
{
      SORT_IN_TEMPDB = { ON | OFF }
    | MAXDOP = max_degree_of_parallelism
    | RESUMABLE = { ON | OFF }
    | MAX_DURATION = <time> [ MINUTES ]
    | DATA_COMPRESSION = { NONE | ROW | PAGE | COLUMNSTORE | COLUMNSTORE_ARCHIVE }
    | XML_COMPRESSION = { ON | OFF }
    | ONLINE = { ON [ ( <low_priority_lock_wait> ) ] | OFF }
}

<reorganize_option> ::=
{
       LOB_COMPACTION = { ON | OFF }
    |  COMPRESS_ALL_ROW_GROUPS =  { ON | OFF }
}

<set_index_option> ::=
{
      ALLOW_ROW_LOCKS = { ON | OFF }
    | ALLOW_PAGE_LOCKS = { ON | OFF }
    | OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF }
    | IGNORE_DUP_KEY = { ON | OFF }
    | STATISTICS_NORECOMPUTE = { ON | OFF }
    | COMPRESSION_DELAY = { 0 | delay [ Minutes ] }
}

<resumable_index_option> ::=
 {
    MAXDOP = max_degree_of_parallelism
    | MAX_DURATION = <time> [ MINUTES ]
    | <low_priority_lock_wait>
 }

<low_priority_lock_wait> ::=
{
    WAIT_AT_LOW_PRIORITY ( MAX_DURATION = <time> [ MINUTES ] ,
                          ABORT_AFTER_WAIT = { NONE | SELF | BLOCKERS } )
}

Syntax för Azure Synapse Analytics and Analytics Platform System (PDW).

ALTER INDEX { index_name | ALL }
    ON [ schema_name. ] table_name
{
      REBUILD {
            [ PARTITION = ALL [ WITH ( <rebuild_index_option> ) ] ]
          | [ PARTITION = partition_number [ WITH ( <single_partition_rebuild_index_option> ) ] ]
      }
    | DISABLE
    | REORGANIZE [ PARTITION = partition_number ]
}
[ ; ]

<rebuild_index_option> ::=
{
    DATA_COMPRESSION = { COLUMNSTORE | COLUMNSTORE_ARCHIVE }
        [ ON PARTITIONS ( { <partition_number> [ TO <partition_number> ] } [ , ...n ] ) ]
    | XML_COMPRESSION = { ON | OFF }
        [ ON PARTITIONS ( { <partition_number> [ TO <partition_number> ] } [ , ...n ] ) ]
}

<single_partition_rebuild_index_option> ::=
{
    DATA_COMPRESSION = { COLUMNSTORE | COLUMNSTORE_ARCHIVE }
    | XML_COMPRESSION = { ON | OFF }
}

Argument

index_name

Namnet på indexet. Indexnamn måste vara unika i en tabell eller vy men behöver inte vara unika i en databas. Indexnamn måste följa reglerna för identifierare.

ALLA

Anger alla index som är associerade med tabellen eller vyn oavsett indextyp. Om du anger ALL misslyckas instruktionen om ett eller flera index finns i en offlinefilgrupp eller en skrivskyddad filgrupp eller om den angivna åtgärden inte tillåts för en eller flera indextyper. I följande tabell visas indexåtgärder och otillåtna indextyper.

Använda nyckelordet ALL med den här åtgärden	Misslyckas om tabellen har en eller flera
REBUILD WITH ONLINE = ON	XML-index Rumsligt index Kolumnlagringsindex i SQL Server 2017 (14.x) och endast äldre versioner. Senare versioner stöder online återskapande av kolumnlagringsindex.
REBUILD PARTITION = <partition_number>	Icke-partitionerat index, XML-index, rumsligt index eller inaktiverat index
REORGANIZE	Index med ALLOW_PAGE_LOCKS inställt på OFF
REORGANIZE PARTITION = <partition_number>	Icke-partitionerat index, XML-index, rumsligt index eller inaktiverat index
IGNORE_DUP_KEY = ON	XML-index Rumsligt index Kolumnlagringsindex 1
ONLINE = ON	XML-index Rumsligt index Kolumnlagringsindex 1
RESUMABLE = ON	Återanvändbara index stöds inte med nyckelordet ALL

Om ALL anges med PARTITION = <partition_number>måste alla index justeras. Det innebär att de partitioneras baserat på motsvarande partitionsfunktioner. Om du använder ALL med PARTITION kan alla indexpartitioner med samma <partition_number> återskapas eller omorganiseras. Mer information om partitionerade index finns i Partitionerade tabeller och index.

Mer information om onlineindexåtgärder finns i Riktlinjer för onlineindexåtgärder.

database_name

Namnet på databasen.

schema_name

Namnet på schemat som tabellen eller vyn tillhör.

table_or_view_name

Namnet på tabellen eller vyn som är associerad med indexet. Om du vill visa indexinformation för en tabell eller vy använder du sys.indexes katalogvy.

Azure SQL Database stöder namnformatet i tre delar <database_name>.<schema_name>.<object_name> när <database_name> är det aktuella databasnamnet eller <database_name> är tempdb och <object_name> börjar med # eller ##. Om schemanamnet är dbokan <schema_name> utelämnas.

REBUILD [ WITH ( <rebuild_index_option> [ ,... n ] ) ] ]

gäller för: SQL Server 2012 (11.x) och senare versioner, Azure SQL Database och Azure SQL Managed Instance

Anger att indexet återskapas med samma kolumner, indextyp, unikhetsattribut och sorteringsordning. REBUILD aktiverar ett inaktiverat index. Återskapa ett grupperat index återskapar inte associerade icke-grupperade index om inte nyckelordet ALL har angetts. Om indexalternativ inte anges tillämpas de befintliga indexalternativvärdena i sys.indexes. För alla indexalternativ vars värde inte visas i sys.indexesgäller standardvärdet som anges i argumentdefinitionen för alternativet.

Om ALL anges och den underliggande tabellen är en heap har återskapandeåtgärden ingen effekt på heapen. Alla icke-grupperade index som är associerade med tabellen återskapas.

Den REBUILD åtgärden kan loggas minimalt om databasåterställningsmodellen antingen är massloggad eller enkel.

När du återskapar ett primärt XML-index är den underliggande användartabellen inte tillgänglig under hela indexåtgärden.

För columnstore-index återskapar du åtgärden:

• Komprimerar alla data till kolumnarkivet igen. Det finns två kopior av kolumnlagringsindexet medan återskapandeåtgärden pågår. När återskapande är klar tar Databasmotorn bort det ursprungliga kolumnlagringsindexet.
• Bevarar inte sorteringsordningen, om sådan finns. Om du vill återskapa ett columnstore-index och bevara eller införa en sorteringsordning använder du instruktionen CREATE [CLUSTERED] COLUMNSTORE INDEX ... ORDER (...) ... WITH (DROP_EXISTING = ON).

Mer information finns i Optimera indexunderhåll för att förbättra frågeprestanda och minska resursförbrukningen.

SKIFTE

Anger att endast en partition av ett index återskapas eller omorganiseras. PARTITION kan inte anges om index_name inte är ett partitionerat index.

PARTITION = ALL återskapar alla partitioner.

Varning

Det är möjligt att skapa och återskapa nonaligerade index i en tabell med fler än 1 000 partitioner, men stöds inte. Detta kan orsaka försämrad prestanda eller överdriven minnesförbrukning under dessa åtgärder. Microsoft rekommenderar att du endast använder justerade index när antalet partitioner överstiger 1 000.

• partition_number

  Partitionsnumret för ett partitionerat index som ska återskapas eller omorganiseras. partition_number är ett konstant uttryck som kan referera till variabler. Dessa inkluderar användardefinierade typvariabler eller funktioner och användardefinierade funktioner, men kan inte referera till en Transact-SQL-instruktion. partition_number måste finnas eller så misslyckas instruktionen.

• MED ( <single_partition_rebuild_index_option> )

  SORT_IN_TEMPDB, MAXDOP, DATA_COMPRESSIONoch XML_COMPRESSION är de alternativ som kan anges när du återskapar en enskild partition med hjälp av syntaxen för (PARTITION = partition_number). XML-index kan inte anges i en enda partitions återskapandeåtgärd.

INAKTIVERA

Markerar indexet som inaktiverat och otillgängligt för användning av databasmotorn. Alla index kan inaktiveras. Indexdefinitionen för ett inaktiverat index finns kvar i systemkatalogen utan underliggande indexdata. Om du inaktiverar ett grupperat index förhindras användarens åtkomst till underliggande tabelldata. Om du vill aktivera ett index använder du ALTER INDEX REBUILD eller CREATE INDEX WITH DROP_EXISTING. Mer information finns i Inaktivera index och begränsningar och Aktivera index och begränsningar.

OMORGANISERA ett radlagringsindex

För radlagringsindex anger REORGANIZE att indexlövnivån ska ordnas om. Åtgärden REORGANIZE är:

• Utförs alltid online. Det innebär att långsiktiga blockeringstabelllås inte lagras och att frågor eller uppdateringar av data i den underliggande tabellen kan fortsätta under den ALTER INDEX REORGANIZE transaktionen.
• Inte tillåtet för ett inaktiverat index.
• Tillåts inte när ALLOW_PAGE_LOCKS är inställt på OFF.
• Återställs inte när den utförs inom en transaktion och transaktionen återställs.

Not

När ALTER INDEX REORGANIZE använder explicita transaktioner (till exempel ALTER INDEX i en BEGIN TRAN ... COMMIT/ROLLBACK) i stället för det implicita standardtransaktionsläget blir låsbeteendet för REORGANIZE mer restriktivt, vilket kan orsaka blockering. Mer information om implicita transaktioner finns i SET-IMPLICIT_TRANSACTIONS.

Mer information finns i Optimera indexunderhåll för att förbättra frågeprestanda och minska resursförbrukningen.

OMORGANISERA MED ( LOB_COMPACTION = { ON | OFF } )

Gäller för radlagringsindex.

• PÅ

  • Anger för att komprimera alla sidor som innehåller data för dessa stora objektdatatyper: bild, text, ntext, varchar(max), nvarchar(max), varbinary(max)och xml-. Om du komprimerar dessa data kan du minska datastorleken på disken.
  • För ett grupperat index komprimerar detta alla LOB-kolumner som finns i tabellen.
  • För ett icke-grupperat index komprimerar detta alla LOB-kolumner som inte är nyckelkolumner (ingår) i indexet.
  • REORGANIZE ALL utför LOB-komprimering på alla index. För varje index komprimerar detta alla LOB-kolumner i klustrat index, underliggande tabell eller inkluderade kolumner i ett icke-grupperat index.

• BORT

  • Sidor som innehåller stora objektdata komprimeras inte.
  • OFF har ingen effekt på en hög.

OMORGANISERA ett kolumnlagringsindex

För kolumnlagringsindex komprimerar REORGANIZE varje stängd deltaradgrupp till kolumnarkivet som en komprimerad radgrupp. Åtgärden REORGANIZE utförs alltid online. Det innebär att långsiktiga blockeringstabelllås inte lagras och att frågor eller uppdateringar av den underliggande tabellen kan fortsätta under den ALTER INDEX REORGANIZE transaktionen.

Mer information finns i Optimera indexunderhåll för att förbättra frågeprestanda och minska resursförbrukningen.

• REORGANIZE krävs inte för att flytta de stängda deltaradgrupperna till komprimerade radgrupper. TM-processen (background tuple-mover) aktiveras regelbundet för att komprimera de stängda deltaradgrupperna. Vi rekommenderar att du använder REORGANIZE när tuppeln släpar efter. REORGANIZE kan komprimera radgrupper mer aggressivt.
• Information om hur du komprimerar alla öppna och stängda radgrupper finns i REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS).

För columnstore-index i SQL Server 2016 (13.x) och senare versioner, Azure SQL Database och Azure SQL Managed Instance utför REORGANIZE följande extra defragmenteringsoptimeringar online:

• Tar fysiskt bort borttagna rader från en radgrupp när 10% eller fler av raderna har tagits bort logiskt. Borttagna byte frigörs på det fysiska mediet. Om till exempel en komprimerad radgrupp på 1 miljon rader har 100 000 rader borttagna, tar databasmotorn bort de borttagna raderna och komprimerar radgruppen igen med 900 000 rader.

• Kombinerar en eller flera komprimerade radgrupper för att öka rader per radgrupp upp till högst 1 048 576 rader. Om du till exempel massimporterar 5 batchar med 102 400 rader får du 5 komprimerade radgrupper. Om du kör REORGANIZEsammanfogas dessa radgrupper till 1 komprimerad radgrupp med 512 000 rader. Detta förutsätter att det inte finns några ordlistestorlekar eller minnesbegränsningar.

• För radgrupper där 10% eller fler av raderna har tagits bort logiskt försöker databasmotorn kombinera den här radgruppen med en eller flera radgrupper. Till exempel komprimeras radgrupp 1 med 500 000 rader och radgrupp 21 komprimeras med högst 1 048 576 rader. Radgrupp 21 har 60% av raderna borttagna som lämnar 409 830 rader. Databasmotorn föredrar att kombinera dessa två radgrupper för att komprimera en ny radgrupp som har 909 830 rader.

OMORGANISERA MED ( COMPRESS_ALL_ROW_GROUPS = { ON | OFF } )

Gäller för columnstore-index.

gäller för: SQL Server 2016 (13.x) och senare versioner, Azure SQL Database och Azure SQL Managed Instance

COMPRESS_ALL_ROW_GROUPS är ett sätt att tvinga öppna eller stängda deltaradgrupper till kolumnarkivet. Med det här alternativet är det inte nödvändigt att återskapa kolumnlagringsindexet för att tömma deltaradgrupperna. I kombination med de andra defragmenteringsfunktionerna för borttagning och sammanslagning behöver du inte längre återskapa ett columnstore-index i de flesta situationer.

• PÅ

  Tvingar alla radgrupper till kolumnarkivet, oavsett storlek och tillstånd (stängd eller öppen).

• BORT

  Tvingar alla stängda radgrupper till kolumnarkivet.

Mer information finns i Optimera indexunderhåll för att förbättra frågeprestanda och minska resursförbrukningen.

SET ( <set_index alternativ> [ ,... n ] )

Ändrar indexalternativ utan att återskapa eller omorganisera indexet. SET kan inte anges för ett inaktiverat index.

PAD_INDEX = { ON | AV }

Anger indexutfyllnad. Standardvärdet är OFF.

• PÅ

  Procentandelen ledigt utrymme som anges av fyllningsfaktorn tillämpas på sidorna på mellannivå i indexet. Om FILLFACTOR inte anges samtidigt PAD_INDEX anges till ONanvänds fyllningsfaktorvärdet i sys.indexes.

• BORT

  Sidorna på mellannivå fylls till nära kapacitet, vilket ger tillräckligt med utrymme för minst en rad av den maximala storlek som indexet kan ha, med tanke på uppsättningen nycklar på mellanliggande sidor. Detta inträffar också om PAD_INDEX är inställt på ON men fyllningsfaktorn inte har angetts.

Mer information finns i CREATE INDEX.

FILLFACTOR = fillfactor

Anger en procentandel som anger hur full databasmotorn ska göra lövnivån för varje indexsida när index skapas eller ändras. Värdet för fillfactor måste vara ett heltalsvärde från 1 till 100. Standardvärdet är 0. Fyllningsfaktorvärdena 0 och 100 är desamma i alla avseenden.

En explicit FILLFACTOR-inställning gäller endast när indexet först skapas eller återskapas. Databasmotorn behåller inte dynamiskt den angivna procentandelen tomt utrymme på sidorna. Mer information finns i CREATE INDEX.

Om du vill visa fyllningsfaktorinställningen använder du fill_factor i sys.indexes.

Viktig

Om du skapar ett index med en FILLFACTOR mindre än 100 ökar mängden lagringsutrymme som data upptar eftersom databasmotorn omdistribuerar data enligt fyllningsfaktorn när det skapar eller återskapar ett index.

SORT_IN_TEMPDB = { ON | AV }

Anger om tillfälliga sorteringsresultat ska lagras i tempdb. Standardvärdet är OFF förutom Azure SQL Database Hyperscale. För alla index build-åtgärder i Hyperskala är SORT_IN_TEMPDB alltid ON om inte en återanvändbar indexversion används. För återupptabara indexversioner är SORT_IN_TEMPDB alltid OFF.

• PÅ

  Mellanliggande sorteringsresultat som används för att skapa indexet lagras i tempdb. Detta kan minska den tid som krävs för att skapa ett index. Detta ökar dock mängden diskutrymme som används under indexversionen.

• BORT

  Mellanliggande sorteringsresultat lagras i samma databas som indexet.

Om en sorteringsåtgärd inte krävs eller om sorteringen kan utföras i minnet ignoreras alternativet SORT_IN_TEMPDB.

Mer information finns i SORT_IN_TEMPDB alternativet för index.

IGNORE_DUP_KEY = { ON | AV }

Anger felsvaret när en infogningsåtgärd försöker infoga dubblettnyckelvärden i ett unikt index. Alternativet IGNORE_DUP_KEY gäller endast för infogningsåtgärder när indexet har skapats eller återskapats. Standardvärdet är OFF.

• PÅ

  Ett varningsmeddelande inträffar när dubbletter av nyckelvärden infogas i ett unikt index. Endast de rader som bryter mot unikhetsbegränsningen infogas inte.

• BORT

  Ett felmeddelande uppstår när dubbletter av nyckelvärden infogas i ett unikt index. Hela INSERT-åtgärden återställs.

IGNORE_DUP_KEY kan inte anges till ON för index som skapats i en vy, icke-unika index, XML-index, rumsliga index och filtrerade index.

Om du vill visa inställningen IGNORE_DUP_KEY för ett index använder du kolumnen ignore_dup_key i sys.indexes katalogvy.

I bakåtkompatibel syntax motsvarar WITH IGNORE_DUP_KEYWITH IGNORE_DUP_KEY = ON.

STATISTICS_NORECOMPUTE = { ON | AV }

Inaktivera eller aktivera alternativet för automatisk statistikuppdatering AUTO_STATISTICS_UPDATEför statistiken i indexet. Standardvärdet är OFF.

• PÅ

  Automatiska statistikuppdateringar inaktiveras när indexet har återskapats.

• BORT

  Automatiska statistikuppdateringar aktiveras när indexet har återskapats.

Om du vill återställa automatisk uppdatering av statistik anger du STATISTICS_NORECOMPUTE till OFFeller kör UPDATE STATISTICS utan NORECOMPUTE-satsen.

Varning

Om du inaktiverar automatisk omberäkning av statistik genom att ange STATISTICS_NORECOMPUTE = ONkan du förhindra att frågeoptimeraren väljer optimala körningsplaner för frågor som involverar tabellen.

Om du anger STATISTICS_NORECOMPUTE till ON hindrar inte uppdateringen av indexstatistiken som inträffar under indexet återskapandeåtgärden.

STATISTICS_INCREMENTAL = { ON | AV }

gäller för: SQL Server 2014 (12.x) och senare versioner, Azure SQL Database och Azure SQL Managed Instance

När ONär statistiken som skapas i indexet per partitionsstatistik. När OFFtas den befintliga statistiken bort och databasmotorn beräknar om statistiken. Standardvärdet är OFF.

Om statistik per partition inte stöds ignoreras alternativet och en varning genereras. Inkrementell statistik stöds inte i följande fall:

• Statistik som skapats med index som inte är partitionsjusterade med bastabellen
• Statistik som skapats för läsbara sekundära databaser i tillgänglighetsgruppen
• Statistik som skapats på skrivskyddade databaser
• Statistik som skapats för filtrerade index
• Statistik som skapats för vyer
• Statistik som skapats i interna tabeller
• Statistik som skapats med rumsliga index eller XML-index

ONLINE = { ON | AV }

Anger om underliggande tabeller och associerade index är tillgängliga för frågor och dataändringar under indexåtgärden. Standardvärdet är OFF.

För ett XML-index eller rumsligt index stöds endast ONLINE = OFF och om ONLINE är inställt på ON uppstår ett fel.

Viktig

Onlineindexåtgärder är inte tillgängliga i varje version av Microsoft SQL Server. En lista över funktioner som stöds av versionerna av SQL Server finns i Utgåvor och funktioner som stöds i SQL Server 2022.

• PÅ

  Långsiktiga tabelllås hålls inte under indexåtgärdens varaktighet. Under huvudfasen av indexåtgärden lagras endast ett delat avsiktslås (IS) i källtabellen. Detta gör att frågor eller uppdateringar av den underliggande tabellen och index kan fortsätta. I början av åtgärden hålls ett delat (S) lås på källobjektet under en kort tidsperiod. I slutet av åtgärden, under en kort tidsperiod, hämtas ett delat (S) lås på objektet om ett icke-grupperat index skapas. Ett schemaändringslås (Sch-M) hämtas när ett klustrat index skapas eller tas bort online och när ett klustrat eller icke-grupperat index återskapas. ONLINE kan inte anges till ON när ett index skapas i en lokal tillfällig tabell.

  Not

  Du kan använda alternativet WAIT_AT_LOW_PRIORITY för att minska eller undvika blockering under onlineindexåtgärder. Mer information finns i WAIT_AT_LOW_PRIORITY med onlineindexåtgärder.

• BORT

  Tabelllås tillämpas under indexåtgärdens varaktighet. En offlineindexåtgärd som skapar, återskapar eller släpper ett klustrat, rumsligt index eller XML-index, eller återskapar eller släpper ett icke-grupperat index, hämtar ett schemaändringslås (Sch-M) i tabellen. Detta förhindrar all användaråtkomst till den underliggande tabellen under hela åtgärden. En offlineindexåtgärd som skapar ett icke-grupperat index hämtar ursprungligen ett delat lås (S) i tabellen. Detta förhindrar ändringar av den underliggande tabelldefinitionen, men tillåter läsning och ändring av data i tabellen medan indexet byggs.

Mer information finns i Utföra indexåtgärder online och riktlinjer för onlineindexåtgärder.

Index, inklusive index i globala temporära tabeller, kan återskapas online förutom i följande fall:

• XML-index
• Index i en lokal temporär tabell
• Första unika klustrade index i en vy
• Inaktiverade klustrade index
• Grupperade kolumnlagringsindex i SQL Server 2017 (14.x)) och tidigare versioner
• Icke-grupperade columnstore-index i SQL Server 2016 (13.x)) och tidigare versioner
• Grupperat index, om den underliggande tabellen innehåller LOB-datatyper (bild, ntext, text) och rumsliga datatyper
• varchar(max) och varbinary(max) kolumner kan inte ingå i en indexnyckel. I SQL Server (från och med SQL Server 2012 (11.x)), i Azure SQL Database och i Azure SQL Managed Instance, när en tabell innehåller varchar(max) eller varbinary(max) kolumner, kan ett grupperat index som innehåller andra kolumner skapas eller återskapas med hjälp av alternativet ONLINE.

Mer information finns i Hur onlineindexåtgärder fungerar.

RESUMABLE = { ON | OFF}

gäller för: SQL Server 2017 (14.x) och senare versioner, Azure SQL Database och Azure SQL Managed Instance

Anger om en onlineindexåtgärd kan återupptas.

• PÅ

  Indexåtgärden kan återupptas.

• BORT

  Indexåtgärden kan inte återupptas.

MAX_DURATION = tid [ MINUTER ] som används med RESUMABLE = ON (kräver ONLINE = ON)

gäller för: SQL Server 2017 (14.x) och senare versioner, Azure SQL Database och Azure SQL Managed Instance

Anger hur länge, i heltalsminuter, en återupptabar indexåtgärd körs innan den pausas.

ALLOW_ROW_LOCKS = { ON | AV }

Anger om radlås tillåts. Standardvärdet är ON.

• PÅ

  Radlås tillåts vid åtkomst till indexet. Databasmotorn avgör när radlås används.

• BORT

  Radlås används inte.

ALLOW_PAGE_LOCKS = { ON | AV }

Anger om sidlås tillåts. Standardvärdet är ON.

• PÅ

  Sidlås tillåts när du kommer åt indexet. Databasmotorn avgör när sidlås används.

• BORT

  Sidlås används inte.

OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | AV }

gäller för: SQL Server 2019 (15.x) och senare versioner, Azure SQL Database och Azure SQL Managed Instance

Anger om du vill optimera för att undvika infogningskonkurnation på sista sidan. Standardvärdet är OFF. Mer information finns i sekventiella nycklar.

MAXDOP = max_degree_of_parallelism

Åsidosätter maximal grad av parallellitet konfigurationsalternativ för indexåtgärden. Mer information finns i Konfigurera den maximala graden av parallellitet serverkonfigurationsalternativ. Använd MAXDOP för att begränsa graden av parallellitet och den resulterande resursförbrukningen för en indexgenereringsåtgärd.

Även om det MAXDOP alternativet stöds syntaktiskt för alla XML-index och rumsliga index, använder ALTER INDEX för närvarande endast en enda processor.

max_degree_of_parallelism kan vara:

• 1

  Undertrycker parallell plangenerering.

• >1

  Begränsar den maximala grad av parallellitet som används i en parallell indexåtgärd till det angivna talet eller mindre baserat på den aktuella systemarbetsbelastningen.

• 0 (standard)

  Använder den grad av parallellitet som anges på server-, databas- eller arbetsbelastningsgruppsnivå, såvida den inte minskas baserat på den aktuella systemarbetsbelastningen.

Mer information finns i Konfigurera parallella indexåtgärder.

Not

Parallella indexåtgärder är inte tillgängliga i varje version av SQL Server. En lista över funktioner som stöds av versionerna av SQL Server finns i Utgåvor och funktioner som stöds i SQL Server 2022.

COMPRESSION_DELAY = { 0 | varaktighet [ minuter ] }

gäller för: SQL Server (från och med SQL Server 2016 (13.x)), Azure SQL Database och Azure SQL Managed Instance

För en diskbaserad tabell med ett kolumnlagringsindex anger det minsta antalet minuter som en deltaradgrupp i stängt tillstånd måste finnas kvar i deltaarkivet innan databasmotorn kan komprimera den till en komprimerad radgrupp. Eftersom diskbaserade tabeller inte spårar infognings- och uppdateringstider på enskilda rader tillämpar databasmotorn endast den här fördröjningen på deltalagringsradgrupper i stängt tillstånd.

Standardvärdet är 0 minuter.

Rekommendationer om när du ska använda COMPRESSION_DELAYfinns i Komma igång med columnstore för driftanalys i realtid.

DATA_COMPRESSION

Anger datakomprimeringsalternativet för det angivna indexet, partitionsnumret eller partitionsintervallet. Alternativen är följande:

• INGEN

  Index eller angivna partitioner komprimeras inte. Detta gäller inte för kolumnlagringsindex.

• RAD

  Index eller angivna partitioner komprimeras med hjälp av radkomprimering. Detta gäller inte för kolumnlagringsindex.

• SIDA

  Index eller angivna partitioner komprimeras med hjälp av sidkomprimering. Detta gäller inte för kolumnlagringsindex.

• COLUMNSTORE

  gäller för: SQL Server 2014 (12.x) och senare versioner, Azure SQL Database och Azure SQL Managed Instance

  Gäller endast för columnstore-index, inklusive både icke-grupperade kolumnarkiv och grupperade kolumnlagringsindex. Om du anger COLUMNSTORE tar bort all annan datakomprimering, inklusive COLUMNSTORE_ARCHIVE.

• COLUMNSTORE_ARCHIVE

  gäller för: SQL Server 2014 (12.x) och senare versioner, Azure SQL Database och Azure SQL Managed Instance

  Gäller endast för columnstore-index, inklusive både icke-grupperade kolumnarkiv och grupperade kolumnlagringsindex. COLUMNSTORE_ARCHIVE komprimerar den angivna partitionen ytterligare till en mindre storlek. Detta kan användas för arkivering, eller för andra situationer som kräver en mindre lagringsstorlek och har råd med mer tid för lagring och hämtning.

Mer information om komprimering finns i Datakomprimering.

XML_COMPRESSION

gäller för: SQL Server 2022 (16.x) och senare versioner, Azure SQL Database och Azure SQL Managed Instance

Anger XML-komprimeringsalternativet för det angivna indexet som innehåller en eller flera xml- datatypskolumner. Alternativen är följande:

• PÅ

  Index eller angivna partitioner komprimeras med hjälp av XML-komprimering.

• BORT

  Index eller angivna partitioner komprimeras inte.

PÅ PARTITIONER ( { <partition_number_expression> | <intervall> } [ ,... n ] )

Anger de partitioner som inställningarna för DATA_COMPRESSION eller XML_COMPRESSION gäller för. Om indexet inte är partitionerat genererar argumentet ON PARTITIONS ett fel. Om ON PARTITIONS-satsen inte tillhandahålls gäller alternativet DATA_COMPRESSION eller XML_COMPRESSION för alla partitioner i ett partitionerat index.

<partition_number_expression> kan anges på följande sätt:

• Ange numret för en partition, till exempel: ON PARTITIONS (2).
• Ange partitionsnumren för flera enskilda partitioner avgränsade med kommatecken, till exempel: ON PARTITIONS (1, 5).
• Ange både intervall och enskilda partitioner: ON PARTITIONS (2, 4, 6 TO 8).

<range> kan anges som partitionsnummer avgränsade med ordet TO, till exempel: ON PARTITIONS (6 TO 8).

Om du vill ange olika typer av datakomprimering för olika partitioner anger du alternativet DATA_COMPRESSION mer än en gång, till exempel:

REBUILD WITH
(
DATA_COMPRESSION = NONE ON PARTITIONS (1),
DATA_COMPRESSION = ROW ON PARTITIONS (2, 4, 6 TO 8),
DATA_COMPRESSION = PAGE ON PARTITIONS (3, 5)
);

Du kan också ange alternativet XML_COMPRESSION mer än en gång, till exempel:

REBUILD WITH
(
XML_COMPRESSION = OFF ON PARTITIONS (1),
XML_COMPRESSION = ON ON PARTITIONS (2, 4, 6 TO 8),
XML_COMPRESSION = OFF ON PARTITIONS (3, 5)
);

ÅTERUPPTA

gäller för: SQL Server 2017 (14.x) och senare versioner, Azure SQL Database och Azure SQL Managed Instance

Återupptar en indexåtgärd som pausas manuellt, eftersom den maximala varaktigheten har uppnåtts eller på grund av ett fel.

• MAX_DURATION

  Anger hur länge, i heltalsminuter, en återupptabar indexåtgärd körs efter att ha återupptagits innan den pausas igen.

• WAIT_AT_LOW_PRIORITY

  Om du återupptar en indexgenereringsåtgärd efter en paus måste du hämta de nödvändiga låsen. WAIT_AT_LOW_PRIORITY anger att indexet build-åtgärden hämtar lås med låg prioritet, vilket gör att andra åtgärder kan fortsätta medan indexet bygge åtgärden väntar. Om du utelämnar alternativet WAIT_AT_LOW_PRIORITY motsvarar det WAIT_AT_LOW_PRIORITY (MAX_DURATION = 0 minutes, ABORT_AFTER_WAIT = NONE). Mer information finns i WAIT_AT_LOW_PRIORITY.

PAUS

gäller för: SQL Server 2017 (14.x) och senare versioner, Azure SQL Database och Azure SQL Managed Instance

Pausar en återupptabar indexversionsåtgärd.

AVBRYTA

gäller för: SQL Server 2017 (14.x) och senare versioner, Azure SQL Database och Azure SQL Managed Instance

Avbryter en aktiv eller pausad indexgenereringsåtgärd som startades som återupptabar. Du måste uttryckligen köra ett ABORT kommando för att avsluta en återupptabar indexgenereringsåtgärd. Ett fel eller en paus i en återupptabar indexåtgärd avslutar inte körningen. I stället lämnar den åtgärden i ett obestämt paustillstånd.

Anmärkningar

ALTER INDEX kan inte användas för att partitionera om ett index eller flytta det till en annan filgrupp. Den här instruktionen kan inte användas för att ändra indexdefinitionen, till exempel lägga till eller ta bort kolumner eller ändra kolumnordningen. Använd CREATE INDEX med DROP_EXISTING-satsen för att utföra dessa åtgärder.

När ett alternativ inte uttryckligen anges tillämpas den aktuella inställningen. Om en FILLFACTOR inställning till exempel inte anges i REBUILD-satsen används fyllningsfaktorvärdet som lagras i systemkatalogen under ombyggnadsprocessen. Om du vill visa de aktuella indexalternativinställningarna använder du sys.indexes.

Värdena för ONLINE, MAXDOPoch SORT_IN_TEMPDB lagras inte i systemkatalogen. Om det inte anges i index-instruktionen används standardvärdet för alternativet.

På datorer med flera processorer, precis som andra frågor, använder ALTER INDEX REBUILD automatiskt fler processorer för att utföra de genomsöknings- och sorteringsåtgärder som är associerade med att ändra indexet. Omvänt är ALTER INDEX REORGANIZE en enda trådad åtgärd. Mer information finns i Konfigurera parallella indexåtgärder.

I SQL-databasen i Microsoft Fabric stöds inte ALTER INDEX ALL, men ALTER INDEX <index name> är det.

Återskapa index

Om du återskapar ett index sjunker och indexet återskapas. Detta tar bort fragmentering, frigör diskutrymme genom att komprimera sidorna baserat på den angivna eller befintliga fyllningsfaktorinställningen och ordna om indexraderna på sammanhängande sidor. När ALL anges tas alla index i tabellen bort och återskapas i en enda transaktion. Begränsningar för främmande nycklar behöver inte tas bort i förväg. När index med 128 omfattningar eller mer återskapas, defersar databasmotorn de faktiska sidallokeringarna och deras associerade lås tills transaktionen har checkats in. Mer information finns i Uppskjuten frigöring.

Mer information finns i Optimera indexunderhåll för att förbättra frågeprestanda och minska resursförbrukningen.

Omorganisera index

Omorganisering av ett index använder minimala systemresurser. Den defragmenterar lövnivån för grupperade och icke-grupperade index i tabeller och vyer genom att fysiskt ordna om bladnivåsidorna så att de matchar lövnodernas logiska ordning, från vänster till höger. Omorganiseringen komprimerar också indexsidorna. Komprimering baseras på det befintliga fyllningsfaktorvärdet.

När ALL anges omorganiseras relationsindex, både klustrade och icke-grupperade, och XML-index i tabellen. Vissa begränsningar tillämpas när du anger ALL.

Mer information finns i Optimera indexunderhåll för att förbättra frågeprestanda och minska resursförbrukningen.

Not

För en tabell med ett ordnat kolumnlagringsindex sorterar ALTER INDEX REORGANIZE inte om data. Om du vill använda dataanvändningen ALTER INDEX REBUILD.

Inaktivera index

Om du inaktiverar ett index förhindras användarens åtkomst till indexet och för klustrade index till underliggande tabelldata. Indexdefinitionen finns kvar i systemkatalogen. Om du inaktiverar ett icke-grupperat index eller klustrat index i en vy tas indexdata bort fysiskt. Om du inaktiverar ett klustrade index förhindras åtkomst till data, men data förblir omainterade i B-trädet tills indexet tas bort eller återskapas. Om du vill se om ett index är inaktiverat använder du kolumnen is_disabled i sys.indexes katalogvyn.

Not

I dokumentationen används termen B-träd vanligtvis som referens till index. I radlagringsindex implementerar databasmotorn ett B+-träd. Detta gäller inte för kolumnlagringsindex eller index i minnesoptimerade tabeller. Mer information finns i arkitekturen och designguiden för SQL Server och Azure SQL-index.

Om en tabell finns i en transaktionsreplikeringspublikation kan du inte inaktivera ett index som är associerat med en primärnyckelbegränsning. Dessa index krävs av replikering. Om du vill inaktivera ett sådant index måste du först ta bort tabellen från publikationen. Mer information finns i Publicera data och databasobjekt.

Använd ALTER INDEX REBUILD-instruktionen eller CREATE INDEX WITH DROP_EXISTING-instruktionen för att aktivera indexet. Det går inte att återskapa ett inaktiverat klustrade index med alternativet ONLINE inställt på ON. Mer information finns i Inaktivera index och begränsningar.

Ange alternativ

Du kan ange alternativen ALLOW_ROW_LOCKS, ALLOW_PAGE_LOCKS, OPTIMIZE_FOR_SEQUENTIAL_KEY, IGNORE_DUP_KEYoch STATISTICS_NORECOMPUTE för ett angivet index utan att återskapa eller omorganisera indexet. De ändrade värdena tillämpas omedelbart på indexet. Om du vill visa de här inställningarna använder du sys.indexes. Mer information finns i Ange indexalternativ.

Alternativ för rad- och sidlås

När ALLOW_ROW_LOCKS = ON och ALLOW_PAGE_LOCK = ONtillåts lås på radnivå, sidnivå och tabellnivå när du kommer åt indexet. Databasmotorn väljer lämpligt lås och kan eskalera låset från ett rad- eller sidlås till ett tabelllås.

När ALLOW_ROW_LOCKS = OFF och ALLOW_PAGE_LOCK = OFFtillåts endast ett lås på tabellnivå när du kommer åt indexet.

Om ALL anges när alternativen för rad- eller sidlås anges tillämpas inställningarna på alla index. När den underliggande tabellen är en heap tillämpas inställningarna på följande sätt:

Alternativ	Gäller för
ALLOW_ROW_LOCKS = ON eller OFF	Heap och alla associerade icke-grupperade index.
ALLOW_PAGE_LOCKS = ON	Heap och alla associerade icke-grupperade index.
ALLOW_PAGE_LOCKS = OFF	De icke-illustrerade indexen, där alla sidlås inte tillåts. För heap tillåts inte endast delade (S), uppdatera (U) och exklusiva (X) sidlås. Databasmotorn kan fortfarande hämta avsiktssidelås (IS, IUeller IX) för interna ändamål.

Varning

Vi rekommenderar inte att du inaktiverar rad- eller sidlås på ett index. Samtidighetsrelaterade problem kan uppstå och vissa funktioner kan vara otillgängliga. Ett index kan till exempel inte ordnas om när ALLOW_PAGE_LOCKS är inställt på OFF.

Onlineindexåtgärder

När du återskapar ett index och alternativet ONLINE anges till ONär data i indexet, dess associerade tabell och andra index i samma tabell tillgängliga för frågor och ändringar. Du kan också återskapa en del av ett index som finns på en enda partition online. Exklusiva tabelllås hålls endast under en kort tid i slutet av indexet återskapas.

Omorganiseringen av ett index utförs alltid online. Processen låser sig endast under korta tidsperioder och kommer sannolikt inte att blockera frågor eller uppdateringar.

Du kan endast utföra samtidiga onlineindexåtgärder i samma tabell eller tabellpartition när du utför följande åtgärder:

• Skapa flera icke-illustrerade index.
• Ordna om olika index i samma tabell.
• Omorganisera olika index samtidigt som index som inte överlappas återskapas i samma tabell.

Alla andra onlineindexåtgärder som utförs samtidigt misslyckas. Du kan till exempel inte återskapa två eller flera index i samma tabell samtidigt, eller skapa ett nytt index samtidigt som du återskapar ett befintligt index i samma tabell.

Mer information finns i Utföra indexåtgärder online.

Återupptabara indexåtgärder

gäller för: SQL Server 2017 (14.x) och senare versioner, Azure SQL Database och Azure SQL Managed Instance

Du kan göra ett återskapande av onlineindexet igen. Det innebär att återskapande av index kan stoppas och senare startas om från den punkt där det stoppades. Om du vill köra ett återskapat index som återtagningsbart anger du alternativet RESUMABLE = ON.

Följande riktlinjer gäller för återupptabara indexåtgärder:

• Om du vill använda alternativet RESUMABLE måste du också använda alternativet ONLINE.
• Alternativet RESUMABLE sparas inte i metadata för ett visst index och gäller endast varaktigheten för den aktuella DDL-instruktionen. Därför måste RESUMABLE = ON-satsen anges uttryckligen för att möjliggöra återupptagande.
• Alternativet MAX_DURATION kan anges i två kontexter:
  • MAX_DURATION för alternativet RESUMABLE anger tidsintervallet för ett index som skapas. Efter den här tiden har förflutit och om indexversionen fortfarande körs pausas den. Du bestämmer när bygget för ett pausat index kan återupptas. Den tiden i minuter för MAX_DURATION måste vara större än 0 minuter och mindre än eller lika med en vecka (7 * 24 * 60 = 10080 minuter). En lång paus i en indexåtgärd kan märkbart påverka DML-prestanda för en specifik tabell samt databasdiskkapaciteten eftersom både det ursprungliga indexet och det nyligen skapade indexet kräver diskutrymme och måste uppdateras av DML-åtgärder. Om MAX_DURATION alternativet utelämnas fortsätter indexåtgärden tills det har slutförts eller tills ett fel inträffar.
  • MAX_DURATION för alternativet WAIT_AT_LOW_PRIORITY anger tiden att vänta med hjälp av lås med låg prioritet om indexåtgärden blockeras innan åtgärden vidtas. Mer information finns i WAIT_AT_LOW_PRIORITY med onlineindexåtgärder.
• Om du vill pausa indexåtgärden direkt kan du köra kommandot ALTER INDEX PAUSE eller köra kommandot KILL <session_id>.
• Om du kör den ursprungliga ALTER INDEX REBUILD-instruktionen igen med samma parametrar återupptas en pausad återskapande av index. Du kan också återuppta en pausad återskapande av index genom att köra ALTER INDEX RESUME-instruktionen.
• Kommandot ABORT stoppar sessionen som kör en indexversion och avbryter indexåtgärden. Du kan inte återuppta en indexåtgärd som har avbrutits.
• När du återupptar en indexåterställningsåtgärd som har pausats kan du ändra värdet MAXDOP till ett nytt värde. Om MAXDOP inte anges när du återupptar en indexåtgärd som har pausats används det MAXDOP värde som användes för den senaste meritförteckningen. Om alternativet MAXDOP inte har angetts alls för en ny indexåtgärd används standardvärdet.

En återupptabar indexåtgärd körs tills den har slutförts, pausar eller misslyckas. Om åtgärden pausas utfärdas ett fel som anger att åtgärden pausades och att indexet inte slutfördes. Om åtgärden misslyckas utfärdas även ett fel.

Om du vill se om en indexåtgärd körs som en återupptabar åtgärd och kontrollera dess aktuella körningstillstånd använder du sys.index_resumable_operations katalogvyn.

Resurser

Följande resurser krävs för att återuppta indexåtgärder:

• Ytterligare utrymme krävs för att behålla indexet som skapas, inklusive tiden då bygget pausas.
• Ytterligare loggdataflöde under sorteringsfasen. Den totala användningen av loggutrymme för återupptabara index är mindre jämfört med vanlig återskapande av onlineindex och tillåter loggtrumsavgränsning under den här åtgärden.
• DDL-instruktioner som försöker ändra ett index som återskapas eller dess associerade tabell medan indexåtgärden pausas tillåts inte.
• Ghost-rensning blockeras i det inbyggda indexet under åtgärdens varaktighet både när åtgärden pausas och medan åtgärden körs.
• Om tabellen innehåller LOB-kolumner krävs ett schemaändringslås (Sch-M) i början av åtgärden för att kunna återuppta klustrad indexversion.

Aktuella funktionsbegränsningar

Återtagningsbara åtgärder för återskapande av index har följande begränsningar:

• Alternativet SORT_IN_TEMPDB = ON stöds inte för återupptabara indexåtgärder.
• DDL-kommandot med RESUMABLE = ON kan inte köras i en explicit transaktion.
• Du kan inte skapa ett återupptabart index som innehåller:
  • Beräknad eller tidsstämpel/radversion kolumner som nyckelkolumner.
  • LOB-kolumn som en inkluderad kolumn.
• Återupptabara indexåtgärder stöds inte för:
  • Kommandot ALTER INDEX REBUILD ALL
  • Kommandot ALTER TABLE REBUILD
  • Kolumnlagringsindex
  • Filtrerade index
  • Inaktiverade index

WAIT_AT_LOW_PRIORITY med onlineindexåtgärder

gäller för: SQL Server 2014 (12.x) och senare versioner, Azure SQL Database och Azure SQL Managed Instance

När du inte använder alternativet WAIT_AT_LOW_PRIORITY måste alla aktiva blockerande transaktioner som innehåller lås i tabellen eller indexet slutföras för att indexet ska kunna startas och slutföras. När onlineindexåtgärden startar och innan den slutförs måste den hämta ett delat (S) eller en schemaändring (Sch-M) låsa tabellen och hålla den under en kort tid. Även om låset endast hålls under en kort tid kan det avsevärt påverka arbetsbelastningens dataflöde, öka frågefördröjningen eller orsaka tidsgränser för körning.

För att undvika dessa problem kan du med alternativet WAIT_AT_LOW_PRIORITY hantera beteendet för S eller Sch-M lås som krävs för att en onlineindexåtgärd ska starta och slutföras och välja bland tre alternativ. Om det under den väntetid som anges av MAX_DURATION = n [minutes] inte finns någon blockering som omfattar indexåtgärden fortsätter indexåtgärden omedelbart.

WAIT_AT_LOW_PRIORITY gör att onlineindexåtgärden väntar med hjälp av lås med låg prioritet, vilket gör att andra åtgärder som använder normala prioritetslås kan fortsätta under tiden. Om du utelämnar alternativet WAIT_AT_LOW_PRIORITY motsvarar det WAIT_AT_LOW_PRIORITY (MAX_DURATION = 0 minutes, ABORT_AFTER_WAIT = NONE).

MAX_DURATION = tid [MINUTES]

Väntetiden (ett heltalsvärde som anges i minuter) som onlineindexåtgärden väntar med hjälp av lås med låg prioritet. Om åtgärden blockeras under MAX_DURATION tid körs den angivna ABORT_AFTER_WAIT åtgärden. MAX_DURATION tiden är alltid i minuter och ordet MINUTES kan utelämnas.

ABORT_AFTER_WAIT = [NONE | SELF | BLOCKERS ]

• NONE: Fortsätt att vänta på låset med normal prioritet.
• SELF: Avsluta den onlineindexåtgärd som körs för närvarande, utan att vidta några åtgärder. Alternativet SELF kan inte användas när MAX_DURATION är 0.
• BLOCKERS: Avsluta alla användartransaktioner som blockerar onlineindexåtgärden så att åtgärden kan fortsätta. Alternativet BLOCKERS kräver att huvudkontot som kör CREATE INDEX- eller ALTER INDEX-instruktionen har ALTER ANY CONNECTION behörighet.

Du kan använda följande utökade händelser för att övervaka indexåtgärder som väntar på lås med låg prioritet:

• lock_request_priority_state
• process_killed_by_abort_blockers
• ddl_with_wait_at_low_priority

Begränsningar för rumsligt index

När du återskapar ett rumsligt index är den underliggande användartabellen inte tillgänglig under indexåtgärden.

Det PRIMARY KEY villkoret i användartabellen kan inte ändras när ett rumsligt index definieras i en kolumn i den tabellen. Om du vill ändra villkoret PRIMARY KEY släpper du först varje rumsligt index i tabellen. När du har modifierat villkoret PRIMARY KEY kan du återskapa vart och ett av de rumsliga indexen.

I en enda partitionsrekonstruktionsåtgärd kan du inte ange några rumsliga index. Du kan dock ange rumsliga index i en återskapad tabell.

Om du vill ändra alternativ som är specifika för ett rumsligt index, till exempel BOUNDING_BOX eller GRID, kan du antingen använda en CREATE SPATIAL INDEX-instruktion som anger DROP_EXISTING = ONeller släppa det rumsliga indexet och skapa ett nytt. Ett exempel finns i CREATE SPATIAL INDEX.

Datakomprimering

Mer information om datakomprimering finns i Datakomprimering.

Följande är de viktigaste punkterna att tänka på i samband med indexgenereringsåtgärder när datakomprimering används:

• Komprimering kan tillåta att fler rader lagras på en sida, men ändrar inte den maximala radstorleken.
• Icke-lövsidor i ett index är inte sidkomprimerade utan kan radkomprimeras.
• Varje icke-grupperat index har en individuell komprimeringsinställning och ärver inte komprimeringsinställningen för den underliggande tabellen.
• När ett klustrat index skapas på en heap ärver det klustrade indexet heapens komprimeringstillstånd om inte ett alternativt komprimeringstillstånd anges.

Följande överväganden gäller återskapande av partitionerade index:

• Du kan inte ändra komprimeringsinställningen för en enskild partition om tabellen har icke-berättigade index.
• Syntaxen ALTER INDEX <index> ... REBUILD PARTITION ... WITH DATA_COMPRESSION = ... återskapar den angivna partitionen av indexet med det angivna komprimeringsalternativet. Om WITH DATA_COMPRESSION-satsen utelämnas används det befintliga komprimeringsalternativet.
• Syntaxen ALTER INDEX <index> ... REBUILD PARTITION = ALL återskapar alla partitioner av indexet med hjälp av befintliga komprimeringsalternativ.
• Syntaxen ALTER INDEX <index> ... REBUILD PARTITION = ALL (WITH ...) återskapar alla partitioner av indexet. Du kan välja olika komprimering för olika partitioner med hjälp av satsen DATA_COMPRESSION = ... ON PARTITIONS ( ...).

Om du vill utvärdera hur ändring av PAGE och ROW komprimering påverkar en tabell, ett index eller en partition använder du den sp_estimate_data_compression_savings lagrade proceduren.

Statistik

När du återskapar ett index uppdateras statistiken för indexet med fullständig genomsökning efter icke-partitionerade index och med standardsamplingsförhållandet för partitionerade index. Ingen annan statistik i tabellen uppdateras som en del av återskapande av index.

Behörigheter

Behörigheten ALTER i tabellen eller vyn krävs.

Versionsanteckningar

• Azure SQL Database stöder inte andra filgrupper än PRIMARY.
• Azure SQL Database och Azure SQL Managed Instance stöder inte FILESTREAM alternativ.
• Kolumnlagringsindex är inte tillgängliga före SQL Server 2012 (11.x).
• Återupptabara indexåtgärder är tillgängliga i SQL Server 2017 (14.x) och senare versioner, Azure SQL Database och Azure SQL Managed Instance.

Exempel på grundläggande syntax

ALTER INDEX index1 ON table1 REBUILD;
ALTER INDEX ALL ON table1 REBUILD;
ALTER INDEX ALL ON dbo.table1 REBUILD;

Exempel: Columnstore-index

De här exemplen gäller för columnstore-index.

A. OMORGANISERA demo

Det här exemplet visar hur kommandot ALTER INDEX REORGANIZE fungerar. Den skapar en tabell som har flera radgrupper och visar sedan hur REORGANIZE sammanfogar radgrupperna.

-- Create a database
CREATE DATABASE [columnstore];
GO

-- Create a rowstore staging table
CREATE TABLE [staging] (
    AccountKey INT NOT NULL,
    AccountDescription NVARCHAR(50),
    AccountType NVARCHAR(50),
    AccountCodeAlternateKey INT
);

-- Insert 10 million rows into the staging table.
DECLARE @loop INT;
DECLARE @AccountDescription VARCHAR(50);
DECLARE @AccountKey INT;
DECLARE @AccountType VARCHAR(50);
DECLARE @AccountCode INT;

SELECT @loop = 0

BEGIN TRANSACTION

WHILE (@loop < 300000)
BEGIN
    SELECT @AccountKey = CAST(RAND() * 10000000 AS INT);
    SELECT @AccountDescription = 'accountdesc ' + CONVERT(VARCHAR(20), @AccountKey);
    SELECT @AccountType = 'AccountType ' + CONVERT(VARCHAR(20), @AccountKey);
    SELECT @AccountCode = CAST(RAND() * 10000000 AS INT);

    INSERT INTO staging
    VALUES (
        @AccountKey,
        @AccountDescription,
        @AccountType,
        @AccountCode
     );

    SELECT @loop = @loop + 1;
END

COMMIT

-- Create a table for the clustered columnstore index
CREATE TABLE cci_target (
    AccountKey INT NOT NULL,
    AccountDescription NVARCHAR(50),
    AccountType NVARCHAR(50),
    AccountCodeAlternateKey INT
);

-- Convert the table to a clustered columnstore index named inxcci_cci_target;
CREATE CLUSTERED COLUMNSTORE INDEX idxcci_cci_target ON cci_target;

Använd alternativet TABLOCK för att infoga rader parallellt. Från och med SQL Server 2016 (13.x) kan den INSERT INTO åtgärden köras parallellt när TABLOCK används.

INSERT INTO cci_target WITH (TABLOCK)
SELECT TOP 300000 * FROM staging;

Kör det här kommandot för att se OPEN delta rowgroups. Antalet radgrupper beror på graden av parallellitet.

SELECT *
FROM sys.dm_db_column_store_row_group_physical_stats
WHERE object_id  = object_id('cci_target');

Kör det här kommandot för att tvinga alla CLOSED och OPEN radgrupper till kolumnarkivet.

ALTER INDEX idxcci_cci_target ON cci_target REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);

Kör det här kommandot igen så ser du att mindre radgrupper sammanfogas till en komprimerad radgrupp.

ALTER INDEX idxcci_cci_target ON cci_target REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);

B. Komprimera STÄNGDa deltaradgrupper till kolumnarkivet

I det här exemplet används alternativet REORGANIZE för att komprimera varje CLOSED deltaradgrupp till kolumnarkivet som en komprimerad radgrupp. Detta är inte nödvändigt, men är användbart när tuppeln inte komprimerar CLOSED radgrupper tillräckligt snabbt.

Du kan köra båda exemplen i AdventureWorksDW2022 exempeldatabas.

Det här exemplet kör REORGANIZE på alla partitioner.

ALTER INDEX cci_FactInternetSales2 ON FactInternetSales2 REORGANIZE;

Det här exemplet kör REORGANIZE på en specifik partition.

-- REORGANIZE a specific partition
ALTER INDEX cci_FactInternetSales2 ON FactInternetSales2 REORGANIZE PARTITION = 0;

C. Komprimera alla öppna och stängda deltaradgrupper till kolumnarkivet

gäller för: SQL Server 2016 (13.x) och senare versioner, Azure SQL Database och Azure SQL Managed Instance

Kommandot REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON) komprimerar varje OPEN och CLOSED delta rowgroup till kolumnarkivet som en komprimerad radgrupp. Detta tömmer deltaarkivet och tvingar alla rader att komprimeras till kolumnarkivet. Detta är särskilt användbart när du har utfört många infogningsåtgärder eftersom dessa åtgärder lagrar raderna i en eller flera deltaradgrupper.

REORGANIZE kombinerar radgrupper för att fylla radgrupper upp till ett maximalt antal rader <= 1 024 576. När du komprimerar alla OPEN och CLOSED radgrupper får du därför inte många komprimerade radgrupper som bara har några rader i sig. Du vill att radgrupper ska vara så fulla som möjligt för att minska den komprimerade storleken och förbättra frågeprestandan.

I följande exempel används databasen AdventureWorksDW2022.

I det här exemplet flyttas alla OPEN och CLOSED deltaradgrupper till kolumnlagringsindexet.

ALTER INDEX cci_FactInternetSales2 ON FactInternetSales2 REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);

I det här exemplet flyttas alla OPEN och CLOSED deltaradgrupper till kolumnlagringsindexet för en specifik partition.

ALTER INDEX cci_FactInternetSales2 ON FactInternetSales2 REORGANIZE PARTITION = 0 WITH (COMPRESS_ALL_ROW_GROUPS = ON);

D. Defragmentera ett columnstore-index online

Gäller inte för: SQL Server 2012 (11.x) och SQL Server 2014 (12.x).

Från och med SQL Server 2016 (13.x) gör REORGANIZE mer än att komprimera deltaradgrupper till kolumnarkivet. Den utför också onlinedefragmentering. Först minskar det storleken på kolumnarkivet genom att fysiskt ta bort borttagna rader när 10% eller fler av raderna i en radgrupp har tagits bort. Sedan kombineras radgrupper tillsammans för att bilda större radgrupper som har upp till högst 1 024 576 rader per radgrupper. Alla radgrupper som ändras komprimeras igen.

Not

Från och med SQL Server 2016 (13.x) är det inte längre nödvändigt att återskapa ett kolumnlagringsindex i de flesta situationer eftersom REORGANIZE fysiskt tar bort borttagna rader och sammanfogar radgrupper. Alternativet COMPRESS_ALL_ROW_GROUPS tvingar alla OPEN eller CLOSED deltaradgrupper till kolumnarkivet som tidigare bara kunde göras med en återskapande. REORGANIZE är online och förekommer i bakgrunden så att frågor kan fortsätta när åtgärden utförs.

I följande exempel utförs en REORGANIZE för att defragmentera indexet genom att fysiskt ta bort rader som har tagits bort logiskt från tabellen och slå samman radgrupper.

ALTER INDEX cci_FactInternetSales2 ON FactInternetSales2 REORGANIZE;

E. Återskapa ett grupperat columnstore-index offline

Gäller för: SQL Server, Azure SQL Database och Azure SQL Managed Instance

Dricks

Från och med SQL Server 2016 (13.x) och i Azure SQL Database rekommenderar vi att du använder ALTER INDEX REORGANIZE i stället för ALTER INDEX REBUILD för kolumnlagringsindex.

Not

I SQL Server 2012 (11.x) och SQL Server 2014 (12.x) används REORGANIZE endast för att komprimera CLOSED radgrupper till kolumnarkivet. Det enda sättet att utföra defragmenteringsåtgärder och tvinga alla deltaradgrupper till kolumnarkivet är att återskapa indexet.

Det här exemplet visar hur du återskapar ett grupperat kolumnlagringsindex och tvingar alla deltaradgrupper till kolumnarkivet. Det här första steget förbereder en tabell FactInternetSales2 i AdventureWorksDW2022-databasen med ett grupperat kolumnlagringsindex och infogar data från de fyra första kolumnerna.

CREATE TABLE dbo.FactInternetSales2 (
    ProductKey [int] NOT NULL,
    OrderDateKey [int] NOT NULL,
    DueDateKey [int] NOT NULL,
    ShipDateKey [int] NOT NULL);

CREATE CLUSTERED COLUMNSTORE INDEX cci_FactInternetSales2
ON dbo.FactInternetSales2;

INSERT INTO dbo.FactInternetSales2
SELECT ProductKey, OrderDateKey, DueDateKey, ShipDateKey
FROM dbo.FactInternetSales;

SELECT * FROM sys.column_store_row_groups;

Resultatet visar en OPEN radgrupp, vilket innebär att SQL Server väntar på att fler rader ska läggas till innan den stänger radgruppen och flyttar data till kolumnarkivet. Nästa instruktion återskapar det klustrade kolumnlagringsindexet, vilket tvingar alla rader till kolumnarkivet.

ALTER INDEX cci_FactInternetSales2 ON FactInternetSales2 REBUILD;
SELECT * FROM sys.column_store_row_groups;

Resultatet av SELECT-instruktionen visar att radgruppen är COMPRESSED, vilket innebär att kolumnsegmenten i radgruppen nu komprimeras och lagras i kolumnarkivet.

F. Återskapa en partition av ett grupperat columnstore-index offline

gäller för: SQL Server 2012 (11.x) och senare versioner, Azure SQL Database och Azure SQL Managed Instance

Om du vill återskapa en partition av ett stort grupperat kolumnlagringsindex använder du ALTER INDEX REBUILD med partitionsalternativet. I det här exemplet återskapas partition 12. Från och med SQL Server 2016 (13.x) rekommenderar vi att du ersätter REBUILD med REORGANIZE.

ALTER INDEX cci_fact3
ON fact3
REBUILD PARTITION = 12;

G. Ändra ett grupperat columnstore-index för att använda arkiveringskomprimering

Gäller inte för: SQL Server 2012 (11.x)

Du kan välja att minska storleken på ett grupperat kolumnlagringsindex ytterligare med hjälp av alternativet COLUMNSTORE_ARCHIVE datakomprimering. Detta är praktiskt för äldre data som du vill behålla på billigare lagring. Vi rekommenderar att du bara använder detta på data som inte används ofta eftersom dekomprimering är långsammare än med den normala COLUMNSTORE komprimering.

I följande exempel återskapas ett grupperat columnstore-index för att använda arkiveringskomprimering och visar sedan hur du tar bort arkiveringskomprimering. Slutresultatet använder endast columnstore-komprimering.

Förbered först exemplet genom att skapa en tabell med ett grupperat kolumnlagringsindex. Komprimera sedan tabellen ytterligare med hjälp av arkiveringskomprimering.

--Prepare the example by creating a table with a clustered columnstore index.
CREATE TABLE SimpleTable (
    ProductKey [int] NOT NULL,
    OrderDateKey [int] NOT NULL,
    DueDateKey [int] NOT NULL,
    ShipDateKey [int] NOT NULL
);

CREATE CLUSTERED INDEX cci_SimpleTable ON SimpleTable (ProductKey);

CREATE CLUSTERED COLUMNSTORE INDEX cci_SimpleTable
ON SimpleTable
WITH (DROP_EXISTING = ON);

--Compress the table further by using archival compression.
ALTER INDEX cci_SimpleTable ON SimpleTable
REBUILD
WITH (DATA_COMPRESSION = COLUMNSTORE_ARCHIVE);
GO

Det här exemplet tar bort arkivkomprimering och använder bara kolumnlagringskomprimering.

ALTER INDEX cci_SimpleTable ON SimpleTable
REBUILD
WITH (DATA_COMPRESSION = COLUMNSTORE);
GO

Exempel: Radlagringsindex

A. Återskapa ett index

I följande exempel återskapas ett enda index i tabellen Employee i AdventureWorks2022-databasen.

ALTER INDEX PK_Employee_EmployeeID ON HumanResources.Employee REBUILD;

B. Återskapa alla index i en tabell och ange alternativ

I följande exempel anges nyckelordet ALL. Detta återskapar alla index som är associerade med tabellen Production.Product i AdventureWorks2022-databasen. Tre alternativ har angetts.

ALTER INDEX ALL ON Production.Product
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON);

I följande exempel läggs alternativet ONLINE, inklusive låsalternativet med låg prioritet, till och alternativet för radkomprimering läggs till.

gäller för: SQL Server 2014 (12.x) och senare versioner, Azure SQL Database och Azure SQL Managed Instance

ALTER INDEX ALL ON Production.Product
REBUILD WITH
(
    FILLFACTOR = 80,
    SORT_IN_TEMPDB = ON,
    STATISTICS_NORECOMPUTE = ON,
    ONLINE = ON ( WAIT_AT_LOW_PRIORITY ( MAX_DURATION = 4 MINUTES, ABORT_AFTER_WAIT = BLOCKERS ) ),
    DATA_COMPRESSION = ROW
);

C. Omorganisera ett index med LOB-komprimering

I följande exempel omorganiseras ett enskilt klustrat index i AdventureWorks2022-databasen. Eftersom indexet innehåller en LOB-datatyp på lövnivån komprimerar instruktionen även alla sidor som innehåller stora objektdata. Det krävs inte att du anger alternativet WITH (LOB_COMPACTION = ON) eftersom standardvärdet är PÅ.

ALTER INDEX PK_ProductPhoto_ProductPhotoID ON Production.ProductPhoto REORGANIZE WITH (LOB_COMPACTION = ON);

D. Ange alternativ för ett index

I följande exempel anges flera alternativ för indexet AK_SalesOrderHeader_SalesOrderNumber i AdventureWorks2022-databasen.

ALTER INDEX AK_SalesOrderHeader_SalesOrderNumber ON
    Sales.SalesOrderHeader
SET (
    STATISTICS_NORECOMPUTE = ON,
    IGNORE_DUP_KEY = ON,
    ALLOW_PAGE_LOCKS = ON
    ) ;
GO

E. Inaktivera ett index

I följande exempel inaktiveras ett icke-grupperat index i tabellen Employee i AdventureWorks2022-databasen.

ALTER INDEX IX_Employee_ManagerID ON HumanResources.Employee DISABLE;

F. Inaktivera begränsningar

I följande exempel inaktiveras en PRIMARY KEY begränsning genom att inaktivera PRIMARY KEY-indexet i AdventureWorks2022-databasen. Den FOREIGN KEY begränsningen för den underliggande tabellen inaktiveras automatiskt och varningsmeddelandet visas.

ALTER INDEX PK_Department_DepartmentID ON HumanResources.Department DISABLE;

Resultatuppsättningen returnerar det här varningsmeddelandet.

Warning: Foreign key 'FK_EmployeeDepartmentHistory_Department_DepartmentID'
on table 'EmployeeDepartmentHistory' referencing table 'Department'
was disabled as a result of disabling the index 'PK_Department_DepartmentID'.

G. Aktivera begränsningar

I följande exempel aktiveras de PRIMARY KEY och FOREIGN KEY begränsningar som inaktiverades i exempel F.

Villkoret PRIMARY KEY aktiveras genom att PRIMARY KEY index återskapas.

ALTER INDEX PK_Department_DepartmentID ON HumanResources.Department REBUILD;

Villkoret FOREIGN KEY aktiveras sedan.

ALTER TABLE HumanResources.EmployeeDepartmentHistory
CHECK CONSTRAINT FK_EmployeeDepartmentHistory_Department_DepartmentID;
GO

H. Återskapa ett partitionerat index

I följande exempel återskapas en enskild partition, ett partitionsnummer 5, av det partitionerade indexet IX_TransactionHistory_TransactionDate i AdventureWorks2022-databasen. Partition 5 återskapas med ONLINE=ON och väntetiden på 10 minuter för lågprioriteringslåset gäller separat för varje lås som hämtas av indexet. Om låset inte kan hämtas för att slutföra indexet återskapas under den här tiden avbryts själva återskapningsåtgärden på grund av ABORT_AFTER_WAIT = SELF.

gäller för: SQL Server 2014 (12.x) och senare versioner, Azure SQL Database och Azure SQL Managed Instance

-- Verify the partitioned indexes.
SELECT *
FROM sys.dm_db_index_physical_stats (DB_ID(),OBJECT_ID(N'Production.TransactionHistory'), NULL , NULL, NULL);
GO
--Rebuild only partition 5.
ALTER INDEX IX_TransactionHistory_TransactionDate
ON Production.TransactionHistory
REBUILD Partition = 5
   WITH (ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 10 minutes, ABORT_AFTER_WAIT = SELF)));
GO

Jag. Ändra komprimeringsinställningen för ett index

I följande exempel återskapas ett index i en icke-partitionerad radlagringstabell.

ALTER INDEX IX_INDEX1
ON T1
REBUILD
WITH (DATA_COMPRESSION = PAGE);
GO

J. Ändra inställningen för ett index med XML-komprimering

gäller för: SQL Server 2022 (16.x) och senare versioner, Azure SQL Database och Azure SQL Managed Instance.

I följande exempel återskapas ett index i en icke-partitionerad radlagringstabell.

ALTER INDEX IX_INDEX1
ON T1
REBUILD
WITH (XML_COMPRESSION = ON);
GO

Fler exempel på datakomprimering finns i Datakomprimering.

K. Återskapa index som kan återupptas online

gäller för: SQL Server 2017 (14.x) och senare versioner, Azure SQL Database och Azure SQL Managed Instance

I följande exempel visas hur du använder återskapande av index som kan återupptas online.

Kör en återskapad onlineindex som återupptabar åtgärd med MAXDOP = 1. Om du kör samma kommando igen efter att en indexåtgärd har pausats återupptas automatiskt åtgärden för att återskapa indexet.

ALTER INDEX test_idx on test_table REBUILD WITH (ONLINE = ON, MAXDOP = 1, RESUMABLE = ON);

Kör en återskapad onlineindex som återupptabar åtgärd med MAX_DURATION inställt på 240 minuter.

ALTER INDEX test_idx on test_table REBUILD WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 240);

Pausa en återupptagning av onlineindex som körs.

ALTER INDEX test_idx on test_table PAUSE;

Återuppta ett återskapande av onlineindex för en återskapad index som kördes som en återupptagningsbar åtgärd som anger ett nytt värde för MAXDOP inställt på 4.

ALTER INDEX test_idx on test_table RESUME WITH (MAXDOP = 4);

Återuppta en online-indexåterbyggnadsåtgärd för ett återskapande av index online som kördes som återupptagningsbar. Ange MAXDOP till 2, ange körningstiden för indexet som ska köras som 240 minuter, och om ett index blockeras på låset väntar du 10 minuter och därefter dödar alla blockerare.

ALTER INDEX test_idx on test_table
    RESUME WITH (MAXDOP = 2, MAX_DURATION = 240 MINUTES,
    WAIT_AT_LOW_PRIORITY (MAX_DURATION = 10, ABORT_AFTER_WAIT = BLOCKERS));

Avbryt återupptabar återskapande av index som körs eller pausas.

ALTER INDEX test_idx on test_table ABORT;

Relaterat innehåll

• arkitektur och designguide för SQL Server och Azure SQL-index
• Utföra indexåtgärder online
• CREATE INDEX (Transact-SQL)
• CREATE SPATIAL INDEX (Transact-SQL)
• CREATE XML INDEX (Transact-SQL)
• DROP INDEX (Transact-SQL)
• Inaktivera index och begränsningar
• XML-index (SQL Server)
• Optimera indexunderhåll för att förbättra frågeprestanda och minska resursförbrukningen
• sys.dm_db_index_physical_stats (Transact-SQL)
• EVENTDATA (Transact-SQL)