Thursday, November 09, 2006

What's new in SQL Server 2005 SP2

Check out the new improvements here

Some additions that catch the eye:

  • Added new functionality in the SQL Server 2005 Enterprise Edition to provide an alternate storage format that can be used to minimize the disk space needed to store existing decimal and numeric data types. No application changes area are required to use its benefits. This new storage format, known as vardecimal storage format, stores decimal and numeric data as variable length columns and can be enabled or disabled at a table level on new or existing tables. If you have declared a decimal or numeric column with high precision but most values do not require it, you can potentially save the disk space needed to store the table. A new stored procedure is provided to estimate the reduction in average row size with the new storage format.

    Funny that they add this new datatype only to the Enterprise Edition in my humble opinion. Although I understand they probably have the most benefit of storage saving features.

  • Plan cache improvements that provide improved system performance, better use of the available physical memory for database pages, and the ability to return text XML query plans that contain an XML nesting level greater than or equal to 128 by using the new sys.dm_exec_text_query_plan table-valued function.

    Sounds very nice but I'd love to see more detail on how exactly this is achieved.

  • The value of the BypassPrepare property of the Execute SQL task has been changed to True by default.In earlier versions, the value of the BypassPrepare property was false, which indicated that statements were always prepared. In SP2, by default queries are not prepared. This eliminates errors with certain providers when you try to prepare a statement that uses parameter placeholders ("?").

    Seems I'm not the only one with this problem.

  • Generate Script Wizard. You can now specify that the scripted objects include a DROP statement before the CREATE statement. Scripting of objects into separate files is now possible.


The fixlist for SQL Server 2005 SP2 CTP can be found here