Data Type Conversion

What would be the output of the following script? Also give explanation for your answer.

DECLARE @FirstName varchar(10)
DECLARE @LastName char(10)
DECLARE @LenFirstName int
DECLARE @LenLastName int

Set @FirstName = 'Shweta'
Set @LastName = 'Agarwal'
Set @LenFirstName = Len(@FirstName)
Set @LenLastName = Len(@LastName)

PRINT @LenFirstName + '-' + @LenLastName

The result of query will be 13.
To understand the reason for this unexpected answer we need to first understand how SQL expression evaluator treats with data types. When the arguments and operands have incompatible data types, simply implicit type conversion takes place or if conversion not possible it will generate error.
If two expressions are combined and they are not the same data type, then the result will be implicitly converted according to these rules of precedence.
Here is a chart about implicit data type conversions in SQL Server from the MSDN library:

So, now time to move towards our example. You can see in above chart there is a implicit conversion possible in char to int. the ‘-‘is converting to 0 you can verify this by

print cast('-' as int)
print convert(int,'-')

The result will be zero 0.

Before execution our expression becomes

PRINT @LenFirstName + 0 + @LenLastName
6+0+7=13

SQL Server won’t concatenate values of different data types. Furthermore, concatenation is strictly a string function. That means you must convert all values to strings before SQL Server will concatenate them. Otherwise you will get results like above

Add on by Shweta, Navdeep, Shivani, Tulika
SQL Server uses the following precedence order for data types:
1. user-defined data types (highest)
2. sql_variant
3. xml
4. datetimeoffset
5. datetime2
6. datetime
7. smalldatetime
8. date
9. time
10. float
11. real
12. decimal
13. money
14. smallmoney
15. bigint
16. int
17. smallint
18. tinyint
19. bit
20. ntext
21. text
22. image
23. timestamp
24. uniqueidentifier
25. nvarchar (including nvarchar(max) )
26. nchar
27. varchar (including varchar(max) )
28. char
29. varbinary (including varbinary(max) )
30. binary (lowest)