Results 1 to 3 of 3
  1. #1
    Join Date
    May 2002

    Unanswered: isolation level 0 and unique index usage

    Hello All, Can I get some help please ?

    When queries are run at isolation level 0, I have read that often a unique index on the table is used in the scan. Is this actually correct ?

    Should the unique index not be the most suitable for performance, can I then force another index of my choice in the selection statement? Is this common practice ? Any pros and cons?


  2. #2
    Join Date
    May 2005
    South Africa
    Provided Answers: 1

    isolation level 0 read

    Quote from Performance and Tuning:
    If the table uses allpages locking, a unique index is required to perform an isolation level 0 read, unless the database is read-only. The index is required to restart the scan if an update by another process changes the query's result set by modifying the current row or page. Forcing the query to use a table scan or a non unique index can lead to problems if there is significant update activity on the underlying table, and is not recommended.

  3. #3
    Join Date
    May 2002
    *allpages* locking.
    This appears to be the key.

    Yeah I had read the same from the P and T guide. Thanks for your post.

    Can anyone explain why this unique index requirement and scanning apply to allpages locking schemes only ?

Posting Permissions

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