APPLY on compatibility level 80!!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

APPLY on compatibility level 80!!

Author

Topic

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-11 : 15:14:11

I'm seeing a strange behaviour on one of our servers here. Its a SQL 2008 instance set at a compatibility level of 80 due to some legacy code and I'm seeing queries using new features like APPLY working fine without any issues

see an example below


..
EXEC sp_dbcmptlevel 'dbname'
GO
SELECT COUNT(*) AS Cnt
FROM [Import_Staging] stg
CROSS APPLY(
SELECT Event_category FROM 
 CAT_Mapping m
WHERE  ',' + stg.event_category + ',' LIKE '%,' + m.HMA_Event_Category  + ',%')t
...


output
-----------------------------
The current compatibility level is 80.

Cnt
-----------
130

(1 row(s) affected)

I checked BOL and this is what it says for APPLY operator

SQL Server 2008 Other Versions SQL Server 2008 R2 SQL Server 2005
6 out of 20 rated this helpful - Rate this topic
The APPLY operator allows you to invoke a table-valued function for each row returned by an outer table expression of a query. The table-valued function acts as the right input and the outer table expression acts as the left input. The right input is evaluated for each row from the left input and the rows produced are combined for the final output. The list of columns produced by the APPLY operator is the set of columns in the left input followed by the list of columns returned by the right input.

Note
To use APPLY, the database compatibility level must be at least 90.

I cant understand how the above code is working though!
Am I missing something obvious here?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2012-10-11 : 17:00:46

I recall things like this happened to me, and SQL Server 2008/2008R2 documentation hints about that. see section SET Options in ALTER DATABASE: ([url]http://technet.microsoft.com/en-us/library/bb510680%28SQL.100%29.aspx[/url])
But as of 2012 ([url]http://technet.microsoft.com/en-us/library/bb510680(v=sql.110).aspx[/url]) that sections was removed. I am not sure if that was specific to 2008/2008R2 or not. But I have learned the lesson that compatibility levels is more of aid. So use it that way.

For us, there is only the trying. The rest is not our business. ~T.S. Eliot

Muhammad Al Pasha

AaronBertrand
Starting Member

1 Post

Posted - 2012-10-11 : 20:18:49

Books Online is slightly misleading. Compat level does not need to be 90 to use APPLY. It just needs to be 90 to use APPLY in certain constructs. For example the derived table you're using is fine in 80 or 90. Try now with a function, e.g.:

SELECT * FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.plan_handle) AS t;

That will fail with a syntax error in 80, but work fine in 90. This is "strange" only because the wording in Books Online is not entirely accurate. I would suggest focusing on getting rid of your legacy code and finally letting go of this ancient compat level instead of focusing on why your code works. Usually that's a good thing.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-12 : 12:48:31

quote:
Originally posted by AaronBertrand

Books Online is slightly misleading. Compat level does not need to be 90 to use APPLY. It just needs to be 90 to use APPLY in certain constructs. For example the derived table you're using is fine in 80 or 90. Try now with a function, e.g.:

SELECT * FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.plan_handle) AS t;

That will fail with a syntax error in 80, but work fine in 90. This is "strange" only because the wording in Books Online is not entirely accurate. I would suggest focusing on getting rid of your legacy code and finally letting go of this ancient compat level instead of focusing on why your code works. Usually that's a good thing.

Yep you were right
It throws error on suggested usage

I also gave the same suggestion to them. But they're not ready to take it because this is an inherited system and its having dbs supporting couple of legacy apps. So they're telling they cant invest time for doing testing and changing compat level etc. So I guess we've to live with this for some more time

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources