Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <blockquote> <p>Also important thing is because of partitioning the float column is going to be part of a composite key.</p> </blockquote> <p>What? Why? You've gone through great pains in attempt to make this employee/time based value unique, what else would you need in the primary key? And on the other side of that question, are the other components of your key unique already? If so, why not just use them?</p> <p>Your scheme leaves a bad taste in my mouth. I'm not quite sure why, though, because, the more I think about it, the more solid it seems.</p> <ul> <li>At first I worried about performance. But a float is just 8 bytes (assuming your DBMS uses IEEE 754 double), which just isn't all that big. That's no worse than having a 64-bit integer as a key, or two 32-bit ints. Your key generation process is the only thing that might be slowed down, but even that not by much.</li> <li>I then worried about uniqueness. This scheme doesn't <em>guarantee</em> that you won't generate the same key twice. But given your assertion that the combination of user and datetime will be unique, then this might actually work: <ul> <li>An IEEE 754 double has 53 bits of precision.</li> <li>The datetime will use 42 bits. Assumptions: <ul> <li>Resolution of datetime is 1/300 second (3.33... ms). This is true for MS SQL Server, at least.</li> <li>ceiling(log<sub>2</sub>(86400 * 300 * 100000)) = 42</li> </ul></li> <li>This leaves 9 bits for your employee ID. If the employee ID is greater 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 will lose accuracy of more than a second.</li> </ul></li> <li>I then worried about the difficulty in looking up a key value later. Given the 0.2 != 0.1 + 0.1 problem, concerns of floating-point equality always come to mind. But there's no reason you would be performing any calculations on this key value, and presumably it would be in IEEE 754 double format at any given time (be it in the table, in stored proc variables, or in variables in your executable), then it should never change and can be treated as a unique 64-bit value.</li> </ul> <p>After considering all this, your scheme does appear relatively safe. <a href="https://stackoverflow.com/questions/1318455/using-datetime-float-representation-as-primary-key/1318587#1318587">Edoode's suggestion</a> about not clustering the index is a good one, and with that in mind, as well as my caveats above about the size of your employee ID, you can use this scheme to generate primary keys just about as well as any other method.</p> <p>I still question whether it's the <strong>best</strong> method, though, or if it's even necessary.</p> <ul> <li><p>Can the other components of the composite key not be used by themselves (i.e., as a natural key)?</p></li> <li><p>You <em>could</em>, as you suggest, keep a sequential key seed in another table. And you would need only one table, not one table per partition as you assume. You would simply need two columns in this table: one for the partition number, and one for the current identity value of that partition.</p></li> <li><p>Using a GUID or varchar primary key isn't out of the question. Many people do this on many different tables. It won't <em>kill</em> your performance. And it might be more straight-forward, or at least more easily understood, than this scheme.</p></li> <li><p>If your composite key already includes the employee ID, you could just add a datetime column to the key and call it a day. Or if not, you could add both columns. There's no reason you have to mash the two together.</p></li> </ul> <p>HTH</p>
    singulars
    1. This table or related slice is empty.
    1. POUsing datetime float representation as primary key
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USCommunity
    1. USP Daddy
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POUsing datetime float representation as primary key
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COI finally made up my mind to use composite primary key as suggested by PDaddy. Initially I thought using composite keys might degrade performance but in my case using a combination of Int and datetime fields does not make much difference. The composite key is non clustered and I have created a clustered index on the Int field
      singulars
      1. PO
      1. USdevanalyst
    2. COForgot to mention that the clustered index is created on the Int field because its going to be used in joins
      singulars
      1. PO
      1. USdevanalyst
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload