A safe way to store a primary key generator outside the database?

Question

We have an application that stores its data in SQL Server. Each table has a bigint primary key. We used them exclusively on demand, i.e. When you enter to insert a new line, you first make a call to create the next identifier, and then you insert.

We added support for working offline: if your connection does not work (or SQL Server does not work), it stores data in a local file until you return to the Internet, and then synchronize everything that you have done since .

This requires the ability to generate identifiers on the client side. Instead of querying SQL for the next 1 identifier, it now queries the next 100 or 1000 or 10,000 identifiers, and then saves the range locally, so it does not need to query more until those 10,000 are over. This will actually lead them to smaller chunks, so when 5000 ends, it still has a 5000 buffer, and it can request another 5000.

The problem is that as soon as it began to live, we began to receive messages about primary key violations. We saved the data in the Windows registry in HKEY_CURRENT_USER (the only place in the registry from which the user can write). Therefore, after some research, we found that HKEY_CURRENT_USER is part of the roaming profile, so it is possible that identifiers can be overwritten with the old version. Especially if the user registers on several computers on the network at the same time.

So, we have rewritten the part that generates identifiers for reading / writing the file from the user's Local Settings directory. Of course, this should not be overwritten by the old version. But even now, I still see occasional primary key violations. The only thing we can do in this case is to delete any keys in the file, drive the user out of the application and not let them return until new identifier ranges appear.

But if the "Local Settings" are unsafe, what would it be? Is there anywhere you can keep a constant value on a computer that is guaranteed not to be returned to the old version? Can someone explain why the "Local Settings" do not meet these criteria?

I reviewed such a GUID solution, but it has problems on their own.

Answer 1

, , ?

GUID, , , . , 1, , .

Answer 2

, GUID

Answer 3

IDENTITY (http://www.simple-talk.com/sql/t-sql-programming/identity-columns/) bigint, SQL Server .

When your application is disconnected, you keep pending changes local. When it returns to the network, you send your updates (including new records), and SQL Server will INSERT them and automatically assign a primary key, since you have the IDENTITY installation installed.

If you need to know what key value was created / used after insertion, you can use the @@ IDENTITY property ( http://msdn.microsoft.com/en-us/library/aa933167%28v=sql.80%29.aspx )