SQL Server 2008 handles implicit data type conversions in execution plan enhancements

A test table with uneven data distribution is first established by testing the following tests.

Use Tempdbgocreate TABLE _t (    C varchar (50)); CREATE INDEX Ix_c on _t (c); go--Add 10,000 data Insert _tselect (9999 + ID) from (    SELECT TOP 10000 id = row_number () over (ORDER by GETDATE ())    F ROM sys.all_columns A, sys.all_columns) id--change 100-10000 data to 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; Goset showplan_all Ongoselect * from _t WHERE c = ' 10005 ';     --Actual 1 strips goset showplan_all OFF; Goalter INDEX ix_c on _t REBUILD; Goset showplan_all Ongoselect * from _t WHERE c = N ' 10005 ';     --Actual 1 strips goset showplan_all OFF; Goalter INDEX ix_c on _t REBUILD; Goset showplan_all Ongoselect * from _t WHERE c = ';          --Actual 9,900 strips Goset showplan_all OFF; Goalter INDEX ix_c on _t REBUILD; Goset showplan_all Ongoselect * from _t WHERE c = N ';         --Actual 9,900 strips Goset 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; Goset showplan_all ongodeclare @v varchar; SELECT * from _t WHERE c = @v; --Varchargoset Showplan_all OFF; Goalter INDEX ix_c on _t REBUILD; Goset showplan_all ongodeclare @nv nvarchar; SELECT * from _t WHERE c = @nv; --Nvarchargoset 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 article explains the SQL Server related content, more related content please concern PHP Chinese network.