Setting Locking Granularity for All Statements in a Stored Procedure - Microsoft SQL Server 2000

Question

I'm developing an ETL application with batch processing. There is low (i.e. no) concurrency for updates. I'd like to avoid the overhead of granular locks and lock escalation by merely locking the entire table.

I'd like to avoid having to specify TABLOCK in every statement. Is there any way to set the locking granularity at the top of a stored procedure such that every statement automatically gets table locks on every table used? Shared or exclusive doesn't matter, though shared is preferred; the ETL will run overnight with no adhoc query load and prior to a batch of reports triggered when the ETL is complete.

Thanks!

Answer 1

You will need to take a look at Transaction Isolation Levels

To be honest though, I can't see why you need to be doing anything. I would have thought SQL Server would do a good enough job of locking by itself.