Character column length vs 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 won’t. 
 
Consider the following example

Declare @t table(id int, test_col varchar(2))
Insert into @t select 1,'test'
Select id,test_col from @t

 
Now, You get the following 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 don’t 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
          ) as data

The result is

data
-------------------------------
This has more than thirty char

 
When you cast a literal to character type without specifying the length,
by default it is 30.

Depends on the usage, if the column does not have enough length
1 Error is generated
2 Data is truncated
3 Default length is assumed
 
So you need to specify enough column length when you use character datatypes

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s