Disadvantages of running 80 compatibility on 2005?

I have a hosted solution where the web host provider tells me I'm running on a 2005 platform, but when I check the DB options, it shows compatibility level 80. The provider tells me I'm not losing any performance by running in the lower compatibility mode. Is this truly the case? What else am I losing out on in terms of features or capabilities?

when you have ealier version compatibility , you will have different behaviour for same command .

eg

create table #tt (tablesample int)

run the above mentioned command in both Comp - 80 and comp-90 database and see the behaviour change

Otherewise i don't think there is any performance degradation using lower compatibility level.

Refer sp_dbcmptlevel (Transact-SQL) in BOL it has very deatiled description about it

Madhu

|||

I am aware of the difference in command behavior, I'm not too concerned about that ...

thanks for the note on performance, any chance you have any references I can check out? I'm not in a position to benchmark between 2000 and 20005 myself right now. what I was looking for, in addition to the performance issue, was whether or not I lose features like Service Broker and other 2005-specific stuff if I'm running in 80 ...

|||Yes, FEATURES MAY BE LOST, Service Broker in particular requires a 90 compat level....however I think the ultimate answer is it depends on the feature.|||The answer depends on the feature. Yes, running in 80 compatibility mode, you can pretty much expect to not be able to utilize any of the features brand new to SQL Server 2005. Just about all of them that I've come across require 90 compatibility mode. Setting the compatibility mode is really designed to allow a short term bridge between versions for things that need to be changed. It isn't intended to be running in that mode for an extended period of time.|||Thanks all for the replies.

Disable TOP

Dear all,
It's possible disable TOP operator?The only way that i have found is set
the compatibility to 6.5 but......
Thanks!!!!!!
That's the only way. BTW, why would you want to do this?
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Stefano_PITECO" <Stefano_PITECO@.discussions.microsoft.com> wrote in message
news:FB886D3B-8E16-4D60-AC85-A16974A63EB4@.microsoft.com...
Dear all,
It's possible disable TOP operator?The only way that i have found is set
the compatibility to 6.5 but......
Thanks!!!!!!
|||Stefano_PITECO wrote:
> Dear all,
> It's possible disable TOP operator?The only way that i have found is set
> the compatibility to 6.5 but......
> Thanks!!!!!!
You could try using SET FIPS_FLAGGER. See Books Online for details.
This will generate warnings for a lot of other stuff too.
David Portas
SQL Server MVP
|||Strange requirement :-D No that not posssible...
Why do you wanna do this ?
HTH, Jens Suessmeyer.
|||Top is one of the most important field of my database....
"Tom Moreau" wrote:

> That's the only way. BTW, why would you want to do this?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Stefano_PITECO" <Stefano_PITECO@.discussions.microsoft.com> wrote in message
> news:FB886D3B-8E16-4D60-AC85-A16974A63EB4@.microsoft.com...
> Dear all,
> It's possible disable TOP operator?The only way that i have found is set
> the compatibility to 6.5 but......
> Thanks!!!!!!
>
|||So what you're saying is that TOP is a column name? Ouch. It'll mean that
you'll have to change all references to use [TOP] - or you'll have to change
the column name and then change all references to use that.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Stefano_PITECO" <Stefano_PITECO@.discussions.microsoft.com> wrote in message
news:9B742642-294F-401C-87A0-5654F4C261F0@.microsoft.com...
Top is one of the most important field of my database....
"Tom Moreau" wrote:

> That's the only way. BTW, why would you want to do this?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Stefano_PITECO" <Stefano_PITECO@.discussions.microsoft.com> wrote in
> message
> news:FB886D3B-8E16-4D60-AC85-A16974A63EB4@.microsoft.com...
> Dear all,
> It's possible disable TOP operator?The only way that i have found is set
> the compatibility to 6.5 but......
> Thanks!!!!!!
>
|||Stefano_PITECO wrote:
> Top is one of the most important field of my database....
>
This is on reason why column naming standards are important. Avoid
using reserved words as column names. Either change the name of the
column or make sure you always delimit TOP with square brackets.
David Portas
SQL Server MVP
|||Try using square brackets
SELECT TOP 10
[top]
, [ColumnB]
FROM
SomeTable
Should/might work.
Adrian
David Portas wrote:
> Stefano_PITECO wrote:
>
> This is on reason why column naming standards are important. Avoid
> using reserved words as column names. Either change the name of the
> column or make sure you always delimit TOP with square brackets.
>

Disable TOP

Dear all,
It's possible disable TOP operator?The only way that i have found is set
the compatibility to 6.5 but......
Thanks!!!!!!That's the only way. BTW, why would you want to do this?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Stefano_PITECO" <Stefano_PITECO@.discussions.microsoft.com> wrote in message
news:FB886D3B-8E16-4D60-AC85-A16974A63EB4@.microsoft.com...
Dear all,
It's possible disable TOP operator?The only way that i have found is set
the compatibility to 6.5 but......
Thanks!!!!!!|||Stefano_PITECO wrote:
> Dear all,
> It's possible disable TOP operator?The only way that i have found is set
> the compatibility to 6.5 but......
> Thanks!!!!!!
You could try using SET FIPS_FLAGGER. See Books Online for details.
This will generate warnings for a lot of other stuff too.
David Portas
SQL Server MVP
--|||Strange requirement :-D No that not posssible...
Why do you wanna do this ?
HTH, Jens Suessmeyer.|||Top is one of the most important field of my database....
"Tom Moreau" wrote:

> That's the only way. BTW, why would you want to do this?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Stefano_PITECO" <Stefano_PITECO@.discussions.microsoft.com> wrote in messa
ge
> news:FB886D3B-8E16-4D60-AC85-A16974A63EB4@.microsoft.com...
> Dear all,
> It's possible disable TOP operator?The only way that i have found is set
> the compatibility to 6.5 but......
> Thanks!!!!!!
>|||So what you're saying is that TOP is a column name? Ouch. It'll mean that
you'll have to change all references to use [TOP] - or you'll have to ch
ange
the column name and then change all references to use that.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Stefano_PITECO" <Stefano_PITECO@.discussions.microsoft.com> wrote in message
news:9B742642-294F-401C-87A0-5654F4C261F0@.microsoft.com...
Top is one of the most important field of my database....
"Tom Moreau" wrote:

> That's the only way. BTW, why would you want to do this?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Stefano_PITECO" <Stefano_PITECO@.discussions.microsoft.com> wrote in
> message
> news:FB886D3B-8E16-4D60-AC85-A16974A63EB4@.microsoft.com...
> Dear all,
> It's possible disable TOP operator?The only way that i have found is set
> the compatibility to 6.5 but......
> Thanks!!!!!!
>|||Stefano_PITECO wrote:
> Top is one of the most important field of my database....
>
This is on reason why column naming standards are important. Avoid
using reserved words as column names. Either change the name of the
column or make sure you always delimit TOP with square brackets.
David Portas
SQL Server MVP
--|||Try using square brackets
SELECT TOP 10
[top]
, [ColumnB]
FROM
SomeTable
Should/might work.
Adrian
David Portas wrote:
> Stefano_PITECO wrote:
>
>
> This is on reason why column naming standards are important. Avoid
> using reserved words as column names. Either change the name of the
> column or make sure you always delimit TOP with square brackets.
>

Disable TOP

Dear all,
It's possible disable TOP operator?The only way that i have found is set
the compatibility to 6.5 but......
Thanks!!!!!!That's the only way. BTW, why would you want to do this?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Stefano_PITECO" <Stefano_PITECO@.discussions.microsoft.com> wrote in message
news:FB886D3B-8E16-4D60-AC85-A16974A63EB4@.microsoft.com...
Dear all,
It's possible disable TOP operator?The only way that i have found is set
the compatibility to 6.5 but......
Thanks!!!!!!|||Stefano_PITECO wrote:
> Dear all,
> It's possible disable TOP operator?The only way that i have found is set
> the compatibility to 6.5 but......
> Thanks!!!!!!
You could try using SET FIPS_FLAGGER. See Books Online for details.
This will generate warnings for a lot of other stuff too.
--
David Portas
SQL Server MVP
--|||Strange requirement :-D No that not posssible...
Why do you wanna do this ?
HTH, Jens Suessmeyer.|||Top is one of the most important field of my database....
"Tom Moreau" wrote:
> That's the only way. BTW, why would you want to do this?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Stefano_PITECO" <Stefano_PITECO@.discussions.microsoft.com> wrote in message
> news:FB886D3B-8E16-4D60-AC85-A16974A63EB4@.microsoft.com...
> Dear all,
> It's possible disable TOP operator?The only way that i have found is set
> the compatibility to 6.5 but......
> Thanks!!!!!!
>|||So what you're saying is that TOP is a column name? Ouch. It'll mean that
you'll have to change all references to use [TOP] - or you'll have to change
the column name and then change all references to use that.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Stefano_PITECO" <Stefano_PITECO@.discussions.microsoft.com> wrote in message
news:9B742642-294F-401C-87A0-5654F4C261F0@.microsoft.com...
Top is one of the most important field of my database....
"Tom Moreau" wrote:
> That's the only way. BTW, why would you want to do this?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Stefano_PITECO" <Stefano_PITECO@.discussions.microsoft.com> wrote in
> message
> news:FB886D3B-8E16-4D60-AC85-A16974A63EB4@.microsoft.com...
> Dear all,
> It's possible disable TOP operator?The only way that i have found is set
> the compatibility to 6.5 but......
> Thanks!!!!!!
>|||Stefano_PITECO wrote:
> Top is one of the most important field of my database....
>
This is on reason why column naming standards are important. Avoid
using reserved words as column names. Either change the name of the
column or make sure you always delimit TOP with square brackets.
--
David Portas
SQL Server MVP
--|||Try using square brackets
SELECT TOP 10
[top]
, [ColumnB]
FROM
SomeTable
Should/might work.
Adrian
David Portas wrote:
> Stefano_PITECO wrote:
>>Top is one of the most important field of my database....
>
> This is on reason why column naming standards are important. Avoid
> using reserved words as column names. Either change the name of the
> column or make sure you always delimit TOP with square brackets.
>