SQL Server 2008 What's New

http://www.microsoft.com/sqlserver/2008/en/us/whats-new.aspx
 

Enterprise Data Platform

Policy-Based Management Policy-Based Management enables the efficient management of multiple SQL Server instances from a single location. Easily create policies that control security, database options, object naming conventions, and other settings at a highly granular level. Policies can evaluate servers for compliance with a set of predefined conditions and prevent undesirable changes being made to servers. Additional Resources Administering Servers by Using Policy-Based Management SQL Server Policy-Based Management Blog Management is centralized, thereby reducing the need to configure each server separately Administration is simplified, reducing the effort required to maintain standardization and compliance, even in complex environments Configuration is straightforward and can be done entirely within SQL Server Management Studio Out-of-the-box predefined policies make it easy to get started Backwards compatibility supports managing instances of SQL Server 2008, SQL Server 2005, and SQL Server 2000

Performance Data Collection (Data Collector) The Data Collector provides a convenient way to collect, store, and view performance data automatically. It collects disk usage, server activity, and query statistics data, which it loads in a management data warehouse and performance data can be reviewed in SQL Server Management Studio or by using third-party tools. Additional Resources Performance Data Collector for SQL Server 2008-Revealed Data Collector in DQL Server 2008 Troubleshooting the Data Collector Introducing the Data Collector Setup wizard makes configuration simple Data collection is automated by using set of SQL Server Agent jobs and SQL Server Integration Services packages Management is centralized so data collection can be easily configured and results can be viewed in one place Performance impact is minimal because the data collected can be cached and uploaded to the data warehouse later

Data Compression Data compression reduces the amount of storage space needed to store tables and indexes, which enables more efficient storage of data. Data Compression does not require changes be made to applications in order to be enabled. Additional Resources Backup Compression (SQL Server) SQL Server Storage Engine Save disk storage Enable compression option for individual tables or indexes Configuration is easy using the Data Compression wizard Applications do not need to be reconfigured as SQL Server handles compression and decompression of data Compression can improve disk I/O and memory utilization

Resource Governor The Resource Governor enables administrators to control and allocate CPU and memory resources to high priority applications. This enables predictable performance to be maintained and helps avoid performance from being negatively affected by resource-intense applications or processes Additional Resources Introducing Resource Governor Resource Governor Concepts Troubleshooting Resource Governor Prioritize applications, users, and computers competing for the same resources Prevent runaway queries that hold resources for extended periods of time Limitations are not enforced when there is no competition for resources

Transparent Data Encryption Transparent Data Encryption enables data to be stored securely by encrypting the database files. If the disks that contain database files become compromised, data in those files is protected because that data can only be de-encrypted by an authorized agent. SQL Server performs the encryption and de-encryption directly, so the process is entirely transparent to connecting applications. Applications can continue to read and write data to and from the database as they normally would. Backup copies of encrypted database files are also automatically encrypted. Additional Resources SQL Server 2008: Transparent data encryption feature - a quick overview Understanding Transparent Data Encryption SQL Server Encryption Implements strong encryption keys and certificates to secure data Applications do not need to be modified to support Transparent Data Encryption Enables compliance with data privacy regulations Does not increase the size of the database

External Key Management / Extensible Key Management External Key Management enables certificates and encryption keys to be stored using third-party hardware security modules that are designed specifically for this purpose. Storing the keys separately from the data enables a more extensible and robust security architecture. Additional Resources Understanding Extensible Key Management SQL Server and Database Encryption Keys (Database Engine) How to: Create a Database Master Key Helps organizations comply with data privacy regulations Reduces administration requirements when there are multiple keys and certificates to manage Standard interface supports third party hardware security modules

Data Auditing Data Auditing provides a simple way to track and log events relating to your databases and servers. You can audit logons, password changes, data access and modification, and many other events. Tracking these events helps maintain security and can also provide valuable troubleshooting information. The results of audits can be saved to file or to the Windows Security or Application logs for later analysis or archiving. Additional Resources SQL Server 2008 Auditing SQL Server Audit Action Groups and Actions Enables compliance with security regulations Simple configuration using SQL Server Management Studio Minimal impact on performance because audit data is stored outside of SQL Server database files

Hot-Add CPUs and Hot-Add Memory Hot-add CPUs, a feature available with the 64-bit edition SQL Server Enterprise, allows CPUs to be dynamically added to servers as needed, without the need to shut down the server or limit client connections. Hot-add memory enables memory to be added in the same way. Additional Resources Hot-Add CPU Hot-Add memory Managing memory for large databases Dynamically add memory and processors to servers without incurring downtime

Streamlined Installation The SQL Server 2008 installation process has been streamlined to be easier and more efficient. Individual SQL Server components, such as Database Services, Analysis Services, and Integration Services, can be optionally selected for installation. Failover cluster support configuration has also been added to the installation. Additional Resources How to install SQL Server 2008 SQL Server 2008 Installation feedback Easier to install SQL Server Setup now advises of configuration problems such as installation pre-requisites, which helps streamline the installation process

Server Group Management Server Group management enables T-SQL queries to be issued against multiple servers from a single Central Management Server, which simplifies administration. Stream results of multi-server queries into a single result set or into multiple result sets enables the option of evaluating policies against a server group. Additional Resources SQL Server Policy-Based Management Management is centralized, so servers do not need to be configured individually Provides a simpler administration model for policy evaluation

Upgrade Advisor The Upgrade Advisor generates a report that highlights any issues that might hinder an upgrade. This provides administrators detailed information that can be used to prepare for upgrades. Additional Resources Using upgrade advisor to prepare for upgrades Version and edition upgrades Upgrading to SQL Server 2008 SQL Server Upgrade Advisor Helps streamline the upgrade process by identifying upgrade issues in advance Can be used to analyze both local and remote systems Upgrade Advisor reports provide links to technical information that assist in addressing upgrade problems

Partition Aligned Indexed Views Indexed Views let SQL Server persist the results of a view, instead of having to dynamically combine the results from the individual queries in the view definition. Indexed Views can now be created to follow the partitioning scheme of the table that they reference. Indexed views that are aligned in this manner do not need to be dropped before a partition is switched out of the partitioned table, as was the case with SQL Server 2005 indexed views. Additional Resources Partitioned switching when indexed views are defined Transferring data efficiently by using portioning switching Indexed views improve performance with very large partitioned tables, such as fact tables in data warehouses.

Backup Compression Backup compression enables the backup of a database to be compressed without having to compress the database itself. All backup types, including log backups, are supported and data is automatically uncompressed upon restore. Additional Resources Backup Compression (SQL Server) SQL Server 2008 Back-up Compression PSS SQL Serve Engineers Save storage space Compressed backups can be stored on tape or on disk Simple configuration using SQL Server Management Studio Default state of all backups on a server to be compressed can be configured

Extended Events The extended events infrastructure provides an in-depth troubleshooting tool that enables administrators to address difficult-to-solve problems more efficiently. Administrators can investigate excessive CPU usage, deadlocks, and application time outs as well as many other issues. Extended events data can be correlated with Windows events data to obtain a more complete picture that will aid in problem resolution. Additional Resources SQL Server Extended Events Using SQL Server Extended Events Provides detailed information about low level events in SQL Server Reduces the time required to troubleshoot complex problems Provides access to event data that has previously been difficult to obtain

Dynamic Development

Grouping Sets Use GROUPING SETS to obtain results similar to those generated by using CUBE and ROLLUP, however GROUPING SETS is more flexible, offers better performance, and is ANSI SQL 2006 compliant. GROUPING SETS enables the GROUP BY clause to generate multiple grouped aggregations in a single result set. It is equivalent to using UNION ALL to return a result set from multiple SELECT statements, each of which has a GROUP BY clause. Additional Resources Grouping Sets in SQL Server 2008 Grouping Sets equilvalents Troubleshooting Group by error Simplifies the process of writing basic reports that include aggregates Offers better performance and flexibility compared with other ways of accessing the same data

MERGE Operator The new MERGE operator streamlines the process of populating a data warehouse from a source database. For example, rows that get updated in the source database will probably already exist in the data warehouse but rows that are inserted into the source database will not already exist in the data warehouse. The MERGE statement distinguishes between the new and updated rows from the source database so that the appropriate action (insert or update) can be performed against the data warehouse in one single call. Additional Resources Clustered Index Merge Showplan Operator SQL Server 2008 - Merge Capability Using MERGE in Integration Services Packages MERGE (Transact-SQL) Inserting, Updating, and Deleting Data by Using MERGE Optimizing MERGE Statement Performance Provides a streamlined mechanism for performing 'UPSERT (INSERT and UPDATE) operations Reduces the need to use other less performance-efficient methods such as Lookup transformations, to achieve the same functionality Provides an efficient, granular level of control

LINQ Language Integrated Query (LINQ) is a .NET Framework version 3.5 feature that provides developers with a common syntax to query any data source from client applications. Using LINQ to SQL or LINQ to Entities, developers can select, insert, update, and delete data that is stored in SQL Server 2008 databases using any .NET programming language such as C# and VB.NET. Additional Resources LINQ to SQL and SQL Server 2008 Update on LINQ to SQL and LINQ to Entities Roadmap LINQ to SQL Enables developers to manipulate SQL Server databases using a variety of languages, not just Transact-SQL. Application developers do not need to understand Transact-SQL to access SQL Server databases Queries can be run against strongly typed objects, which minimizes runtime errors

Change Data Capture Use Change Data Capture (CDC) to track changes to the data in your tables. CDC uses a SQL Server Agent job to capture insert, update and delete activity. This information is stored in a relational table, from where it can be accessed by data consumers such as SQL Server 2008 Integration Services. Use CDC in conjunction with Integration Services to incrementally populate data warehouses, enabling you to produce more frequent reports that contain up-to-date information. It also allows sync-enabled mobile and desktop applications to perform efficient data synchronization between client and server, without requiring changes to the database. Additional Resources Change Data Capture Tables (Transact-SQL) Change Data Capture Stored Procedures (Transact-SQL) Change Data Capture functions (Transact-SQL) Change Data Capture SQL Server 2008 Simplifies the process of capturing changed data and making it available to consumers Provides relatively low impact on performance because triggers and replication is not required Improves accuracy of reports by making recent changes to data more readily available A SQL Server Agent job automatically cleans up CDC system tables so that they do not grow too large.

Table-Valued Parameters Table-Valued Parameters (TVPs) allows stored procedures to accept and return lists of parameters. Developers can write applications that pass sets of data into stored procedures rather than just one value at a time. Table-valued parameters make the development of stored procedures that manipulate data more straightforward and can improve performance by reducing the number of times a procedure needs to call a database. Additional Resources Table Valued parameters—SQL server 2008 Introducing Table-Valued Parameters - Part 1 Table Valued Parameters (Database Engine) Table Valued parameters (ODBC) Eliminates the need to use less efficient methods of processing data sets, such as passing XML data into stored procedures Reduces complexity and simplifies the process of working with data sets for developers

ADO.NET Entity Framework and the Entity Data Model SQL Server 2008 databases store data in a relational format, but developers typically access the data they contain by using an application that was developed in an object-oriented programming language. Creating such applications can be made more complex if you need to build knowledge of the underlying database schema into the applications. The ADO.NET Entity Framework allows a database to be abstracted and modeled into business objects, or entities, which can be more efficiently used by object-oriented programming languages such as C# and VB.NET. Applications can then use LINQ to query these entities without having to understand the underlying physical database schema. Additional Resources Why use the Entity Framework? ADO.NET Entity Framework Design: N-Tier Improvements for Entity Framework ADO.NET Entity Framework FAQ ADO.NET Team Blog Entity Framework (SQL Server Compact) Tutorial: Entity Data Source Control Simplifies the development of applications that require access to relational data sources Applications do not need to be modified in response to changes in the database schema, so ongoing development effort is reduced Developers can visually design and edit Entities using the Visual Entity Designer from within Visual Studio 2008 SP1

