Using timestamp plus integer as primary key

Question

In Sql Server, what would the drawbacks and disadvantages be of using a primary key that is:

Two Character Table Identifier + YEAR + MONTH + DAY + HOUR + MINUTE + MILLISECOND + random integer between 0 and 100?

Why would this be preferred to using an Integer Auto_Incremented field? (Unless it wouldn't, then I want to know that too.)

I would love reasons why this is a really bad design practice. Casting everything in order to get back that integer value is also a total pain in the ass.

I basically stand once chance at getting my small team of developers access to help improve our applications' database, which it needs badly-To avoid situations like primary key Varchar(50) fields with left-padded zero's, or to just start improving the non-normalized database, or comma-separated lists in one field.

Answer 1

Sure you CAN do it, but why would you? To save a few CAST expressions? That seems a bit weak.

The reasons to have auto incrementing surrogate int PK's are many:

1. You pretty much never have to manage them with normal databases. Ever. Unless you are rebuilding tables or inserting data where you have to turn the identity off and on. But operationally it works well with little overhead. If you do it the way you suggest, it causes much more overhead with the need to have a UDF to create the key which involves a lot more CPU cycles to create than an auto increment int.
2. They are the most efficient storage solution. The way you suggest would require a varchar(20)/char(20) or so meaning 20 bytes per row just for the key. A normal int is only 4 bytes. It might not seem like much, but put 100 million rows in there and you increase the size by around 1.5GB. Not cool. Then you have to put it as the FK in another table, another 1.5GB. Or in 3 other tables and your DB is needlessly almost 5GB larger. You get the idea...
3. Tables should really be linked together with something the users don't care about or understand. This is so that you have a low granularity of control as a DBA, a value that you can refer to which the users know nothing of which becomes important when doing highly granular changes, fine tuning the table or managing the data structure. I can just tell you this from experience of working with DBs that have composite keys like you suggest. They become frustratingly annoying and you are always tempted to just put in an auto incrementing int and be done with the complexity and frustration. Imagine for example, you wish to quickly insert a whole bunch of records that have come from a different source. With an auto increment, easy with minimal hit on server resources. With a composite key... you have to run a UDF which creates the key for each insert... and in your example the randomiser for 100 keys per ms might not be enough, SQL Server may work too fast for you and give you the same ID for 2 records. Damn, you need a different approach which doesn't violate the unique constraint. Or imagine the user wishes to start "reusing" a key for some reason (it happens, sometimes on some mistaken idea of "recycling making the database faster"). Users end up trying to tune the DB themselves, a recipe for disaster. Telling them (and having) behind the scenes things occurring that they don't understand gives a level of separation that often avoids the situation. They don't try to optimise anything, taking their sticky little fingers out of your side of the business, meaning you can optimise the way it should be done. Much like a teenager with no idea attempting to change the electronic timing of a modern car with their smart phone and a hammer... a job much better done by a professional with the right knowledge and tools.
4. SQL Server expects int PK-FK auto increments and is optimised for it. It will mean faster processing overall due to FK checks when inserting records.
5. Lastly, I find natural keys to be absolutely fine with small databases with few users that need little optimisation and management. They can also make for cleaner looking queries (by using the composite key in WHERE statements) when the queries are simple, meaning you have less joined tables etc. But as soon as the database starts getting large and complex... managing auto incrementing ints surrogate keys becomes a lot easier.

My final thought: Composite keys are FINE for lookup tables which have a low number of members (meaning a shorter composite key and therefore low space requirements) that are not subject to a lot of change (meaning low management overhead). An example is two letter state codes in the US. They will mean potentially one less join and over many stored procs, this could make a small but measurable performance improvement.

Answer 2

This would most definitely not be preferred to an auto-increment integer field.

For starters:

1. Index width would be degraded significantly with your proposal.
2. Just how do you propose to manage all those random 2-digit integers to enforce uniqueness. Have you thought of how much code would have to be written and maintained to implement this scheme.
3. Won't typing all those key fields in for every join in the implementation be a joy.

As an aside; Why on earth do you want to insert a two-character table identifier into every instance of this new field? The table that the row came form is immediately obvious from the table being inspected.

If you really think that your data collision rate across a large number of sites is so extreme as to justify a scheme such as this, at least use a GUID, an already implemented technology, instead of this NIH design.

Update

The monotonic nature of the autoincrement key is made use of in some very efficient tally-table algorithms for running totals( at least) to enforce proper sequencing of running-total calculations. This scheme would invalidate the use of those algorithms.

Answer 3

Your idea isn't that crazy. Ordering the primary key DOES reduce fragmentation, but this can be achieved with an IDENTITY column. However, ordered keys also has a drawback, namely the one described here: http://kejser.org/clustered-indexes-vs-heaps.

Now, I can see how you would benefit from knowing WHEN the key was generated and that you therefore want to pack a little extra information in there. It breaks normalisation rules - but those are for academics, not for real system designers.

I think I also know where you are coming from (and correct me if wrong) when prefixing the key with the table name - this prevents users from joining on the wrong keys and getting bad data because any such join will return nothing.

If you want to keep your strategy and maintain the benefits of good physical design, here is how I would do it.

• Make the key a 64 bit integer
• The upper 32 bits are constructed like so: Year * 10000 + Month * 100 + Day (This is 8 digits long and fits into 32 bits)
• The lower 32 bits are generated from a 32 bit sequence (look up CREATE SEQUENCE) that is global across all tables and rolls over when it hits maxint. This makes the key unique across tables without needing the prefix of the table name.

With the above strategy, you can generate 2B keys every day, which hopefully is enough. If it isn't, you could consider shaving the year down to 2 digits (yeah, yeah, I know...)

This above strategy creates a sequentially laid out index with the benefits and drawbacks this has. If you want to change the key logic to use a more scalable layout, you can flip the bits sequence of the lower 32 bits using the trick described here: http://dangerousdba.blogspot.co.uk/2011/10/day-sequences-saved-world.html.

Answer 4

My preference is to use an identity column for primary key and if required add a unique index on other fields if you need to enforce uniqueness.

In my opinion, a primary keys job is to uniquely identify a single row of data. Integers do this more efficiently without risk of ambiguity.

I dislike using varchars as pkeys or in joins as they can be affected by spaces, upper/lower case, padding, leading zeroes or implicit conversions.

Having a simple pkey in a single field with unambiguous values will make everyone's job easier.

Answer 5

Hmm, I agree with you, almost, and not with the folks who suggest using an arbitrary integer to identify your rows.

Your primary key should be

tid char(2), asof datetime, sequence smallint

where you control the sequence. That's a perfectly fine primary key because it identifies the row. The burden is on the surrogate crowd to explain the circumstances under which a "more granular" identifier will be needed.

This gives your a 16-byte key. It's correct, hardly gigantic, and likely as not more efficient than a surrogate key. Why?

• it uses values you application will have on hand
• as a clustered key, it will be ordered by naturally occurring data
• a surrogate key would require a unique constraint on them, too

Use datetime instead of character data to represent a date. You get automatic date validation, and date arithmetic is greatly simplified.

Also, is the sequence number really needed? If not, you can drop it. If it is, that's another reason to eschew auto-incrementing values: they are a point of resource contention, because all insertions must get their auto-incrementing value from the same place.

If you do need the sequence number, do it this way:

insert into T as t
select /* values ... */ ,  1 + coalesce(max(sequence), 0) as seq 
from T 
where tid = t.tid and asof = t.asof

because the atomicity of insert ... select guarantees that each process will get a distinct value.