Migrating From SQL Server Server 7 To 2005, What should I get excited about?

Question

The company I work for has decided to join the 21st century and upgrade our main database cluster from SQL Server 7 to SQL Server 2005. As a web developer what new whiz-bang features of SQL Server 2005 should I get excited about or get to know?

Currently I'm mainly writing CRUD style queries, pretty much exclusively using Stored Procdures for a mixed ASP.net and Classic ASP environment.

Answer 1

One that comes to mind is that you can integrate CLR / .NET code into SQL Server.

Here are some others.

Answer 2

My favorite new features are

• varchar(max) nvarchar(max) data types (much easier to work with than text, ntext)
• xml data type and Xpath support
• Improved error handling with try...catch blocks
• .NET code integration
• Improved management interface

Answer 3

Common table expressions, PIVOT and UNPIVOT, APPLY

Answer 4

SQL 7? I'd start with the basics, like referential integrity and cascades. Or, you don't have to code your own triggers for all that stuff that Access was doing back in 98 or so when one had Sql 7.

Answer 5

1. system tables are now inaccessible, you will need use the system catalog views. Yes, some of your code needs to be rewrited.
2. partitioning. You can split very big table into two.
3. Try..Catch.
4. Common table expressions (CTE). It's like temporary tables, but more powerful.
5. Management server, that helps you to register your server groups and then use these groups from any another client place. (maybe it is in 2008 only? I cannot remember)
6. I don't know about your needs, but I was excited by mirroring. But I'm DBA :)
7. Some new types.
8. Snapshots.
9. Most of your code will still work :)

and many another things.

Answer 6

Performance monitoring, profiling, tuning

Dynamic Management Views & Functions

http://msdn.microsoft.com/en-us/library/cc917696.aspx