TSQL Improvements during upgradtion to SQL2005 from SQL2000

Have you gone through UPGRADE ADVISOR tool on this database?
It is better to run thru this tool in order to get any reference on the issues that might cropup and differences between 2000 & 2005 versions.
Best option is to reindex all the tables when you have upgraded the database in order to store the best plan in the cache and optimizer will be taking care of such performance aspect.
Also review the content http://sqlserver-qa.net/blogs/tools/archive/2008/01/22/upgrade-blurg.aspx on this blog post.

Hi,
take a help of SSUA tool which will give you good information on where you need to change the code, here is the reference for t-sql code
http://blogs.msdn.com/steven_bates/archive/2006/02/11/TSQL-Enhancements-in-SQL-2005.aspx