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.


6 Answers 6


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.

    Comparisons between integers are fast in general, and this is just as true for joins. There are very many optimizations in the engine for integer/bigint (too many to list), but as an example, seeking down the b-tree may use linear interpolation instead of binary search. As a second example, batch mode processing is very much faster when the normalized representation fits in 64 bits. This is the case for integer and bigint, but not, for example, for UUIDs. Third example, bitmap filters built on not null integer/bigint can be pushed into the storage engine for early evaluation. Bitmap filters can also be more efficient in batch mode when the 64-bit threshold is not exceeded.

  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.

  • Good analysis but a compound PK epoch-time+auto-increment is typically for super-sharded systems which require time-based data-distribution. Do we really need a UDF ? Can't we build a Key Generation Service (KGS) that background generates keys ( taking advantage of one or more SQL Server tables for the autoincrement portion ( e.g. odds + evens ) ) and stores in a Keys db. Each app server retrieves from KGS batches of keys that it caches - and so for live writes keys are immediately available. Yes additional storage is a problem but far less so every year as db technology advances.
    – BaltoStar
    Oct 20, 2018 at 20:03
  • Anyone who thinks this answer is good (for OLTP) must check out a data modeling book, like Richard Barker's classic or titles from David C. Hay. And then study Celko's, namely SQL Programming Style. May 6, 2020 at 9:01
  • Cite any source for #4. You shouldn't be using auto incrementing ints for PK's in SQL server.
    – Jeremy
    Jun 9, 2021 at 20:18

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.


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.


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.


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.

  • What is the benefit from flipping? Aug 14, 2015 at 10:15
  • That the keys get distributed evenly over the B-tree.. This improves scale by removing the hotspot at the end of the B-tree you get if you use sequential keys Aug 14, 2015 at 10:44

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.

  • I sympathize with the idea of using natural key's, something that the user can identify. One thing to take into consideration though is the stability of the timestamp (which we know nothing about). Also, it is questionable whether a timestamp really is that familiar to the user. Aug 14, 2015 at 8:01
  • 2
    SQL Server's IDENTITY value generation is quite efficient and not at all likely to be a bottleneck in this scenario. Aug 14, 2015 at 10:57

2021 answer:

Are you using SSD's for your storage? Don't obsess over fragmentation or adjacent records- every sector on the disk is virtually the same distance from every other sector.

Bytes are cheap. Use a GUID for your PK- this is as easy as setting a default of newid() in SQL Server. This will also work effectively as a rowguid and prevent any developer from joining a FK to an invalid PK (there won't be any matches.) You don't even have to ask SQL if the GUID is in use somewhere- it just isn't. The odds of any collisions are astonishingly thin. So thin you honestly can forget they exist. It just won't happen in any of our lifetimes.

Avoid INTs for PK. When you forget about 2.1 billion and inevitably hit that wall- it will be painful.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.