select  setuser

Chapter 1: Commands

set

Description

Sets Adaptive Server query-processing options for the duration of the user’s work session; sets some options inside a trigger or stored procedure.

Syntax

set @variable = expression [, @variable = expression...] 
set ansinull {on | off}
set ansi_permissions {on | off}
set arithabort [arith_overflow | numeric_truncation]
	{on | off}
set arithignore [arith_overflow] {on | off}
set bulk array size number
set bulk batch size number
set {chained, close on endtran, nocount, noexec, parseonly, procid, 
	self_recursion, showplan, sort_resources} {on | off}
set char_convert {off | on [with {error | no_error}] |
	charset [with {error | no_error}]}
set cis_rpc_handling {on | off}
set [clientname client_name | clienthostname 
	host_name | clientapplname application_name]
set cursor rows number for cursor_name
set {datefirst number, dateformat format,
	language language}
set delayed_commit { on | off | default }
set fipsflagger {on | off}
set flushmessage {on | off}
set forceplan {on | off}
set identity_insert [database.[owner.]]table_name
	{on | off}
set identity_update table_name {on | off}
set lock { wait [ numsecs ] | nowait } 
set metrics_capture on/off
set offsets {select, from, order, compute, table,
	procedure, statement, param, execute} {on | off}
set option show
set parallel_degree number 
set plan {dump | load } [group_name] {on | off}
set plan exists check {on | off}
set plan for show
set plan optgoal allrows_mix | allrows_dss
set plan opttimeoutlimit number
set plan replace {on | off}
set prefetch [on|off]
set proc_output_params on | off
set proc_return_status on | off
set process_limit_action {abort | quiet | warning}
set proxy login_name
set quoted_identifier {on | off}
set repartition_degree number
set resource_granularity number
set role {"sa_role" | "sso_role" | "oper_role" | 
	role_name [with passwd "password"]} {on | off}
set {rowcount number, textsize number}
set scan_parallel_degree number 
set session authorization login_name
set statistics {io, subquerycache, time, plancost} {on | off}
set statistics simulate { on | off }
set strict_dtm_enforcement {on | off}
set string_rtruncation {on | off}
set textsize {number}
set transaction isolation level { 
	[ read uncommitted | 0 ] | 
	[ read committed | 1 ] |
	[ repeatable read | 2 ]| 
	[ serializable | 3 ] } 
set transactional_rpc {on | off}

Parameters

@variable

allows multiple variable assignments in one statement. The set @variable = expression command is an identical — and an alternative — command to select @variable = expression in Transact-SQL.

expression

includes constant, function, any combination of constants, and functions connected by arithmetic or bitwise operators, or a subquery.

ansinull

impacts on both aggregate and comparison behaviors. See “Aggregate behavior” for more information on aggregate and comparison behaviors.

ansi_permissions

determines whether ANSI SQL permission requirements for delete and update statements are checked. The default is off. Table 1-38 summarizes permission requirements:

Table 1-38: Permissions required for update and delete

Command

Permissions required with set ansi_permissions off

Permissions required with set ansi_permissions on

update

  • update permission on columns where values are being set

  • update permission on columns where values are being set

  • select permission on all columns appearing in where clause

  • select permission on all columns on right side of set clause

delete

  • delete permission on table

  • delete permission on table

  • select permission on all columns appearing in where clause

arithabort

determines how Adaptive Server behaves when an arithmetic error occurs. The two arithabort options, arithabort arith_overflow and arithabort numeric_truncation, handle different types of arithmetic errors. You can set each option independently or set both options with a single set arithabort on or set arithabort off statement.

arithignore arith_overflow

determines whether Adaptive Server displays a message after a divide-by-zero error or a loss of precision. By default, the arithignore option is set to off. This causes Adaptive Server to display a warning message after any query that results in numeric overflow. To have Adaptive Server ignore overflow errors, use set arithignore on. You can omit the optional arith_overflow keyword without any effect.

bulk array size number

establishes the number of rows that are buffered in local server memory before being transferred using the bulk copy interface.

Use this option only with Component Integration Services for transferring rows to a remote server using select into.

View your current setting using the @@bulkarraysize global variable.

number indicates the number of rows to buffer. If the rows being transferred contain text, unitext , image or java ADTs, then the bulk copy interface ignores the current setting for array size and uses a value of 1. Also, the array size actually used will never exceed the value of @@bulkbatchzise. If @@bulkbatchsize is smaller than array size, then the smaller value is used.

The initial value of the array size is inherited by new connections from the current setting of the configuration property cis bulk insert array size, which defaults to 50. Setting this value to 0 will reset the value to the default.

bulk batch size number

establishes the number of rows transferred to a remote server via select into proxy_table when the bulk interface is used. The bulk interface is available to all Adaptive Servers, as well as DirectConnect for Oracle version 12.5.1.

Use this option only with Component Integration Services for transferring rows to a remote server using select into.

View your current setting using the @@bulkbatchsize global variable.

The bulk interface allows a commit after a specified number of rows. This allows the remote server to free any log space being consumed by the bulk transfer operation, and enables the transfer of large data sets from one server to another without filling the transaction log.

The initial value of the batch size is inherited by new connections from the current setting of the configuration property cis bulk insert batch size, which by default is 0. A value of 0 indicates that no rows should be committed until after the last row is transferred.

chained

begins a transaction just before the first data retrieval or data modification statement at the beginning of a session and after a transaction ends. In chained mode, Adaptive Server implicitly executes a begin transaction command before the following statements: delete, fetch, insert, lock table, open, select, and update. You cannot execute set chained within a transaction.

char_convert

enables or disables character set conversion between Adaptive Server and a client. If the client is using Open Client DB-Library release 4.6 or later, and the client and server use different character sets, conversion is turned on during the login process and is set to a default based on the character set the client is using. You can also use set char_convert charset to start conversion between the server character set and a different client character set.

charset can be either the character set’s ID or a name from syscharsets with a type value of less than 2000.

set char_convert off turns conversion off so that characters are sent and received unchanged. set char_convert on turns conversion on if it is turned off. If character set conversion was not turned on during the login process or by the set char_convert command, set char_convert on generates an error message.

If you request character set conversion with set char_convert charset, and Adaptive Server cannot perform the requested conversion, the conversion state remains the same as it was before the request. For example, if conversion is set to off prior to the set char_convert charset command, conversion remains turned off if the request fails.

When the with no_error option is included, Adaptive Server does not notify an application when characters from Adaptive Server cannot be converted to the client’s character set. Error reporting is initially turned on when a client connects with Adaptive Server: if you do not want error reporting, you must turn it off for each session with set char_convert {on | charset} with no_error. To turn error reporting back on within a session, use set char_convert {on | charset} with error.

Whether or not error reporting is turned on, the bytes that cannot be converted are replaced with ASCII question marks (?).

See the System Administration Guide for a more complete discussion of error handling in character set conversion.

cis_rpc_handling

determines whether Component Integration Services handles outbound remote procedure call (RPC) requests by default.

clientapplname

assigns an application an individual name. This is useful for differentiating among clients in a system where many clients connect to Adaptive Server using the same application name. After you assign a new name to an application, it appears in the sysprocesses table under the new name.

clienthostname

assigns a host an individual name. This is useful for differentiating among clients in a system where many clients connect to Adaptive Server using the same host name. After you assign a new name to a host, it appears in the sysprocesses table under the new name.

clientname

assigns a client an individual name. This is useful for differentiating among clients in a system where many clients connect to Adaptive Server using the same client name. After you assign a new name to a user, they appear in the sysprocesses table under the new name.

close on endtran

causes Adaptive Server to close all cursors opened within a transaction at the end of that transaction. A transaction ends by the use of either the commit or rollback statement. However, only cursors declared within the scope that sets this option (stored procedure, trigger, and so on) are affected. For more information about cursor scopes, see the Transact-SQL User’s Guide.

For more information about the evaluated configuration, see the System Administration Guide.

cursor rows

causes Adaptive Server to return the number of rows for each cursor fetch request from a client application. The number can be a numeric literal with no decimal point or a local variable of type integer. If the number is less than or equal to zero, the value is set to 1. You can set the cursor rows option for a cursor, whether it is open or closed. However, this option does not affect a fetch request containing an into clause. cursor_name specifies the cursor for which to set the number of rows returned.

datefirst

uses numeric settings to specify the first day of the week. The us_english language default is Sunday. To set the first day of the week, use the following:

To set the first day of the week as

Use this setting

Monday

1

Tuesday

2

Wednesday

3

Thursday

4

Friday

5

Saturday

6

Sunday (us_english language default)

7

NoteRegardless of which day you set as the first day of the week, the value of that first day becomes 1. This value is not the same as the numeric setting you use in set datefirst n. For example, if you set Sunday as your first day of the week, its value is 1. If you set Monday as your first day of the week, Monday’s value becomes 1. If you set Wednesday as your first day of the week, Wednesday’s value becomes 1, and so on.

dateformat

sets the order of the date parts month/day/year for entering datetime , smalldatetime, date or time data. Valid arguments are mdy, dmy, ymd, ydm, myd, and dym. The us_english language default is mdy.

delayed_commit

determines when log records are written to disk. With the delayed_commit parameter set to true, the log records are asynchronously written to the disk and control is returned to the client without waiting for the IO to complete.

The session-level setting overrides any existing the database-level setting. Change delayed_commit to its default to revert back to the database-level setting.

NoteUse delayed_commit only after careful consideration of your application.

explicit_transaction_required

when set to true, causes any attempts to start an implicit transaction, or send an RPC to a remote server outside a transaction, to fail.All other commands succeed.

fipsflagger

determines whether Adaptive Server displays a warning message when Transact-SQL extensions to entry-level ANSI SQL are used. By default, Adaptive Server does not tell you when you use nonstandard SQL. This option does not disable SQL extensions. Processing completes when you issue the non-ANSI SQL command.

flushmessage

determines when Adaptive Server returns messages to the user. By default, messages are stored in a buffer until the query that generated them is completed or the buffer is filled to capacity. Use set flushmessage on to return messages to the user immediately, as they are generated.

forceplan

causes the query optimizer to use the order of the tables in the from clause of a query as the join order for the query plan. forceplan is generally used when the optimizer fails to choose a good plan. Forcing an incorrect plan can have severely bad effects on I/O and performance. For more information, see the Performance and Tuning Guide.

identity_insert

determines whether explicit inserts into a table’s IDENTITY column are allowed. (Updates to an IDENTITY column are never allowed.) This option can be used only with base tables. It cannot be used with views or set within a trigger.

Setting identity_insert table_name on allows the table owner, Database Owner, or System Administrator to explicitly insert a value into an IDENTITY column. Inserting a value into the IDENTITY column allows you to specify a seed value for the column or to restore a row that was deleted in error. Unless you have created a unique index on the IDENTITY column, Adaptive Server does not verify the uniqueness of the inserted value; you can insert any positive integer.

The table owner, Database Owner, or System Administrator can use the set identity_insert table_name on command on a table with an IDENTITY column to enable the manual insertion of a value into an IDENTITY column. However, only the following users can actually insert a value into an IDENTITY column, when identity_insert is on:

Setting identity_insert table_name off restores the default behavior by prohibiting explicit inserts to IDENTITY columns. At any time, you can use set identity_insert table_name on for a single database table within a session.

identity_update

With set identity_update on, you can explicitly update the value of the IDENTITY column on a table. identity_update changes the identity column value for the qualified rows. When identity_update is enabled, you can update the identity value to any value greater than 0. However, if the input value is greater than the identity burn max value, a new set of ID values is allocated, and the identity burn max value on the OAM page is updated accordingly. If update is included in a transaction, the new identity burn max value cannot be rolled back. You can use syb_identity to point to the identity column for update. For example:

update table_name set syb_identity = value
where clause

Adaptive Server does not check for duplicates entries or verify that entries are unique. You can update an existing value to any positive integer within the range allowed by the column's declared precision. You can check for duplicate entries by creating a unique index on the identity column

language

is the official name of the language that displays system messages. The language must be installed on Adaptive Server. The default is us_english.

metrics_capture on | off

enables the capture of QP metrics at the session level, set the capture to “on”. Query processing (QP) metrics identify and compare empirical metric values in query execution. When a query is executed, it is associated with a set of defined metrics that are the basis for comparison in QP metrics.

nocount

controls the display of rows affected by a statement. set nocount on disables the display of rows; set nocount off reenables the count of rows.

noexec

compiles each query but does not execute it. noexec is often used with showplan. After you set noexec on, no subsequent commands are executed (including other set commands) until you set noexec off.

lock wait

specifies the length of time that a command waits to acquire locks before aborting and returning an error.

numsecs

specifies the number of seconds a command is to wait to acquire a lock. Valid values are from 0 to 2147483647, the maximum value for an integer.

lock nowait

specifies that if a command cannot acquire a lock immediately, it returns an error and fails. set lock nowait is equivalent to set lock wait 0.

offsets

returns the position of specified keywords (with relation to the beginning of the query) in Transact-SQL statements. The keyword list is a comma-separated list that can include any of the following Transact-SQL constructs: select, from, order, compute, table, procedure, statement, param, and execute. Adaptive Server returns offsets if there are no errors. This option is used in Open Client DB-Library only.

option show

generates diagnostics output in text format. The following table lists the options for show. See Chapter 4, “Displaying Query Optimization Strategies And Estimates,” in Query Optimizer for more information.

Options for set option

Description

show <normal/brief/long/on/off>

Basic syntax common to all modules

show_lop <normal/brief/long/on/off>

Shows the logical operators (scans, joins, etc.) used

show_managers <normal/brief/long/on/off>

Shows data structure managers used during optimization.

show_log_props <normal/brief/long/on/off>

Shows the logical properties (row count, selectivity, etc.) evaluated.

show_parallel <normal/brief/long/on/off>

Shows details of parallel query optimization

show_histograms <normal/brief/long/on/off>

Shows the processing of histograms associated with SARG/Join columns

show_abstract_plan <normal/brief/long/on/off>

Shows the details of an abstract plan

show_search_engine <normal/brief/long/on/off>

Shows the details of the join ordering algorithm

show_counters <normal/brief/long/on/off>

Shows the optimization counters

show_best_plan <normal/brief/long/on/off>

Shows the details of the best query plan selected by the optimizer

show_pio_costing <normal/brief/long/on/off>

Shows estimates of physical input/output (reads/writes from/to the disk)

show_lio_costing <normal/brief/long/on/off>

Shows estimates of logical input/output (reads/writes from/to memory)

show_elimination <normal/brief/long/on/off>

Shows partition elimination

show_missing_stats <normal/brief/long/on/off>

Shows details of useful statistics missing from SARG/Join columns

parallel_degree

specifies an upper limit for the number of worker processes used in the parallel execution of a query. This number must be less than or equal to the number of worker processes per query, as set by the max parallel degree configuration parameter. The @@parallel_degree global variable stores the current setting.

parseonly

checks the syntax of each query and returns any error messages without compiling or executing the query. Do not use parseonly inside a stored procedure or trigger.

plan

introduces an abstract plan command. For more information, see Chapter 30, “Creating and Using Abstract Plans,” in the Performance and Tuning Guide.

dump

enables or disables capturing abstract plans for the current connection. If a group_name is not specified, the plans are stored in the default group, ap_stdout.

load

enables or disables loading abstract plans for the current connection. If a group_name is not specified, the plans are loaded from the default group, ap_stdin.

group_name

is the name of the abstract plan group to use for loading or storing plans.

exists check

when used with set plan load, stores hash keys for up to 20 queries from an abstract plan group in a per-user cache.

optplan number

sets the optimization goal. See Chapter 1, “Understanding Query Processing in Adaptive Server” in Query Processor for more information about optimization plans.

allrows_mix

is the default optmization goal, and the most useful goal in a mixed-query environment. It balances the needs of OLTP and DSS query environments.

opttimeoutlimit number

sets the timeout at the session level, where n is any integer between 0 and 1000. See Chapter 1, “Understanding Query Processing in Adaptive Server” in Query Processor for more information about optimization plans.

allrows_dss

is the most useful goal for operational DSS queries of medium to high complexity. Currently, this goal is provided on an experimental basis.

for show

generates an XML document for the diagnostic output. The following table lists the valid values for show. See Chapter 4, “Displaying Query Optimization Strategies And Estimates,” in Query Processor for more information.

Command

Definition

show_exec_xml

Gets the compiled plan output in XML, showing each of the query plan operators.

show_execio_xml

Gets the plan output along with estimated and actual IOs. This also includes the query text.

show_opt_xml

Gets optimizer diagnostic output, which shows all of the different components like logical operators, output from the managers, some of the search engine diagnostics, and the best query plan.

show_lop_xml

Gets the output logical operator tree in XML.

show_managers_xml

Shows the output of the different component managers during the preparation phase of the query optimizer.

show_log_props_xml

Shows the logical properties for a given equivalence class (one or more group of relations in the query).

show_parallel_xml

Shows the diagnostics related to the optimizer while generating parallel query plans.

show_histograms_xml

Shows diagnostics related to histograms and the merging of histograms.

show_abstract_plan_xml

Shows the AP generation/application.

show_search_engine_xml

Shows the search engine related diagnostics.

show_counters_xml

Shows plan object construction/destruction counters.

show_best_plan_xml

Shows the best plan in XML.

show_pio_costing_xml

Shows actual PIO costing in XML.

show_lio_costing_xml

Shows actual LIO costing in XML.

show_elimination_xml

Shows partition elimination in XML.

client

When specified, output goes to the client.

message

When specified, output goes to an internal message buffer.

replace

enables or disables replacing existing abstract plans during plan capture mode. By default, plan replacement is off.

prefetch

enables or disables large I/Os to the data cache.

proc_output_params

controls sending of output parameters that a stored procedure generates back to the client. set proc_output_params off suppresses sending the output parameters back to the client. The default for this parameter is on.

proc_return_status

controls sending of a return status TDS token back to the client. set proc_return_status off suppresses sending the return status token to the client, and isql client does not display the (return status = 0) message. The default for this parameter is on.

WARNING! If the client application that executes a procedure relies on the success or failure of the procedure based on the return status, then do not use the set proc_return_status off option.

process_limit_action

specifies whether Adaptive Server executes parallel queries when an insufficient number of worker processes is available. Under these circumstances, when process_limit_action is set to quiet, Adaptive Server silently adjusts the plan to use a degree of parallelism that does not exceed the number of available processes. If process_limit_action is set to warning when an insufficient number of worker processes are available, Adaptive Server issues a warning message when adjusting the plan; and if process_limit_action is set to abort, Adaptive Server aborts the query and issues an explanatory message an insufficient number of worker processes are available.

procid

returns the ID number of the stored procedure to Open Client DB-Library/C (not to the user) before sending rows generated by the stored procedure.

proxy

allows you to assume the permissions, login name, and suid (server user ID) of login_name. For login_name, specify a valid login from master..syslogins, enclosed in quotation marks. To revert to your original login name and suid, use set proxy with your original login_name.

NoteWithout explicit permission, neither the “sa_role” nor the “sso_role” can issue the set proxy login_name command. To use set proxy login_name, any user, including the System Security Officer, must have permission explicitly granted by the System Security Officer. ­

See “Using proxies” for more information.

quoted_identifier

determines whether Adaptive Server recognizes delimited identifiers within double quotation marks. By default, quoted_identifier is off and all identifiers must either:

If you use set quoted_identifier on, double quotation marks behave the way brackets do, and you can use table, view, and column names that begin with a nonalphabetic character, including characters that would not otherwise be allowed, or are reserved words, by enclosing the identifiers within double quotation marks. Delimited identifiers cannot exceed 28 bytes, may not be recognized by all front-end products, and may produce unexpected results when used as parameters to system procedures.

When quoted_identifier is on, all character strings enclosed within double quotes are treated as identifiers. Use single quotes around character or binary strings.

repartition_degree number

the maximum degree to which any intermediate data stream is re-partitioned for semantic purposes. See Chapter 2, “Parallel Query Processing” in Query Processor for more information about setting the value of max repartition degree for a session.

resource_granularity number

overrides the global value max resource granularity and sets it to a session specific value, which influences whether Adaptive Server uses memory-intensive operation or not. See Chapter 2, “Parallel Query Processing” in Query Processor for more information.

role

turns the specified role on or off during the current session. When you log in, all system roles that have been granted to you are turned on. Use set role role_name off to turn a role off, and set role role_name on to turn it back on again, as needed. System roles are “sa_role”, “sso_role”, and “oper_role”. If you are not a user in the current database, and if there is no “guest” user, you cannot set sa_role off, because there is no server user ID for you to assume.

role_name

is the name of any user-defined role created by the System Security Officer. User-defined roles are not turned on by default. To set user-defined roles to activate at login, the user or the System Security Officer must use set role on.

with passwd

specifies the password to activate the role. If a user-defined role has an attached password, you must specify the password to activate the role.

rowcount

causes Adaptive Server to stop processing the query (select, insert, update, or delete) after the specified number of rows are affected. The number can be a numeric literal with no decimal point or a local variable of type integer. To turn this option off, use:

set rowcount 0

You can determine the current value for set rowcount with the @@setrowcount global variable. For example:

select @@setrowcount
____________
           37
scan_parallel_degree

specifies the maximum session-specific degree of parallelism for hash-based scans (parallel index scans and parallel table scans on nonpartitioned tables). This number must be less than or equal to the current value of the max scan parallel degree configuration parameter. The @@scan_parallel_degree global variable stores the current setting.

self_recursion

determines whether Adaptive Server allows triggers to cause themselves to fire again (this is called self recursion). By default, Adaptive Server does not allow self recursion in triggers. You can turn this option on only for the duration of a current client session; its effect is limited by the scope of the trigger that sets it. For example, if the trigger that sets self_recursion on returns or causes another trigger to fire, this option reverts to off. This option works only within a trigger and has no effect on user sessions.

session authorization

is identical to set proxy, with this exception: set session authorization follows the SQL standard, while set proxy is a Transact-SQL extension.

showplan

generates a description of the processing plan for the query. The results of showplan are of use in performance diagnostics. showplan does not print results when it is used inside a stored procedure or trigger. For parallel queries, showplan output also includes the adjusted query plan at runtime, if applicable. For more information, see the Performance and Tuning Guide.

sort_resources

generates a description of the sorting plan for a create index statement. The results of sort_resources are of use in determining whether a sort operation is done serially or in parallel. When sort_resouces is on, Adaptive Server prints the sorting plan but does not execute the create index statement. For more information, see Chapter 24, “Parallel Sorting,” in the Performance and Tuning Guide.

statistics io

displays the following statistics information for each table referenced in the statement:

For each command, statistics io displays the number of buffers written.

If Adaptive Server has been configured to enforce resource limits, statistics io also displays the total I/O cost. For more information, see Chapter 34, “Using the set statistics Commands” in the Performance and Tuning Guide.

plancost

displays the query statistics in a tree format.

statistics subquerycache

displays the number of cache hits, misses, and the number of rows in the subquery cache for each subquery.

statistics time

displays the amount of time Adaptive Server used to parse and compile for each command. For each step of the command, statistics time displays the amount of time Adaptive Server used to execute the command. Times are given in milliseconds and timeticks, the exact value of which is machine-dependent.

statistics simulate

specifies that the optimizer should use simulated statistics to optimize the query.

strict_dtm_enforcement

determines whether the server propagates transactions to servers that do not support Adaptive Server transaction coordination services. The default value is inherited from the value of the strict dtm enforcement configuration parameter.

string_rtruncation

determines whether Adaptive Server raises a SQLSTATE exception when an insert or update command truncates a char, unichar, varchar or univarchar string. If the truncated characters consist only of spaces, no exception is raised. The default setting, off, does not raise the SQLSTATE exception, and the character string is silently truncated.

textsize

specifies the maximum size in bytes of text, unitext, or image type data that is returned with a select statement. The @@textsize global variable stores the current setting. To reset textsize to the default size (32K), use:

set textsize 0

The default setting is 32K in isql. Some client software sets other default values.

transaction isolation level

sets the transaction isolation level for your session. After you set this option, any current or future transactions operate at that isolation level.

read uncommitted | 0

scans at isolation level 0 do not acquire any locks. Therefore, the result set of a level 0 scan may change while the scan is in progress. If the scan position is lost due to changes in the underlying table, a unique index is required to restart the scan. In the absence of a unique index, the scan may be aborted.

By default, a unique index is required for a level 0 scan on a table that does not reside in a read-only database. You can override this requirement by forcing the Adaptive Server to choose a nonunique index or a table scan, as follows:

select * from table_name (index table_name)

Activity on the underlying table may cause the scan to be aborted before completion.

read committed | 1

By default, the Adaptive Server transaction isolation level is read committed or 1, which allows shared read locks on data.

repeatable read | 2

prevents nonrepeatable reads.

serializable | 3

specify isolation level 3, Adaptive Server applies a holdlock to all select and readtext operations in a transaction, which holds the queries’ read locks until the end of that transaction. If you also set chained mode, that isolation level remains in effect for any data retrieval or modification statement that implicitly begins a transaction.

transactional_rpc

controls the handling of remote procedure calls. If this option is set to on, when a transaction is pending, the RPC is coordinated by Adaptive Server. If this option is set to off, the remote procedure call is handled by the Adaptive Server site handler. The default value is inherited from the value of the enable xact coordination configuration parameter.

Examples

Example 1

Tells Adaptive Server to evaluate NULL-valued operands of equality (=) and inequality (!=) comparisons and aggregate functions in compliance with the entry level ANSI SQL standard:

set ansinull on

When you use set ansinull on, aggregate functions and row aggregates raise the following SQLSTATE warning when Adaptive Server finds null values in one or more columns or rows:

Warning - null value eliminated in set function

If the value of either the equality or the inequality operands is NULL, the comparison’s result is UNKNOWN. For example, the following query returns no rows in ansinull mode:

select * from titles where price = null

If you use set ansinull off, the same query returns rows in which price is NULL.

Example 2

Activates character set conversion, setting it to a default based on the character set the client is using. Adaptive Server also notifies the client or application when characters cannot be converted to the client’s character set:

set char_convert on with error

Example 3

Specifies that Component Integration Services handles outbound RPC requests by default:

set cis_rpc_handling on

Example 4

Assigns this user:

set clientname 'alison'
set clienthostname 'money1'
set clientapplname 'webserver2'

Example 5

Returns five rows for each succeeding fetch statement requested by a client using test_cursor:

set cursor rows 5 for test_cursor

Example 6

Inserts a value of 100 into the IDENTITY column of the stores_south table, then prohibits further explicit inserts into this column. Note the use of the syb_identity keyword; Adaptive Server replaces the keyword with the name of the IDENTITY column:

set identity_insert stores_south on
go
insert stores_south (syb_identity)
values (100)
go
set identity_insert stores_south off
go

Example 7

Enables idenity_update and updates tables with values 1 and 10, respectively, then disables identity_update:

set identity_update t1 on
update t1 set c2 = 10 where c1 =1
select * from t1
c1              c2
_ _ _ _ _       _ _ _ _
1                 10

set identity_update t1 off

Example 8

Tells Adaptive Server to display a warning message if you use a Transact-SQL extension:

set fipsflagger on

Then, if you use nonstandard SQL, like this:

use pubs2
go

Adaptive Server displays:

SQL statement on line number 1 contains Non-ANSI text. The error is caused due to the use of use database.

Example 9

Subsequent commands in the session or stored procedure return an error and fail if they cannot get requested locks immediately:

set lock nowait

Example 10

Subsequent commands in the current session or stored procedure wait indefinitely long to acquire locks:

set lock wait

Example 11

Subsequent commands in the session or stored procedure wait 5 seconds to acquire locks before generating an error message and failing:

set lock wait 5

Example 12

Enables capturing abstract plans to the dev_plans group:

set plan dump dev_plans on

Example 13

Enables loading of abstract plans from the dev_plans group for queries in the current session:

set plan load dev_plans on

Example 14

Suppresses the output of parameter information:

1> create procedure sp_pout (@x int output) as select 
    @x = @x + 1
2> go

1> set proc_output_params off
2> go
1> declare @x int
2> select @x = 1
3> exec sp_pout @x output
4> print "Value of @x returned from sproc is: %1!", @x
5> go

(1 row affected)
(return status = 0)

Value of @x returned from sproc is: 1 

If you do not perform set proc_output_params off, the output after (return status = 0) includes the following:

Return parameters:

 ----------- 
            2

Example 15

Suppresses the output of both parameters and the return status TDS token:

set proc_output_params OFF
go

set proc_return_status OFF
go

declare @x int
select @x = 2
exec sp_pout @x output
print "Value of @x returned from sproc is: %1!", @x
go(1 row affected)
Value of @x returned from sproc is: 2
(1 row affected)

In addition, you can also suppress the lines reporting the number of rows affected to generate output with no extra messages using the set nocount on option before running this batch.

Example 16

The user executing this command now operates within the server as the login “mary” and Mary’s server user ID:

set proxy "mary"

Example 17

For each insert, update, delete, and select statement, Adaptive Server stops processing the query after it affects the first four rows. For example:

select title_id, price from titles
title_id  price
--------  ----------
BU1032         19.99
BU1111         11.95
BU2075          2.99
BU7832         19.99

(4 rows affected)
set rowcount 4

Example 18

Tells Adaptive Server to treat any character string enclosed in double quotes as an identifier. The table name “!*&strange_table” and the column name “emp’s_name” are legal identifier names while quoted_identifier is on:

set quoted_identifier on
go
create table "!*&strange_table" 
     ("emp’s_name" char(10), 
     age int)
go
set quoted_identifier off
go

Example 19

Treats a character string enclosed in brackets as an identifier. The table name [!*&strange_table] and the column name [emp’s_name] are legal identifier names because they are enclosed in brackets, even though quoted_identifier is off:

set quoted_identifier off
go
create table [!*&strange_table] 
     ([emp’s_name] char(10), 
     age int)
go

See “Delimited identifiers” for usage information about bracket identifiers.

Example 20

Activates the “doctor” role. This command is used by users to specify the roles they want activated:

set role doctor_role on

Example 21

Deactivates the user’s System Administrator role for the current session:

set role "sa_role" off

Example 22

Activates the “doctor” role when the user enters the password:

set role doctor_role with passwd "physician" on

Example 23

Deactivates the “doctor” role:

set role doctor_role off

Example 24

Specifies a maximum degree of parallelism of 4 for parallel index scans and parallel table scans on nonpartitioned tables:

set scan_parallel_degree 4

Example 25

An alternative way of stating example 5:

set session authorization "mary"

Example 26

For each query, returns a description of the processing plan, but does not execute it:

set showplan, noexec on 
go 
select * from publishers 
go

Example 27

Displays the statistics for the query in a tree format:

set statistics plancost on
select * from authors

au_id       au_lname     au_fname     phone        address
city    state       country          postalcode
----------- -----------  -----------  ------------ ---------------------
------------ -----  ----------      ------------------------------------172-32-1176  White        Johnson      408 496-7223 10932 Bigge Rd.
Menlo Park      CA     USA         94025
213-46-8915  Green        Marjorie     415 986-7020  309 63rd St. #411
Oakland         CA     USA          94618

. . . 

998-72-3567  Ringer        Albert       801 826-0752   67 Seventh Av.
Salt Lake City  UT      USA          84152

==================== Lava Operator Tree ====================

            Emit
            (VA = 1)
            23 rows est: 23
            cpu: 0

/
TableScan
authors
(VA = 0)
23 rows est: 23
lio: 1 est: 2
pio: 0 est: 2

============================================================

(23 rows affected)

Example 28

Causes Adaptive Server to generate an exception when truncating a char, unichar, or nchar string:

set string_rtruncation on

If an insert or update statement would truncate a string, Adaptive Server displays:

string data, right truncation

Example 29

Sets the limit on text, unitext, or image data returned with a select statement to 100 bytes:

set textsize 100

Example 30

Specifies that when a transaction is pending, the RPC is handled by the Component Integration Services access methods rather than by the Adaptive Server site handler:

set transactional_rpc on

Example 31

All subsequent queries in the session run at the repeatable reads transaction isolation level:

set transaction isolation level 2

Example 32

Implements read-locks with each select statement in a transaction for the duration of that transaction:

set transaction isolation level 3

Usage

fipsflagger, string_rtruncation, ansinull, ansi_permissions, arithabort, and arithignore affect aspects of Adaptive Server error handling and compliance to SQL standards.


Aggregate behavior

