Implementing [NOLOCK]

Is there a way to accomplish this? I remember something you said in the training class but I couldn’t recall. I appreciate your response on this.

Per SQL server documentation, SQL Server operates with READCOMMITTED as the default isolation level, and thus for selects default is LOCK. The statement you have provided applies to SQL Server 2005 Compact edition and is different for SQL Server 2005.

Here is the documentation note from SQL Server books online: (http://msdn2.microsoft.com/en-us/library/ms171885.aspx)

Although the default isolation level in SQL Server Compact Edition is Read Committed, using this isolation level does not result in S locks being taken when data is read. This behavior is unlike Microsoft SQL Server. In SQL Server, when using Read Committed (the default is ‘READCOMMITED’), an S lock is requested whenever a row is read, and this will wait if there is a conflicting lock on that row. SQL Server Compact Edition does not require an S lock because versions of data pages are automatically maintained to ensure that committed data can be read without the need to take a lock. This is important because, in SQL Server Compact Edition, SELECT operations do not need to take any locks on the data and almost always succeed. SELECT operations will not wait if some data has an X lock, as opposed to SQL Server. The SELECT operation still requires a Sch-S lock. The operation will fail only if the table is being modified, because a conflicting Sch-X lock will exist.

So, with the above statement, NOLOCK is not the default behavior/setting for SQL SERVER 2005 and that needs to be set manually. Earlier we used to convert RdbQuery to PassThruRdbQuery and replace the string to set NOLOCK (for e.g., pPassthruRdbQuery.SqlSelect = pPassthruRdbQuery.SqlSelect.Replace("from \"dbo\".\"<TABLE_NAME>\"", "from \"dbo\".\"<TABLE_NAME>\" (NOLOCK)");)

Correct me if my analysis is incorrect or I miss something.