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


Data type for Gender


Data type for Gender

Author
Message
kgottfredsen
kgottfredsen
SSC-Enthusiastic
SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)

Group: General Forum Members
Points: 123 Visits: 2639

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.





Ray M
Ray M
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1491 Visits: 1076

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


Ragesh Chavarattil
Ragesh Chavarattil
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

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


jay-h
jay-h
SSC Eights!
SSC Eights! (923 reputation)SSC Eights! (923 reputation)SSC Eights! (923 reputation)SSC Eights! (923 reputation)SSC Eights! (923 reputation)SSC Eights! (923 reputation)SSC Eights! (923 reputation)SSC Eights! (923 reputation)

Group: General Forum Members
Points: 923 Visits: 2222

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 --
Robert Stewart
Robert Stewart
SSC-Enthusiastic
SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)

Group: General Forum Members
Points: 199 Visits: 60

"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.





jay-h
jay-h
SSC Eights!
SSC Eights! (923 reputation)SSC Eights! (923 reputation)SSC Eights! (923 reputation)SSC Eights! (923 reputation)SSC Eights! (923 reputation)SSC Eights! (923 reputation)SSC Eights! (923 reputation)SSC Eights! (923 reputation)

Group: General Forum Members
Points: 923 Visits: 2222

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 --
yaip
yaip
SSC-Enthusiastic
SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)

Group: General Forum Members
Points: 124 Visits: 12
That brings up a very interesting point. When would I use nvarchar vs. varchar?
Ninja's_RGR'us
Ninja's_RGR'us
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20931 Visits: 9671
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.
yaip
yaip
SSC-Enthusiastic
SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)

Group: General Forum Members
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.
Robert Stewart
Robert Stewart
SSC-Enthusiastic
SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)

Group: General Forum Members
Points: 199 Visits: 60

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?





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