Sets Adaptive Server query-processing options for the duration of the user’s work session; sets some options inside a trigger or stored procedure.
set advanced_aggregation on/off
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 builtin_date_strings number
set {chained, close on endtran, nocount, noexec, parseonly, 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 compression {on | off | default}
set cursor rows number for cursor_name
set {datefirst number, dateformat format, language language}
set delayed_commit {on | off | default}
set deferred_name_resolution { on | off }
set dml_logging {minimal | default}
set encryption passwd 'password_phrase' for {key | column} {keyname | column_name}
set export_options [on | off]
set fipsflagger {on | off}
set flushmessage {on | off}
set fmtonly {on | off}
set forceplan {on | off}
set identity_insert [database.[owner.]]table_name {on | off}
set identity_update table_name {on | off}
set index_union on | off
set literal_autoparam 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 opttimeoutlimit
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 print_minlogged_mode_override
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 repthreshold 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 send_locator {on | off }
set session authorization login_name
set switch [serverwide] {on | off} trace_flag ,[trace_flag,] [with option [, option]
set show_exec_info [“on” | “off”]
set show_sqltext {on | off}
set show_transformed_sql, {on|off}
set statement_cache on | off
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 system_view {instance | cluster | clear}
set textsize {number}
set tracefile [filename] [off] [for spid]
set transaction isolation level { [read uncommitted | 0] | [read committed | 1] | [repeatable read | 2] | [serializable | 3]}
set transactional_rpc {on | off}
enables and disables advanced aggregation at the session level.
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.
impacts on both aggregate and comparison behaviors. See “Aggregate behavior” for more information on aggregate and comparison behaviors.
determines whether ANSI SQL permission requirements for delete and update statements are checked. The default is off. Table 1-29 summarizes permission requirements:
Command |
Permissions required with set ansi_permissions: |
|
---|---|---|
Off |
On |
|
update |
|
|
delete |
|
|
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.
arithabort arith_overflow – specifies the Adaptive Server behavior following a divide-by-zero error, range overflow during an explicit or implicit datatype converson, or a domain error. This type of error is serious. The default setting, arithabort arith_overflow on, rolls back the entire transaction in which the error occurs. If the error occurs in a batch that does not contain a transaction, arithabort arith_overflow on does not roll back earlier commands in the batch; however, Adaptive Server does not execute any statements in the batch that follow the error-generating statement.
Setting arith_overflow to on refers to the execution time, not to the level of normalization to which Adaptive Server is set.
If you set arithabort arith_overflow off, Adaptive Server aborts the statement that causes the error, but continues to process other statements in the transaction or batch.
arithabort numeric_truncation – specifies the Adaptive Server behavior following a loss of scale by an exact numeric type during an implicit datatype conversion. (When an explicit conversion results in a loss of scale, the results are truncated without warning.) The default setting, arithabort numeric_truncation on, aborts the statement that causes the error, but Adaptive Server continues to process other statements in the transaction or batch. If you set arithabort numeric_truncation off, Adaptive Server truncates the query results and continues processing.
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.
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 CIS 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.
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 CIS 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.
If a string is given as an argument in place of the chronological value the server interprets it as a datetime value regardless of its apparent precision. This is the default behavior and indicated by a builtin_date_strings value of 0.
Changing the builtin_date_strings value to 1 makes the server interpret the argument strings as bigdatetimes. This will affect the result of chronological builtins.
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.
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.
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.
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.
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.
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. 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. See Chapter 24, “Parallel Sorting,” in the Performance and Tuning Guide.
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.
makes CIS the remote procedure call (RPC) handling mechanism the default mechanism for Shared Disk Cluster (SDC) handling
assigns names to the client.
clientname client_name – 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.
client_name is the new name you assign to the user.
clienthostname host_name – 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.
host_name is the new name you assign to the host.
clientapplname application_name – 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.
application_name is the new name you assign to the application.
enables or disables compression for the session:
on – enables data compression on new data for tables and partitions on which compression is configured. Adaptive Server compresses all rows that qualify.
off – inserts and updates all new data as uncompressed. Inserts that trigger a page compression ignore the uncompressed rows. Updated rows remain uncompressed. Uncompressed rows that are compressed during an update (rows inserted as uncompressed remain uncompressed until explicitly compressed).
default – resets the compression level to the default setting (inserts and updates are compressed according to the table or partition setting).
Unlike most set parameters, if you execute set export_options before issuing set compression in a nested procedure, Adaptive Server does not export the compression level to the parent procedure’s context.
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.
specifies the following settings:
datefirst number – 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 |
Regardless 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 format – 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.
language 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.
sets deferred name resolution on for the current session only.
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.
Use delayed_commit only after careful consideration of your application.
determines the amount of logging for insert, update, and delete (DML) operations. Valid values are:
minimal – Adaptive Server attempts to log no changes for the DML statements. In most cases, Adaptive Server generates little or no logging to syslogs.
default – Adaptive Server disables session-specific minimal logging and uses the logging mode you enabled for individual tables based on table-specific and database-wide logging levels.
creates the encryption key’s password to encrypt or decrypt data on an insert, update, delete, select, alter table, or select into statement.
password_phrase – is the explicit password specified with the create encryption key or alter encryption key command to protect the key.
key – indicates that Adaptive Server uses this password to decrypt the key when accessing any column encrypted by the named key
keyname – may be supplied as a fully qualified name. For example:
[[database.][owner].]keyname
column – specifies that Adaptive Server use this password only in the context of encrypting or decrypting the named column. End users do not necessarily know the name of the key that encrypts a given column.
column_name – name of the column on which you are setting an encryption password. Supply column_name as:
[[ database.][ owner ]. ]table_name.column_name
Adaptive Server’s default behavior is to reset any set parameter changes that are set by a trigger or system procedure after they finish running. Enabling set export_options allows you to retain the session settings that are set by a system procedure or trigger for the duration of the session.
For example, this enables set export_options:
set export_options on
This disables set export_options and returns Adaptive Server to the default behavior:
set export_options off
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.
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.
captures plans in stored procedures without actually executing them.
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. See the Performance and Tuning Guide.
The query optimizer ignores attempts to force illegal join orders with outer joins, such as in the following:
1> set forceplan on 2> select * from table1, table2 where table2.id *= table1.id
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.
After setting identity_insert on for the table, the table owner, database owner, or system administrator can manually insert any legal value greater than 5. For example, inserting a value of 55 would create a large gap in IDENTITY column values:
insert stores_cal (syb_identity, stor_id, stor_name) values (55, "5025", "Good Reads") select syb_identity from stores_cal
id_col ------- 1 5 55 (3 rows affected)
If identity_insert is then set to off, Adaptive Server assigns an IDENTITY column value of 55 + 1, or 56, for the next insertion. If the transaction that contains the insert statement is rolled back, Adaptive Server discards the value 56 and uses a value of 57 for the next insertion.
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:
Table owner
Database owner:
If granted explicit insert permission on the column by the table owner
Impersonating the table owner by using the setuser command
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.
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
when enabled, sets limits the scan of a table with an or clause.
Index unions (also known as an or strategy) are used for queries that contain or clauses. For example:
select * from titleauthor where au_id = "409-56-7008" or title_id = "PC8888"
If index_union is:
Enabled – this example uses an index on au_id to
find the row IDs (RIDs) of all titleauthor tuples
with au_id = "409-56-7008"
,
and uses an index on title_id to find
the RIDs of all titleauthor tuples with title_id = "PC8888"
.
Adaptive Server then performs a union on all RIDs to eliminate duplicates.
The resulting RIDs are joined with a RidJoin to
access the data tuples.
Disabled – Adaptive Server does not use an index union strategy in a query to limit the table scan. Instead, it uses other access paths on the table (in the example above, it would use a table scan for table titleauthor), and applies the or clause as a filter in the scan operator.
is on by default. If the server-level setting for literal_autoparam is on, this option enables and disables use of that feature. If the server level setting is off, this setting has no effect.
specifies the settings for a 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.
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.
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.
generates diagnostics output in text format.
The valid values for show_option are:
show – shows the basic syntax common to all modules
show_lop – shows the logical operators (scans, joins, etc.) used
show_managers – shows data structure managers used during optimization.
show_log_props – shows the logical properties (row count, selectivity, etc.) evaluated.
show_parallel – shows details of parallel query optimization
show_histograms – shows the processing of histograms associated with SARG/Join columns
show_abstract_plan – shows the details of an abstract plan
show_search_engine – shows the details of the join ordering algorithm
show_counters – shows the optimization counters
show_best_plan – shows the details of the best query plan selected by the optimizer
show_pio_costing – shows estimates of physical input/output (reads/writes from/to the disk)
show_lio_costing – shows estimates of logical input/output (reads/writes from/to memory)
show_elimination – shows partition elimination
show_missing_stats – shows details of useful statistics missing from SARG/Join columns
See Chapter 4, “Displaying Query Optimization Strategies And Estimates,” in Query Optimizer for more information.
sets the timeout limit for the optimizer. The valid range of values for opttimeoutlimit 0 to 4000 ms, with 0 indicating no optimization limit.
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.
introduces an abstract plan command.
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.
See Chapter 30, “Creating and Using Abstract Plans,” in the Performance and Tuning Guide.
when used with set plan load, stores hash keys for up to 20 queries from an abstract plan group in a per-user cache.
generates an XML document for the diagnostic output. The valid values for show are:
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.
See Chapter 4, “Displaying Query Optimization Strategies And Estimates,” in Query Processor for more information.
sets the optimization goal.
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.
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.
See Chapter 1, “Understanding Query Processing in Adaptive Server” in Query Processor for more information about optimization plans.
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.
enables or disables replacing existing abstract plans during plan capture mode. By default, plan replacement is off.
enables or disables large I/Os to the data cache.
generates trace information to session output, reporting on the statement for which the minimally logged mode of a table was overridden by some other rules, such as presence of referential integrity constraints, deferred mode choice, name of the table affected and a description of the affecting rules, and so on.
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.
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.
specifies whether Adaptive Server executes parallel queries when an insufficient number of worker processes is available. Under these circumstances, if:
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.
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
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.
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.
Without 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.
determines whether Adaptive Server recognizes delimited identifiers within double quotation marks. By default, quoted_identifier is off and all identifiers must either:
Conform to the rules for valid identifiers.
Be enclosed in brackets.
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.
is 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.
sets the SQL replication threshold at the session level. If set repthreshold is invoked in a stored procedure, its scope is that of the procedure. If it is invoked in a user session, its scope is that of the session.
A user can alter the scope of the threshold and set the session threshold using a login trigger, in which case you need not explicitly set the session threshold at login.
For example,
create proc myproc as set repthreshold 777 --------------------- alter login sa modify login script 'myproc' ---------------------- option changed.
(Return status = 0
The procedure ’myproc’ is invoked each time the user sa logs in, and ensures that the replication threshold is set at 777 for the whole session.
Another way to alter the scope of the threshold is to use set export_options:
create proc p2 as set repthreshold222 set export_options on ---------------------------
After p2 is executed the threshold remains set at 222.The hierarchy of thresholds is:
Session > Table > Database
When you set the session threshold to 0, the only replication thresholds that still matter are table and database thresholds, if they have been specified, in that order. If you set no threshold, the threshold defaults to 50 rows.
To set the table level threshold, see sp_setrepdbmode; to set the database level threshold, see sp_setrepdefmode, both in the Reference Manual: Procedures.
The session threshold is exportable; a stored procedure can set the threshold and turn the export options setting ON. Adaptive Server enforces the new threshold in the invoking procedure or session.
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.
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.
"sa_role" | "sso_role" | "oper_role" are system roles. 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.
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
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.
specifies whether Adaptive Server sends the LOB or the locator that references the LOB in a result set sent to the client. When the option is off (the default), Adaptive Server sends the LOB.
is identical to set proxy, with this exception: set session authorization follows the SQL standard, while set proxy is a Transact-SQL extension.
generates additional information when a command executes.
on – generates extra diagnostics about the logging mode for a DML statement. Displays the selected logging mode for the current statement and session, and the user executing the DML.
off – disables show_exec_info.
allows you to print the SQL text for ad-hoc queries, stored procedures, cursors, and dynamic prepared statements.
You do not need to enable the set show_sqltext before you execute the query (as you do with commands like set showplan on) to collect diagnostic information for a SQL session. Instead you can enable it while the commands are running to help determine which query is performing poorly and diagnose their problems.
Before you enable show_sqltext, you must first enable dbcc traceon to display the output to standard out:
dbcc traceon(3604)
The syntax for set show_sqltext is:
set show_sqltext {on | off}
For example, this enables show_sqltext:
set show_sqltext on
Once set show_sqltext is enabled, Adaptive Server prints all SQL text to standard out for each command or system procedure you enter. Depending on the command or system procedure you run, this output can be extensive.
To disable show_sqltext, enter:
set show_sqltext off
is on by default. If the server-level setting for statement_cache is on, this option enables and disables use of that feature. If the server level setting is off, this setting has no effect.
displays various types of statistics information
io – displays statistics for each table referenced in the statement:
The number of times the table is accessed (scan count)
The number of logical reads (pages accessed in memory)
The number of physical reads (database device accesses)
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.
subquerycache – displays the number of cache hits, misses, and the number of rows in the subquery cache for each subquery.
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.
plancost – displays the query statistics in a tree format.
When you enable set statistics plancost,
Adaptive Server abbreviates the names for lio
, pio
,
and row
to l
, p
,
and r
, respectively.
simulate – specifies that the optimizer should use simulated statistics to optimize the query.
See Chapter 34, “Using the set statistics Commands” in the Performance and Tuning Guide.
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.
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.
(clusters only) specifies the system view for a session, and controls the materialization of fake tables, which impact the output of stored procedures such as sp_who.
instance – sets the system view for the current instance.
cluster – sets the system view for the cluster.
clear – clears any session-level
setting, reverting to the system_view setting
for the logical cluster hosting that spid. Enter
select @@system_view
to
check the current value.
allows you to set trace flags and switch names locally and server-wide.
serverwide – optional and will set a switch serverwide ON or OFF. The default is session-specific.
on – trace flags are switched on.
off – trace flags are switched off.
trace_flag – a sequence of numbers (the old traceflag numbers) and/or switch names.
option – an optional sequence of switch options. Valid values are:
override – this option is necessary to enable a non-documented switch names or trace flags
no_info – this option is used to surpress any informational warnings
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.
The default setting is 32K in isql. Some client software sets other default values. To reset textsize to the default size (32K), use:
set textsize 0
once enabled, saves all SQL text for the current session to the specified file, each SQL text batch appending to the previous batch.
The syntax to enable tracing is:
set tracefile file_name [off] [for spid
The syntax to disable tracing is:
set tracefile off [for spid]
Where:
file_name – is the full path to the file in which you are saving the SQL text. If you do not specify a directory path, Adaptive Server creates the file in $SYBASE.
If file_name contains special characters (“:”, “/”, and so on) other than numbers and letters, you must include file_name in quotes. For example, this file_name must be in quotes because of the “/” for the directory structure:
set tracefile '/tmp/mytracefile.txt' for 25
If file_name does not contain special characters and you want to save it to $SYBASE, it does not require quotes. For example, this file_name does not need to be in quotes:
set tracefile mytracefile.txt
off – disables the tracing for this session or spid.
spid – server process ID whose SQL text you want saved to a trace file. Only the users with the SA or systems security officer role can enable tracing for other spids. You cannot save the SQL text for system tasks (such as the housekeeper or the port manager).
After you use set tracefile for a particular session, the diagnostic output of all successive set commands or DBCC traces are then redirected to a tracefile.
Make sure to switch off all the diagnostic commands you turned on before issuing set tracefile off, or else the output that should go to the tracefile will instead go to the client.
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 – specifies 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.
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.
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.
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
Specifies that CIS handles outbound RPC requests by default:
set cis_rpc_handling on
Assigns this user the client name alison, the host name money1, and the application name webserver2:
set clientname 'alison' set clienthostname 'money1' set clientapplname 'webserver2'
Returns five rows for each succeeding fetch statement requested by a client using test_cursor:
set cursor rows 5 for test_cursor
Tells Adaptive Server to retain the session settings that are set by a system procedure or trigger for the duration of the session:
set export_options on
To disable set export_options and return Adaptive Server to the default behavior, use:
set export_options off
You can export these optimization settings using set export_options on.
By default, set export_options are enabled for login triggers.
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.
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
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
Subsequent commands in the session or stored procedure return an error and fail if they cannot get requested locks immediately:
set lock nowait
Subsequent commands in the current session or stored procedure wait indefinitely long to acquire locks:
set lock wait
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
Enables capturing abstract plans to the dev_plans group:
set plan dump dev_plans on
Enables loading of abstract plans from the dev_plans group for queries in the current session:
set plan load dev_plans on
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
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.
The user executing this command now operates within the server as the login “mary” and Mary’s server user ID:
set proxy "mary"
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
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
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.
Activates the “doctor” role. This command is used by users to specify the roles they want activated:
set role doctor_role on
Deactivates the user’s system administrator role for the current session:
set role "sa_role" off
Activates the “doctor” role when the user enters the password:
set role doctor_role with passwd "physician" on
Deactivates the “doctor” role:
set role doctor_role off
Specifies a maximum degree of parallelism of 4 for parallel index scans and parallel table scans on nonpartitioned tables:
set scan_parallel_degree 4
An alternative way of stating example 5:
set session authorization "mary"
For each query, returns a description of the processing plan, but does not execute it:
set showplan, noexec on go select * from publishers go
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)
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
Sets the limit on text, unitext, or image data returned with a select statement to 100 bytes:
set textsize 100
Sets the serverwide switch on to set traceflags for 110, an undocumented traceflag, with no additional informational warnings:
set switch serverwide on 110 with override, no_info
Opens a trace file named sql_text_file for the the current session:
set tracefile '/var/sybase/REL1502/text_dir/sql_text_file'
Subsequent outputs from set showplan, set statistics io, and dbcc traceon(100) are saved in sql_text_file.
Does not specify a directory path, so the trace file is saved in $SYBASE/sql_text_file:
set tracefile 'sql_text_file' for 11
Any SQL run on spid 11 is saved to this tracefile.
Saves the SQL text for spid 86:
set tracefile '/var/sybase/REL1502/text_dir/sql_text_file' for 86
Specifies that when a transaction is pending, the RPC is handled by the CIS access methods rather than by the Adaptive Server site handler:
set transactional_rpc on
All subsequent queries in the session run at the repeatable reads transaction isolation level:
set transaction isolation level 2
Implements read-locks with each select statement in a transaction for the duration of that transaction:
set transaction isolation level 3
This example shows how the DML logging mode for multiple statements on the same table remains unchanged if the table was initially operated with minimal logging in a transaction.
Begin the transaction and set the DML logging to minimal:
begin tran set dml_logging minimal
Run an insert command:
insert into tab1 values(1)
Set DML logging back to the default:
set dml_logging default
Even though you reset DML logging to the default, because t1 was previously run with minimal logging in this transaction, this insert is executed with minimal logging:
insert into tab1 values(1)
The error log includes reasons the logging mode choice was overridden.
This example changes the show_exec_info from minimal to full within the same session:
Log in to Adaptive Server:
isql -Ubob -Pbob123 use myimdb
Create table tab1:
create table tab1(col1 int)
Enable show_exec_info and set DML logging to minimal:
set show_exec_info on set dml_login minimal
Insert values into tab1:
insert into tab1 values(1)
Adaptive Server displays the name of the table and database, the user ID running the command, and the logging mode used:
Operating on the table 'tab1', database 'myimdb' (owner ID 3) in 'minimal' logging mode by user ID 3.
Set the DML logging back to default:
set dml_logging default
Insert more values into tab1:
insert into tab1 values(1)
Adaptive Server displays the name of the table and database, the user ID running the command, and the logging mode used:
Operating on the table 'tab1', database 'myimdb' (owner ID 3) in 'full' logging mode by user ID 3.
fipsflagger, string_rtruncation, ansinull, ansi_permissions, arithabort, and arithignore affect aspects of Adaptive Server error handling and compliance to SQL standards.
You can use the cis_rpc_handling and transactional_rpc options only when CIS is enabled.
The async log service option and delayed_commit are mutually exclusive. delayed_commit will not work if async log service is set to “true.”
parallel_degree and scan_parallel_degree limit the degree of parallelism for queries, if Adaptive Server is configured for parallelism. When you use these options, you give the optimizer a hint to limit parallel queries to use fewer worker processes than allowed by the configuration parameters. Setting these parameters to 0 restores the server-wide configuration values.
If you specify a number that is greater than the numbers allowed by the configuration parameters, Adaptive Server issues a warning message and uses the value set by the configuration parameter.
If you use the set command inside a trigger or stored procedure, most set options revert to their former settings after the trigger or procedure executes.
The following options do not revert to their former settings after the procedure or trigger executes, but remain for the entire Adaptive Server session or until you explicitly reset them:
datefirst
dateformat
identity_insert
language
quoted_identifier
If you specify more than one set option, the first syntax error causes all following options to be ignored. However, the options specified before the error are executed, and the new option values are set.
If you assign a user a client name, host name, or application name, these assignments are only active for the current session. You must reassign these the next time the user logs in. Although the new names appear in sysprocesses, they are not used for permission checks, and sp_who still shows the client connection as belonging to the original login. For more information about setting user processes, see the System Administration Guide.
All set options except showplan and char_convert take effect immediately. showplan takes effect in the following batch. Here are two examples that use set showplan on:
set showplan on select * from publishers go
pub_id pub_name city state ------- --------------------- ----------- --- 0736 New Age Books Boston MA 0877 Binnet & Hardley Washington DC 1389 Algodata Infosystems Berkeley CA (3 rows affected)
But:
set showplan on go select * from publishers go
QUERY PLAN FOR STATEMENT 1 (at line 1). STEP 1 The type of query is SELECT FROM TABLE publishers Nested iteration Table Scan Ascending Scan. Positioning at start of table. pub_id pub_name city state ------ -------------------- ---------- ---- 0736 New Age Books Boston MA 0877 Binnet & Hardley Washington DC 1389 Algodata Infosystems Berkeley CA (3 rows affected)
Adaptive Server automatically stores
one or more spaces in clientname, clienthostname,
and clientapplname columns. For this reason,
a query using any of these three columns that includes “is
null
” does not return an expected result
set.
set proxy issue the following warning when they are issued while set fipsflagger option is enabled:
SQL statement on line number 1 contains Non-ANSI text. The error is caused due to the use of DBCC.
If you use a login trigger to set current execution properties, any exportable set option that you enable or disable inside a login trigger takes affect in the current process.
Some set options can be grouped together:
parseonly, noexec, prefetch, showplan, rowcount, and nocount control the way a query is executed. It does not make sense to set both parseonly and noexec on. The default setting for rowcount is 0 (return all rows); the default for the others is off.
The statistics options display performance statistics after each query. The default setting for the statistics options is off. For more information about noexec, prefetch, showplan and statistics, see the Performance and Tuning Guide.
You can update up to 1024 columns in the set clause using literals, variables, or expressions returned from a subquery.
offsets is used in DB-Library to interpret results from Adaptive Server. The default setting for this option is on.
datefirst, dateformat, and language affect date functions, date order, and message display. If used within a trigger or stored procedure, these options do not revert to their previous settings.
In the default language, us_english, datefirst is 1 (Sunday), dateformat is mdy, and messages are displayed in us_english. Some language defaults (including us_english) produce Sunday=1, Monday=2, and so on; others produce Monday=1, Tuesday=2, and so on.
set language implies that Adaptive Server should use the first weekday and date format of the language it specifies, but does not override an explicit set datefirst or set dateformat command issued earlier in the current session.
cursor rows and close on endtran affect the way Adaptive Server handles cursors. The default setting for cursor rows with all cursors is 1. The default setting for close on endtran is off.
chained and transaction isolation level allow Adaptive Server to handle transactions in a way that is compliant with the SQL standards.
In version 15.0.2 and later, Adaptive Server changes the compile-time behavior for some abstract plan set parameters when you use them to create stored procedures or run them in Transact-SQL batches.
In earlier versions of Adaptive Server, the set parameters took effect after the stored procedure was executed or recompiled. Adaptive Server 15.0.2 allows you to use optimizer set parameters at compile time to affect the optimizer in stored procedures or batches.
This changed behavior may effect the composition of the result set. Sybase recommends that you review the result set created by the 15.0.2 versions of the set parameters before using them in your production systems.
You must reset the set parameter before returning from the stored procedure or the execution of subsequent stored procedures may be affected. If you intend to propogate this change to subsequent stored procedures, use export_options parameter.
Table 1-30 shows the optimizer options that you can export when you use set export_options on.
optgoal opttimeout merge_join hash_join nl_join distinct_sorted distinct_sorting distinct_hashing group_sorted group_hashing group_inserting order_sorting addend_union_all merge_union_all merge_union_distinct hash_union_distinct |
store_index bushy_space_search parallel_query replicated_partitioning basic_optimization index_intersection index_union multi_gt_store_index opportunistic_grouping opportunistic_distinct auto_query_tuning streaming_sort nary_nl_join query_tuning_mem_limit query_tuning_time_limit showlop |
showmanagers showlogprops showparallel showhistograms showabstractplan showsearchengine showcounters showbestplan showfinalplan showcodegen showpiocosting showliocosting showelimination showpllcosting shownostats showexecio |
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.
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:
Sybase NULL mode – “val = NULL
” is
true when “val
” is
NULL
ANSI NULL mode – “val = NULL
” is
unknown when “val
” is
NULL
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:
Enable ansinull mode – do
not use the Sybase NULL comparisons (val = NULL
or val != NULL
).
Expect to use ANSI-null mode during insert and update – do not use the Sybase NULL comparisons in check constraints.
Instead, use the ANSI IS NULL or IS NOT NULL syntax to prevent from having unexpected results.
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')
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:
create table [table name]
create database [database name]
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:
A dot (.) cannot appear in an identifier name, however delimited
Object names as stored procedure parameters – Adaptive Server stored procedure object names can be treated as strings, and do not need delimiters. For example, the following gives correct results if a table named table actually exists:
exec sp_help 'dbo.table'
However, the brackets are not stripped from the object name in the following:
exec sp_help 'dbo.[table]'
When you log in to Adaptive Server, all system-defined roles granted to you are automatically activated. User-defined roles granted to you are not automatically activated. To automatically activate user-defined roles granted to you, use sp_modifylogin. See sp_modifylogin in Reference Manual: Procedures. Use set role role_name on or set role role_name off to turn roles on and off.
For example, if you have been granted the system administrator role, you assume the identity (and user ID) of database owner in the current database. To assume your real user ID, execute this command:
set role "sa_role" off
If you are not a user in the current database, and if there is no “guest” user, you cannot set sa_role off.
If the user-defined role you intend to activate has an attached password, you must specify the password to turn the role on. Thus, you would enter:
set role "role_name" with passwd "password" on
During set role, Adaptive Server locks the role if your failed role activation attempts reach the number you set in max failed_logins. When this happens, locksuid, locdate, and lockreason in are updated in syssrvroles.
Setting the logging level to minimal affects the logging mode only on objects owned by the current user. However, if the user has system administrator privileges, setting the logging to minimal affects the logging mode for all objects in the user’s session.
Data copied into the table created with select into is minimally logged. with dml_logging = minimal specifies the logging mode for future DML operations on this table
show_exec_info does not display the reason Adaptive Server overrides a minimally logged mode selected by a user. Use set switch print_minlogged_mode_override to view the reasons for this override.
Session-specific settings for the logging mode override the logging options set at the table and database level, and include these restrictions:
Database-wide settings
Disabling DML logging for the current session based on the logging mode:
Database-wide logging mode settings
Table-specific logging mode settings
The ownership of the tables being updated
If you set the session-specific DML logging to minimal, running set dml_logging default returns the logging mode for the affected tables to their default logging mode, based on the table and database-wide settings.
You cannot use set dml_logging to perform fully logged DML if the database or table owner has already configured the table to run with minimal logging.
Enabling or disabling compression for the session does not change the compression level for existing data.
Updated rows remain uncompressed. Adaptive Server uncompresses any compressed rows during the update.
If set compression is set to off, commands that require a data copy (for example, reorg rebuild and alter table) uncompress data rows.
After you configure set compression on, subsequent updates use the partition or tables’ compression level.
The behavior of the cis rpc handling configuration property and the set transactional_rpc commands changed with the introduction of ASTC. In versions earlier than 12.0, enabling cis rpc handling caused all RPCs to be routed through CIS’s Client-Library connection. As a result, whenever cis rpc handling was enabled, transactional_rpc behavior occurred whether or not it had been specifically set. As of Adaptive Server 12.0, this behavior has changed. If cis rpc handling is enabled and transactional_rpc is off, RPCs within a transaction are routed through the site handler. RPCs executed outside a transaction are sent via CIS’s Client-Library connection.
When Adaptive Server distributed transaction management services are enabled, you can place RPCs within transactions. These RPCs are called transactional RPCs. A transactional RPC is an RPC whose work can be included in the context of a current transaction. This remote unit of work can be committed or rolled back along with the work performed by the local transaction.
To use transactional RPCs, enable CIS and distributed transaction management with sp_configure, then issue the set transactional_rpc command. When set transactional_rpc is on and a transaction is pending, the Adaptive Server (as opposed to the Adaptive Server site handler) coordinates the RPC.
The set transactional_rpc command default is off. The set cis_rpc_handling command overrides the set transactional_rpc command. If you set cis_rpc_handling on, all outbound RPCs are handled by CIS.
See the Component Integration Services User’s Guide for a discussion of using set transactional_rpc, set cis_rpc_handling, and sp_configure.
Without 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.
Before you can use the set proxy or set session authorization command, a system security officer must grant permission to execute set proxy or set session authorization from the master database.
You can switch your server user identity to any other server login and limit its use based on the target login roles by using:
grant set proxy to user_or_role_list [restrict role role_list | all | system]
See grant for more information.
Executing set proxy or set session authorization with the original login_name reestablishes your previous identity.
You cannot execute set proxy or set session authorization from within a transaction.
Adaptive Server permits only one level of login identity change. Therefore, after you use set proxy or set session authorization to change identity, you must return to your original identity before changing it again. For example, assume that your login name is “ralph”. To create a table as “mary”, create a view as “joe”, then return to your own login identity. Use the following statements:
set proxy "mary" create table mary_sales (stor_id char (4), ord_num varchar (20), date datetime) grant select on mary_sales to public set proxy "ralph" set proxy "joe" create view joes_view (publisher, city, state) as select stor_id, ord_num, date from mary_sales set proxy "ralph"
If a user issues set proxy to assume the permissions, login name, and suid of another user, Adaptive Server checks the proxy user’s access to database objects, rather than the original user’s access.
Adaptive Server uses the name and password information of the user who logged in to check for automatic access to encryption keys using login credentials. Adaptive Server does not have access to the proxy user’s password. Access to keys through the login password is on behalf of the user who logs in, not on behalf of the user assumed through an alias, set proxy, or setuser. Access to copies of encryption keys that were set up for login association, but which are still encrypted by the system encryption password, is treated similarily.
By default, an Adaptive Server task that cannot immediately acquire a lock waits until incompatible locks are released, then continues processing. This is equivalent to set lock wait with no value specified in the numsecs parameter.
You can set a server-wide lock wait period by using sp_configure with the lock wait period option.
lock wait period, with the session-level setting set lock wait nnn, is only applicable for user-defined tables. These settings have no influence on system tables.
A lock wait period defined at the session level or in a stored procedure with the set lock command overrides a server-level lock-wait period.
If set lock wait is used by itself, with no value for numsecs, all subsequent commands in the current session wait indefinitely to acquire requested locks.
sp_sysmon reports the number of times that tasks waiting for a lock could not acquire the lock within the waiting period.
The repeatable-reads isolation level, also known as transaction isolation level 2, holds locks on all pages read by the statement until the transaction completes.
A nonrepeatable read occurs when one transaction reads rows from a table and a second transaction can modify the same rows and commit the changes before the first transaction completes. If the first transaction rereads the rows, they now have different values, so the initial read is not repeatable. Repeatable reads hold shared locks for the duration of a transaction, blocking transactions that update the locked rows or rows on the locked pages.
You can load simulated statistics into a database using the simulate mode of the optdiag utility program. If set statistics simulate on has been issued in a session, queries are optimized using simulated statistics, rather than the actual statistics for a table.
Table 1-31 lists the global variables that contain information about the session options controlled by the set command.
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 Adaptive Server is Sunday (based on the
us_language default), which you set by specifying |
@@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. |
Table 1-32 lists set options and values that work with @@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 |
When fipsflagger is on, Adaptive Server displays a warning message when these extensions are used:
The installjava utility
The remove java command
Column and variable declarations that reference Java classes as datatypes
Statements that use Java-SQL expressions for member references
The status of fipsflagger does not affect arithmetic expressions performed by Java methods.
For more information about Java in the database, see Java in Adaptive Server Enterprise.
You cannot save the SQL text for system tasks (such as the housekeeper or the port manager).
You must have the sa or sso roles, or be granted set tracing permission, to run enable or disable tracing.
set tracefile is not allowed to open an existing file as a tracefile.
During an SA or systems security officer session, if you enable set tracfile for a specific spid, all subsequent tracing commands executed take effect on that spid, not the system administrator or systems security officer spid.
If Adaptive Server runs out of file space while writing the tracefile, it closes the file and disables the tracing.
If an isql session starts tracing for a spid, but the isql session quits without disabling the tracing, another isql session can begin tracing this spid.
Tracing occurs for the session for which it is enabled only, not for the session that enabled it.
You cannot trace more than one session at a time
from a single sa or sso session. If you attempt to open a tracefile
for a session for which there is already a trace file open, Adaptive
Server issues this error message: tracefile
is already open for this session.
You cannot trace the same session from multiple sa or sso sessions.
The file storing the trace output is closed when the session being traced quits or when you disable tracing.
Before you allocate resources for tracing, keep in mind that each tracing requires one file descriptor per engine.
You can use set tracefile in combination with other set commands and options that provide diagnostic information for a better understanding of slow-running queries. These are the set commands and options that save diagnostic information to a file:
set show_sqltext [on | off]
set showplan [on | off]
set statistics io [on | off]
set statistics time [on | off]
set statistics plancost [on | off]
These are the set options:
set option show [normal | brief | long | on | off]
set option show_lop [normal | brief | long | on | off]
set option show_parallel [normal | brief | long | on | off]
set option show_search_engine [normal | brief | long | on | off]
set option show_counters [normal | brief | long | on | off]
set option show_managers [normal | brief | long | on | off]
set option show_histograms [normal | brief | long | on | off]
set option show_abstract_plan [normal | brief | long | on | off]
set option show_best_plan [normal | brief | long | on | off]
set option show_code_gen [normal | brief | long | on | off]
set option show_pio_costing [normal | brief | long | on | off]
set option show_lio_costing [normal | brief | long | on | off]
set option show_log_props [normal | brief | long | on | off]
set option show_elimination [normal | brief | long | on | off]
You must have the sa or sso roles to run show_sqltext.
You cannot use show_sqltext to print the SQL text for triggers.
You cannot use show_sqltext to show a binding variable or a view name.
Adaptive Server enables set options inside login triggers to remain valid for the entire user session. The following set options are automatically exported:
|
|
|
ANSI SQL – Compliance level: Transact-SQL extension.
The ANSI SQL standard specifies behavior that differs from Transact-SQL behavior in versions of Adaptive Server earlier than 15.7. Compliant behavior is enabled, by default, for all Embedded-SQL precompiler applications. Other applications needing to match this standard of behavior can use these set options:
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 |
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.
Changes to logging apply only to objects owned by the user in this session, when applicable. In addition, only the tables owned by the session owner are affected by set dml_logging:
Any user can execute set dml_logging for minimal logging and for returning to the default mode of logging. Once this set succeeds, DML on all tables owned by the user executing the statement in any database is minimally logged for the current session, until set dml_logging default is executed.
DML logging defaults to the database- and table-level settings when minimal logging is enabled for the session but the DML operates on tables not owned by the session’s user.
The DML logging setting in a session or procedure is inherited by procedures, however, only the tables owned by the user running the session are affected.
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 |
|
Commands create trigger, fetch, grant, insert, lock table, revoke, set
Functions convert