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?
This question is related to
sql-server-2005
sql-server-2008
comparison
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.
The Denver SQL Server Users group has had some really good presentations over the last couple of months on the new features in SQL 2008 including one from Paul Nielsen just last week shortly after he got back from "Jump Start" up in Redmond (if I remember the name of the event correctly).
A couple of caveats on all of the "new features" for SQL 2008, the triage to determine which features will be in the various editions is still in progress. Many/most of the new/very cool features like data compression, partitioned indexes, policies, etc. are only going to be in the enterprise edition. Unless you're planning on running enterprise edition a lot of the features that are in the CTP's will probably not be in SQL 2008 standard, etc.
On other minor but often overlooked issue - SQL 2008 will only be 64-bit, if you're buying new hardware shouldn't be an issue but if you're planning on using existing hardware... also, if you've got dependencies on third party drivers (e.g. oracle) best be sure that a 64-bit version is available/works
Someone with more reputation can copy this into the main answer:
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.
I guess it depends on your role
For me as a developer:
Edit, late update, after using it
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.
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.
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.
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 :)
I guess it depends on your role
For me as a developer:
Edit, late update, after using it
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.
SQL Server 2008 introduces four new date and time data types, which include:
* DATE: As you can imagine, the DATE data type only stores a date in the format of YYYY-MM-DD. It has a range of 0001-01-01 through 9999-12-32, which should be adequate for most business and scientific applications. The accuracy is 1 day, and it only takes 3 bytes to store the date.
* TIME: TIME is stored in the format: hh:mm:ss.nnnnnnn, with a range of 00:00:00.0000000 through 23:59:59:9999999 and is accurate to 100 nanoseconds. Storage depends on the precision and scale selected, and runs from 3 to 5 bytes.
* DATETIME2: DATETIME2 is very similar to the older DATETIME data type, but has a greater range and precision. The format is YYYY-MM-DD hh:mm:ss:nnnnnnnm with a range of 0001-01-01 00:00:00.0000000 through 9999-12-31 23:59:59.9999999, and an accuracy of 100 nanoseconds. Storage depends on the precision and scale selected, and runs from 6 to 8 bytes.
* DATETIMEOFFSET: DATETIMEOFFSET is similar to DATETIME2, but includes additional information to track the time zone. The format is YYYY-MM-DD hh:mm:ss[.nnnnnnn] [+|-]hh:mm with a range of 0001-01-01 00:00:00.0000000 through 0001-01-01 00:00:00.0000000 through 9999-12-31 23:59:59.9999999 (in UTC), and an accuracy of 100 nanoseconds. Storage depends on the precision and scale selected, and runs from 8 to 10 bytes.
SQL Server 2008 introduces four new date and time data types, which include:
* DATE: As you can imagine, the DATE data type only stores a date in the format of YYYY-MM-DD. It has a range of 0001-01-01 through 9999-12-32, which should be adequate for most business and scientific applications. The accuracy is 1 day, and it only takes 3 bytes to store the date.
* TIME: TIME is stored in the format: hh:mm:ss.nnnnnnn, with a range of 00:00:00.0000000 through 23:59:59:9999999 and is accurate to 100 nanoseconds. Storage depends on the precision and scale selected, and runs from 3 to 5 bytes.
* DATETIME2: DATETIME2 is very similar to the older DATETIME data type, but has a greater range and precision. The format is YYYY-MM-DD hh:mm:ss:nnnnnnnm with a range of 0001-01-01 00:00:00.0000000 through 9999-12-31 23:59:59.9999999, and an accuracy of 100 nanoseconds. Storage depends on the precision and scale selected, and runs from 6 to 8 bytes.
* DATETIMEOFFSET: DATETIMEOFFSET is similar to DATETIME2, but includes additional information to track the time zone. The format is YYYY-MM-DD hh:mm:ss[.nnnnnnn] [+|-]hh:mm with a range of 0001-01-01 00:00:00.0000000 through 0001-01-01 00:00:00.0000000 through 9999-12-31 23:59:59.9999999 (in UTC), and an accuracy of 100 nanoseconds. Storage depends on the precision and scale selected, and runs from 8 to 10 bytes.
I went to a bunch of SQL Server 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 parameters and merge were probably my next favourite. Day-to-day, IntelliSense is a huge win.. But this isn't really specific to SQL Server 2008, just the SQL Server 2008 toolset (other tools can give you similar IntelliSense against SQL Server 2005, 2000, etc.).
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.
Someone with more reputation can copy this into the main answer:
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.
I guess it depends on your role
For me as a developer:
Edit, late update, after using it
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.
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.
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
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 :)
Someone with more reputation can copy this into the main answer:
I went to a bunch of SQL Server 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 parameters and merge were probably my next favourite. Day-to-day, IntelliSense is a huge win.. But this isn't really specific to SQL Server 2008, just the SQL Server 2008 toolset (other tools can give you similar IntelliSense against SQL Server 2005, 2000, etc.).
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.
The Denver SQL Server Users group has had some really good presentations over the last couple of months on the new features in SQL 2008 including one from Paul Nielsen just last week shortly after he got back from "Jump Start" up in Redmond (if I remember the name of the event correctly).
A couple of caveats on all of the "new features" for SQL 2008, the triage to determine which features will be in the various editions is still in progress. Many/most of the new/very cool features like data compression, partitioned indexes, policies, etc. are only going to be in the enterprise edition. Unless you're planning on running enterprise edition a lot of the features that are in the CTP's will probably not be in SQL 2008 standard, etc.
On other minor but often overlooked issue - SQL 2008 will only be 64-bit, if you're buying new hardware shouldn't be an issue but if you're planning on using existing hardware... also, if you've got dependencies on third party drivers (e.g. oracle) best be sure that a 64-bit version is available/works
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
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.
Source: Stackoverflow.com