INF: Optimizing Comparisons with Numeric, Integer, and Others

• Microsoft SQL Server version 6.5

SUMMARY

Data type optimization with numeric, decimal, and integer data types use some very strict rules.

This article explains and clarifies the conditions and constraints that the optimizer uses to perform exact numeric data type comparisons.

Review the following section to help clarify the terms and concepts used in this article.

"Precision" is the number of digits in a number. "Scale" is the number of digits to the right of the decimal point in a number. For example, the number 123.45 has a precision of 5 and a scale of 2.

Due to limitations with the binary numbering system used by computers, some decimal fractions simply cannot be represented exactly. For example, the decimal fraction 0.1 does not have an exact binary representation. It can only be approximated. It is for this reason that floating point and real data types are considered approximate values; whereas integer, numeric, and decimal data types are treated as exact data types.

The terms "strict" or "exact" are referred to in their computational definition. For example, a numeric(10,1) will not exactly or strictly compare with a numeric(10,2) even if the numbers involved are mathematically equivalent. Mathematically, a value of 10.1 and 10.10 are exactly the same. However, due to the difference in scale, they are not treated as a computationally exact match.

MORE INFORMATION

To understand the optimizer's comparison choices, you must first understand the way incoming data is parsed and handled.

• When the SQL Server receives a value of 10 or fewer digit positions without a decimal point, the value is treated as an integer data type.



• When the SQL Server receives a value of 11 or more digit positions, it is treated as an exact data type (decimal or numeric).



• When the SQL Server receives a value with a decimal point, it is treated as an exact data type.



• Numeric and decimal (8,0) or smaller values that do not require decimal positions can be represented by an integer value. Whenever a strict comparison is performed on a numeric or decimal (that is, (8,0), (7,0), (6,0) and so on), the integer value is always considered a more precise data type.

   create table tblTest( a numeric(8,0) PRIMARY KEY, b int)
   select * from tblTest where a = 123 

• The incoming argument is considered an integer value.



• The column definition is (8,0) or less, so the integer is considered a more precise data type. Thus, the conversion must be applied to the data column and a less than optimal plan may be selected.

   create table tblTest( a numeric(10,0) PRIMARY KEY, b int)
   select * from tblTest where a = 123. 

• The incoming argument contains a decimal point and no decimal positions are represented in the WHERE clause so the argument is seen as an exact data type of decimal or numeric (n,0).



• The column definition is (10,0) so the argument can be promoted and compared exactly. This generally results in a query plan that will use a direct index search.

   create table tblTest( a numeric(10,0) PRIMARY KEY, b int)
   select * from tblTest where a = 123.0 

• The incoming argument contains a decimal point and a single decimal position. This results in an exact data type (n,1).
  
  NOTE: The decimal position in this query is very important. The query submitted is stating that it mathematically wants to compare at a precision and scale of (n,1) NOT (n,0).



• The column definition is (10,0) so the argument is considered a more precise data type. Thus, the conversion must be applied to the data column and a less than optimal plan may be selected.

   create table tblTest( a numeric(10,1) PRIMARY KEY, b int)
   select * from tblTest where a = 123.15 

Additional query words: datatype datatypes

Keywords : SSrvTran_SQL kbbug6.50.sp2 kbbug6.50.sp3 kbbug6.50.sp4
Version : winnt:6.5
Platform : winnt
Issue type : kbinfo