SQL SESSION

What does SQL SESSION do?

The logical context that preserves connection-level settings, variables, and transaction state for the lifetime of a client’s connection to a database.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL SESSION: PostgreSQL, MySQL, MariaDB, SQL Server, Oracle, DB2, Firebird, SAP HANA. SQLite offers only minimal PRAGMA session state.

SQL SESSION Full Explanation

A SQL SESSION begins when a client authenticates to the database and ends when the connection is closed or times out. Within this scope the server stores session-level data: configuration parameters, temporary tables, prepared statements, user or system variables, and the current transaction. Commands such as SET SESSION, SET LOCAL, or vendor-specific APIs read or modify these values. Every new connection starts with default settings, isolating one session’s changes from others. Sessions persist across multiple statements but never survive disconnects. Understanding sessions is essential for tuning performance (e.g., work_mem in PostgreSQL), enforcing security contexts (e.g., multi-tenant SESSION_CONTEXT in SQL Server), and guaranteeing repeatable behavior during batch jobs.

SQL SESSION Syntax

-- Standard SQL
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- PostgreSQL
SET SESSION search_path TO sales, public;
RESET SESSION AUTHORIZATION;

-- MySQL
SET SESSION sql_mode = 'STRICT_ALL_TABLES';

-- SQL Server
EXEC sys.sp_set_session_context @key = 'tenant_id', @value = 42;

SQL SESSION Parameters

value - any Literal or expression evaluated and stored for the session

Example Queries Using SQL SESSION

-- Change default schema path for the current PostgreSQL session
SET SESSION search_path TO analytics, public;
SELECT current_schema;  -- returns 'analytics'

-- Enforce strict SQL mode only for this MySQL session
SET SESSION sql_mode = 'STRICT_ALL_TABLES';
SELECT @@session.sql_mode;

-- Store tenant id in SQL Server session context
EXEC sys.sp_set_session_context @key = 'tenant_id', @value = 42;
SELECT SESSION_CONTEXT(N'tenant_id') AS tenant_id;

Expected Output Using SQL SESSION

All subsequent statements in the connection run with the new parameter values until they are changed again or the session ends
No other connections are affected

Use Cases with SQL SESSION

Override global settings temporarily during ETL jobs
Store per-user metadata such as tenant id or feature flags
Tune memory or timeout parameters for long analytical queries
Ensure consistent transaction isolation for a batch of statements

Common Mistakes with SQL SESSION

Assuming session variables persist after disconnecting
Attempting to modify parameters that require superuser rights
Confusing user-defined variables (@var) with session variables in MySQL
Forgetting to reset session changes, leading to unexpected behavior in pooled connections

First Introduced In

SQL-92 introduced SET SESSION CHARACTERISTICS

Frequently Asked Questions

What is stored inside a SQL session?

Session scope can include runtime parameters, temporary tables, prepared statements, cursors, and user-defined variables. Vendors may add more items such as role information or client tags.

Do session settings survive a server restart?

No. When the server drops all connections during a restart, every session ends. All session data is lost unless saved elsewhere.

Is changing a session parameter safe in connection pools?

Yes, but always reset the parameter or use SET LOCAL inside a transaction to avoid leaking settings to the next borrower of the same pooled connection.

How can I list current session parameters?

Most databases provide an introspection view. Example: SHOW ALL in PostgreSQL, SELECT @@GLOBAL and @@SESSION variables in MySQL, or sys.dm_exec_sessions in SQL Server.