So Aaron posted My reasons for upgrading to SQL Server 2008
I thought it was an interesting list, not as interesting as the one by Jason Massie (10 Reasons to Upgrade to SQL Server 2008) but still interesting :-) I would like to give you my list.
Date data type
We have data that goes back to 1896 so we cannot use smalldatetime instead of datetime. When your table has billions of rows and grows by millions of rows a day this will save a lot of storage. The time data type can also be beneficial if you need to find all customers that placed orders between 4 and 5 PM in the last 5 years.
Partition-Aware Seek Operation also known as skip scan
I still have to do some more testing with this but here is what it basically does: the query processor performs a first-level seek operation to find every partition that contains rows that meet the condition This identifies the partitions to be accessed. Within each partition identified, the processor then performs a second-level seek into the clustered index. More about this is explained here: http://msdn.microsoft.com/en-us/library/ms345599.aspx
Row and Page Level compression
I have tested this and for some tables performance is better and for some it is worse, you will need to test to see if it makes sense to turn this on or not. If you have lots of repeated values on the page then it should benefit you. I will have a blog post on this before the economy recovers (I just bought myself some time here)
Transact-SQL Row Constructors
If you have ever had to allow people to upload files, parse these files on a web server and import this data you will appreciate Row Constructors. No longer do you need to call a proc for every row in the file, after that checking that the rowcount is the same in the table as in the file. You will be able to insert a ton of rows in one shot, this will make the process much faster and it is also part of one transaction, they all go in or they don’t. BULK INSERT is of course better but most likely you only have 1 port open between the web server and the SQL server (and if you don’t then you should)
C# in SSIS
This might not seem as a big deal but switching between C# and VB can be a pain in the neck. If you are a developer who does 50% SQL development and 50% C# windows forms/web forms/MVC development then you don’t have to switch modes in your head every time you go from SQL to web. If you are doing VB then of course this doesn’t apply to you.
Table-Valued Parameters are nice because you don’t have to create temp tables anymore before calling the proc so that you can read from the temp table after the proc has executed. I always felt ‘dirty’ doing that and it felt like a hack.
Other new T-SQL things.
Not as important but still nice are the MERGE (UPSERT) statement, hierarchyid Data Type and Compound Operators. I am sure some people will love the geometry and geography spatial data types, I have no use for them right now
Other new SQL Server things
A couple of other things which I find interesting are:
On partitioned tables, you can configure locks to escalate to the partitions instead of to the whole table by using the LOCK_ESCALATION option of ALTER TABLE.
The FORCESEEK table hint.
SQL Server Extended Events
There you have it, I will be upgrading on some machines from 2000 to 2008 so this list will be even bigger for those instances. Actually these will be new machines as well.