SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 APPLY on compatibility level 80!!
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/11/2012 :  15:14:11  Show Profile  Reply with Quote
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

Saudi Arabia
264 Posts

Posted - 10/11/2012 :  17:00:46  Show Profile  Reply with Quote
I recall things like this happened to me, and SQL Server 2008/2008R2 documentation hints about that. see section SET Options in ALTER DATABASE: (http://technet.microsoft.com/en-us/library/bb510680%28SQL.100%29.aspx)
But as of 2012 (http://technet.microsoft.com/en-us/library/bb510680 v=sql.110 .aspx) 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
Go to Top of Page

AaronBertrand
Starting Member

USA
1 Posts

Posted - 10/11/2012 :  20:18:49  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/12/2012 :  12:48:31  Show Profile  Reply with Quote
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/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000