Which datatype is good for my database field?

Every time at the designing of new database table we stuck for the choosing right datatype for the particular field.

It has been said that if there is right database design have been done, it’s easy to accomplish changes in the application. If you require to change database field type or require to add new field in the table, it costs a lot at later stage.

So with this confusion of choosing right database, I have some check list what to use for the string type field.

Consider following points for your string data field.

àWhat will be max size of field, how many characters could be there?

àIs there any fix length I will have?

àDoes field require using Unicode character?

There are basic for three types of datatype are supported.

Char : this datatype can be used when we know our field’s data. We have prior idea of value. It will be good to use this type.

Varchar: when we are not firm about the value come in the field, this can be the choice.

Now one interesting datatype

Nvarchar: this is same as varchar expect it stores value as Unicode character.

Unicode character are especially useful when we would like to support Multilanguage in application else there is not much reason to use it without any purpose.

Nvarachar datatype occupies double space than the varchar, it should be considered when there will be large amount of data will be feed in the system.

You can check it with following sample

After running query you will notice that varchar datatype takes less space compared to others.

declare @na char(30)

SET @na = 'shailesh'

declare @nam nvarchar(30)

SET @nam = 'shailesh'

declare @name varchar(30)

SET @name = 'shailesh'

select datalength(@na)

select datalength(@nam)

select datalength(@name)