Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Varchar or Char? Expand / Collapse
Author
Message
Posted Thursday, October 15, 2009 10:40 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, January 21, 2014 8:55 AM
Points: 1,520, Visits: 3,035
Comments posted to this topic are about the item Varchar or Char?
Post #803943
Posted Friday, October 16, 2009 12:30 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, April 10, 2014 7:08 AM
Points: 3,448, Visits: 4,406
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.


  Post Attachments 
Result.GIF (56 views, 1.92 KB)
Post #803964
Posted Friday, October 16, 2009 12:31 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 9:58 AM
Points: 141, Visits: 837
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
Post #803966
Posted Friday, October 16, 2009 12:36 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, March 19, 2014 1:27 AM
Points: 2,366, Visits: 1,837
Nice question, but i agree with diamondgm. The average calculation need not be the same as you pointed out.

"Keep Trying"
Post #803970
Posted Friday, October 16, 2009 1:20 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, October 04, 2012 4:26 AM
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!
Post #803983
Posted Friday, October 16, 2009 1:28 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, April 03, 2014 3:33 PM
Points: 2,526, Visits: 530
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
MCITP:Database Developer 2008
MCTS: SQL Server 2008, Implementation and Maintenance
Post #803988
Posted Friday, October 16, 2009 1:41 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:54 PM
Points: 5,787, Visits: 8,001
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
Post #803999
Posted Friday, October 16, 2009 1:46 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, March 30, 2014 11:40 AM
Points: 459, Visits: 256
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
Post #804003
Posted Friday, October 16, 2009 2:05 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, April 01, 2014 2:57 PM
Points: 443, Visits: 822
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.
Post #804015
Posted Friday, October 16, 2009 2:09 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:54 PM
Points: 5,787, Visits: 8,001
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
Post #804017
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse