Float as primary key

Question

Float as primary key

albertob

SSChasing Mays

Points: 649
More actions
June 12, 2006 at 1:45 am

#114544

I've read in several places that using a float or real column for the primary key of a table is a bad idea, as it hurts performance, but I can't find any reasoning justifying this statement. Can someone ellaborate a little bit more on this matter?
TIA

Viewing 9 posts - 1 through 8 (of 8 total)

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

Eddie Wuerch SSChampion Points: 12535 More actions · Answer 1

Float and Real columns hold approximate data. The value you try to store in it may not be the value that is actually stored. While there are valid uses for the datatype, in my professional experience, it's primarily been used by people who didn't know any better, who should have used a specific decimal or int instead.

BOL: "Because the binary representation of a floating-point number may use one of many legal rounding schemes, it is impossible to reliably quantify a floating-point value."

Without even getting in to the guts of the storage and query engines, I think it best to examine why you wish to use an imprecise value as the PK. There may be a better datatype for what you are trying to accomplish.

-Eddie

Eddie Wuerch
MCM: SQL

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 2

My view is that it's not a good idea - but - if there is a good reason why you need to do this then fine. I prefer keys which are part of the data rather than surrogate keys .. I think the problem is that rounding of the datatypee may give rise to "duplicate" values.

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

albertob SSChasing Mays Points: 649 More actions · Answer 3

The thing is I have a table with a float primary key mainly because of the broader range of values supported by this type, but a bigint would do it as well. Which of them do you think will give the best performance?

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 4

probably the bigint, I use them quite often.

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

AndrewMurphy SSCertifiable Points: 5586 More actions · Answer 5

The real issue for you to sovle is getting a field which will hold an accurate and predicatable PK. If the size of the field matters, then use a char(x) definition and apply some constraints to ensure it can only hold numbers.

Philip Yale-193937 SSCarpal Tunnel Points: 4603 More actions · Answer 6

Performance will depend on what degree of precision is used for your float. Single-precision uses 4 bytes, double-precision requires 8 bytes. This is an overhead when it comes to non-clustered indexes (assuming you are going to put a clustered index on your float primary key). Since the PK columns are also added automatically to every NC index, the wider your clustered key then the wider all the NC indexes wll be too. From this point of view, float should be better than bigint.

HOWEVER - as has already been discussed above, float is a very bad candidate for a PK, as it cannot be guaranteed to be unique due to the rounding feature.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 7

BigInt should do just about anything you need. BigInt holds a huge number ((2^63) -1) and much less behind the scenes processing is done by SQL Server on the Int and BigInt data types than on Float. Further, most folks will pass criteria as integer data so you also have the hidden cost of implicit conversions when you use other data types, especially CHAR data types. Anytime some form of character based solution get's involved, things slow down quite a bit compared to when INT or BigINT are used.

If you are using SQL Server 7 and need numbers that big, use DECIMAL(x,0). At least it's not an approximation like Float or Real and doesn't force decimal places like Money does.

--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)

stax68 SSChampion Points: 11711 More actions · Answer 8

Agree float is absolutely inappropriate for a surrogate key.

The efficency of a key column depends mainly on two things: how big it is, and how straightforwardly it is compared.

Using character data for numbers is bad on both counts: you need one (or 2 for unicode) bytes per digit, and comparing the data involves string matching which is not a straight comparison of binary values. Float is fine for size, but comparisons are unreliable (!!!!) and probably a bit more expensive than int types.

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant