What's New in SQL Server 2005 SP1
In Microsoft SQL Server 2005 Service Pack 1 (SP1), the following feature areas have new or improved features.
Feature Areas
Analysis Services
Data Programmability
Database Engine
Integration Services
Maintenance Plans
Profiler and Replay
Replication
Reporting Services
SQL Server 2005 Express Edition
SQL Server Agent
SQL Server Management Objects (SMO)
SQL Server Management Studio
Analysis Services
- Query performance and stability have been improved.
- HTTP connectivity now supports HTTP compression.
- Optimized queries to perspectives are now as fast as queries to the perspective’s base cube.
- The AdventureWorks Analysis Services Project has been updated. If you have previously deployed this project to an Analysis Services instance, you must redeploy this project to view the changes.
- The ascmd command-prompt utility sample application has been added to the SQL Server Analysis Services Samples collection.
.gif "Arrow icon used with Back to Top link")
Back to Top
Data Programmability
- Client connection time has been significantly improved when failover occurs while the server is using database mirroring and the clients are using TCP/IP.
Back to Top
Database Engine
Database mirroring has been enabled. Database mirroring is also supported for production deployments. The trace flag for testing database mirroring (t-1400) has been deprecated. Therefore, this flag has no effect in SP1.
A new database mirroring monitor has been added to SQL Server Management Studio.
SQL Server Profiler is now able to trace large data volumes on large computers.
XSD support has been added for some schemas that use nondeterministic content models.
There is a new system user-defined function that shows which OpenXML handles are open.
When you upgrade from SQL Server 2000 to SQL Server 2005, the query optimizer for the Database Engine has been improved to accommodate the upgrade process for third-party products that use SQL Server as their back-end database.
Unique nonclustered indexes can now be created online. For more information, see Guidelines for Performing Online Index Operations.
New dynamic management views have been added that monitor Query Execution memory grant status. For more information, see sys.dm_exec_query_memory_grants and sys.dm_exec_query_resource_semaphores.
Database Console Commands (DBCC) are now integrated with the Watson application. If a DBCC check detects that a database has become corrupted, the Watson application uploads that error report to Microsoft.
Note
For more information about the data reporting and collections process, visit the Microsoft Online Crash Analysis Web site. For information about the Microsoft data collection policy for these dialog boxes and about steps that Microsoft takes to help protect privacy, visit the Privacy Statement for the Microsoft Error Reporting Service Web page.
Improvements have been made to the following areas:
- Performance when compiling an XQuery that contains complex XML schemas.
- Performance when you use XML Data Modification Language (XML DML) to update XML.
- Performance of the dynamic management view, dm_exec_query_plan.
- Performance in floating-point arithmetic operations.
- Benchmark performance.
- Targeted statistics and cardinality.
- Debugging of a SQL common language runtime (CLR) program that uses parallel threads.
- How the Database Engine handles parallelized user-defined functions, aggregates, and user-defined type (UDT) methods.
- TPC-H performance.
Back to Top
Integration Services
- The Web Service task now supports parameters.
- The Import/Export Wizard now handles multiple-table scenarios.
- The IDtsPipelineEnvironmentService interface lets custom data flow components have programmatic access to the parent Data Flow task.
- Interoperability with Analysis Services has been improved. The DataReader source in Integration Services supports the System.Object data type by converting columns that have this data type to the DT_NTEXT Integration Services data type. To change the data type to a type that is more appropriate for your data, you can add a Data Conversion transformation.
- Performance has been improved for many transformations, such as the Sort transformation.
- Designer usability has been improved. For example, you can now right-click the Data Flow design surface and then click Execute Task to execute only the Data Flow task. You do not have to switch to the Control Flow tab to execute only the Data Flow task.
- We have added a registry-based policy for handling Integration Services package signatures as a package is executed. For example, administrators can disable loading of unsigned packages and untrusted packages. Because these settings are in the registry, administrators can propagate these settings in the domain by using Microsoft Windows Group Policy policy settings.
- In the Advanced Editor dialog box, the Flat File source has the new property UseBinaryFormat. This property supports loading packed decimal data into the pipeline for processing by a script or by a custom transformation.
- In the Advanced Editor dialog box, the DataReader source has the new property CommandTimeout. You can use this property to modify the time-out period to allow for long-running operations.
- To create or to modify the property expressions of variables, you can now open the Expression Builder dialog box from the Properties window.
- You can now add annotations to precedence constraints.
Back to Top
Maintenance Plans
- The maintenance plan feature works when you have either SQL Server Tools or Integration Services installed. In the release version, you had to have installed both of these components.
- You can now use cleanup tasks in a maintenance plan to delete backup files from subdirectories.
Back to Top
Profiler and Replay
Previously, profiling Analysis Services showed the time in Coordinated Universal Time (UTC). The time now appears in the user's local time.
Previously, events that successfully replayed were not counted correctly, so incorrect replay statistics were reported to the user. This problem has been fixed.
Note
Users may see a lower percentage of replayed events than the percentage of replayed events reported in the release version of SQL Server 2005. However, the number of replayed events is correct. You can use this number when you compare replay in SQL Server 2005 with replay in SQL Server 2000.
Previously, Replay ignored the network packet size that you set when you connected to the network. Instead, Replay always created a connection that had a packet size of 32 kilobytes (KB). Sometimes, large playbacks would cause the server to run out of memory. This problem has been fixed.
Back to Top
Replication
- You can now use database mirroring together with replication. For more information, see Replication and Database Mirroring.
- For tables that use cascading referential integrity constraints, such as the ON DELETE CASCADE clause and the ON UPDATE CASCADE clause, replication now supports merge operations on precomputed partitions. For more information, see Optimizing Parameterized Filter Performance with Precomputed Partitions.
- Usability has been improved in the user interface for replication.
Reporting Services
- You can now add static images, such as company logos or other graphics, to reports in Report Builder.
- A new data processing extension provides a graphical query designer that you can use to access InfoCubes and queries in Reporting Services reports for SAP NetWeaver Business Intelligence data sources. For more information, see Defining Report Datasets for Multidimensional Data from an SAP NetWeaver BI System.
- Many bugs have been fixed in the integration of Analysis Services in Report Designer. For example, the report wizard now works correctly with Analysis Services data sources. Additionally, the automated parameter-generation behavior has been improved.
- Large databases no longer cause auto-generation problems for the Report Model Wizard in the Report Model Designer.
- Complex parameter declarations should no longer cause parsing problems in Report Designer.
Back to Top
SQL Server 2005 Express Edition
- SQL Server 2005 Express Edition with Advanced Services is a new downloadable package that contains everything that was available previously in SQL Server 2005 Express Edition, together with the new management tool, SQL Server Management Studio Express. Additionally, this package includes Full-Text Search and SQL Server Reporting Services.
- Full-Text Search: You can use this feature to issue full-text queries against plain character-based data in SQL Server tables. Full-text queries can include words and phrases or multiple forms of a word or phrase. For more information, see Full-Text Search.
- Reporting Services: This feature includes Report Server and Business Intelligence Development Studio. You can use this feature to create, modify, and run reports on local, relational databases. For more information, see Reporting Services in SQL Server 2005 Express Edition with Advanced Services.
- SQL Server Management Studio Express: This feature is a free tool that you can use to manage and to administer SQL Server 2005 Express Edition. For more information, see SQL Server Management Studio Express.
- SQL Server 2005 Express Toolkit is a separate downloadable package that includes Business Intelligence Development Studio. Business Intelligence Development Studio lets you create and modify server-based reports. For more information, see SQL Server Express Toolkit.
Back to Top
SQL Server Agent
- The SQL Server Agent job step token syntax has changed. As a result, an escape macro must now accompany all tokens used in job steps, or else those job steps will fail. Using escape macros and updating your SQL Server Agent job steps that use tokens are described in Using Tokens in Job Steps.
- A registry switch has been added that you can use to control encryption between a master server and a target server (MSX-TSX).
- In a master server/target server environment, the SQL Server Agent service that is running under the NetworkService account can be run from either a master server or a target server.
- Database Mail will now run on 64-bit versions of SQL Server Management Studio and also on 64-bit operating systems.
- Previously, Agent XML for Analysis (XMLA) jobs over 32 KB were being truncated. This problem has been fixed. The jobs now run correctly.
Back to Top
SQL Server Management Objects (SMO)
- Reliability has been improved in the SMO scripting and transfer features.
- Previously, SMO may have produced incorrect Transact-SQL statements in some scenarios. Additionally, SMO may not have transferred a database in some scenarios. These problems have been fixed.
Back to Top
SQL Server Management Studio
- In SQL Server 2000 Compatibility mode, you can now install database diagram support objects on databases.
- A new Database Mirroring Monitor has been added. For more information, see Database Mirroring Monitor Overview.
Back to Top
See Also
Concepts
New and Updated Books Online Topics (14 April 2006)