Using datetime float representation as primary key

Posted by devanalyst on Stack Overflow See other posts from Stack Overflow or by devanalyst
Published on 2009-08-23T12:27:48Z Indexed on 2010/05/20 20:30 UTC
Read the original article Hit count: 428

Filed under:
|

From my experience I have learn that using an surrogate INT data type column as primary key esp. an IDENTITY key column offers better performance than using GUID or char/varchar data type column as primary key. I try to use IDENTITY key as primary key wherever possible. But recently I came across a schema where the tables were horizontally partitioned and were managed via a Partitioned view. So the tables could not have an IDENTITY column since that would make the Partitioned View non updatable. One work around for this was to create a dummy 'keygenerator' table with an identity column to generate IDs for primary key. But this would mean having a 'keygenerator' table for each of the Partitioned View. My next thought was to use float as a primary key. The reason is the following key algorithm that I devised

DECLARE @KEY FLOAT

SET @KEY = CONVERT(FLOAT,GETDATE())/100000.0 

SET @KEY = @EMP_ID + @KEY

Heres how it works.

CONVERT(FLOAT,GETDATE()) 

gives float representation of current datetime since internally all datetime are represented by SQL as a float value.

CONVERT(FLOAT,GETDATE())/100000.0 

converts the float representation into complete decimal value i.e. all digits are pushed to right side of ".".

@KEY = @EMP_ID + @KEY

adds the Employee ID which is an integer to this decimal value.

The logic is that the Employee ID is guaranteed to be unique across sessions since an employee cannot connect to an application more than once at the same time. And for the same employee each time a key will be generated the current datetime will be unique.

In all an unique key across all employee sessions and across time.

So for Emp Ids 11 and 12, I have key values like 12.40046693321566357, 11.40046693542361111

But my concern whether float data type as primary key offer benefits compared to choosing GUID or char/varchar as primary keys. Also important thing is because of partitioning the float column is going to be part of a composite key.

© Stack Overflow or respective owner

Related posts about float

Related posts about primary-key