New features in SQL Server 2005 SP2

In Microsoft SQL Server 2005 Service Pack 2 (SP2), the following components have new or improved features. SQL Server 2005 Express Edition SP2 does not support all the features listed in this topic. Those features that SQL Server 2005 Express Edition SP2 does support have been identified explicitly.

Database Engine

• Maintenance plans are now supported by the SQL Server Database Services installation. Before SP2, you were required to install SQL Server 2005 Integration Services (SSIS) to run maintenance plans on a server-only installation.
• Maintenance plans now support multiserver environments, logging to remote servers, and multiple schedules.
• Added new functionality in the SQL Server 2005 Enterprise Edition to provide another storage format that can be used to minimize the disk space that is needed to store existing decimal and numeric data types. No application changes 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 in the column do not require many digits of precision, you can potentially save a large amount of the disk space that is needed to store the table. A new stored procedure is provided to estimate the reduction in average row size with the new storage format.
• Added logon triggers and a common criteria compliance enabled Option for sp_configure to support Common Criteria in the SQL Server 2005 Enterprise Edition.
• The sqllogship application is now supported. This application performs a backup, copy, or restore operation, and associated clean-up tasks for a log shipping configuration.
• 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. This feature is supported in SQL Server 2005 Express Edition SP2.
• SMO for Relational Engine Features: Table.CheckIdentityValue() correctly generates the schema name for the fully qualified object name. This feature is supported in SQL Server 2005 Express Edition SP2; Column.AddDefaultConstraint() works against table columns for SQL Server 2000 database instances. This feature is supported in SQL Server 2005 Express Edition SP2.
• Added five columns to sys.dm_exec_sessions for Common Criteria compliance: original_security_id, original_login_name, last_successful_logon, last_unsuccessful_logon, and unsuccessful_logons.
• Added the parameter database_id to the OBJECT_NAME syntax. Added the built-in function OBJECT_SCHEMA_NAME (Transact-SQL).
• In Enterprise Edition, you can now initialize subscriptions to snapshot and transactional publications by using a database snapshot. To do this, specify a value of database snapshot or database snapshot character for the sync_method publication property. Database snapshots provide the foundation for a robust snapshot processing mechanism that can reduce lock contention at the publication database during snapshot generation. For more information, see sp_addpublication.
• Merge replication now provides a stored procedure that regenerates the triggers, stored procedures, and views that are used to track data changes. For more information, see sp_vupgrade_mergeobjects.

SQL Server Management Tools

Maintenance Plans

• Multiple schedules. The Maintenance Plan Designer supports multiple subplans for a maintenance plan. Each subplan can have an independent schedule for maintenance tasks.
  
• Multiple server administration. Maintenance plans will work on multiple servers using master server (MSX) and target server (TSX). After you run the Maintenance Plan Wizard some manual configuration is required.
  
• The Maintenance Plan Wizard now includes the highly sought after Maintenance Cleanup task that was in SQL Server 2000. This task removes files remaining from executing a maintenance plan.
  
• Multiple fixes to individual tasks:
  
  • The Database Backup maintenance plan task now includes the ability to specify the backup expiration option that was previously available in SQL Server 2000.
    
  • The Database Backup maintenance plan task no longer changes the folder location for the backup if it is set to something other than the default location.
    
  • The Backup Database maintenance plan task prohibits the ability to mistakenly set the option to create differential and transaction log backups for system databases.
    
  • The History Cleanup maintenance plan task includes the ability to set the option to delete files using a unit in hours.
    
  • The Update Statistics task includes options for full scan or for sample size as was available in SQL Server 2000 maintenance plans.
    

Management Studio Reports

• Custom reports. Users can now integrate their own management reports into Management Studio. For more information, see Custom Reports in Management Studio. This feature is supported in SQL Server 2005 Express Edition SP2.
  
• Improved discoverability. Management Studio reports are now opened from the shortcut menu of Object Explorer. This feature is supported in SQL Server 2005 Express Edition SP2.
  
• Improved usability. Each report opens in its own window. This enables the user to easily compare reports. We also keep track of most recently used reports. This gives users quicker access to their favorite reports. This feature is supported in SQL Server 2005 Express Edition SP2.
  
• Scalability improvements. Many of the reports that we shipped, such as the Disk Usage report for databases, perform better with lots of objects. This feature is supported in SQL Server 2005 Express Edition SP2.
  
  • The Disk Usage report for a database was divided into five separate reports to improve overall performance of the reports. The earlier version of the report displayed all the information in a single report, which caused the report to take a long time to display data from large databases. The new Disk Usage report shows only the top-level view for the data and log files that are used by the database. The other reports are as follows: Disk Usage per Top Tables for the top 1000 tables, Disk Usage per Table, Disk Usage per Partition, and Index Physical Statistics. This feature is supported in SQL Server 2005 Express Edition SP2.
    
  • The Disk Usage report for a database no longer recommends reorganizing all indexes and is now based on the recommendation for the sys.dm_index_usage_stats. This feature is supported in SQL Server 2005 Express Edition SP2.
    

Scripting

• Script generation. You can control how objects are scripted from the Object Explorer using the new Tools | Options script dialog box. This dialog box also controls the defaults for the generate Script Wizard. This feature is supported in SQL Server 2005 Express Edition SP2.
  
• 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. This feature is supported in SQL Server 2005 Express Edition SP2.
  
• Fewer connection prompts when you open .Transact-SQL scripts. Management Studio determines the connection of the current context. This feature is supported in SQL Server 2005 Express Edition SP2.
  

Database Mail

• You can now customize parameters that are passed to DatabaseMail90.exe
  
• Customers and SQL Server Customer Support had difficulty in quickly identifying Database mail misconfigurations. Error reporting has been enhanced. The user will be given much more context to the underlying problems.
  

Copy Database Wizard

• The wizard can discover many of the issues with dependent objects to improve reliability.
  
• Verbose logging. A verbose logging file is created during operation and can be examined to diagnose problems with the operation.
  
• Improved reliability for online database transfers using SMO method:
  
  • Stored procedures, Jobs, Packages, Messages, Endpoints, LOGINs
    
  • Permissions (explicit GRANT/DENY)
    

SQL Server Management Studio Express

• Users can now manage SQL Server Compact Edition databases.
  
• Management Studio reports and custom reports will now run in SQL Server 2005 Express Edition.
  

Service Pack Setup and Hotfix Installer

• Several issues prevented customers from installing Service Pack 1. Most the issues in the installer for the core product and updates are fixed.
  

Miscellaneous

• Graphical show plans. The spacing between nodes is reduced to display more plan information. This feature is supported in SQL Server 2005 Express Edition SP2.
  
• Linked servers. The New Linked Server dialog box now includes a Test Connection button. The Linked Servers node in Object Explorer now displays system catalogs and user database objects. As soon as the linked server is created, you can expand its node to show tables and views on the remote server. This feature is supported in SQL Server 2005 Express Edition SP2.
  
• Viewing permissions. Users can automatically see the principle’s list of securables that were specifically granted, revoked, or denied. This feature is supported in SQL Server 2005 Express Edition SP2.
  
• The Restore Database dialog restores a database when multiple databases are backed up within a single .bak file. This feature is supported in SQL Server 2005 Express Edition SP2.
  
• The Restore Database dialog allows for the editing of the backup location path so that nonmapped network drives can be used for restoring backups. This feature is supported in SQL Server 2005 Express Edition SP2.
  
• The Restore Database dialog recognizes European date time formats when you perform point-in-time restore operations. This feature is supported in SQL Server 2005 Express Edition SP2.
  
• The Attach Database dialog allows for the changing of the database owner for logins that are mapped to a Windows domain user account as [BUILTIN\Administrators]. This feature is supported in SQL Server 2005 Express Edition SP2.
  
• The Backup Database dialog lets you use a null device as the backup destination. This feature is supported in SQL Server 2005 Express Edition SP2.
  
• The Backup Database dialog with Management Studio Express allows for the backup of databases on SQL Server Desktop Engine (also known as MSDE 2000) database instances. This feature is supported in SQL Server 2005 Express Edition SP2.
  
• The Database Property dialog performance was greatly improved to better support databases with lots of files and file groups. This feature is supported in SQL Server 2005 Express Edition SP2.
  
• The Full-Text Catalog Properties dialog allows for for creating the index using a schedule for named SQL Server database instances.
  
• The Server Properties (Security Page) page allows for the enabling of the common criteria option for SQL Server 2005 Enterprise Edition. This feature is supported in SQL Server 2005 Express Edition SP2.
  

SQL Server Analysis Services

• The 2007 version of Microsoft Office requires the installation of SQL Server 2005 Analysis Services SP2 to support all its business intelligence features. If you run against an instance of Analysis Services that does not have SP2 installed, features of Microsoft Office that require SP2 will be disabled.
  
• The functionality of local and session cubes has been greatly improved.
  
• Performance and scalability has been improved with regard to subselects, running sum calculations, NUMA optimizations for partition processing, partition query scalability, ragged hierarchies, visual totals, ROLAP dimensions, cell writeback, many-to-many dimensions, drillthrough, semi-additive measures, unary operators, and stored procedures.
  
• A warning message now appears when a user-defined hierarchy is not defined as a natural hierarchy.
  
• The MDX Drilldown* functions have a new argument that lets you specify drill down on specified tuples only.
  
• The SCOPE_ISOLATION property has been added to the MDX CREATE method.
  This property enables session-scoped and query-defined calculations to be resolved before calculations in the cube, rather than after.
  
• Numerous functionality and performance-related improvements have been incorporated. Specifically, improvements have been made to incremental processing, usage-based aggregation design algorithms, backward and forward compatibility, parent-child security, partition query scalability, cell writeback, and the Time Intelligence Wizard.
  
• The performance and functionality of the neural network viewer has been improved, and support for multiple nested tables has been added.
  
• The performance of naïve bayes predictions have been improved through caching of commonly used attributes.
  
• Neural network training has been improved through better utilization of memory, with sparse training data sets and better utilization of multiple threads during error computation (SQL Server 2005 Enterprise Edition feature).
  
• Limited support for data mining viewers with local mining models has been added.
  
• The redistribution of data mining viewer controls is now dependent upon ADOMD.NET.
  The new redist file will be available in a feature pack that will ship soon after Service Pack 2.

SQL Server Reporting Services

• You can integrate a report server instance with Windows SharePoint Services 3.0 or Microsoft Office 2007 SharePoint Server to store, secure, access, and manage report server items from a SharePoint site. Integration features are provided jointly through SP2 and a special Reporting Services Add-in that you download and install on an instance of the SharePoint technology you are using.
  The new Report Viewer Web Part is included in the Reporting Services Add-in that you install on a SharePoint technology instance.
  
• In SQL Server 2005 Reporting Services (SSRS), a Select All check box was automatically added when you created an available values list for a multivalue report parameter. If you upgraded to SQL Server 2005 Service Pack 1 (SP1), the Select All check box was no longer available. In SQL Server 2005 Service Pack 2 (SP2), the Select All check box has been restored. This feature is supported in SQL Server 2005 Express Edition SP2.
  
• To access a Hyperion System 9.3 BI+ Enterprise Analytics data source, Reporting Services now provides the Microsoft .NET Data Provider for Hyperion Essbase. This new data processing extension provides a graphical query designer that enables you to interactively build Multidimensional Expressions (MDX) queries. The Microsoft .NET Data Provider for Hyperion Essbase sends these MDX queries to the Hyperion System 9 BI+ Analytic Provider Services 9.3, which builds the structures necessary to query and retrieve data from a Hyperion Essbase OLAP repository. The Microsoft .NET Data Provider for Hyperion Essbase requires Hyperion System 9.3 Beta 2. You must install Hyperion System 9.3 Beta 2 on the data source server before you can use Hyperion Essbase as a data source.
  
• Report model generation from Oracle data sources that run on version 9.2.0.3 or later is supported. You can generate Oracle-based models by using Report Manager, Management Studio, or Model Designer.
  The Oracle client must be installed on the report server and on any client computers that will access the report server remotely. The Oracle client directory must be located in the system path and both the Report Server Windows service and the Report Server Web service must have permissions to access the files in this directory.

SQL Server Integration Services

• You can now troubleshoot a package's interaction with external data sources by enabling logging and selecting the package's Diagnostic event for logging. Many package failures occur during interaction with external data providers. However, those providers often do not return messages to Integration Services that provide enough information to begin troubleshooting. The Integration Services components listed below have now been instrumented to write a message to the log before and after every call to an external data provider. This message includes the name of the method being called; for example, the Open method of an OLE DB Connection object, or the ExecuteNonQuery method of a Command object. You can view these messages by enabling logging and selecting the package's Diagnostic event.
  
  • OLE DB Connection Manager, Source, and Destination
    
  • ADO.NET Connection Manager and DataReader Source
    
  • Execute SQL Task
    
  • Lookup Transformation
  
  
• Integration Services and the SQL Server Import and Export Wizard now support the Microsoft Office 12.0 Access Database Engine OLE DB Provider for connectivity to Microsoft Office Access 2007 and Excel 2007 data sources.
  You cannot use the Microsoft Jet OLE DB Provider to connect to Access 2007 and Excel 2007 data sources. To connect to Excel 2007 data sources, you must use the OLE DB Connection Manager and the OLE DB Source and Destination; to connect to Excel 2003 and earlier data sources, you continue to use the Excel Connection Manager and the Excel Source and Destination.
• 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 ("?").
• You can now use a combo box to select variables for properties of certain data flow components that require a variable name.
  In earlier versions, you had to type a variable name as free text to provide a value for properties that expected a variable name, such as the OpenRowsetVariable property of the OLE DB Source. The combo box now contains all the available variables, including system variables.
• The Lookup transformation now reports the final count of cached rows.
  In full cache mode, the Lookup transformation uses a function activated by a timer to report the count of rows that have been cached. In previous versions, the function did not always report the final count of cached rows. In SP2, the Lookup transformation provides a new informational message that reports the final number of rows that were cached in full-cache mode. This message appears in the Progress window and is logged by the OnInformation event.
• The Execute SQL task now has a ParameterSize property for string parameters.
  In earlier versions, the Execute SQL task failed when using an ADO.NET connection to execute a stored procedure that returned a string output parameter, because the size of the string parameter was set to 0 by default. In SP2, parameters used with the Execute SQL task have a ParameterSize property that can be used to allocate the memory required for a string output parameter. The Script component now raises an error when a <columnname>_IsNull property is set to True.
• The IsNull property of columns in the Script Component now raises a warning when it is used incorrectly.
  The code generated by the Script component contains a <columnname> and a <columnname>_IsNull property for each input and output column. The intention of the <columnname>_IsNull property is to allow the user to specify a Null value for the column by setting the value of the property to True. To specify a non-null value, the user should assign a value to the <columnname> property. In earlier versions, if you set <columnname>_IsNull to False, the property was silently reset to True. In SP2, the Script component raises a warning message about the incorrect usage.