Also talk about SQL Server 2008 processing implicit data type conversions in execution plan enhancements (cont.)

In the previous article also talk about SQL Server 2008 handling implicit data type conversions in the execution plan enhancements, I mentioned that implicit data type conversions increase the data distribution to a very uneven table, the estimated number of rows of data and the actual value of a large discrepancy, after further testing, I found that this assessment inaccuracy should be exactly the same as guessing, and it uses the way variables are evaluated. A test table with uneven data distribution is first established by testing the following tests.

Use Tempdbgo CREATE TABLE _t (c varchar()); CREATE INDEX ix_c  on _t (c);GO--Add 10,000 data INSERT _tSelect (9999 + ID) from ( SELECT TOP 10000 id = row_n Umber () Over ( ORDER  by GETDATE ())  from Sys.all_columns A, sys.all_columns) id--will 100
      - 10000 data becomes the same value UPDATE _t SET c = ' WHERE c >= ' 10100 ' 

The estimated number of rows of the execution plan satisfying the condition 1 and satisfying the condition 8,900 are then tested by varhcar and nvarchar values respectively.

 ALTER INDEX ix_c  on _t REBUILD;GO SET showplan_all onGOSELECT *  from _t WHERE c = ' 10005 ' /c10>;     --Actual 1 articlesGO SET showplan_all OFF;GO ALTER INDEX ix_c  on _t REBUILD;GO SET showplan_all onGOSELECT *  from _t WHERE c = N' 10005 ' /c7>;     --Actual 1 articlesGO SET showplan_all OFF;GO ALTER INDEX ix_c  on _t REBUILD;GO SET showplan_all onGOSELECT *  from _t WHERE c = '; /c0>          --Actual 9,900 articlesGO SET showplan_all OFF;GO ALTER INDEX ix_c  on _t REBUILD;GO SET showplan_all on GOSELECT *  from _t WHERE c = N';          --Actual 9,900 articlesGO SET showplan_all OFF;GO

The estimated number of rows for the resulting query plan is as shown

The estimated number of rows displayed shows that the estimated results are accurate for varchar values (no hidden data type conversions are required). However, for the nvarchar value, regardless of whether the specified value is only one data, or 8,900 data matches, the estimated result is 99.0099, which indicates that the estimate does not take into account the value we specify.
Further testing with variables

 ALTER INDEX ix_c  on _t REBUILD;GO SET showplan_all onGODECLARE @v varchar; SELECT *  from _t WHERE c = @v; --varcharGO SET showplan_all OFF;GO ALTER INDEX ix_c  on _t REBUILD;GO SET showplan_all onGODECLARE @nv nvarchar; SELECT *  from _t WHERE c = @nv; --nvarcharGO SET showplan_all OFF;GO

The results are as follows:

Whether it is a varchar or a nvarchar variable, the estimated number of rows is 99.0099, which is the same as the result of using the nvarchar constant value, it seems that the SQL Server query optimizer should indeed put the Getrangethroughconvert As the result of the variable, this should be considered in the design of a less comprehensive place, after all, when specifying a fixed constant value, the results of Getrangethroughconvert should also be determined value. (This problem appears to have been adjusted in SQL Server 2014, which was not found in the 2014 test).

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

Also talk about SQL Server 2008 processing implicit data type conversions in execution plan enhancements (cont.)