0

From time to time, I want to run a stored procedure to get a rough estimate of how many records in two or three different tables satisfy some criteria. If during this estimate new records are added, deleted or updated, there is not really a problem (I just want a rough estimate). That being, I can afford for this process using SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED. However, I have two questions about this:

1) Since I am only using SELECT COUNT(*) instructions, do I really need to wrap these statement in a BEGIN/COMMIT TRANSACTION block?

2) Do I need to SET TRANSACTION ISOLATION LEVEL READ COMMITTED back in the end of the stored procedure, or will this be automatically set once its execution ends?

B.M
  • 533
  • 2
  • 8
  • 16

2 Answers2

0

  1. No. Reads don't need to be in a transaction

  2. The SET is scoped only for the stored procedure. See my answer here: Is it okay if from within one stored procedure I call another one that sets a lower transaction isolation level?. However, you'd use the NOLOCK hint rather then SET: SELECT COUNT(*) FROM myTable WITH (NOLOCK).

If you want an approximate count without WHERE filters, then use sys.dm_db_partition_stats. See my answer here: Fastest way to count exact number of rows in a very large table?

Community
  • 1
  • 1
gbn
  • 422,506
  • 82
  • 585
  • 676
0

1) No. It uses implicit transaction for each statement if you do not specify the transaction scope. You do not have to put explicit transaction scope for making 'set transaction isolation level to work'.

2) You don't have to reset it to original. It will be taken care by SQL Server. Please refer to this SO entry: Transaction Isolation Level Scopes

Community
  • 1
  • 1
Anand Patel
  • 6,031
  • 11
  • 48
  • 67