Performance Tuning Part 5- Variable Declaration

Define variable with the proper data type. Improper data type may cause conversions which is not good for performance.

DECLARE @Name VARCHAR(50)

SET @Name = 'Blade'

SELECT ProductID
	,Color
	,Class
FROM AdventureWorks2016.Production.Product
WHERE name = @Name

Execution plan

As you can see, SQL server is doing an implicit conversion to compare @name variable with the name column because  the data type of name column is nvarchar while @name variable is declared with data type varchar so implicit conversion is happening to convert varchar value to nvarchar.

Author: Sauras Pandey

Sauras Pandey is an MCP, SQL Server DBA/Architect with 9+ years of experience working in a variety of environments

Leave a Reply

Your email address will not be published.