Reference: Statements and Options

SQL Statements

Common Elements in SQL Syntax

Syntax Conventions

Statement Applicability Indicators

Row Limitation Clauses in SELECT Query Blocks

ALLOCATE DESCRIPTOR Statement [ESQL]

ALTER AGENT Statement

ALTER DATABASE Statement

ALTER DBSPACE Statement

ALTER DOMAIN Statement

ALTER EVENT Statement

ALTER FUNCTION Statement

ALTER INDEX Statement

ALTER LDAP SERVER Statement

ALTER LOGICAL SERVER Statement

ALTER LOGIN POLICY Statement

Login Policy Options

LDAP Login Policy Options

Multiplex Login Policy Configuration

Logical Server Access Configuration

ALTER LS POLICY Statement

ALTER MULTIPLEX RENAME Statement

ALTER MULTIPLEX SERVER Statement

ALTER PROCEDURE Statement

ALTER ROLE Statement

ALTER SEQUENCE statement

ALTER SERVER Statement

ALTER SERVICE Statement

ALTER SPATIAL REFERENCE SYSTEM Statement

ALTER TABLE Statement

ALTER TEXT INDEX Statement

ALTER TEXT CONFIGURATION Statement

ALTER TRIGGER statement

ALTER USER Statement

ALTER VIEW Statement

Identifying and Fixing Invalid Dependent Views

BACKUP DATABASE Statement

BEGIN … END Statement

BEGIN PARALLEL IQ … END PARALLEL IQ Statement

BEGIN TRANSACTION Statement [T-SQL]

CALL Statement

CASE Statement

CHECKPOINT Statement

CLEAR Statement [Interactive SQL]

CLOSE Statement [ESQL] [SP]

COMMENT Statement

COMMIT Statement

CONFIGURE Statement [Interactive SQL]

CONNECT Statement [ESQL] [Interactive SQL]

CREATE AGENT Statement

CREATE DATABASE Statement

CREATE DBSPACE Statement

CREATE DOMAIN Statement

CREATE EVENT Statement

CREATE EXISTING TABLE Statement

CREATE EXTERNLOGIN Statement

CREATE FUNCTION Statement

CREATE FUNCTION Statement (Java UDF)

CREATE INDEX Statement

CREATE LDAP SERVER Statement

CREATE LOGICAL SERVER Statement

CREATE LOGIN POLICY Statement

Login Policy Options

LDAP Login Policy Options

Multiplex Login Policy Configuration

CREATE LS POLICY Statement

CREATE MESSAGE Statement [T-SQL]

CREATE MULTIPLEX SERVER Statement

CREATE PROCEDURE Statement

Referencing Temporary Tables Within Procedures

CREATE PROCEDURE Statement [T-SQL]

CREATE PROCEDURE Statement (External Procedures)

CREATE PROCEDURE Statement (Java UDF)

CREATE PROCEDURE Statement (Table UDF)

CREATE ROLE Statement

CREATE SCHEMA Statement

CREATE SEQUENCE statement

CREATE SERVER Statement

CREATE SERVICE Statement

CREATE SPATIAL REFERENCE SYSTEM Statement

CREATE SPATIAL UNIT OF MEASURE Statement

CREATE TABLE Statement

CREATE TEXT CONFIGURATION Statement

CREATE TEXT INDEX Statement

CREATE TRIGGER statement

CREATE USER Statement

CREATE VARIABLE Statement

CREATE VIEW Statement

DEALLOCATE DESCRIPTOR Statement [ESQL]

Declaration Section [ESQL]

DECLARE Statement

DECLARE CURSOR Statement [ESQL] [SP]

DECLARE CURSOR Statement [T-SQL]

DECLARE LOCAL TEMPORARY TABLE Statement

DELETE Statement

DELETE (positioned) Statement [ESQL] [SP]

DESCRIBE Statement [ESQL]

DISCONNECT Statement [Interactive SQL]

DROP Statement

DROP AGENT Statement

DROP CONNECTION Statement

DROP DATABASE Statement

DROP EXTERNLOGIN Statement

DROP LDAP SERVER Statement

DROP LOGIN POLICY Statement

DROP LOGICAL SERVER Statement

