changing compatability level from80(2000) to 90(2005)

Hi

I am planning to migrate my database from sql 2000 to sql 2005

I need to know when i change the compatibility from 80 to 90

1) What are the things which i need to look out for. If i have changed every thing as per upgrade advisor will i have to make or anticipate further changes.

2) Please tell me whether if i dont use 90 compatability level will i miss out anything?

What am i missing out ?

Will it be possible to do online index re-build with compatability level set to 80.

Is there a list of new benefits/changes i will be getting when i move to sql 2005 compatability set to 90.

Please do reply .Awaiting your answers,....

Thanks in advance.

The differences are all here (table lists them all about half way down the page) - http://msdn2.microsoft.com/en-us/library/ms178653.aspx
Many other things to watch for are here - http://msdn2.microsoft.com/en-us/library/ms143179.aspx

Online indexing operations are supported in 80 compatibility (on SQL Server 2005 Enterprise as that is the only version that supports them).

|||

Thank you for your reply

I want to know whether i wont be able to use any feature of Management studio or any other feature if i am not changing to compatability level 90

Thanks in advance

|||

Couple that I can think of

You can't use Database Diagrams

You can't use custom reports (on the summary tab) - http://sqlblogcasts.com/blogs/sqldbatips/archive/2006/11/22/ssms-custom-reports-backwards-compatibility-or-lack-thereof.aspx

Changing a 2005 db from 8.0 compatability to 9.0

I've inherited a 2005 database that is in 8.0 compatability mode. I'd like t
o
move it to 9.0 so we can do some CLR stuff. However, the upgrade advisor
won't analyze that version of SQL Server. (2005 sp1). I've looked in BOL and
I think the main thing I need to look for are the old outer join operators
(*= and =*). I've reviewed the list here:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/d686cdf0-d11d-4dba-9ec8-
de1a5f189f25.htm
and I think the join operators are the only thing that would affect me. Does
anyone have any other common issues going from 8.0 to 9.0 that I might be
missing from that list?
Thanks in advance.Also look for TOP 100 PERCENT ... ORDER BY in views. It may already be
biting you without knowing it ;-). Other than new keywords and the
deletion of the "evil join" operators, I can't think of anything else
that's common that you'd run into.
sqlboy2000 wrote:
> I've inherited a 2005 database that is in 8.0 compatability mode. I'd like
to
> move it to 9.0 so we can do some CLR stuff. However, the upgrade advisor
> won't analyze that version of SQL Server. (2005 sp1). I've looked in BOL a
nd
> I think the main thing I need to look for are the old outer join operators
> (*= and =*). I've reviewed the list here:
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/d686cdf0-d11d-4dba-9ec
8-de1a5f189f25.htm
> and I think the join operators are the only thing that would affect me. Do
es
> anyone have any other common issues going from 8.0 to 9.0 that I might be
> missing from that list?
> Thanks in advance.|||We do have a few of those stupid TOP 100 percent views.
Thanks for the heads up.
"David Markle" wrote:

> Also look for TOP 100 PERCENT ... ORDER BY in views. It may already be
> biting you without knowing it ;-). Other than new keywords and the
> deletion of the "evil join" operators, I can't think of anything else
> that's common that you'd run into.
> sqlboy2000 wrote:
>

Changing a 2005 db from 8.0 compatability to 9.0

I've inherited a 2005 database that is in 8.0 compatability mode. I'd like to
move it to 9.0 so we can do some CLR stuff. However, the upgrade advisor
won't analyze that version of SQL Server. (2005 sp1). I've looked in BOL and
I think the main thing I need to look for are the old outer join operators
(*= and =*). I've reviewed the list here:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/d686cdf0-d11d-4dba-9ec8-de1a5f189f25.htm
and I think the join operators are the only thing that would affect me. Does
anyone have any other common issues going from 8.0 to 9.0 that I might be
missing from that list?
Thanks in advance.Also look for TOP 100 PERCENT ... ORDER BY in views. It may already be
biting you without knowing it ;-). Other than new keywords and the
deletion of the "evil join" operators, I can't think of anything else
that's common that you'd run into.
sqlboy2000 wrote:
> I've inherited a 2005 database that is in 8.0 compatability mode. I'd like to
> move it to 9.0 so we can do some CLR stuff. However, the upgrade advisor
> won't analyze that version of SQL Server. (2005 sp1). I've looked in BOL and
> I think the main thing I need to look for are the old outer join operators
> (*= and =*). I've reviewed the list here:
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/d686cdf0-d11d-4dba-9ec8-de1a5f189f25.htm
> and I think the join operators are the only thing that would affect me. Does
> anyone have any other common issues going from 8.0 to 9.0 that I might be
> missing from that list?
> Thanks in advance.|||We do have a few of those stupid TOP 100 percent views.
Thanks for the heads up.
"David Markle" wrote:
> Also look for TOP 100 PERCENT ... ORDER BY in views. It may already be
> biting you without knowing it ;-). Other than new keywords and the
> deletion of the "evil join" operators, I can't think of anything else
> that's common that you'd run into.
> sqlboy2000 wrote:
> > I've inherited a 2005 database that is in 8.0 compatability mode. I'd like to
> > move it to 9.0 so we can do some CLR stuff. However, the upgrade advisor
> > won't analyze that version of SQL Server. (2005 sp1). I've looked in BOL and
> > I think the main thing I need to look for are the old outer join operators
> > (*= and =*). I've reviewed the list here:
> > ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/d686cdf0-d11d-4dba-9ec8-de1a5f189f25.htm
> > and I think the join operators are the only thing that would affect me. Does
> > anyone have any other common issues going from 8.0 to 9.0 that I might be
> > missing from that list?
> >
> > Thanks in advance.
>