Data type for Gender

Question

Post reply

Data type for Gender

yaip

SSCrazy

Points: 2656
More actions
September 15, 2005 at 3:37 pm

#187594

What is the recommended datatype for Gender?

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

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

andrewkane17 SSCertifiable Points: 7529 More actions · Answer 1

could think of a few rather un pc answers but don't think that will help your database issue

Ray M SSC-Insane Points: 21093 More actions · Answer 2

In reality its a boleean

so bit is fine just have to decide

0 = Male, 1 = Female

or 1 = Male, 0 = Female

How bout

Char(1)

M (Male)

F (Female)

U (Unknown)

B (Both)

yaip SSCrazy Points: 2656 More actions · Answer 3

Char(1) sound better. I think Boolean might give problems for NULL data.

yaip SSCrazy Points: 2656 More actions · Answer 4

Char(1) sound better. I think Boolean might give problems for NULL data.

EdVassie SSC Guru Points: 60445 More actions · Answer 5

I would go for char(1). Many poorer countries do not perform gender assignment surgery for those babies whose bits are not obvious.

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

Lowell SSC Guru Points: 323504 More actions · Answer 6

i typically use a char(1) with a check constraint to allow only two values:

ie ALTER TABLE BLAH ADD GENDER CHAR(1) NULL CHECK (GENDER='M' OR GENDER='F')

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!

Robert Stewart Hall of Fame Points: 3399 More actions · Answer 7

varchar(1)

Null is unknown. That is the purpose of null.

>How bout

>Char(1)

>M (Male)

>F (Female)

>U (Unknown)

>B (Both)

yaip SSCrazy Points: 2656 More actions · Answer 8

I see that char or varchar is recommended. Any reason to choose one over the other? What is the selection process to select char, varchar, nvarchar?

Robert Stewart Hall of Fame Points: 3399 More actions · Answer 9

Because of my indoctrination by Oracle DBAs, I use varchar and never use char. The reason for nvarchar would be for international character purposes. I have never seen a compelling reason to use char.

kgottfredsen Mr or Mrs. 500 Points: 542 More actions · Answer 10

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 SSC-Insane Points: 21093 More actions · Answer 11

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 SSC Veteran Points: 298 More actions · Answer 12

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 SSCoach Points: 18816 More actions · Answer 13

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 Hall of Fame Points: 3399 More actions · Answer 14

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