Why use data type and not just use Varchar? [duplicate]

Question

Why use data type and not just use Varchar? [duplicate]

Navigation

#1 by (1 votes)

0

I'm manipulating some databases where the vast majority of columns are with data type VARCHAR for fields like Date, Price, Total Value, Birth, Date / Time, and so on. Briefly, virtually everything is in varchar.

At least the tables that I had to create later I decided to put in the data type that column is intended because, for me, it is easier at the time of programming to manipulate this data.

But the people where I work "get on my feet" because I decide to use data types other than VARCHAR . For some time, it heated up to an argument. They said it was silly and waste of time.

However, I believe there are benefits to specifying the type of data, mainly because it is easier to manipulate during programming and to occupy less space in the database (is this correct for me?).

Are there more benefits in specifying the type of data each column is destined for in a bank? Better yet, is there some sort of study that deals with the benefits of using certain types of data while storing or executing a program in a project?

tipagem

asked by anonymous 01.07.2017 / 07:55

1 answer

Help with Trigger that adds / subtracts EF 6 - Migrations does not reflect changes in entities

score 1 · Answer 1

I think you're quite right in using the correct types for each field. At a glance I can cite three disadvantages in using varchar for everything:

Decreasing semantic understanding: Type helps in the quicker understanding of field utility.

Results and Restrictions: Depending on the type, the comparisons made in queries can generate unexpected results. In the example below the result would be for ages > '18 'would return Mary who is 9 years old (because of the alphabetical order used to varchar consider' 9 '> '18').

        create table Pessoa(
            pk_id int primary key identity(1,1),
            idade varchar(3),
            nome varchar(10),
        );

        insert into  Pessoa(idade, nome) 
        values
        ('17', 'José'),
        ('9', 'Maria'),
        ('11', 'Antonio'),
        ('22', 'Julia');

        select * 
        from Pessoa
        where idade>'18';

Another problem would be the use of grouping functions such as AVG and SUM that would not be possible to calculate values from varchar.

I think there are even more problems than those I mentioned, if you can, read Five Simple Database Design Errors You Should Avoid , some similar problems that occur during database modeling.