Hi Friends,

This blog is focused on Fixed and Variable length data type char() versus varchar(). During the creation of any new table, we take the decision for data types. Here we will see one interesting scenario which you have to keep in mind during the selection of data types. Let say we have a column which will store a fixed length character data type, then it is very simple, we will go for the char data type.Now think about a different scenario, i have a column that will store character length 11 or 12 so here I’ll go for the varchar data type. We have selected varchar data type to save the space because most of the values are of character length 11. But this is not 100% correct here. Let me show you.

I am going to create two tables where one will store fixed 12 character length data in char(12) and other will store 11 and 12 character length data in varchar(12).

Now let’s check the space used by both the tables:

Output

Now you can see, varchar storage has taken more space. This is due to the reason of variable length column storage. For varchar storage, SQL Server uses some extra bytes per record, i.e. two bytes per variable length column for providing the offset to the end of that column value.

HAPPY LEARNING!

Regards:
Prince Kumar Rastogi

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook

Follow Prince Rastogi on Twitter | Follow Prince Rastogi on FaceBook