Order of (nolock) and table aliases on SQL Server

Question

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?

Answer 1

I am not sure but maybe it is caused by compatibility level?

Since SQL 2005 by default correct syntax of hint is WITH (NOLOCK) and maybe this is the reason.

Answer 2

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'.