Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
RCSI for a single query

Answered RCSI for a single query

  • Wednesday, February 20, 2013 11:17 AM
     
     
    Sign In to Vote
    0

    Hi.

    Can I set Read Committed Snapshot Isolation for a specific query?

    In order to run query like Read uncommitted, we can use "with (nolock)". Is there something similar to RCSI?

    Thanks.

    itaigitt, http://copypastenet.blogspot.com

     

All Replies

  • Wednesday, February 20, 2013 11:27 AM
     
      Has Code
    Sign In to Vote
    1

    Hi yes - but your database has to be enabled for Snapshot Isolation first.

    use databasename
go
select snapshot_isolation_state from sys.databases
where database_id = db_id()

    set

    transaction isolation level snapshot

     
  • Wednesday, February 20, 2013 11:30 AM
     
     
    Sign In to Vote
    0

    And how should I write the query?

    Select col1, col2 from MyTable ....?

    itaigitt, http://copypastenet.blogspot.com

     
  • Wednesday, February 20, 2013 11:37 AM
     
     Answered Has Code
    Sign In to Vote
    1

    Hi

    as normal

    just at the beginning of your query put:

    set transaction isolation level snapshot
go
select .... bla bla bla...

    SQL will pull the latest value form tempdb if the rows is exclusively locked in the queries table.



     
  • Wednesday, February 20, 2013 11:45 AM
     
     
    Sign In to Vote
    0
    But this "set" will be relevant for the query or for the whole DB?

    itaigitt, http://copypastenet.blogspot.com

     
  • Wednesday, February 20, 2013 11:53 AM
     
     Answered
    Sign In to Vote
    1

    It remains set for the duration of your connection - you can set it back to read uncommitted at the end of the query.

    "

    Only one of the isolation level options can be set at a time, and it remains set for that connection until it is explicitly changed. All read operations performed within the transaction operate under the rules for the specified isolation level unless a table hint in the FROM clause of a statement specifies different locking or versioning behavior for a table.

    " --quoted from http://msdn.microsoft.com/en-gb/library/ms173763.aspx

     
  • Wednesday, February 20, 2013 12:04 PM
     
     
    Sign In to Vote
    0

    OK, I've got it.

    Another similar question: How can I create a view with transaction isolation level snapshot?

    itaigitt, http://copypastenet.blogspot.com