Delen via

CREATE INDEX (Transact-SQL)

  • Artikel

van toepassing op:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics 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 en SQL Database.

Raadpleeg de sql Server-indexontwerphandleidingvoor informatie over richtlijnen voor indexontwerp.

voorbeelden:

  1. Een niet-geclusterde index maken in een tabel of weergave

    CREATE INDEX index1 ON schema1.table1 (column1);

  2. Een geclusterde index maken in een tabel en een driedelige naam voor de tabel gebruiken

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

  3. 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) en SQL Database 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. Een geclusterde index in een weergave moet uniek zijn.

De database-engine staat het maken van een unieke index niet toe voor kolommen die al dubbele waarden bevatten, ongeacht of IGNORE_DUP_KEY is ingesteld op AAN. Als dit wordt geprobeerd, wordt in de database-engine een foutbericht weergegeven. Dubbele waarden moeten worden verwijderd voordat een unieke index kan worden gemaakt in de kolom of kolommen. Kolommen die in een unieke index worden gebruikt, moeten worden ingesteld op NOT NULL, omdat meerdere null-waarden worden beschouwd als duplicaten wanneer een unieke index wordt gemaakt.

GECLUSTERDE

Hiermee maakt u een index waarin de logische volgorde van de sleutelwaarden de fysieke volgorde van de bijbehorende rijen in een tabel bepaalt. Het onderste of bladniveau van de geclusterde index bevat de werkelijke gegevensrijen van de tabel. Een tabel of weergave is één geclusterde index tegelijk toegestaan.

Een weergave met een unieke geclusterde index wordt een geïndexeerde weergave genoemd. 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. Zie Geïndexeerde weergaven makenvoor meer informatie.

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.

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

Notitie

Omdat het bladniveau van een geclusterde index en de gegevenspagina's per definitie hetzelfde zijn, wordt een geclusterde index gemaakt en met behulp van de component ON partition_scheme_name of ON filegroup_name effectief een tabel verplaatst van 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 uitschakelenvoor meer informatie.

NIET-GECLUSTERD

Hiermee maakt u een index die de logische volgorde van een tabel aangeeft. Met een niet-geclusterde index is de fysieke volgorde van de gegevensrijen onafhankelijk van de geïndexeerde volgorde.

Elke tabel kan maximaal 999 niet-geclusterde indexen bevatten, ongeacht hoe de indexen worden gemaakt: impliciet met PRIMAIRE SLEUTEL en UNIEKE beperkingen, of 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 weergavedefinitie kan ook geen ntext, tekstof afbeelding kolommen bevatten, zelfs niet als er niet naar wordt verwezen in de instructie CREATE INDEX.

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 DOOR de gebruiker gedefinieerde CLR-typenvoor 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 de 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 de geclusterde indexkolommen als een geclusterde index is gedefinieerd in de tabel. Zie Indexen maken met opgenomen kolommenvoor meer informatie.

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

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 in niet-sleutelkolommen zolang de berekende kolomgegevenstypen zijn 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 UDT-kolom, een kolom met ruimtelijke gegevenstypen of een kolom met het gegevenstype hierarchyID. Vergelijkingen met NULL letterlijke gegevens zijn niet toegestaan met de vergelijkingsoperatoren. 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 UNIEKE 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 kolom op waarmee een gepartitioneerde index wordt gepartitioneerd. 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.

AAN "standaard"

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

De standaardterm is in deze context geen trefwoord. Het is een id voor de standaardbestandsgroep en moet worden gescheiden, zoals in ON "default" of ON [default]. Als 'standaard' is opgegeven, moet de optie QUOTED_IDENTIFIER ingeschakeld zijn voor de huidige sessie. Dit is de standaardinstelling. Zie SET QUOTED_IDENTIFIERvoor meer informatie.

Notitie

'standaard' geeft niet de standaardbestandsgroep van de database aan in de context van CREATE INDEX. Dit verschilt van CREATE TABLE, waarbij standaard de tabel in de standaardbestandsgroep van de database wordt gevonden.

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

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.

De weergave moet worden gedefinieerd met SCHEMABINDING om er een index op te maken. Er moet een unieke geclusterde index worden gemaakt in een weergave voordat een niet-geclusterde index wordt gemaakt. Zie de sectie 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 de database_name de huidige database is of de database_name is tempdb en de object_name begint met #.

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

OP
Het percentage vrije ruimte dat wordt opgegeven door fillfactor wordt toegepast op de pagina's op tussenliggend niveau van de index.

UIT of fillfactor is niet opgegeven
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.

De optie PAD_INDEX is alleen nuttig wanneer FILLFACTOR is opgegeven, omdat PAD_INDEX het percentage gebruikt dat is opgegeven door FILLFACTOR. Als het opgegeven percentage voor FILLFACTOR 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 van 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 voor 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.

Gebruik fill_factor in sys.indexesom de instelling van de vulfactor weer te geven.

Belangrijk

Het maken van een geclusterde index met een FILLFACTOR minder dan 100 heeft invloed op de hoeveelheid opslagruimte die de gegevens in beslag nemen, omdat de database-engine de gegevens herdistribueert wanneer de geclusterde index wordt gemaakt.

Zie Vulfactor opgeven voor een index-voor meer informatie.

SORT_IN_TEMPDB = { ON | UIT }

Hiermee geeft u op of tijdelijke sorteerresultaten moeten worden opgeslagen in tempdb-. De standaardwaarde is UIT, met uitzondering van Azure SQL Database Hyperscale. Voor alle indexbuildbewerkingen in Hyperscale is SORT_IN_TEMPDB altijd INGESCHAKELD, ongeacht de optie die is opgegeven, tenzij hervattend opnieuw opbouwen van indexen wordt gebruikt.

OP
De tussenliggende sorteerresultaten die worden gebruikt om de index te bouwen, worden opgeslagen in tempdb-. Dit kan de tijd verminderen die nodig is om een index te maken als tempdb- zich op een andere set schijven bevindt dan de gebruikersdatabase. 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, moet tempdb- ongeveer dezelfde hoeveelheid extra ruimte hebben 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 UIT.

OP
Er wordt een waarschuwingsbericht weergegeven wanneer dubbele sleutelwaarden worden ingevoegd in een unieke index. Alleen de rijen die de uniekheidsbeperking schenden, mislukken.

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

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

Als u IGNORE_DUP_KEYwilt weergeven, gebruikt u sys.indexes.

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 distributiestatistieken opnieuw worden berekend. De standaardwaarde is UIT.

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.

Belangrijk

Als u automatische hercomputatie van distributiestatistieken uitschakelt, kan het voorkomen dat de optimalisatiefunctie voor query's optimale uitvoeringsplannen kiest voor query's die betrekking hebben op de tabel.

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

STATISTICS_INCREMENTAL = { ON | UIT }

van toepassing op: SQL Server (vanaf SQL Server 2014 (12.x)) en Azure SQL Database

Wanneer ON, zijn de gemaakte statistieken per partitiestatistieken. Wanneer UIT, wordt de structuur voor statistieken verwijderd en berekent SQL Server de statistieken opnieuw. De standaardwaarde is UIT.

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

  • 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 UIT.

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

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 Intent Share-vergrendeling (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 zeer korte periode op het bronobject bewaard. Aan het einde van de bewerking wordt gedurende een korte periode een S-vergrendeling (Gedeeld) op de bron verkregen als er een niet-geclusterde index wordt gemaakt. Er wordt een Sch-M -vergrendeling (Schemawijziging) 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 AAN wanneer een index wordt gemaakt in een lokale tijdelijke tabel.

Notitie

Het maken van online indexen kan de low_priority_lock_wait-opties instellen, zie WAIT_AT_LOW_PRIORITY met online indexbewerkingen.

AF
Tabelvergrendelingen worden toegepast voor de duur van de indexbewerking. Een offline indexbewerking waarmee een geclusterde index wordt gemaakt, opnieuw wordt opgebouwd of verwijderd, of een niet-geclusterde index wordt opgebouwd of verwijderd, wordt een schemawijziging (Sch-M) voor 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, verkrijgt een gedeelde (S)-vergrendeling op de tabel. Hiermee voorkomt u updates van de onderliggende tabel, maar staat u leesbewerkingen toe, zoals SELECT-instructies.

Zie Indexbewerkingen online uitvoerenvoor 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 ouder
  • Niet-geclusterde columnstore-indexen in SQL Server 2016 (13.x)) en ouder
  • 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 (beginnend met SQL Server 2012 (11.x)) en Azure SQL Database, kan een geclusterde index met andere kolommen worden gemaakt of opnieuw worden opgebouwd met behulp van de optie ONLINE, wanneer een tabel of varbinary(max) kolommen bevat.
  • Niet-geclusterde indexen in een tabel met een geclusterde columnstore-index

Zie How Online Index Operations Workvoor meer informatie.

HERVATBAAR = { AAN | UIT }

van toepassing op: SQL Server (vanaf SQL Server 2019 (15.x)) en Azure SQL Database

Hiermee geeft u op of een online indexbewerking hervat kan worden.

OP
De indexbewerking kan worden hervat.

AF
De indexbewerking kan niet worden hervat.

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

van toepassing op: SQL Server (vanaf SQL Server 2019 (15.x)) en Azure SQL Database

Geeft de tijd aan (een geheel getal dat is opgegeven in minuten) dat een hervatbare online indexbewerking wordt uitgevoerd voordat deze wordt onderbroken.

Belangrijk

Zie Richtlijnen voor online indexbewerkingenvoor meer gedetailleerde informatie over indexbewerkingen die online kunnen worden uitgevoerd.

Notitie

Hervattbare herbouw van online indexen worden niet ondersteund voor columnstore-indexen of uitgeschakelde indexen.

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 (vanaf SQL Server 2019 (15.x)) en Azure SQL Database

Hiermee geeft u op of het al dan niet moet worden geoptimaliseerd voor conflicten met het invoegen van laatste pagina's. De standaardwaarde is UIT. Zie de sectie Sequentiële sleutels voor meer informatie.

MAXDOP = max_degree_of_parallelism

Overschrijft de maximale mate van parallelle uitvoering configuratieoptie voor de duur van de indexbewerking. Zie De maximale mate van parallelle configuratie van server configurerenvoor meer informatie. Gebruik MAXDOP om het aantal processors dat wordt gebruikt in een parallelle uitvoering van een plan te beperken. Het maximum is 64 processors.

max_degree_of_parallelism kan het volgende zijn:

1
Onderdrukt het genereren van parallelle plannen.

>1
Hiermee beperkt u het maximum aantal processors dat in een parallelle indexbewerking wordt gebruikt tot het opgegeven aantal of minder op basis van de huidige systeemworkload.

0 (standaard)
Gebruikt het werkelijke aantal processors of minder 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.

ROEIEN
Indexen of opgegeven partities worden gecomprimeerd met behulp van rijcompressie.

BLADZIJDE
Index- of opgegeven partities worden gecomprimeerd met behulp van paginacompressie.

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
Indexen of opgegeven partities worden niet gecomprimeerd.

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 woord AAN, 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

De CREATE INDEX-instructie is geoptimaliseerd zoals elke andere query. Als u wilt opslaan bij I/O-bewerkingen, kan de queryprocessor 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 multiprocessorcomputers CREATE INDEX meer processors kunnen gebruiken om de scan- en sorteerbewerkingen uit te voeren 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 de sessie wordt beëindigd, worden de indexen verwijderd.

Een geclusterde index kan worden gebouwd op een tabelvariabele wanneer een primaire sleutel wordt gemaakt. Wanneer de query is voltooid of de sessie eindigt, wordt de index 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 Geclusterde indexen maken en de SQL Server-indexarchitectuur en ontwerphandleidingvoor meer informatie over geclusterde indexen.

Niet-geclusterde indexen

Vanaf SQL Server 2016 (13.x) en in Azure SQL Database 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 op 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.

Beperkingen en beperkingen:

  • 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 telkens wanneer gegevens worden toegevoegd door invoegbewerkingen. Voeg bewerkingen in waarmee dubbele sleutelwaarden worden gegenereerd, worden teruggedraaid en de database-engine geeft een foutbericht weer. Dit geldt zelfs als de invoegbewerking veel rijen wijzigt, maar slechts één duplicaat veroorzaakt. Als er een poging wordt gedaan om gegevens in te voeren waarvoor er een unieke index is en de IGNORE_DUP_KEY component is ingesteld op AAN, mislukken alleen de rijen die de UNIEKE index schenden.

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 SQL Server-indexarchitectuur en ontwerphandleidingvoor meer informatie over gepartitioneerde indexen.

In SQL Server worden statistieken niet gemaakt door alle rijen in de tabel te scannen wanneer een gepartitioneerde index wordt gemaakt of opnieuw wordt opgebouwd. In plaats daarvan gebruikt de queryoptimalisatie het standaardsamplingsalgoritmen om statistieken te genereren. 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 opties SET in de kolom Vereiste waarde zijn vereist wanneer een van de volgende voorwaarden voorkomt:

  • Een gefilterde index maken.

  • De bewerking INSERT, UPDATE, DELETE of MERGE wijzigt de gegevens in een gefilterde index.

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

    OPTIES INSTELLEN Vereiste waarde Standaardserverwaarde Verstek

    OLE DB- en ODBC-waarde    		 Verstek

    DB-Library waarde
    ANSI_NULLS OP OP OP AF
    ANSI_PADDING OP OP OP AF
    ANSI_WARNINGS* OP OP OP AF
    ARITHABORT OP OP AF AF
    CONCAT_NULL_YIELDS_NULL OP OP OP AF
    NUMERIC_ROUNDABORT AF AF AF AF
    QUOTED_IDENTIFIER OP OP OP AF
    • Als ANSI_WARNINGS op ON wordt ingesteld, wordt ARITHABORT impliciet ingesteld op AAN 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 optie ARITHABORT expliciet worden ingesteld op AAN.

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

  • De gefilterde index wordt niet gemaakt.
  • De database-engine genereert een fout en rolt INSERT-, UPDATE-, DELETE- of MERGE-instructies terug die gegevens in de index wijzigen.
  • Query optimizer beschouwt de index in het uitvoeringsplan niet voor Transact-SQL instructies.

Zie Filtered Indexes maken en de SQL Server Index Architecture and Design Guidevoor meer informatie over gefilterde indexen.

Ruimtelijke indexen

Zie CREATE SPATIAL INDEX en Spatial Indexes Overviewvoor 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 op 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; Volgende acties voor het invoegen of bijwerken van de kolommen waardoor de totale grootte groter is dan de limiet mislukt. De indexsleutel van een geclusterde index kan geen varchar kolommen bevatten met bestaande gegevens in de ROW_OVERFLOW_DATA toewijzingseenheid. Als er een geclusterde index wordt gemaakt op een varchar kolom en de bestaande gegevens zich in de IN_ROW_DATA toewijzingseenheid bevinden, mislukken volgende invoeg- of bijwerkacties in de kolom die de gegevens buiten de rij pushen.

Niet-geclusterde indexen kunnen niet-sleutelkolommen 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 ontwerphandleidingvoor 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. Bovendien kunnen berekende kolommen de eigenschap PERSISTENTED hebben. 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 echter de eigenschap PERSISTENTED gebruikt, wordt het type geïndexeerde berekende kolommen 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 permanente berekende kolommen moeten de volgende SET-opties worden ingesteld, zoals wordt weergegeven in de vorige sectie Vereiste SET-opties voor gefilterde indexen.

De beperking UNIEKE of PRIMAIRE SLEUTEL 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 leiden tot het mislukken van een invoeg- of bijwerkbewerking die eerder werkte. Een dergelijke fout kan optreden wanneer de berekende kolom resulteert in rekenkundige fouten. In de volgende tabel werkt de INSERT-instructie, hoewel berekende kolom c resulteert in een rekenkundige fout.

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

Als u na het maken van de tabel een index maakt op 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 van een indexscan vinden; de tabel- of geclusterde indexgegevens zijn niet toegankelijk. Zie Indexen maken met opgenomen kolommen en de SQL Server-indexarchitectuur en ontwerphandleidingvoor meer informatie.

Indexopties opgeven

SQL Server 2005 (9.x) heeft nieuwe indexopties geïntroduceerd en wijzigt ook de manier waarop opties worden opgegeven. In 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 PRIMAIRE SLEUTEL of UNIEKE beperking 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 PRIMAIRE SLEUTEL of UNIEKE 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. Daarom kunnen zelfs de niet-geclusterde indexen die eerder zijn uitgelijnd met de geclusterde index, niet langer met deze index worden uitgelijnd. Zie Gepartitioneerde tabellen en indexenvoor meer informatie over gepartitioneerde indexuitlijning.

