Table of Contents

Indexing with computed columns

This guide is about a specific design problem:

Which column should you materialize so the generated SQL can stop transforming values at runtime and start using an index efficiently?

The focus here is not general DataColumn modeling. It is the optimization path where Neos reuses standard columns or computed columns to avoid runtime extraction and normalization in SQL predicates.

The starting point is the request sent by the front. The front usually expresses a business query, not a storage query. It asks for things such as:

  • startswith(search.normalize(code), search.normalize('ABC'))
  • description/fr ne null
  • startswith(search.normalize(description/fr), search.normalize('ABC'))

That is the right level of expression for the client because the front works with business properties and localizable values. It should not need to know whether the database stores a plain column, a computed column, a JSON payload, or a pre-normalized search column.

The problem appears later, when that business query is translated to SQL. If no compatible persisted column exists, the database often has to do the transformation at runtime inside the predicate:

  • extract one language from a localizable value,
  • normalize a string for case-insensitive and accent-insensitive search,
  • or do both in the same expression.

Typical SQL then contains functions such as JSON_VALUE, jsonb_extract_path_text, TRANSLATE, UPPER, or a collation expression directly in the WHERE clause. That usually makes index usage much less effective than a predicate that targets a materialized column directly.

In this article, "normalized" keeps its business meaning: a value prepared for case-insensitive and accent-insensitive search. On PostgreSQL and Oracle, that means the computed column expression rewrites the value to an uppercase accent-free form. On SQL Server, the generated expression takes a different implementation path and relies on a case-insensitive and accent-insensitive collation to reach the same search goal.

This is why the optimization is not done by changing the front contract. You keep the business-oriented call from the front, and you optimize persistence so the SQL translator can reuse a compatible materialized column behind that original query.

For localized values, the OData shape therefore stays on the localizable property, for example description/fr, even when the optimized solution later relies on an additional persisted column.

How to read the examples

Each example follows the same structure:

  • the OData query sent by the client,
  • the SQL generated without a dedicated computed column,
  • the SQL generated once the right computed column exists,
  • and the DataColumn modeling needed to obtain the optimized SQL.

All search examples below use startswith(...) rather than contains(...). That is intentional: a regular index can support prefix search much more naturally than substring search. If your real query is contains(...), adding a computed column still removes runtime transformations, but you should not expect a normal index to become fully effective just because the column is precomputed.

Example 1: a standard column is already normalized

Use this pattern when the stored value is already uppercase and accent-free by design, for example a code, barcode, or imported search key.

OData
$filter=startswith(search.normalize(code), search.normalize('ABC'))

Without optimization, the database normalizes the source column during execution.

PostgreSQL
WHERE TRANSLATE(UPPER(i."Code"), 'ÉÈÊËÀÂÎÏÔÛÙÜÇ', 'EEEEAAIIOUUUC') LIKE 'ABC%'

SQL Server
WHERE [i].[Code] COLLATE Latin1_General_CI_AI LIKE 'ABC%'

Oracle
WHERE TRANSLATE(UPPER("i"."Code"), 'ÉÈÊËÀÂÎÏÔÛÙÜÇ', 'EEEEAAIIOUUUC') LIKE 'ABC%'

With optimization, the translator can keep the existing column directly.

PostgreSQL
WHERE i."Code" LIKE 'ABC%'

SQL Server
WHERE [i].[Code] LIKE 'ABC%'

Oracle
WHERE "i"."Code" LIKE 'ABC%'

To obtain that optimized SQL, do not add a shadow computed column. Keep the standard column and mark it as already normalized for search.

- Name: Code
  MaxLength: 32
  NormalizedForSearch: true
  Required: true

This is the cheapest optimization because the physical column already contains the searchable value.

Example 2: keep a readable text, but search on a normalized shadow column

Use this pattern when the business value must stay readable as entered, but search must remain index-friendly.

OData
$filter=startswith(search.normalize(label), search.normalize('ABC'))

Without optimization, the predicate normalizes the source column during execution.

PostgreSQL
WHERE TRANSLATE(UPPER(i."Label"), 'ÉÈÊËÀÂÎÏÔÛÙÜÇ', 'EEEEAAIIOUUUC') LIKE 'ABC%'

SQL Server
WHERE [i].[Label] COLLATE Latin1_General_CI_AI LIKE 'ABC%'

Oracle
WHERE TRANSLATE(UPPER("i"."Label"), 'ÉÈÊËÀÂÎÏÔÛÙÜÇ', 'EEEEAAIIOUUUC') LIKE 'ABC%'

With optimization, the translator can reuse a normalized computed column.

PostgreSQL
WHERE i."LabelNormalized" LIKE 'ABC%'

SQL Server
WHERE [i].[LabelNormalized] LIKE 'ABC%'

Oracle
WHERE "i"."LabelNormalized" LIKE 'ABC%'

To obtain that optimized SQL, keep the readable source column and add a computed search column.

- Name: LabelNormalized
  DataColumnType: PredefinedComputedColumn
  MaxLength: 200
  NormalizedForSearch: true
  SourceColumnName: Label

