Advantages of MS SQL Server 2008 over MS SQL Server 2005?

Question

What are the key differences between MS SQL Server 2005 and 2008?

Are there any compelling reasons for upgrading (any edition, as I have a customer with multiple editions)? Or can you point to a website with either a chart or bullet point comparison of the two servers?

Also, is there anything noteworthy in the Express editions of either version?

Answer 1

There are new features added. But, you will have to see if it is worth the upgrade. Some good improvements in Management Studio 2008 though, especially the intellisense for the Query Editor.

Answer 2

Obviously, there's the huge advantage of having the very latest version number - nobody can say your system's out of date!

I make it a point not to upgrade software unless I know of a particular benefit I want or that may have some value in the future that's worth today's pain. Databases in particular are things you should never update on a whim as usually you're using a database for a vital reason and something could change in an upgrade that upsets the apple cart.

---

UPDATE:

Notice that I didn't edit or delete the post just because three of you decided to vote this one down. That's because those who voted this down MISSED THE POINT and they might very well learn something by stopping and thinking...

The very idea of always going to a younger release is flawed; vendors love it, but if there's nothing wrong, don't fix it! This is especially true in the database world where it's not at all uncommon for entire enterprises depend on a database. Upgrading without knowing exactly what benefits and risks there are is just plain stupid.

The original poster said they didn't know if there were any benefits to be had. In my opinion it's VERY risky to suggest to that person that they upgrade because the risks are unknown. Sites often have dependencies they don't even know about! ...Hang out in this industry for some thirty years and maybe you'll figure this out... I guess there's also a big difference between coding engineers and the support staff that have to keep systems running 24/7. Those of us who have to keep the lights burning know how vital our systems are and that we have to take a more cautious approach than someone writing code.

Another point here; when you see something you don't directly agree with, you might just think about it for a while instead of just deciding it doesn't fit your paradigm so it must be wrong - and therefore voting it down. Voting down should only be done for things that are clearly wrong and not because it disagrees with your OPINION.

Answer 3

• Transparent Data Encryption. The ability to encrypt an entire database.
• Backup Encryption. Executed at backup time to prevent tampering.
• External Key Management. Storing Keys separate from the data.
• Auditing. Monitoring of data access.
• Data Compression. Fact Table size reduction and improved performance.
• Resource Governor. Restrict users or groups from consuming high levels or resources.
• Hot Plug CPU. Add CPUs on the fly.
• Performance Studio. Collection of performance monitoring tools.
• Installation improvements. Disk images and service pack uninstall options.
• Dynamic Development. New ADO and Visual Studio options as well as Dot Net 3.
• Entity Data Services. Line Of Business (LOB) framework and Entity Query Language (eSQL)
• LINQ. Development query language for access multiple types of data such as SQL and XML.
• Data Synchronizing. Development of frequently disconnected applications.
• Large UDT. No size restriction on UDT.
• Dates and Times. New data types: Date, Time, Date Time Offset.
• File Stream. New data type VarBinary(Max) FileStream for managing binary data.
• Table Value Parameters. The ability to pass an entire table to a stored procedure.
• Spatial Data. Data type for storing Latitude, Longitude, and GPS entries.
• Full Text Search. Native Indexes, thesaurus as metadata, and backup ability.
• SQL Server Integration Service. Improved multiprocessor support and faster lookups.
• MERGE. TSQL command combining Insert, Update, and Delete.
• SQL Server Analysis Server. Stack improvements, faster block computations.
• SQL Server Reporting Server. Improved memory management and better rendering.
• Microsoft Office 2007. Use OFFICE as an SSRS template. SSRS to WORD.
• SQL 200 Support Ends. Mainstream Support for SQL 2000 is coming to an end.

(Good intro article part 1, part 2, part 3. As for compelling reasons, that depends on what you are using SQL server for. Do you need hierarchical data types? Do you currently store files in the database and want to switch over to SQL Server's new filestream feature? Could you use more disk space by turning on data compression?

And let's not forget the ability to MERGE data.

Answer 4

Someone with more reputation can copy this into the main answer:

• Change Tracking. Allows you to get info on what changes happened to which rows since a specific version.
• Change Data Capture. Allows all changes to be captured and queried. (Enterprise)

Answer 5

I guess it depends on your role

For me as a developer:

• Merge statement
• Reporting Services improvement
• Date/time changes

Answer 6

Kevin Kline wrote a pretty good whitepaper summing up the top ten features in 2008 that make it worthwhile.

SQL 2008 Worth the Wait

But be aware that a lot of the really killer features are only in Enterprise Edition. Data compression and backup compression are among two of my top favorites - they give you free performance improvements right off the bat. Data compression lessens the amount of I/O you have to do, so a lot of queries speed up 20-40%. CPU use goes up, but in today's multi-core environments, we often have more CPU power but not more IO. Anyway, those are only in Enterprise.

If you're only going to use Standard Edition, then most of the improvements require changes to your application code and T-SQL code, so it's not quite as easy of a sell.

Answer 7

SQL 2008 also allows you to disable lock escalation on specific tables. I have found this very useful on small frequently updated tables where locks can escalate causing concurrency issues. In SQL 2005, even with the ROWLOCK hint on delete statements locks can be escalated which can lead to deadlocks. In my testing, an application which I have developed had concurrency issues during small table manipulation due to lock escalation on SQL 2005. In SQL 2008 this problem went away.

It is still important to bear in mind the potential overhead of handling large numbers of row locks, but having the option to stop escalation when you want to is very useful.

Answer 8

I went to a bunch of sql 2008 talks in PASS 2008, the only 'killer feature' from my point of view is extended events. There are lots of great improvements, but that was the only one that got close to being a game changer for me. Table value params and merge were probably my next fav. Day-to-day, intellisense is a huge win.. but this isn't really specific to sql 2008, just the sql 2008 toolset (other tools can give you similar intellisense against sql2005/2000/etc).

Answer 9

One of my favourites are Filtered indexes. Now I can create lightning fast covering indexes for my most critical queries with only minor impact on DML statements.

/Håkan Winther

Answer 10

The new features are really great and its meets the very important factors of current age. For .net people it’s always be a boon to use SQL Server, I hope using the latest version we will have better security and better performance as well as the introduction of compression the size of the database. The backup encryption utility is also phenomenon.

Once again thanks to Microsoft for their great thoughts in form of software :)