Float vs Char primary key 1

[adam0101]

I'm on a project where I noticed that the database has a table with a primary key that always consists of 13 digits. Right now it is a float data type. Would it be more efficient to convert it to a char(13)?

Adam

 

[NoCoolHandle]

NO!

Char(13) = 13 bytes of storage..
Numeric(13) = 9 bytes of storage.

This effects storage and joins. All fk's would get bigger etc..

 

[adam0101]

Hmm, good to know. I was told along the way that numeric data that won't have any calculations done on it should be stored as a string. For example, a social security number. But now that I look at it, a ssn could be stored using only 5 bytes instead of 9. I was lied to!

Thanks for the help!

Adam

 

[vongrunt]

4, if you use plain ol' 32-bit signed integer (int type)

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[NoCoolHandle]

Social security numbers should NEVER be used as a primary key as there are DUPLICATES!!!!!!!

I heard from a person who used them for a chicago hospital patient id and they had 3 people that had the same (correct as per ss office) ss number in the hospital when they tried to bring the systme up.. (caused a weee problem) - true story!!!

YUK..

Next statement.. based on the duplicate staus of ss numbers you might want to check you are the person you are donating a retirement check to.

Rob

 

[vongrunt]

Out of curiosity... how often that happens and why?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[chiph]

Yup, there are dupes in the SSNs. One case I heard about was two little girls who were born on the same day in the same hospital with similar names. When the clerk at the SSA processed their applications, they made a mistake and gave them both the same number.

I'm not thrilled about the idea of using a float as a key. This is because it's impossible to represent a floating-point number precisely in binary storage (.1 base-10 ends up as a repeating-number in base-2).

I would recommend using an Identity column for your key, and all domain-specific values then become ordinary columns, perhaps with an alternate index on them if you do searches.

Chip H.


____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first