Using float view as primary key

Question

From my experience, I learned that using the surrogate INT data type column as the esp primary key. the IDENTITY key column provides better performance than using a GUID or char / varchar data column as the primary key. I try to use the IDENTITY key as the primary key, where possible. But recently, I came across a scheme in which tables were split horizontally and controlled through a split view. Thus, the tables could not have an IDENTITY column, as this would make the Partitioned View not updatable. One of them was to create a keygenerator mannequin table with an identifier column to generate identifiers for the primary key. But that would mean having a "keygenerator" table for each of the Partitioned View. My next thought was to use float as my primary key. The reason is the following key algorithm that I developed

DECLARE @KEY FLOAT SET @KEY = CONVERT(FLOAT,GETDATE())/100000.0 SET @KEY = @EMP_ID + @KEY Heres how it works. CONVERT(FLOAT,GETDATE()) 

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

 CONVERT(FLOAT,GETDATE())/100000.0 

converts the float representation to a full decimal value, that is, all digits are shifted to the right of ".".

 @KEY = @EMP_ID + @KEY 

adds the identifier of the employee, which is an integer with this decimal value.

The logic is that the employee identifier is guaranteed to be unique for all sessions, since the employee cannot simultaneously connect to the application several times. And for the same employee, every time a key is generated, the current time and date will be unique.

In a unique way in all sessions of employees and in time.

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

But my concern is whether the floating point data type offers the primary key advantages over choosing GUIDs or char / varchar as primary keys. It is also important that splitting the float column will be part of the composite key.

Answer 1

It is also important that splitting the float column will be part of the composite key.

What? What for? You have faced tremendous efforts to make this employee / time unique value, what else is needed in the primary key? And on the other hand of this question, are the other components of your key unique already? If so, why not just use them?

Your circuit leaves a bad taste in your mouth. I don’t quite understand why, because the more I think about it, the more solid it seems.

• At first I was worried about performance. But the float is only 8 bytes (provided that your DBMS uses IEEE 754 double), which is not so great. This is no worse than having a 64-bit integer as a key or two 32-bit ints. The key generation process is the only thing that can be slowed down, but even that is not so much.
• Then I was worried about uniqueness. This scheme does not guarantee that you will not generate the same key twice. But, given your claim that the combination of user and date and time will be unique, then this can really work:
  • IEEE 754 double has 53 bits of accuracy.
  • The datetime will use 42 bits. Assumptions:
    • The datetime resolution is 1/300 second (3.33 ... ms). This is true for MS SQL Server, at least.
    • ceiling (log ₂ (86400 * 300 * 100000)) = 42
  • This leaves 9 bits for your employee id. If the employee ID is more than 511, then you will lose part of the datetime, but it will be on the order of milliseconds. Your employee ID can reach 131071 before you lose accuracy for more than a second.
• Then I worried about the difficulties in finding the key value later. Given the problem of 0.2! = 0.1 + 0.1, floating point equality problems always come to mind. But there is no reason why you will perform any calculations on this key value, and presumably it will be in IEEE 754 double format at any given time (whether in a table, in stored proc variables or in variables in your executable file), then it should never change and can be considered as a unique 64-bit value.

Having considered all this, your circuit looks relatively safe. Edoode 's suggestion about not clustering the index is good, and with that in mind, as well as my warnings above about the size of your employee ID, you can use this scheme to generate primary keys in much the same way as any other method.

I still doubt that this method is the best , or even if it is necessary.

• Can other components of the composite key not be used on their own (i.e. as a natural key)?

• You could, as you think, store the sequential key seed in another table. And you only need one table, not one table per partition, as you expect. You just need two columns in this table: one for the section number and one for the current identifier value of this section.

• Using a primary key GUID or varchar is out of the question. Many people do this at different tables. It will not kill your work. And it can be more straightforward, or at least more understandable than this scheme.

• If your composite key already contains an employee identifier, you can simply add a datetime column to the key and call it a day. Or, if not, you can add both columns. There is no reason why you should stretch them.

NTN

Answer 2

I would not consider such a scheme of unorthodox key creation - it has the taste of a bad hack. Why don't you just use integers? There are many methods and algorithms for coordinating the generation of distributed keys. From blocking the entire table (s) and searching for the next free identifier by predefined identifier ranges on the client to get it from information specific to the client (similar to your employee’s offer + time).

Answer 3

Since you did not mention rdbms, I will use the SQL server. When creating the Primary Key, a clustered index of this key is also created. The table is sorted in the order of this key. When using Guides as a primary key (with a clustered index), each insertion means reordering the table. This is also true for your floating point view. Other problems aside, if you want to use this scheme, do not create a clustered index of this primary key.