Advantages of SQL Server 2008 over SQL Server 2005

Question

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

User · Answer

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

User · Answer

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

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

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

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

User · Answer

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

User · Answer

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    http   soft-engineering blogspot com

User · Answer

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

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

User · Answer

I guess it depends on your role  For me as a developer    Merge statement Reporting Services improvement Date time changes   Edit  late update  after using it   filtered indexes table valued parameters Reporting Services without IIS

User · Answer

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    http   soft-engineering blogspot com

User · Answer

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

User · Answer

I guess it depends on your role  For me as a developer    Merge statement Reporting Services improvement Date time changes   Edit  late update  after using it   filtered indexes table valued parameters Reporting Services without IIS

User · Answer

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

User · Answer

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

User · Answer

I guess it depends on your role  For me as a developer    Merge statement Reporting Services improvement Date time changes   Edit  late update  after using it   filtered indexes table valued parameters Reporting Services without IIS

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

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

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

User · Answer

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

User · Answer

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

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

User · Answer

I guess it depends on your role  For me as a developer    Merge statement Reporting Services improvement Date time changes   Edit  late update  after using it   filtered indexes table valued parameters Reporting Services without IIS

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

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

User · Answer

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-server-2005] Advantages of SQL Server 2008 over SQL Server 2005?

Examples related to sql-server-2005

Examples related to sql-server-2008

Examples related to comparison