De DROP_EXISTING component sorteert de gegevens niet opnieuw als dezelfde indexsleutelkolommen in dezelfde volgorde en met dezelfde oplopende of aflopende volgorde worden gebruikt, tenzij de indexinstructie een niet-geclusterde index opgeeft en de optie ONLINE is ingesteld op UIT. Als de geclusterde index is uitgeschakeld, moet de CREATE INDEX WITH DROP_EXISTING bewerking worden uitgevoerd met ONLINE ingesteld op UIT. 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 UIT of AAN.

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.

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 optie low_priority_lock_wait argument kunt u bepalen hoe de indexbewerking kan worden voortgezet wanneer deze wordt geblokkeerd op de Sch-M-vergrendeling.

Zie Indexbewerkingen online uitvoerenvoor meer informatie.

Weg

De volgende resources zijn vereist voor het hervatten van een online indexbewerking:

  • Extra ruimte die nodig is om de index te laten bouwen, inclusief het tijdstip waarop de index 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.
  • Een DDL-status die DDL-wijzigingen verhindert
  • Ghost-opschoning wordt geblokkeerd voor de in-build-index voor de duur van de bewerking, zowel tijdens onderbroken als terwijl de bewerking wordt uitgevoerd.

Huidige functionele beperkingen

De volgende functionaliteit is uitgeschakeld voor hervatbare bewerkingen voor het maken van indexen:

  • Nadat een hervatbare online index maken is onderbroken, kan de initiële waarde van MAXDOP niet worden gewijzigd

  • Maak een index die het volgende bevat:

    • Berekende of TIMESTAMP-kolom(en) als sleutelkolommen
    • LOB-kolom als opgenomen kolom voor hervatbare index maken
    • Gefilterde index

Hervatbare indexbewerkingen

van toepassing op: SQL Server (vanaf SQL Server 2019 (15.x)) en Azure SQL Database

De volgende richtlijnen zijn van toepassing op hervatbare indexbewerkingen:

  • Online index maken wordt opgegeven als hervat met behulp van de optie RESUMABLE = ON.
  • De optie HERVATTEN wordt niet bewaard in de metagegevens voor een bepaalde index en is alleen van toepassing op de duur van een huidige DDL-instructie. Daarom moet de RESUMABLE = ON-component expliciet worden opgegeven om hervatting mogelijk te maken.
  • MAX_DURATION optie wordt alleen ondersteund voor RESUMABLE = ON optie.
  • MAX_DURATION voor de optie HERVATBAAR geeft het tijdsinterval op voor een index die wordt gemaakt. Zodra deze tijd wordt gebruikt, wordt de indexbuild onderbroken of wordt de uitvoering voltooid. Gebruiker bepaalt wanneer een build voor een onderbroken index kan worden hervat. De tijd in minuten voor MAX_DURATION moet groter zijn dan 0 minuten en kleiner of gelijk zijn aan één week (7 * 24 * 60 = 10080 minuten). Een lange pauze voor een indexbewerking kan van invloed zijn op de DML-prestaties op een specifieke tabel en de capaciteit van de databaseschijf, omdat zowel de oorspronkelijke als de nieuwe indexeerfunctie schijfruimte vereist en moet worden bijgewerkt tijdens DML-bewerkingen. Als MAX_DURATION optie wordt weggelaten, wordt de indexbewerking voortgezet totdat deze is voltooid of totdat er een fout optreedt.
  • Als u de indexbewerking onmiddellijk wilt onderbreken, kunt u de lopende opdracht stoppen (Ctrl-C), de ALTER INDEX OPDRACHT ONDERBREKEN of de opdracht KILL <session_id> uitvoeren. Zodra de opdracht is onderbroken, kan deze worden hervat met opdracht ALTER INDEX.
  • Als u de oorspronkelijke CREATE INDEX-instructie voor hervatbare index opnieuw uitvoert, wordt automatisch een onderbroken indexbewerking hervat.
  • De optie SORT_IN_TEMPDB = ON wordt niet ondersteund voor hervatbare indexen.
  • De DDL-opdracht met RESUMABLE = ON kan niet worden uitgevoerd binnen een expliciete transactie (kan geen deel uitmaken van TRAN ... COMMIT blok).
  • Als u een index wilt hervatten/afbreken, gebruikt u de ALTER INDEX T-SQL-syntaxis.
  • Uitgeschakelde indexen worden niet ondersteund.

Notitie

