moved from ms sql 2000 to ms sql2005

[HandersonVA]

database has been recently upgraded from ms sql 2000 to ms sql 2005.
are there anything I need to be aware after upgrading to ms sql 2005?

for my experience, i got an error if i use column alias in ORDER BY
clause which was fine on ms sql 200.

thanks

[Erland Sommarskog]

Re: moved from ms sql 2000 to ms sql2005

HandersonVA (handersonva@ho tmail.com) writes:

database has been recently upgraded from ms sql 2000 to ms sql 2005.
are there anything I need to be aware after upgrading to ms sql 2005?

Run sp_updatestats on all databases, since statistics from SQL 2000 are
invalidated with the upgrade.

for my experience, i got an error if i use column alias in ORDER BY
clause which was fine on ms sql 200.

It's fine in SQL 2005 too. However, there were bugs in SQL 2000 which lead
to incorrect code being accepted. For instance in SQL 2000 you can
say:

SELECT name FROM sysobjects ORDER BY myownalias.name

This is correctly rejected in SQL 2005. There are a couple of variations
on this theme.

Another issue that has bitten more that one is that they had views
like:

CREATE VIEW myview AS
SELECT TOP 100 PERCENT ....
ORDER BY somecol

then they expect "SELECT ... FORM myview" to always return data ordered
by somecol. SQL 2000 usually honors that, which is mere chance. On
SQL 2005 you are less lucky. The moral is that you should always
specify an ORDER BY clause on SELECT statements that produces data.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx