Filtered indexes
A filtered index only covers the rows that match a condition, instead of every row in the table. It is the right tool when queries always target the same subset of a large table: the index stays smaller, cheaper to maintain, and more selective.
A common case is a wide table that mixes several record kinds in one physical table, where you only ever query one kind.
For example, a Document table that stores several document kinds, but where the application always queries active standard documents (Status = 1 and Category = 'Standard'):
-- SQL Server
CREATE INDEX [IX_Document_ActiveStandard]
ON [Document] ([OwnerId], [CreatedOn])
INCLUDE ([Amount])
WHERE [Status] = 1 AND [Category] = 'Standard'
Neos lets you obtain this kind of index declaratively, without writing the CREATE INDEX statement yourself.
Declaring a filter on an index
An index exposes three optional filter properties, one per database engine:
| Property | Engine | Generated object |
|---|---|---|
PostgreSqlFilter |
PostgreSQL | native partial index |
SqlServerFilter |
SQL Server | native filtered index |
OracleFilter |
Oracle | function-based unique index |
Each property holds a SQL WHERE predicate for its engine.
There is one property per engine because value and operator syntax differs between engines, so you stay in control of the exact predicate that each database receives.
Leave a filter empty to keep a normal (full) index. Existing unfiltered indexes are unchanged: adding the filter properties to the model does not alter indexes that do not use them.
Referencing columns with {ColumnName} placeholders
Inside a filter, reference a column by wrapping its name in braces: {Status}, {Category}, and so on.
Neos substitutes each placeholder with the correctly quoted physical column identifier for the target engine, honoring the cluster's quoted identifiers setting.
Everything outside the placeholders — values, operators, literals — is written verbatim and passed to the engine as-is.
A placeholder may reference any column of the table, not only the index's key columns.
For example, an index keyed on OwnerId can filter on {Status} = 1 even though Status is not part of the index key.
A placeholder must reference a column that actually belongs to the table; otherwise generation fails with a validation error.
Example predicate, identical for PostgreSQL and SQL Server:
{Status} = 1 AND {Category} = 'Standard'
Example
An index is authored as a single file per index, under the module's metadata/Indexes/ folder, named after the index.
To reproduce the index shown above, declare the index columns as usual and add a per-engine filter on the index file:
# metadata/Indexes/IX_Document_ActiveStandard.yml
PostgreSqlFilter: '{Status} = 1 AND {Category} = ''Standard'''
SqlServerFilter: '{Status} = 1 AND {Category} = ''Standard'''
TableName: Document
Unique: false
On PostgreSQL this generates a partial index:
CREATE INDEX "IX_Document_ActiveStandard"
ON "Document" (...)
WHERE "Status" = 1 AND "Category" = 'Standard'
On SQL Server this generates a filtered index:
CREATE INDEX [IX_Document_ActiveStandard]
ON [Document] (...)
WHERE [Status] = 1 AND [Category] = 'Standard'
Create the index only where a filter is set
The filter properties also act per engine as a switch for whether the index is created at all:
- No filter on any engine — the index is created on every engine as a normal full index. This is the default and is unchanged.
- A filter on some engines but not all — the index is created only on the engines whose filter is non-empty. An empty filter for an engine means the index is not created on that engine (it is not turned into a full index there).
This lets you target an index at the engines where the partial form is useful, and skip it elsewhere. When filters are specified for some engines only, Neos Studio shows an info message to make the partial coverage explicit.
Per-engine support
| Engine | Filter on a non-unique index | Filter on a unique index |
|---|---|---|
| PostgreSQL | Native partial index | Native partial index |
| SQL Server | Native filtered index | Native filtered index |
| Oracle | Not allowed (validation error) | Function-based unique index (partial uniqueness only) |
Oracle behavior
Oracle has no native partial or filtered index, so a filter is only allowed on a unique index. There, Neos emulates it with a function-based unique index that indexes the column only for the matching rows, roughly:
CASE WHEN <filter> THEN <column> END
This preserves the partial-uniqueness semantics — uniqueness is enforced only across rows that satisfy the filter — but it does not provide any query-optimization benefit, because Oracle still scans the full table for ordinary lookups.
A filter on a non-unique Oracle index is a validation and generation error: there is no meaningful function-based equivalent for it, so define the index without a filter (or make it unique) on Oracle.
Note
On an Oracle cluster configured with unquoted identifiers (Database:QuotedIdentifiers = false), Oracle upper-cases the value of string literals when it stores the function-based-index expression ('Standard' becomes 'STANDARD').
A string-literal filter on a filtered unique index is therefore not fully supported in that mode: the index may be recreated on every migration, and the indexed subset may not match the authored value.
This does not affect the default quoted-identifier mode. On unquoted-identifier Oracle clusters, prefer numeric or non-string conditions for filtered unique indexes.
Limitations
Note
SQL Server filtered-index predicates are restricted. SQL Server only allows a WHERE predicate built
from simple comparisons (=, <>, >, >=, <, <=, IN, IS NULL, IS NOT NULL) combined with
AND. OR, LIKE, NOT IN, BETWEEN and any computed expression are not allowed and SQL Server
rejects the CREATE INDEX (for example, an OR predicate fails with "Incorrect syntax near 'OR'").
To filter a single column on several values, rewrite an OR-of-equalities as an IN list — for example
write {Status} IN (1, 2) instead of {Status} = 1 OR {Status} = 2. An IN list is valid on every engine
(on Oracle it is folded into the function-based-index expression).
Note
Filter predicates are meant to compare columns against simple constants — type codes, flags, enum values. The contents of string literals are preserved exactly when the migrator compares the authored filter with the one stored by the engine, so values containing SQL punctuation, mixed case or inner spaces are handled correctly and do not trigger spurious rebuilds. Precedence-significant parentheses outside literals are still kept as authored; an exotic predicate whose engine-stored form differs from the authored form only by such parentheses may trigger a single, one-time rebuild.