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»»»

one character of data Expand / Collapse
Author
Message
Posted Friday, June 5, 2009 4:08 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, November 26, 2013 5:47 AM
Points: 1,065, Visits: 245
To be able to store every possible character in this variable it definitely has to be a nchar(1).
E.g. using 2-byte Unicode characters.

Leo
Post #729525
Posted Friday, June 5, 2009 4:17 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, August 23, 2010 8:30 AM
Points: 229, Visits: 146
I agree with many others that nchar(1) should be the correct answer because I am under the impression that varchars store an additional 2 bytes of data in the background to indicate the length of the data - so therefore Varchar(1) uses 3 bytes if it contains a byte of data and 2 bytes if the field was null or empty. I remember having discussions about the sensible minimum field length for varchar fields and when chars should be used instead to store least data. I could be wrong though...
Post #729530
Posted Friday, June 5, 2009 4:35 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, May 31, 2013 4:24 AM
Points: 1,417, Visits: 197
I've chosen for nchar(1) because it only says that the value won't be NULL or empty, but there's no limitation on the characters used.


Thanks,
Ashesh


Post #729542
Posted Friday, June 5, 2009 4:37 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, January 11, 2013 8:20 AM
Points: 1,608, Visits: 373
Hi,

The Online Manuals state it quite clearly:

http://msdn.microsoft.com/en-us/library/ms176089.aspx:
char [ ( n ) ]
Fixed-length, non-Unicode character data with a length of n bytes. n must be a value from 1 through 8,000. The storage size is n bytes. The ISO synonym for char is character.

varchar [ ( n | max ) ]
Variable-length, non-Unicode character data. n can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes. The storage size is the actual length of data entered + 2 bytes. The data entered can be 0 characters in length. The ISO synonyms for varchar are char varying or character varying.


http://msdn.microsoft.com/en-us/library/ms186939.aspx:
nchar [ ( n ) ]
Fixed-length Unicode character data of n characters. n must be a value from 1 through 4,000. The storage size is two times n bytes. The ISO synonyms for nchar are national char and national character.

nvarchar [ ( n | max ) ]
Variable-length Unicode character data. n can be a value from 1 through 4,000. max indicates that the maximum storage size is 2^31-1 bytes. The storage size, in bytes, is two times the number of characters entered + 2 bytes. The data entered can be 0 characters in length. The ISO synonyms for nvarchar are national char varying and national character varying.


So, depending on whether your horizon goes beyond the US or not, you may argue either for CHAR(1) or NCHAR(1), respectively.



Best regards,
Dietmar Weickert.
Post #729544
Posted Friday, June 5, 2009 4:55 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:46 AM
Points: 5,989, Visits: 12,916
As what the characters might be was not specified, add a vote for nchar(1)

but so long as question generates debate...................


---------------------------------------------------------------------

Post #729559
Posted Friday, June 5, 2009 4:58 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 17, 2009 7:03 AM
Points: 1, Visits: 17
Always nchar for those of us in EMEA
Post #729560
Posted Friday, June 5, 2009 5:14 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 5:42 AM
Points: 986, Visits: 386
Being from EU myself I wanted to pick nchar(1) , but chose char(1) instead as this is an American site( hoping for the point).

I fail to see why you use a varchar when you know that all the space is required and will never be NULL or empty space as stated in the question
Post #729574
Posted Friday, June 5, 2009 5:44 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, February 11, 2014 4:12 PM
Points: 2,007, Visits: 768
Respectfully disagree with the answer; I'd actually say varchar(1) is the worst choice. You've got the +2 byte overhead for allowing variable length when the question states the length will always be one, plus if you are going to waste space then use it on the possibility of needing to store Unicode characters. It seems there are two choices for "best" and that would be char(1) or nchar(1)... char(1) because it does the job with the least space unless you need Unicode, in which case nchar(1) is best.
Post #729601
Posted Friday, June 5, 2009 5:46 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, February 24, 2010 4:10 AM
Points: 1,553, Visits: 2,232
bad answer...
VARCHAR(1) is most definitly not the BEST answer, it should be char(1) or nCHAR(1)




----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley


Posting Best Practices
Numbers / Tally Tables

SQL-4-Life
Post #729602
Posted Friday, June 5, 2009 6:03 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 2:03 PM
Points: 4,504, Visits: 1,814
Noel McKinney (6/5/2009)
Respectfully disagree with the answer; I'd actually say varchar(1) is the worst choice. You've got the +2 byte overhead for allowing variable length when the question states the length will always be one, plus if you are going to waste space then use it on the possibility of needing to store Unicode characters. It seems there are two choices for "best" and that would be char(1) or nchar(1)... char(1) because it does the job with the least space unless you need Unicode, in which case nchar(1) is best.


Very well said. There really isn't one "right" answer to this question, but char(1) and nchar(1) are the only "reasonable" answers. This question is a good example of how dangerous bad information can be. The author is under the misconception that the overhead for the varchar length is 1 byte rather than 2. This can make a big difference when determining space requirements for a table with several varchar columns and many rows.



Post #729609
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse