CREATE INDEX (Transact-SQL)

2025-04-09

van toepassing op:SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

Hiermee maakt u een relationele index voor een tabel of weergave. Ook wel een rowstore-index genoemd omdat het een geclusterde of niet-geclusterde B-tree-index is. U kunt een rowstore-index maken voordat er gegevens in de tabel staan. Gebruik een rowstore-index om de queryprestaties te verbeteren, met name wanneer de query's uit specifieke kolommen selecteren of waarden in een bepaalde volgorde moeten worden gesorteerd.

Notitie

Documentatie maakt gebruik van de term B-tree in het algemeen in verwijzing naar indexen. In rijstore-indexen implementeert de database-engine een B+-structuur. Dit geldt niet voor columnstore-indexen of indexen voor tabellen die zijn geoptimaliseerd voor geheugen. Zie de SQL Server- en Azure SQL-indexarchitectuur en ontwerphandleidingvoor meer informatie.

Azure Synapse Analytics and Analytics Platform System (PDW) biedt momenteel geen ondersteuning voor unieke beperkingen. Voorbeelden die verwijzen naar unieke beperkingen zijn alleen van toepassing op SQL Server, Azure SQL Database en Azure SQL Managed Instance.

Raadpleeg de ontwerphandleiding voor de SQL Server-index voor informatie over richtlijnen voor indexontwerp.

voorbeelden:

Een niet-geclusterde index maken in een tabel of weergave
```
CREATE INDEX index1 ON schema1.table1 (column1);
```
Een geclusterde index maken in een tabel en een driedelige naam voor de tabel gebruiken
```
CREATE CLUSTERED INDEX index1 ON database1.schema1.table1 (column1);
```

Een niet-geclusterde index maken met een unieke beperking en de sorteervolgorde opgeven

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

sleutelscenario:

Vanaf SQL Server 2016 (13.x), in Azure SQL Database en in Azure SQL Managed Instance kunt u een niet-geclusterde index in een columnstore-index gebruiken om de queryprestaties van datawarehousing te verbeteren. Zie Columnstore-indexen - datawarehouse voor meer informatie.

Zie voor aanvullende typen indexen:

Transact-SQL syntaxisconventies

Syntaxis

Syntaxis voor 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>

Achterwaarts compatibele relationele index

Belangrijk

De achterwaarts compatibele relationele indexsyntaxisstructuur wordt verwijderd in een toekomstige versie van SQL Server. Vermijd het gebruik van deze syntaxisstructuur in nieuwe ontwikkelwerkzaamheden en plan om toepassingen te wijzigen die momenteel gebruikmaken van de functie. Gebruik in plaats daarvan de syntaxisstructuur die is opgegeven in <relational_index_option>.

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
}

Syntaxis voor Azure Synapse Analytics en 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 } )
[;]

Argumenten

UNIEK

Hiermee maakt u een unieke index voor een tabel of weergave. Een unieke index is een index waarin geen twee rijen dezelfde indexsleutelwaarde mogen hebben.

De database-engine staat het maken van een unieke index niet toe voor kolommen die al dubbele waarden bevatten, ongeacht of IGNORE_DUP_KEY deze is ingesteld op ON. Als dit wordt geprobeerd, geeft de database-engine een foutbericht weer. Dubbele waarden moeten worden verwijderd voordat een unieke index kan worden gemaakt in de kolom of kolommen.

Een UNIQUE beperking wordt NULL behandeld als een waarde. Als een kolom nullable is en er een UNIQUE beperking bestaat in de kolom, is maximaal één rij met een NULL toegestaan.

GECLUSTERDE

Hiermee maakt u een index waarin de sorteervolgorde die is opgegeven voor de indexsleutelkolommen de paginavolgorde in de indexstructuur op schijf bepaalt. Rijen op de pagina's onderaan of blad, niveau van de geclusterde index bevatten altijd alle kolommen van de tabel. Rijen op de pagina's in de bovenste niveaus van de index bevatten alleen sleutelkolommen.

Een tabel kan slechts één geclusterde index hebben. Als er een geclusterde index in een tabel bestaat, bevat deze alle gegevens in de tabel. Een tabel zonder een geclusterde index wordt een heap genoemd.

Een weergave met een unieke geclusterde index wordt een geïndexeerde weergave genoemd. Een geïndexeerde weergave kan slechts één geclusterde index hebben. Als u een unieke geclusterde index maakt voor een weergave, wordt de weergave fysiek gerealiseerd. Er moet een unieke geclusterde index worden gemaakt in een weergave voordat andere indexen in dezelfde weergave kunnen worden gedefinieerd. Voor meer informatie, zie Geïndexeerde weergaven maken.

Maak de geclusterde index voordat u niet-geclusterde indexen maakt. Bestaande niet-geclusterde indexen in tabellen worden opnieuw opgebouwd wanneer een geclusterde index wordt gemaakt. Dit is een resource-intensieve bewerking als de tabel groot is.

Als CLUSTERED niet is opgegeven, wordt er een niet-geclusterde index gemaakt.

Notitie

Omdat de geclusterde index alle gegevens in de tabel bevat, wordt door het maken van een geclusterde index de tabel effectief verplaatst van ON partition_scheme_nameON filegroup_name de bestandsgroep waarop de tabel is gemaakt naar het nieuwe partitieschema of de bestandsgroep. Voordat u tabellen of indexen maakt voor specifieke bestandsgroepen, controleert u welke bestandsgroepen beschikbaar zijn en of er voldoende lege ruimte is voor de index.

In sommige gevallen kan het maken van een geclusterde index eerder uitgeschakelde indexen inschakelen. Zie Indexen en beperkingen inschakelen en indexen en beperkingen uitschakelen voor meer informatie.

NIET-GECLUSTERD

Hiermee maakt u een index waarin de sorteervolgorde die is opgegeven voor de indexsleutelkolommen de paginavolgorde in de indexstructuur op schijf bepaalt. In tegenstelling tot de geclusterde index bevatten rijen op de pagina's in het bladniveau van een niet-geclusterde index alleen de indexsleutelkolommen. U kunt eventueel een subset van niet-sleutelkolommen opnemen met behulp van de INCLUDE component.

Elke tabel kan maximaal 999 niet-geclusterde indexen bevatten, ongeacht hoe de indexen worden gemaakt: impliciet met de PRIMARY KEY beperkingen of UNIQUE expliciet met CREATE INDEX.

Voor geïndexeerde weergaven kunnen niet-geclusterde indexen alleen worden gemaakt in een weergave met een unieke geclusterde index die al is gedefinieerd.

Als dit niet anders is opgegeven, is het standaardindextype niet geclusterd.

index_name

De naam van de index. Indexnamen moeten uniek zijn binnen een tabel of weergave, maar hoeven niet uniek te zijn binnen een database. Indexnamen moeten voldoen aan de regels van id's.

kolom

De kolom of kolommen waarop de index is gebaseerd. Geef twee of meer kolomnamen op om een samengestelde index te maken voor de gecombineerde waarden in de opgegeven kolommen. Geef de kolommen weer die moeten worden opgenomen in de samengestelde index, in sorteerprioriteitsvolgorde, tussen de haakjes na table_or_view_name.

Maximaal 32 kolommen kunnen worden gecombineerd tot één samengestelde indexsleutel. Alle kolommen in een samengestelde indexsleutel moeten zich in dezelfde tabel of weergave bevinden. De maximale toegestane grootte van de gecombineerde indexwaarden is 900 bytes voor een geclusterde index of 1700 voor een niet-geclusterde index. De limieten zijn 16 kolommen en 900 bytes voor versies vóór SQL Database en SQL Server 2016 (13.x).

Kolommen die van de gegevenstypen groot object (LOB) zijn ntext, tekst, varchar(max), nvarchar(max), varbinary(max), xml-of afbeelding kunnen niet worden opgegeven als sleutelkolommen voor een index. Een geïndexeerde weergavedefinitie kan ook geen ntekst-, tekst- of afbeeldingskolommen bevatten, zelfs niet als er niet naar wordt verwezen in de CREATE INDEX instructie.

U kunt indexen maken op door de gebruiker gedefinieerde CLR-typekolommen als het type binaire volgorde ondersteunt. U kunt ook indexen maken voor berekende kolommen die zijn gedefinieerd als methodeaanroepen van een door de gebruiker gedefinieerde kolom, zolang de methoden zijn gemarkeerd als deterministisch en geen bewerkingen voor gegevenstoegang uitvoeren. Zie CLR-door de gebruiker gedefinieerde typen voor meer informatie over het indexeren van door de gebruiker gedefinieerde CLR-typen.

[ ASC | DESC ]

Bepaalt de aflopende of aflopende sorteerrichting voor de specifieke indexkolom. De standaardwaarde is ASC.

INCLUDE (kolom [ ,... n ] )

Hiermee geeft u de niet-sleutelkolommen op die moeten worden toegevoegd aan het bladniveau van een niet-geclusterde index. De niet-geclusterde index kan uniek of niet-uniek zijn.

Kolomnamen kunnen niet worden herhaald in de INCLUDE lijst en kunnen niet tegelijkertijd worden gebruikt als zowel sleutel- als niet-sleutelkolommen. Niet-geclusterde indexen bevatten altijd impliciet de geclusterde indexkolommen als een geclusterde index is gedefinieerd in de tabel. Zie Indexen maken met opgenomen kolommen voor meer informatie.

Alle gegevenstypen zijn toegestaan, behalve tekst, ntexten afbeelding. Vanaf SQL Server 2012 (11.x), in Azure SQL Database en in Azure SQL Managed Instance, als een van de opgegeven niet-sleutelkolommen varchar(max), nvarchar(max)- of varbinary(max) -gegevenstypen zijn, kan de index worden gebouwd of opnieuw opgebouwd met behulp van de ONLINE optie.

Berekende kolommen die deterministisch zijn en die nauwkeurig of onnauwkeurig zijn, kunnen kolommen worden opgenomen. Berekende kolommen die zijn afgeleid van afbeelding, ntext, tekst, varchar(max), nvarchar(max), varbinary(max), en XML-gegevenstypen kunnen worden opgenomen zolang het berekende kolomgegevenstype is toegestaan als een opgenomen kolom. Zie Indexen voor berekende kolommenvoor meer informatie.

Zie CREATE XML INDEXvoor meer informatie over het maken van een XML-index.

WHERE <filter_predicate>

Hiermee maakt u een gefilterde index door op te geven welke rijen in de index moeten worden opgenomen. De gefilterde index moet een niet-geclusterde index in een tabel zijn. Hiermee maakt u gefilterde statistieken voor de gegevensrijen in de gefilterde index.

Het filterpredicaat maakt gebruik van eenvoudige vergelijkingslogica en kan niet verwijzen naar een berekende kolom, een door de gebruiker gedefinieerde gegevenstypekolom (UDT), een kolom voor het ruimtelijke gegevenstype of een kolom met een hiërarchie-id . Vergelijkingen met NULL letterlijke gegevens met behulp van de vergelijkingsoperatoren zijn niet toegestaan. Gebruik in plaats daarvan de operators IS NULL en IS NOT NULL.

Hier volgen enkele voorbeelden van filterpredicaten voor de Production.BillOfMaterials tabel:

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

WHERE ComponentID IN (533, 324, 753)

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

Gefilterde indexen zijn niet van toepassing op XML-indexen en indexen in volledige tekst. Voor UNIQUE indexen moeten alleen de geselecteerde rijen unieke indexwaarden hebben. Gefilterde indexen staan de optie IGNORE_DUP_KEY niet toe.

ON partition_scheme_name ( column_name )

Hiermee geeft u het partitieschema op waarmee de bestandsgroepen worden gedefinieerd waarop de partities van een gepartitioneerde index worden toegewezen. Het partitieschema moet bestaan in de database door PARTITIESCHEMA maken of ALTER PARTITION SCHEMEuit te voeren. column_name geeft de partitioneringskolom voor de index op. Deze kolom moet overeenkomen met het gegevenstype, de lengte en de precisie van het argument van de partitiefunctie die partition_scheme_name gebruikt. column_name is niet beperkt tot de kolommen in de indexdefinitie. Elke kolom in de basistabel kan worden opgegeven, behalve wanneer u een unieke index partitioneert, column_name moet worden gekozen uit de kolommen die worden gebruikt als de unieke sleutel. Met deze beperking kan de database-engine alleen de uniekheid van sleutelwaarden binnen één partitie verifiëren.

Notitie

Wanneer u een niet-unieke, geclusterde index partitioneert, voegt de database-engine standaard de partitioneringskolom toe aan de lijst met geclusterde indexsleutels, als deze nog niet is opgegeven. Bij het partitioneren van een niet-unieke, niet-geclusterde index voegt de database-engine de partitioneringskolom toe als een niet-sleutelkolom (opgenomen) van de index, als deze nog niet is opgegeven.

Als partition_scheme_name of bestandsgroep niet is opgegeven en de tabel is gepartitioneerd, wordt de index in hetzelfde partitieschema geplaatst met dezelfde partitiekolom als de onderliggende tabel.

Notitie

U kunt geen partitioneringsschema opgeven voor een XML-index. Als de basistabel is gepartitioneerd, gebruikt de XML-index hetzelfde partitieschema als de tabel.

Voor meer informatie over partitioneringsindexen, gepartitioneerde tabellen en indexen.

OP filegroup_name

Hiermee maakt u de opgegeven index voor de opgegeven bestandsgroep. Als er geen locatie is opgegeven en de tabel of weergave niet is gepartitioneerd, gebruikt de index dezelfde bestandsgroep als de onderliggende tabel of weergave. De bestandsgroep moet al bestaan.

ON [standaard]

Hiermee maakt u de opgegeven index voor dezelfde bestandsgroep of hetzelfde partitieschema als de tabel of weergave.

De term defaultis in deze context geen trefwoord. Het is een id voor de bestandsgroep of het gepartitioneerde schema van de tabel of weergave en moet worden gescheiden, zoals in ON "default" of ON [default]. Als "default" dit is opgegeven, moet de QUOTED_IDENTIFIER optie voor de huidige sessie zijn ON . Dit is de standaardinstelling. Zie SET QUOTED_IDENTIFIERvoor meer informatie.

Notitie

Geef in de context van CREATE INDEXen [default]"default" niet de standaardbestandsgroep van de database aan. Ze geven de bestandsgroep of het partitieschema aan dat wordt gebruikt door de basistabel of -weergave. Dit verschilt van CREATE TABLE, waar "default" en [default] plaats de tabel op de standaardbestandsgroep van de database.

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

Hiermee geeft u de plaatsing van FILESTREAM-gegevens voor de tabel op wanneer een geclusterde index wordt gemaakt. Met de component FILESTREAM_ON kunnen FILESTREAM-gegevens worden verplaatst naar een andere FILESTREAM-bestandsgroep of partitieschema.

De filestream_filegroup_name is de naam van een FILESTREAM-bestandsgroep. De bestandsgroep moet één bestand hebben gedefinieerd voor de bestandsgroep met behulp van een instructie CREATE DATABASE of ALTER DATABASE; anders wordt er een fout gegenereerd.

Als de tabel is gepartitioneerd, moet de FILESTREAM_ON component worden opgenomen en moet u een partitieschema opgeven van FILESTREAM-bestandsgroepen die gebruikmaken van dezelfde partitiefunctie en partitiekolommen als het partitieschema voor de tabel. Anders wordt er een fout gegenereerd.

Als de tabel niet is gepartitioneerd, kan de FILESTREAM-kolom niet worden gepartitioneerd. FILESTREAM-gegevens voor de tabel moeten worden opgeslagen in één bestandsgroep die is opgegeven in de FILESTREAM_ON-component.

FILESTREAM_ON NULL kan worden opgegeven in een CREATE INDEX instructie als er een geclusterde index wordt gemaakt en de tabel geen FILESTREAM-kolom bevat.

Zie FILESTREAM (SQL Server)voor meer informatie.

<object>::=

Het volledig gekwalificeerde of niet-gekwalificeerde object dat moet worden geïndexeerd.

database_name

De naam van de database.

schema_name

De naam van het schema waartoe de tabel of weergave behoort.

table_or_view_name

De naam van de tabel of weergave die moet worden geïndexeerd.

Als u een index voor een weergave wilt maken, moet de weergave worden gedefinieerd met SCHEMABINDING. Er moet een unieke geclusterde index worden gemaakt in een weergave voordat een niet-geclusterde index wordt gemaakt. Zie Opmerkingen voor meer informatie over geïndexeerde weergaven.

Vanaf SQL Server 2016 (13.x) kan het object een tabel zijn die is opgeslagen met een geclusterde columnstore-index.

Azure SQL Database ondersteunt de driedelige naamindeling <database_name>.<schema_name>.<object_name> wanneer <database_name> de naam van de huidige database is of <database_name>tempdb is en <object_name> begint met # of ##. Als de schemanaam dbois, kan <schema_name> worden weggelaten.

<relational_index_option>::=

Hiermee geeft u de opties op die moeten worden gebruikt bij het maken van de index.

PAD_INDEX = { ON | UIT }

Hiermee geeft u indexopvulling. De standaardwaarde is OFF.

OP

Het percentage vrije ruimte dat is opgegeven door vulfactor, wordt toegepast op de pagina's op tussenliggend niveau van de index. Als FILLFACTOR niet tegelijkertijd is opgegeven PAD_INDEX is ingesteld op ON, wordt de waarde van de vulfactor in sys.indexes gebruikt.
AF

De pagina's op tussenliggend niveau zijn gevuld met bijna-capaciteit, waardoor er voldoende ruimte is voor ten minste één rij met de maximale grootte van de index, rekening houdend met de set sleutels op de tussenliggende pagina's. Dit treedt ook op als PAD_INDEX is ingesteld op ON, maar de vulfactor niet is opgegeven.

De PAD_INDEX optie is alleen nuttig wanneer FILLFACTOR wordt opgegeven, omdat PAD_INDEX het percentage wordt gebruikt dat is opgegeven door FILLFACTOR. Als het opgegeven FILLFACTOR percentage niet groot genoeg is om één rij toe te staan, overschrijft de database-engine intern het percentage om het minimum toe te staan. Het aantal rijen op een tussenliggende indexpagina is nooit minder dan twee, ongeacht hoe laag de waarde is FILLFACTOR.

In achterwaarts compatibele syntaxis is WITH PAD_INDEX gelijk aan WITH PAD_INDEX = ON.

FILLFACTOR = fillfactor

Hiermee geeft u een percentage op dat aangeeft hoe vol de database-engine het bladniveau van elke indexpagina moet maken tijdens het maken of opnieuw opbouwen van de index. De waarde van de fillfactor moet een geheel getal tussen 1 en 100 zijn. Vulfactorwaarden 0 en 100 zijn in alle opzichten hetzelfde. Als fillfactor 100 is, maakt de database-engine indexen met bladpagina's die zijn gevuld met capaciteit.

De instelling FILLFACTOR is alleen van toepassing wanneer de index wordt gemaakt of opnieuw wordt opgebouwd. De database-engine behoudt niet dynamisch het opgegeven percentage lege ruimte op de pagina's.

Als u de instelling voor de opvulfactor wilt weergeven, gebruikt u de fill_factor kolom in de catalogusweergave sys.indexes .

Belangrijk

Als u een index maakt met een FILLFACTOR kleiner dan 100, neemt de hoeveelheid opslagruimte die de gegevens in beslag nemen toe, omdat de database-engine de gegevens herdistribueert volgens de vulfactor bij het maken of herbouwen van een index.

Zie Vulfactor voor een index opgeven voor meer informatie.

SORT_IN_TEMPDB = { ON | UIT }

Hiermee geeft u op of tijdelijke sorteerresultaten moeten worden opgeslagen in tempdb. De standaardwaarde is OFF, met uitzondering van Azure SQL Database Hyperscale. Voor alle indexbuildbewerkingen in Hyperscale wordt SORT_IN_TEMPDB altijd ON tenzij een hervatbare indexbuild wordt gebruikt. Voor hervatbare index builds wordt SORT_IN_TEMPDB altijd OFF.

OP

De tussenliggende sorteerresultaten die worden gebruikt om de index te bouwen, worden opgeslagen in tempdb. Dit kan de tijd verkorten die nodig is om een index te maken. Dit verhoogt echter de hoeveelheid schijfruimte die wordt gebruikt tijdens de indexbuild.
AF

De tussenliggende sorteerresultaten worden opgeslagen in dezelfde database als de index.

Naast de ruimte die nodig is in de gebruikersdatabase om de index te maken, tempdb moet er ongeveer dezelfde hoeveelheid extra ruimte zijn om de tussenliggende sorteerresultaten te bewaren. Zie SORT_IN_TEMPDB optie voor indexenvoor meer informatie.

In achterwaarts compatibele syntaxis is WITH SORT_IN_TEMPDB gelijk aan WITH SORT_IN_TEMPDB = ON.

IGNORE_DUP_KEY = { ON | UIT }

Hiermee geeft u het foutbericht op wanneer een invoegbewerking probeert dubbele sleutelwaarden in een unieke index in te voegen. De optie IGNORE_DUP_KEY is alleen van toepassing op het invoegen van bewerkingen nadat de index is gemaakt of opnieuw is opgebouwd. De optie heeft geen effect bij het uitvoeren van CREATE INDEX, ALTER INDEXof UPDATE. De standaardwaarde is OFF.

OP

Er treedt een waarschuwingsbericht op wanneer dubbele sleutelwaarden worden ingevoegd in een unieke index. Alleen de rijen die de uniekheidsbeperking schenden, worden niet ingevoegd.
AF

Er treedt een foutbericht op wanneer dubbele sleutelwaarden worden ingevoegd in een unieke index. De hele INSERT instructie wordt teruggedraaid.

IGNORE_DUP_KEY kan niet worden ingesteld op ON voor indexen die zijn gemaakt in een weergave, niet-unieke indexen, XML-indexen, ruimtelijke indexen en gefilterde indexen.

Als u de IGNORE_DUP_KEY-instelling voor een index wilt weergeven, gebruikt u de kolom ignore_dup_key in de sys.indexes catalogusweergave.

In achterwaarts compatibele syntaxis is WITH IGNORE_DUP_KEY gelijk aan WITH IGNORE_DUP_KEY = ON.

STATISTICS_NORECOMPUTE = { AAN | UIT}

Hiermee geeft u op of statistieken opnieuw worden berekend. De standaardwaarde is OFF.

OP

Verouderde statistieken worden niet automatisch opnieuw berekend.
AF

Automatisch bijwerken van statistieken is ingeschakeld.

Als u automatische updates van statistieken wilt herstellen, stelt u de STATISTICS_NORECOMPUTE in op UIT of voert u UPDATE STATISTICS uit zonder de NORECOMPUTE-component.

Waarschuwing

Als u automatische hercomputatie van statistieken uitschakelt door STATISTICS_NORECOMPUTE = ONin te stellen, kunt u voorkomen dat de queryoptimalisatie optimale uitvoeringsplannen kiest voor query's die betrekking hebben op de tabel.

Als u STATISTICS_NORECOMPUTE instelt op ON wordt het bijwerken van indexstatistieken die optreden tijdens de herbouwbewerking van de index niet voorkomen.

In achterwaarts compatibele syntaxis is WITH STATISTICS_NORECOMPUTE gelijk aan WITH STATISTICS_NORECOMPUTE = ON.

STATISTICS_INCREMENTAL = { ON | UIT }

van toepassing op: SQL Server 2014 (12.x) en latere versies, Azure SQL Database en Azure SQL Managed Instance

Wanneer ONde gemaakte statistieken per partitiestatistieken zijn. Wanneer OFF, wordt de structuur statistieken verwijderd en sql Server berekent de statistieken opnieuw. De standaardwaarde is OFF.

Als statistieken per partitie niet worden ondersteund, wordt de optie genegeerd en wordt er een waarschuwing gegenereerd. Incrementele statistieken worden in de volgende gevallen niet ondersteund:

Statistieken die zijn gemaakt met indexen die niet zijn gepartitioneerd met de basistabel.
Statistieken die zijn gemaakt in leesbare secundaire databases met AlwaysOn.
Statistieken die zijn gemaakt voor alleen-lezendatabases.
Statistieken die zijn gemaakt voor gefilterde indexen.
Statistieken die zijn gemaakt in weergaven.
Statistieken die zijn gemaakt op interne tabellen.
Statistieken die zijn gemaakt met ruimtelijke indexen of XML-indexen.

DROP_EXISTING = { ON | UIT }

Is een optie om de bestaande geclusterde of niet-geclusterde index met gewijzigde kolomspecificaties te verwijderen en opnieuw te bouwen en dezelfde naam voor de index te behouden. De standaardwaarde is OFF.

OP

Hiermee geeft u op om de bestaande index te verwijderen en opnieuw te bouwen, die dezelfde naam moet hebben als de parameter index_name.
AF

Hiermee geeft u niet op om de bestaande index te verwijderen en opnieuw te bouwen. SQL Server geeft een fout weer als de opgegeven indexnaam al bestaat.

Met DROP_EXISTINGkunt u het volgende wijzigen:

Een niet-geclusterde rowstore-index naar een geclusterde rowstore-index.

Met DROP_EXISTINGkunt u het volgende niet wijzigen:

Een geclusterde rowstore-index naar een niet-geclusterde rowstore-index.
Een geclusterde columnstore-index voor elk type rowstore-index.

In achterwaarts compatibele syntaxis is WITH DROP_EXISTING gelijk aan WITH DROP_EXISTING = ON.

ONLINE = { ON | UIT }

Hiermee geeft u op of onderliggende tabellen en bijbehorende indexen beschikbaar zijn voor query's en gegevenswijziging tijdens de indexbewerking. De standaardwaarde is OFF.

Belangrijk

Online indexbewerkingen zijn niet beschikbaar in elke editie van Microsoft SQL Server. Zie -edities en ondersteunde functies van SQL Server 2022voor een lijst met functies die worden ondersteund door de edities van SQL Server.

OP

Langetermijntabelvergrendelingen worden niet bewaard voor de duur van de indexbewerking. Tijdens de hoofdfase van de indexbewerking wordt alleen een intentievergrendeling (IS) op de brontabel bewaard. Hierdoor kunnen query's of updates voor de onderliggende tabel en indexen worden voortgezet. Aan het begin van de bewerking wordt een gedeelde vergrendeling (S) gedurende een korte periode op het bronobject bewaard. Aan het einde van de bewerking wordt gedurende een korte periode een gedeelde (S) vergrendeling op het object verkregen als er een niet-geclusterde index wordt gemaakt. Een schemawijzigingsvergrendeling (Sch-M) wordt verkregen wanneer een geclusterde index online wordt gemaakt of verwijderd en wanneer een geclusterde of niet-geclusterde index opnieuw wordt opgebouwd. ONLINE kan niet worden ingesteld op ON wanneer een index wordt gemaakt in een lokale tijdelijke tabel.

Notitie

U kunt de optie WAIT_AT_LOW_PRIORITY gebruiken om tijdens online indexbewerkingen blokkeren of blokkeren te voorkomen. Zie WAIT_AT_LOW_PRIORITY met online indexbewerkingenvoor meer informatie.
AF

Tabelvergrendelingen worden toegepast voor de duur van de indexbewerking. Een offline indexbewerking waarmee een geclusterde, ruimtelijke of XML-index wordt gemaakt, herbouwd of verwijderd of een niet-geclusterde index wordt gemaakt, opnieuw wordt opgebouwd of verwijderd, wordt een schemawijziging (Sch-M) op de tabel verkregen. Hiermee voorkomt u dat alle gebruikers toegang hebben tot de onderliggende tabel voor de duur van de bewerking. Een offline indexbewerking waarmee een niet-geclusterde index wordt gemaakt, wordt in eerste instantie een gedeelde (S) vergrendeling voor de tabel verkregen. Hiermee voorkomt u dat de onderliggende tabeldefinitie wordt gewijzigd, maar de gegevens in de tabel kunnen worden gelezen en gewijzigd terwijl de indexbuild wordt uitgevoerd.

Zie Indexbewerkingen online uitvoeren en Richtlijnen voor online indexbewerkingenvoor meer informatie.

Indexen, inclusief indexen voor globale tijdelijke tabellen, kunnen online worden gemaakt, met uitzondering van de volgende gevallen:

XML-index
Indexeren op een lokale tijdelijke tabel
Initiële unieke geclusterde index in een weergave
Uitgeschakelde geclusterde indexen
Geclusterde columnstore-indexen in SQL Server 2017 (14.x)) en oudere versies
Niet-geclusterde columnstore-indexen in SQL Server 2016 (13.x)) en oudere versies
Geclusterde index, als de onderliggende tabel LOB-gegevenstypen bevat (afbeelding, ntext, tekst) en ruimtelijke gegevenstypen
varchar(max) en varbinary(max) kolommen kunnen geen deel uitmaken van een indexsleutel. In SQL Server (te beginnen met SQL Server 2012 (11.x)), in Azure SQL Database en in Azure SQL Managed Instance, wanneer een tabel varchar(max) of varbinary(max) kolommen bevat, kan een geclusterde index met andere kolommen worden gemaakt of opnieuw opgebouwd met behulp van de ONLINE optie.
Niet-geclusterde indexen in een tabel met een geclusterde columnstore-index

Zie Hoe online indexbewerkingen werkenvoor meer informatie.

HERVATBAAR = { AAN | UIT }

van toepassing op: SQL Server 2019 (15.x) en latere versies, Azure SQL Database en Azure SQL Managed Instance

Hiermee geeft u op of een online indexbewerking hervat kan worden. Zie Hervatbare indexbewerkingen en overwegingen voor hervatbare indexen voor meer informatie.

OP

De indexbewerking kan worden hervat.
AF

De indexbewerking kan niet worden hervat.

MAX_DURATION = tijd [MINUTEN] gebruikt met `RESUMABLE = ON` (hiervoor is `ONLINE = ON`vereist)

van toepassing op: SQL Server 2019 (15.x) en latere versies, Azure SQL Database en Azure SQL Managed Instance

Hiermee geeft u op hoe lang, in minuten, een hervatbare indexbewerking wordt uitgevoerd voordat deze wordt onderbroken.

ALLOW_ROW_LOCKS = { AAN | UIT }

Hiermee geeft u op of rijvergrendelingen zijn toegestaan. De standaardwaarde is ON.

OP

Rijvergrendelingen zijn toegestaan bij toegang tot de index. De database-engine bepaalt wanneer rijvergrendelingen worden gebruikt.
AF

Rijvergrendelingen worden niet gebruikt.

ALLOW_PAGE_LOCKS = { AAN | UIT }

Hiermee geeft u op of paginavergrendelingen zijn toegestaan. De standaardwaarde is ON.

OP

Paginavergrendelingen zijn toegestaan bij toegang tot de index. De database-engine bepaalt wanneer paginavergrendelingen worden gebruikt.
AF

Paginavergrendelingen worden niet gebruikt.

OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | UIT }

van toepassing op: SQL Server 2019 (15.x) en latere versies, Azure SQL Database en Azure SQL Managed Instance

Hiermee geeft u op of u wilt optimaliseren om conflicten met laatste pagina's te voorkomen. De standaardwaarde is OFF. Zie de sectie Sequentiële sleutels voor meer informatie.

MAXDOP = max_degree_of_parallelism

Overschrijft de maximale mate van parallelle uitvoering configuratieoptie voor de indexbewerking. Zie De maximale mate van parallelle configuratie van server configurerenvoor meer informatie. Gebruik MAXDOP om de mate van parallelle uitvoering en het resulterende resourceverbruik voor een indexbuildbewerking te beperken.

max_degree_of_parallelism kan het volgende zijn:

1

Onderdrukt het genereren van parallelle plannen.
>1

Hiermee beperkt u de maximale mate van parallelle uitvoering die wordt gebruikt in een parallelle indexbewerking tot het opgegeven getal of minder op basis van de huidige systeemworkload.
0 (standaard)

Maakt gebruik van de mate van parallelle uitvoering die is opgegeven op server-, database- of workloadgroepsniveau, tenzij verminderd op basis van de huidige systeemworkload.

Zie Parallelle indexbewerkingen configurerenvoor meer informatie.

Notitie

Parallelle indexbewerkingen zijn niet beschikbaar in elke editie van Microsoft SQL Server. Zie -edities en ondersteunde functies van SQL Server 2022voor een lijst met functies die worden ondersteund door de edities van SQL Server.

DATA_COMPRESSION

Hiermee geeft u de optie voor gegevenscompressie voor de opgegeven index, partitienummer of bereik van partities. De opties zijn als volgt:

GEEN

Indexen of opgegeven partities worden niet gecomprimeerd. Dit geldt niet voor columnstore-indexen.
ROEIEN

Indexen of opgegeven partities worden gecomprimeerd met behulp van rijcompressie. Dit geldt niet voor columnstore-indexen.
BLADZIJDE

Index- of opgegeven partities worden gecomprimeerd met behulp van paginacompressie. Dit geldt niet voor columnstore-indexen.
COLUMNSTORE

van toepassing op: SQL Server 2014 (12.x) en latere versies, Azure SQL Database en Azure SQL Managed Instance

Is alleen van toepassing op columnstore-indexen, inclusief niet-geclusterde columnstore- en geclusterde columnstore-indexen.
COLUMNSTORE_ARCHIVE

van toepassing op: SQL Server 2014 (12.x) en latere versies, Azure SQL Database en Azure SQL Managed Instance

Is alleen van toepassing op columnstore-indexen, inclusief niet-geclusterde columnstore- en geclusterde columnstore-indexen. COLUMNSTORE_ARCHIVE de opgegeven partitie verder comprimeert naar een kleinere grootte. Dit kan worden gebruikt voor archivering, of voor andere situaties die een kleinere opslagruimte vereisen en meer tijd voor opslag en ophalen kunnen veroorloven.

Zie Gegevenscompressievoor meer informatie over compressie.

XML_COMPRESSION

van toepassing op: SQL Server 2022 (16.x) en latere versies, Azure SQL Database en Azure SQL Managed Instance

Hiermee geeft u de XML-compressieoptie voor de opgegeven index die een of meer xml- kolommen van het gegevenstype bevat. De opties zijn als volgt:

OP

Indexen of opgegeven partities worden gecomprimeerd met behulp van XML-compressie.
AF

Index- of opgegeven partities worden niet gecomprimeerd met XML-compressie.

ON PARTITIONS ( { <partition_number_expression> | <bereik> } [ ,...n ] )

Hiermee geeft u de partities waarop de DATA_COMPRESSION of XML_COMPRESSION instellingen van toepassing zijn. Als de index niet is gepartitioneerd, genereert het argument ON PARTITIONS een fout. Als de ON PARTITIONS component niet is opgegeven, is de optie DATA_COMPRESSION of XML_COMPRESSION van toepassing op alle partities van een gepartitioneerde index.

<partition_number_expression> kunt u op de volgende manieren opgeven:

Geef het getal op voor een partitie, bijvoorbeeld: ON PARTITIONS (2).
Geef de partitienummers op voor verschillende afzonderlijke partities, gescheiden door komma's, bijvoorbeeld: ON PARTITIONS (1, 5).
Geef zowel bereiken als afzonderlijke partities op, bijvoorbeeld: ON PARTITIONS (2, 4, 6 TO 8).

<range> kan worden opgegeven als partitienummers gescheiden door het trefwoord TO, bijvoorbeeld: ON PARTITIONS (6 TO 8).

Als u verschillende typen gegevenscompressie voor verschillende partities wilt instellen, geeft u de optie DATA_COMPRESSION meerdere keren op, bijvoorbeeld:

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)
);

U kunt ook de optie XML_COMPRESSION meerdere keren opgeven, bijvoorbeeld:

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)
);

Opmerkingen

Bij het maken van het queryplan voor de CREATE INDEX instructie kan de queryoptimalisatie ervoor kiezen om een andere index te scannen in plaats van een tabelscan uit te voeren. De sorteerbewerking kan in sommige situaties worden geëlimineerd. Op computers met meerdere processors CREATE INDEX kan parallellisme worden gebruikt voor de scan- en sorteerbewerkingen die zijn gekoppeld aan het maken van de index, op dezelfde manier als andere query's. Zie Parallelle indexbewerkingen configurerenvoor meer informatie.

De CREATE INDEX bewerking kan minimaal worden vastgelegd als het databaseherstelmodel is ingesteld op bulksgewijs geregistreerd of eenvoudig.

Indexen kunnen worden gemaakt in een tijdelijke tabel. Wanneer de tabel wordt verwijderd of buiten het bereik valt, worden de indexen verwijderd.

Een geclusterde index is gebaseerd op een tabelvariabele wanneer een primaire-sleutelbeperking wordt toegevoegd. Op dezelfde manier wordt een niet-geclusterde index gebouwd op een tabelvariabele wanneer er een unieke beperking wordt toegevoegd. Wanneer de tabelvariabele buiten het bereik valt, worden de indexen verwijderd.

Indexen ondersteunen uitgebreide eigenschappen.

CREATE INDEX wordt niet ondersteund in Microsoft Fabric.

Geclusterde indexen

Voor het maken van een geclusterde index in een tabel (heap) of het verwijderen en opnieuw maken van een bestaande geclusterde index moet er extra werkruimte beschikbaar zijn in de database voor het sorteren van gegevens en een tijdelijke kopie van de oorspronkelijke tabel of bestaande geclusterde indexgegevens. Zie De architectuur en ontwerphandleiding voor sql Server-indexen maken voor meer informatie over geclusterde indexen.

Niet-geclusterde indexen

Vanaf SQL Server 2016 (13.x), in Azure SQL Database en in Azure SQL Managed Instance kunt u een niet-geclusterde index maken voor een tabel die is opgeslagen als een geclusterde columnstore-index. Als u eerst een niet-geclusterde index maakt voor een tabel die is opgeslagen als een heap- of geclusterde index, blijft de index behouden als u de tabel later converteert naar een geclusterde columnstore-index. Het is ook niet nodig om de niet-geclusterde index te verwijderen wanneer u de geclusterde columnstore-index opnieuw opbouwt.

De optie FILESTREAM_ON is niet geldig wanneer u een niet-geclusterde index maakt voor een tabel die is opgeslagen als een geclusterde columnstore-index.

Unieke indexen

Wanneer er een unieke index bestaat, controleert de database-engine op dubbele waarden telkens wanneer gegevens worden toegevoegd of gewijzigd. Bewerkingen die dubbele sleutelwaarden genereren, worden teruggedraaid en de database-engine retourneert een foutbericht. Dit geldt zelfs als de bewerking voor het toevoegen of wijzigen van gegevens veel rijen wijzigt, maar slechts één duplicaat veroorzaakt. Als er een poging wordt gedaan om rijen in te voegen wanneer er een unieke index is met de IGNORE_DUP_KEY optie ingesteld ONop, worden de rijen die de unieke index schenden genegeerd.

Gepartitioneerde indexen

Gepartitioneerde indexen worden op een vergelijkbare manier gemaakt en onderhouden als gepartitioneerde tabellen, maar net als gewone indexen worden ze verwerkt als afzonderlijke databaseobjecten. U kunt een gepartitioneerde index hebben voor een tabel die niet is gepartitioneerd en u kunt een niet-gepartitioneerde index hebben voor een tabel die is gepartitioneerd.

Als u een index maakt in een gepartitioneerde tabel en geen bestandsgroep opgeeft waarop de index moet worden geplaatst, wordt de index op dezelfde manier gepartitioneerd als de onderliggende tabel. Dit komt doordat indexen standaard worden geplaatst op dezelfde bestandsgroepen als de onderliggende tabellen, en voor een gepartitioneerde tabel in hetzelfde partitieschema dat gebruikmaakt van dezelfde partitioneringskolommen. Wanneer de index hetzelfde partitieschema en dezelfde partitiekolom als de tabel gebruikt, wordt de index uitgelijnd met de tabel.

Waarschuwing

Het maken en herbouwen van niet-uitgelijnde indexen in een tabel met meer dan 1000 partities is mogelijk, maar wordt niet ondersteund. Dit kan leiden tot verminderde prestaties of overmatig geheugenverbruik tijdens deze bewerkingen. U wordt aangeraden alleen uitgelijnde indexen te gebruiken wanneer het aantal partities groter is dan 1000.

Bij het partitioneren van een niet-unieke, geclusterde index voegt de database-engine standaard eventuele partitioneringskolommen toe aan de lijst met geclusterde indexsleutels, als deze nog niet zijn opgegeven.

Geïndexeerde weergaven kunnen op dezelfde manier worden gemaakt op gepartitioneerde tabellen als indexen in tabellen. Zie Gepartitioneerde tabellen en indexen en de architectuur en ontwerphandleiding voor SQL Server-indexen voor meer informatie over gepartitioneerde indexen.

Wanneer een index wordt gemaakt of opnieuw wordt opgebouwd, worden met de query statistieken voor de index geoptimaliseerd. Voor een gepartitioneerde index gebruikt de queryoptimalisatie het standaardsamplingsalgoritmen in plaats van alle rijen in de tabel te scannen op een niet-gepartitioneerde index. Als u statistieken over gepartitioneerde indexen wilt verkrijgen door alle rijen in de tabel te scannen, gebruikt u CREATE STATISTICS of UPDATE STATISTICS met de FULLSCAN-component.

Gefilterde indexen

Een gefilterde index is een geoptimaliseerde niet-geclusterde index die geschikt is voor query's die een klein percentage rijen uit een tabel selecteren. Er wordt een filterpredicaat gebruikt om een deel van de gegevens in de tabel te indexeren. Een goed ontworpen gefilterde index kan de queryprestaties verbeteren, de opslagkosten verlagen en de onderhoudskosten verlagen.

Vereiste SET-opties voor gefilterde indexen

De SET opties in de kolom Vereiste waarde zijn vereist wanneer een van de volgende voorwaarden voorkomt:

U maakt een gefilterde index.
Een INSERT, UPDATEof DELETEMERGE instructie wijzigt gegevens in een gefilterde index.

De gefilterde index wordt door de queryoptimalisatie gebruikt om het queryplan te produceren.

`SET` optie	Vereiste waarde	Standaardserverwaarde	Standaard-OLE DB- en ODBC-waarde	Standaardwaarde voor DB-Library
`ANSI_NULLS`	`ON`	`ON`	`ON`	`OFF`
`ANSI_PADDING`	`ON`	`ON`	`ON`	`OFF`
`ANSI_WARNINGS` ¹	`ON`	`ON`	`ON`	`OFF`
`ARITHABORT`	`ON`	`ON`	`OFF`	`OFF`
`CONCAT_NULL_YIELDS_NULL`	`ON`	`ON`	`ON`	`OFF`
`NUMERIC_ROUNDABORT`	`OFF`	`OFF`	`OFF`	`OFF`
`QUOTED_IDENTIFIER`	`ON`	`ON`	`ON`	`OFF`

¹ Instelling ANSI_WARNINGS om impliciet in te ON stellen ARITHABORTON wanneer het compatibiliteitsniveau van de database is ingesteld op 90 of hoger. Als het compatibiliteitsniveau van de database is ingesteld op 80 of eerder, moet de ARITHABORT optie expliciet worden ingesteld op ON.

Als de SET opties onjuist zijn, kunnen de volgende voorwaarden optreden:

Het maken van de gefilterde index mislukt.
De database-engine genereert een fout en rolt de INSERTinstructie , UPDATEof MERGEDELETEde instructie terug waarmee gegevens in de index worden gewijzigd.
Query optimizer beschouwt de index in het uitvoeringsplan niet voor Transact-SQL instructies.

Zie De architectuur en ontwerphandleiding voor sql Server-indexen maken voor meer informatie over gefilterde indexen.

Ruimtelijke indexen

Zie het overzicht VAN CREATE SPATIAL INDEX en Spatial indexes voor meer informatie over ruimtelijke indexen.

XML-indexen

Zie CREATE XML INDEX en XML Indexes (SQL Server)voor meer informatie over XML-indexen.

Grootte van indexsleutel

De maximale grootte voor een indexsleutel is 900 bytes voor een geclusterde index en 1700 bytes voor een niet-geclusterde index. (Vóór SQL Database en SQL Server 2016 (13.x) was de limiet altijd 900 bytes.) Indexen voor varchar-kolommen die de bytelimiet overschrijden, kunnen worden gemaakt als de bestaande gegevens in de kolommen de limiet niet overschrijden op het moment dat de index wordt gemaakt; Latere invoeg- of updatebewerkingen voor de kolommen die ervoor zorgen dat de totale grootte groter is dan de limiet, mislukt. De indexsleutel van een geclusterde index kan geen varchar- kolommen bevatten die bestaande gegevens bevatten in de ROW_OVERFLOW_DATA toewijzingseenheid. Als een geclusterde index wordt gemaakt in een varchar-kolom en de bestaande gegevens zich in de IN_ROW_DATA toewijzingseenheid bevinden, worden volgende invoeg- of updatebewerkingen in de kolom die de gegevens buiten rij pushen, mislukt.

Niet-geclusterde indexen kunnen niet-sleutelkolommen (opgenomen) bevatten in het bladniveau van de index. Deze kolommen worden niet meegenomen door de database-engine bij het berekenen van de grootte van de indexsleutel. Zie Indexen maken met opgenomen kolommen en de sql Server-indexarchitectuur en ontwerphandleiding voor meer informatie.

Notitie

Wanneer tabellen worden gepartitioneerd, worden ze door de database-engine toegevoegd aan de index als de partitiesleutelkolommen nog niet aanwezig zijn in een niet-unieke geclusterde index. De gecombineerde grootte van de geïndexeerde kolommen (waarbij geen opgenomen kolommen worden geteld), plus toegevoegde partitioneringskolommen mogen niet groter zijn dan 1800 bytes in een niet-unieke geclusterde index.

Berekende kolommen

Indexen kunnen worden gemaakt op berekende kolommen. Daarnaast kunnen berekende kolommen de eigenschap PERSISTEDhebben. Dit betekent dat de database-engine de berekende waarden in de tabel opslaat en bijwerken wanneer andere kolommen waarvan de berekende kolom afhankelijk is, worden bijgewerkt. De database-engine gebruikt deze persistente waarden wanneer er een index in de kolom wordt gemaakt en wanneer naar de index wordt verwezen in een query.

Als u een berekende kolom wilt indexeren, moet de berekende kolom deterministisch en nauwkeurig zijn. Als u de PERSISTED eigenschap gebruikt, wordt het type geïndexeerde berekende kolommen echter uitgebreid met:

Berekende kolommen op basis van Transact-SQL- en CLR-functies en door de gebruiker gedefinieerde CLR-typemethoden die door de gebruiker zijn gemarkeerd als deterministisch.
Berekende kolommen op basis van expressies die deterministisch zijn zoals gedefinieerd door de database-engine, maar onnauwkeurig.

Voor persistente berekende kolommen moeten de volgende SET opties worden ingesteld, zoals wordt weergegeven in de vorige sectie Vereiste SET-opties voor gefilterde indexen.

De UNIQUE of PRIMARY KEY beperking kan een berekende kolom bevatten zolang deze voldoet aan alle voorwaarden voor indexering. De berekende kolom moet met name deterministisch en nauwkeurig of deterministisch zijn en behouden blijven. Zie Deterministische en niet-deterministische functiesvoor meer informatie over determinisme.

Berekende kolommen die zijn afgeleid van afbeelding, ntext, tekst, varchar(max), nvarchar(max), varbinary(max)en xml- gegevenstypen kunnen worden geïndexeerd als een sleutel of niet-sleutelkolom zolang het berekende kolomgegevenstype is toegestaan als een indexsleutelkolom of niet-sleutelkolom. U kunt bijvoorbeeld geen primaire XML-index maken voor een berekende xml- kolom. Als de indexsleutel groter is dan 900 bytes, wordt er een waarschuwingsbericht weergegeven.

Het maken van een index op een berekende kolom kan de fout veroorzaken van een invoeg- of bijwerkbewerking die eerder werkte. Een dergelijke fout kan optreden wanneer de berekende kolom resulteert in een rekenkundige fout.

In de volgende tabel, hoewel de expressie van de berekende kolom c lijkt te resulteren in een rekenkundige fout wanneer de rij wordt ingevoegd, werkt de INSERT instructie.

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

Als u echter een index maakt voor een berekende kolom c, mislukt dezelfde INSERT instructie.

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);

Zie Indexen voor berekende kolommenvoor meer informatie.

Opgenomen kolommen in indexen

Niet-sleutelkolommen, ook wel opgenomen kolommen genoemd, kunnen worden toegevoegd aan het bladniveau van een niet-geclusterde index om de queryprestaties te verbeteren door de query te behandelen. Dat wil gezegd: alle kolommen waarnaar in de query wordt verwezen, worden opgenomen in de index als sleutel- of niet-sleutelkolommen. Hierdoor kan de queryoptimalisatie alle vereiste informatie verkrijgen van een niet-geclusterde indexscan of zoekopdracht; de tabel- of geclusterde indexgegevens zijn niet toegankelijk. Zie Indexen maken met opgenomen kolommen en de sql Server-indexarchitectuur en ontwerphandleiding voor meer informatie.

Indexopties opgeven

SQL Server 2005 (9.x) heeft nieuwe indexopties geïntroduceerd en ook de manier gewijzigd waarop opties worden opgegeven. In de achterwaarts compatibele syntaxis is WITH option_name gelijk aan WITH (option_name = ON). Wanneer u indexopties instelt, zijn de volgende regels van toepassing:

Nieuwe indexopties kunnen alleen worden opgegeven met behulp van WITH (<option_name> = <ON | OFF>).
Opties kunnen niet worden opgegeven met behulp van zowel de achterwaarts compatibele als de nieuwe syntaxis in dezelfde instructie. Als u bijvoorbeeld WITH (DROP_EXISTING, ONLINE = ON) opgeeft, mislukt de instructie.
Wanneer u een XML-index maakt, moeten de opties worden opgegeven met behulp van WITH (<option_name> = <ON | OFF>).

DROP_EXISTING-component

U kunt de component DROP_EXISTING gebruiken om de index opnieuw op te bouwen, kolommen toe te voegen of neer te zetten, opties te wijzigen, de sorteervolgorde van kolommen te wijzigen of het partitieschema of de bestandsgroep te wijzigen.

Als de index een PRIMARY KEY of UNIQUE meer beperkingen afdwingt en de indexdefinitie op geen enkele manier wordt gewijzigd, wordt de index verwijderd en opnieuw gemaakt met behoud van de bestaande beperking. Als de indexdefinitie echter wordt gewijzigd, mislukt de instructie. Als u de definitie van een PRIMARY KEY of UNIQUE beperking wilt wijzigen, zet u de beperking neer en voegt u een beperking toe met de nieuwe definitie.

DROP_EXISTING verbetert de prestaties wanneer u een geclusterde index opnieuw maakt, met dezelfde of een andere set sleutels, in een tabel met niet-geclusterde indexen. DROP_EXISTING vervangt de uitvoering van een DROP INDEX-instructie op de oude geclusterde index, gevolgd door de uitvoering van een CREATE INDEX-instructie voor de nieuwe geclusterde index. De niet-geclusterde indexen worden eenmaal opnieuw opgebouwd en alleen als de indexdefinitie is gewijzigd. Met de component DROP_EXISTING worden de niet-geclusterde indexen niet opnieuw opgebouwd wanneer de indexdefinitie dezelfde indexnaam, sleutel- en partitiekolommen, uniekheidskenmerk en sorteervolgorde heeft als de oorspronkelijke index.

Ongeacht of de niet-geclusterde indexen opnieuw worden opgebouwd of niet, ze blijven altijd in hun oorspronkelijke bestandsgroepen of partitieschema's staan en gebruiken de oorspronkelijke partitiefuncties. Als een geclusterde index opnieuw wordt opgebouwd naar een andere bestandsgroep of een ander partitieschema, worden de niet-geclusterde indexen niet verplaatst om samen te vallen met de nieuwe locatie van de geclusterde index. Dus zelfs als de niet-geclusterde indexen eerder zijn uitgelijnd met de geclusterde index, kunnen ze er niet meer mee worden uitgelijnd. Zie Gepartitioneerde tabellen en indexen voor meer informatie over gepartitioneerde indexuitlijning.

De DROP_EXISTING component sorteert de gegevens niet opnieuw als dezelfde indexsleutelkolommen worden gebruikt in dezelfde volgorde en met dezelfde oplopende of aflopende volgorde, tenzij de indexinstructie een niet-geclusterde index opgeeft en de ONLINE optie is ingesteld op OFF. Als de geclusterde index is uitgeschakeld, moet de CREATE INDEX WITH DROP_EXISTING bewerking worden uitgevoerd met ONLINE ingesteld op OFF. Als een niet-geclusterde index is uitgeschakeld en niet is gekoppeld aan een uitgeschakelde geclusterde index, kan de CREATE INDEX WITH DROP_EXISTING bewerking worden uitgevoerd met ONLINE ingesteld op OFF of ON.

Notitie

Wanneer indexen met 128 gebieden of meer worden verwijderd of opnieuw opgebouwd, worden de werkelijke pagina-deallocaties en de bijbehorende vergrendelingen uitgesteld totdat de transactiedoorvoeringen zijn doorgevoerd. Zie Deferred deallocationvoor meer informatie.

ONLINE-optie

De volgende richtlijnen zijn van toepassing op het online uitvoeren van indexbewerkingen:

De onderliggende tabel kan niet worden gewijzigd, afgekapt of verwijderd terwijl een online indexbewerking wordt uitgevoerd.
Er is extra tijdelijke schijfruimte vereist tijdens de indexbewerking.
Onlinebewerkingen kunnen worden uitgevoerd op gepartitioneerde indexen en indexen die persistente berekende kolommen of opgenomen kolommen bevatten.
Met de WAIT_AT_LOW_PRIORITY argumentoptie kunt u bepalen hoe de indexbewerking verloopt wanneer er wordt gewacht op een Sch-M vergrendeling. Zie WAIT_AT_LOW_PRIORITY voor meer informatie

Zie Indexbewerkingen online uitvoerenvoor meer informatie.

Hervatbare indexbewerkingen

van toepassing op: SQL Server 2019 (15.x) en latere versies, Azure SQL Database en Azure SQL Managed Instance

U kunt een online indexbewerking maken die kan worden hervat. Dit betekent dat de indexbuild kan worden gestopt en later opnieuw kan worden gestart vanaf het punt waar deze is gestopt. Als u een indexbuild wilt uitvoeren als hervatbaar, geeft u de RESUMABLE = ON optie op.

De volgende richtlijnen zijn van toepassing op hervatbare indexbewerkingen:

Als u de optie RESUMABLE wilt gebruiken, moet u ook de optie ONLINE gebruiken.
De RESUMABLE optie wordt niet bewaard in de metagegevens voor een bepaalde index en is alleen van toepassing op de duur van de huidige DDL-instructie. Daarom moet de RESUMABLE = ON-component expliciet worden opgegeven om hervatting mogelijk te maken.
De optie MAX_DURATION kan in twee contexten worden opgegeven:
- MAX_DURATION voor de RESUMABLE optie geeft u het tijdsinterval op voor een index die opnieuw wordt opgebouwd. Na deze tijd is verstreken en als de index opnieuw wordt opgebouwd, wordt deze onderbroken. U bepaalt wanneer de herbouw van een onderbroken index kan worden hervat. De tijd in minuten voor MAX_DURATION moet groter zijn dan 0 minuten en kleiner dan of gelijk aan één week (7 * 24 * 60 = 10080 minuten). Een lange pauze in een indexbewerking kan merkbaar van invloed zijn op de DML-prestaties op een specifieke tabel en de capaciteit van de databaseschijf, omdat zowel de oorspronkelijke index als de zojuist gemaakte index schijfruimte vereisen en moeten worden bijgewerkt door DML-bewerkingen. Als MAX_DURATION optie wordt weggelaten, wordt de indexbewerking voortgezet totdat deze is voltooid of totdat er een fout optreedt.
- MAX_DURATION voor de optie WAIT_AT_LOW_PRIORITY geeft de tijd aan waarop moet worden gewacht met vergrendelingen met lage prioriteit als de indexbewerking wordt geblokkeerd, voordat u actie onderneemt. Zie WAIT_AT_LOW_PRIORITY met online indexbewerkingenvoor meer informatie.
Als u de indexbewerking onmiddellijk wilt onderbreken, kunt u de opdracht ALTER INDEX PAUSE uitvoeren of de opdracht KILL <session_id> uitvoeren.
Als u de oorspronkelijke CREATE INDEX instructie opnieuw uitvoert met dezelfde parameters, wordt een onderbroken indexbuildbewerking hervat. U kunt ook een onderbroken indexbuildbewerking hervatten door de ALTER INDEX RESUME instructie uit te voeren.
Met de opdracht ABORT wordt de sessie met een indexbuild beëindigd en wordt de indexbewerking geannuleerd. U kunt een indexbewerking die is afgebroken niet hervatten.

Een hervatbare indexbewerking wordt uitgevoerd totdat deze is voltooid, onderbreekt of mislukt. Als de bewerking wordt onderbroken, wordt er een fout weergegeven die aangeeft dat de bewerking is onderbroken en dat het maken van de index niet is voltooid. Als de bewerking mislukt, wordt er ook een fout uitgegeven.

Als u wilt zien of een indexbewerking wordt uitgevoerd als een hervatbare bewerking en om de huidige uitvoeringsstatus te controleren, gebruikt u de sys.index_resumable_operations catalogusweergave.

Weg

De volgende resources zijn vereist voor hervatbare indexbewerkingen:

Extra ruimte die nodig is om de index te laten bouwen, inclusief de tijd waarop de build wordt onderbroken.
Extra logboekdoorvoer tijdens de sorteerfase. Het totale gebruik van logboekruimte voor hervatbare indexen is minder dan het normale online index maken en maakt het afkappen van logboeken tijdens deze bewerking mogelijk.
DDL-instructies die proberen de tabel te wijzigen die is gekoppeld aan de index die wordt gemaakt terwijl de indexbewerking is onderbroken, zijn niet toegestaan.
Ghost-opschoning wordt geblokkeerd voor de in-build-index voor de duur van de bewerking, zowel tijdens onderbroken als terwijl de bewerking wordt uitgevoerd.
Als de tabel LOB-kolommen bevat, is voor een hervatbare geclusterde indexbuild een schemawijziging (Sch-M) vereist aan het begin van de bewerking.

Huidige functionele beperkingen

Hervattingsbewerkingen voor het maken van indexen hebben de volgende beperkingen:

Nadat een hervatbare online index maken is onderbroken, kan de initiële waarde niet MAXDOP worden gewijzigd.
De optie SORT_IN_TEMPDB = ON wordt niet ondersteund voor hervatbare indexbewerkingen.
De DDL-opdracht met RESUMABLE = ON kan niet worden uitgevoerd binnen een expliciete transactie.
U kunt geen hervatbare index maken die het volgende bevat:
- Berekende of timestamp (rowversion) kolom(en) als sleutelkolommen.
- LOB-kolom als een opgenomen kolom.
Hervatbare indexbewerkingen worden niet ondersteund voor:
- De opdracht ALTER INDEX REBUILD ALL
- De opdracht ALTER TABLE REBUILD
- Columnstore-indexen
- Gefilterde indexen
- Uitgeschakelde indexen

WAIT_AT_LOW_PRIORITY met online indexbewerkingen

van toepassing op: SQL Server 2022 (16.x) en latere versies, Azure SQL Database en Azure SQL Managed Instance

Wanneer u de WAIT_AT_LOW_PRIORITY optie niet gebruikt, moeten alle actieve blokkerende transacties die vergrendelingen in de tabel of index bevatten, worden voltooid voordat de bewerking voor het maken van de index wordt gestart en voltooid. Wanneer de online indexbewerking wordt gestart en voordat deze is voltooid, moet deze een gedeelde (S) of een schemawijziging (Sch-M) in de tabel verkrijgen en deze gedurende korte tijd bewaren. Hoewel de vergrendeling slechts korte tijd wordt bewaard, kan dit van invloed zijn op de doorvoer van de werkbelasting, de querylatentie verhogen of time-outs voor de uitvoering veroorzaken.

Om deze problemen te voorkomen, kunt u met de WAIT_AT_LOW_PRIORITY optie het gedrag van S of Sch-M vergrendelingen beheren die nodig zijn om een online indexbewerking te starten en te voltooien, waarbij u uit drie opties kunt kiezen. In alle gevallen, als er tijdens de wachttijd die is opgegeven door MAX_DURATION = n [minutes] er geen blokkering is die betrekking heeft op de indexbewerking, wordt de indexbewerking onmiddellijk uitgevoerd.

