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


one character of data


one character of data

Author
Message
Leo Hesen
Leo Hesen
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1065 Visits: 248
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
tony.sawyer
tony.sawyer
SSC Veteran
SSC Veteran (233 reputation)SSC Veteran (233 reputation)SSC Veteran (233 reputation)SSC Veteran (233 reputation)SSC Veteran (233 reputation)SSC Veteran (233 reputation)SSC Veteran (233 reputation)SSC Veteran (233 reputation)

Group: General Forum Members
Points: 233 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...
Ashesh
Ashesh
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1423 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

Dietmar Weickert
Dietmar Weickert
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: 1608 Visits: 374
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.
george sibbald
george sibbald
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6324 Visits: 13687
As what the characters might be was not specified, add a vote for nchar(1)

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

---------------------------------------------------------------------
stewart.coates
stewart.coates
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 17
Always nchar for those of us in EMEA
Aidan Dennehy
Aidan Dennehy
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1006 Visits: 405
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
Noel McKinney
Noel McKinney
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2017 Visits: 796
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.
Christopher Stobbs
Christopher Stobbs
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: 1574 Visits: 2232
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

w00t
Posting Best Practices
Numbers / Tally Tables

SQL-4-Life
craigpessano
craigpessano
SSCertifiable
SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)

Group: General Forum Members
Points: 5004 Visits: 2276
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.



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