SKAPA INDEX (Transact-SQL)

gäller för:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)

Skapar ett relationsindex i en tabell eller vy. Kallas även för ett radlagringsindex eftersom det antingen är ett grupperat eller icke-grupperat B-trädindex. Du kan skapa ett radlagringsindex innan det finns data i tabellen. Använd ett radlagringsindex för att förbättra frågeprestanda, särskilt när frågorna väljer från specifika kolumner eller kräver att värden sorteras i en viss ordning.

Azure Synapse Analytics and Analytics Platform System (PDW) stöder för närvarande inte unika begränsningar. Exempel som refererar till unika begränsningar gäller endast för SQL Server och SQL Database.

Information om riktlinjer för indexdesign finns i designguiden SQL Server Index.

exempel:

1. Skapa ett icke-grupperat index i en tabell eller vy

   CREATE INDEX index1 ON schema1.table1 (column1);
   

2. Skapa ett klustrat index i en tabell och använd ett 3-delsnamn för tabellen

   CREATE CLUSTERED INDEX index1 ON database1.schema1.table1 (column1);
   

3. Skapa ett icke-grupperat index med en unik begränsning och ange sorteringsordningen

   CREATE UNIQUE INDEX index1 ON schema1.table1 (column1 DESC, column2 ASC, column3 DESC);
   

Nyckelscenario:

Från och med SQL Server 2016 (13.x) och SQL Database kan du använda ett icke-grupperat index på ett kolumnlagringsindex för att förbättra frågeprestanda för datalager. Mer information finns i Columnstore Indexes – Data Warehouse.

Ytterligare typer av index finns i:

• CREATE XML INDEX
• SKAPA SPATIAL INDEX
• CREATE COLUMNSTORE INDEX

Transact-SQL syntaxkonventioner

Syntax

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

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
    ON <object> ( column [ ASC | DESC ] [ ,...n ] )
    [ INCLUDE ( column_name [ ,...n ] ) ]
    [ WHERE <filter_predicate> ]
    [ WITH ( <relational_index_option> [ ,...n ] ) ]
    [ ON { partition_scheme_name ( column_name )
         | filegroup_name
         | default
         }
    ]
    [ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ]

[ ; ]

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

<relational_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 }
  | DROP_EXISTING = { 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 }
  | OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF }
  | MAXDOP = max_degree_of_parallelism
  | DATA_COMPRESSION = { NONE | ROW | PAGE }
     [ ON PARTITIONS ( { <partition_number_expression> | <range> }
     [ , ...n ] ) ]
  | XML_COMPRESSION = { ON | OFF }
     [ ON PARTITIONS ( { <partition_number_expression> | <range> }
     [ , ...n ] ) ]
}

<filter_predicate> ::=
    <conjunct> [ AND ] [ ...n ]

<conjunct> ::=
    <disjunct> | <comparison>

<disjunct> ::=
        column_name IN (constant ,...n)

<comparison> ::=
        column_name <comparison_op> constant

<comparison_op> ::=
    { IS | IS NOT | = | <> | != | > | >= | !> | < | <= | !< }

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

<range> ::=
<partition_number_expression> TO <partition_number_expression>

Bakåtkompatibelt relationsindex

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
    ON <object> ( column_name [ ASC | DESC ] [ ,...n ] )
    [ WITH <backward_compatible_index_option> [ ,...n ] ]
    [ ON { filegroup_name | "default" } ]

<object> ::=
{
    [ database_name. [ owner_name ] . | owner_name. ]
    table_or_view_name
}

<backward_compatible_index_option> ::=
{
    PAD_INDEX
  | FILLFACTOR = fillfactor
  | SORT_IN_TEMPDB
  | IGNORE_DUP_KEY
  | STATISTICS_NORECOMPUTE
  | DROP_EXISTING
}

Syntax för Azure Synapse Analytics och Parallel Data Warehouse

CREATE CLUSTERED COLUMNSTORE INDEX index_name
    ON [ database_name . [ schema ] . | schema . ] table_name
    [ORDER (column[,...n])]
    [WITH ( DROP_EXISTING = { ON | OFF } )]
[;]


CREATE [ CLUSTERED | NONCLUSTERED ] INDEX index_name
    ON [ database_name . [ schema ] . | schema . ] table_name
        ( { column [ ASC | DESC ] } [ ,...n ] )
    WITH ( DROP_EXISTING = { ON | OFF } )
[;]

Argument

UNIK

Skapar ett unikt index i en tabell eller vy. Ett unikt index är ett där inga två rader tillåts ha samma indexnyckelvärde. Ett grupperat index i en vy måste vara unikt.

Databasmotorn tillåter inte att du skapar ett unikt index för kolumner som redan innehåller duplicerade värden, oavsett om IGNORE_DUP_KEY är inställt på PÅ eller inte. Om detta provas visas ett felmeddelande i databasmotorn. Dubblettvärden måste tas bort innan ett unikt index kan skapas i kolumnen eller kolumnerna. Kolumner som används i ett unikt index bör anges till NOT NULL, eftersom flera null-värden betraktas som dubbletter när ett unikt index skapas.

KLUSTRADE

Skapar ett index där nyckelvärdenas logiska ordning avgör den fysiska ordningen för motsvarande rader i en tabell. Den nedersta nivån, eller lövnivån för det klustrade indexet, innehåller de faktiska dataraderna i tabellen. En tabell eller vy tillåts ett grupperat index i taget.

En vy med ett unikt grupperat index kallas för en indexerad vy. När du skapar ett unikt grupperat index i en vy materialiseras vyn fysiskt. Ett unikt grupperat index måste skapas i en vy innan andra index kan definieras i samma vy. Mer information finns i Skapa indexerade vyer.

Skapa det klustrade indexet innan du skapar några icke-grupperade index. Befintliga icke-grupperade index i tabeller återskapas när ett klustrat index skapas.

Om CLUSTERED inte anges skapas ett icke-grupperat index.

I vissa fall kan du aktivera tidigare inaktiverade index genom att skapa ett grupperat index. Mer information finns i Aktivera index och begränsningar och Inaktivera index och begränsningar.

NONCLUSTERED

Skapar ett index som anger den logiska ordningen för en tabell. Med ett icke-grupperat index är den fysiska ordningen på dataraderna oberoende av deras indexerade ordning.

Varje tabell kan ha upp till 999 icke-grupperade index, oavsett hur indexen skapas: antingen implicit med PRIMÄRNYCKEL och UNIKA begränsningar, eller uttryckligen med CREATE INDEX.

För indexerade vyer kan icke-grupperade index endast skapas i en vy som har ett unikt grupperat index som redan har definierats.

Om inget annat anges är standardindextypen inte illustrerad.

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.

kolumn

Kolumnen eller kolumnerna som indexet baseras på. Ange två eller flera kolumnnamn för att skapa ett sammansatt index för de kombinerade värdena i de angivna kolumnerna. Visa en lista över de kolumner som ska ingå i det sammansatta indexet, i sorteringsprioritetsordning, inom parenteserna efter table_or_view_name.

Upp till 32 kolumner kan kombineras till en enda sammansatt indexnyckel. Alla kolumner i en sammansatt indexnyckel måste finnas i samma tabell eller vy. Den maximala tillåtna storleken för de kombinerade indexvärdena är 900 byte för ett grupperat index, eller 1 700 för ett icke-grupperat index. Gränserna är 16 kolumner och 900 byte för versioner före SQL Database och SQL Server 2016 (13.x).

Kolumner som är av de stora objektdatatyperna (LOB) ntext, text, varchar(max), nvarchar(max), varbinary(max), xmleller bild inte kan anges som nyckelkolumner för ett index. En vydefinition kan inte heller innehålla ntext, texteller bild kolumner, även om de inte refereras till i CREATE INDEX-instruktionen.

Du kan skapa index för CLR-användardefinierade typkolumner om typen stöder binär ordning. Du kan också skapa index för beräknade kolumner som definieras som metodanrop från en användardefinierad typkolumn, så länge metoderna är markerade som deterministiska och inte utför dataåtkomståtgärder. Mer information om hur du indexerar CLR-användardefinierade typkolumner finns i CLR-användardefinierade typer.

[ ASC | DESC ]

Avgör stigande eller fallande sorteringsriktning för den specifika indexkolumnen. Standardvärdet är ASC.

INCLUDE (column [ ,... n ] )

Anger de icke-nyckelkolumner som ska läggas till på lövnivån för det icke-grupperade indexet. Det icke-illustrerade indexet kan vara unikt eller icke-unikt.

Kolumnnamn kan inte upprepas i INCLUDE-listan och kan inte användas samtidigt som både nyckelkolumner och icke-nyckelkolumner. Icke-grupperade index innehåller alltid klustrade indexkolumner om ett klustrat index definieras i tabellen. Mer information finns i Skapa index med inkluderade kolumner.

Alla datatyper tillåts förutom text, ntextoch bild. Från och med SQL Server 2012 (11.x) och Azure SQL Database, om någon av de angivna icke-nyckelkolumnerna är varchar(max), nvarchar(max)eller varbinary(max) datatyper, kan indexet skapas eller återskapas med hjälp av onlinealternativet.

Beräknade kolumner som är deterministiska och antingen exakta eller oprecisa kan inkluderas kolumner. Beräknade kolumner som härletts från bild, ntext, text, varchar(max), nvarchar(max)kan varbinary(max), och XML- datatyper inkluderas i icke-nyckelkolumner så länge datatyperna för beräknade kolumner är tillåtna som en inkluderad kolumn. Mer information finns i Index på beräknade kolumner.

Information om hur du skapar ett XML-index finns i CREATE XML INDEX.

VAR <filter_predicate>

Skapar ett filtrerat index genom att ange vilka rader som ska inkluderas i indexet. Det filtrerade indexet måste vara ett icke-grupperat index i en tabell. Skapar filtrerad statistik för dataraderna i det filtrerade indexet.

Filterpredikatet använder enkel jämförelselogik och kan inte referera till en beräknad kolumn, en UDT-kolumn, en kolumn av rumslig datatyp eller en hierarki-ID-datatypkolumn. Jämförelser med NULL literaler tillåts inte med jämförelseoperatorerna. Använd operatorerna IS NULL och IS NOT NULL i stället.

Här följer några exempel på filterpredikat för tabellen Production.BillOfMaterials:

WHERE StartDate > '20000101' AND EndDate <= '20000630'

WHERE ComponentID IN (533, 324, 753)

WHERE StartDate IN ('20000404', '20000905') AND EndDate IS NOT NULL

Filtrerade index gäller inte för XML-index och fulltextindex. För UNIKA index måste endast de markerade raderna ha unika indexvärden. Filtrerade index tillåter inte alternativet IGNORE_DUP_KEY.

PÅ partition_scheme_name ( column_name )

Anger partitionsschemat som definierar de filgrupper som partitionerna i ett partitionerat index ska mappas till. Partitionsschemat måste finnas i databasen genom att antingen köra CREATE PARTITION SCHEME eller ALTER PARTITION SCHEME. column_name anger den kolumn som ett partitionerat index ska partitioneras mot. Den här kolumnen måste matcha datatypen, längden och precisionen för argumentet för partitionsfunktionen som partition_scheme_name använder. column_name är inte begränsat till kolumnerna i indexdefinitionen. Alla kolumner i bastabellen kan anges, förutom när du partitionerar ett UNIKT index, column_name måste väljas bland dem som används som unik nyckel. Med den här begränsningen kan databasmotorn endast verifiera att nyckelvärdena är unika inom en enda partition.

Om partition_scheme_name eller filgrupp inte anges och tabellen partitioneras placeras indexet i samma partitionsschema med samma partitioneringskolumn som den underliggande tabellen.

Mer information om partitionering av index finns partitionerade tabeller och index.

PÅ filegroup_name

Skapar det angivna indexet för den angivna filgruppen. Om ingen plats har angetts och tabellen eller vyn inte är partitionerad använder indexet samma filgrupp som den underliggande tabellen eller vyn. Filgruppen måste redan finnas.

PÅ "standard"

Skapar det angivna indexet för samma filgrupps- eller partitionsschema som tabellen eller vyn.

Termen standard i den här kontexten är inte ett nyckelord. Det är en identifierare för standardfilgruppen och måste avgränsas, som i ON "default" eller ON [default]. Om "standard" anges måste alternativet QUOTED_IDENTIFIER vara PÅ för den aktuella sessionen. Det här är standardinställningen. Mer information finns i SET QUOTED_IDENTIFIER.

[ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ]

Anger placeringen av FILESTREAM-data för tabellen när ett klustrat index skapas. Med FILESTREAM_ON-satsen kan FILESTREAM-data flyttas till ett annat FILESTREAM-filgrupps- eller partitionsschema.

filestream_filegroup_name är namnet på en FILESTREAM-filgrupp. Filgruppen måste ha en fil definierad för filgruppen med hjälp av en CREATE DATABASE eller ALTER DATABASE-instruktion. annars utlöses ett fel.

Om tabellen är partitionerad måste FILESTREAM_ON-satsen inkluderas och måste ange ett partitionsschema med FILESTREAM-filgrupper som använder samma partitionsfunktion och partitionskolumner som partitionsschemat för tabellen. Annars utlöses ett fel.

Om tabellen inte är partitionerad kan kolumnen FILESTREAM inte partitioneras. FILESTREAM-data för tabellen måste lagras i en enda filgrupp som anges i FILESTREAM_ON-satsen.

FILESTREAM_ON NULL kan anges i en CREATE INDEX-instruktion om ett klustrat index skapas och tabellen inte innehåller någon FILESTREAM-kolumn.

Mer information finns i FILESTREAM (SQL Server).

<objekt>::=

Det fullständigt kvalificerade eller icke-kvalificerade objekt som ska indexeras.

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 ska indexeras.

Vyn måste definieras med SCHEMABINDING för att skapa ett index på den. Ett unikt grupperat index måste skapas i en vy innan ett icke-grupperat index skapas. Mer information om indexerade vyer finns i avsnittet Anmärkningar.

Från och med SQL Server 2016 (13.x) kan objektet vara en tabell som lagras med ett grupperat columnstore-index.

Azure SQL Database stöder namnformatet i tre delar database_name. [schema_name].object_name när database_name är den aktuella databasen eller database_name är tempdb och object_name börjar med #.

<relational_index_option>::=

Anger vilka alternativ som ska användas när du skapar indexet.

PAD_INDEX = { ON | AV }

Anger indexutfyllnad. Standardvärdet är OFF.

PÅ
Procentandelen ledigt utrymme som anges av fillfactor- tillämpas på sidorna på mellannivå i indexet.

AV- eller fillfactor- har inte angetts
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.

Alternativet PAD_INDEX är bara användbart när FILLFACTOR har angetts, eftersom PAD_INDEX använder procentandelen som anges av FILLFACTOR. Om procentandelen som angetts för FILLFACTOR inte är tillräckligt stor för att tillåta en rad åsidosätter databasmotorn internt procentandelen för att tillåta minimivärdet. Antalet rader på en mellanliggande indexsida är aldrig mindre än två, oavsett hur lågt värdet för fillfactor.

I bakåtkompatibel syntax motsvarar WITH PAD_INDEXWITH PAD_INDEX = ON.

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 återskapas. Värdet för fillfactor måste vara ett heltalsvärde från 1 till 100. Fyllningsfaktorvärdena 0 och 100 är desamma i alla avseenden. Om fillfactor- är 100 skapar databasmotorn index med lövsidor fyllda till kapacitet.

Inställningen FILLFACTOR gäller endast när indexet skapas eller återskapas. Databasmotorn behåller inte dynamiskt den angivna procentandelen tomt utrymme på sidorna.

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

Mer information finns i Ange fyllningsfaktor för 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 PÅ, oavsett vilket alternativ som anges om inte återskapande av index används.

PÅ
Mellanliggande sorteringsresultat som används för att skapa indexet lagras i tempdb. Detta kan minska tiden som krävs för att skapa ett index om tempdb finns på en annan uppsättning diskar än användardatabasen. Detta ökar dock mängden diskutrymme som används under indexversionen.

BORT
Mellanliggande sorteringsresultat lagras i samma databas som indexet.

Förutom det utrymme som krävs i användardatabasen för att skapa indexet måste tempdb ha ungefär samma mängd extra utrymme för att lagra mellanliggande sorteringsresultat. Mer information finns i SORT_IN_TEMPDB alternativ för index.

I bakåtkompatibel syntax motsvarar WITH SORT_IN_TEMPDBWITH SORT_IN_TEMPDB = ON.

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. Alternativet har ingen effekt när du kör CREATE INDEX, ALTER INDEXeller UPDATE. Standardvärdet är OFF.

PÅ
Ett varningsmeddelande visas när dubbletter av nyckelvärden infogas i ett unikt index. Endast de rader som bryter mot unikhetsbegränsningen misslyckas.

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

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

Om du vill visa IGNORE_DUP_KEYanvänder du sys.indexes.

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

STATISTICS_NORECOMPUTE = { ON | OFF}

Anger om distributionsstatistiken omberäknas. Standardvärdet är OFF.

PÅ
Inaktuell statistik omberäknas inte automatiskt.

BORT
Automatisk uppdatering av statistik är aktiverade.

Om du vill återställa automatisk uppdatering av statistik ställer du in STATISTICS_NORECOMPUTE på AV eller kör UPDATE STATISTICS utan NORECOMPUTE-satsen.

I bakåtkompatibel syntax motsvarar WITH STATISTICS_NORECOMPUTEWITH STATISTICS_NORECOMPUTE = ON.

STATISTICS_INCREMENTAL = { ON | AV }

gäller för: SQL Server (från och med SQL Server 2014 (12.x)) och Azure SQL Database

När ONär den statistik som skapas per partitionsstatistik. När OFFtas statistikträdet bort och SQL Server beräknar statistiken igen. Standardvärdet är OFF.

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

• Statistik som skapats med index som inte är partitionsjusterade med bastabellen.
• Statistik som skapats på skrivbara sekundära databaser med AlwaysOn.
• 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.

DROP_EXISTING = { ON | AV }

Är ett alternativ för att släppa och återskapa det befintliga klustrade eller icke-grupperade indexet med ändrade kolumnspecifikationer och behålla samma namn för indexet. Standardvärdet är OFF.

PÅ
Anger för att släppa och återskapa det befintliga indexet, som måste ha samma namn som parametern index_name.

BORT
Anger att det befintliga indexet inte ska släppas och återskapas. SQL Server visar ett fel om det angivna indexnamnet redan finns.

Med DROP_EXISTINGkan du ändra:

• Ett icke-grupperat radlagringsindex till ett grupperat radlagringsindex.

Med DROP_EXISTINGkan du inte ändra:

• Ett grupperat radlagringsindex till ett icke-grupperat radlagringsindex.
• Ett grupperat columnstore-index till alla typer av radlagringsindex.

I bakåtkompatibel syntax motsvarar WITH DROP_EXISTINGWITH DROP_EXISTING = ON.

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.

PÅ
Långsiktiga tabelllås hålls inte under indexåtgärdens varaktighet. Under huvudfasen av indexåtgärden lagras endast ett IS-lås (Intent Share) 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 lås (S) på källobjektet under en mycket kort tidsperiod. I slutet av åtgärden hämtas ett S-lås (delat) på källan under en kort tidsperiod om ett icke-grupperat index skapas. Ett lås för Sch-M (schemaändring) hämtas när ett klustrat index skapas eller tas bort online och när ett grupperat eller icke-grupperat index återskapas. ONLINE kan inte anges till PÅ när ett index skapas i en lokal tillfällig tabell.

BORT
Tabelllås tillämpas under indexåtgärdens varaktighet. En offlineindexåtgärd som skapar, återskapar eller släpper ett grupperat index, å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 ett delat lås (S) i tabellen. Detta förhindrar uppdateringar av den underliggande tabellen men tillåter läsåtgärder, till exempel SELECT-instruktioner.

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

Index, inklusive index i globala temporära tabeller, kan skapas 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 äldre
• Icke-grupperade kolumnlagringsindex i SQL Server 2016 (13.x)) och äldre
• 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)) och Azure SQL Database, när en tabell innehåller varchar(max) eller varbinary(max) kolumner, kan ett klustrat index som innehåller andra kolumner skapas eller återskapas med hjälp av alternativet ONLINE.
• Icke-grupperade index i en tabell med ett grupperat columnstore-index

Mer information finns i How Online Index Operations Work.

RESUMABLE = { ON | AV }

gäller för: SQL Server (från och med SQL Server 2019 (15.x)) och Azure SQL Database

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 (från och med SQL Server 2019 (15.x)) och Azure SQL Database

Anger tid (ett heltalsvärde som anges i minuter) att en återupptabar onlineindexå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 vid åtkomst till 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 (från och med SQL Server 2019 (15.x)) och Azure SQL Database

Anger om du vill optimera för att infoga konkurrens på sista sidan eller inte. Standardvärdet är OFF. Mer information finns i avsnittet sekventiella nycklar.

MAXDOP = max_degree_of_parallelism

Åsidosätter maximal grad av parallellitet konfigurationsalternativ under indexåtgärdens varaktighet. Mer information finns i Konfigurera den maximala graden av parallellitet serverkonfigurationsalternativ. Använd MAXDOP för att begränsa antalet processorer som används i en parallell plankörning. Maximalt är 64 processorer.

max_degree_of_parallelism kan vara:

1
Undertrycker parallell plangenerering.

>1
Begränsar det maximala antalet processorer som används i en parallell indexåtgärd till det angivna antalet eller färre baserat på den aktuella systemarbetsbelastningen.

0 (standard)
Använder det faktiska antalet processorer eller färre baserat på den aktuella systemarbetsbelastningen.

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

DATA_COMPRESSION

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

INGEN
Index eller angivna partitioner komprimeras inte.

RAD
Index eller angivna partitioner komprimeras med hjälp av radkomprimering.

SIDA
Index eller angivna partitioner komprimeras med hjälp av sidkomprimering.

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, till exempel: 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)
);

Anmärkningar

Instruktionen CREATE INDEX är optimerad som andra frågor. Om du vill spara på I/O-åtgärder kan frågeprocessorn välja att söka igenom ett annat index i stället för att utföra en tabellgenomsökning. Sorteringsåtgärden kan elimineras i vissa situationer. På datorer med flera processorer kan CREATE INDEX använda fler processorer för att utföra de genomsöknings- och sorteringsåtgärder som är associerade med att skapa indexet, på samma sätt som andra frågor gör. Mer information finns i Konfigurera parallella indexåtgärder.

Den CREATE INDEX åtgärden kan loggas minimalt om databasåterställningsmodellen är inställd på antingen massloggad eller enkel.

Index kan skapas i en tillfällig tabell. När tabellen tas bort eller sessionen slutar tas indexen bort.

Ett grupperat index kan byggas på en tabellvariabel när en primärnyckel skapas. När frågan är klar eller sessionen avslutas tas indexet bort.

Index stöder utökade egenskaper.

CREATE INDEX stöds inte i Microsoft Fabric.

Klustrade index

Om du skapar ett klustrade index i en tabell (heap) eller släpper och återskapar ett befintligt klustrat index måste ytterligare arbetsyta vara tillgänglig i databasen för datasortering och en tillfällig kopia av den ursprungliga tabellen eller befintliga klustrade indexdata. Mer information om klustrade index finns i Create Clustered Indexes and the SQL Server Index Architecture and Design Guide.

Icke-grupperade index

Från och med SQL Server 2016 (13.x) och i Azure SQL Database kan du skapa ett icke-grupperat index i en tabell som lagras som ett grupperat kolumnlagringsindex. Om du först skapar ett icke-grupperat index i en tabell som lagras som ett heap- eller klustrat index sparas indexet om du senare konverterar tabellen till ett grupperat kolumnlagringsindex. Det är inte heller nödvändigt att släppa det icke-illustrerade indexet när du återskapar det klustrade kolumnlagringsindexet.

Begränsningar och begränsningar:

• Alternativet FILESTREAM_ON är inte giltigt när du skapar ett icke-grupperat index i en tabell som lagras som ett grupperat kolumnlagringsindex.

Unika index

När det finns ett unikt index söker databasmotorn efter duplicerade värden varje gång data läggs till av infogningsåtgärder. Infogningsåtgärder som skulle generera duplicerade nyckelvärden återställs och databasmotorn visar ett felmeddelande. Detta gäller även om infogningsåtgärden ändrar många rader men bara orsakar en dubblett. Om ett försök görs att ange data för vilka det finns ett unikt index och IGNORE_DUP_KEY-satsen är inställd på PÅ, misslyckas endast de rader som bryter mot DET UNIKA indexet.

Partitionerade index

Partitionerade index skapas och underhålls på ett liknande sätt som partitionerade tabeller, men precis som vanliga index hanteras de som separata databasobjekt. Du kan ha ett partitionerat index i en tabell som inte är partitionerad och du kan ha ett icke-partitionerat index i en tabell som är partitionerad.

Om du skapar ett index i en partitionerad tabell och inte anger en filgrupp som indexet ska placeras på partitioneras indexet på samma sätt som den underliggande tabellen. Det beror på att index som standard placeras i samma filgrupper som deras underliggande tabeller och för en partitionerad tabell i samma partitionsschema som använder samma partitioneringskolumner. När indexet använder samma partitionsschema och partitioneringskolumn som tabellen justeras indexet med tabellen.

När du partitionerar ett icke-unikt grupperat index lägger databasmotorn som standard till alla partitioneringskolumner i listan över klustrade indexnycklar, om de inte redan har angetts.

Indexerade vyer kan skapas på partitionerade tabeller på samma sätt som index i tabeller. Mer information om partitionerade index finns i Partitionerade tabeller och index och arkitektur och designguide för SQL Server-index.

I SQL Server skapas inte statistik genom att genomsöka alla rader i tabellen när ett partitionerat index skapas eller återskapas. I stället använder frågeoptimeraren standardsamplingsalgoritmen för att generera statistik. Om du vill hämta statistik om partitionerade index genom att skanna alla rader i tabellen använder du CREATE STATISTICS eller UPDATE STATISTICS med FULLSCAN-satsen.

Filtrerade index

Ett filtrerat index är ett optimerat icke-grupperat index som passar för frågor som väljer en liten procentandel rader från en tabell. Den använder ett filterpredikat för att indexera en del av data i tabellen. Ett väldesignat filtrerat index kan förbättra frågeprestanda, minska lagringskostnaderna och minska underhållskostnaderna.

Obligatoriska SET-alternativ för filtrerade index

SET-alternativen i kolumnen Obligatoriskt värde krävs när något av följande villkor inträffar:

• Skapa ett filtrerat index.

• Åtgärden INSERT, UPDATE, DELETE eller MERGE ändrar data i ett filtrerat index.

• Det filtrerade indexet används av frågeoptimeraren för att skapa frågeplanen.

  SET-alternativ	Obligatoriskt värde	Standardservervärde	Standard OLE DB- och ODBC-värde	Standard DB-Library värde
  ANSI_NULLS	PÅ	PÅ	PÅ	BORT
  ANSI_PADDING	PÅ	PÅ	PÅ	BORT
  ANSI_WARNINGS*	PÅ	PÅ	PÅ	BORT
  ARITHABORT	PÅ	PÅ	BORT	BORT
  CONCAT_NULL_YIELDS_NULL	PÅ	PÅ	PÅ	BORT
  NUMERIC_ROUNDABORT	BORT	BORT	BORT	BORT
  QUOTED_IDENTIFIER	PÅ	PÅ	PÅ	BORT

  • Om du anger ANSI_WARNINGS till ON anges implicit ARITHABORT till PÅ när databaskompatibilitetsnivån är inställd på 90 eller högre. Om databaskompatibilitetsnivån är inställd på 80 eller tidigare måste alternativet ARITHABORT uttryckligen anges till PÅ.

Om SET-alternativen är felaktiga kan följande villkor inträffa:

• Det filtrerade indexet skapas inte.
• Databasmotorn genererar ett fel och återställer INSERT-, UPDATE-, DELETE- eller MERGE-instruktioner som ändrar data i indexet.
• Frågeoptimeraren tar inte hänsyn till indexet i körningsplanen för några Transact-SQL-instruktioner.

Mer information om filtrerade index finns i Skapa filtrerade index och arkitektur och designguide för SQL Server-index.

Rumsliga index

Information om rumsliga index finns i CREATE SPATIAL INDEX and Spatial Indexes Overview.

XML-index

Information om XML-index finns i CREATE XML INDEX and XML Indexes (SQL Server).

Indexnyckelstorlek

Den maximala storleken för en indexnyckel är 900 byte för ett klustrat index och 1 700 byte för ett icke-grupperat index. (Före SQL Database och SQL Server 2016 (13.x) var gränsen alltid 900 byte.) Index på varchar kolumner som överskrider bytegränsen kan skapas om befintliga data i kolumnerna inte överskrider gränsen när indexet skapas. Efterföljande infognings- eller uppdateringsåtgärder på kolumnerna som gör att den totala storleken blir större än gränsen misslyckas dock. Indexnyckeln för ett grupperat index får inte innehålla varchar kolumner som har befintliga data i ROW_OVERFLOW_DATA allokeringsenhet. Om ett klustrat index skapas på en varchar kolumn och befintliga data finns i IN_ROW_DATA allokeringsenhet misslyckas efterföljande infognings- eller uppdateringsåtgärder i kolumnen som skulle skicka data utanför raden.

Icke-grupperade index kan innehålla icke-nyckelkolumner i indexets lövnivå. Dessa kolumner beaktas inte av databasmotorn vid beräkning av indexnyckelns storlek . Mer information finns i Skapa index med inkluderade kolumner och arkitektur och designguide för SQL Server-index.

Beräknade kolumner

Index kan skapas på beräknade kolumner. Dessutom kan beräknade kolumner ha egenskapen PERSISTED. Det innebär att databasmotorn lagrar de beräknade värdena i tabellen och uppdaterar dem när andra kolumner som den beräknade kolumnen är beroende av uppdateras på. Databasmotorn använder dessa bevarade värden när det skapar ett index i kolumnen och när indexet refereras i en fråga.

Om du vill indexera en beräknad kolumn måste den beräknade kolumnen vara deterministisk och exakt. Men om du använder egenskapen PERSISTED expanderas typen av indexerbara beräknade kolumner till att omfatta:

• Beräknade kolumner baserade på Transact-SQL- och CLR-funktioner och CLR-användardefinierade typmetoder som markeras som deterministiska av användaren.
• Beräknade kolumner baserade på uttryck som är deterministiska enligt definitionen av databasmotorn men oprecisa.

Beständiga beräknade kolumner kräver att följande SET-alternativ anges enligt föregående avsnitt Obligatoriska UPPSÄTTNINGsalternativ för filtrerade index.

Begränsningen UNIK eller PRIMÄR NYCKEL kan innehålla en beräknad kolumn så länge den uppfyller alla villkor för indexering. Mer specifikt måste den beräknade kolumnen vara deterministisk och exakt eller deterministisk och bevarad. Mer information om determinism finns i Deterministiska och nondeterministiska funktioner.

