August 5, 2002 at 8:53 am
I have a case where I need to increment a "Change Number" every time I add or edit rows in a SQL Database. It's kind of a replication scheme, but the clients involved aren't all SQL clients.
Basically, 400 different companies post a lot of records and retrieve a few to/from a centralized SQL Server. Most of the updates are done via ADO from Jet based clients. They need to know when someone had edited or added records they care about, so we use a Change Number system. You simply remember the highest Change Number available for a given table when you last checked, then when you check again just ask for all records (with other qualifiers) that have a higher change number.
We were using TimeStamp columns as our ChangeNumbers so that the updating was automatic. Two ugly problems showed up.
One is that SQL Server (Version 7) doesn't seem to like to use the indexes on these columns and the Query Plan was frequently choosing other less efficient methods.
The second problem is worse. After a disk failure and restore of a current backup, we found that the Timestamp columns all got updated during the restore. I'm sure that this is by design since Timestamps are really meant for SQL's internal use in replication, but it caused us a huge pain - all our customer's client programs thought every record in the 5 Gig database was new or updated!
Finally - the question. I've decided to use an integer column for our ChangeNumbers and update it using triggers. The only question is how? One way is to create a ChangeNumber table that has little in it besides an Identity column. The triggers in the various tables call a SP that inserts a row in the ChangeNumber table and returns the new "ChangeNumber" to be stored in the original table. It just seems inefficient to be constantly adding rows to a table for this reason. Another way would be to do a "Select Max(ChangeNum) from THISTABLE" and then add one to it in the trigger. I'm not sure this guarantees me a unique ChangeNumber because two customers could be doing the select at once. Any other ideas? They'll have to include locking of some sort to make sure that all the ChangeNumbers stay unique.
Thanks for your thoughts,
Student of SQL and Golf, Master of Neither
Student of SQL and Golf, Master of Neither
August 5, 2002 at 10:20 am
August 5, 2002 at 10:41 am
I thought briefly of using the GUID, but this excerpt from BOL changed my mind:
The uniqueidentifier data type has several disadvantages:
The values are long and obscure...
The values are random and cannot accept any patterns that may make them more meaningful to users.
There is no way to determine the sequence in which uniqueidentifier values were generated. They are not suited for existing applications that depend on serially incrementing key values.
At 16 bytes, the uniqueidentifier data type is relatively large compared to other data types such as 4-byte integers. This means indexes built using uniqueidentifier keys may be relatively slower than implementing the indexes using an int key.
Since our customers will be doing things like "Select * from Jobs where WHO_TO = ME and ChangeNumber > LastChangeNumberIGot" the GUID isn't very helpful. It's not sequential at all, and the JET customers can't store a long binary data type in the first place. It does have the attribute of not changing during a restore though.
Thanks for the idea, but I don't think it's "the answer".
Student of SQL and Golf, Master of Neither
Student of SQL and Golf, Master of Neither
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply