June 21, 2011 at 6:28 am
Ninja's_RGR'us (6/21/2011)
HowardW (6/21/2011)
LutzM (6/21/2011)
There might be a confusion between indexed views and "ordered" views.In SS2K it's possible to control the order of the rows returned via ORDER BY in the view definition.
SS2K8 will simply ignore it. As far as I remember, the upgrade advisor didn't catch it.
Whilst Indexed Views are obviously supported in SQL Server 2008, they cannot be utilised by queries in Standard Edition without code changes (the NOEXPAND hint). Only Enterprise edition will consider using an indexed view to satisfy a query without using NOEXPAND.
WHY???????
?????? PROFIT?
Have a look at the editions comparison:
http://www.microsoft.com/sqlserver/2008/en/us/editions-compare.aspx
Indexed views are clearly marked as an Enterprise Edition feature only.
It's easy to get caught out as it will happily create an indexed view and maintain it as normal. You can also select the view name without error, however it's just expanding the definition, not utilising the indexed view. The only way to use the indexed view in other editions is to add the NOEXPAND hint.
In Enterprise Edition, even if you don't reference the view name in the query, the optimiser can decide to use it.
June 21, 2011 at 7:02 am
Link to the specific part of the documentation that describes the behaviour:
http://msdn.microsoft.com/en-us/library/ms181151.aspx
Indexed views can be created in any edition of SQL Server. In SQL Server Enterprise, the query optimizer automatically considers the indexed view. To use an indexed view in all other editions, the NOEXPAND table hint must be used.
June 21, 2011 at 7:03 am
Sumanta Roy (6/21/2011)
Hi grant,As you said ANSI 89 outer joins are no longer supported in 2008, I have one doubt. If we change the database compatiblity level to SQL server 2000, will it then support ANSI 89 outer join?
I honestly don't recall off the top of my head. Regardless, I advocate against using the compatibility mode. Make the move off of 2000 or stay there. Don't move to 2008 and sacrifice most of the functionality.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 21, 2011 at 7:08 am
Grant Fritchey (6/21/2011)
Sumanta Roy (6/21/2011)
Hi grant,As you said ANSI 89 outer joins are no longer supported in 2008, I have one doubt. If we change the database compatiblity level to SQL server 2000, will it then support ANSI 89 outer join?
I honestly don't recall off the top of my head. Regardless, I advocate against using the compatibility mode. Make the move off of 2000 or stay there. Don't move to 2008 and sacrifice most of the functionality.
Yes, they will be supported in 80 mode. Support is completely dropped from the product in Denali because 80 mode is being dropped.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 21, 2011 at 8:20 am
Sumanta Roy (6/21/2011)
Hi grant,As you said ANSI 89 outer joins are no longer supported in 2008, I have one doubt. If we change the database compatiblity level to SQL server 2000, will it then support ANSI 89 outer join?
No.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 21, 2011 at 8:24 am
opc.three (6/21/2011)
Grant Fritchey (6/21/2011)
Sumanta Roy (6/21/2011)
Hi grant,As you said ANSI 89 outer joins are no longer supported in 2008, I have one doubt. If we change the database compatiblity level to SQL server 2000, will it then support ANSI 89 outer join?
I honestly don't recall off the top of my head. Regardless, I advocate against using the compatibility mode. Make the move off of 2000 or stay there. Don't move to 2008 and sacrifice most of the functionality.
Yes, they will be supported in 80 mode. Support is completely dropped from the product in Denali because 80 mode is being dropped.
Are you sure? I have run into syntax errors with DBs in compat 80 and ANSI 89 Outer joins (on SQL 2005 and 2008).
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 21, 2011 at 8:26 am
SQLRNNR (6/21/2011)
Sumanta Roy (6/21/2011)
Hi grant,As you said ANSI 89 outer joins are no longer supported in 2008, I have one doubt. If we change the database compatiblity level to SQL server 2000, will it then support ANSI 89 outer join?
No.
Ummmm.....yes, *= is supported on 2008 when the DB is in 80 compat mode.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 21, 2011 at 8:27 am
SQLRNNR (6/21/2011)
opc.three (6/21/2011)
Grant Fritchey (6/21/2011)
Sumanta Roy (6/21/2011)
Hi grant,As you said ANSI 89 outer joins are no longer supported in 2008, I have one doubt. If we change the database compatiblity level to SQL server 2000, will it then support ANSI 89 outer join?
I honestly don't recall off the top of my head. Regardless, I advocate against using the compatibility mode. Make the move off of 2000 or stay there. Don't move to 2008 and sacrifice most of the functionality.
Yes, they will be supported in 80 mode. Support is completely dropped from the product in Denali because 80 mode is being dropped.
Are you sure? I have run into syntax errors with DBs in compat 80 and ANSI 89 Outer joins (on SQL 2005 and 2008).
I've just double checked this myself after your last post. Definitely works. At least in 2005 and 2008. Haven't got an R2 instance to try - this is *= etc.
Gives the following error under 90/100:
Msg 4147, Level 15, State 1, Line 2
The query uses non-ANSI outer join operators ("*=" or "=*"). To run this query without modification, please set the compatibility level for current database to 80, using the SET COMPATIBILITY_LEVEL option of ALTER DATABASE. It is strongly recommended to rewrite the query using ANSI outer join operators (LEFT OUTER JOIN, RIGHT OUTER JOIN). In the future versions of SQL Server, non-ANSI join operators will not be supported even in backward-compatibility modes.
And suceeds in 80 Compat mode
June 21, 2011 at 8:29 am
HowardW (6/21/2011)
SQLRNNR (6/21/2011)
opc.three (6/21/2011)
Grant Fritchey (6/21/2011)
Sumanta Roy (6/21/2011)
Hi grant,As you said ANSI 89 outer joins are no longer supported in 2008, I have one doubt. If we change the database compatiblity level to SQL server 2000, will it then support ANSI 89 outer join?
I honestly don't recall off the top of my head. Regardless, I advocate against using the compatibility mode. Make the move off of 2000 or stay there. Don't move to 2008 and sacrifice most of the functionality.
Yes, they will be supported in 80 mode. Support is completely dropped from the product in Denali because 80 mode is being dropped.
Are you sure? I have run into syntax errors with DBs in compat 80 and ANSI 89 Outer joins (on SQL 2005 and 2008).
I've just double checked this myself after your last post. Definitely works. At least in 2005 and 2008. Haven't got an R2 instance to try - this is *= etc.
Gives the following error under 90/100:
Msg 4147, Level 15, State 1, Line 2
The query uses non-ANSI outer join operators ("*=" or "=*"). To run this query without modification, please set the compatibility level for current database to 80, using the SET COMPATIBILITY_LEVEL option of ALTER DATABASE. It is strongly recommended to rewrite the query using ANSI outer join operators (LEFT OUTER JOIN, RIGHT OUTER JOIN). In the future versions of SQL Server, non-ANSI join operators will not be supported even in backward-compatibility modes.
And suceeds in 80 Compat mode
Ok then. I stand corrected.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 21, 2011 at 10:27 am
Ultimately, you need to test. I tend to agree with Grant that you don't want to live in 80 compat mode, but I might do it in the short term (0-6 mos) if you can allocate some time to clean up the code.
There are code changes, and some identifiers that you use in SQL 2000 might be keywords in 2008, and there also are some functionality differences.
If you can, I'd recommend you get an extensive trace of all activity on the 2000 edition, and replay that against an upgraded database (can be dev edition) that is in 10 capt mode on 2008. See what errors out. That will help you to understand what might need to change.
The upgrade advisor will also help.
One more thing, if you have DTS packages, be careful. That support will go away, so you might consider rewriting them in SSIS.
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply