Always specify length for character data types

Whenever you use character datatypes such as CHAR, VARCHAR, etc, always make sure to specify the length. Otherwise depends on the usage, you get unexpected result or an error

Consider the following example

Declare @name varchar
set @name='SQL Server'
select @name as name

The result is S and not SQL Server because of missing length in the declaration part by default only first character is assigned to the variable

See what happens when you execute the following

select cast('Welcome to SQL Server. Can you read this fully?' as varchar) as name

Do you think the result is W because of missing length?. No. The result is

name
------------------------------
Welcome to SQL Server. Can you

If you count the length it is 30

Consider another example

declare @test table(name varchar)
insert into @test(name)
select 'SQL Server'

This time you get the following error

Msg 8152, Level 16, State 14, Line 2
String or binary data would be truncated.
The statement has been terminated.

So you need to be aware of these and always specify the length as you won’t get error for certain cases

Advertisements

2 thoughts on “Always specify length for character data types

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