Synchronization Services for ADO.NET Synchronization Services for ADO.NET enables developers to build occasionally connected systems (OCSs) such as personal digital assistants (PDAs), laptop computers, and mobile phones to synchronize with server based databases. Users can work with a copy of the data that is cached on their local device and then synchronize changes with a server when a connection becomes available. Additional Resources Sample-SQL Express Client Synchronization using Sync Services for ADO.NET More efficient than replication for some scenarios Only incremental changes are copied, which reduces the amount of data sent and the time it takes to synchronize Develop applications that use Synchronization Services by using Visual Studio 2008 Applications can synchronize directly with servers or through multiple intermediate tiers

CLR Improvements Common Language Runtime functionality in SQL Server 2008 has been improved in several areas. User-defined aggregates (UDAs) now support up to 2GB of data and can accept multiple inputs. User-defined types (UDTs) are, like UDAs, and also support up to 2GB of data. CLR table-valued functions now feature an optional ORDER clause in the CREATE FUNCTION statement, which helps the optimizer to run the query more efficiently. Additional Resources CLR User-Defined Types Large CLR User-Defined Types Use UDAs and UDTs with larger data since 8KB size limit restriction has been removed Table-valued functions with a predefined sort order generally execute faster

Conflict Detection in Peer-to-Peer Replication In a peer-to-peer replication scenario, all nodes in the replication topology contain the same data and any node can replicate to any other node, leading to the possibility of data conflicts. Use conflict detection to make sure that no such errors go undetected and that data remains consistent. Additional Resources SQL Server Replication How do I find tutorials (Replication)? Troubleshooting (Replication) Ensures that data remains consistent across nodes that have been configured to use peer-to-peer replication Has a minimal performance impact if applications are configured to write changes for specific rows to specific nodes, which helps to avoid replication conflicts occurring in the first place

Service Broker Priorities and Diagnostics Service Broker provides an asynchronous communication mechanism that allows servers to communicate by exchanging queued messages. Service Broker can be configured to prioritize certain messages so that they are sent and processed before other lower priority messages. Use the Service Broker Diagnostic Utility to investigate communication problems between participating Service Broker services. Additional Resources SQL Server Service Broker Getting Assistance (Service Broker) Troubleshooting (Service Broker) Priorities Service Broker messages easily Troubleshooting is made easier with the dedicated diagnostic utility.

ADO.NET Data Services Microsoft ADO.NET Data Services provides a data access infrastructure for Internet applications by enabling Web applications to expose SQL Server data as a service that can be consumed by client applications in corporate networks and across the Internet. Additional Resources ADO.NET Data Services on MSDN ADO.NET Data Services conceptual documentation on MSDN ADO.NET Data Services API Reference ADO.NET Data Services team blog ADO.NET Data Services support forum Whitepaper: Using ADO.NET Data Services Access SQL Server data from a service Simpler data access code that uses common Web protocols and techniques such as HTTP GET and PUT requests with a simple, stateless REST architecture. Interoperability across platforms, programming languages, and development frameworks through standard XML, JSON, and ATOM/APP data formats.

Beyond Relational

Spatial data with GEOGRAPHY and GEOMETRY data types New GEOGRAPHY and GEOMETRY data types allow spatial data to be stored directly in a SQL Server 2008 database. Use these spatial data types to work with location-based data that describes physical locations, such as longitude and latitude. GEOGRAPHY enables you to represent three-dimensional geodetic data such as GPS applications use. GEOMETRY enables you to represent two-dimensional planar data such as points on maps. Spatial data types help you to answer questions like 'How many of our stores are located within 20 miles of Seattle?' Additional Resources SQL Server 2008 New Data Types Spatial Indexing Overview SQL Server 2008 Spatial Data types Types of Spatial Data SQL Server 2008: Using the Geometry Data Type to Work with 3 and 4 Dimensions Spatial Ed blog Store spatial data natively in SQL Server2008 databases Describe physical locations natively using industry standards such as WGS84 Integrate SQL Server 2008 with Microsoft Virtual Earth to display location data visually

