Too bad I wasn't aware of this post earlier but I feel like contributing. The whole point behind my post was basically to raise awareness to the guy in need about the NOLOCK option but maybe I should have stressed a little more to be really aware where to use it. I used to be the head developer of a "large" oltp system where we needed to do nasty calculations in real-time to present on the web. The data source changed so fast that using NOLOCK was the smallest of several cons when doing selects. In the post Kristen referred to I stated that 98% of all selects are on historic data that might as well have been cast in stone, and why on earth would I need to bother my server with locking these data when there is no chance whatsoever that they will be changed?
And the question was raised if I had done extensive testing on this or not, and belive me; I have (!) and the results made me a sworn beliver! The most important thing to consider is this: "Will anything ever get corrupted if I use NOLOCK in this select?" If the answer is no, I can't think of any reason why you shouldn't use it.
-- Lumbago "Real programmers don't document, if it was hard to write it should be hard to understand"
Sunil, Thank you for your response, I have follow ups: 1. regarding the balance between the two it appears that while the new feature is more consistent for a company using nolock because their performance is on the edge - the scaling requirements to ensure that changing to snapshot queries won't worsen their performance situation make it a difficult sell to the customer - expecially that 14 bytes of data on each row requirement...
2. Regarding guidance, I agree with your suggestions - in fact I'll go furhter and say the "Update <table ReportData> With Select <Table1Data> join <Table2Data>" style of query routinely causes problems and would be better implemented as a large query with a separate process to then insert the rows of the Report Data to maintain a scalable environment. But what I'm looking for is formal guidance that says that in a production environment of thousands or millions of rows this type of statement is bad because it creates a long running transaction.
Similarlly a statement that while a bulk upload to initialize a database makes sense a daily upload of data for an older file based interface is more scalable when the uploading process executes a larger number of small transacations instead of trying to do a bulk upload in the middle of traditional OLTP activities.
I think there are alot of people that recognize these patterns in database implementation, but unfortunately there are far more people who don't and who have to be convinced every time.