What does it mean for a query to be autoparameterized?

• michael.leach2015

  SSCommitted

  Points: 1597

  More actions

  May 12, 2019 at 5:26 am

  #3640779

  See the orange box called caution.  This is where I came across this term:

  https://docs.microsoft.com/en-us/sql/t-sql/data-types/int-bigint-smallint-and-tinyint-transact-sql?view=sql-server-2017

   

  Also, from the 2nd paragraph of the "caution" section, we have this:

  "When a query is <span style="text-decoration: underline;">not autoparameterized</span>, the constant value is first converted to numeric,"

  Then from the 3rd paragraph of the "caution" section, we have this:

  "When a query <span style="text-decoration: underline;">is autoparameterized</span>, the constant value is <span style="text-decoration: underline;">always</span> converted to numeric (10, 0) before converting to the final data type"

  So when we are autoparameterizing, the constant is <span style="text-decoration: underline;">always converted</span> to a numeric type.  This implies that when we are <span style="text-decoration: underline;">not autoparameterizing</span>, there are cases when constant values <span style="text-decoration: underline;">may not be converted</span> to the numeric data type.  When are constant values not converted to the numeric data type?

   

   

• Thom A

  SSC Guru

  Points: 99133

  More actions

  May 12, 2019 at 11:55 am

  #3640880

  Couple of articles that might help you understand: Auto-Parameterization in SQL Server & How To Tell If Your Query Has Been Auto-Parameterized (and why wasn’t it?)

  As for your question "When are constant values not converted to the numeric data type?": That's covered in the document you were talking about:

  When you use the +, -, *, /, or % arithmetic operators to perform implicit or explicit conversion of int, smallint, tinyint, or bigint constant values to the float, real, decimal or numeric data types, the rules that SQL Server applies when it calculates the data type and precision of the expression results differ depending on whether the query is autoparameterized or not.

  Emphasis mine. So when you don't use arithmetic to change the data type, or won't change an intto a numeric.

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk