SQL Server 2008 Enhancements to Handling Implicit Data Type Conversions in Execution Plans

Verified by the following test, first establish a test table with uneven data distribution.

USE tempdb
GOCREATE TABLE _t(
    c varchar(50)
);CREATE INDEX IX_c ON _t( c );GO-- 加入 10000 条数据INSERT _tSELECT (9999 + id) FROM(    SELECT TOP 10000 id = ROW_NUMBER() OVER( ORDER BY GETDATE() )    FROM sys.all_columns a, sys.all_columns
)ID
-- 将 100 - 10000 的数据变成相同值UPDATE _t SET c = '' WHERE c >= '10100'

Then use the varhcar and nvarchar values ​​to test the estimated number of rows in the execution plan that satisfy 1 condition and 8900 conditions respectively.

ALTER INDEX IX_c ON _t REBUILD;GOSET SHOWPLAN_ALL ONGOSELECT * FROM _t WHERE c = '10005';     -- 实际1条GOSET SHOWPLAN_ALL OFF;GOALTER INDEX IX_c ON _t REBUILD;GOSET SHOWPLAN_ALL ONGOSELECT * FROM _t WHERE c = N'10005';     -- 实际1条GOSET SHOWPLAN_ALL OFF;GOALTER INDEX IX_c ON _t REBUILD;GOSET SHOWPLAN_ALL ONGOSELECT * FROM _t WHERE c = '';          -- 实际9900条GOSET SHOWPLAN_ALL OFF;GOALTER INDEX IX_c ON _t REBUILD;GOSET SHOWPLAN_ALL ONGOSELECT * FROM _t WHERE c = N'';         -- 实际9900条GOSET SHOWPLAN_ALL OFF;GO

The estimated number of rows in the obtained query plan is shown in the figure below

It can be seen from the estimated number of data rows displayed in the figure , for varchar values ​​(no hidden data type conversion required), the estimated results are accurate. But for the nvarchar value, no matter whether the specified value has only one piece of data or 8900 data matching, the estimated result is 99.0099, which shows that the estimate does not take into account the value we specified.
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 shown below:

Whether it is a varchar or nvarchar variable, the estimated number of rows is It is 99.0099. This value is the same as the result of using nvarchar constant value. It seems that the SQL Server query optimizer should indeed treat the result of GetRangeThroughConvert as a variable. This should be a poorly considered design consideration. After all, a fixed constant value is specified. When , the result of GetRangeThroughConvert should also be a certain value.

This article explains the relevant content of SQL Server. For more related content, please pay attention to the php Chinese website.

Related recommendations:

How to implement infinite-level parent-child relationship query in a single sentence in MySQL

SQL Server FileStream with progress How to access

What to do if you forget your SQL Server administrator password

The above is the detailed content of SQL Server 2008 Enhancements to Handling Implicit Data Type Conversions in Execution Plans. For more information, please follow other related articles on the PHP Chinese website!