Compatibility level ?

Are there any downsides to changing it from 80 to 90 for a given database ?Hi Rob
It depends...
Does your database have any objects with names that are now reserved
keywords in compatibility level 90?
EXTERNAL, PIVOT, UNPIVOT, REVERT, TABLESAMPLE
Does any of your code use the *= or =* syntax for outer joins?
Does any of your code update a view that was defined WITH NOCHECK that
includes a TOP?
If your database includes any constructs that is allowed in 80 compatibility
but is not allowed in 90, then you will have problems. Otherwise, you won't.
You can find the full list in the Books Online if you look up
sp_dbcmptlevel.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Rob" <robc1@.yahoo.com> wrote in message
news:HoudnQA8uN6nwNXbnZ2dnUVZ_vGinZ2d@.comcast.com...
> Are there any downsides to changing it from 80 to 90 for a given database
> ?
>|||Hi Kalen,
Correct me if I'm wrong, because this is how I explain compatibility level
when teaching or presenting: the compatibility only tells the query engine
how to interpret TSQL code. Thus, at 80 no new features will work, nor syntax
changes in 90.
I'm not missing something, am I?
"Kalen Delaney" wrote:
> Hi Rob
> It depends...
> Does your database have any objects with names that are now reserved
> keywords in compatibility level 90?
> EXTERNAL, PIVOT, UNPIVOT, REVERT, TABLESAMPLE
> Does any of your code use the *= or =* syntax for outer joins?
> Does any of your code update a view that was defined WITH NOCHECK that
> includes a TOP?
> If your database includes any constructs that is allowed in 80 compatibility
> but is not allowed in 90, then you will have problems. Otherwise, you won't.
> You can find the full list in the Books Online if you look up
> sp_dbcmptlevel.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "Rob" <robc1@.yahoo.com> wrote in message
> news:HoudnQA8uN6nwNXbnZ2dnUVZ_vGinZ2d@.comcast.com...
> > Are there any downsides to changing it from 80 to 90 for a given database
> > ?
> >
>
>|||This is an overgeneralization. Compatibility level is mainly concerned with
interpreting TSQL, but to say NO new features will work is not true at all.
Did you look at the page on sp_dbcmptlevel in BOL?
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"James Luetkehoelter" <JamesLuetkehoelter@.discussions.microsoft.com> wrote
in message news:DE983716-D4D0-440B-AEAB-53C61561B12F@.microsoft.com...
> Hi Kalen,
> Correct me if I'm wrong, because this is how I explain compatibility level
> when teaching or presenting: the compatibility only tells the query engine
> how to interpret TSQL code. Thus, at 80 no new features will work, nor
> syntax
> changes in 90.
> I'm not missing something, am I?
> "Kalen Delaney" wrote:
>> Hi Rob
>> It depends...
>> Does your database have any objects with names that are now reserved
>> keywords in compatibility level 90?
>> EXTERNAL, PIVOT, UNPIVOT, REVERT, TABLESAMPLE
>> Does any of your code use the *= or =* syntax for outer joins?
>> Does any of your code update a view that was defined WITH NOCHECK that
>> includes a TOP?
>> If your database includes any constructs that is allowed in 80
>> compatibility
>> but is not allowed in 90, then you will have problems. Otherwise, you
>> won't.
>> You can find the full list in the Books Online if you look up
>> sp_dbcmptlevel.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://sqlblog.com
>>
>> "Rob" <robc1@.yahoo.com> wrote in message
>> news:HoudnQA8uN6nwNXbnZ2dnUVZ_vGinZ2d@.comcast.com...
>> > Are there any downsides to changing it from 80 to 90 for a given
>> > database
>> > ?
>> >
>>|||Thanks, I'll hone my explanation.
"Kalen Delaney" wrote:
> This is an overgeneralization. Compatibility level is mainly concerned with
> interpreting TSQL, but to say NO new features will work is not true at all.
> Did you look at the page on sp_dbcmptlevel in BOL?
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "James Luetkehoelter" <JamesLuetkehoelter@.discussions.microsoft.com> wrote
> in message news:DE983716-D4D0-440B-AEAB-53C61561B12F@.microsoft.com...
> > Hi Kalen,
> >
> > Correct me if I'm wrong, because this is how I explain compatibility level
> > when teaching or presenting: the compatibility only tells the query engine
> > how to interpret TSQL code. Thus, at 80 no new features will work, nor
> > syntax
> > changes in 90.
> >
> > I'm not missing something, am I?
> >
> > "Kalen Delaney" wrote:
> >
> >> Hi Rob
> >>
> >> It depends...
> >>
> >> Does your database have any objects with names that are now reserved
> >> keywords in compatibility level 90?
> >> EXTERNAL, PIVOT, UNPIVOT, REVERT, TABLESAMPLE
> >>
> >> Does any of your code use the *= or =* syntax for outer joins?
> >>
> >> Does any of your code update a view that was defined WITH NOCHECK that
> >> includes a TOP?
> >>
> >> If your database includes any constructs that is allowed in 80
> >> compatibility
> >> but is not allowed in 90, then you will have problems. Otherwise, you
> >> won't.
> >>
> >> You can find the full list in the Books Online if you look up
> >> sp_dbcmptlevel.
> >> --
> >> HTH
> >> Kalen Delaney, SQL Server MVP
> >> www.InsideSQLServer.com
> >> http://sqlblog.com
> >>
> >>
> >> "Rob" <robc1@.yahoo.com> wrote in message
> >> news:HoudnQA8uN6nwNXbnZ2dnUVZ_vGinZ2d@.comcast.com...
> >> > Are there any downsides to changing it from 80 to 90 for a given
> >> > database
> >> > ?
> >> >
> >>
> >>
> >>
>
>