SQL Settings Reference

InterSystems SQL enables you to control various settings to set up your SQL environment. These settings can be controlled through various interfaces, including the Configuration Parameter File, the Management Portal, the SET OPTION command, and methods in the %SYSTEM.SQL.Util class. However, not all options are available through all interfaces and some have different names across the multiple interfaces they might be available through. This page acts as a reference for how you can change each of the settings.

Note:

To enable SQL security, enable its system-wide security parameter.

How to Change Each SQL Setting

Since there are many different SQL settings and multiple mechanisms to change them, the table below provides a summary of how you may change each one.

To find the SQL settings page in the Management Portal, navigate to System Administration > Configuration > SQL and Object Settings > SQL.

Option Name / Management Portal Name (if applicable)	CPF Name	API	SET OPTION Name
AdaptiveMode / Turn off Adaptive Mode to disable run time plan choice and automatic tuning	AdaptiveMode	SetOption()	n/a
AllowExtrinsicFunction	ExtrinsicFunctions	SetOption()	n/a
AllowRowIDUpdate	AllowRowIDUpdate	n/a	n/a
ANSIPrecedence	ANSIPrecedence	SetOption()	n/a
AutoCommit	n/a	SetOption()	n/a
AutoParallel / Execute queries in a single process	AutoParallel	SetOption()	n/a
AutoParallelThreshold	AutoParallelThreshold	SetOption()	AUTO_PARALLEL_ THRESHOLD
BiasQueriesAsOutlier	BiasQueriesAsOutlier	n/a	n/a
BitmapFriendlyCheck	n/a	SetOption()	n/a
CachedQueryLockTimeout	n/a	SetOption()	n/a
CachedQuerySaveSource / Retain cached query source	SaveMAC	SetOption()	n/a
ClientMaxIdleTime / Client maximum idle time (seconds)	ClientMaxIdleTime	SetOption()	n/a
CollectionProjection	n/a	SetOption()	n/a
CompileModeDeferred	n/a	SetOption()	COMPILEMODE
CompileModeImmediate	n/a	SetOption()	COMPILEMODE
CompileModeInstall	n/a	SetOption()	COMPILEMODE
CompileModeNocheck	n/a	SetOption()	COMPILEMODE
DDLDefineBitmapExtent	DDLDefineBitmapExtent	SetOption()	n/a
DDLDropTabDelData	DropDelete	SetOption()	n/a
DDLFinal	DDLFinal	SetOption()	n/a
DDLNo201 / Ignore redundant DDL	DDLNo201	n/a	n/a
DDLNo30 / Ignore redundant DDL	DDLNo30	n/a	n/a
DDLNo307 / Ignore redundant DDL	DDLNo307	n/a	n/a
DDLNo311 / Ignore redundant DDL	DDLNo311	n/a	n/a
DDLNo315 / Ignore redundant DDL	DDLNo315	n/a	n/a
DDLNo324 / Ignore redundant DDL	DDLNo324	n/a	n/a
DDLNo333 / Ignore redundant DDL	DDLNo333	n/a	n/a
DDLPKeyNotIDKey / Define primary key as ID key for tables created via DDL	IDKey	SetOption()	PKEY_IS_IDKEY
DDLSQLOnlyCompile	DDLSQLOnlyCompile	n/a	n/a
DDLUseExtentSet	DDLUseExtentSet	SetOption()	n/a
DDLUseSequence	DDLUseSequence	SetOption()	n/a
DefaultSchema / Default schema	DefaultSchema	n/a	DEFAULT_SCHEMA
DefaultTimePrecision	TimePrecision	SetOption()	n/a
DelimitedIdentifiers	DelimitedIds	SetOption()	SUPPORT_DELIMITED_ IDENTIFIERS
ECPSync	ECPSync	SetOption()	n/a
FastDistinct / GROUP BY and DISTINCT queries must produce original values	FastDistinct	SetOption()	EXACT_DISTINCT
FilterRefIntegrity	ReferentialChecks	SetOption()	n/a
IdentityInsert	n/a	SetOption()	n/a
IdTrxFrom	IdTrxFrom	SetDDLIdentifier Translations()	n/a
IdTrxTo	IdTrxTo	SetDDLIdentifier Translations()	n/a
IsolationMode	n/a	SetOption()	n/a
LockThreshold / Lock escalation threshold	LockThreshold	SetOption()	LOCK_ESCALATION_ THRESHOLD
LockTimeout / Lock timeout (seconds)	LockTimeout	SetOption()	LOCK_TIMEOUT
ODBCVarcharMaxlen / Default length for VARCHAR	ODBCVarcharMaxlen	n/a	n/a
ProcessLockTimeout	n/a	SetOption()	n/a
ParameterSampling / Turn on parameter sampling to sample the parameter value for query execution	ParameterSampling	SetOption()	n/a
QueryProcedures	QueryProcedures	SetOption()	n/a
RetainSQL	Comment	SetOption()	n/a
RTPC	RTPC	SetOption()	n/a
SQLFunctionArgConversion	n/a	SetOption()	n/a
SelectMode	n/a	SetOption()	n/a
ServerDisconnectCode	n/a	SetOption()	n/a
ServerInitCode	n/a	SetOption()	n/a
TCPKeepAlive / TCP keepalive for client connections (seconds)	TCPKeepAlive	SetOption()	n/a
ToDateDefaultFormat / TO_DATE default format	TODATEDefaultFormat	SetOption()	n/a

Settings that Require Permissions

In order to change the following settings, a user must have the %Admin:USE permission:

AdaptiveMode	ExtrinsicFunctions	ANSIPrecedence	AutoParallel
SaveMAC	DropDelete	DDLFinal	IDKey
DDLUseExtentSet	DDLUseSquence	TimePrecision	DelimitedIds
ECPSync	FastDistinct	ReferentialChecks	IdTrxFrom
IdTrxTo	LockThreshold	LockTimeout	ParameterSampling
QueryProcedures	Comment	RTPC	TCPKeepAlive
TODATEDefaultFormat