I have an application which creates millions of tables in a SQL Server 2008 database (non clustered). I am looking to upgrade to SQL Server 2014 (clustered), but am hitting an error message when under load:
“There is already an object named ‘PK__tablenameprefix__179E2ED8F259C33B’ in the database”
This is a system generated constraint name. It looks like a randomly generated 64-bit number. Is it possible that I am seeing collisions due to the large number of tables? Assuming I have 100 million tables, I calculate less than a 1-in-1-trillion chance of a collision when adding the next table, but that assumes a uniform distribution. Is it possible that SQL Server changed its name generation algorithm between version 2008 and 2014 to increase the odds of collision?
The other significant difference is that my 2014 instance is a clustered pair, but I am struggling to form a hypothesis for why that would generate the above error.
P.S. Yes, I know creating millions of tables is insane. This is black box 3rd party code over which I have no control. Despite the insanity, it worked in version 2008 and now doesn’t in version 2014.
Edit: on closer inspection, the generated suffix always seems to start with 179E2ED8 – meaning the random part is actually only a 32-bit number and the odds of collisions are a mere 1-in-50 every time a new table is added, which is a much closer match to the error rate I’m seeing!