Shulou(Shulou.com)05/31 Report--

Editor to share with you SQL Server 2008 to deal with implicit data type conversion example analysis, I believe that most people do not know much about it, so share this article for your reference, I hope you will learn a lot after reading this article, let's go to know it!

Through the following test verification, firstly, a test table with uneven data distribution is established.

USE tempdbGOCREATE TABLE _ t (c varchar (50)); CREATE INDEX IX_c ON _ t (c); GO-- adds 10000 pieces of data INSERT _ tSELECT (9999 + id) FROM (SELECT TOP 10000 id = ROW_NUMBER () OVER (ORDER BY GETDATE ()) FROM sys.all_columns a, sys.all_columns) ID-- turns 100-10000 data into the same value UPDATE _ t SET c =''WHERE c > =' 10100'

Then the estimated number of rows in the execution plan that meets the condition 1 and 8900 are tested by varhcar and nvarchar values, respectively.

ALTER INDEX IX_c ON _ t REBUILD;GOSET SHOWPLAN_ALL ONGOSELECT * FROM _ t WHERE c = '10005GOSET SHOWPLAN_ALL OFF;GOALTER INDEX IX_c ON;-- actual 1 GOSET SHOWPLAN_ALL OFF;GOALTER INDEX IX_c ON _ t REBUILD;GOSET SHOWPLAN_ALL ONGOSELECT * FROM _ t WHERE c = Nlast 10005mm;-- actual 1 GOSET SHOWPLAN_ALL OFF;GOALTER INDEX IX_c ON _ t REBUILD;GOSET SHOWPLAN_ALL ONGOSELECT * FROM _ t WHERE c ='';-- actual 9900 GOSET SHOWPLAN_ALL OFF GOALTER INDEX IX_c ON _ t REBUILD;GOSET SHOWPLAN_ALL ONGOSELECT * FROM _ t WHERE c = GOSET SHOWPLAN_ALL OFF;GO;-- actual 9900 GOSET SHOWPLAN_ALL OFF;GO

The estimated number of rows in the resulting query plan is shown in the following figure

As you can see from the number of rows of estimated data shown in the figure, for the varchar value (no hidden data type conversion is required), the estimated result is accurate. However, for the nvarchar value, whether the specified value has only one data or 8900 data matches, the estimated result is 99.0099, which indicates that the estimate does not take into account the value we specified.

Further test 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 result is shown in the following figure:

Whether it is a variable of varchar or nvarchar, the estimated number of rows is 99.0099, which is the same as the result of using the constant value of nvarchar. It seems that the SQL Server query optimizer should indeed regard the result of GetRangeThroughConvert as a variable, which should not be considered thoroughly in design. After all, when specifying a fixed constant value, the result of GetRangeThroughConvert should also be a definite value.

The above is all the content of the article "SQL Server 2008 sample Analysis of handling implicit data Type conversion". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.