Do not lock the whole table on select statement I have a table that is constantly updated by some scripts. The scripts are adding new values and changing tuples but never deleting any tuple.

I need to run some queries, that use all rows in the table (about 2 million at the moment) for joining. The Problem is that the scripts lock the table in a way, that my select statements are blocked and are executed very slowly (compare 16minutes vs a few seconds if running without any script in the background).

Is it possible to lock the table in a way, that i read a current snapshot and do not care about changes? For most applications it is sufficient to allow that ghost entries are present (data is changed/added while the select query was running). It is not necassary in the most cases to lock the whole table, but rather use a column lock (which i think will not work on my MS SQL Server 2005)
Sounds like you want to consider READ COMMITTED SNAPSHOT ISOLATION, which uses tempdb to provide a point-in-time version of the data and prevents writers from blocking readers and vice-versa (as long as it's ok that readers do not see the most current version of the row). With a table that large and having this much of an impact by concurrent write operations, the impact to tempdb could be huge, and there's no way for us to predict it, so you will need to test it.

Kendra Little has a great intro post from the administration side here:


Implementing Snapshot or Read Committed Snapshot Isolation in SQL Server: A Guide


And Paul White goes into detail about what happens at the query level here, in his series on ACID and the various transaction isolation levels in SQL Server:


Read Committed Snapshot Isolation


Official documentation which you'll want to review, especially to understand how it works and the limitations involved:


Understanding Row Versioning-based Isolation Levels
Choosing Row Versioning-based Isolation Levels
Enabling Row Versioning-based Isolation Levels
Using Row Versioning-based Isolation Levels