SET and SELECT with SQL 2005 and SQL 2000

Came across an interesting bug reported on the feedback centre. Basically SQL2000 allows the following syntax


where as SQL 2005 doesn't.

There are quite a few of these types of bugs/design feature that have popped up, this is generally where the design teams have corrected a bug in SQL 2000, the bug was ignored in SQL 2000 but when you try and do it in SQL 2005 with the bug fixed your problem comes to light.

Another is conflicting table hints in SQL 2000 this was valid


SET myCol = newvalue

FROM mytable (NOLOCK)

JOIN myothertable (NOLOCK) on mytable.col2 = myothertable.col1

In SQL 2000 the table hint in the FROM clause on mytable is ignored (in this scenario) but in SQL 2005 it isn't thus the conflicting table hints.

There has been lots of "sorting the ship out" and tighting up. Especially around security and impersonation. This is a good thing in my view

Published Sunday, February 26, 2006 2:45 PM by simonsabin


No Comments