What to Choose ? Char Or Varchar datatype ?

[Mariner]

Hi,

We have recently designed a database in SQL Server 6.5. Later we may go for SQL Server 7.0. We have used Char datatype for Primary Keys for many Master Tables. Most of these are Char(2), Char(4) etc.

Now, for most of these primary keys, user will be entering values through front end i.e. We are not auto-generating the values for these Primary keys thro. VB code. So user may enter values which are less in length than the actual declared length in table design. As the datatype is Char, spaces will get padded to whatever user enters. I was thinking that, it will give me problems in comparisons and quering the database in future. But when I tried to query the database with such a case, Database returned me correct results. For example,
Suppose a Primary Key Column is declared as say SectionCd char(3) and User enters only one character say "A". In database it gets saved as "A ". But when I tried quering the database with the condition - Where SectionCd = "A", it gave me correct result and displayed the record properly. I didn't have to attach a space while giving the condition in the query.

Now, my question is, will it really create problems at some other places in future ? while coding the system ? For such small lenght character fields, looking from this "space padding" point of view, will there be any problems ? Is it still advisable to use Varchar data type only ? What is done normally in professional Database Design ? especially in such cases where the Primary key column length is small but you are taking the input from the user and you have no control on how much lenght user enters. As currently it's just a design phase, we can shift to varchar datatype now only. At a later stage it will be difficult. Is it advisable ?

Please help.

Regards,

Mariner

[Al]

Do you think that declaring a char(3) variable and an end-user entering "A" you actually have "A " in your database? As you know char and varchar is only the space assigned in memory, so char(3) is space already assigned and it is used even though you are writing "A", the whole 3 bytes in this case. Varchar would use only 1 byte and the rest would be freed, that means you will use 1 byte not 3 bytes. If you run a query where col1 ="A" should give you the correct result, because is not comparing col1="A ", it is not comparing space in memory.
If you have varchar(3) and char(3) and you assign "A" you have the same value in both variables with different datatype.
If you really want to verify that end-user enters the only characters you really want, you can use datatype rules to validate ranges, you can use rules that allow only charcaters from a-z or a size or combination of characters in certain positions. It does not matter if you use varchar or char, the end-user will be enter whatever they want without any difference regardless the datatype.
You can use clustered unique index to have a unique primary keys. If you use char(3) and want to increase the size of that column, you will be able to switch to varchar(N), but if you have something like rules or indexes bound to that column, you need to be careful since something can be bound to 3 characters. For primary keys I would recommend you to use integers.




------------
Mariner at 3/18/01 4:27:39 AM

Hi,

We have recently designed a database in SQL Server 6.5. Later we may go for SQL Server 7.0. We have used Char datatype for Primary Keys for many Master Tables. Most of these are Char(2), Char(4) etc.

Now, for most of these primary keys, user will be entering values through front end i.e. We are not auto-generating the values for these Primary keys thro. VB code. So user may enter values which are less in length than the actual declared length in table design. As the datatype is Char, spaces will get padded to whatever user enters. I was thinking that, it will give me problems in comparisons and quering the database in future. But when I tried to query the database with such a case, Database returned me correct results. For example,
Suppose a Primary Key Column is declared as say SectionCd char(3) and User enters only one character say "A". In database it gets saved as "A ". But when I tried quering the database with the condition - Where SectionCd = "A", it gave me correct result and displayed the record properly. I didn't have to attach a space while giving the condition in the query.

Now, my question is, will it really create problems at some other places in future ? while coding the system ? For such small lenght character fields, looking from this "space padding" point of view, will there be any problems ? Is it still advisable to use Varchar data type only ? What is done normally in professional Database Design ? especially in such cases where the Primary key column length is small but you are taking the input from the user and you have no control on how much lenght user enters. As currently it's just a design phase, we can shift to varchar datatype now only. At a later stage it will be difficult. Is it advisable ?

Please help.

Regards,

Mariner