Select From a table that is constantly being inserted into

Question

How would I go about grabbing data from a table that is CONSTANTLY being inserted into (and needs to be) without causing any locking so that the inserts will continue unheeded.

I've looked around and found select with nolock option but, if I'm understanding correctly, this does not stop the lock creation, rather goes around current locks and grabs everything?

Thanks.

EDIT: This table will never be UPDATED, only INSERTS and SELECTS

Answer 1

As long as you don't mind getting dirty reads from your table this shouldn't be a problem for you. Make sure that the translation isolation level is set appropriately and that your calling code (if applicable) isn't using implicit transactions and you should be fine.

Microsoft's Transaction Isolation Docs: http://msdn.microsoft.com/en-us/library/ms173763.aspx

NOLOCK is a common, and in my opinion, abused option when running into situations like this. Although it can help you overcome problems in high contention situations it can also cause difficult to track down bugs. Although this is something of an ongoing argument check out http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx for an idea of some of the risks with using hints like this.