3 General questions

• BillyWilly

  SSCrazy

  Points: 2304

  More actions

  March 5, 2004 at 7:39 am

  #84524

  Hi-

  I have a few general questions that perhaps a guru can answer.

  1) What the difference between the "Hints"(NOLOCK etc..) and the "SET" statements(Serializeable etc..)?

  2) Is there any reason to explicitly use a Transaction for a SELECT statement?  I use Transactions for Inserts, Updates, and Deletes, but for a Select ??

  3) When Joining to a non-Indexed View - what happens behind the scenes?  Is it a performance disaster?

  Thanks - B

• ray_higdon

  SSCarpal Tunnel

  Points: 4589

  More actions

  March 6, 2004 at 6:30 am

  #497699

  1) NOLOCK is a locking hint you can use to avoid contention in waiting for exclusive locks to finish. NOLOCK will create a non-schema verified lock which means you could be getting dirty data. Your isolation levels can allow you to do a couple different things, BOL is probably the best tool to research these.

  2) NO, as you cannot ROLLBACK or COMMIT a select, no point that I know of to use a tran.

  3) It's like a select statement really, no cached plan but will still utilize existing indexes on underlying tables.

  HTH

  ------------
  
  Ray Higdon MCSE, MCDBA, CCNA

• carmines

  SSC Veteran

  Points: 293

  More actions

  March 8, 2004 at 6:25 am

  #497765

  If you wanted to use a pessimistic locking technique and apply a lock to a row at the time you select it then you would need to do it within a transaction.

  For example:

  SELECT * FROM MyTable WITH (ROWLOCK, UPDLOCK)

  would need to be done within a transaction otherwise the lock would not be maintained.

  Regards

  John

• Prasad Bhogadi

  SSCrazy Eights

  Points: 9235

  More actions

  March 9, 2004 at 9:54 am

  #498004

  What if the above statement is used inside a cursor like

  declare mycur cursor for

  SELECT * FROM MyTable WITH (ROWLOCK, UPDLOCK)

  Do we still need to include it in a transaction for the lock to be maintained?

  Thanks

   

  Prasad Bhogadi
  www.inforaise.com

• carmines

  SSC Veteran

  Points: 293

  More actions

  March 9, 2004 at 1:25 pm

  #498044

  All the testing I have ever done on this tells me that the lock will not be maintained unless it is within a transaction however I have not done too much with cursors.

  In your cursor example I was not entirely sure what would happen so I have tried it out.

  To my surprise I find that the lock is maintained on the latest row that is fetched from the cursor without the need for any explicit transaction.

  Do you already know this or were you asking the question?

  Regards

  John