Advantages of SQL Server 2008 over SQL Server 2005?

sql-server-2005 sql-server-2008 comparison
torial picture torial · Oct 13, 2008 · Viewed 160.2k times · Source

What are the key differences between Microsoft's SQL Server 2005 and SQL Server 2008?

Are there any compelling reasons for upgrading (any edition, as I have a customer with multiple editions)? Or is there 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

Josef picture Josef · Oct 13, 2008
  • 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 2000 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.

Related questions

How to avoid the "divide by zero" error in SQL?

I have this error message: Msg 8134, Level 16, State 1, Line 1 Divide by zero error encountered. What is the best way to write SQL code so that I will never see this error message again? I could do either of the following: …

Read More
SQL Server query to find all permissions/access for all users in a database

I would like to write a query on a sql 2008 that will report all the users that have access to a specific database, or objects within the database such as tables, views, and stored procedures, either directly or due to …

Read More
sql query to return differences between two tables

I am trying to compare two tables, SQL Server, to verify some data. I want to return all the rows from both tables where data is either in one or the other. In essence, I want to show all the …

Read More