TSQL Settings
Settings are used to tailor the behavior of the compiler and colorizer. The TSQL configuration options are part of the standard InterSystems IRIS® data platform configuration.
InterSystems IRIS supports the following TSQL settings:
-
CASEINSCOMPARE (String comparison is not case-sensitive.)
These values are used to set the corresponding ^%SYS("tsql","SET",...) global array values.
You can view and modify these settings using the InterSystems IRIS Management Portal and/or the %SYSTEM.TSQLOpens in a new tab Get and Set class methods.
-
Go into the InterSystems IRIS Management Portal. Go to System Administration, Configuration, SQL and Object Settings, TSQL Compatibility. Here you can specify the DIALECT (Sybase or MSSQL, default is Sybase), and turn on or off the ANSI_NULLS, CASEINSCOMPARE, and QUOTED_IDENTIFIER settings.
If you change one or more configuration settings, this is indicated by an asterisk (*) in the upper left-hand corner of the screen immediately following the Management Portal path. For example, System > Configuration > TSQL Settings (configuration settings)*. You must press the Save button for configuration changes to take effect.
-
Invoke the $SYSTEM.TSQL.CurrentSettings()Opens in a new tab method to display the settings:
DO ##class(%SYSTEM.TSQL).CurrentSettings()
You can use %SYSTEM.TSQLOpens in a new tab class methods to get or set these settings. These methods take a dialect string and change both the current dialect and the specified setting. There are not separate settings for each TSQL dialect. For example, changing CaseInsCompare changes this configuration setting for both Sybase and MSSQL.
You can also change InterSystems IRIS configuration settings to be more compatible with TSQL and to provide better performance. The following configurable options are described on this page:
DIALECT
The DIALECT configuration option allows you to select the Transact-SQL dialect. The available options are Sybase and MSSQL. The default is Sybase. You can return the current setting using $SYSTEM.TSQL.GetDialect()Opens in a new tab. This option is set system-wide using the InterSystems IRIS Management Portal or by using the $SYSTEM.TSQL.SetDialect()Opens in a new tab method:
WRITE ##class(%SYSTEM.TSQL).SetDialect("Sybase")
This method returns the prior Dialect setting.
If DIALECT=MSSQL: a DECLARE statement binds host variable values.
If DIALECT=Sybase: host variable values are refreshed for each cursor OPEN.
You can also set InterSystems SQL to handle Transact-SQL source code by overriding the InterSystems SQL default:
To set the Transact-SQL dialect in InterSystems Dynamic SQL.
To set the Transact-SQL dialect in the Management Portal SQL interface
To set the Transact-SQL dialect in the InterSystems SQL Shell.
To set the Transact-SQL dialect in JDBC.
ANSI_NULLS
The ANSI_NULLS configuration option allows you to specify whether comparisons to a null value return true or false. The default is OFF.
-
ON: All comparisons to a null value evaluate to Unknown. For example, Age = Null returns false, even when Age is null. Null is unknown, so it is false/unknown to specify null=null.
-
OFF: For each row, a comparison of a field value to NULL evaluates to True if the field does not contain a value. For example, Age = Null returns True for each row where Age does not contain a value. However, you cannot use ANSI_NULLS OFF to compare null values in two different fields. Comparisons of two fields that do not contain a value are always false. For example, Age = DateOfBirth and Age != DateOfBirth both return False when both fields do not contain a value.
You can determine the current ANSI_NULLS setting using %SYSTEM.TSQLOpens in a new tab class methods, or from the TSQLAnsiNullsOpens in a new tab property, as follows:
SET context=##class(%SYSTEM.Context.SQL).%New()
WRITE "ANSI_NULLS is = ",context.TSQLAnsiNulls
You can return the current setting using $SYSTEM.TSQL.GetAnsiNulls()Opens in a new tab. This method returns both the current default dialect and the current ANSI_NULLS setting as a comma-separated string: for example, MSSQL, ON.
You can activate (ON) or deactivate (OFF) ANSI_NULLS system-wide using the InterSystems IRIS Management Portal or by using the $SYSTEM.TSQL.SetAnsiNulls()Opens in a new tab method:
WRITE ##class(%SYSTEM.TSQL).SetAnsiNulls("Sybase","OFF")
This method sets both the default dialect and the ANSI_NULLS setting, returns the prior settings as a comma-separated string: for example, MSSQL, ON.
CASEINSCOMPARE
The CASEINSCOMPARE setting specifies non-case-sensitive equality comparisons, such as 'A'='a'. The default is OFF. If this option is set to ON, the comparison operators = and <> operate without regard to case in most contexts. However, there are a few contexts where such insensitivity does not apply:
-
Where a comparison is the ON condition for a JOIN.
-
Where either operand is a subquery.
These exceptions exist because InterSystems SQL does not accept the %SQLUPPER operator in these contexts.
You can determine the current CASEINSCOMPARE setting using %SYSTEM.TSQLOpens in a new tab class methods, or from the TSQLCaseInsCompareOpens in a new tab property, as follows:
SET context=##class(%SYSTEM.Context.SQL).%New()
WRITE "ANSI_NULLS is = ",context.TSQLCaseInsCompare
You can return the current setting using $SYSTEM.TSQL.GetCaseInsCompare()Opens in a new tab. You can activate (ON) or deactivate (OFF) CASEINSCOMPARE system-wide using using the InterSystems IRIS Management Portal or by using the $SYSTEM.TSQL.SetCaseInsCompare()Opens in a new tab method:
WRITE ##class(%SYSTEM.TSQL).SetCaseInsCompare("Sybase","OFF")
This method returns the prior CASEINSCOMPARE setting.
QUOTED_IDENTIFIER
The QUOTED_IDENTIFIER configuration option allows you to select whether quoted identifiers are supported. The default is OFF (not supported). This option is set using the InterSystems IRIS Management Portal. When QUOTED_IDENTIFIER is on, double quotes are parsed as delimiting an identifier. When QUOTED_IDENTIFIER is off, double quotes are parsed as alternative delimiters for string literals. The preferable delimiters for string literals are single quotes.
You can determine the current QUOTED_IDENTIFIER setting using %SYSTEM.TSQLOpens in a new tab class methods, or from the TSQLQuotedIdentifierOpens in a new tab property, as follows:
SET context=##class(%SYSTEM.Context.SQL).%New()
WRITE "ANSI_NULLS is = ",context.TSQLQuotedIdentifier
You can return the current setting using $SYSTEM.TSQL.GetQuotedIdentifier()Opens in a new tab. You can activate (ON) or deactivate (OFF) QUOTED_IDENTIFIER system-wide using using the InterSystems IRIS Management Portal or by using the $SYSTEM.TSQL.SetQuotedIdentifier()Opens in a new tab method:
WRITE ##class(%SYSTEM.TSQL).SetQuotedIdentifier("Sybase","OFF")
This method returns the prior QUOTED_IDENTIFIER setting.
Equal Literal Replacement
The Equal Literal Replacement configuration option is set using a method; is not available from the Management Portal. It controls the behavior of the TSQL compiler. You can return the current setting using $SYSTEM.TSQL.GetEqualLiteralReplacement()Opens in a new tab. The default is ON.
You can activate (ON) or deactivate (OFF) Equal Literal Replacement system-wide using the $SYSTEM.TSQL.SetEqualLiteralReplacement()Opens in a new tab method:
WRITE ##class(%SYSTEM.TSQL).SetEqualLiteralReplacement("Sybase","OFF")
Setting SetEqualLiteralReplacement("Sybase","OFF") means TSQL queries with a WHERE clause equal sign (=) predicate or an IN(...) predicate will not perform literal substitution for literal values on the left or right side of the equal sign, or for any literal value in the IN predicate. This can help the query optimizer choose a better plan when the condition includes fields that have an outlier value.
TRACE
The TRACE configuration option creates a log file of the execution of TSQL procedures. When a TSQL stored procedure (method or system stored procedure) is compiled with TRACE active, running a TSQL procedure will log trace messages to the active tsql log file.
A separate tsql trace log file is created for each process from which TSQL procedures are run. Trace is activated system-wide; trace log files are namespace-specific.
TRACE is not set using the Management Portal. This option is set system-wide using the $SYSTEM.TSQL.SetTrace()Opens in a new tab method. No dialect is specified:
WRITE ##class(%SYSTEM.TSQL).SetTrace("ON")
You can return the current setting using $SYSTEM.TSQL.GetTrace()Opens in a new tab.
You can also activate (1) or deactivate (0) TRACE system-wide using the following ObjectScript command:
SET ^%SYS("tsql","TRACE")=1
To return the current trace setting:
WRITE ^%SYS("tsql","TRACE")
The TRACE log file records a timestamp for each operation, the elapsed time for each operation, a global references count and a %ROWCOUNT (where applicable). Note that TRUNCATE TABLE always returns a %ROWCOUNT of -1. If an operation involves sharded tables, the global references count is only for the process the procedure is executed on. Work sent to the other shards is not included in the global reference count.
The TRACE log file represents a temporary table using the internal temporary table name; it displays the corresponding user-specified #TempTable name in a /* mytemptable */ comment.
The TRACE log file is created in your InterSystems IRIS instance in the mgr directory, in the subdirectory for the current namespace. It is named using the current process number. For example: IRIS/mgr/user/ tsql16392.log. The following is a typical TRACE log file:
IRIS TSQL Log, created 07/06/2020 13:44:41.020101 by process 16392 Version: IRIS for Windows (x86-64) 2020.2 (Build 211U) Fri Jun 26 2020 13:19:52 EDT User: glenn 07/06/2020 13:44:41.020488 PREPARE EXECUTEPROC: Sample.StuffProc 07/06/2020 15:02:44.270773 PREPARE EXECUTEPROC: sp_addtype 07/06/2020 15:04:50.625108 PREPARE EXECUTEPROC: sp_addtype Log restarted: 07/06/2020 15:15:42 07/06/2020 15:15:42.623033 CALLSP:: CreateMyTableProc() 07/06/2020 15:15:42.624807 EXECUTE CREATE TABLE Sample.MyTable (Name SHORTSTR, BigName MIDSTR): Elapsed time = .313114s # Global Refs = 17,446 RETURN:: CreateMyTable with value = 0 07/06/2020 15:15:42.938084 context object: 154@%Library.ProcedureContext Context status is OK 07/06/2020 15:23:42.171761 CALLSP:: CreateMyTable() 07/06/2020 15:23:42.174175 EXECUTE CREATE TABLE Sample.MyTable (Name SHORTSTR, BigName MIDSTR): ERROR: -201 Table 'Sample.MyTable' already exists SQLCODE = -400 Elapsed time = .002356s # Global Refs = 151 RETURN:: CreateMyTable with value = 0 07/06/2020 15:23:42.176979 context object: 485@%Library.ProcedureContext Error: ERROR #5540: SQLCODE: -201 Message: Table 'Sample.MyTable' already exists
Cached Query Source
To aid in debugging, it is also desirable to retain cached query source code as well as the generated cached queries. You can configure this option as follows:
SET status=$SYSTEM.SQL.Util.SetOption("CachedQuerySaveSource",1,.oldval)
Data Collation and String Truncation
The default collation for InterSystems SQL is SQLUPPER. This is not the best match for the collation order native to TSQL.
Sybase supports several different collation sort orders. The default is binary. This default is described as follows: “Sorts all data according to numeric byte values for that character set. Binary order sorts all ASCII uppercase letters before lowercase letters. Accented or ideographic (multibyte) characters sort in their respective standards order, which may be arbitrary. All character sets have binary order as the default.”
This binary collating order best matches the InterSystems IRIS SQLSTRING collating order. Therefore, changing the SQL collation sequence from SQLUPPER to SQLSTRING is likely the best compatibility option, though it is not guaranteed to be correct for all characters.
-
To set the default collation for the current namespace: SET status=$$SetEnvironment^%apiOBJ("COLLATION","%Library.String","SQLSTRING")
-
To set the default collation system-wide: SET ^%oddENV("collation","%Library.String")="SQLSTRING"
SQLSTRING collation preserves trailing blank spaces in strings when data is loaded in the database. When these strings are processed by stored procedures, these trailing blank spaces can cause errors. It is therefore recommended that you configure string data types to truncate strings when they are read. You can configure string data types as follows:
SET status=$$SetSysDatatypes^%SYS.CONFIG(,"CHAR","%Library.String(MAXLEN=1,TRUNCATE=1)")
SET status=$$SetSysDatatypes^%SYS.CONFIG(,"CHAR(%1)","%Library.String(MAXLEN=%1,TRUNCATE=1)")
SET status=$$SetSysDatatypes^%SYS.CONFIG(,"VARCHAR","%Library.String(MAXLEN=1,TRUNCATE=1)")
SET status=$$SetSysDatatypes^%SYS.CONFIG(,"VARCHAR(%1)","%Library.String(MAXLEN=%1,TRUNCATE=1)")
SET status=$$SetSysDatatypes^%SYS.CONFIG(,"VARCHAR(%1,%2)","%Library.String(MAXLEN=%1,TRUNCATE=1)")
Timestamp and Time Precision
The default timestamp data type for InterSystems SQL is %TimeStamp. If a TSQL database has many datetime fields (as is often the case with financial databases) using %PosixTime as the default timestamp data type can reduce the amount of disk space required, therefore increasing row access speed, both for reading and writing. You can configure timestamp data types as follows:
SET status=$$SetSysDatatypes^%SYS.CONFIG(,"DATETIME","%Library.PosixTime")
SET status=$$SetSysDatatypes^%SYS.CONFIG(,"DATETIME2","%Library.PosixTime")
SET status=$$SetSysDatatypes^%SYS.CONFIG(,"TIMESTAMP","%Library.PosixTime")
You should also configure the default time precision to the desired number of decimal places of precision, as shown in the following example:
SET status=$SYSTEM.SQL.Util.SetOption("DefaultTimePrecision",6,.oldval)
Settings for Temporary Databases
For improved performance, it may be advantageous to change temporary database settings and work database settings to remove standard checks. These changes may not be appropriate for all TSQL environments. Note that work tables are temporary in nature, but could survive from one stored procedure run to another.