:::: MENU ::::

Parameterization (PARAMETERIZATION) of SQL Server T-SQL Queries

  • Nov 05 / 2008
  • 0
dbDigger, Performance Tunning and Optimization

Parameterization (PARAMETERIZATION) of SQL Server T-SQL Queries

Parameterizing SQL queries enables the database optimizer to reuse a previously compiled query plan. By this way it eliminates the need for recompiling it for next invocations of the same query with differing parameter values. If a non parameterized SQL statement is executed, SQL Server internally tries to parameterize the statement to increase the possibility of matching it against an existing execution plan. This mode of parameterization is referred to as simple parameterization.
SQL Server 2005 also introduces a new parameterization mode called forced parameterization. With forced parameterization, all non parameterized SQL statements, subject to certain limitations, are force parameterized, and unlike simple parameterization the likelihood of SQL Server 2005 parameterizing those statements is far higher.
This option can be used to control whether the queries in the database will be simple or forced parameterized. When PARAMETERIZATION is set to SIMPLE, SQL Server will try to parameterize queries using the simple scheme unless a query hint has been specified for a particular query to force parameterize it. Conversely, when PARAMETERIZATION is set to FORCED, all queries will be force parameterized unless a query hint has been specified for a particular query to parameterize it using the simple scheme.

Consult us to explore the Databases. Contact us