Virtual Earth Integration Use the new spatial data types in SQL Server 2008 with Microsoft Virtual Earth to deliver rich graphical representations of the physical locations stored in a database. Use Virtual Earth support to create applications that display data about locations in desktop maps or web pages. For example, SQL Server 2008 makes it easy to show the locations of all company sites that are less than 50 kilometers from Denver. Additional Resources SQL Server 2008 and Virtual Earth Creating Heat Maps and Virtual Earth and SQL Server 2008 Spatial data types enable geographical data to be stored natively in SQL Server2008 databases Built-in library of industry-standard spatial functions streamlines development Provide meaningful representations of geographical data

Sparse Columns Sparse columns provide an efficient way to store NULL data in tables by not requiring NULL values to take up space. Applications that reference sparse columns can access them in the same way as they access regular columns. Multiple sparse columns in a table are supported by using a column set. Additional Resources Use Sparse Columns SQL Server 2008 Sparse Columns Sparse Columns Support in SQL Server Native Client Save significant storage space for tables contain lots of NULL data Define how NULL values are handled on a column by column basis provides granular control Filtered indexes built on sparse columns provide improved performance.

Filtered Indexes A filtered index is essentially an index that supports a WHERE condition and includes only matching rows. It is a non-clustered index that is created on a subset of rows. Because filtered indexes generally do not contain all rows in the table, they are smaller and deliver faster performance for queries that reference the rows it contains. Use filtered indexes to optimize performance for specific queries by ensuring that they contain only the rows referenced by the queries. Additional Resources Filtered Indexes Design guidelines SQL Server 2008 Filtered Indexes SQL Server 2008 Filtered Indexes vs. Index Views Improved query performance Reduced storage and maintenance costs

Integrated Full-Text Search Full text indexes enable queries to be performed for words and phrases on text stored in your databases. The Full-Text Engine in SQL Server 2008 is fully integrated into the database and full-text indexes are stored within database files rather than externally in the file system. This allows Full text indexes to be fully backed up and restored along with the rest of the database. Full-text indexes are also integrated with the Query Processor, so they are used more efficiently. Additional Resources Getting Started with Full-Text Search SQL Server 2008 Full-Text Search: Internals and Enhancements Best Practices for Integrated Full-Text Search Full Text Search (SQL Server) Search for words and phrases in text data more efficiently. Manage full-text indexes as part of the database, making administration simpler.

FILESTREAM Data FILESTREAM enables binary large object (BLOB) data to be stored in the Microsoft Windows NTFS file system instead of in a database file. Data that is stored using FILESTREAM behaves like any other data type and can be manipulated using T-SQL select, insert, update and delete statements. Unlike traditional BLOB storage, FILESTREAM data is logically shackled to the database while being stored efficiently outside the database in the NTFS file system. FILESTREAM data participates in all SQL Server transactions and backup operations, along with the rest of the database. Additional Resources FILESTREAM Overview Getting Started with FILESTREAM Storage FILESTREAM Data type: SQL Server 2008 Getting Transaction with SQL Server 2008 FILESTREAM SQL Server 2008 FILESTREAM FAQs Provides improved performance for read access since FILESTREAM data is managed directly by Windows NTFS No imposed maximum size for FILESTEAM data means you are only limited by the size of the volume that stores the data. BLOB data participates seamlessly in transactions, rollbacks and backup operations Storage and management of unstructured data is simplified

Large User-Defined Types (UDTs) Create user-defined types (UDTs) that go beyond the traditional data types supported to describe custom data types. UDTs in SQL Server 2008 are more extensible than previous versions since the 8KB size limit has been increased to 2GB. Note that the powerful new spatial data types GEOMETRY and GEOGRAPHY in SQL Server 2008 were developed using this new UDT architecture. Additional Resources CLR User-Defined Types Large CLR User-Defined Types SQL Server 2008 Spatial Data Types Create extensive, custom data types describe large, complex data Use a variety of .NET languages to create UDTs, including VB.NET and C#.NET Use familiar Visual Studio 2008 environment to create UDTs

Large User-Defined Aggregates (UDAs) SQL Server 2008 features a set of built-in aggregate functions that can be used to perform common aggregations such as summing or averaging data. Create custom, user-defined aggregates (UDAs) to manage custom aggregations. UDAs in SQL Server 2008 are more extensible than previous versions since the 8KB size limit has been increased to 2GB. Additional Resources CLR User-Defined Aggregates Introduction to New T-SQL Programmability Features in SQL Server 2008 Process larger amounts of data Use a variety of .NET languages to create UDAs, including C# and VB.NET Use familiar Visual Studio 2008 environment to create UDAs

DATE / TIME Data Types SQL Server 2008 introduces several new date and time based data types. DATETIME2 references the Coordinated Universal Time (UTC) instead of the system time for greater accuracy and can store date and time data to a precision of 100 nanoseconds. The new DATE and TIME data types enable you to store date and time data separately. The new DATETIMEOFFSET data type introduces time zone support by storing date, time and offset such as 'plus 5 hours'. Additional Resources DATETIME2 (Transact-SQL) DATETIMEOFFSET (Transact-SQL) DATETIME (Transact-SQL) SQL Server 2008 - Enhancements in Date and Time Data Types New SQL Server Date Data Types: DATE, TIME, DATETIME2 & DATETIMEOFFSET Greater flexibility for storing date and time data. Greater accuracy and precision for date and time data. DATETIME and SMALLDATETIME data types still supported.

Improved XML Support SQL Server 2008 features several XML enhancements including Lax validation, the DATETIME data type, and union functionality for list types all provide greater flexibility for defining XML schemas. XQuery includes support for the let clause, and the modify method of the xml data type now accepts xml variables as input for an insert expression. Additional Resources Using XML Data Guidelines and Limitations in Using XML Schema Collections on the Server Using XML with DATE, TIME, DATETIME2 & DATETIMEOFFSET Data Types Managing XML Schema Collections on the Server Greater integration for XML improves interoperability Improved options for defining xml schemas allow for more comprehensive xml data validation and promote data integrity Enhancements in the manipulation of xml data allow you to manage it more like you manage your relational data

ORDPATH Hierarchical data is organized differently to relational data, typically in the form of a tree. An example of hierarchical data is a typical organization chart that outlines the relationships between managers and the employees they manage. A column in a table that uses the HierarchyID data type contains data that describes the hierarchical relationships between rows explicitly in the form of a path. ORDPATH makes it efficient to program hierarchical data by using the HierarchyID data type. Additional Resources Using HIERARCHYID Data Types (Database Engine) HIERARCHYID (Transact-SQL) Working with HIERARCHYID Data Tutorial: Using the HIERARCHYID Data Type SQL Server 2008 - HIERARCHYID Part I SQL Server 2008 - HIERARCHYID - Part II Hierarchies with HIERARCHYID in SQL2008 Efficiently represent hierarchical relationships within relational table Make hierarchical relationships explicit HierarchyID has a set of methods that enable you to manage hierarchical data

Pervasive Insight

Fixed Query Plan Guides (Plan Freezing) Freezing Query Plans enables you to influence how the SQL Server query optimizer executes queries. SQL Server 2008 allows existing query execution plans to be imported. Plan Guide to force the query optimizer to always use a particular execution plan for a specific query. Using fixed query plans ensures that queries will be executed in the same way every time they run. Additional Resources Understanding Plan Guides Designing and implementing plan guides How to: Create a Plan Guide (SQL Server Management Studio) New Plan Guide Plan Guide Properties Freeze query plans on previous versions of SQL Server during upgrade to SQL Server 2008 to help ensure a predictable state of operation Ensure that query plans are retained when servers are upgraded You can revert to a known good execution plan if query performance degenerates

Star Join Query Optimization Data warehouses are often implemented as star schemas. A star schema has a fact table at its centre, which typically contains a very large number of rows. Star join query optimization can provide improvements in performance for queries that select a subset of those rows. When SQL Server processes queries using star join query optimization, bitmap filters eliminate rows that do not qualify for inclusion in the result set very early on, so that the rest of the query is processed more efficiently. Additional Resources Introduction to New Data Warehouse Scalability Features in SQL Server 2008 Significantly quicker query processing for data warehouses. Star join query optimization happens automatically, applications do not need to be configured to use it.

Enterprise Reporting Engine The reporting engine in SQL Server 2008 Reporting Services enables the pulling together of data from multiple heterogeneous sources from across an Enterprise. Large and complex reports can be produced in various formats, including list, chart, table, matrix, and tablix (a table/matrix hybrid). Access and manage reports through a Microsoft SharePoint Services site, simplifying administration, security, and collaboration, and making reports more easily available. Additional Resources SQL Server Reporting Services Getting Started Evaluate Reporting Services Reports can incorporate data from Oracle, SQL Server, DB2 and many other sources. Multiple report formats are available for making maximum visual impact of data Simplified administration and access to reports

Report Builder Enhancements Report Builder is an end-user tool for the creation and editing of reports. Report Builder in SQL Server 2008 has an interface that is consistent with Microsoft Office 2007 products, and because it masks the underlying complexity of report building, nontechnical users can create sophisticated reports with relative ease. Additional Resources SQL Server Report Builder What's new (Reporting Services) Report Builder 2.0 - Release Familiar interface eases the learning curve for end users Report layout templates and drag and drop functionality simplify the report creation process Users can preview, print and publish their reports from within Report Builder

Improving Rendering for Microsoft Office® Word and Excel Reports generated by SQL Server 2008 Reporting Services can be viewed and edited by using Microsoft Office Excel and Microsoft Office Word. The Excel rendering extension produces .xls files that are compatible with versions of Microsoft Office Excel from version 97 upwards. It offers improved options over previous versions, such as the rendering of subreports. The Word rendering extension, which new in SQL Server 2008 Reporting Services, produces .doc files that are compatible with versions of Microsoft Office Word from version 2000 upwards. Additional Resources An Insider's Look into the World of SQL Server Reporting Services Report Rendering Users can view and edit reports without having to install special software Reports are generated in a format that is backwards compatible More options for Microsoft Office Excel

Partitioned Table Parallelism Parallelism refers to using multiple processors in parallel to process a query, which improves query response time. On a multiprocessor system, SQL Server 2008 uses parallel processing when you run queries that reference partitioned tables. When SQL Server 2008 processes such a query, rather than allocating just one processor for each partition referenced by the query, it can allocate all available processors, regardless of the number of partitions referenced. Additional Resources Partitioned Tables, Parallelism & Performance considerations Partitioned Table in SQL Server 2008 Queries against partitioned tables run faster More efficient use of processor resources than in previous versions of SQL Server Operations involving partitioned indexes are optimized in the same way

IIS Agnostic Report Deployments Reporting Services in SQL Server 2008 does not depend on IIS to provide core functionality as it did in SQL Server 2005. Reporting Services can directly generate and deliver reports by accessing the HTTP.SYS driver directly. This has the effect of simplifying the deployment and management of Reporting Services in addition to offering better performance when generating larger reports. Additional Resources Deploying Reporting Services and Internet Information Services Side-by-Side How to: Configure a URL (Reporting Services Configuration) How to: Start Reporting Services Configuration Reporting Services Configuration How-to Topics No need to install IIS to use Reporting Services Installation and management of Reporting Services is streamlined More efficient use of resources cab result in quicker rendering of reports

Persistent Lookups SQL Server Integration Services packages use lookups to reference external data rows in the data flow. Lookup data flow transformations load the external data into cache to improve the performance of this operation. SQL Server 2008 Integration Services uses persistent lookups so that data loaded into the lookup cache is available to other packages, or to multiple pipelines within the same package, without the need to reload the cache. Additional Resources Getting optimal performance with Integration Services Lookups Helpful information and examples on how to use SQL Server Integration Services. Upgrading Lookup Transformations Integration Services Development Enhancements Improved performance for lookup transformations More efficient use of memory resources

Analysis Services Query and Writeback Performance Cell writeback in SQL Server Analysis Services enables users to perform speculative analysis on data. Users can modify specific data values and then issuing queries to see the effect of the changes. This can be useful for forecasting, for example. In SQL Server 2008 Analysis Services, the values that a user changes are stored in a MOLAP format writeback partition, which results in better query and writeback performance than the ROLAP format that was used in SQL Server 2005 Analysis Services. Additional Resources Introduction to New Data Warehouse Scalability Features in SQL Server 2008 More efficient storage of writeback data provides improved performance. MOLAP storage is used automatically, without the need for extra configuration.

Best Practice Design Alerts Good design is fundamental to creating optimal Analysis Services solutions. SQL Server 2008 Analysis Services uses Analysis Management Objects (AMO) warnings to alert you when the choices you make in your design deviate from best practice. Design problems are underlined in blue, similar to the way spelling mistakes are underlined in red in Microsoft Office Word. You can see the full text of warning by placing your arrow over the underlined object. You can disable AMO warnings if you choose. Additional Resources Powerful New Business Intelligence Capabilities Join SQL Server 2008 Provides real time guidelines to enable correct design from the start. Helps to comply with best practice. Helps inexperienced designers learn best practice.

Analysis Services Dimension Design Various new features in SQL Server 2008 Analysis Services contribute to improving and simplifying the dimension design process. Analysis Management Objects (AMO) warnings help ensure designs comply with best practice, the Attribute Relationship Designer is a visual tool for defining attribute relationships, and key column management is easier with the key columns dialog box. Additional Resources Best practices for Data Warehousing with SQL Server Avoid having 10 or more dimensions with a single attribute. Consider unifying them. Design process is more straightforward Attribute relationships are represented visually, making them easier to understand. Helps comply with best practice

Analysis Services Time Series Microsoft Time Series enables trends over time to be forecasted. For example, you can use it to predict product sales over the coming 12 month period. SQL Server 2008 Analysis Services includes the same algorithm for short term analysis that SQL Server 2005 Analysis Services used, and additionally introduces an algorithm for long term trend analysis. Both algorithms are used by default and you can also choose to use just one or the other. Additional Resources Time Series Prediction DMX Tutorial Mastering Time Series Prediction with SQL Server 2008 Data Mining Improved future trend analysis capabilities. Take account of seasonal variations when making predictions. Weight one algorithm over another to achieve either short or long term analysis.

Data Profiling SQL Server 2008 Integration Services includes the Data Profiling task, which enables the quality of data to be inspected before adding it to your databases. The task creates a profile that includes information such as the number of rows, NULL values, and distinct values that are present. Read profiles created by the Data Profiling task by using the Data Profile Viewer, and then clean and standardize the data as appropriate. Additional Resources Column Pattern Profile Request Options (Data Profiling Task) Profiling Data with the Data Profiling Task and Viewer How to: Use the Data Profiling Task (SQL Server Video) Data Profiling Task Enables a better understanding of your data Proactive approach to eliminating data quality issues Backwards compatible, supports data stored in SQL Server 2000, SQL Server 2005, and SQL Server 2008 databases.

 
 
 
 

---

Insert movie times and more without leaving Hotmail®. See how.