Which Data Type to use

Question

Which Data Type to use

dave_282

SSC Rookie

Points: 39
More actions
February 22, 2009 at 6:29 am

#128532

I want to have a column in an Employee table called Sex in SQL Server 2005 and I want to be able to choose either M or F. How do I set it to only allow M or F and what data type will it need to be?

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 1

You can do it char(1) with check constraint that will limit the values to M or F. BOL has some examples on how to define check constraint (look for it in the article about create table). If there could be a situation that it isn’t clear if it is a male or female (for example you get a letter from someone, and it isn’t clear), then you should make it nullable or consider adding another status that unclear.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Lowell SSC Guru Points: 323518 More actions · Answer 2

use a char1, and simply put a check constraint--

CREATE TABLE blah(

GENDER CHAR(1) NULL DEFAULT 'U' CHECK(GENDER IN('M','F','U') --U for undisclosed? some politically correct places require that nowadays

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lowell SSC Guru Points: 323518 More actions · Answer 3

omg Adi great minds think alike. paraphrased but verbatim answers

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 4

I also enjoyed the fact that there was another answer that was the same as mine and came almost at the same time that my answer came. It feels as if it gives more validity to the answer:-)

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

dave_282 SSC Rookie Points: 39 More actions · Answer 5

Thanks guys, the check constrant worked. Is is possible though to have M and F come in as a drop down when doing data entry so that nothing else could be entered or selected or is that not possible?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

Yes, put a dropdown down in whatever application you are building to enter that data.

If it's ssms or QA, then forget about it as it's not possible.

David.Poole SSC Guru Points: 76001 More actions · Answer 7

ISO5218 has the following definitions for Gender

0 = Not Known

1 = Male

2 = Female

9 = Not applicable

If you have the freedom to choose the encoding then start with the ISO standard values.

LinkedIn Profile

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

David.Poole (2/22/2009)
9 = Not applicable

?????

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 9

... and what's the value for a transexual or people with both sexes?

I'm really asking, just wondering if they covered all bases.

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 10

Ninja's_RGR'us (2/22/2009)
... and what's the value for a transexual or people with both sexes?
I'm really asking, just wondering if they covered all bases.

Heh... too easy... use the BIT datatype... 0 for female, 1 for male, NULL for everyone else. 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 11

I don't like bits for things like this and I'd go with ISO. Who knows what crazy categories someone might assign, so leave room for growth or changes.

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 12

Steve Jones - Editor (2/22/2009)
I don't like bits for things like this and I'd go with ISO. Who knows what crazy categories someone might assign, so leave room for growth or changes.

http://www.merriam-webster.com/dictionary/joke 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Anye Mercy SSCrazy Points: 2134 More actions · Answer 13

Ninja's_RGR'us (2/22/2009)
... and what's the value for a transexual or people with both sexes?
I'm really asking, just wondering if they covered all bases.

Totally off topic, but transgendered people generally are considered to be the gender they identify with, not the gender they were born with. So a M-to-F person is considered female.

Hermaphrodites, ok, you got me there... but again if the person identifies with a particular gender it is considerate to accept that statement.

There are also people (I think the term is pansexual but could be misremembering) that consider themselves genderless or find gender to be an irrelevant societal construct... but I think the catch-all category is probably good enough for all exceptions to the rule.

--
Anye Mercy
"Service Unavailable is not an Error" -- John, ENOM support
"You keep using that word. I do not think it means what you think it means." -- Inigo Montoya in "Princess Bride"
"Civilization exists by geologic consent, subject to change without notice." -- Will Durant

David.Poole SSC Guru Points: 76001 More actions · Answer 14

GilaMonster (2/22/2009)
David.Poole (2/22/2009)
9 = Not applicable
?????

If you have a contact table and the contact is "The company secretary" then you are trying to contact a title rather than a person so the gender would be 9.

I'm finding that there is an ever increasing requirement to share data externally either through web services, cloud computing or old fashioned methods and trying to provide documentation, support or bespoke services for each partner/client is an absolute nightmare. Sticking to a standard lets us offload the pain.

LinkedIn Profile