ansinull determines whether evaluation of NULL-valued operands in aggregate functions is compliant with the ANSI SQL standard. If you use set ansinull on, Adaptive Server generates a warning when an aggregate function eliminates a null-valued operand from the calculation.

For example, if you perform the following query on the titles table with set ansinull off (the default value):

select avg(total_sales) from titles

Adaptive Server returns:

-----------
       6090

However, if you perform the same query with set ansinull on, Adaptive Server returns the following:

1> use pubs2
2> go
1> select avg(total_sales) from titles
2> go
 -----------
         6090
(1 row affected)
1> set ansinull on
2> go
1> select avg(total_sales) from titles
2> go
 -----------
         6090
Warning - null value eliminated in set function(1 row affected)

This message indicates that some entries in total_sales contain NULL instead of a real amount, so you do not have complete data on total sales for all books in this table. However, of the available data, the value returned is the highest.


Comparison behavior

The SQL standard requires that if either one of the two operands of an equality comparison is NULL, the result is UNKNOWN. Transact-SQL treats NULL values differently. If one of the operands is a column, parameter, or variable, and the other operand is the NULL constant or a parameter or variable whose value is NULL, the result is either TRUE or FALSE:

The ANSI rule for the where and on clauses return rows that are true, and rejects rows that are both false and unknown.

The ANSI rule for a check constraint rejects values that are false. For this reason, unknown or true results are not rejected.

If you:

Instead, use the ANSI IS NULL or IS NOT NULL syntax to prevent from having unexpected results.


Delimited identifiers

When the quoted_identifier option is set to on, you do not need to use double quotes around an identifier if the syntax of the statement requires that a quoted string contain an identifier. For example:

set quoted_identifier on
create table "1one" (c1 int)

However, object_id requires a string, so you must include the table name in quotes to select the information:

select object_id('1one')
-----------------------
  896003192

You can include an embedded double quote in a quoted identifier by doubling the quote:

create table "embedded""quote" (c1 int)

However, there is no need to double the quote when the statement syntax requires the object name to be expressed as a string:

select object_id('embedded"quote')

Bracketed identifiers Adaptive Server supports an alternative to quoted identifiers that uses brackets to surround an identifier. The behavior of bracketed identifiers is identical to that of quoted identifiers, with the exception that you do not have to use set quoted_identifier on to use them.

When you use bracketed identifiers instead of quoted identifiers to create objects, your objectname should have at least one valid character, such as:

All trailing spaces are removed from the objectname, so the following are all treated identically:

[tab1<space><space>]
[tab1<space><space>]
[tab1]
[tab1<space><space><space>]
tab1

This applies to all objects that can be created using bracketed identifiers.

The following are restrictions when using delimited identifiers in Adaptive Server:


Roles and set options


Distributed transactions, CIS, and set options


Using proxies

NoteWithout explicit permission, neither the “sa_role” nor the “sso_role” can issue the set proxy login_name command. To use set proxy login_name, any user, including the System Security Officer, must have permission explicitly granted by the System Security Officer. ­


Using lock wait


Repeatable-reads transaction isolation level


Using simulated statistics


Global variables affected by set options


Using fipsflagger with Java in the database

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

The ANSI SQL standard specifies behavior that differs from Transact-SQL behavior in earlier Adaptive Server versions. Compliant behavior is enabled by default for all Embedded-SQL precompiler applications. Other applications needing to match this standard of behavior can use the set options listed in Table 1-40.

Table 1-40: Options to set for entry level ANSI SQL compliance

Option

Setting

ansi_permissions

on

ansinull

on

arithabort

off

arithabort numeric_truncation

on

arithignore

off

chained

on

close on endtran

on

fipsflagger

on

quoted_identifier

on

string_rtruncation

on

transaction isolation level

3

Permissions

In general, set permission defaults to all users and no special permissions are required to use it. Exceptions include set role, set proxy, and set session authorization.

To use set role, a System Administrator or System Security Officer must have granted you the role. If you gain entry to a database only because you have a certain role, you cannot turn that role off while you are using the database. For example, if you are not normally authorized to use a database info_plan, but you use it as a System Administrator, Adaptive Server returns an error message if you try to set sa_role off while you are still in info_plan.

To use set proxy or set session authorization, you must have been granted permission by a System Security Officer.

Auditing

Values in event and extrainfo columns of sysaudits are:

Event

Audit option

Command or access audited

Information in extrainfo

88

security

set proxy or set session authorization

  • Roles – Current active roles

  • Keywords or options – NULL

  • Previous value – Previous suid

  • Current value – New suid

  • Other information – NULL

  • Proxy information – Original login name, if set proxy or set session authorization had no parameters; otherwise, NULL.

See also

Commands create trigger, fetch, grant, insert, lock table, revoke, set

Functions convert

Utilities isql, optdiag





Copyright © 2005. Sybase Inc. All rights reserved. setuser

View this book as PDF