Performance impact of SQL Server data type precedence

Original: SQL Server data type priority impact on performance

Translated from:

Http://www.mssqltips.com/sqlservertip/2749/sql-server-data-type-precedence/?utm_source=dailynewsletter&utm _medium=email&utm_content=headline&utm_campaign=2012814

Problem:

I use a simple query/stored procedure in my application to access a large table. But it took a long time to execute. In the WHERE clause, I used a field that was indexed and highly selective (selective) and was not wrapped with a function. But it looks like you're not using an index, so what's the problem?

Solution:

The problem with this microsecond may be that the data type as a parameter is inconsistent with the data type in the query. In this case, SQL Server will either put the column in the where, or implicitly convert the parameter's data type to a higher-level or lower-level data type. When the column being queried is converted (the victim in the conversion race), a scan (scan) is generated to satisfy the query request. Let's take a look at the following two examples, the first example using the sample database AdventureWorks, we will query this customer through a client's accountnumber in the Sales.Customer table. AccountNumber The data type of this column is varchar (10) and has a unique index on it. Run the following query and review the execution plan to see the results as we wish:

Create Proceduredbo. Precedencetest

(

@AccountNumber varchar (10)

)

As

Begin

SET NOCOUNT ON

SELECT *

From Sales.Customer

where AccountNumber = @AccountNumber

End

Go

EXEC dbo. Precedencetest ' AW00030113 '

Go

The implementation plan is as follows:

Let's make some minor changes to the parameter, change it to nvarchar (10), and then re-execute the statement:

ALTER PROCEDURE dbo. Precedencetest

(

@AccountNumber nvarchar (10)

)

As

Begin

SET NOCOUNT ON

From Sales.Customer

where AccountNumber = @AccountNumber

End

Go

EXEC dbo. Precedencetest ' AW00030113 '

Go

The execution plan shows that the optimizer has selected the index on the scan TerritoryID.

Check the filter operation and you can see that the type is implicitly converted on the AccountNumber column to match the parameters passed in. Because the data type varchar is lower than the parameter type nvarchar level, the index in which it resides is invalidated.

Now let's verify that the lower-level data type is the case under the lookup parameter. In this example, the LastName column of the Person.person table is the nvarchar type, and there is an available index on it, and the parameter passed in by the stored procedure is a varchar type:

ALTER PROCEDURE dbo. Precedencetest (

@LastName varchar (50)

)

As

Begin

SET NOCOUNT ON

From Person.person

where LastName = @LastName

End

Go

EXEC dbo. Precedencetest ' Tamburello '

Go

The execution plan shows that the optimizer chooses to use an index lookup:

By opening the details of the index seek, you can see that the data type of the column LastName is implicitly converted to a more advanced nvarchar type because of the reason for passing in the parameter.

The optimizer is free to choose the optimal execution plan when the index column is no longer affected by the conversion.

Whether you are defining query parameters in the application or in a stored procedure, ensuring that the data types in the query parameters match the data types of the query columns can avoid problems caused by index scans and other conversions.

Add: The priority of the data type, from high to the end:

2. user-defined data types (highest)

4. sql_variant

6. Xml

8. DateTimeOffset

10. DateTime2

12. Datetime

14. smalldatetime

16. Date

18. Time

20. Float

22. Real

24. Decimal

26. Money

28. SmallMoney

30. bigint

32. Int

34. smallint

36. tinyint

38. Bit

40. ntext

42. Text

44. Image

46. Timestamp

48. uniqueidentifier

50. nvarchar (including nvarchar (max) )

52. NChar

54. varchar (including varchar (max) )

56. Char

58. varbinary (including varbinary (max) )

60. binary (lowest)

Performance impact of SQL Server data type precedence