I'm working with an old sql server 2000 database, mixing some of it's information with a new app I'm building. I noticed some of the primary keys in several of the tables are floats rather than any type of ints. They aren't foreign keys and are all unique. I can't think of any reason that anyone would want to make their unique primary key IDs floats, but I'm not a SQL expert by any means. So I guess what I'm asking is does whoever designed this fairly extensive database know something I don't?
+2
A:
Is it a NUMERIC( x, y) format and an IDENTITY? If so, it might be an upgrade from an older version of SQL Server. Back-in-the-day IDENTITY could only be a NUMERIC format, not the common INT we use today.
Otherwise, there's no way to tell if a float is suitable as a primary key -- it depends upon your domain. It's a bit harder to compare (IEEE INT is more efficient than float) and most people use monotonically increasing numbers (IDENTITY), so integers are often what people really want.
Since it looks like you're storing ints:
To answer the original question more directly: If you're storing ints, use the integer datatype. It's more efficient to store and compare.
Matt Rogish
2009-07-09 17:17:07
Actually, PKs could always been INTs but since BIGINTs did not exist, people used NUMERICs
zvolkov
2009-07-09 17:20:02
Ah OK. I know that Sybase SQL Server -- forever ago -- only supported NUMERICS and I thought that SQL Server's older than 6.5 inherited that restriction
Matt Rogish
2009-07-09 17:27:46
I believe they used SQL Server 2000 from the beginning, although I'm not 100% sure. I'm going to double check though.
Graham
2009-07-09 17:41:40
+1
A:
Floats have one interesting property: it is always possible to insert a value between two others, except for the pathological case where you run out of bits. It has the disadvantage that representational issues may keep you from referring to a row by the key; it's hard to make two floating point values equal to each other.
Mark Ransom
2009-07-09 17:23:57
As a matter of principle, if you want these properties I would think you could always add an indexed column containing the float, in addition to the int primary key.
Robert Harvey
2009-07-09 17:36:09
That was my first thought, but I can't find any like that, they're all sequential 3.0, 4.0, 5.0 etc. perhaps this was some sort of future proofing, although I'm not quite sure why since they just seem to be IDs. Thanks for your input.
Graham
2009-07-09 17:44:06
It doesn't sound like this is the right answer for the questioner's specific case...but you get voted up anyway for having an interesting and possible answer for the next person to search for this question.
Beska
2009-07-09 18:23:13
+1
A:
I worked with someone who used floats as PKs in SQL Server databases. He was worried about running out of numbers for identifiers if he stuck to INTs. (32 bit on SQL Server.) He just looked at the range of float and did not think about the fact never mind that for larger numbers the ones place is not held in the number, due to limited precision. So his code to take MAX(PK) + 1.0 would at some point return a number equal to MAX(PK). Not good. I finally convinced him to not use float for surrogate primary keys for future databases. He quit before fixing the DB he was working on.
To answer your question, "So I guess what I'm asking is does whoever designed this fairly extensive database know something I don't?" Most likely NO! At least not with respect to choosing datatypes.
Shannon Severance
2009-07-09 17:57:18
+2
A:
I'm currently working with a rather big accountant package where EACH of 350+ tables has a primary key of FLOAT(53). All actual values are integers and the system strictly checks that they indeed are (there are special functions that do all the incrementing work).
I did wonder at this design, yet I can understand why it was chosen and give it some credits. On the one hand, the system is big enough to have billion records in some tables. On the other hand, those primary keys must be easily readable from external applications like Excel or VB6, in which case you don't really want to make them BIGINT.
Hence, float is fine.
GSerg
2009-07-09 18:51:24
That's interesting, can you explain what you mean by easily readable? Do you mean its easier for the external applications to read them or human readable? Thanks for the response.
Graham
2009-07-09 19:09:48
No, I mean those systems with which people are writing their own little helper applications around the big thing, can easily be not able to cope with 64-bit integers. There is no data type in VB6 to natively suppor a BIGINT, you'd have to play with Double (as Excel suggests automatically) or Variant-Decimal (as ADO does). And that's at least something! I can easily imagine an external system that will not be able to consume BIGINTs at all.
GSerg
2009-07-09 20:16:47