General database options is the class of options consisting of all options except Transact-SQL compatibility options and Interactive SQL 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_NAME1 – TEMP_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 |