Literal Parameterization

Adaptive Server version 15.0.1 lets you automatically convert literal values in SQL queries to parameter descriptions (similar to variables).

In earlier versions of Adaptive Server, two queries that were identical except for one or more literal values resulted in the statement cache storing two separate query plans, or two additional rows in sysqueryplans. For example, the query plans for these queries were stored separately, even though they are almost identical:

select count(*) from titles where total_sales > 100
select count(*) from titles where total_sales > 200

Adaptive Server version 15.0.1 allows you to automatically convert literal values in SQL queries to parameter descriptions (similar to variables). A new sp_configure option supports this feature, which is called enable literal autoparam.

To enable or disable enable literal autoparam server-wide, use:

sp_configure "enable literal autoparam", [0 | 1]

Where 1 automatically converts literals to parameters, and 0 disables the feature. The default is 1.

You can set literal parameterization at the session level with the set command:

set literal_autoparam [off | on]