adam.everett (2/24/2011)
nice, good article never thought about how your could use vertical tables to solve that, and will also go and look at sequences in the next sql release!.Can I ask why in the original code the lock on the kvp was required for the duration of the whole buniness transaction. Could they just not of got the required key values into some temps using some short transactions before the main long running business process transaction took place?
come to think about it, the kvp would not even need to be in a transaction if all they want is to increment a seed, a single update key statement to increment and at the same time assign the read value to a temp.
thanks;-)
Thanks for your feedback Adam, and yes, you are right! If only all application developers also knew this! One of the main reasons I decided to use the architecture using Identities was so that I could put in place a method for deriving KVP values which would be independent of long running transactions.
The procedures written in the article are cutdown versions of the real business procedures, which were much more lengthier. They also had conditions in them after which KVP values were derived, or completely skipped.
But I take your point, ideally, the derivation of KVP values should be done outside transactions.
Ajit Ananthram
Blog - http://ajitananthram.wordpress.com