Varchar<>Varchar(n)

This is a general mistake that many developers do and sometimes goes unnoticed while reviews and testing, but then suddenly one day start appearing as bugs.

Many developers understand and (misinterpret) varchar as variable-length data type and hence doesn’t specify the length in the variable definitions which is incorrect. Here are some examples of ‘weird’ behavior of incorrectly defined varchar. Here is some demo

DECLARE @var VARCHAR
SET @var='This string will get truncated'
SELECT @var

GO
SELECT CAST ('This is very very long string which will get truncated' AS VARCHAR)
GO

Here is the result

The data types CHAR, NVARCHAR, NCHAR, DECIMAL, NUMERIC also work in the same faction.  So next time when you define a variable, don’t forget to specify the lengths properly.

Reference
Question from MSDN forums