Also talk about SQL Server 2008 enhancements to processing implicit data type conversions in run plans (cont.)

In the previous article also talk about SQL Server 2008 handling implicit data type conversions in the run plan enhancements, I mentioned implicit data type conversions to add tables that are very unevenly distributed for data distribution. The number of data rows evaluated has a very large discrepancy with the actual value, after further testing. I have found that the inaccuracy of this assessment should be exactly the same as that of the test, which uses the method of evaluating the variables.

Pass for example the following test certificate. First, the data distribution is not averaged frequently.

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 ' 

Then through the varhcar and nvarchar values respectively tested to meet the conditions of 1 and meet the condition 8,900 of the running plan estimated number of rows.

 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 query plan, as seen in the

The estimated number of rows displayed can be seen for the varchar value (no hidden data type conversions are required). The result of the prediction is accurate. However, for the nvarchar value, the specified value is only one data. There are still 8,900 data matches. The estimated results are all 99.0099, which indicates that the estimate does not take into account the values 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 for example are as seen in:

Whether it is a varchar or a nvarchar variable, the estimated number of rows is 99.0099. This value is the same as the result of using the nvarchar constant value, it seems that the SQL Server query optimizer should indeed consider the result of Getrangethroughconvert as a variable. This should be the design of a less thoughtful place, after all, specify a fixed constant value. The result of the Getrangethroughconvert should also be a definite value. (This problem appears to be adjusted in SQL Server 2014, which is not found in the 2014 test).

Also talk about SQL Server 2008 enhancements to processing implicit data type conversions in run plans (cont.)