SESSIONPROPERTY

SESSIONPROPERTY is a built-in system function in SQL Server. It returns the current setting of the specified session setting property. This function is useful for retrieving the current configuration of a specific property related to SQL server sessions.

SESSIONPROPERTY( property )

  • deadlock_priority: Dictates the priority of the current session in a deadlock situation. Higher values indicate higher priority.
  • is_user_sid: Returns 1 if the session ID is that of a user; returns 0 if it’s not.
  • language: Sets the language of the current session. This influences the format of date, time, and currency.
  • lock_timeout: Defines the waiting period for the session when a lock is encountered. Measured in milliseconds.
  • max_precision: Sets the maximum precision of decimal, numeric, float, and real values in the current session.
  • sqlid: Determines the SQL identifier of the current session.
  • datefirst: Determines the starting day of the week in the context of the current session.
  • dateformat: Shows the order of the month, day, and year in date data types within the session.
  • authorization: Sets the name of the user or role that grants permissions for the current session.
  • ansi_null_dflt_on: Determines if the session follows the ANSI SQL-92 standard for null-able columns. If set to ON, columns will allow null values unless specified otherwise.
  • ansi_null_dflt_off: Indicates if the session adopts the ANSI SQL-92 standard for null-able columns. If set to ON, columns will not permit null values unless explicitly stated.
  • ansi_nulls: Sets if the session adheres to the ANSI SQL-92 standard for NULL comparison. If set to ON, any comparison with NULL results in a NULL.
  • ansipadding: Determines if the session follows ANSI padding rules. If set to ON, fixed-length variables will be filled with spaces.
  • ansi_warnings: Indicates if the session follows the ANSI SQL-92 standard for raising an error at runtime. If set to ON, warnings will be displayed for null value errors.
  • arithabort: Enables the session to terminate when an arithmetic error occurs.
  • arithignore: Allows the session to continue operating even if an arithmetic overflow or divide-by-zero error occurs.
  • concat_null_yields_null: If set to ON, the concatenation of a null value with a string yields a NULL result.
  • cursor_close_on_commit: If set to ON, open cursors close automatically when a transaction commits.
  • fmtonly: Allows retrieval of metadata without returning any rows.
  • nocount: If set to ON, the message indicating the number of rows affected by a TSQL command is suppressed.
  • quoted_identifier: Sets if SQL Server will follow the ISO rules concerning quoted identifiers.
  • textsize: Defines the maximum size of text, ntext, and image data returned in RESULT SETS.
  • transaction_isolation_level: Determines the current transaction isolation level for the session.

Example

SELECT SESSIONPROPERTY('USEROPTIONS')

Output

5488

Explanation

In the example, the SESSIONPROPERTY function retrieves the setting value of the current SQL Server session property ‘USEROPTIONS’. The returned value is always integer in nature, which in this case is 5488. Each bit of this integer value determines the ON or OFF status of a SET option.

For in-depth explanations and examples SQL keywords where you write your SQL, install our extension.