What's New in SQL Server 2008 Part 3
October 5, 2007
SQL 2008, with a scheduled release of Quarter 1 in 2008, includes a variety of new features, improvements, and additions. This article will review new Data Integration features, enhancements to Analysis Services, Reporting Server additions, and Office integration.
SQL Server Integration Services
SSIS (SQL Server Integration Services) is a built in application for developing and executing ETL (extraction, transformation, and load) packages. SSIS replaced SQL 2000 DTS. Integration Services includes the necessary wizards, tools, and tasks for creating both simple import export packages, as well very complex data cleansing operations. SQL Server 2008 SSIS includes a number of improvements and enhancements such as better parallel execution. In SSIS 2005, the pipeline didnt scale past two processors. SSIS 2008 will scale past two processors on multiprocessor machines. Also, the newly redesigned pipeline improves performance on large packages that contain long sub-trees. In addition, the SSIS engine is reported to be more stable with fewer incidents of deadlocks.
The Lookup component has been improved. Lookups are a very common SSIS operation that fetches a related piece of information. Such as a lookup obtaining the Customer Name from the CustomerID and brining that value into the dataset being worked on. Because Lookups are very common in SSIS and can be performed on large million row datasets, performance could be poor. Improvements have been made in SQL 2008 to increase performance. In addition, Lookups can be done on a variety of data sources including ADO.NET, XML, OLEDB, and other SSIS packages.
SQL 2008 includes the TSQL command MERGE. Using this statement allows a single statement to UPDATE, INSERT, or DELETE a row depending on its condition. The example below demonstrates the MEGRE being used to combine a new list of Inventory Item descriptions into the existing Inventory Master. In addition to new Descriptions, there are some new parts included in the NewInventory table. Without the Merge statement, two commands would need to run. The first would look for a match then upgrade the Description. The second statement would look for non matches and then INSERT. With MERGE, one statement can perform both tasks as shown below.
MERGE InventoryMaster AS im USING (SELECT InventoryID, Descr FROM NewInventory) AS src ON im. InventoryID = src. InventoryID WHEN MATCHED THEN UPDATE SET im.Descr = src.Descr WHEN NOT MATCHED THEN INSERT (InventoryID, Descr) VALUES (src. InventoryID, src.Descr);
A number of improvements and enhancements have been made to SSAS (SQL Server Analysis Server). The BI Stack has been improved for increased performance. Commodity hardware can be by utilized by scale out management tools. Also, Block Computation can provide significant performance improvements in cube analysis.
Processing and performance have been improved in SSRS (SQL Server Reporting Server). Large reports will no longer consume all available memory. In addition, there is greater consistency between layout and render. Also, the TABLIX, a cross between a table and a matrix is included in SQL SSRS 2008. Application Embedding allows URLs in reports to point to a calling application.
Microsoft Office 2007
SQL Server 2008 can tightly integrate with Microsoft Office 2007. For example, in SQL Server Reporting Server reports can now export directly to Word. In addition, both Word and Excel can be used as templates for SSRS reports by using the Report Authoring tool. Excel SSAS performance has been improved and there is a data mining add-in.
SQL Server 2008 contains many new features and enhancements, a large numbers of which were not covered in this series. Additional information can be found at the main SQL 2008 Microsoft page: http://www.microsoft.com/sql/2008/default.mspx. Listed below is a concise bulleted list of the SQL Server 2008 features reviewed in this series.