If users search this field frequently, index LabelNormalized rather than relying on runtime normalization.

For SQL Server specifically, LabelNormalized still represents a column normalized for search. The generated expression uses COLLATE Latin1_General_CI_AI as an implementation shortcut to obtain the same case-insensitive and accent-insensitive behavior, rather than physically rewriting the stored text to uppercase and without accents.

Example 3: filter one language of a LocalizableString

Use this pattern when one language is filtered, sorted, exported, or indexed often enough that repeated JSON extraction becomes a cost.

OData
$filter=description/fr ne null

Without optimization, the database must extract the localized value at query time.

PostgreSQL
WHERE jsonb_extract_path_text(i."Description", 'fr') IS NOT NULL

SQL Server
WHERE JSON_VALUE([i].[Description], '$.fr') IS NOT NULL

Oracle
WHERE JSON_VALUE("i"."Description", '$.fr') IS NOT NULL

With optimization, the translator can reuse a dedicated computed column for the French value.

PostgreSQL
WHERE i."DescriptionFr" IS NOT NULL

SQL Server
WHERE [i].[DescriptionFr] IS NOT NULL

Oracle
WHERE "i"."DescriptionFr" IS NOT NULL

To obtain that optimized SQL, model a computed column that extracts the language and then index it if the scenario is performance-sensitive.

- Name: DescriptionFr
  DataColumnType: PredefinedComputedColumn
  MaxLength: 250
  SourceColumnLanguage: fr
  SourceColumnName: Description

Recommended when relevant to your workload:

# Index column
- ColumnName: DescriptionFr
  Position: 1

The gain is simple: filtering and sorting no longer depend on a JSON extraction function in the predicate.

Example 4: search one localized language in normalized form

Use this pattern when the user searches a localized text with case-insensitive and accent-insensitive matching.

OData
$filter=startswith(search.normalize(description/fr), search.normalize('ABC'))

Without optimization, the database must both extract the language and normalize the text during the query.

PostgreSQL
WHERE TRANSLATE(UPPER(jsonb_extract_path_text(i."Description", 'fr')), 'ÉÈÊËÀÂÎÏÔÛÙÜÇ', 'EEEEAAIIOUUUC') LIKE 'ABC%'

SQL Server
WHERE JSON_VALUE([i].[Description], '$.fr') COLLATE Latin1_General_CI_AI LIKE 'ABC%'

Oracle
WHERE TRANSLATE(UPPER(JSON_VALUE("i"."Description", '$.fr')), 'ÉÈÊËÀÂÎÏÔÛÙÜÇ', 'EEEEAAIIOUUUC') LIKE 'ABC%'

With optimization, the translator can target a precomputed normalized column directly.

PostgreSQL
WHERE i."DescriptionFrNormalized" LIKE 'ABC%'

SQL Server
WHERE [i].[DescriptionFrNormalized] LIKE 'ABC%'

Oracle
WHERE "i"."DescriptionFrNormalized" LIKE 'ABC%'

If you only need normalized search on one language, a single computed column can do both extraction and normalization:

- Name: DescriptionFrNormalized
  DataColumnType: PredefinedComputedColumn
  MaxLength: 250
  NormalizedForSearch: true
  SourceColumnLanguage: fr
  SourceColumnName: Description

If you also need to filter, sort, or expose the extracted non-normalized value, then model both steps explicitly:

- Name: DescriptionFr
  DataColumnType: PredefinedComputedColumn
  MaxLength: 250
  SourceColumnLanguage: fr
  SourceColumnName: Description

- Name: DescriptionFrNormalized
  DataColumnType: PredefinedComputedColumn
  MaxLength: 250
  NormalizedForSearch: true
  SourceColumnName: DescriptionFr

If this search is central to the application, add an index on DescriptionFrNormalized.

This is usually the strongest predefined optimization pattern for localized search because it removes both runtime extraction and runtime normalization from the predicate.

The same SQL Server nuance applies here: DescriptionFrNormalized is still a column normalized for search, but SQL Server reaches that result through a collation-based expression rather than by materializing a separately rewritten uppercase accent-free string.

What to model first

When you design around indexing, the right order is:

  1. Start from the real OData filter or order by clause.
  2. Identify the runtime transformation in the generated SQL: language extraction, normalization, or both.
  3. Add the computed column that materializes that transformation ahead of time.
  4. Add the index on that computed column when the query is part of a hot path.

If the optimized SQL still wraps the source column in JSON_VALUE, jsonb_extract_path_text, TRANSLATE, UPPER, or a provider collation expression, your persistence model is still missing the computed column that matches the query shape.

The physical storage strategy for computed columns is resolved automatically by Neos according to the target database engine. It is not a metadata choice exposed to the developer. On SQL Server, Neos emits a computed column as PERSISTED only when that is required for the indexed scenario; on PostgreSQL, generated computed columns are emitted as STORED, and on Oracle they are emitted as VIRTUAL. You therefore model the computed column for the query shape you want to optimize, and let generation and migration choose the database-specific storage behavior.

See also