I have seen some new SQL Server developers declaring Varchar and Nvarchar data types without specifying a length, since it is optional. Although this works in some other programming languages like C#, SQL Server behaves differently and this is a bad practice overall. Let us see with an example of what’s wrong with not declaring a length for the Char or Varchar data type.
Surprised seeing the output? When a length is not specified while declaring a variable, the default length is 1 and when a length is not specified when using the CAST and CONVERT functions, the default length is 30. That’s the reason you get the output you see above.
Note: Try doing the same when you create a table with a CHAR column, without specifying the length.
To get the desired results, rewrite the t-sql code in the following manner:
DECLARE @v2 AS varchar(60)
SET @v1 = 'abcdefghijkl'
SET @v2 = 'The quick brown fox jumped over the lazy dog'
DATALENGTH(CAST(@v2 as varchar(60)))
Now you get the desired results
Another place where you got to be careful is while creating stored procedures with parameters. If you have created a stored procedure that accepts a parameter with a VARCHAR datatype with no length, you will be in for a surprise to learn that SQL Server silently truncates the string and adds some leading characters.
Overall, always specify a length for the CHAR, VARCHAR, NVARCHAR and similar data types. It’s a good practice!