Purpose: If you are doing SELECTs and data accuracy is not extremely important, use "nolock" to avoid conflicts with other users.
If you have long running processes that do a lot of SELECTs, this can dramatically improve performance.
1: create table testdb.dbo.NoLockTest
2: (3: ID int identity(1,1),
4: Product varchar(20),
5: SalesDate datetime,6: SalesPrice int
7: ) 8: 9: INSERT INTO testdb.dbo.NoLockTest (Product, SalesDate, SalesPrice) VALUES ('PoolTable', GETDATE(), 200)
10: INSERT INTO testdb.dbo.NoLockTest (Product, SalesDate, SalesPrice) VALUES ('MyTable', GETDATE(), 500)
11: INSERT INTO testdb.dbo.NoLockTest (Product, SalesDate, SalesPrice) VALUES ('GeoffTable', GETDATE(), 400)
12: INSERT INTO testdb.dbo.NoLockTest (Product, SalesDate, SalesPrice) VALUES ('LarryTable', GETDATE(), 250)
13: INSERT INTO testdb.dbo.NoLockTest (Product, SalesDate, SalesPrice) VALUES ('StephenTable', GETDATE(), 360)
14: 15: select * from testdb.dbo.NoLockTest
16: 17: -- Query 1
18: BEGIN TRANSACTION
19: INSERT INTO testdb.dbo.NoLockTest (Product, SalesDate, SalesPrice)
20: VALUES ('PoolTable', GETDATE(), 1000)
21: 22: -- Query 2 will continue to run until ROLLBACK TRANSACTION OR COMMITE does not apply to any INSERT/UPDATE/DELETE stmt.
23: -- only way to stop the query is to use "Cancel Executing Query" from SSMS Menu.
24: select count(*) from testdb.dbo.NoLockTest
25: 26: -- Query 3 will allow you to pull data even though there is a lock on the table due to INSERT/UPDATE/DELETE stmt running
27: -- in this case we have from Query 1
28: select count(*) from testdb.dbo.NoLockTest with(nolock)
29: 30: -- Query 4 -- execute below statement.
31: ROLLBACK TRANSACTION
32: 33: -- Query 5
34: select count(*) from testdb.dbo.NoLockTest
