Global variables affected by set options

set options can customize the display of results, show processing statistics, and provide other diagnostic aids for debugging your Transact-SQL programs.

Table 15-6 lists the global variables that contain information about the session options controlled by the set command.

Table 15-6: Global variables containing session options

Global variable

Description

@@char_convert

Contains 0 if character set conversion is 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 it 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 Adaptive Server 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).

@@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.

@scan_parallel_degree

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

@@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.

@@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.

@@tranchained

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

The @@options global variable contains a hexadecimal representation of the session’s set options.Table 15-7 lists set options and values that work with @@options.

Table 15-7: 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

For more information on session options, see set in the Reference Manual: Commands.