Madhivanan

Column length and Data length

When you use character datatypes(char,varchar,nchar,etc),
always make sure that column has enough length to have data. 
Otherwise sometimes you would get errors and sometimes you wont. 
 Consider the following example
Declare @t table(i int, test_col varchar(2))
Insert into @t select 1,'test'
Select i,test_col from @t
 
Well. You get the error 
Msg 8152, Level 16, State 14, Line 4
String or binary data would be truncated.
The statement has been terminated.

Because the column length of test_col is not enough to have the value 'test'
But only when you insert data to a table, you get this error. 
In other cases you dont
Declare @v varchar(2)
set @v='test'
select @v
Now there is no error but the value is truncated and 
only first two characters are assigned to the variable. 
 
The same case is applied when you use a stored procedure with input parameter create procedure test(@test_param varchar(2))
as
Select @test_param
Go
EXEC test1 'test'
Go
drop procedure test
Now see what happens when you dont specify the length
Declare @v varcharset @v='test'
select @v
By default the length is 1
Consider another example
Select cast('This has more than thirty characters' as varchar)
The result is This has more than thirty char
 
When you cast a literal to character type without specifying the length, 
by default it is 30. 
 
So you need to specify the enough column length when you use character datatypes