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 «««1516171819

one character of data Expand / Collapse
Author
Message
Posted Monday, August 23, 2010 8:18 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:48 PM
Points: 6,021, Visits: 8,290
pankaj upadhyay (8/23/2010)
thanks actually i get confused after reading the ans Either char(1) or varchar(1) may be used. for this question that when we take varchar(1) than will it save length or not.

anyway thanks again.

No, in this case varchar will not save space, it will cost more space.

For varchar, the space taken is (actual length) + (2 bytes overhead). So for a one-char string, the actual space is 1 + 2 = 3 bytes; for an empty string the actual space is 0 + 2 = 2 bytes.
For char, the space taken is (declared length). So both for a one-char string and the empty string, the actual space taken is always exactly 1 byte.

Varying length strings should only be considered when the difference between average actual length and maximum length is more than 2. If it's less than two, always use fixed length. If it's way more than two, varying length is a no-brainer. Between 2 and, let's say, 3.5 - those are the cases when you really have to consider other stuff.



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #973469
Posted Monday, August 23, 2010 8:23 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 5:59 PM
Points: 31,082, Visits: 15,529
Hugo is correct, a varchar(1) or nvarchar(1), doesn't make sense. It uses more space. Not much, but more.

I have typically gone with char(4) or less. When I get to 5, I usually just go varchar at this point, especially as many fields above this size aren't necessarily set. Too often I've seen someone say that the values above 5 are set, like for US phone numbers or zip codes, however in my career we've gone to 9 digit zip codes and 11-13 digit phones.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #973473
Posted Monday, August 23, 2010 8:45 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, September 22, 2014 1:20 AM
Points: 1,383, Visits: 1,315
With char(n) where n > 1 you must take care of ANSI PADDING settings. It's another story.
nchar is "better", it always behaves as set to ON.
From this point of view varchar(2) can be more comfortable, but it takes some extra space.




See, understand, learn, try, use efficient
© Dr.Plch
Post #973496
« Prev Topic | Next Topic »

Add to briefcase «««1516171819

Permissions Expand / Collapse