Click here to Skip to main content
15,916,398 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
As a professional, Which should be better to use
statement in stored procedure or Lock Hints in particular query..



                                     FROM   #Targetlist AS T    
                                            INNER JOIN new.CompanyIndex AS CI2 WITH (NOLOCK) ON [T].[CompanyID] = CI2.CompanyID    
                                     WHERE  [CriteriaID] = @CriteriaID    
                                            AND ( @includeNonMain = 1    
                                                  OR isMain = 1    
                                            AND [SegmentNo] = @SegmentNo    
                                   ) AS CI ON UD.[CompanyID] = CI.CompanyID    
                WHERE   UD.[LocalIdentifierID] = @LocalIdentifierID    
                        AND [UD].[IsActive] = 1    
                ORDER BY UD.[CompanyID] 
Updated 16-Oct-12 23:28pm
Corporal Agarn 17-Oct-12 11:32am    
Remember they are similar but not the same. We had used SET TRANSACTION but it caused some problems (sorry do not remember - something to do with persistent connections) and now use the NOLOCK hint. I am no expert thus the comment instead of a solution.

I'd say use the hints. Imagine if the stored procedure is being called by a program that is using a global transaction object used across the program by several threads (I am not saying that it is a right or wrong way to do, I just saw it happen). The isolation level set in your stored procedure can affect a statement at the other thread.

Also, if your stored procedure calls RAISERROR you may forget to re-set the isolation level back.

I think, as a good practice, you should set the isolation level once on the transaction initialisation for each transaction used in your program
Share this answer
I agreed Andrew Cherednik. just i want to confirm so i posted the question.
Actually where i am working is a large transaction traffic and by using isolation level, sometimes u get data with dirty reads or penthoms. But as far locks are safe.
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900