Thursday, February 11, 2010

Importance of specifying length in datatypes

This post is just to emphasize on importance in specifying length while declaring fields,variables or parameters. I have seen quite a few occasions where developers have ignored length part.
The problem is depicted below

select CAST('12.34354' as numeric)

---------------------------------------
12

select CAST('12.34354' as decimal)

---------------------------------------
12

SELECT CAST('This is a long string which is over the default length of varchar datatype' AS varchar)

------------------------------
This is a long string which is

SELECT CAST('This is a long string which is over the default length of char datatype' AS char)

------------------------------
This is a long string which is

SELECT CAST('This is a long string which is over the default length of nvarchar datatype' AS nvarchar)

------------------------------
This is a long string which is


As seen above for numeric types absence of precision scale values causes them to assume default values of 30 and 0 for precision scale respectively

For string data, the field gets reduced to default length of 30

So make it a point to specify appropriate length for your fields in future