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.
Question from MSDN forums