WAIT_AT_LOW_PRIORITY maakt de online indexbewerking wachten met vergrendelingen met lage prioriteit, zodat andere bewerkingen met normale prioriteitsvergrendelingen ondertussen kunnen doorgaan. Het weglaten van de optie WAIT_AT_LOW_PRIORITY is gelijk aan WAIT_AT_LOW_PRIORITY (MAX_DURATION = 0 minutes, ABORT_AFTER_WAIT = NONE).

MAX_DURATION = tijd [MINUTES]

De wachttijd (een geheel getal dat is opgegeven in minuten) die de onlineindexbewerking wacht met vergrendelingen met lage prioriteit. Als de bewerking wordt geblokkeerd voor de MAX_DURATION tijd, wordt de opgegeven ABORT_AFTER_WAIT actie uitgevoerd. MAX_DURATION tijd is altijd in minuten en het woord MINUTES kan worden weggelaten.

ABORT_AFTER_WAIT = [NONE | SELF | BLOCKERS ]

NONE: Blijf wachten op de vergrendeling met de normale prioriteit.
SELF: Sluit de onlineindexbewerking die momenteel wordt uitgevoerd, zonder actie te ondernemen. De optie SELF kan niet worden gebruikt wanneer MAX_DURATION 0 is.
BLOCKERS: alle gebruikerstransacties beëindigen die de onlineindexbewerking blokkeren, zodat de bewerking kan worden voortgezet. Voor de optie BLOCKERS moet de principal de CREATE INDEX- of ALTER INDEX-instructie uitvoeren om de machtiging ALTER ANY CONNECTION te hebben.

U kunt de volgende uitgebreide gebeurtenissen gebruiken om indexbewerkingen te bewaken die wachten op vergrendelingen met een lage prioriteit:

lock_request_priority_state
process_killed_by_abort_blockers
ddl_with_wait_at_low_priority

Opties voor rij- en paginavergrendelingen

Wanneer ALLOW_ROW_LOCKS = ON en ALLOW_PAGE_LOCK = ON, vergrendelingen op rij-, pagina- en tabelniveau zijn toegestaan bij het openen van de index. De database-engine kiest de juiste vergrendeling en kan de vergrendeling van een rij- of paginavergrendeling escaleren naar een tabelvergrendeling.

Wanneer ALLOW_ROW_LOCKS = OFF en ALLOW_PAGE_LOCK = OFF, is alleen een vergrendeling op tabelniveau toegestaan bij het openen van de index.

Waarschuwing

Het is niet raadzaam om rij- of paginavergrendelingen op een index uit te schakelen. Gelijktijdigheidsproblemen kunnen optreden en bepaalde functionaliteit is mogelijk niet beschikbaar. Een index kan bijvoorbeeld niet opnieuw worden geordend wanneer ALLOW_PAGE_LOCKS is ingesteld op OFF.

Sequentiële sleutels

Van toepassing op: SQL Server 2019 (15.x) en latere versies, in Azure SQL Database en in Azure SQL Managed Instance.

Conflicten tussen het invoegen van laatste pagina's zijn een veelvoorkomend prestatieprobleem dat optreedt wanneer een groot aantal gelijktijdige threads probeert rijen in te voegen in een index met een sequentiële sleutel. Een index wordt beschouwd als opeenvolgend wanneer de voorloopsleutelkolom waarden bevat die altijd toenemen (of afnemen), zoals een identiteitskolom of een datum die standaard is ingesteld op de huidige datum/tijd. Omdat de sleutels die worden ingevoegd opeenvolgend zijn, worden alle nieuwe rijen aan het einde van de indexstructuur ingevoegd, met andere woorden, op dezelfde pagina. Dit leidt tot conflicten voor de pagina in het geheugen die kan worden waargenomen als verschillende threads die wachten om een vergrendeling voor de betreffende pagina te verkrijgen. Het bijbehorende wachttype is PAGELATCH_EX.

Als u de optie OPTIMIZE_FOR_SEQUENTIAL_KEY index inschakelt, wordt een optimalisatie binnen de database-engine ingeschakeld waarmee de doorvoer voor invoegingen met hoge gelijktijdigheid in de index kan worden verbeterd. Het is bedoeld voor indexen die een sequentiële sleutel hebben en dus gevoelig zijn voor conflicten bij het invoegen van laatste pagina's, maar het kan ook helpen bij indexen met hot spots in andere gebieden van de B-Tree-indexstructuur.

Notitie

Gegevenscompressie

Zie Gegevenscompressie voor meer informatie over gegevenscompressie.

Hier volgen de belangrijkste aandachtspunten in de context van indexbuildbewerkingen wanneer gegevenscompressie wordt gebruikt:

Met compressie kunnen meer rijen op een pagina worden opgeslagen, maar wordt de maximale rijgrootte niet gewijzigd.
Niet-bladpagina's van een index zijn niet gecomprimeerde pagina's, maar kunnen wel worden gecomprimeerd.
Elke niet-geclusterde index heeft een afzonderlijke compressie-instelling en neemt de compressie-instelling van de onderliggende tabel niet over.
Wanneer een geclusterde index wordt gemaakt op een heap, neemt de geclusterde index de compressiestatus van de heap over, tenzij een alternatieve compressiestatus is opgegeven.

Als u wilt evalueren hoe het wijzigen van de compressiestatus van invloed is op het ruimtegebruik door een tabel, een index of een partitie, gebruikt u de sp_estimate_data_compression_savings opgeslagen procedure.

XML-compressie

van toepassing op: SQL Server 2022 (16.x) en latere versies, Azure SQL Database en Azure SQL Managed Instance.

Veel van de overwegingen voor gegevenscompressie zijn van toepassing op XML-compressie. Houd ook rekening met de volgende overwegingen:

Wanneer een lijst met partities is opgegeven, kan XML-compressie worden ingeschakeld voor afzonderlijke partities. Als de lijst met partities niet is opgegeven, worden alle partities ingesteld om XML-compressie te gebruiken. Wanneer een tabel of index wordt gemaakt, wordt XML-gegevenscompressie uitgeschakeld, tenzij anders opgegeven. Wanneer een tabel wordt gewijzigd, blijft de bestaande compressie behouden, tenzij anders is opgegeven.
Als u een lijst met partities of een partitie opgeeft die buiten het bereik valt, wordt er een fout gegenereerd.
Wanneer een geclusterde index wordt gemaakt op een heap, neemt de geclusterde index de XML-compressiestatus van de heap over, tenzij een alternatieve compressieoptie is opgegeven.
Als u de INSTELLING voor XML-compressie van een heap wijzigt, moeten alle niet-geclusterde indexen in de tabel opnieuw worden opgebouwd, zodat ze verwijzen naar de nieuwe rijlocaties in de heap.
U kunt XML-compressie online of offline in- of uitschakelen. Het inschakelen van compressie op een heap is één thread voor een online bewerking.
Als u de XML-compressiestatus van partities in een gepartitioneerde tabel wilt bepalen, gebruikt u de xml_compression kolom van de sys.partitions catalogusweergave.

Indexstatistieken

Wanneer een rowstore-index wordt gemaakt, maakt de database-engine ook statistieken over de belangrijkste kolommen van de index. De naam van het statistiekenobject in de catalogusweergave sys.stats komt overeen met de naam van de index. Voor een niet-gepartitioneerde index worden de statistieken gemaakt met behulp van een volledige scan van de gegevens. Voor een gepartitioneerde index worden statistieken gebouwd met behulp van het standaardsamplingsalgoritmen.

Wanneer een columnstore-index wordt gemaakt, maakt de database-engine ook een statistiekenobject in sys.stats . Dit statistiekenobject bevat geen statistiekengegevens, zoals het histogram en de dichtheidsvector. Deze wordt gebruikt bij het maken van een databaseklonen door de database te scripten. Op dat moment worden de DBCC SHOW_STATISTICS en UPDATE STATISTICS ... WITH STATS_STREAM opdrachten gebruikt voor het verkrijgen van columnstore-metagegevens, zoals segment, woordenlijst en deltaopslaggrootte en toevoegen aan de statistieken in de columnstore-index. Deze metagegevens worden dynamisch verkregen tijdens het compileren van query's voor een reguliere database, maar worden geleverd door het statistiekenobject voor een database-kloon. De opdracht UPDATE STATISTICS wordt niet ondersteund voor het statistiekenobject in een columnstore-index in een ander scenario.

Machtigingen

Vereist de ALTER machtiging voor de tabel of het weergeven of lidmaatschap van de rol van de db_ddladmin vaste database.

Beperkingen en beperkingen

In Azure Synapse Analytics and Analytics Platform System (PDW) kunt u het volgende niet maken:

Een geclusterde of niet-geclusterde rowstore-index in een datawarehousetabel wanneer er al een columnstore-index bestaat. Dit gedrag verschilt van SMP SQL Server, waardoor zowel rowstore- als columnstore-indexen naast elkaar in dezelfde tabel kunnen bestaan.
U kunt geen index maken in een weergave.

Metagegevens

Als u informatie over bestaande indexen wilt weergeven, kunt u een query uitvoeren op de sys.indexes catalogusweergave.

Versieopmerkingen

Azure SQL Database biedt geen ondersteuning voor bestandsgroepen anders dan PRIMARY.
Azure SQL Database en Azure SQL Managed Instance bieden geen ondersteuning voor FILESTREAM opties.
Columnstore-indexen zijn niet beschikbaar vóór SQL Server 2012 (11.x).
Hervatbare indexbewerkingen zijn beschikbaar vanaf SQL Server 2017 (14.x), in Azure SQL Database en in Azure SQL Managed Instance.

Voorbeelden: Alle versies. Maakt gebruik van de AdventureWorks-database

Een. Een eenvoudige niet-geclusterde rowstore-index maken

In de volgende voorbeelden wordt een niet-geclusterde index gemaakt in de kolom VendorID van de Purchasing.ProductVendor tabel.

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. Een eenvoudige niet-geclusterde samengestelde rowstore-index maken

In het volgende voorbeeld wordt een niet-geclusterde samengestelde index gemaakt op de SalesQuota en SalesYTD kolommen van de Sales.SalesPerson tabel.

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

C. Een index maken voor een tabel in een andere database

In het volgende voorbeeld wordt een geclusterde index gemaakt in de kolom VendorID van de ProductVendor tabel in de Purchasing-database.

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

