Settings ​

Settings apply at three scopes. Choose the scope according to whether a value should configure the service, persist with a database or affect one format operation.

Scopes ​

Scope	Interface	Lifetime
Server	XML configuration	Applied at startup; changes require restart.
Database	SET or a documented PRAGMA/catalog write	Persisted with the selected database and applied before later requests.
Format statement	SETTINGS clause	Applies only to that statement.

foreign_keys, recursive_triggers, user_version and application_id use PRAGMA or validated system.settings writes rather than SET.

Database management changes require autocommit and no active transactions in the selected database. Resource limits are sampled once per request, so a SET within a batch changes defaults for later requests.

Inspecting values ​

SHOW SETTINGS and system.settings report effective database values and read-only format defaults. Server-only XML options are not listed there. Read main.settings when the stored database value is required instead of its effective value.

Ceilings ​

A nonzero server resource limit is a ceiling. A larger saved database value, or a saved zero meaning no database limit, cannot bypass it. A smaller nonzero database value tightens the ceiling.

See database settings, format settings and server ceilings.