SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Data type for Gender


Data type for Gender

Author
Message
kgottfredsen
kgottfredsen
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: 2775

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
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11835 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 Journeyman
SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)

Group: General Forum Members
Points: 96 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
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7818 Visits: 2565

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
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: 1555 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
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7818 Visits: 2565

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
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: 1364 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 Guru
SSC Guru (154K reputation)SSC Guru (154K reputation)SSC Guru (154K reputation)SSC Guru (154K reputation)SSC Guru (154K reputation)SSC Guru (154K reputation)SSC Guru (154K reputation)SSC Guru (154K reputation)

Group: General Forum Members
Points: 154722 Visits: 9672
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
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: 1364 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
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: 1555 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