What’s New in SQL Server 2008

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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?’

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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