New features in SQL server 2008

Backup Compression:

This is introduced in Enterprise or above editions. Now sql server supports compressing backups. Compressed backup can be restored to any edition of sql server 2008.

Configuration Servers:

This is a new method of administering multiple servers. An instance of sql server is designated as a configuration server to maintain list of registered servers.

Change Tracking:

SQL Server now allows applications to obtain incremental changes to user tables by tracking changes, which enables developing synchronization applications is easier and faster.

FILESTREAM storage:

FILESTREAM in SQL Server 2008 enables SQL Server-based applications to store unstructured data, such as documents and images, on the file system. FILESTREAM integrates the SQL Server Database Engine with an NTFS file system by storing varbinary(max) binary large object (BLOB) data as files on the file system. Transact-SQL statements can insert, update, query, search, and back up FILESTREAM data.

Hot Add CPU:

SQL Server 2008 supports dynamically adding CPUs to a running system. Adding CPUs can occur physically by adding new hardware, logically by online hardware partitioning, or virtually through a virtualization layer.

Plan Guide Enhancements:

The sp_create_plan_guide stored procedure has been extended to accept XML Showplan output directly in the @hints parameter instead of embedding the output in the USE PLAN hint. This simplifies the process of applying a fixed query plan as a plan guide hint. You can create multiple OBJECT or SQL plan guides for the same query and batch or module. However, only one of these plan guides can be enabled at any given time.

Query Processing on Partitioned Objects:

SQL Server 2008 improves query processing performance on partitioned tables for many parallel plans, changes the way parallel and serial plans are represented, and enhances the partitioning information provided in both compile-time and run-time execution plans.

Partition Switching on Partitioned Tables and Indexes:

Partitioning data enables you to manage and access subsets of your data quickly and efficiently while maintaining the integrity of the entire data collection. Now you can use partition switching to quickly and efficiently transfer subsets of your data by switching a partition from one table to another.

Resource Governor:

Resource Governor is a feature that you can use to manage SQL Server workload and system resource consumption. Resource Governor enables you to limit the amount of CPU and memory that incoming application requests can use.

Extensible Key Management:

The Extensible Key Management (EKM) feature in the Enterprise, Developer, and Evaluation Editions of SQL Server 2008 allows third-party enterprise key management and hardware security module (HSM) vendors to register their devices in SQL Server.

Transparent Data Encryption:

Transparent Data Encryption introduces a new database option that encrypts the database files automatically, without needing to alter any applications.

Server Administration:

SQL Server 2008 introduces Declarative Management, a new policy-based management framework for the SQL Server Database Engine.

Spatial Data Storage, Methods and Indexing:

Spatial data represents information about the physical location and shape of geometric objects. These objects can be point locations or more complex objects such as countries, roads, or lakes.

SQL Server Management Studio Enhancements:

Transact-SQL Query Editor IntelliSense:

The Transact-SQL Editor now provides IntelliSense functionality such as word completion and error underlining. IntelliSense is provided for frequently used Transact-SQL elements.

Transact-SQL Error List Window:

SQL Server Management Studio includes an Error List window that displays the syntax and semantic errors generated from the IntelliSense code in the Transact-SQL Query Editor.

Tools Enhancements:

SQL Server Management Studio has the following improvements:

® In the Query Editor window, you can query multiple servers at the same time by opening query windows from registered server groups. The query results can be combined into a single results pane, or can be returned in separate results panes.

® You can access SQL Server Profiler from the Query Editor window, from the Query menu, the Query Editor toolbar, or by pressing CTRL+ALT+P.

® You can now open tables by using a Return Top n rows option.

® On the Tools/Option menu, you can specify the action that results by double-clicking tables in Object Explorer.

Transact-SQL Enhancements:

Lock Escalation Option - A new LOCK_ESCALATION option of ALTER TABLE allows you to disable lock escalation on a table.

Query Hints - Table hints can now be specified as query hints to provide advanced query performance tuning options.

hierarchyid Data Type - SQL Server 2008 introduces a new system-provided data type to encapsulate hierarchical relationships.

MERGE Statement - This new Transact-SQL statement performs INSERT, UPDATE, or DELETE operations on a target table based on the results of a join with a source table.

Table-Valued Parameters - The Database Engine introduces a new parameter type that can reference user-defined table types.

User-Defined Table Type - The Database Engine introduces a new user-defined table type that supports representing table structures for use as parameters in stored procedures and functions, or in a batch or the body of a stored procedure or function.

Compatibility Level - ALTER DATABASE SET COMPATIBILITY_LEVEL replaces sp_dbcmptlevel for setting the database compatibility level.

GROUPING SETS - The GROUPING SETS, ROLLUP, and CUBE operators are added to the GROUP BY clause.

Table Hints - The FORCESEEK table hint is added to provide advanced query performance tuning options.

Transact-SQL Row Constructors - Transact-SQL is enhanced to allow multiple value inserts within a single INSERT statement.

Data Collector:

SQL Server 2008 introduces a data collector that you can use to obtain and save data that is gathered from several sources.

Database Mirroring:

Log Performance Enhancements:

® Compression of stream data for which at least a 12.5 percent compression ratio can be achieved.

® Write-ahead on the incoming log stream on the mirror server.

® Improved use of log send buffers.

® Page read-ahead during the undo phase.

Automatic Recovery from Corrupted Pages:

A database mirroring partner running on SQL Server 2008 or later versions automatically tries to resolve certain types of errors that prevent reading a data page.

SQL Dependency Reporting Enhancements:

SQL Server 2008 introduces a new catalog view and system functions to provide consistent and reliable SQL dependency reporting.

SQL Server Extended Events:

SQL Server 2008 introduces SQL Server Extended Events, an event infrastructure for server systems.

Change Data Capture:

Change data capture is designed to capture insert, update, and delete activity applied to SQL Server tables, and to make the details of the changes available in an easily consumed relational format.

Optimized Bitmap Filtering:

The query optimizer can place bitmap filters dynamically in parallel query plans to improve the performance of queries against a star schema.

Dynamic Management Views:

There are five new dynamic management views to present memory information.

DDL Triggers and Event Notifications:

The class of events on which you can create DDL triggers and event notifications is expanded to include numerous stored procedures that perform DDL-like operations.