General Database Options

General database options is the class of options consisting of all options except Transact-SQL compatibility options and Interactive SQL options.

Note: There are additional internal options not listed in this table that Sybase Technical Support might ask you to use.
General Database Specific Options

Option name

Allowed values

Default setting

AGGREGATION_PREFERENCE

-3 to 3

0

ALLOW_READ_CLIENT_FILE

ON, OFF

OFF

APPEND_LOAD

ON, OFF

OFF

AUDITING

ON, OFF

OFF

BIT_VECTOR_PINNABLE_CACHE_PERCENT*

0 – 100

40

BLOCKING

OFF

OFF

BT_PREFETCH_MAX_MISS

0 – 1000

2

BT_PREFETCH_SIZE

0 – 100

10

BTREE_PAGE_SPLIT_PAD_PERCENT

0 - 90

50

CACHE_PARTITIONS

power of 2, 0 to 64

0

CHECKPOINT_TIME

number of minutes

60

CIS_ROWSET_SIZE

integer

50

CONVERSION_MODE

0, 1

0

CONVERT_VARCHAR_TO_1242

ON, OFF

OFF

COOPERATIVE_COMMIT_TIMEOUT

integer

250

COOPERATIVE_COMMITS

ON, OFF

ON

CURSOR_WINDOW_ROWS

20 – 100000

200

DATE_FIRST_DAY_OF_WEEK

0 – 6

0

DATE_FORMAT

string

'YYYY-MM-DD'

DATE_ORDER

'YMD', 'DMY', 'MDY'

'YMD'

DBCC_LOG_PROGRESS

ON, OFF

OFF

DBCC_PINNABLE_CACHE_PERCENT

0 – 100

50

DEBUG_MESSAGES

ON, OFF

OFF

DEFAULT_DBSPACE

string

'' (empty string)

DEFAULT_DISK_STRIPING

ON, OFF

ON

DEDICATED_TASK

ON, OFF

OFF

DEFAULT_HAVING_SELECTIVITY_PPM

0 – 1000000

0

DEFAULT_KB_PER_STRIPE

1 – max unsigned bigint

1

DEFAULT_LIKE_MATCH_SELECTIVITY_PPM

0 – 1000000

150000

DEFAULT_LIKE_RANGE_SELECTIVITY_PPM

1 – 1000000

150000

DELAYED_COMMIT_TIMEOUT

integer

500

DELAYED_COMMITS

OFF

OFF

DISABLE_RI_CHECK

ON, OFF

OFF

EARLY_PREDICATE_EXECUTION

ON, OFF

ON

ENABLE_LOB_VARIABLES

ON, OFF

OFF

EXTENDED_JOIN_SYNTAX

ON, OFF

ON

FORCE_DROP

ON, OFF

OFF

FORCE_NO_SCROLL_CURSORS

ON, OFF

OFF

FORCE_UPDATABLE_CURSORS

ON, OFF

OFF

FP_LOOKUP_SIZE

1 MB – 4096 MB

16 MB

FP_LOOKUP_SIZE_PPM

1 – 1000000

2500

FP_PREDICATE_WORKUNIT_PAGES

integer

200

FP_PREFETCH_SIZE

0 – 100

10

FPL_EXPRESSION_MEMORY_KB

0 – 20000

1024

GARRAY_FILL_FACTOR_PERCENT

0 – 1000

25

GARRAY_INSERT_PREFETCH_SIZE

0 – 100

3

GARRAY_PAGE_SPLIT_PAD_PERCENT

0-100

25

GARRAY_RO_PREFETCH_SIZE

0 – 100

10

HASH_PINNABLE_CACHE_PERCENT*

0 – 100

20

HASH_THRASHING_PERCENT

0 – 100

10

HG_DELETE_METHOD

0 – 3

0

HG_SEARCH_RANGE

integer

10

HTTP_SESSION_TIMEOUT

integer (1 – 525600)

30

IDENTITY_ENFORCE_UNIQUENESS

ON, OFF

OFF

IDENTITY_INSERT

string

'' (empty string)

INDEX_ADVISOR

ON, OFF

OFF

INDEX_PREFERENCE

-10 – 10

0

INFER_SUBQUERY_PREDICATES

ON, OFF

ON

IN_SUBQUERY_PREFERENCE

-3 – 3

0

IQGOVERN_MAX_PRIORITY

1 – 3

2

IQGOVERN_PRIORITY

1 – 3

2

IQGOVERN_PRIORITY_TIME

1 – 1000000 seconds

0 (disabled)

ISOLATION_LEVEL

0, 1, 2, 3

0

JAVA_LOCATION

string

'' (empty string)

JAVA_VM_OPTIONS

string

'' (empty string)

JOIN_EXPANSION_FACTOR

0 – 100

30

JOIN_OPTIMIZATION

ON, OFF

ON

JOIN_PREFERENCE

-7 – 7

0

JOIN_SIMPLIFICATION_THRESHOLD

1 – 64

15

LARGE_DOUBLES_ACCUMULATOR

ON, OFF

OFF

LF_BITMAP_CACHE_KB

1 – 8

4

LOAD_ZEROLENGTH_ASNULL

ON, OFF

OFF

LOCKED

ON, OFF

OFF

LOG_CONNECT

ON, OFF

ON

LOG_CURSOR_OPERATIONS

ON, OFF

OFF

LOGIN_MODE

STANDARD, MIXED, INTEGRATED

STANDARD

LOGIN_PROCEDURE

string

sp_login_environment

MAIN_RESERVED_DBSPACE_MB

integer >= 200 in MB

200

MAX_CARTESIAN_RESULT

integer

100000000

MAX_CLIENT_NUMERIC_PRECISION

0 – 126

0

MAX_CLIENT_NUMERIC_SCALE

0 – 126

0

MAX_CONNECTIONS

0 - 2147483647

Unlimited

MAX_CUBE_RESULT

0 – 4294967295

10000000

MAX_CURSOR_COUNT

integer

50

MAX_DAYS_SINCE_LOGIN

0 - 2147483647

Unlimited

MAX_FAILED_LOGIN_ATTEMPTS

0 - 2147483647

Unlimited

MAX_HASH_ROWS

integer to 4294967295

2500000

MAX_IQ_THREADS_PER_CONNECTION

3 – 10000

144

MAX_IQ_THREADS_PER_TEAM

1 – 10000

144

MAX_JOIN_ENUMERATION

1 – 64

15

MAX_NON_DBA_CONNECTIONS

0 – 2147483647

Unlimited

MAX_PREFIX_PER_CONTAINS_PHRASE

0 – 300

1

MAX_QUERY_PARALLELISM

integer

64

MAX_QUERY_TIME

0 – 232 - 1

0 (disabled)

MAX_STATEMENT_COUNT

integer

100

MAX_TEMP_SPACE_PER_CONNECTION

integer

0

MAX_WARNINGS

integer

248 - 1

MINIMIZE_STORAGE

ON, OFF

OFF

MIN_PASSWORD_LENGTH

integer >= 0

0 characters

MONITOR_OUTPUT_DIRECTORY

string

database directory

NOEXEC

ON, OFF

OFF

NON_ANSI_NULL_VARCHAR

ON, OFF

OFF

NOTIFY_MODULUS

integer

100000

ODBC_DISTINGUISH_CHAR_AND_VARCHAR

ON, OFF

OFF

ON_CHARSET_CONVERSION_FAILURE

string

IGNORE

OS_FILE_CACHE_BUFFERING

ON, OFF

OFF

PASSWORD_GRACE_TIME

0 – 2147483647

0

PASSWORD_EXPIRY_ON_NEXT_LOGIN

ON, OFF

OFF

PASSWORD_LIFE_TIME

0 – 2147483647

Unlimited

POST_LOGIN_PROCEDURE

string

dbo.sa_post_login_procedure

PRECISION

126

126

PREFETCH

ON, OFF

ON

PREFETCH_BUFFER_LIMIT

integer

0

PREFETCH_BUFFER_PERCENT

0 – 100

40

PREFETCH_GARRAY_PERCENT

0 – 100

60

PREFETCH_SORT_PERCENT

0 – 100

20

PRESERVE_SOURCE_FORMAT

ON, OFF

ON

QUERY_DETAIL

ON, OFF

OFF

QUERY_NAME

string

'' (empty string)

QUERY_PLAN

ON, OFF

ON

QUERY_PLAN_AFTER_RUN

ON, OFF

OFF

QUERY_PLAN_AS_HTML

ON, OFF

OFF

QUERY_PLAN_AS_HTML_DIRECTORY

string

'' (empty string)

QUERY_PLAN_TEXT_ACCESS

ON, OFF

OFF

QUERY_PLAN_TEXT_CACHING

ON, OFF

OFF

QUERY_ROWS_RETURNED_LIMIT

integer

0

QUERY_TEMP_SPACE_LIMIT

integer

0

QUERY_TIMING

ON, OFF

OFF

RECOVERY_TIME

number of minutes

2

RETURN_DATE_TIME_AS_STRING

ON, OFF

OFF

ROW_COUNT

integer

0

SCALE

0 – 126

38

SIGNIFICANTDIGITSFORDOUBLEEQUALITY

0 – 15

0

SORT_COLLATION

Internal, collation_name, or collation_id

Internal

SORT_PINNABLE_CACHE_PERCENT*

0 – 100

20

SUBQUERY_CACHING_PREFERENCE

-3 – 3

0

SUBQUERY_FLATTENING_PERCENT

0, 1 - 232 -1

100

SUBQUERY_FLATTENING_PREFERENCE

-3 – 3

0

SUBQUERY_PLACEMENT_PREFERENCE

-1 – 1

0

SUPPRESS_TDS_DEBUGGING

ON, OFF

OFF

SWEEPER_THREADS_PERCENT

1 to 40

10

TDS_EMPTY_STRING_IS_NULL

ON, OFF

OFF

TEMP_DISK_PER_STRIPE

integer > 0 in KB

1

TEMP_EXTRACT_APPEND

ON, OFF

OFF

TEMP_EXTRACT_BINARY

ON, OFF

OFF

TEMP_EXTRACT_COLUMN_DELIMITER

string

','

TEMP_EXTRACT_DIRECTORY

string

'' (empty string)

TEMP_EXTRACT_ESCAPE_QUOTES

ON, OFF

OFF

TEMP_EXTRACT_NAME1TEMP_EXTRACT_NAME8

string

'' (empty string)

TEMP_EXTRACT_NULL_AS_EMPTY

ON, OFF

OFF

TEMP_EXTRACT_NULL_AS_ZERO

ON, OFF

OFF

TEMP_EXTRACT_QUOTE

string

'' (empty string)

TEMP_EXTRACT_QUOTES

ON, OFF

OFF

TEMP_EXTRACT_QUOTES_ALL

ON, OFF

OFF

TEMP_EXTRACT_ROW_DELIMITER

string

'' (empty string)

TEMP_EXTRACT_SIZE1 TEMP_EXTRACT_SIZE8

AIX & HP-UX: 0 – 64GB Sun Solaris: & Linux 0 – 512GB Windows: 0 – 128GB

0

TEMP_EXTRACT_SWAP

ON, OFF

OFF

TEMP_RESERVED_DBSPACE_MB

integer >= 200 in MB

200

TEMP_SPACE_LIMIT_CHECK

ON, OFF

ON

TEXT_DELETE_METHOD

0 – 2

0

TIME_FORMAT

string

'HH:NN:SS.SSS'

TIMESTAMP_FORMAT

string

'YYYY- MM-DD HH:NN:SS.SSS'

TOP_NSORT_CUTOFF_PAGES

1 – 1000

1

TRIM_PARTIAL_MBC

ON, OFF

OFF

USER_RESOURCE_RESERVATION

integer

1

VERIFY_PASSWORD_FUNCTION

string

'' (empty string)

WASH_AREA_BUFFERS_PERCENT

1 – 100

20

WAIT_FOR_COMMIT

ON, OFF

OFF

WD_DELETE_METHOD

0 – 3

0

Related concepts
Transact-SQL Compatibility Options
Interactive SQL Options
Alphabetical List of Options