I just had a showerthought that the default Identity Seed is 1. I have some tables that I know will grow to the billions at a certain point. Wouldn't it make more sense to start on int.Min ( -2,147,483,648) for these tables?

This could just make the difference of migrating your key to bigint in 4 years or in 8 years. Can be relevant enough.

Is this common? It feels weird. Is there anything I am missing?

  • 3
    I suggest you use bigint now rather than wait 4-8 years, – Dan Guzman Jan 29 at 11:32
  • Yup - taking a hospital patient administration system to a backup readonly copy for a whole weekend to do an int to bigint conversion didn't reflect well on the system supplier or the IT Dept. – Stephen Morris - Mo64 Jan 29 at 12:10
  • 1
    You make complete sense. Unfortunately, developers have a habit of assuming a valid identity value is greater than zero and frequently use that assumption in their code. It is truly senseless to lose half the domain of your datatype. – SMor Jan 29 at 12:57
  • 3
    One other aspect is that if you are using data compression you get the benefit right away when you start at 1 but you don't get it until way later if you start at -2 billion. For all of the potential dangers listed below (they're not all good reasons, but they are real) I'd rather just use bigint and start at 1. – Aaron Bertrand Jan 29 at 14:32
  • One case where it can be useful is if you are replacing a table where identity is used with a new one, using int.Min as the seed for the replacement can make it obvious to humans whether they are looking at ids from the old or new table during the transition. – user1937198 Jan 30 at 13:07

There is nothing at all wrong with starting at -2,147,483,648 as far as SQL Server is concerned. Starting at 2,147,483,647 and counting backwards with IDENTITY(2147483647,-1) is perfectly valid too.

Things that would make me be wary of doing so:

  1. It might confuse people who don't expect to see negative values in such positions. It is unusual enough that it could easily look like bug. If nothing else you might get sick of explaining it.
  2. If you ever pass the IDs to other services for any reason they may fail input validation do to that code not expecting to see negatives (an external system shouldn't care about your internal IDs like this, but there are many things that happen which shouldn't!). Furthermore, code in other layers of your application might use an unsigned int32 making the "down from int.max" option even more dangerous as you'll not hit the problem until dropping below 0.
  3. People sometimes use negative magic numbers to have special meaning, and this could cause collisions which lead to difficult to explain bugs. For instance shortening WHERE (x.a<>y.a OR x.a IS NULL AND y.a IS NOT NULL OR x.a IS NOT NULL AND y.a IS NULL) to WHERE ISNULL(x.a,-1)<>ISNULL(x.a,-2) is something I've seen numerous times. Not likely for your PK as that'll never be NULL but might happen in comparing FK values in other tables. I've even seen someone use somevalue>-1 in place of somevalue IS NOT NULL (apparently there is a circumstance where that is more efficient, though he never explained to my satisfaction what the circumstance might be!). There might be that and other odd shenanigans in code outside the DB too.
  4. Most importantly: scale, particularly unexpected scale. Software and data that doesn't die early often outlives their creators' vision, and grows more over time accordingly. Unless you are very constrained by storage or RAM (perhaps in an embedded system) then plan for at least one order of magnitude higher than you expect.
    If I expect hitting ~400,000,000 is ever going to be likely in the lifetime of the data, far before the difference between 2 or 4 000,000,000 is a consideration, I'm already going to go for a larger key so doubling up by using negative values isn't going to make much difference.
    You don't want to be making a change to something core like a PK in four years time, but you even less want to do it in eight years time. In both cases if the design lasts that long you've long forgotten details and many other bits & bobs have started to depend on the key so the changes needing to be made grow massively, and even that single table is going to be massive work to change (unless most of the data gets deleted after a time) as it contains that many rows, then you have all those that refer to it with FKs to work on also.
  • In that (4) scenario it's quite likely that rate of growth is increasing. If it was growing linearly, with no increase in the rate of new rows, then the only way it hits the ceiling is if the database lasts way, way longer than you planned. Which can happen, but is less likely than growth. So, do you want to plan now to write some confusing code that might buy you (say) 6 months in 4 years time, or given that you're even thinking about the issue now, do you just want to make it 64 bits now and be future-proof. Same conclusion: 64 bits it is :-) – Steve Jessop Jan 29 at 19:56
  • 1
    Case 2 can also be an advantage. If you are replacing an existing set of ids with a new one, all those validation checks can help you spot where code is confusing the old and new ids. – user1937198 Jan 30 at 13:09
  • Scale is the most important answer. For consistency I'm always using 64 bit integers for keys, that way I don't have to worry about this being a problem. And it's consistent, I don't need to check if it's a 32 or 64 bit key. – Polygorial Jan 31 at 10:35
  • @Polygorial: and best of all, nowadays you can even argue that if your table is so piddlingly small that it could use a 32 bit key, you're not even wasting much space using a 64 bit key. 16GB or something. Obviously that argument falls down if you have FK references from much bigger tables into this table, so it's not entirely serious, but if someone wants a 32 bit key it's worth wondering exactly what optimisation they're hoping to make :-) – Steve Jessop Jan 31 at 16:57
  • 1
    @Polygorial: I think 32 bits is magic because there was an extended period of time when it genuinely was big enough to address pretty much anything you actually used. Systems able to manage billions of records were niche. Not so much with 16 bits: even those who worked on 16 bit systems felt cramped doing so. But it's a psychological effect, plus some defaults or magic numbers in various places (like Java int) still hanging around from that time. Files over 4GB were the sign the dam had broken. – Steve Jessop Jan 31 at 20:23

No, you're not missing anything. Identities are meant to be meaningless internal-only surrogates which are for the computer to allocate and deal with, and the computer doesn't care whether you use positive or negative numbers.

However.. it's never quite that simple. There will always be a human somewhere trying to read these things, comprehend them, and reconcile them. We just find it harder to think about massive negative numbers than about massive positive ones. Don't make it harder on people trying to make your system work. Eventually these will leak - into reports, external references, screens. Try telling customer minus two billion they have to use a phone key pad to enter their customer ID to access their account!

If you think there is any chance of overflowing the identity in any likely future just use the larger datatype straight away. The considerations that drove Y2K problems are past. Disk is cheap. The extra memory used per query can be balanced against the peace of mind knowing the application is designed for a 50 year lifespan. You won't have to implement extra monitoring against the day 4 (or 8!) years from now when the last integer is allocated.

I know of one system which overflowed and the identities were decremented by 2.1Bn, effectively re-starting at int.Min. I've seen another use negative identities but fail because logging cast them to varchar(10) truncating the minus sign. I know another where the identity was defined as numeric(18,0), just to be sure. And I've seen another overflow without a plan in place, bringing the system down for some time. Because when you do reach int.max you have, by definition, four billion rows to deal with, and that doesn't make for a fun weekend.

  • A fun weekend - only if the problem happens on a Friday afternoon. :-) – Aaron Bertrand Jan 29 at 14:33
  • We used BIGINT (SQL server's name for an 8 byte integer). – Joshua Jan 29 at 20:00
  • "that doesn't make for a fun weekend" - I have actually done that once and it went smoothly, but it was on a very tame table. Crucially I had no FK references to update: the PK on the table was barely used. Also I didn't do it over a tense weekend: I finally got around to scheduling the work about 6 months before the table reached 2 billion rows. I created the table in 2010: arguably should have known better in the first place but I just didn't think about it at the time. – Steve Jessop Jan 31 at 17:02
  • 1
    Oh Aaron, these things only ever happen on Friday afternoons - the ones preceding a public holiday. – Michael Green Jan 31 at 22:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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