D. Een kolom toevoegen aan een index

In het volgende voorbeeld wordt een index gemaakt IX_FF met twee kolommen uit de dbo. Tabel FactFinance. Met de volgende instructie wordt de index opnieuw opgebouwd met één kolom en blijft de bestaande naam.

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);

Voorbeelden: SQL Server, Azure SQL Database

E. Een unieke niet-geclusterde index maken

In het volgende voorbeeld wordt een unieke niet-geclusterde index gemaakt in de kolom Name van de Production.UnitMeasure tabel in de AdventureWorks2022-database. De index dwingt uniekheid af voor de gegevens die zijn ingevoegd in de kolom Name.

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

Met de volgende query wordt de uniekheidsbeperking getest door een rij in te voegen met dezelfde waarde als die in een bestaande rij.

-- 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());

Het resulterende foutbericht is:

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. De optie IGNORE_DUP_KEY gebruiken

In het volgende voorbeeld ziet u het effect van de optie IGNORE_DUP_KEY door eerst meerdere rijen in een tijdelijke tabel in te voegen, waarbij de optie is ingesteld op ON en opnieuw met de optie ingesteld op OFF. Er wordt één rij ingevoegd in de #Test tabel die opzettelijk een dubbele waarde veroorzaakt wanneer de tweede INSERT instructie met meerdere rijen wordt uitgevoerd. Een aantal rijen in de tabel retourneert het aantal ingevoegde rijen.

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

Hier volgen de resultaten van de tweede INSERT instructie.

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

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

U ziet dat de rijen die zijn ingevoegd uit de Production.UnitMeasure tabel die niet in strijd zijn met de beperking voor uniekheid, zijn ingevoegd. Er is een waarschuwing gegeven en de dubbele rij is genegeerd, maar de hele transactie is niet teruggedraaid.

Dezelfde instructies worden opnieuw uitgevoerd, maar met IGNORE_DUP_KEY ingesteld op 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

Hier volgen de resultaten van de tweede INSERT instructie.

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

U ziet dat geen van de rijen uit de Production.UnitMeasure tabel in de tabel is ingevoegd, hoewel slechts één rij in de tabel de UNIQUE indexbeperking heeft geschonden.

G. Een index verwijderen en opnieuw maken met behulp van DROP_EXISTING

In het volgende voorbeeld wordt een bestaande index in de kolom ProductID van de Production.WorkOrder tabel in de AdventureWorks2022-database verwijderd en opnieuw gemaakt met behulp van de optie DROP_EXISTING. De opties FILLFACTOR en PAD_INDEX zijn ook ingesteld.

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

H. Een index maken in een weergave

In het volgende voorbeeld worden een weergave en een index voor die weergave gemaakt. Er worden twee query's opgenomen die gebruikmaken van de geïndexeerde weergave.

-- 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

Ik. Een index maken met opgenomen kolommen (niet-sleutelkolommen)

In het volgende voorbeeld wordt een niet-geclusterde index gemaakt met één sleutelkolom (PostalCode) en vier niet-sleutelkolommen (AddressLine1, AddressLine2, City, StateProvinceID). Een query die wordt gedekt door de index, volgt. Als u de index wilt weergeven die is geselecteerd door de queryoptimalisatie, selecteert u in het menu Query in SQL Server Management Studio de optie Werkelijke uitvoeringsplan weergeven voordat u de query uitvoert.

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. Een gepartitioneerde index maken

In het volgende voorbeeld wordt een niet-geclusterde gepartitioneerde index gemaakt op TransactionsPS1, een bestaand partitieschema in de AdventureWorks2022-database. In dit voorbeeld wordt ervan uitgegaan dat het gepartitioneerde indexvoorbeeld is geïnstalleerd.

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

K. Een gefilterde index maken

In het volgende voorbeeld wordt een gefilterde index gemaakt in de tabel Production.BillOfMaterials in de AdventureWorks2022-database. Het filterpredicaat kan kolommen bevatten die geen sleutelkolommen in de gefilterde index zijn. Het predicaat in dit voorbeeld selecteert alleen de rijen waarin EndDate niet null is.

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

L. Een gecomprimeerde index maken

In het volgende voorbeeld wordt een index gemaakt voor een niet-gepartitioneerde tabel met behulp van rijcompressie.

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

In het volgende voorbeeld wordt een index voor een gepartitioneerde tabel gemaakt met behulp van rijcompressie op alle partities van de index.

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

In het volgende voorbeeld wordt een index gemaakt op een gepartitioneerde tabel met behulp van paginacompressie op partities 1 van de index- en rijcompressie op partities 2 via 4 van de index.

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. Een index maken met XML-compressie

van toepassing op: SQL Server 2022 (16.x) en latere versies, Azure SQL Database en Azure SQL Managed Instance.

In het volgende voorbeeld wordt een index gemaakt voor een niet-gepartitioneerde tabel met behulp van XML-compressie. Ten minste één kolom in de index moet het xml- gegevenstype zijn.

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

In het volgende voorbeeld wordt een index voor een gepartitioneerde tabel gemaakt met behulp van XML-compressie op alle partities van de index.

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

N. Hervatbare indexbewerkingen maken, hervatten, onderbreken en afbreken

van toepassing op: SQL Server 2019 (15.x) en latere versies, Azure SQL Database en Azure SQL Managed Instance

-- 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. CREATE INDEX met verschillende vergrendelingsopties met lage prioriteit

In de volgende voorbeelden wordt de optie WAIT_AT_LOW_PRIORITY gebruikt om verschillende strategieën voor het blokkeren op te geven.

--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

In het volgende voorbeeld wordt zowel de optie RESUMABLE gebruikt als twee MAX_DURATION waarden, de eerste is van toepassing op de ABORT_AFTER_WAIT optie, de tweede is van toepassing op de RESUMABLE optie.

--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);

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

P. Basissyntaxis

Hervatbare indexbewerkingen maken, hervatten, onderbreken en afbreken

van toepassing op: SQL Server 2019 (15.x) en latere versies, Azure SQL Database en Azure SQL Managed Instance

-- 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. Een niet-geclusterde index maken in een tabel in de huidige database

In het volgende voorbeeld wordt een niet-geclusterde index gemaakt in de kolom VendorID van de ProductVendor tabel.

CREATE INDEX IX_ProductVendor_VendorID
  ON ProductVendor (VendorID);

R. Een geclusterde index maken in een tabel in een andere database

In het volgende voorbeeld wordt een niet-geclusterde index gemaakt in de kolom VendorID van de ProductVendor tabel in de Purchasing-database.

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

S. Een geordende geclusterde index maken in een tabel

In het volgende voorbeeld wordt een geordende geclusterde index gemaakt op de c1 en c2 kolommen van de T1 tabel in de MyDB-database.

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

T. Een CCI converteren naar een geordende geclusterde index in een tabel

In het volgende voorbeeld wordt de bestaande geclusterde columnstore-index geconverteerd naar een geordende columnstore-index met de naam MyOrderedCCI op de c1 en c2 kolommen van de T2 tabel in de MyDB database.

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

Delen via

CREATE INDEX (Transact-SQL)

Syntaxis

Syntaxis voor SQL Server, Azure SQL Database, Azure SQL Managed Instance

Achterwaarts compatibele relationele index

Syntaxis voor Azure Synapse Analytics en Parallel Data Warehouse

Argumenten

UNIEK

GECLUSTERDE

NIET-GECLUSTERD

index_name

kolom

[ ASC | DESC ]

INCLUDE (kolom [ ,... n ] )

WHERE <filter_predicate>

ON partition_scheme_name ( column_name )

OP filegroup_name

ON [standaard]

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

<object>::=

database_name

schema_name

table_or_view_name

<relational_index_option>::=

PAD_INDEX = { ON | UIT }

FILLFACTOR = fillfactor

SORT_IN_TEMPDB = { ON | UIT }

IGNORE_DUP_KEY = { ON | UIT }

STATISTICS_NORECOMPUTE = { AAN | UIT}

STATISTICS_INCREMENTAL = { ON | UIT }

DROP_EXISTING = { ON | UIT }

ONLINE = { ON | UIT }

HERVATBAAR = { AAN | UIT }

MAX_DURATION = tijd [MINUTEN] gebruikt met RESUMABLE = ON (hiervoor is ONLINE = ONvereist)

ALLOW_ROW_LOCKS = { AAN | UIT }

ALLOW_PAGE_LOCKS = { AAN | UIT }

OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | UIT }

MAXDOP = max_degree_of_parallelism

DATA_COMPRESSION

XML_COMPRESSION

ON PARTITIONS ( { <partition_number_expression> | <bereik> } [ ,...n ] )

Opmerkingen

Geclusterde indexen

Niet-geclusterde indexen

Unieke indexen

Gepartitioneerde indexen

Gefilterde indexen

Vereiste SET-opties voor gefilterde indexen

Ruimtelijke indexen

XML-indexen

Grootte van indexsleutel

Berekende kolommen

Opgenomen kolommen in indexen

Indexopties opgeven

DROP_EXISTING-component

ONLINE-optie

Hervatbare indexbewerkingen

Weg

Huidige functionele beperkingen

WAIT_AT_LOW_PRIORITY met online indexbewerkingen

Opties voor rij- en paginavergrendelingen

Sequentiële sleutels

Gegevenscompressie

XML-compressie

Indexstatistieken

Machtigingen

Beperkingen en beperkingen

Metagegevens

Versieopmerkingen

Voorbeelden: Alle versies. Maakt gebruik van de AdventureWorks-database

Een. Een eenvoudige niet-geclusterde rowstore-index maken

B. Een eenvoudige niet-geclusterde samengestelde rowstore-index maken

C. Een index maken voor een tabel in een andere database

D. Een kolom toevoegen aan een index

Voorbeelden: SQL Server, Azure SQL Database

E. Een unieke niet-geclusterde index maken

F. De optie IGNORE_DUP_KEY gebruiken

G. Een index verwijderen en opnieuw maken met behulp van DROP_EXISTING

H. Een index maken in een weergave

Ik. Een index maken met opgenomen kolommen (niet-sleutelkolommen)

MAX_DURATION = tijd [MINUTEN] gebruikt met `RESUMABLE = ON` (hiervoor is `ONLINE = ON`vereist)