Using datetime float representation as primary key
- by devanalyst
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.
