2008 and 2000 compatibility

[donutman]

I plan to upgrade to SQL Server 2008 from 2000. I’ve read that to run in 2008 mode, you must not use an alias (for a column name) in the Order By or Group By clauses. The use of *= and =* for joins is also verboten along with the keyword “WITH” (except in nolocks).
Hmm, does that mean:

Select a, b, c, 1 as AnAlias, d from aTable
UNION
Select e, f, g, 2, h from anotherTable order by AnAlias

will not work?
Are there any other gotchas in 2008 TSQL?
Karl


[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[markros]

It will work - not sure where did you read it.

See also link in Abdallah's message in this thread

http://social.msdn.microsoft.com/Fo...l/thread/03af8a09-f6f2-441e-9fef-25fc83945f3e

PluralSight Learning Library

 

[gmmastros]

I would encourage you to download sqlexpress 2008. Try it. I'm pretty sure the syntax you show would work.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[donutman]

Thank you both. I read the links inside the link and I downloaded a 2008 evaluation. I guess I misread something, but it is a little tricky.

Select a as b, c from aTable
order by aTable.b

That is invalid, but if you remove the aTable. from aTable.b, then it is ok.

Select a as b, c from aTable
group by b, c

That is also invalid, but if you replace "group by b" with "group by a" it is ok.

My original example is ok.
Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[SQLSister]

BTW even in SQL Server 2000, you should not be using *= and =* for joins as they will not always return the correct record set. Sometimes SQl Server interprets those as out joins and sometimes as cross joins, so if you used them in code right now, you need to change even before you convert.

"NOTHING is more important in a database than integrity." ESquared

 

[donutman]

Thanks and hi SQLSister. I've never used them, but I put them into the post for completeness. I assume you can't think of any other TSQL that I might have to change, can you? I also don't use triggers, so any compatibility problems there I can ignore.
Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[NastiaMurzin]

select columnname as aliasname from tablename;

 

[SQLSister]

Hi Karl, how's the bakery business?

Most likely you will be fine. There is very little from 2000 that won't work in 2008. However, if you are using ntext and text fields, you might want to go ahead and change to nvarchar(max) or varchar (max) as those will be going away in the next version of SQL server. This may mean you can simplify some of your code as well as the max datatypes are easier to get data into and out of than text and ntext.

We also have a third party encryption product that does not havea a 2008 version yet and so we have to use our databases in 2000 compatibility mode until we ccan get that fixed.

"NOTHING is more important in a database than integrity." ESquared