SQL: Usage of char, varchar and varchar(MAX)
These are character data types in SQL. We will take an example and describe each type. Later we determine the cases where one of them can be applied.
Create Table testable( Name char(20), Email varchar(250), Address varchar(Max))
We usually call it char or character. In the above example, Name is declared with char(20) as type. Here, 20 is the storage size and it does not depend on the actual length of name entered, whether is below 20 or above 20.
It is always fixed length and can have value from from 1 through 8,000. The default length is 1 and can be mentioned as in below example.
DECLARE @testvariable AS char
We usually call it varchar or varying character. In the above example, Email is declared with varchar(250) as type. Here, 250 is the maximum storage size. The storage size is flexible and dependent on the actual length of data entered so finally storage size is computed as actual length + 2 bytes.
It is always variable length and can have value from 1 through 8,000. Plus point is that the data entered can be 0 characters in length.
The only difference is the max storage limit which in this case is 2^31-1 bytes.
- When we are considerably sure about the length of the data in column, we should use char. The main concern is consistency about the column entries.
- Varchar is exact reverse condition of char. If there is a lack of consistency in column entries or length of the column data vary considerably, use varchar.
- Varchar(max) is a superlative form and used when the size might exceed 8,000 bytes.
In the real world, we are more concerned about the storage space and above points, though miniscule, can really help us