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, and after further testing, I found that the inaccuracy of the assessment should be exactly the same as the one that was being tested, and it used the method of evaluating the variables. Based on the following test certificate, for example, first of all, the data distribution is not evenly measured 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 of data that are displayed can be seen. For varchar values (data type conversions that do not need to be hidden), the estimated results are accurate. However, for the nvarchar value, there are 8,900 data matches, regardless of whether the specified value has only one data. 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's 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, when specifying a fixed constant value, the result of Getrangethroughconvert should also be determined 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.)