Transac SQL statments
| abs | abs - Returns the absolute value of an expression. abs(numeric_expression) |
| acos | acos - Returns the angle (in radians) whose cosine is specified. acos(cosine) |
| alter database | alter database - Increases the amount of space allocated to a database. alter database database_name [on {default | database_device } [= size] [, database_device [= size]]...] [log on { default | database_device } [ = size ] [ , database_device [= size]]...] [with override] [for load] |
| alter role | alter role - Defines mutually exclusive relationships between roles and adds, drops, and changes passwords for roles. alter role role1 { add | drop } exclusive { membership | activation } role2 alter role role_name { add passwd "password"| drop passwd } |
| alter table | alter table - Changes the locking scheme for an existing table; specifies ascending or descending index order when alter table is used to create referential integrity constraints that are based on indexes; specifies the ratio of filled pages to empty pages, to reduce storage fragmentation. alter table [database.[owner].]table_name { add column_name datatype [default {constant_expression | user | null}] {identity | null} [ [constraint constraint_name] { { unique | primary key } [clustered | nonclustered] [asc | desc] [with { { fillfactor = pct , reservepagegap = num_pages }] [on segment_name] | references [[database.]owner.]ref_table [(ref_column)] | check (search_condition) ] ... } [, next_column]... | add { [constraint constraint_name] { {unique | primary key} [clustered | nonclustered] (column_name [asc | desc] [, column_name [asc | desc]...]) [with { { fillfactor = pct , reservepagegap = num_pages}] [on segment_name] | foreign key (column_name [,column_name...]) references [[database.]owner.]ref_table [(ref_column [, ref_column...])] | check (search_condition)} } | drop constraint constraint_name | replace column_name default {constant_expression | user | null} | partition number_of_partitions | unpartition |
| ascii | ascii - Returns the ASCII code for the first character in an expression. ascii(char_expr) |
| asin | asin - Returns the angle (in radians) whose sine is specified. asin(sine) |
| atan | atan - Returns the angle (in radians) whose tangent is specified. atan(tangent) |
| atn2 | atn2 - Returns the angle (in radians) whose sine and cosine are specified. atn2(sine, cosine) |
| avg | avg - Returns the numeric average of all (distinct) values. avg([all | distinct] expression) |
| begin transaction | begin transaction - Marks the starting point of a user-defined transaction. begin tran[saction] [transaction_name] |
| begin...end | begin...end - Encloses a series of SQL statements so that control-of-flow language, such as if...else, can affect the performance of the whole group. begin statement block end |
| break | break - Causes an exit from a while loop. break is often activated by an if test. while logical_expression statement break statement continue |
| case | case - Supports conditional SQL expressions. case expressions can be used anywhere a value expression can be used. case when search_condition then expression [when search_condition then expression]... [else expression] end case expression when expression then expression [when expression then expression]... [else expression] end coalesce(expression, expression [, expression]...) nullif(expression, expression) |
| ceiling | ceiling - Returns the smallest integer greater than or equal to the specified value. ceiling(value) |
| char | char - Returns the character equivalent of an integer. char(integer_expr) |
| char_length | char_length - Returns the number of characters in an expression. char_length(char_expr) |
| charindex | charindex - Returns an integer representing the starting position of an expression. charindex(expression1, expression2) |
| checkpoint | checkpoint - Writes all dirty pages (pages that have been updated since they were last written) to the database device. checkpoint |
| close | close - Deactivates a cursor. close cursor_name |
| col_length | col_length - Returns the defined length of a column. col_length(object_name, column_name) |
| col_name | col_name - Returns the name of the column whose table and column IDs are specified. col_name(object_id, column_id[, database_id]) |
| commit | commit - Marks the ending point of a user-defined transaction. commit [tran[saction] | work] [transaction_name] |
| compare | compare - Compares two character strings based on alternate collation rules. compare (char_expression1, char_expression2 [, {collation_name | collation_ID}]) |
| compute Clause | compute Clause - Generates summary values that appear as start_of_select_statement [by column_name [, column_name]...] |
| connect to...disconnect | connect to...disconnect - Connects to the specified server and disconnects the connected server. connect to server_name disconnect |
| continue | continue - Restarts the while loop. continue is often activated by an if test. while boolean_expression statement break statement continue |
| convert | convert - Returns the specified value, converted to another datatype or a different datetime display format. convert (datatype [(length) | (precision[, scale])], expression[, style]) |
| cos | cos - Returns the cosine of the specified angle. cos(angle) |
| cot | cot - Returns the cotangent of the specified angle. cot(angle) |
| count | count - Returns the number of (distinct) non-null values count([all | distinct] expression) |
| create database | create database - Creates a new database. Use create database from the master database. create database database_name [on {default | database_device} [= size] [, database_device [= size]]...] [log on database_device [= size] [, database_device [= size]]...] [with override] [for load] |
| create default | create default - Specifies a value to insert in a column (or in all columns of a user- defined datatype) if no value is explicitly supplied at insert time. create default [owner.]default_name as constant_expression |
| create existing table | create existing table - Allows you to map the proxy table to a table, view, or procedure at a remote location. create existing table table_name (column_list) [ on segment_name ] [ [ external {table | procedure} ] at pathname ] |
| create index | create index - Creates an index in ascending or descending order for each column; allows up to 31 columns per index; leaves a specified number of unused pages during index creation; allows specification of the number of steps in the distribution histogram for the index. create [unique] [clustered | nonclustered] index index_name on [[database.]owner.]table_name (column_name [asc | desc] [, column_name [asc | desc]]...) [with { reservepagegap = num_pages, consumers = x, ignore_dup_key, sorted_data, , statistics using num_steps values } ] [on segment_name] |
| create procedure | create procedure - Creates a stored procedure or an extended stored procedure (ESP) that can take one or more user-supplied parameters. create procedure [owner.]procedure_name[;number] [[(]@parameter_name datatype [(length) | (precision [, scale])] [= default][output] [, @parameter_name datatype [(length) | (precision [, scale])] [= default][output]]...[)]] [with recompile] as {SQL_statements | external name dll_name} |
| create proxy_table | create proxy_table - Creates a proxy table without specifying a column list. CIS derives the column list from the metadata it obtains from the remote table. create proxy_table table_name [ external table ] at pathname |
| create role | create role - Creates a user-defined role. create role role_name [with passwd "password"] |
| create rule | create rule - Specifies the domain of acceptable values for a particular column or for any column of a user-defined datatype. create rule [owner.]rule_name as condition_expression |
| create schema | create schema - Creates a new collection of tables, views, and permissions for a database user. create schema authorization authorization_name create_oject_statement [ create_object_statement ... ] [ permission_statement ... ] |
| create table | create table - Specifies a locking scheme for the table being created; specifies ascending or descending index order when creating referential integrity constraints that depend on indexes; specifies the specifies a ratio of empty pages to be left for each filled page. create table [database.[owner].]table_name (column_name datatype [default {constant_expression | user | null}] {[{identity | null | not null}] | [[constraint constraint_name] {{unique | primary key} [clustered | nonclustered] [asc | desc] [with { { fillfactor = pct , reservepagegap = num_pages }] [on segment_name] | references [[database.]owner.]ref_table [(ref_column)] | check (search_condition)}]}... | [constraint constraint_name] {{unique | primary key} [clustered | nonclustered] (column_name [asc | desc] [{, column_name [asc | desc]}...]) [with { {fillfactor = pct reservepagegap = num_pages } ] [on segment_name] |foreign key (column_name [{, column_name}...]) references [[database.]owner.]ref_table [(ref_column [{, ref_column}...])] | check (search_condition) ... } [{, {next_column | next_constraint}}...]) reservepagegap = num_pages } ] [on segment_name] [ [ external table ] at pathname ] |
| create trigger | create trigger - Creates a trigger, a type of stored procedure that is often used for enforcing integrity constraints. A trigger executes automatically when a user attempts a specified data modification statement on a specified table. create trigger [owner.]trigger_name on [owner.]table_name for {insert , update , delete} as SQL_statements Or, using the if update clause: create trigger [owner.]trigger_name on [owner.]table_name for {insert , update} as [if update (column_name) [{and | or} update (column_name)]...] SQL_statements [if update (column_name) [{and | or} update (column_name)]... SQL_statements]... |
| create view | create view - Creates a view, which is an alternative way of looking at the data in one or more tables. create view [owner.]view_name [(column_name [, column_name]...)] as select [distinct] select_statement [with check option] |
| curunreservedpgs | curunreservedpgs - Returns the number of free pages in the specified disk piece. curunreservedpgs(dbid, lstart, unreservedpgs) |
| data_pgs | data_pgs - Returns the number of pages used by the specified table or index. data_pgs(object_id, {data_oam_pg_id | index_oam_pg_id}) |
| datalength | datalength - Returns the actual length, in bytes, of the specified column or string. datalength(expression) |
| dateadd | dateadd - Returns the date produced by adding a given number of years, quarters, hours, or other date parts to the specified date. dateadd(date_part, integer, date) |
| datediff | datediff - Returns the difference between two dates. datediff(datepart, date1, date2) |
| datename | datename - Returns the name of the specified part of a datetime value. datename (datepart, date) |
| datepart | datepart - Returns the integer value of the specified part of a datetime value. datepart(date_part, date) |
| db_id | db_id - Returns the ID number of the specified database. db_id(database_name) |
| db_name | db_name - Returns the name of the database whose ID number is specified. db_name([database_id]) |
| dbcc | dbcc - Database Consistency Checker (dbcc) checks the logical and physical consistency of a database. Use dbcc regularly as a periodic check or if you suspect any damage. dbcc checkalloc [(database_name [, fix | nofix])] dbcc checkcatalog [(database_name)] dbcc checkdb [(database_name [, skip_ncindex])] dbcc checkstorage [(database_name)] dbcc checktable({table_name|table_id}[, skip_ncindex]) dbcc checkverify(dbname) dbcc dbrepair (database_name, dropdb) dbcc fix_text ({table_name | table_id}) dbcc indexalloc ({table_name | table_id}, index_id [, {full | optimized | fast | null} [, fix | nofix]]) dbcc reindex ({table_name | table_id}) dbcc tablealloc ({table_name | table_id} [, {full | optimized | fast | null} [, fix | nofix]])| dbcc { traceon | traceoff } (3604,302) dbcc tune ( { ascinserts, {0 | 1 } , tablename | cleanup, {0 | 1 } | cpuaffinity, start_cpu [, on| off] | deviochar vdevno, "batch_size" | doneinproc { 0 | 1 } | maxwritedes, writes_per_batch } ) |
| deallocate cursor | deallocate cursor - Makes a cursor inaccessible and releases all memory resources committed to that cursor. deallocate cursor cursor_name |
| declare | declare - Declares the name and type of local variables for a batch or procedure. Variable declaration: declare @variable_name datatype [, @variable_name datatype]... Variable assignment: select @variable = {expression | select_statement} [, @variable = {expression | select_statement} ...] [from table_list] [where search_conditions] [group by group_by_list] [having search_conditions] [order by order_by_list] [compute function_list [by by_list]] |
| declare cursor | declare cursor - Defines a cursor. declare cursor_name cursor for select_statement [for {read only | update [of column_name_list]}] |
| degrees | degrees - Returns the size, in degrees, of an angle with the specified number of radians. degrees(numeric) |
| delete | delete - The readpast clause allows the delete command to delete [[database.]owner.]{table_name | view_name} [from [[database.]owner.]{view_name [readpast]| table_name [readpast] [(index {index_name | table_name } [ prefetch size ][lru|mru])]} [, [[database.]owner.]{view_name [readpast]| table_name [readpast] [(index {index_name | table_name } [ prefetch size ][lru|mru])]} ...] [where search_conditions] ] |
| delete statistics | delete statistics - Removes statistics from the sysstatistics system table. delete statistics table_name [(column_name [,...])] |
| difference | difference - Returns the difference between two soundex values. difference(char_expr1, char_expr2) |
| disk init | disk init - Makes a physical device or file usable by Adaptive Server. disk init 1name = "device_name" , physname = "physicalname" , vdevno = virtual_device_number , size = number_of_blocks [, vstart = virtual_address , cntrltype = controller_number ] [, contiguous] |
| disk mirror | disk mirror - Creates a software mirror that immediately takes over when the primary device fails. disk mirror name = "device_name" , mirror = "physicalname" [ ,writes = { serial | noserial }] [ ,contiguous ] (OpenVMS only) |
| disk refit | disk refit - Rebuilds the master database's sysusages and sysdatabases system tables from information contained in sysdevices. disk refit |
| disk reinit | disk reinit - Rebuilds the master database's sysdevices system table. Use disk reinit as part of the procedure to restore the master database. disk reinit name = "device_name", physname = "physicalname" , vdevno = virtual_device_number , size = number_of_blocks [, vstart = virtual_address , cntrltype = controller_number] |
| disk remirror | disk remirror - Restarts disk mirroring after it is stopped by failure of a mirrored device or temporarily disabled by the disk unmirror command. disk remirror name = "device_name" |
| disk unmirror | disk unmirror - Suspends disk mirroring initiated with the disk mirror command to allow hardware maintenance or the changing of a hardware device. disk unmirror name = "device_name" [ ,side = { "primary" | secondary }] [ ,mode = { retain | remove }] |
| drop database | drop database - Removes one or more databases from Adaptive Server. drop database database_name [, database_name]... |
| drop default | drop default - Removes a user-defined default. drop default [owner.]default_name [, [owner.]default_name]... |
| drop index | drop index - Removes an index from a table in the current database. drop index table_name.index_name [, table_name.index_name]... |
| drop procedure | drop procedure - Removes a procedure. drop proc[edure] [owner.]procedure_name [, [owner.]procedure_name] ... |
| drop role | drop role - Drops a user-defined role. drop role role_name [with override] |
| drop rule | drop rule - Removes a user-defined rule. drop rule [owner.]rule_name [, [owner.]rule_name]... |
| drop table | drop table - Removes a table definition and all of its data, indexes, triggers, and permissions from the database. drop table [[database.]owner.]table_name [, [[database.]owner.]table_name ]... |
| drop trigger | drop trigger - Removes a trigger. drop trigger [owner.]trigger_name [, [owner.]trigger_name]... |
| drop view | drop view - Removes one or more views from the current database. drop view [owner.]view_name [, [owner.]view_name]... |
| dump database | dump database - Makes a backup copy of the entire database, including the transaction log, in a form that can be read in with load database. Dumps and loads are performed through Backup Server. dump database database_name to stripe_device [ at backup_server_name ] [density = density_value, blocksize = number_bytes, capacity = number_kilobytes, dumpvolume = volume_name, file = file_name] [stripe on stripe_device [ at backup_server_name ] [density = density_value, blocksize = number_bytes, capacity = number_kilobytes, dumpvolume = volume_name, file = file_name]] [[stripe on stripe_device [ at backup_server_name ] [density = density_value, blocksize = number_bytes, capacity = number_kilobytes, dumpvolume = volume_name, file = file_name]]...] [with { density = density_value, blocksize = number_bytes, capacity = number_kilobytes, dumpvolume = volume_name, file = file_name, [dismount | nodismount], [nounload | unload], retaindays = number_days, [noinit | init], notify = {client | operator_console} }] |
| dump transaction | dump transaction - Dumps only completed transactions. To make a routine or standby access log dump: dump tran[saction] database_name to stripe_device [ at backup_server_name ] [density = density_value, blocksize = number_bytes, capacity = number_kilobytes, dumpvolume = volume_name, file = file_name] [stripe on stripe_device [ at backup_server_name ] [density = density_value, blocksize = number_bytes, capacity = number_kilobytes, dumpvolume = volume_name, file = file_name]] [[stripe on stripe_device [ at backup_server_name ] [density = density_value, blocksize = number_bytes, capacity = number_kilobytes, dumpvolume = volume_name, file = file_name] ]...] [with { density = density_value, blocksize = number_bytes, capacity = number_kilobytes, dumpvolume = volume_name, file = file_name, [dismount | nodismount], [nounload | unload], retaindays = number_days, [noinit | init], notify = {client | operator_console} standby_access }] To truncate the log without making a backup copy: dump tran[saction] database_name with truncate_only To truncate a log that is filled to capacity.Use only as a last resort dump tran[saction] database_name with no_log To back up the log after a database device fails: dump tran[saction] database_name to stripe_device [ at backup_server_name ] [density = density_value, blocksize = number_bytes, capacity = number_kilobytes, dumpvolume = volume_name, file = file_name] [stripe on stripe_device [ at backup_server_name ] [density = density_value, blocksize = number_bytes, capacity = number_kilobytes, dumpvolume = volume_name, file = file_name]] [[stripe on stripe_device [ at backup_server_name ] [density = density_value, blocksize = number_bytes, capacity = number_kilobytes, dumpvolume = volume_name, file = file_name] ]...] [with { density = density_value, blocksize = number_bytes, capacity = number_kilobytes, dumpvolume = volume_name, file = file_name, [dismount | nodismount], [nounload | unload], retaindays = number_days, [noinit | init], no_truncate, notify = {client | operator_console}}] |
| execute | execute - Runs a procedure. [exec[ute]] [@return_status = ] [[[server.]database.]owner.]procedure_name[;number] [[@parameter_name =] value | [@parameter_name =] @variable [output] [,[@parameter_name =] value | [@parameter_name =] @variable [output]...]] [with recompile] |
| exp | exp - Returns the value that results from raising the constant e to the specified power. exp(approx_numeric) |
| fetch | result set. fetch cursor_name [ into fetch_target_list ] |
| floor | floor - Returns the largest integer that is less than or equal to the specified value. floor(numeric) |
| getdate | getdate - Returns the current system date and time. getdate() |
| goto Label | goto Label - Branches to a user-defined label. label: goto label |
| grant | grant - Assigns permissions to users or to user-defined roles. To grant permission to access database objects: grant {all [privileges]| permission_list} on { table_name [(column_list)] | view_name[(column_list)] | stored_procedure_name} to {public | name_list | role_name} [with grant option] To grant permission to execute certain commands: grant {all [privileges] | command_list} to {public | name_list | role_name} To grant a role to a user or a role: grant {role role_granted [, role_granted ...]} to grantee [, grantee...] |
| group by and having | group by and having - Used in select statements to divide a table into groups and to return only groups that match conditions in the having clause. Start of select statement [group by [all] aggregate_free_expression [, aggregate_free_expression]...] [having search_conditions] End of select statement |
| hextoint | hextoint - Returns the platform-independent integer equivalent of a hexadecimal string. hextoint (hexadecimal_string) |
| host_id | host_id - Returns the host process ID or the client process. host_id() |
| host_name | host_name - Returns the current host computer name of the client process. host_name() |
| if...else | if...else - Imposes conditions on the execution of a SQL statement. if logical_expression statements [else [if logical_expression] statement] |
| index_col | index_col - Returns the name of the indexed column in the specified table or view. index_col (object_name, index_id, key_# [, user_id]) |
| index_colorder | index_colorder - Returns the column order. index_colorder (object_name, index_id, key_# [, user_id]) |
| insert | insert [into] [database.[owner.]]{table_name|view_name} [(column_list)] {values (expression [, expression]...) |select_statement } |
| inttohex | inttohex - Returns the platform-independent hexadecimal equivalent of the specified integer. inttohex (integer_expression) |
| is_sec_service_on | is_sec_service_on - Returns 1 if the security service is active and 0 if it is not. is_sec_service_on(security_service_nm) |
| isnull | isnull - Substitutes the value specified in expression2 when expression1 evaluates to NULL. isnull(expression1, expression2) |
| kill | kill - Kills a process. kill spid |
| lct_admin | lct_admin - Returns the current value of the last-chance threshold. Aborts transactions in a transaction log that has reached its last-chance threshold. lct_admin({{"lastchance" | "logfull" }, database_id |"reserve", {log_pages | 0 } | "abort", process-id [, database-id]}) |
| load database | load database - Loads a backup copy of a user database, including its transaction log, that was created with dump database. load database database_name from stripe_device [at backup_server_name ] [density = density_value, blocksize = number_bytes, dumpvolume = volume_name, file = file_name] [stripe on stripe_device [at backup_server_name ] [density = density_value, blocksize = number_bytes, dumpvolume = volume_name, file = file_name] [[stripe on stripe_device [at backup_server_name ] [density = density_value, blocksize = number_bytes, dumpvolume = volume_name, file = file_name]]...] [with { density = density_value, blocksize = number_bytes, dumpvolume = volume_name, file = file_name, [dismount | nodismount], [nounload | unload], listonly [= full], headeronly, notify = {client | operator_console} }]] |
| load transaction | load transaction - Loads a backup copy of the transaction log that was created with the dump transaction command. load tran[saction] database_name from stripe_device [at backup_server_name] [density = density_value, blocksize = number_bytes, dumpvolume = volume_name, file = file_name] [stripe on stripe_device [at backup_server_name] [density = density_value, blocksize = number_bytes, dumpvolume = volume_name, file = file_name] [[stripe on stripe_device [at backup_server_name] [density = density_value, blocksize = number_bytes, dumpvolume = volume_name, file = file_name]]...] [with { density = density_value, blocksize = number_bytes, dumpvolume = volume_name, file = file_name, [dismount | nodismount], [nounload | unload], listonly [= full], headeronly, notify = {client | operator_console} until_time = datetime}]] |
| lock table | lock table - Explicitly locks a table within a transaction. lock table table_name in {share | exclusive } mode [ wait [ numsecs ] | nowait ] |
| log | log - Returns the natural logarithm of the specified number. log(approx_numeric) |
| log10 | log10 - Returns the base 10 logarithm of the specified number. log10(approx_numeric) |
| lower | lower - Returns the lowercase equivalent of the specified expression. lower(char_expr) |
| ltrim | ltrim - Returns the specified expression, trimmed of leading blanks. ltrim(char_expr) |
| max | max - Returns the highest value in an expression. max(expression) |
| min | min - Returns the lowest value in a column. min(expression) |
| mut_excl_roles | mut_excl_roles - Returns information about the mutual exclusivity between two roles. mut_excl_roles ("role1", "role2" ["membership" | "activation"]) |
| object_id | object_id - Returns the object ID of the specified object. object_id(object_name) |
| object_name | object_name - Returns the name of the object whose object ID is specified. object_name(object_id[, database_id]) |
| online database | online database - Brings a database online after loading a transaction log dumped with the standby_access option. online database database_name [for standby_access] |
| open | open - Opens a cursor for processing. open cursor_name |
| order by Clause | order by Clause - Returns query results in the specified column(s) in sorted order. [Start of select statement] [order by {[table_name.| view_name.]column_name | select_list_number | expression} [asc | desc] [,{[table_name.| view_name.] column_name select_list_number|expression} [asc |desc]]...] [End of select statement] |
| patindex | patindex - Returns the starting position of the first occurrence of a specified pattern. patindex("%pattern%", char_expr [, using {bytes | characters | chars} ] ) |
| pi | pi - Returns the constant value 3.1415926535897936. pi() |
| power | power - Returns the value that results from raising the specified number to a given power. power(value, power) |
| prepare transaction | prepare transaction - Used by DB-Library in a two-phase commit application to see if a server is prepared to commit a transaction. prepare tran[saction] |
| print - Prints a user-defined message on the user's screen. print {format_string | @local_variable | @@global_variable} [, arg_list] | |
| proc_role | proc_role - Returns 1 if the user has the correct role to execute the procedure; 0 if the user does not have this role. proc_role ("sa_role" | "sso_role" | "oper_role" | "replication_role" | "role_name" ) |
| ptn_data_pgs | ptn_data_pgs - Returns the number of data pages used by a partition. ptn_data_pgs(object_id, partition_id) |
| radians | radians - Returns the size, in radians, of an angle with the specified number of degrees. radians(numeric) |
| raiserror | raiserror - Prints a user-defined error message on the user's screen and sets a system flag to record that an error condition has occurred. raiserror error_number [{format_string | @local_variable}] [, arg_list] [with errordata restricted_select_list] |
| rand | rand - Returns a random value between 0 and 1, which is generated using the specified seed value. rand([integer]) |
| readtext | have exclusive locks on them, without waiting and without generating a message. readtext [[database.]owner.]table_name.column_name text_pointer offset size [holdlock | noholdlock] [shared] [readpast] [using {bytes | chars | characters}] [at isolation {read uncommitted | read committed | serializable}] |
| reconfigure | reconfigure - The reconfigure command currently has no effect; it is included to allow existing scripts to run without modification. In previous releases, reconfigure was required after the system procedure sp_configure to implement new configuration parameter settings. reconfigure |
| reorg | new pages, depending on the option used. reorg reclaim_space tablename [indexname] [with {resume, time = no_of_minutes}] [with {resume,time = no_of_minutes}] reorg compact tablename [with {resume, time = no_of_minutes}] reorg rebuild tablename |
| replicate | replicate - Returns a string consisting of the specified expression repeated a given number of times. replicate (char_expr, integer_expr) |
| reserved_pgs | reserved_pgs - Returns the number of pages allocated to the specified table or index. reserved_pgs(object_id, {doampg|ioampg}) |
| return | return - Exits from a batch or procedure unconditionally and provides an optional return status. Statements following return are not executed. return [integer_expression] |
| reverse | reverse - Returns the specified string with characters listed in reverse order. reverse(expression) |
| revoke | revoke - Revokes permissions or roles from users or roles. To revoke permission to access database objects: revoke [grant option for] {all [privileges] | permission_list} on { table_name [(column_list)] | view_name [(column_list)] | stored_procedure_name} from {public | name_list | role_name} [cascade] To revoke permission to create database objects, execute set proxy, or execute set session authorization: revoke {all [privileges] | command_list} from {public | name_list | role_name} To revoke a role from a user or another role: revoke role {role_name [, role_name ...]} from {grantee [, grantee ...]} |
| right | right - The rightmost part of the expression with the specified number of characters. right(expression, integer_expr) |
| role_contain | role_contain - Returns 1 if role2 contains role1. role_contain("role1", "role2") |
| role_id | role_id - Returns the system role ID of the role whose name you specify. role_id("role_name") |
| role_name | role_name - Returns the name of a role whose system role ID you specify. role_name([role_id]) |
| rollback | rollback - Rolls back a user-defined transaction to the named savepoint in the transaction or to the beginning of the transaction. rollback {tran[saction] | work} [transaction_name | savepoint_name] |
| rollback trigger | rollback trigger - Rolls back the work done in a trigger, including the data modification that caused the trigger to fire, and issues an optional raiserror statement. rollback trigger [with raiserror_statement] |
| round | round - Returns the value of the specified number, rounded to a given number of decimal places. round(number, decimal_places) |
| rowcnt | specified table. |
| rtrim | rtrim - Returns the specified expression, trimmed of trailing blanks. rtrim(char_expr) |
| save transaction | save transaction - Sets a savepoint within a transaction. save transaction savepoint_name |
| select | select - Allows specifying the locking scheme with select have exclusive locks on them without waiting and without generating a message. select [all | distinct] select_list [into [[database.]owner.]table_name reservepagegap = num_pages } ] ] [from [[database.]owner.]{view_name|table_name [(index {index_name | table_name } [parallel [degree_of_parallelism]] [prefetch size ][lru|mru])]} [holdlock | noholdlock] [readpast] [shared] [,[[database.]owner.]{view_name|table_name [(index {index_name | table_name } [parallel [degree_of_parallelism]] [prefetch size ][lru|mru])]} [holdlock | noholdlock] [readpast] [shared]]...] [where search_conditions] [group by [all] aggregate_free_expression [, aggregate_free_expression]... ] [having search_conditions] [order by {[[[database.]owner.]{table_name.|view_name.}] column_name | select_list_number | expression} [asc | desc] [,{[[[database.]owner.]{table_name|view_name.}] column_name | select_list_number | expression} [asc | desc]]...] [by column_name [, column_name]...]] [for {read only | update [of column_name_list]}] [at isolation { [ read uncommitted | 0 ] | [ read committed | 1 ] | [ repeatable read | 2 ]| [ serializable | 3 ] } ] |
| set | set - Sets Adaptive Server query-processing options for the duration of the user's work session. Can be used to set some options inside a trigger or stored procedure. Can also be used to activate or deactivate a role in the current session. set ansinull {on | off} set ansi_permissions {on | off} set arithabort [arith_overflow | numeric_truncation] {on | off} set arithignore [arith_overflow] {on | off} 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 {datefirst number, dateformat format, language language} set fipsflagger {on | off} set flushmessage {on | off} set identity_insert [database.[owner.]]table_name {on | off} set lock { wait [ numsecs ] | nowait } set offsets {select, from, order, compute, table, procedure, statement, param, execute} {on | off} set parallel_degree number set prefetch [on|off] set process_limit_action {abort | quiet | warning} set proxy [login_name] set quoted_identifier {on | off} set role {"sa_role" | "sso_role" | "oper_role" | role_name [with passwd "password"]} {on | off} set scan_parallel_degree number set session authorization [login_name] set showplan {on | off} set sort_resources {on | off} set statistics {io, subquerycache, time} {on | off} set string_rtruncation {on | off} set table count number set textsize {number} set transaction isolation level { [ read uncommitted | 0 ] | [ read committed | 1 ] | [ repeatable read | 2 ]| [ serializable | 3 ] } set transactional_rpc {on | off} |
| setuser | setuser - Allows a Database Owner to impersonate another user. setuser ["user_name"] |
| show_role | show_role - Shows the login's currently active system-defined roles. show_role() |
| show_sec_services | show_sec_services - Lists the security services that are active for the session. show_sec_services() |
| shutdown | shutdown - Shuts down the Adaptive Server from which the command is issued, its local Backup Server, or a remote Backup Server. This command can be issued only by a System Administrator. shutdown [srvname] [with {wait | nowait}] |
| sign | sign - Returns the sign (+1 for positive, 0, or -1 for negative) of the specified value. sign(numeric) |
| sin | sin - Returns the sine of the specified angle (in radians). sin(approx_numeric) |
| sortkey | sortkey - Generates values that can be used to order results based on collation behavior. sortkey (char_expression [, {collation_name | collation_ID}]) |
| soundex | soundex - Returns a 4-character code representing the way an expression sounds. soundex(char_expr) |
| space | space - Returns a string consisting of the specified number of single-byte spaces. space(integer_expr) |
| sqrt | sqrt - Returns the square root of the specified number. sqrt(approx_numeric) |
| str | str - Returns the character equivalent of the specified number. str(approx_numeric [, length [, decimal] ]) |
| stuff | stuff - Returns the string formed by deleting a specified number of characters from one string and replacing them with another string. stuff(char_expr1, start, length, char_expr2) |
| substring | substring - Returns the string formed by extracting the specified number of characters from another string. substring(expression, start, length) |
| sum | sum - Returns the total of the values. sum([all | distinct] expression) |
| suser_id | suser_id - Returns the server user's ID number from the syslogins table. suser_id([server_user_name]) |
| suser_name | suser_name - Returns the name of the current server user or the user whose server ID is specified. suser_name([server_user_id]) |
| tan | tan - Returns the tangent of the specified angle (in radians). tan(angle) |
| textptr | textptr - Returns the 16-byte binary pointer to the first page of the specified text column. textptr(column_name) |
| textvalid | textvalid - Returns 1 if the pointer to the specified text column is valid; 0 if it is not. textvalid("table_name.column_name", textpointer) |
| truncate table | truncate table [[database.]owner.]table_name |
| tsequal | tsequal - Compares timestamp values to prevent update on a |
| union Operator | union Operator - Returns a single result set that combines the are eliminated from the result set unless the all keyword is specified. select select_list [into clause] [from clause] [where clause] [group by clause] [having clause] [union [all] select select_list [from clause] [where clause] [group by clause] [having clause] ]... [order by clause] [compute clause] |
| update | update - The readpast clause allows the update command to update [[database.]owner.]{table_name | view_name} set [[[database.]owner.]{table_name.|view_name.}] column_name1 = {expression1|NULL|(select_statement)} | variable_name1 = {expression1|NULL|(select_statement)} [, column_name2 = {expression2|NULL|(select_statement)}]... | [, variable_name2 = {expression2|NULL|(select_statement)}]... [from [[database.]owner.]{view_name [readpast]| table_name [readpast] [(index {index_name | table_name } [ prefetch size ][lru|mru])]} [,[[database.]owner.]{view_name [readpast]| table_name [readpast] [(index {index_name | table_name } [ prefetch size ][lru|mru])]}] ...] [where search_conditions] |
| update partition statistics | update partition statistics - Updates information about the number of pages each partition for a partitioned table. update partition statistics table_name [partition_number] |
| update statistics | update statistics - Updates information about the distribution of key values in specified indexes or for specified columns, for all columns in an index, or for all columns on a table; allows specifying the number of steps for a histogram. update statistics table_name [ [index_name] | [( column_list ) ] ] [using step values] [with consumers = consumers ] update all statistics table_name update index statistics table_name [index_name] [using step values] [with consumers = consumers ] |
| upper | upper - Returns the uppercase equivalent of the specified string. upper(char_expr) |
| use | use - Specifies the database with which you want to work. use database_name |
| used_pgs | used_pgs - Returns the number of pages used by the specified table and its clustered index, or the number of pages in a nonclustered index. used_pgs(object_id, doampg, ioampg) |
| user | user - Returns the name of the current user. user |
| user_id | user_id - Returns the ID number of the specified user or of the current user in the database. user_id([user_name]) |
| user_name | user_name - Returns the name within the database of the specified user or of the current user. user_name([user_id]) |
| valid_name | valid_name - Returns 0 if the specified string is not a valid identifier or a number other than 0 if the string is a valid identifier. valid_name(character_expression) |
| valid_user | valid_user - Returns 1 if the specified ID is a valid user or alias in at least one database on this Adaptive Server. valid_user(server_user_id) |
| waitfor | waitfor - Specifies a specific time, a time interval, or an event for the execution of a statement block, stored procedure, or transaction. waitfor { delay time | time time | errorexit | processexit | mirrorexit } |
| where Clause | where Clause - Sets the search conditions in a select, insert, update, or delete statement. Search conditions immediately follow the keyword where in a select, insert, update, or delete statement. If you use more than one search condition in a single statement, connect the conditions with and or or where [not] expression comparison_operator expression where [not] expression [not] like "match_string" [escape "escape_character"] where [not] expression is [not] null where [not] expression [not] between expression and expression where [not] expression [not] in ({value_list | subquery}) where [not] exists (subquery) where [not] expression comparison_operator {any | all} (subquery) where [not] column_name join_operator column_name where [not] logical_expression where [not] expression {and | or} [not] expression |
| while | while - Sets a condition for the repeated execution of a statement or statement block. The statement(s) are executed repeatedly, as long as the specified condition is true. while logical_expression statement |
| writetext | writetext [[database.]owner.]table_name.column_name text_pointer [readpast] [with log] data |