Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2011
    Posts
    6

    Unanswered: how to configure snapshot isolation for selects?

    I'm implementing a UDF that selects from a database table.

    I want this select to be as fast as possible (no locking or blocking) so snapshot isolation appears to be the way to go for this.

    I know I can use the following command to set the snapshot transaction isolation level:

    SET TRANSACTION ISOLATION LEVEL SNAPSHOT

    If I put this command before a select statement in my UDF will this implement the transaction isolation level for the subsequent select as-is?

    Or do I need to have some additional wrapper around the select? For example for multi-table DML I would wrap the transaction in BEGIN TRANSACTION at the beginning and COMMIT TRANSACTION at the end.

    However, what I'm doing is just a select statement so no need to commit anything. Or does SQL Server require some kind of wrapper around any SQL that leverages a transaction?

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    It will set this isolation level for the entire session. You do not have to explicitly work with transaction blocks, but you can.

    This is another good read.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Aug 2008
    Posts
    147
    SET TRANSACTION ISOLATION LEVEL cannot be executed in a UDF , but can be applied with the use of TABLE HINTS.A UDF can’t execute the SET TRANSACTION ISOLATION LEVEL. The alternative for a UDF is a table hint. The table hint overrides the optimizer choice for the duration of the DML. An example of a table hint is :

    USE MyDB
    GO
    SELECT col1,col2 FROM myTable WITH (TABLOCK)
    WHERE col1 = ‘a_value’
    GO
    ---------------------------------
    www.sqlserver-dba.com
    www.dba-db2.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •