Table of Contents SQL Server: Changes to Default Behavior Indexed Views Stored Procedure Handling Isolation Levels Quoting and Identifiers LocalDB / Development Mode Session Settings Summary Why These Are Not SQL Server Defaults Related Pages

pengdows.crud provides full support for Microsoft SQL Server, including LocalDB and Express editions. Several specific behaviors and session settings are applied to ensure compatibility, performance, and correctness.

SQL Server enforces strict session settings in order to use indexed views. Without these, queries can silently be rejected from using indexes. pengdows.crud sets these options automatically using the CheckForSqlServerSettings() method.

The following session options are required:

SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
SET ANSI_WARNINGS ON;
SET CONCAT_NULL_YIELDS_NULL ON;
SET ARITHABORT ON;
SET NUMERIC_ROUNDABORT OFF;

These settings are described in greater detail in this article: Working Around Issues When Using SQL Server Indexed Views

pengdows.crud uses `CommandType.StoredProcedure` to detect when to generate `EXEC` statements. This is handled transparently.

• `SqlContainer` will inject `EXEC procName` when the command type is set.
• Parameters are passed positionally or named depending on SQL Server's support.

SQL Server defaults to `READ COMMITTED`, which pengdows.crud uses as its minimum for write operations.

• Read-only transactions can request `REPEATABLE READ` or higher.
• Transaction context will automatically raise lower isolation levels.

• Object identifiers are wrapped " double quotes as that is the default for most DBs and it is enabled using SET QUOTED_IDENTIFIERS ON
• Named parameters use the @ prefix: @Id, @Email

• When using SQL Server Express LocalDB, use KeepAlive mode to prevent database unload.

The following settings are applied automatically by `CheckForSqlServerSettings()` to ensure ANSI compliance and compatibility with indexed views, computed columns, and deterministic expressions:

SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
SET ANSI_WARNINGS ON;
SET CONCAT_NULL_YIELDS_NULL ON;
SET ARITHABORT ON;
SET NUMERIC_ROUNDABORT OFF;

Most of these settings are not defaults in SQL Server for legacy reasons. They exist to maintain backward compatibility with older T-SQL behaviors:

Setting	Why You Need It	Risks / Unexpected Behavior
SET ANSI_NULLS ON	Required for indexed views, computed columns, ANSI compliance	If OFF, NULL != NULL returns true, which breaks logic and indexing
SET QUOTED_IDENTIFIER ON	Enables ANSI quoting for identifiers, required by views, triggers	If OFF, disables use of double quotes for identifiers
SET ANSI_WARNINGS ON	Raises errors for divide-by-zero, null in aggregates, required for deterministic functions	If OFF, you may silently ignore errors that cause logic bugs
SET CONCAT_NULL_YIELDS_NULL ON	Makes NULL + 'abc' return NULL instead of 'abc'	Legacy code may expect concatenation with NULL to succeed
SET ARITHABORT ON	Required for indexed views and correct plan caching	If OFF, arithmetic errors like divide-by-zero may not abort the batch
SET NUMERIC_ROUNDABORT OFF	Prevents minor numeric rounding from aborting queries	If ON, rounding errors might throw in math-heavy queries

These settings align SQL Server behavior with ANSI SQL and make it act more like PostgreSQL, Oracle, or other standards-compliant databases.

You can read more on the rationale and details in this CodeProject article.

Settings applied at connection open:

SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
SET ANSI_WARNINGS ON;
SET CONCAT_NULL_YIELDS_NULL ON;
SET ARITHABORT ON;
SET NUMERIC_ROUNDABORT OFF;

These ensure compatibility with:

• Indexed views
• Computed columns
• Deterministic functions

• Supported Databases
• DbMode Documentation
• Transactions
• SqlContainer
• EntityHelper<TEntity, TRowID>