DROP LS POLICY Statement

DROP MULTIPLEX SERVER Statement

DROP ROLE Statement

DROP SEQUENCE statement

DROP SERVER Statement

DROP SERVICE Statement

DROP SPATIAL REFERENCE SYSTEM Statement

DROP SPATIAL UNIT OF MEASURE Statement

DROP STATEMENT Statement [ESQL]

DROP TEXT CONFIGURATION Statement

DROP TEXT INDEX Statement

DROP TRIGGER statement

DROP USER Statement

DROP VARIABLE Statement

EXECUTE Statement [ESQL]

EXECUTE Statement [T-SQL]

EXECUTE IMMEDIATE Statement [ESQL] [SP]

EXIT Statement [Interactive SQL]

FETCH Statement [ESQL] [SP]

FOR Statement

FOR JSON Statement

FORWARD TO Statement

FROM Clause

GET DESCRIPTOR Statement [ESQL]

GOTO Statement [T-SQL]

GRANT CHANGE PASSWORD Statement

GRANT CONNECT Statement

GRANT CREATE Statement

GRANT Object-Level Privilege Statement

GRANT EXECUTE Statement

GRANT INTEGRATED LOGIN Statement

GRANT KERBEROS LOGIN Statement

GRANT ROLE Statement

GRANT SET USER Statement

GRANT System Privilege Statement

List of All System Privileges

GRANT USAGE ON SEQUENCE Statement

IF Statement

IF Statement [T-SQL]

INCLUDE Statement [ESQL]

INSERT Statement

INSTALL JAVA Statement

IQ UTILITIES Statement

LEAVE Statement

LOAD TABLE Statement

Storage Sizes

LOCK TABLE Statement

LOOP Statement

MESSAGE Statement

OPEN Statement [ESQL] [SP]

OUTPUT Statement [Interactive SQL]

PARAMETERS Statement [Interactive SQL]

PREPARE Statement [ESQL]

PRINT Statement [T-SQL]

PUT Statement [ESQL]

RAISERROR Statement [T-SQL]

READ Statement [Interactive SQL]

REFRESH TEXT INDEX Statement

RELEASE SAVEPOINT Statement

REMOVE Statement

RESIGNAL Statement

RESTORE DATABASE Statement

RESUME Statement

RETURN Statement

REVOKE CHANGE PASSWORD Statement

REVOKE CONNECT Statement

REVOKE CREATE Statement

REVOKE EXECUTE Statement

REVOKE INTEGRATED LOGIN Statement

REVOKE KERBEROS LOGIN Statement

REVOKE Object-Level Privilege Statement

REVOKE ROLE Statement

REVOKE SET USER Statement

REVOKE System Privilege Statement

List of All System Privileges

REVOKE USAGE ON SEQUENCE Statement

ROLLBACK Statement

ROLLBACK TO SAVEPOINT Statement

ROLLBACK TRANSACTION Statement [T-SQL]

SAVEPOINT Statement

SAVE TRANSACTION Statement [T-SQL]

SELECT Statement

SET Statement [ESQL]

SET Statement [T-SQL]

SET CONNECTION Statement [ESQL] [Interactive SQL]

SET DESCRIPTOR Statement [ESQL]

SET OPTION Statement

SET OPTION Statement [Interactive SQL]

SET SQLCA Statement [ESQL]

SETUSER Statement

SIGNAL Statement

START DATABASE Statement [Interactive SQL]

START ENGINE Statement [Interactive SQL]

START EXTERNAL ENVIRONMENT statement

START JAVA Statement

STOP DATABASE Statement [Interactive SQL]

STOP ENGINE Statement [Interactive SQL]

STOP EXTERNAL ENVIRONMENT statement

STOP JAVA Statement

TRIGGER EVENT Statement

TRUNCATE Statement

TRUNCATE TEXT INDEX Statement

UNION Operation

UPDATE Statement

UPDATE (positioned) Statement [ESQL] [SP]

VALIDATE Statement

VALIDATE LDAP SERVER Statement

WAITFOR Statement

WHENEVER Statement [ESQL]

WHILE Statement [T-SQL]

Database Options

Introduction to Database Options

Current Option Settings

Scope and Duration of Database Options

Temporary Options

PUBLIC Options

SECURITY Options

SYSTEM Options

Delete an Option Setting

Initial Option Settings

Deprecated Database Options

General Database Options

Data Extraction Options

Transact-SQL Compatibility Options

Transact-SQL Option Settings for SAP ASE Compatibility

Interactive SQL Options

Alphabetical List of Options

AFFINITY_AUTOEXCLUDE_TIMEOUT Option

AGGREGATION_PREFERENCE Option

ALLOW_NULLS_BY_DEFAULT Option [TSQL]

ALLOW_SNAPSHOT_VERSIONING Option

ANSI_CLOSE_CURSORS_ON_ROLLBACK Option [TSQL]

ANSI_PERMISSIONS Option [TSQL]

ANSINULL Option [TSQL]

ANSI_SUBSTRING Option [TSQL]

ANSI_UPDATE_CONSTRAINTS Option

ALLOW_READ_CLIENT_FILE Option

ASE_BINARY_DISPLAY Option

ASE_FUNCTION_BEHAVIOR Option

AUDITING Option [database]

BASE_TABLES_IN_RLV_STORE Option

BIT_VECTOR_PINNABLE_CACHE_PERCENT Option

BLOCKING Option

BLOCKING_TIMEOUT Option

BT_PREFETCH_MAX_MISS Option

BT_PREFETCH_SIZE Option

BTREE_PAGE_SPLIT_PAD_PERCENT Option

CACHE_AFFINITY_PERCENT Option

CACHE_PARTITIONS Option

CHAINED Option [TSQL]

CHECKPOINT_TIME Option

CIS_ROWSET_SIZE Option

CLOSE_ON_ENDTRANS Option [TSQL]

CONTINUE_AFTER_RAISERROR Option [TSQL]

CONVERSION_ERROR Option [TSQL]

CONVERSION_MODE Option

Restrict Implicit Binary Conversion Mode for LOAD TABLE

Restrict Implicit Binary Conversion Mode for INSERT

Restrict Implicit Binary Conversion Mode for UPDATE

Restrict Implicit Binary Conversion Mode for Positioned INSERT and Positioned UPDATE via Updatable Cursor

Restrict Implicit Binary Conversion Mode for Queries

CONVERT_VARCHAR_TO_1242 Option

COOPERATIVE_COMMIT_TIMEOUT Option

COOPERATIVE_COMMITS Option

CREATE_HG_WITH_EXACT_DISTINCTS

CREATE_HG_AND_FORCE_PHYSICAL_DELETE

CURSOR_WINDOW_ROWS Option

DATE_FIRST_DAY_OF_WEEK Option

DATE_FORMAT Option

DATE_ORDER Option

DBCC_LOG_PROGRESS Option

DBCC_PINNABLE_CACHE_PERCENT Option

DEBUG_MESSAGES Option

DEDICATED_TASK Option

DEFAULT_DBSPACE Option

DEFAULT_DISK_STRIPING Option

DEFAULT_HAVING_SELECTIVITY_PPM Option

DEFAULT_ISQL_ENCODING Option [Interactive SQL]

DEFAULT_KB_PER_STRIPE Option

DEFAULT_LIKE_MATCH_SELECTIVITY_PPM Option

DEFAULT_LIKE_RANGE_SELECTIVITY_PPM Option

DEFAULT_PROXY_TABLE_ROW_COUNT Option

DEFAULT_TABLE_UDF_ROW_COUNT Option

DELAYED_COMMIT_TIMEOUT Option

DELAYED_COMMITS Option

DISABLE_RI_CHECK Option

DIVIDE_BY_ZERO_ERROR Option [TSQL]

DQP_ENABLED Option

DQP_ENABLED_OVER_NETWORK Option

EARLY_PREDICATE_EXECUTION Option

ENABLE_ASYNC_IO Option

ENABLE_LOB_VARIABLES Option

EXTENDED_JOIN_SYNTAX Option

FLOATING_POINT_ACCUMULATOR Option

FORCE_DROP Option

FORCE_NO_SCROLL_CURSORS Option

FORCE_UPDATABLE_CURSORS Option

FP_LOOKUP_SIZE Option

FP_LOOKUP_SIZE_PPM Option

FP_NBIT_AUTOSIZE_LIMIT Option

FP_NBIT_ENFORCE_LIMITS Option

FP_NBIT_IQ15_COMPATIBILITY Option

FP_NBIT_LOOKUP_MB Option

FP_NBIT_ROLLOVER_MAX_MB Option

FP_PREDICATE_WORKUNIT_PAGES Option

FPL_EXPRESSION_MEMORY_KB Option

GARRAY_FILL_FACTOR_PERCENT Option

GARRAY_INSERT_PREFETCH_SIZE Option

GARRAY_PAGE_SPLIT_PAD_PERCENT Option

GARRAY_RO_PREFETCH_SIZE Option

HASH_PINNABLE_CACHE_PERCENT Option

HASH_THRASHING_PERCENT Option

HG_DELETE_METHOD Option

HG_SEARCH_RANGE Option

HTTP_SESSION_TIMEOUT Option

IDENTITY_ENFORCE_UNIQUENESS Option

IDENTITY_INSERT Option

IN_SUBQUERY_PREFERENCE Option

INDEX_ADVISOR Option

INDEX_ADVISOR_MAX_ROWS Option

INDEX_PREFERENCE Option

INFER_SUBQUERY_PREDICATES Option

IQ_LOG_MAX_SIZE Option

IQ_POINT_IN_TIME_RECOVERY_LOGGING Option

IQ_POINT_IN_TIME_RECOVERY_LOG_BACKUP_INTERVAL Option

IQGOVERN_MAX_PRIORITY Option

IQGOVERN_PRIORITY Option

IQGOVERN_PRIORITY_TIME Option

ISOLATION_LEVEL Option

JAVA_LOCATION Option

JAVA_VM_OPTIONS Option

JOIN_EXPANSION_FACTOR Option

JOIN_OPTIMIZATION Option

JOIN_PREFERENCE Option

JOIN_SIMPLIFICATION_THRESHOLD Option

LF_BITMAP_CACHE_KB Option

LOAD_ZEROLENGTH_ASNULL Option

LOG_CONNECT Option

LOG_CURSOR_OPERATIONS Option

LOG_DEADLOCKS Option

LOGIN_MODE Option

LOGIN_PROCEDURE Option

MAIN_RESERVED_DBSPACE_MB Option

MAX_CARTESIAN_RESULT Option

MAX_CLIENT_NUMERIC_PRECISION Option

MAX_CLIENT_NUMERIC_SCALE Option

MAX_CUBE_RESULT Option

MAX_CURSOR_COUNT Option

MAX_HASH_ROWS Option

MAX_IQ_THREADS_PER_CONNECTION Option

MAX_IQ_THREADS_PER_TEAM Option

MAX_JOIN_ENUMERATION Option

MAX_PARTITIONED_HASH_MB Option

MAX_PREFIX_PER_CONTAINS_PHRASE Option

MAX_QUERY_PARALLELISM Option

MAX_QUERY_TIME Option

MAX_RV_REMOTE_TRANSFER_MB Option

MAX_STATEMENT_COUNT Option

MAX_TEMP_SPACE_PER_CONNECTION Option

MINIMIZE_STORAGE Option

MIN_PASSWORD_LENGTH Option

MIN_ROLE_ADMINS Option

MONITOR_OUTPUT_DIRECTORY Option

MPX_AUTOEXCLUDE_TIMEOUT Option

MPX_HEARTBEAT_FREQUENCY Option

MPX_IDLE_CONNECTION_TIMEOUT Option

MPX_LIVENESS_TIMEOUT Option

MPX_MAX_CONNECTION_POOL_SIZE Option

MPX_MAX_UNUSED_POOL_SIZE Option

MPX_WORK_UNIT_TIMEOUT Option

NEAREST_CENTURY Option [TSQL]

NOEXEC Option

NON_ANSI_NULL_VARCHAR Option

NON_KEYWORDS Option [TSQL]

NOTIFY_MODULUS Option

ODBC_DISTINGUISH_CHAR_AND_VARCHAR Option

ON_CHARSET_CONVERSION_FAILURE Option