Beräknade kolumner som härletts från bild, ntext, text, varchar(max), nvarchar(max), varbinary(max), och XML- datatyper kan indexeras antingen som en nyckel eller inkluderad icke-nyckelkolumn så länge datatypen för den beräknade kolumnen är tillåten som en indexnyckelkolumn eller icke-nyckelkolumn. Du kan till exempel inte skapa ett primärt XML-index på en beräknad xml- kolumn. Om indexnyckelns storlek överskrider 900 byte visas ett varningsmeddelande.

Om du skapar ett index i en beräknad kolumn kan det orsaka fel i en infognings- eller uppdateringsåtgärd som tidigare fungerade. Ett sådant fel kan inträffa när den beräknade kolumnen resulterar i aritmetikfel. I följande tabell fungerar insert-instruktionen, även om den beräknade kolumnen c resulterar i ett aritmetikfel.

CREATE TABLE t1 (a INT, b INT, c AS a/b);
INSERT INTO t1 VALUES (1, 0);

Om du i stället efter att ha skapat tabellen skapar ett index för den beräknade kolumnen ckommer samma INSERT-instruktion nu att misslyckas.

CREATE TABLE t1 (a INT, b INT, c AS a/b);
CREATE UNIQUE CLUSTERED INDEX Idx1 ON t1(c);
INSERT INTO t1 VALUES (1, 0);

Mer information finns i Index på beräknade kolumner.

Inkluderade kolumner i index

Icke-nyckelkolumner, som kallas inkluderade kolumner, kan läggas till på lövnivån för ett icke-grupperat index för att förbättra frågeprestandan genom att täcka frågan. Det betyder att alla kolumner som refereras i frågan ingår i indexet som antingen nyckelkolumner eller icke-nyckelkolumner. På så sätt kan frågeoptimeraren hitta all nödvändig information från en indexgenomsökning. tabell- eller klustrade indexdata används inte. Mer information finns i Skapa index med inkluderade kolumner och arkitektur och designguide för SQL Server-index.

Ange indexalternativ

SQL Server 2005 (9.x) introducerade nya indexalternativ och ändrar även hur alternativ anges. I bakåtkompatibel syntax motsvarar WITH option_nameWITH (option_name = ON). När du anger indexalternativ gäller följande regler:

• Nya indexalternativ kan bara anges med hjälp av WITH (<option_name> = <ON | OFF>).
• Alternativ kan inte anges med både bakåtkompatibel och ny syntax i samma instruktion. Om du till exempel anger WITH (DROP_EXISTING, ONLINE = ON) misslyckas instruktionen.
• När du skapar ett XML-index måste alternativen anges med hjälp av WITH (<option_name> = <ON | OFF>).

DROP_EXISTING-sats

Du kan använda satsen DROP_EXISTING för att återskapa indexet, lägga till eller släppa kolumner, ändra alternativ, ändra kolumnsorteringsordning eller ändra partitionsschemat eller filgruppen.

Om indexet tillämpar en PRIMARY KEY- eller UNIQUE-begränsning och indexdefinitionen inte ändras på något sätt, tas indexet bort och skapas på nytt, vilket bevarar den befintliga begränsningen. Men om indexdefinitionen ändras misslyckas -instruktionen. Om du vill ändra definitionen av en PRIMARY KEY- eller UNIQUE-begränsning släpper du villkoret och lägger till en begränsning med den nya definitionen.

DROP_EXISTING förbättrar prestandan när du återskapar ett klustrat index, med antingen samma eller en annan uppsättning nycklar, i en tabell som också har icke-grupperade index. DROP_EXISTING ersätter körningen av en DROP INDEX-instruktion för det gamla klustrade indexet följt av körningen av en CREATE INDEX-instruktion för det nya klustrade indexet. De icke-grupperade indexen återskapas en gång och sedan bara om indexdefinitionen har ändrats. Satsen DROP_EXISTING återskapar inte de icke-illustrerade indexen när indexdefinitionen har samma indexnamn, nyckel- och partitionskolumner, unika attribut och sorteringsordning som det ursprungliga indexet.

Oavsett om de icke-grupperade indexen återskapas eller inte finns de alltid kvar i sina ursprungliga filgrupper eller partitionsscheman och använder de ursprungliga partitionsfunktionerna. Om ett klustrat index återskapas till ett annat filgrupps- eller partitionsschema flyttas inte de icke-grupperade indexen för att sammanfalla med den nya platsen för det klustrade indexet. Även de icke-grupperade index som tidigare var justerade med det klustrade indexet kanske inte längre är i linje med det. Mer information om partitionerad indexjustering finns i Partitionerade tabeller och index.

Satsen DROP_EXISTING sorterar inte data igen om samma indexnyckelkolumner används i samma ordning och med samma stigande eller fallande ordning, såvida inte indexinstruktionen anger ett icke-grupperat index och onlinealternativet är inställt på OFF. Om det klustrade indexet är inaktiverat måste den CREATE INDEX WITH DROP_EXISTING åtgärden utföras med ONLINE inställt på AV. Om ett icke-grupperat index är inaktiverat och inte är associerat med ett inaktiverat grupperat index kan CREATE INDEX WITH DROP_EXISTING åtgärden utföras med ONLINE inställt på AV eller PÅ.

ONLINE-alternativ

Följande riktlinjer gäller för att utföra indexåtgärder online:

• Den underliggande tabellen kan inte ändras, trunkeras eller tas bort när en onlineindexåtgärd pågår.
• Ytterligare tillfälligt diskutrymme krävs under indexåtgärden.
• Onlineåtgärder kan utföras på partitionerade index och index som innehåller beständiga beräknade kolumner eller inkluderade kolumner.
• Med alternativet low_priority_lock_wait argument kan du bestämma hur indexåtgärden kan fortsätta när den blockeras i Sch-M låset.

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

Resurser

Följande resurser krävs för att återuppta åtgärden för att skapa onlineindex:

• Ytterligare utrymme krävs för att behålla indexet som skapas, inklusive den tid då index pausas
• Ytterligare loggdataflöde under sorteringsfasen. Den totala loggutrymmesanvändningen för återupptabart index är mindre jämfört med vanlig onlineindexskapande och tillåter loggtrunkering under den här åtgärden.
• Ett DDL-tillstånd som förhindrar DDL-ändringar
• Ghost-rensning blockeras i det inbyggda indexet under åtgärdens varaktighet både när åtgärden pausas och medan åtgärden körs.

Aktuella funktionsbegränsningar

Följande funktioner är inaktiverade för åtgärder för att återskapa indexskapande:

• När en återupptabar onlineindexskapandeåtgärd har pausats kan det ursprungliga värdet för MAXDOP inte ändras

• Skapa ett index som innehåller:

  • Beräknade kolumner eller TIMESTAMP-kolumner som nyckelkolumner
  • LOB-kolumn som inkluderad kolumn för att återuppta indexskapande
  • Filtrerat index

Återupptabara indexåtgärder

gäller för: SQL Server (från och med SQL Server 2019 (15.x)) och Azure SQL Database

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

• Onlineindexskapande anges som återupptabart med hjälp av alternativet RESUMABLE = ON.
• Alternativet RESUMABLE sparas inte i metadata för ett visst index och gäller endast varaktigheten för en aktuell DDL-instruktion. Därför måste RESUMABLE = ON-satsen anges uttryckligen för att möjliggöra återupptagande.
• MAX_DURATION alternativet stöds endast för RESUMABLE = ON alternativet.
• MAX_DURATION för alternativet RESUMABLE anger tidsintervallet för ett index som skapas. När den här gången används pausas indexversionen eller så slutförs körningen. Användaren bestämmer när en version 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 eller lika med en vecka (7 * 24 * 60 = 10080 minuter). Att ha en lång paus för en indexåtgärd kan påverka DML-prestanda för en specifik tabell samt databasdiskkapaciteten eftersom båda indexerar den ursprungliga och den nyligen skapade kräver diskutrymme och måste uppdateras under 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.
• Om du vill pausa indexåtgärden direkt kan du stoppa (Ctrl-C) det pågående kommandot, köra kommandot ALTER INDEX PAUSE eller köra kommandot KILL <session_id>. När kommandot har pausats kan det återupptas med kommandot ALTER INDEX.
• Om du kör den ursprungliga CREATE INDEX-instruktionen för återupptar du automatiskt en pausad indexskapandeåtgärd.
• Alternativet SORT_IN_TEMPDB = ON stöds inte för återupptabart index.
• DDL-kommandot med RESUMABLE = ON kan inte köras i en explicit transaktion (kan inte ingå i början TRAN ... COMMIT block).
• Om du vill återuppta/avbryta ett indexskapande/återskapande använder du syntaxen ALTER INDEX T-SQL.
• Inaktiverade index stöds inte.

Information om att en indexskapande körs som en återupptabar åtgärd och för att kontrollera dess aktuella körningstillstånd läser du sys.index_resumable_operations.

WAIT_AT_LOW_PRIORITY med onlineindexåtgärder

gäller för: Den här syntaxen för CREATE INDEX gäller för närvarande endast för SQL Server 2022 (16.x), Azure SQL Database och Azure SQL Managed Instance. För ALTER INDEXgäller den här syntaxen för SQL Server (från och med SQL Server 2014 (12.x)) och Azure SQL Database. Mer information finns i ALTER INDEX.

Med syntaxen low_priority_lock_wait kan du ange WAIT_AT_LOW_PRIORITY beteende. WAIT_AT_LOW_PRIORITY kan endast användas med ONLINE=ON.

Med WAIT_AT_LOW_PRIORITY alternativet kan dbas hantera de Sch-S och Sch-M lås som krävs för att skapa onlineindex och gör att de kan välja något av tre alternativ. Om det under väntetiden MAX_DURATION = n [minutes]i alla tre fall inte finns några blockerande aktiviteter, körs återskapande av onlineindex omedelbart utan att vänta och DDL-instruktionen har slutförts.

WAIT_AT_LOW_PRIORITY anger att åtgärden för att skapa onlineindex väntar på lås med låg prioritet, vilket gör att andra åtgärder kan fortsätta medan onlineindexet byggs. 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 [MINUTER]

Väntetiden (ett heltalsvärde som anges i minuter) som onlineindexet skapar lås väntar med låg prioritet när DDL-kommandot körs. 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 MINUTER kan utelämnas.

ABORT_AFTER_WAIT = [NONE | SELF | BLOCKERS } ]

NONE Fortsätt att vänta på låset med normal (vanlig) prioritet.

SELF Avsluta onlineindexet skapa DDL-åtgärden som körs för närvarande, utan att vidta några åtgärder. Alternativet SELF- kan inte användas med en MAX_DURATION på 0.

BLOCKERS Avliva alla användartransaktioner som blockerar DDL-åtgärden för onlineindexet så att åtgärden kan fortsätta. Alternativet BLOCKERS kräver att inloggningen har ALTER ANY CONNECTION behörighet.

Alternativ för rad- och sidlås

När ALLOW_ROW_LOCKS = ON och ALLOW_PAGE_LOCK = ONtillåts rad-, sid- och tabellnivålås vid åtkomst till 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å vid åtkomst till indexet.

Sekventiella nycklar

gäller för: SQL Server (från och med SQL Server 2019 (15.x)) och Azure SQL Database

Konkurrens vid infogning på sista sidan är ett vanligt prestandaproblem som uppstår när ett stort antal samtidiga trådar försöker infoga rader i ett index med en sekventiell nyckel. Ett index betraktas som sekventiellt när den inledande nyckelkolumnen innehåller värden som alltid ökar (eller minskar), till exempel en identitetskolumn eller ett datum som är standard för aktuellt datum/tid. Eftersom nycklarna som infogas är sekventiella infogas alla nya rader i slutet av indexstrukturen , med andra ord på samma sida. Detta leder till konkurrens om sidan i minnet som kan observeras som flera trådar som väntar på PAGELATCH_EX för sidan i fråga.

Om du aktiverar alternativet OPTIMIZE_FOR_SEQUENTIAL_KEY index kan du optimera databasmotorn som hjälper till att förbättra dataflödet för infogningar med hög samtidighet i indexet. Den är avsedd för index som har en sekventiell nyckel och därmed är benägna att infoga konkurrens på sista sidan, men det kan också hjälpa till med index som har hot spots i andra områden i B-Tree-indexstrukturen.

Visa indexinformation

Om du vill returnera information om index kan du använda katalogvyer, systemfunktioner och system lagrade procedurer.

Datakomprimering

Datakomprimering beskrivs i avsnittet Datakomprimering. Följande är viktiga saker att tänka på:

• 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 begränsningar gäller för 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 ... återskapar den angivna partitionen av indexet.
• Syntaxen ALTER INDEX <index> ... REBUILD WITH ... återskapar alla partitioner av indexet.

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

XML-komprimering

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

Många av samma överväganden för datakomprimering gäller XML-komprimering. Du bör också vara medveten om följande överväganden:

• När en lista över partitioner har angetts kan XML-komprimering aktiveras på enskilda partitioner. Om listan över partitioner inte har angetts är alla partitioner inställda på att använda XML-komprimering. När en tabell eller ett index skapas inaktiveras XML-datakomprimering om inget annat anges. När en tabell ändras bevaras den befintliga komprimering om inget annat anges.
• Om du anger en lista över partitioner eller en partition som ligger utom räckhåll genereras ett fel.
• När ett klustrat index skapas på en heap ärver det klustrade indexet heapens XML-komprimeringstillstånd om inte ett alternativt komprimeringsalternativ har angetts.
• Om du ändrar XML-komprimeringsinställningen för en heap måste alla icke-illustrerade index i tabellen återskapas så att de har pekare till de nya radplatserna i heapen.
• Du kan aktivera eller inaktivera XML-komprimering online eller offline. Aktivering av komprimering på en heap är en tråd för en onlineåtgärd.
• Om du vill fastställa XML-komprimeringstillståndet för partitioner i en partitionerad tabell frågar du kolumnen xml_compression i sys.partitions katalogvyn.

Behörigheter

Kräver ALTER behörighet i tabellen eller vyn eller medlemskapet i den db_ddladmin fasta databasrollen.

Begränsningar och begränsningar

I Azure Synapse Analytics and Analytics Platform System (PDW) kan du inte skapa:

• Ett grupperat eller icke-grupperat radlagringsindex i en informationslagertabell när det redan finns ett kolumnlagringsindex. Det här beteendet skiljer sig från SMP SQL Server som gör att både rowstore- och columnstore-index kan samexistera i samma tabell.
• Du kan inte skapa ett index i en vy.

Metadata

Om du vill visa information om befintliga index kan du fråga sys.indexes katalogvy.

Versionsanteckningar

SQL Database stöder inte filgrupps- och filströmsalternativ.

Exempel: Alla versioner. Använder AdventureWorks-databasen

A. Skapa ett enkelt icke-grupperat radlagringsindex

I följande exempel skapas ett icke-grupperat index i kolumnen VendorID i tabellen Purchasing.ProductVendor.

CREATE INDEX IX_VendorID ON ProductVendor (VendorID);
CREATE INDEX IX_VendorID ON dbo.ProductVendor (VendorID DESC, Name ASC, Address DESC);
CREATE INDEX IX_VendorID ON Purchasing..ProductVendor (VendorID);

B. Skapa ett enkelt sammansatt rowstore-index som inte visas

I följande exempel skapas ett icke-grupperat sammansatt index i kolumnerna SalesQuota och SalesYTD i tabellen Sales.SalesPerson.

CREATE NONCLUSTERED INDEX IX_SalesPerson_SalesQuota_SalesYTD ON Sales.SalesPerson (SalesQuota, SalesYTD);

C. Skapa ett index i en tabell i en annan databas

I följande exempel skapas ett grupperat index i kolumnen VendorID i tabellen ProductVendor i databasen Purchasing.

CREATE CLUSTERED INDEX IX_ProductVendor_VendorID ON Purchasing..ProductVendor (VendorID);

D. Lägga till en kolumn i ett index

I följande exempel skapas index IX_FF med två kolumner från dbo. FactFinance-tabell. Nästa instruktion återskapar indexet med ytterligare en kolumn och behåller det befintliga namnet.

CREATE INDEX IX_FF ON dbo.FactFinance (FinanceKey ASC, DateKey ASC);

-- Rebuild and add the OrganizationKey
CREATE INDEX IX_FF ON dbo.FactFinance (FinanceKey, DateKey, OrganizationKey DESC)
  WITH (DROP_EXISTING = ON);

Exempel: SQL Server, Azure SQL Database

E. Skapa ett unikt icke-grupperat index

I följande exempel skapas ett unikt icke-illustrerat index i kolumnen Name i Production.UnitMeasure-tabellen i AdventureWorks2022-databasen. Indexet framtvingar unikhet för data som infogas i kolumnen Name.

CREATE UNIQUE INDEX AK_UnitMeasure_Name
  ON Production.UnitMeasure(Name);

Följande fråga testar unikhetsbegränsningen genom att försöka infoga en rad med samma värde som i en befintlig rad.

-- Verify the existing value.
SELECT Name FROM Production.UnitMeasure WHERE Name = N'Ounces';
GO

INSERT INTO Production.UnitMeasure (UnitMeasureCode, Name, ModifiedDate)
  VALUES ('OC', 'Ounces', GETDATE());

Det resulterande felmeddelandet är:

Server: Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object 'UnitMeasure' with unique index 'AK_UnitMeasure_Name'. The statement has been terminated.

F. Använd alternativet IGNORE_DUP_KEY

I följande exempel visas effekten av alternativet IGNORE_DUP_KEY genom att infoga flera rader i en tillfällig tabell först med alternativet inställt på ON och igen med alternativet inställt på OFF. En enskild rad infogas i tabellen #Test som avsiktligt orsakar ett duplicerat värde när den andra instruktionen för flera rader INSERT körs. Antalet rader i tabellen returnerar antalet infogade rader.

CREATE TABLE #Test (C1 NVARCHAR(10), C2 NVARCHAR(50), C3 DATETIME);
GO

CREATE UNIQUE INDEX AK_Index ON #Test (C2)
  WITH (IGNORE_DUP_KEY = ON);
GO

INSERT INTO #Test VALUES (N'OC', N'Ounces', GETDATE());
INSERT INTO #Test SELECT * FROM Production.UnitMeasure;
GO

SELECT COUNT(*) AS [Number of rows] FROM #Test;
GO

DROP TABLE #Test;
GO

Här är resultatet av den andra INSERT-instruktionen.

Server: Msg 3604, Level 16, State 1, Line 5 Duplicate key was ignored.

Number of rows
--------------
38

Observera att de rader som infogats från tabellen Production.UnitMeasure som inte bröt mot unikhetsbegränsningen har infogats. En varning utfärdades och dubblettraden ignorerades, men hela transaktionen återställdes inte.

Samma instruktioner körs igen, men med IGNORE_DUP_KEY inställt på OFF.

CREATE TABLE #Test (C1 NVARCHAR(10), C2 NVARCHAR(50), C3 DATETIME);
GO

CREATE UNIQUE INDEX AK_Index ON #Test (C2)
  WITH (IGNORE_DUP_KEY = OFF);
GO

INSERT INTO #Test VALUES (N'OC', N'Ounces', GETDATE());
INSERT INTO #Test SELECT * FROM Production.UnitMeasure;
GO

SELECT COUNT(*) AS [Number of rows] FROM #Test;
GO

DROP TABLE #Test;
GO

Här är resultatet av den andra INSERT-instruktionen.

Server: Msg 2601, Level 14, State 1, Line 5
Cannot insert duplicate key row in object '#Test' with unique index
'AK_Index'. The statement has been terminated.

Number of rows
--------------
1

Observera att ingen av raderna från den Production.UnitMeasure tabellen infogades i tabellen trots att endast en rad i tabellen bröt mot UNIQUE indexvillkor.

G. Använda DROP_EXISTING för att släppa och återskapa ett index

I följande exempel släpps och återskapas ett befintligt index i kolumnen ProductID i tabellen Production.WorkOrder i AdventureWorks2022-databasen med hjälp av alternativet DROP_EXISTING. Alternativen FILLFACTOR och PAD_INDEX anges också.

CREATE NONCLUSTERED INDEX IX_WorkOrder_ProductID
  ON Production.WorkOrder(ProductID)
    WITH (FILLFACTOR = 80,
      PAD_INDEX = ON,
      DROP_EXISTING = ON);
GO

H. Skapa ett index i en vy

I följande exempel skapas en vy och ett index för den vyn. Två frågor ingår som använder den indexerade vyn.

-- Set the options to support indexed views
SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,
  QUOTED_IDENTIFIER, ANSI_NULLS ON;
GO

-- Create view with schemabinding
IF OBJECT_ID ('Sales.vOrders', 'view') IS NOT NULL
  DROP VIEW Sales.vOrders;
GO

CREATE VIEW Sales.vOrders
  WITH SCHEMABINDING
AS
  SELECT SUM(UnitPrice * OrderQty * (1.00 - UnitPriceDiscount)) AS Revenue,
    OrderDate, ProductID, COUNT_BIG(*) AS COUNT
  FROM Sales.SalesOrderDetail AS od, Sales.SalesOrderHeader AS o
  WHERE od.SalesOrderID = o.SalesOrderID
  GROUP BY OrderDate, ProductID;
GO

-- Create an index on the view
CREATE UNIQUE CLUSTERED INDEX IDX_V1
  ON Sales.vOrders (OrderDate, ProductID);
GO

-- This query can use the indexed view even though the view is
-- not specified in the FROM clause.
SELECT SUM(UnitPrice * OrderQty * (1.00 - UnitPriceDiscount)) AS Rev,
  OrderDate, ProductID
FROM Sales.SalesOrderDetail AS od
  JOIN Sales.SalesOrderHeader AS o ON od.SalesOrderID = o.SalesOrderID
    AND ProductID BETWEEN 700 AND 800
    AND OrderDate >= CONVERT(DATETIME, '05/01/2002', 101)
GROUP BY OrderDate, ProductID
ORDER BY Rev DESC;
GO

-- This query can use the above indexed view
SELECT OrderDate, SUM(UnitPrice * OrderQty * (1.00 - UnitPriceDiscount)) AS Rev
FROM Sales.SalesOrderDetail AS od
  JOIN Sales.SalesOrderHeader AS o ON od.SalesOrderID = o.SalesOrderID
    AND DATEPART(mm, OrderDate) = 3
  AND DATEPART(yy, OrderDate) = 2002
GROUP BY OrderDate
ORDER BY OrderDate ASC;
GO

Jag. Skapa ett index med inkluderade kolumner (icke-nyckel)

I följande exempel skapas ett icke-grupperat index med en nyckelkolumn (PostalCode) och fyra icke-nyckelkolumner (AddressLine1, AddressLine2, City, StateProvinceID). En fråga som omfattas av indexet följer. Om du vill visa det index som har valts av frågeoptimeraren går du till menyn Query i SQL Server Management Studio och väljer Visa verklig körningsplan innan du kör frågan.

CREATE NONCLUSTERED INDEX IX_Address_PostalCode
  ON Person.Address (PostalCode)
  INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID);
GO

SELECT AddressLine1, AddressLine2, City, StateProvinceID, PostalCode
FROM Person.Address
WHERE PostalCode BETWEEN N'98000' and N'99999';
GO

J. Skapa ett partitionerat index

I följande exempel skapas ett icke-grupperat partitionerat index på TransactionsPS1, ett befintligt partitionsschema i AdventureWorks2022-databasen. Det här exemplet förutsätter att det partitionerade indexexemplet har installerats.

CREATE NONCLUSTERED INDEX IX_TransactionHistory_ReferenceOrderID
  ON Production.TransactionHistory (ReferenceOrderID)
  ON TransactionsPS1 (TransactionDate);
GO

K. Skapa ett filtrerat index

I följande exempel skapas ett filtrerat index i tabellen Production.BillOfMaterials i databasen AdventureWorks2022. Filterpredikatet kan innehålla kolumner som inte är nyckelkolumner i det filtrerade indexet. Predikatet i det här exemplet väljer endast de rader där EndDate inte är NULL.

CREATE NONCLUSTERED INDEX "FIBillOfMaterialsWithEndDate"
  ON Production.BillOfMaterials (ComponentID, StartDate)
  WHERE EndDate IS NOT NULL;

L. Skapa ett komprimerat index

I följande exempel skapas ett index i en icke-partitionerad tabell med hjälp av radkomprimering.

CREATE NONCLUSTERED INDEX IX_INDEX_1
  ON T1 (C2)
  WITH (DATA_COMPRESSION = ROW);
GO

I följande exempel skapas ett index i en partitionerad tabell med hjälp av radkomprimering på alla partitioner i indexet.

CREATE CLUSTERED INDEX IX_PartTab2Col1
  ON PartitionTable1 (Col1)
  WITH (DATA_COMPRESSION = ROW);
GO

I följande exempel skapas ett index i en partitionerad tabell med hjälp av sidkomprimering på partition 1 av index- och radkomprimering på partitioner 2 via 4 av indexet.

CREATE CLUSTERED INDEX IX_PartTab2Col1
  ON PartitionTable1 (Col1)
  WITH (
    DATA_COMPRESSION = PAGE ON PARTITIONS(1),
    DATA_COMPRESSION = ROW ON PARTITIONS (2 TO 4)
  );
GO

M. Skapa 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 skapas ett index i en icke-partitionerad tabell med hjälp av XML-komprimering. Minst en kolumn i indexet måste vara xml- datatyp.

CREATE NONCLUSTERED INDEX IX_INDEX_1
  ON T1 (C2)
  WITH (XML_COMPRESSION = ON);
GO

I följande exempel skapas ett index i en partitionerad tabell med hjälp av XML-komprimering på alla partitioner i indexet.

CREATE CLUSTERED INDEX IX_PartTab2Col1
  ON PartitionTable1 (Col1)
  WITH (XML_COMPRESSION = ON);
GO

N. Skapa, återuppta, pausa och avbryta återupptabara indexåtgärder

gäller för: SQL Server (från och med SQL Server 2019 (15.x)) och Azure SQL Database

-- Execute a resumable online index create statement with MAXDOP=1
CREATE INDEX test_idx1 ON test_table (col1) WITH (ONLINE = ON, MAXDOP = 1, RESUMABLE = ON);

-- Executing the same command again (see above) after an index operation was paused, resumes automatically the index create operation.

-- Execute a resumable online index creates operation with MAX_DURATION set to 240 minutes. After the time expires, the resumable index create operation is paused.
CREATE INDEX test_idx2 ON test_table (col2) WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 240);

-- Pause a running resumable online index creation
ALTER INDEX test_idx1 ON test_table PAUSE;
ALTER INDEX test_idx2 ON test_table PAUSE;

-- Resume a paused online index creation
ALTER INDEX test_idx1 ON test_table RESUME;
ALTER INDEX test_idx2 ON test_table RESUME;

-- Abort resumable index create operation which is running or paused
ALTER INDEX test_idx1 ON test_table ABORT;
ALTER INDEX test_idx2 ON test_table ABORT;

O. SKAPA INDEX med olika låsalternativ med låg prioritet

I följande exempel används alternativet WAIT_AT_LOW_PRIORITY för att ange olika strategier för att hantera blockering.

--Kill this session after waiting 5 minutes
CREATE CLUSTERED INDEX idx_1 ON dbo.T2 (a) WITH (ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 5 MINUTES, ABORT_AFTER_WAIT = SELF)));
GO

--Kill blocker sessions
CREATE CLUSTERED INDEX idx_1 ON dbo.T2 (a) WITH (ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 5 MINUTES, ABORT_AFTER_WAIT = BLOCKERS)));
GO

I följande exempel används både alternativet RESUMABLE och två MAX_DURATION värden, det första gäller för alternativet ABORT_AFTER_WAIT, det andra gäller för alternativet RESUMABLE.

--With resumable option; default locking behavior 
CREATE CLUSTERED INDEX idx_1 ON dbo.T2 (a) WITH (ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 5 MINUTES, ABORT_AFTER_WAIT = NONE)), RESUMABLE = ON, MAX_DURATION = 240 MINUTES);

Exempel: Azure Synapse Analytics and Analytics Platform System (PDW)

P. Grundläggande syntax

Skapa, återuppta, pausa och avbryta återupptabara indexåtgärder

gäller för: SQL Server (från och med SQL Server 2019 (15.x)) och Azure SQL Database

-- Execute a resumable online index create statement with MAXDOP=1
CREATE INDEX test_idx ON test_table WITH (ONLINE = ON, MAXDOP = 1, RESUMABLE = ON);

-- Executing the same command again (see above) after an index operation was paused, resumes automatically the index create operation.

-- Execute a resumable online index creates operation with MAX_DURATION set to 240 minutes. After the time expires, the resumable index create operation is paused.
CREATE INDEX test_idx ON test_table WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 240);

-- Pause a running resumable online index creation
ALTER INDEX test_idx ON test_table PAUSE;

-- Resume a paused online index creation
ALTER INDEX test_idx ON test_table RESUME;

-- Abort resumable index create operation which is running or paused
ALTER INDEX test_idx ON test_table ABORT;

Q. Skapa ett icke-grupperat index i en tabell i den aktuella databasen

I följande exempel skapas ett icke-grupperat index i kolumnen VendorID i tabellen ProductVendor.

CREATE INDEX IX_ProductVendor_VendorID
  ON ProductVendor (VendorID);

R. Skapa ett grupperat index i en tabell i en annan databas

I följande exempel skapas ett icke-grupperat index i kolumnen VendorID i tabellen ProductVendor i databasen Purchasing.

CREATE CLUSTERED INDEX IX_ProductVendor_VendorID
  ON Purchasing..ProductVendor (VendorID);

S. Skapa ett ordnat grupperat index i en tabell

I följande exempel skapas ett ordnat grupperat index på kolumnerna c1 och c2 i tabellen T1 i MyDB-databasen.

CREATE CLUSTERED COLUMNSTORE INDEX MyOrderedCCI ON MyDB.dbo.T1 
ORDER (c1, c2);

T. Konvertera en CCI till ett ordnat grupperat index i en tabell

I följande exempel konverteras det befintliga klustrade kolumnlagringsindexet till ett ordnat grupperat kolumnlagringsindex med namnet MyOrderedCCI i kolumnerna c1 och c2 i T2-tabellen i MyDB-databasen.

CREATE CLUSTERED COLUMNSTORE INDEX MyOrderedCCI ON MyDB.dbo.T2
ORDER (c1, c2)
WITH (DROP_EXISTING = ON);

Se även

• arkitektur- och designguide för SQL Server-index
• utföra indexåtgärder online
• Index och ALTER TABLE
• ALTER INDEX
• CREATE PARTITION FUNCTION
• SKAPA PARTITIONSSCHEMA
• SKAPA SPATIAL INDEX
• SKAPA STATISTIK
• CREATE TABLE
• CREATE XML INDEX
• datatyper
• DBCC-SHOW_STATISTICS
• DROP INDEX
• XML-index (SQL Server)
• sys.indexes
• sys.index_columns
• sys.xml_index
• EVENTDATA