Indexed View Works in SSMS but NOT in Application

Recently, we have created indexed view and we have tested it in SSMS working fine. This view is developed for the .net application use. .net developer complain us that view is not working. After debugging we come to know that SET Option is differ in SSMS and application. Due to that it is working on SSMS and not working on the Application.

 There may be other reason like isolation level is mismatch but 90% is issue with Set Option.

Indexed views REQUIRE exact SET options.

Ø  SET ANSI_NULLS ON

Ø  SET ANSI_PADDING ON

Ø  SET ANSI_WARNINGS ON

Ø  SET CONCAT_NULL_YIELDS_NULL ON

Ø  SET QUOTED_IDENTIFIER ON

Ø  SET NUMERIC_ROUNDABORT OFF

Indexed views are ignored if even one SET option differs.

SET ANSI_NULLS ON

This Set option is used to how NULL comparisons behave.

NULL = NULL        -- FALSE when ANSI_NULLS ON

Indexed views depend on Stable predicate evaluation and Consistent grouping and Predictable join filtering. If one session treats NULL = NULL as TRUE and another as FALSE. Indexed row mapping breaks SQL Server cannot maintain two truth systems for the same index.

Refer: https://bageshkumarbagi-msbi.blogspot.com/2025/11/ansi-null-statement-in-sql-server.html

ANSI_PADDING ON

This set option is used to storage behavior of CHAR, VARCHAR, BINARY trailing spaces handling. Indexed views index stored values, not logical values.

'ABC' ≠ 'ABC   '   -- storage-level difference

If padding rules differ Index key binary representation changes and Uniqueness guarantees break.

Refer: https://bageshkumarbagi-msbi.blogspot.com/2025/11/ansi-padding-statement-in-sql-server.html

ANSI_WARNINGS ON

This set option is used to whether SQL Server raises errors/warnings for divide by zero or Overflow NULL in aggregate. Indexed views use Aggregates (SUM, COUNT_BIG) this is an arithmetic expression.

SUM (Amount / Quantity)

With warnings OFF Divide by zero silently ignored. Indexed view stores incorrect result this is unacceptable for persisted structures.

Refer : https://bageshkumarbagi-msbi.blogspot.com/2025/11/ansi-warning-statement-in-sql-server.html

ARITHABORT ON

Query termination on arithmetic errors.

Learn more: https://bageshkumarbagi-msbi.blogspot.com/2025/11/arithabort-statement-in-sql-server.html

Optimizer generates different execution plans and Cardinality estimation differs. Indexed view maintenance uses optimizer decisions.

If ARITHABORT differs: SQL Server cannot guarantee identical execution semantics. Plan reuse becomes unsafe. This is why SSMS defaults ARITHABORT ON, but many apps break indexed views.

CONCAT_NULL_YIELDS_NULL ON

This set option controls the string concatenation behavior with null.

'ABC' + NULL = NULL   -- when ON

'ABC' + NULL = 'ABC'   -- when OFF

If concatenation semantics differ, Indexed key values differ and uniqueness and lookup break.

Read here : https://bageshkumarbagi-msbi.blogspot.com/2025/11/concat-null-yield-null-statement-in-sql.html

QUOTED_IDENTIFIER ON

This set option control Whether double quotes represent as Identifiers or string literals.

NUMERIC_ROUNDABORT OFF

Whether rounding errors raise errors. Rounding behavior affects aggregates and Indexed numeric keys.

CAST(1.234567 AS DECIMAL(5,2))

If rounding rules differ and Stored index values differ and Maintenance logic fails. SQL Server wants consistent rounding, not session-specific behavior.