Differences in Sql Server 2005 and Sql Server 2008

Here I have sorted out a list of features that are new to MS SQL Server 2008 and enhances the productivity and output of both the database and the users using them. The list is not exhaustive but covers most of the key features. The list is as follows:

·         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. 

But be aware that a lot of the really killer features are only in Enterprise Edition. . See microsoft.com/sqlserver/2008/en/us/…

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.

In Sql server 2005, there is no option to compress backup files, but in sql server 2008, there you find it. Storing backup file takes 5 minutes without compression in sqlserver 2005, but it takes only 3 minutes in Sql server 2008 for storing backup files with compression.

Change Tracking. This was the obvious feature that all were anticipating. It would make the life of the developer a lot easier as he can track the changes. 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)

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.