Menu
So I've got a SQL statement that pared down looks something like this:
SELECT column
FROM table t (nolock)
LEFT OUTER JOIN table2 (nolock) t2 on t.id = t2.id
This statement works on my SQL 2005 and SQL 2008 environments. It does not on a remote SQL 2005 environment. I've switched the last line to:
LEFT OUTER JOIN table2 t2 (nolock) on t.id = t2.id
This works in the remote environment.
Putting aside issues of whether (nolock) is appropriate and that the syntax should remain internally consistent, any ideas why this happens? I attempted to search for hotfixes/KBs that dealt with this and came up with nothing. Is there a setting on SQL server that could cause this behavior?
349
Sql server nolock syntax is easy and straightforward, you just need to write WITH (NOLOCK) after the table name. If you use the alias of the table you need to write the nolock hint after the alias.
The WITH (NOLOCK) table hint is used to override the default transaction isolation level of the table or the tables within the view in a specific query, by allowing the user to retrieve the data without being affected by the locks, on the requested data, due to another process that is changing it.
Thus, we can say that Nolock reads “Dirty Data” when applied with only Select statement in SQL Server Database. While With (Nolock)do not issue any shared locks and exclusive locks. It is possible with With (Nolock) that, it can read an uncommitted transaction, which can be rolled back at the middle of a read.
The NOLOCK hint has been deprecated in favor of READ COMMITTED SNAPSHOT (RCSI). Starting with SQL Server 2022, these hints will no longer be honored, and the transaction will operate under default isolation level (RCSI, if enabled, or READ COMMITTED if not).
Check your database compatibility level.
It should be 90 for this syntax to work.
Just checked:
sp_dbcmptlevel 'test', 80
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
SELECT TOP 100 *
FROM master t (nolock)
LEFT OUTER JOIN master (nolock) t2 on t.id = t2.id
Сообщение 102, уровень 15, состояние 1, строка 3
Incorrect syntax near 't2'.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
