Char vs Varchar – Performance Justification

Posted: 01/12/2014 in T-SQL
Tags:

Let us consider the same example we have discussed in the previous post.

Case1:

Char(100), Varchar(100) – If we store a value “PRODUCT_04” the net effect on each data type would be like this.

to store this 10 character word “CHAR(100)” would take 100 bytes

to store this 10 character word “VARCHAR(100)” would take 10 + 2 bytes

10 bytes to store the word and 2 bytes to store the length of the word (10 in this case).

Once we select this rows from the database the systems should read the entire 100 bytes in case of CHAR and only 12 bytes in case of VARCHAR.

so that we have seen the huge difference in Estimated Query plan – VARCHAR is faster.

Case 2 :

Char(10), Varchar(10) – If we store a value “PRODUCT_04” the net effect on each data type would be like this.

to store this 10 character word “CHAR(10)” would take 10 bytes

to store this 10 character word “VARCHAR(10)” would take 10 + 2 bytes

in this case CHAR is only considering the exact length, but in case of VARCHAR there is a overhead of 2 bytes – this causes the slight performance improvement in CHAR than VARCHAR – CHAR is faster

Conclusion :

Both are giving a slight variation in performance. Selection of proper data type make sens.

If the length of the value is fixed – Consider CHAR with that fixed length(buffer length for future is not a good practice)

If the length of the value is varying frequently – VARCHAR is the best option with maximum expected length.

Note :

Char is faster than Varchar – Answer is Yes (only if the values fit in the length of the Data type)

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s