Gefilterde indexen maken

Van toepassing op:SQL Server Azure SQL Database Azure SQL Managed Instance SQL-database in Microsoft Fabric

In dit artikel wordt beschreven hoe u een gefilterde index maakt met behulp van SQL Server Management Studio (SSMS) of Transact-SQL. Een gefilterde index is een geoptimaliseerde, niet-geclusterde rijopslagindex die speciaal geschikt is voor query's die een goed gedefinieerde subset met gegevens selecteren. Er wordt een filterpredicaat gebruikt om een gedeelte van rijen in de tabel te indexeren. Een goed ontworpen gefilterde index kan de prestaties van query's verbeteren en indexonderhoud en opslagkosten verminderen vergeleken met indexen in volledige tabellen.

Gefilterde indexen kunnen de volgende voordelen bieden ten opzichte van indexen in volledige tabellen:

Verbeterde prestaties van query's en kwaliteit van plannen.

Een goed ontworpen gefilterde index verbetert de prestaties van query's en de kwaliteit van het uitvoeringsplan omdat deze kleiner is dan een niet-geclusterde volledige tabel en gefilterde statistieken bevat. De gefilterde statistieken zijn nauwkeuriger dan volledige tabelstatistieken, omdat ze alleen betrekking hebben op de rijen in de gefilterde index.
Lagere onderhoudskosten voor indexen.

Een index wordt alleen onderhouden wanneer DML-instructies (Data Manipulat Language) van invloed zijn op de gegevens in de index. Een gefilterde index vermindert de onderhoudskosten voor indexen vergeleken met een niet-geclusterde index in een volledige tabel, omdat deze kleiner is en alleen wordt onderhouden wanneer de gegevens in de index worden gewijzigd. Het is mogelijk om een groot aantal gefilterde indexen te hebben, met name wanneer ze gegevens bevatten die niet vaak worden gewijzigd. Als een gefilterde index alleen de vaak gewijzigde gegevens bevat, vermindert de kleinere grootte van de index de kosten voor het bijwerken van de statistieken.
Lagere kosten voor indexopslag.

Het maken van een gefilterde index kan de schijfopslag voor niet-geclusterde indexen verminderen wanneer een volledige tabelindex niet nodig is. U kunt een niet-geclusterde volledige tabelindex vervangen door meerdere gefilterde indexen zonder de opslagvereisten aanzienlijk te verhogen.

Ontwerpoverwegingen

Wanneer een kolom slechts enkele relevante waarden voor query's bevat, kunt u een gefilterde index maken voor de subset met waarden. De resulterende index is kleiner en kost minder om te onderhouden dan een niet-geclusterde index in een volledige tabel die is gedefinieerd in dezelfde sleutelkolommen.

Denk bijvoorbeeld aan een gefilterde index in de volgende gegevensscenario's. In elk geval moet de WHERE component van de query een subset van de WHERE component van de gefilterde index zijn om te profiteren van de gefilterde index.

Wanneer de waarden in een kolom voornamelijk NULL zijn en de query alleen uit de niet-NULL-waarden selecteert. U kunt een gefilterde index maken voor de niet-NULL-gegevensrijen.
Wanneer rijen in een tabel worden gemarkeerd als verwerkt door een terugkerende workflow of wachtrijproces. Na verloop van tijd zullen de meeste rijen in de tabel als verwerkt worden gemarkeerd. Een gefilterde index voor rijen die nog niet zijn verwerkt, heeft baat bij de terugkerende query die zoekt naar rijen die nog niet zijn verwerkt.
Wanneer een tabel heterogene gegevensrijen bevat. U kunt een gefilterde index maken voor een of meer categorieën gegevens. Dit kan de prestaties van query's op deze gegevensrijen verbeteren door de focus van een query te beperken tot een specifiek gebied van de tabel. De resulterende index is weer kleiner en kost minder om te onderhouden dan een niet-geclusterde index in een volledige tabel.

Limitations

U kunt geen gefilterde index maken in een weergave. De queryoptimalisatie kan echter profiteren van een gefilterde index die is gedefinieerd in een tabel waarnaar wordt verwezen in een weergave. Als de queryresultaten juist zullen zijn, bekijkt de queryoptimalisator een gefilterde index voor een query die uit een weergave selecteert.
U kunt geen gefilterde index maken voor een tabel wanneer de kolom die in de filterexpressie wordt geopend, van een CLR-gegevenstype is.
Gefilterde indexen hebben de volgende voordelen ten opzichte van geïndexeerde weergaven:
- Lagere onderhoudskosten voor indexen. De queryprocessor gebruikt bijvoorbeeld minder CPU-resources om een gefilterde index bij te werken dan een geïndexeerde weergave.
- Verbeterde kwaliteit van het plan. Tijdens het compileren van query's beschouwt de queryoptimalisatie bijvoorbeeld het gebruik van een gefilterde index in meer situaties dan de equivalente geïndexeerde weergave.
- Online index wordt herbouwd. U kunt gefilterde indexen opnieuw bouwen terwijl deze beschikbaar zijn voor query's. Het opnieuw opbouwen van online indexen wordt niet ondersteund voor geïndexeerde weergaven. Zie de optie REBUILD voor ALTER INDEX (Transact-SQL)voor meer informatie.
- Niet-unieke indexen. Gefilterde indexen kunnen niet-uniek zijn, terwijl geïndexeerde weergaven uniek moeten zijn.
Gefilterde indexen worden gedefinieerd in één tabel en ondersteunen alleen eenvoudige vergelijkingsoperators. Als u een filterexpressie nodig hebt die verwijst naar meerdere tabellen of complexe logica heeft, moet u een weergave maken. Gefilterde indexen bieden geen ondersteuning voor LIKE operators.
Een kolom in de gefilterde indexexpressie hoeft geen sleutel of opgenomen kolom in de gefilterde indexdefinitie te zijn als de gefilterde indexexpressie gelijk is aan het querypredicaat en de query retourneert niet de kolom in de gefilterde indexexpressie met de queryresultaten.
Een kolom in de gefilterde indexexpressie moet een sleutel of een opgenomen kolom in de gefilterde indexdefinitie zijn als het querypredicaat gebruikmaakt van de kolom in een vergelijking die niet gelijk is aan de gefilterde indexexpressie.
Een kolom in de gefilterde indexexpressie moet een sleutel of een opgenomen kolom in de gefilterde indexdefinitie zijn als de kolom zich in de resultatenset van de query bevindt.
De geclusterde indexsleutel van de tabel hoeft geen sleutel of opgenomen kolom te zijn in de gefilterde indexdefinitie. De geclusterde indexsleutel wordt automatisch opgenomen in alle niet-geclusterde indexen, inclusief gefilterde indexen. Meer informatie vindt u in de indexarchitectuur en ontwerphandleiding.
Als de vergelijkingsoperator die is opgegeven in de gefilterde indexexpressie van de gefilterde index resulteert in een impliciete of expliciete gegevensconversie, treedt er een fout op als de conversie aan de linkerkant van een vergelijkingsoperator plaatsvindt. Een oplossing is het schrijven van de gefilterde indexexpressie met de operator voor gegevensconversie (CAST of CONVERT) aan de rechterkant van de vergelijkingsoperator.
Bekijk de vereiste SET opties voor het maken van gefilterde indexen in CREATE INDEX (Transact-SQL) syntaxis
Filters kunnen niet worden toegepast op primaire sleutel of unieke beperkingen, maar kunnen worden toegepast op indexen met de eigenschap UNIQUE.
U kunt een gefilterde index maken en verwijzen naar een berekende kolom in de sleutel of bevat, maar u kunt niet verwijzen naar een berekende kolom in de filterdefinitie.

Permissions

Vereist ALTER-machtigingen voor de tabel of weergave. De gebruiker moet lid zijn van de sysadmin vaste serverfunctie of de db_ddladmin en db_owner vaste databaserollen. Als u de gefilterde indexexpressie wilt wijzigen, gebruikt u CREATE INDEX WITH DROP_EXISTING.

Een gefilterde index maken met SSMS

Selecteer in Objectverkenner het plusteken om de database uit te vouwen die de tabel bevat waarop u een gefilterde index wilt maken.
Selecteer het plusteken om de map Tabellen uit te vouwen.
Selecteer het plusteken om de tabel uit te vouwen waarop u een gefilterde index wilt maken.
Klik met de rechtermuisknop op de map Indexen, wijs Nieuwe indexaan en selecteer niet-geclusterde index....
Voer in het dialoogvenster Nieuwe index op de pagina Algemeen de naam van de nieuwe index in het vak Indexnaam.
Selecteer onder indexkolommende optie Toevoegen....
Schakel in het dialoogvenster Kolommen selecteren intable_name het selectievakje of selectievakjes in van de tabelkolom of -kolommen die aan de index moeten worden toegevoegd.
Kies OK.
Voer op de pagina Filter onder FilterexpressieSQL-expressie in die u gaat gebruiken om de gefilterde index te maken.
Kies OK.

Een gefilterde index maken met Transact-SQL

De codevoorbeelden in dit artikel gebruiken de AdventureWorks2025 of AdventureWorksDW2025 voorbeelddatabase, die u kunt downloaden van de Microsoft SQL Server-voorbeelden en communityprojecten startpagina.

Maak in Objectverkennerverbinding met een exemplaar van Database Engine.
Op de standaardbalk, selecteer Nieuwe query.
Kopieer en plak het volgende voorbeeld in het queryvenster en selecteer uitvoeren.

USE AdventureWorks2022;
GO

DROP INDEX IF EXISTS FIBillOfMaterialsWithEndDate
    ON Production.BillOfMaterials
GO

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

De gefilterde index FIBillOfMaterialsWithEndDate is geldig voor de volgende query. Geef een daadwerkelijk uitvoeringsplan weer om te bepalen of de queryoptimalisatie de gefilterde index heeft gebruikt.

USE AdventureWorks2022;
GO

SELECT ProductAssemblyID, ComponentID, StartDate
FROM Production.BillOfMaterials
WHERE EndDate IS NOT NULL
    AND ComponentID = 5
    AND StartDate > '01/01/2008' ;
GO

Feedback

Is deze pagina nuttig?

Last updated on 2025-11-18