Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Varchar or Char?


Varchar or Char?

Author
Message
john.arnott
john.arnott
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1558 Visits: 3059
Comments posted to this topic are about the item Varchar or Char?
vk-kirov
vk-kirov
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3546 Visits: 4408
As the column will hold only English language letters and digits, we don't need the additional bytes required by the national character data types

Not quite correct. You don't need to use Unicode data types for non-English letters. For example, if the identifiers contain English letters, Russian letters, and digits, you can use Cyrillic collation:
CREATE TABLE #tmp (id CHAR(10) COLLATE Cyrillic_General_CS_AS)
INSERT #tmp VALUES ('Привет1') -- Russian word for 'hello'
INSERT #tmp VALUES ('Hello2')
SELECT * FROM #tmp


The result of this query is in the attached picture.

If you need to operate with more than one national alphabet, using of nchar/nvarchar is the only option. Otherwise (only English alphabet, English alphabet + one national alphabet), any of char/varchar/nchar/nvarchar data types can be used.
Attachments
Result.GIF (61 views, 1.00 KB)
diamondgm
diamondgm
SSC-Enthusiastic
SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)

Group: General Forum Members
Points: 177 Visits: 939
Firstly, we assume that the average is the result of an even spread (that the average length is in fact 8.5 - we only need an average of 8 for storage to be a moot point in char vs varchar).
Secondly, within SQL Server, the efficiency may be best to use char in that instance, but take in to account the code operations of trimming and I think it becomes debatable.

I agree with you on a purist level, but practically, I would go with varchar still simply for the programmers' ease.

Nice question :-)
ChiragNS
ChiragNS
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2655 Visits: 1865
Nice question, but i agree with diamondgm. The average calculation need not be the same as you pointed out.

"Keep Trying"
PostXript
PostXript
Mr or Mrs. 500
Mr or Mrs. 500 (527 reputation)Mr or Mrs. 500 (527 reputation)Mr or Mrs. 500 (527 reputation)Mr or Mrs. 500 (527 reputation)Mr or Mrs. 500 (527 reputation)Mr or Mrs. 500 (527 reputation)Mr or Mrs. 500 (527 reputation)Mr or Mrs. 500 (527 reputation)

Group: General Forum Members
Points: 527 Visits: 86
The moment I checked the answer I knew there had to be a catch... While I don't agree on the assumption about distribution of the average I still like the question. More of these please!
hakan.winther
hakan.winther
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2750 Visits: 605
diamondgm (10/16/2009)
Firstly, we assume that the average is the result of an even spread (that the average length is in fact 8.5 - we only need an average of 8 for storage to be a moot point in char vs varchar).
Secondly, within SQL Server, the efficiency may be best to use char in that instance, but take in to account the code operations of trimming and I think it becomes debatable.

I agree with you on a purist level, but practically, I would go with varchar still simply for the programmers' ease.

Nice question :-)


I agree with the author of the question when it comes to storage size (and IO performance), but there are more things to think of than storage, like you the trailing spaces that has to be stripped.

I got the question correct because I thought it had a catch, but personally, I would only use char in rare cases unless all the data for the column are equal.

/Håkan Winther
MCITPBigGrinatabase Developer 2008
MCTS: SQL Server 2008, Implementation and Maintenance
MCSE: Data Platform
Hugo Kornelis
Hugo Kornelis
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 9012 Visits: 11745
This was a great question. Well done, John! I hope you have more of these in store for us.

diamondgm (10/16/2009)
Firstly, we assume that the average is the result of an even spread (that the average length is in fact 8.5 - we only need an average of 8 for storage to be a moot point in char vs varchar).


That's not an assumption, the even spread is explicitly mentioned in the question. Without it, I agree that the question would have been harder (if not impossible) to answer.

Secondly, within SQL Server, the efficiency may be best to use char in that instance, but take in to account the code operations of trimming and I think it becomes debatable.


I doubt it. Almost all business applications I have seen have their performance tied to an I/O bottleneck, and have the CPU twiddling it's virtual thumbs while waiting for more data to be read or written. Those few cycles the CPU spends to trim trailing spaces won't affect performance at all, it just means one less thumb to twiddle.

Oh, and if you really want to compare CPU times, then don't forget that SQL Server has to do extra work for each varying length column (like getting the length, then the data; or comparing length and adding trailing spaces before each comparison). So you'll probably find that the fixed length has lower CPU usage in spite of the extra trimming operations. Not that it will affect overall performance, though.

I agree with you on a purist level, but practically, I would go with varchar still simply for the programmers' ease.


How, exactly, is varchar easier for a programmer?

hakan.winther (10/16/2009)
personally, I would only use char in rare cases unless all the data for the column are equal.


That's reallly a shame, as the only good reasons to use varchar instead of char are a difference of more than 2 between average and maximum length; or a need to preserve trailing spaces.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Damian Widera-396333
Damian Widera-396333
SSC-Addicted
SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)

Group: General Forum Members
Points: 486 Visits: 321
There is one more interesting thing. Fixed length columns are placed in the same place in the row, whilst all columns like varchar, nvarchar etc are at the end of the row and their position may vary.
From the performance point of view it is better to have information stored in fixed length columns as they are on the same place in the row. Especially if you have 8,5 byte of total 10 used (~ lets agree with this assumption).
When you try to find data from nvarchar, nchar etc you have to read extra two bytes (mentioned in the explanation) to know where the data is (looking for the offset in teh row) in the row.

Kindest Regards,

Damian Widera
SQL Server MVP,
MCT, MCSE Data Platform, MCSD.NET
davoscollective
davoscollective
Right there with Babe
Right there with Babe (735 reputation)Right there with Babe (735 reputation)Right there with Babe (735 reputation)Right there with Babe (735 reputation)Right there with Babe (735 reputation)Right there with Babe (735 reputation)Right there with Babe (735 reputation)Right there with Babe (735 reputation)

Group: General Forum Members
Points: 735 Visits: 1004
Good question, made me think about datatypes.

nVarchar can actually use more than 2 bytes for some characters e.g. chinese characters that use 3 bytes.
Hugo Kornelis
Hugo Kornelis
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 9012 Visits: 11745
david.howell (10/16/2009)
nVarchar can actually use more than 2 bytes for some characters e.g. chinese characters that use 3 bytes.


Do you have a source for that? Both Books Online and every website I've ever read say that nvarchar and nchar use 2 bytes per character, never less, never more.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search