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

Data type for Gender Expand / Collapse
Author
Message
Posted Friday, September 16, 2005 9:27 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Yesterday @ 8:46 AM
Points: 122, Visits: 2,066

Since each varchar column requires an additional 2 bytes to store the column length, I would recommend using char on any column that has a maximum length of 2 bytes or less.

 




Post #220738
Posted Friday, September 16, 2005 9:31 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 12:03 PM
Points: 1,480, Visits: 1,028

Agreed, For SQL Server a general rule that I use anything less than 10 characters should be char. Of course it always "Depends"

when calculating size of a row.

Maximum size of all variable-length columns = Max_Var_Size

Total size of variable-length columns (Variable_Data_Size) = 2 + (Num_Variable_Cols x 2) + Max_Var_Size

 

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_02_92k3.asp

Post #220741
Posted Monday, September 19, 2005 2:40 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, August 22, 2013 8:19 AM
Points: 38, Visits: 17

Char will perfom in its best at sinlgle character length and the best practice is to use char for fixed length string instead of varchar. If i were u I would have used char(1) one with one default value as per requirement.

Post #221009
Posted Monday, September 19, 2005 8:03 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Yesterday @ 8:37 AM
Points: 751, Visits: 1,917

Well actually the purpose of null is 'unitialized', not the same as unknown.

Gender may be unknown where the available data do not specify a gender, but null where no data (including the unknown value) has been entered. Usually this indicates data entry problems.

 



...

-- FORTRAN manual for Xerox Computers --
Post #221119
Posted Monday, September 19, 2005 8:16 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Monday, June 23, 2014 7:56 AM
Points: 199, Visits: 57

"the purpose of null is 'unitialized'"  Not in any of the books that I have read. 

If you are worried about performance problems based on varchar(1) and char(1) then there are other design issues in the database that are causing them, not the use of varchar in this case. 

After designing multi-terrabyte systems in Oracle for the last 3-1/2 years, I will stick with the principles learned there.  I will be glad for the partioning capability in the next release.




Post #221131
Posted Monday, September 19, 2005 8:35 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Yesterday @ 8:37 AM
Points: 751, Visits: 1,917

There is some confusion possible with the ambiguous word 'unknown'

True, Codd's rule 3 says "Null must always be interpreted as an unknown value"  but 'unknown' is plainly used in the sense of an unitialized value i.e. no data was entered.

'Unknown' in the above example can be a very real value in the sense that it explicitly tells us that the information is not available or cannot be determined; not the same thing at all. This kind of differentiation is very important in many real world data sets.

[oops rule 3 not 4]



...

-- FORTRAN manual for Xerox Computers --
Post #221138
Posted Monday, September 19, 2005 11:48 AM


SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Thursday, January 13, 2011 7:55 PM
Points: 124, Visits: 12
That brings up a very interesting point. When would I use nvarchar vs. varchar?
Post #221246
Posted Monday, September 19, 2005 11:52 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Thursday, August 28, 2014 4:00 AM
Points: 21,397, Visits: 9,612
When you need to keep foreign dialects like chinese in the db. You need to use unicode for this task (N). Note that these datatypes take double the space of non unicode types.
Post #221248
Posted Monday, September 19, 2005 12:14 PM


SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Thursday, January 13, 2011 7:55 PM
Points: 124, Visits: 12
With the storage being so cheap right now, does it really matter? Unless it is a performance issue. I would consider using a varchar if I had to develop a database where network traffic would be high.
Post #221251
Posted Monday, September 19, 2005 12:20 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Monday, June 23, 2014 7:56 AM
Points: 199, Visits: 57

If you really want to optimize it for performance, make it a tinyint.  Create a lookup table with all the appropriate values in it and use that.  Numerics are always faster than text.

As to null and unknown, I'll stick with Codd's definition of it.  Why bother defining something that is already defined?




Post #221257
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse