Global Variables Affected by set Options

Global variables that contain information about the session options controlled by the set command.

Global Variables Containing Session Options

Global Variable

Description

@@char_convert

Contains 0 if character set conversion not in effect. Contains 1 if character set conversion is in effect.

@@client_csexpansion

Returns the expansion factor used when converting from the server character set to the client character set. For example, if @@client_csexpansion contains a value of 2, a character in the server character set could take up to twice the number of bytes after translation to the client character set.

@@cursor_rows

A global variable designed specifically for scrollable cursors. Displays the total number of rows in the cursor result set. Returns the value -1:

@@datefirst

Set using set datefirst n where n is a value between 1 and 7. Returns the current value of @@datefirst, indicating the specified first day of each week, expressed as tinyint.

The default value in SAP ASE is Sunday (based on the us_language default), which you set by specifying set datefirst 7. See the datefirst option of the set command for more information on settings and values.

@@isolation

Contains the current isolation level of the Transact-SQL program. @@isolation takes the value of the active level (0, 1, or 3).

@@lock_timeout

Set using set lock wait n. Returns the current lock_timeout setting, in milliseconds. @@lock_timeout returns the value of n. The default value is no timeout. If no set lock wait n is executed at the beginning of the session, @@lock_timeout returns -1.

@@options

Contains a hexadecimal representation of the session’s set options.

@@parallel_degree

Contains the current maximum parallel degree setting.

@@rowcount

Contains the number of rows affected by the last query. @@rowcount is set to 0 by any command that does not return rows, such as an if, update, or delete statement. With cursors, @@rowcount represents the cumulative number of rows returned from the cursor result set to the client, up to the last fetch request.

@@rowcount is updated even when nocount is on.

@@scan_parallel_degree

Contains the current maximum parallel degree setting for nonclustered index scans.

@@textsize

Contains the limit on the number of bytes of text, unitext or image data a select returns. Default limit is 32K bytes for isql; the default depends on the client software. Can be changed for a session with set textsize.

If you use enable surrogate processing, Unicode surrogates (two 16-bit values) are returned as single characters, even though the actual return size may be less than the @@text size value.

@@tranchained

Contains the current transaction mode of the Transact-SQL program. @@tranchained returns 0 for unchained or 1 for chained.

set Options and Values for @@options

Numeric value

Hexidecimal value

set option

4

0x04

showplan

5

0x05

noexec

6

0x06

arithignore

8

0x08

arithabort

13

0x0D

control

14

0x0E

offsets

15

0x0F

statistics io and statistics time

16

0x10

parseonly

18

0x12

procid

20

0x14

rowcount

23

0x17

nocount

77

0x4D

opt_sho_fi

78

0x4E

select

79

0x4F

set tracefile