SQL Myth : Char vs Varchar

Posted: 23/11/2014 in T-SQL

I am not here to tell you the difference between Char and Varchar(that you will get from various websites).  I wish to prove a common answer I got while taking interviews.

That answer was “Char is faster than Varchar” – is it ??

Let us check .

/*Script to Create table and insert data*/
CREATE TABLE CHAR_VS_VARCHAR_C
(id int identity(1,1),
Product_name CHAR(100))

CREATE TABLE CHAR_VS_VARCHAR_V
(id int identity(1,1),
Product_name varchar(100))

INSERT INTO CHAR_VS_VARCHAR_C
VALUES (‘PRODUCT_01’)
GO 500
INSERT INTO CHAR_VS_VARCHAR_C
VALUES (‘PRODUCT_02’)
GO 500
INSERT INTO CHAR_VS_VARCHAR_C
VALUES (‘PRODUCT_03’)
GO 500
INSERT INTO CHAR_VS_VARCHAR_C
VALUES (‘PRODUCT_04’)
GO 500

INSERT INTO CHAR_VS_VARCHAR_V
VALUES (‘PRODUCT_01’)
GO 500
INSERT INTO CHAR_VS_VARCHAR_V
VALUES (‘PRODUCT_02’)
GO 500
INSERT INTO CHAR_VS_VARCHAR_V
VALUES (‘PRODUCT_03’)
GO 500
INSERT INTO CHAR_VS_VARCHAR_V
VALUES (‘PRODUCT_04’)
GO 500

/*Code part End*/

 

After  insertion of these tables, see the performance between two select queries.

 

2_char_vs_varchar

So Varchar is faster ??? isn’t it ?

Let us make a small change in the table definition, I have changed Product_name column length from 100 to 10.

and I have run the same Same query again …

Here is my result.

2_char_vs_varchar+c

 

So char is faster ??

We have learned that Column Length is playing an important role in performance.

Now there is question in my mind , How ??

If you have answer with you , please leave a comment.

Advertisements
Comments

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