ON_ERROR Option [Interactive SQL]

ON_TSQL_ERROR Option [TSQL]

OS_FILE_CACHE_BUFFERING Option

OS_FILE_CACHE_BUFFERING_TEMPDB Option

POST_LOGIN_PROCEDURE Option

PRECISION Option

PREFETCH Option

PREFETCH_BUFFER_LIMIT Option

PREFETCH_BUFFER_PERCENT Option

PREFETCH_GARRAY_PERCENT Option

PREFETCH_SORT_PERCENT Option

PRESERVE_SOURCE_FORMAT Option [database]

PROGRESS_MESSAGES Option

QUERY_DETAIL Option

QUERY_NAME Option

QUERY_PLAN Option

QUERY_PLAN_AFTER_RUN Option

QUERY_PLAN_AS_HTML Option

QUERY_PLAN_AS_HTML_DIRECTORY Option

QUERY_PLAN_MIN_TIME Option

QUERY_PLAN_TEXT_ACCESS Option

QUERY_PLAN_TEXT_CACHING Option

QUERY_ROWS_RETURNED_LIMIT Option

QUERY_TEMP_SPACE_LIMIT Option

QUERY_TIMING Option

QUOTED_IDENTIFIER Option [TSQL]

RECOVERY_TIME Option

RESERVED_KEYWORDS Option

RETURN_DATE_TIME_AS_STRING Option

REVERT_TO_V15_OPTIMIZER Option

ROUND_TO_EVEN Option

ROW_COUNT Option

RV_AUTO_MERGE_EVAL_INTERVAL Option

RV_MAX_ACTIVE_SUBFRAGMENT_COUNT Option

RV_MERGE_NODE_MEMSIZE Option

RV_MERGE_TABLE_MEMPERCENT Option

RV_MERGE_TABLE_NUMROWS Option

RV_RESERVED_DBSPACE_MB Option

SCALE Option

SNAPSHOT_VERSIONING Option

SIGNIFICANTDIGITSFORDOUBLEEQUALITY Option

SORT_COLLATION Option

SORT_PINNABLE_CACHE_PERCENT Option

SQL_FLAGGER_ERROR_LEVEL Option [TSQL]

SQL_FLAGGER_WARNING_LEVEL Option [TSQL]

STRING_RTRUNCATION Option [TSQL]

SUBQUERY_CACHING_PREFERENCE Option

SUBQUERY_FLATTENING_PERCENT Option

SUBQUERY_FLATTENING_PREFERENCE Option

SUBQUERY_PLACEMENT_PREFERENCE Option

SUPPRESS_TDS_DEBUGGING Option

SWEEPER_THREADS_PERCENT Option

TDS_EMPTY_STRING_IS_NULL Option [database]

TEMP_EXTRACT_APPEND Option

TEMP_EXTRACT_BINARY Option

TEMP_EXTRACT_COLUMN_DELIMITER Option

TEMP_EXTRACT_DIRECTORY Option

TEMP_EXTRACT_ESCAPE_QUOTES Option

TEMP_EXTRACT_NAMEn Options

TEMP_EXTRACT_NULL_AS_EMPTY Option

TEMP_EXTRACT_NULL_AS_ZERO Option

TEMP_EXTRACT_QUOTE Option

TEMP_EXTRACT_QUOTES Option

TEMP_EXTRACT_QUOTES_ALL Option

TEMP_EXTRACT_ROW_DELIMITER Option

TEMP_EXTRACT_SIZEn Options

TEMP_EXTRACT_SWAP Option

TEMP_RESERVED_DBSPACE_MB Option

TEMP_SPACE_LIMIT_CHECK Option

TEXT_DELETE_METHOD Option

TIME_FORMAT Option

TIMESTAMP_FORMAT Option

TOP_NSORT_CUTOFF_PAGES Option

TRIM_PARTIAL_MBC Option

TRUSTED_CERTIFICATES_FILE Option

TSQL_VARIABLES Option [TSQL]

USER_RESOURCE_RESERVATION Option

VERIFY_PASSWORD_FUNCTION Option

WASH_AREA_BUFFERS_PERCENT Option

WAIT_FOR_COMMIT Option

WD_DELETE_METHOD Option