The best new features in SQL Server 2008

Some new SQL Server 2008 features could make an upgrade more appealing. Get the details on the enhanced data engine, Reporting Services improvements and more.

SQL Server 2008 includes 61 new features—before even counting bug fixes and performance improvements. Detailing each feature at length would be difficult, so here are some features that are off the charts on the snazzy scale.

The enhanced data engine

The data engine has undergone significant improvements. One of the most exciting capabilities of SQL Server 2008 is the merge statement, which allows two data sets to be merged. For example, if I have a data set containing rows that I want to push into an existing table, I can use the merge statement to insert rows that don't exist in the destination table. It's then possible to update rows that exist in the destination table (See code below).

MERGE Production.ProductInventory AS target
USING (SELECT ProductID, SUM(OrderQty) FROM Sales.SalesOrderDetail AS sod
    JOIN Sales.SalesOrderHeader AS soh
    ON sod.SalesOrderID = soh.SalesOrderID
    AND soh.OrderDate = '2004-05-01 00:00:00.000'
    GROUP BY ProductID) AS source (ProductID, OrderQty)
ON (target.ProductID = source.ProductID)
WHEN MATCHED
    THEN UPDATE SET target.Quantity = target.Quantity - source.OrderQty,
         target.ModifiedDate = GETDATE();

The merge statement allows a high degree of performance and extensibility. For example, the merge statement could delete rows on the destination table that exist in the source table and destination table (See code below).

MERGE Production.ProductInventory AS target
USING (SELECT ProductID, SUM(OrderQty) FROM Sales.SalesOrderDetail AS sod
    JOIN Sales.SalesOrderHeader AS soh
    ON sod.SalesOrderID = soh.SalesOrderID
    AND soh.OrderDate = '2004-03-01 00:00:00.000'
    GROUP BY ProductID) AS source (ProductID, OrderQty)
ON (target.ProductID = source.ProductID)
WHEN MATCHED THEN DELETE;

Native compression permitted

SQL Server 2008 Enterprise Edition enables native compression on database and transaction log backups. SQL Server database and transaction log backups are invaluable on servers when:

  • Space is at a premium.
  • You need to minimize I/O write activity during a backup.
  • You want to reduce network bandwidth required when copying a database or transaction log backup across the network.

These database page and row compression capabilities also reduce storage requirements.

Better Reporting Services

The redesigned reporting services feature improves the Report Builder and the reporting templates within Visual Studio or Business Intelligence Development Studio (BIDS). I believe that these improvements are so robust that once you start building reports in Visual Studio 2008, you won't want to go back. Overall, Reporting Services gives developers more functionality and extensibility while also allowing them to generate reports. Microsoft has added several new report types, including:

  • Tablix: Combines a list, table and matrix report (Figure 1).

Figure 1 (click to enlarge)

  • New graphs and charts: Several new chart types, such as axis and formulas, as well as wizards give you greater control over properties.
  • Gauges: Allow you to display key indicators as a gauge and embed gauge controls alone in a report or in conjunction with other report types (Figure 2).

    Figure 2

You can render reports in Microsoft Word along with renders for .html, .xml, .csv, .tiff, .pdf and Excel. SQL Server 2005 also supported those formats. SQL Server 2008 includes support for nested data regions and sub-reports in Excel.

Previous versions of Reporting Services were designed to scale up to large numbers of concurrent users; however, Microsoft did not anticipate the need to generate very large reports such as those over 1,000 pages. Reporting Services 2008 is no longer dependent on Internet Information Services (IIS), so it can scale to even larger numbers of concurrent users while rendering large multi-page reports. DBAs can define thresholds for how much memory Reporting Services will consume on the machine it runs on.

Streamlined Analysis Services

The most exciting new features in SQL Server Analysis Services 2008 are the improvements made to the Design Wizards. The Cube and Dimension wizards have much more intelligence built into them, enabling them to autosuggest dimensions and hierarchies and give best practices warnings. The warnings help you design high-performance Analysis Services cubes. The wizards also are more streamlined in that they require fewer mouse clicks to build a cube.

SQL Server Integration Services savings

SQL Server 2008 Integration Services (SSIS) ships as a standalone component in SQL Server 2008. So you no longer have to install SQL on the same machine running Integration Services. Running SSIS on a dedicated machine improves performance and eliminates the need to buy multiple licenses.

The following components were also redesigned for optimal performance:

  • Lookup transformations have been enhanced.
  • ADO.net is the preferred method for accessing data; SSIS 2008 is optimized to use ADO.net. Previous versions were optimized to use ole-db and ODBC.
  • Pipeline improvements to the SSIS engine optimize scalability and offer better performance with a lower resource footprint.
  • The change data capture and change detection features improve the performance of incremental refreshes.

Data profiling allows you to check data quality to see, for example, how dirty your data is. This can be useful in detecting data that's in an unexpected format. For instance, you might expect data to contain St., Street, Dr., Drive, Ln., Lane—but not Rd. and Road. Data profiler displays such data inconsistencies so you can quantify occurrences and determine if it's a spelling mistake, a data-entry mistake or a legitimate but unexpected entry.

Continue to part three


UPGRADING TO SQL SERVER 2008
Justifying an upgrade

Scaling SQL Server 2008
Upgrading and installing SQL Server 2008

Part 1: Part 2: The best of SQL Server 2008 Part 3: Part 4:

ABOUT THE AUTHOR:   
Hilary Cotter, SQL Server MVP, has been involved in IT for more than 20 years as a Web and database consultant, and has worked with SQL Server for 11 years. Cotter is Director of Text Mining at RelevantNoise, dedicated to indexing blogs for business intelligence. Microsoft first awarded Cotter the Microsoft SQL Server MVP award in 2001. He is the author of A Guide to SQL Server 2000 Transactional and Snapshot Replication, published by Not While The Surf's Up Press, 2004.
This was first published in March 2010

Dig deeper on SQL Server Migration Strategies and Planning

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close