De DDL-opdracht wordt uitgevoerd totdat deze is voltooid, wordt onderbroken of mislukt. Als de opdracht wordt onderbroken, wordt er een fout weergegeven die aangeeft dat de bewerking is onderbroken en dat het maken van de index niet is voltooid. Meer informatie over de huidige indexstatus kan worden verkregen van sys.index_resumable_operations. Net als bij een fout wordt ook een fout uitgegeven.

Zie sys.index_resumable_operationsom aan te geven dat een index wordt gemaakt als hervatbare bewerking en om de huidige uitvoeringsstatus te controleren.

WAIT_AT_LOW_PRIORITY met online indexbewerkingen

is alleen van toepassing op: deze syntaxis voor CREATE INDEX is momenteel alleen van toepassing op SQL Server 2022 (16.x), Azure SQL Database en Azure SQL Managed Instance. Voor ALTER INDEXis deze syntaxis van toepassing op SQL Server (vanaf SQL Server 2014 (12.x)) en Azure SQL Database. Zie ALTER INDEXvoor meer informatie.

Met de syntaxis van low_priority_lock_wait kunt u WAIT_AT_LOW_PRIORITY gedrag opgeven. WAIT_AT_LOW_PRIORITY kan alleen met ONLINE=ON worden gebruikt.

Met de optie WAIT_AT_LOW_PRIORITY kunnen DBA's de Sch-S en Sch-M vergrendelingen beheren die vereist zijn voor het maken van online indexen en kunnen ze een van de drie opties selecteren. In alle drie gevallen, als tijdens de wachttijd MAX_DURATION = n [minutes], er geen blokkerende activiteiten zijn, wordt de online index opnieuw opbouwen onmiddellijk uitgevoerd zonder te wachten en de DDL-instructie is voltooid.

WAIT_AT_LOW_PRIORITY geeft aan dat de bewerking voor het maken van online indexen wacht op vergrendelingen met een lage prioriteit, zodat andere bewerkingen kunnen worden voortgezet terwijl de online indexbuildbewerking wacht. 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 [MINUTEN]

De wachttijd (een geheel getal dat is opgegeven in minuten) die door de onlineindex wordt gemaakt, wacht met een lage prioriteit bij het uitvoeren van de DDL-opdracht. 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 MINUTEN kan worden weggelaten.

ABORT_AFTER_WAIT = [NONE | SELF | BLOCKERS } ]

NONE Blijf wachten op de vergrendeling met normale (normale) prioriteit.

Self Exit the online index create DDL operation currently being executed, without taking any action. De optie SELF- kan niet worden gebruikt met een MAX_DURATION van 0.

BLOCKERS doden alle gebruikerstransacties die de DDL-bewerking voor online index herbouwen blokkeren, zodat de bewerking kan worden voortgezet. De optie BLOCKERS vereist dat de aanmelding ALTER ANY CONNECTION machtiging heeft.

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.

Sequentiële sleutels

van toepassing op: SQL Server (vanaf SQL Server 2019 (15.x)) en Azure SQL Database

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 op PAGELATCH_EX wachten op de betreffende pagina.

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

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.

Indexinformatie weergeven

Als u informatie over indexen wilt retourneren, kunt u catalogusweergaven, systeemfuncties en door het systeem opgeslagen procedures gebruiken.

Gegevenscompressie

Gegevenscompressie wordt beschreven in het onderwerp Gegevenscompressie. Hier volgen belangrijke punten die u moet overwegen:

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

De volgende beperkingen gelden voor gepartitioneerde indexen:

  • U kunt de compressie-instelling van één partitie niet wijzigen als de tabel niet-uitgelijnde indexen heeft.
  • De ALTER INDEX <index> ... REBUILD PARTITION ... syntaxis herbouwt de opgegeven partitie van de index.
  • De ALTER INDEX <index> ... REBUILD WITH ... syntaxis herbouwt alle partities van de index.

Als u wilt evalueren hoe het wijzigen van de compressiestatus van invloed is op 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 dezelfde 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, voert u een query uit op de kolom xml_compression van de sys.partitions catalogusweergave.

Machtigingen

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

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

SQL Database biedt geen ondersteuning voor bestandsgroep- en filestreamopties.

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 (vanaf SQL Server 2019 (15.x)) en 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. 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 (vanaf SQL Server 2019 (15.x)) en 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. 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);

Zie ook