New features of the SQL Server 2008 relational database engine

In SQL Server 2008, the relational database engine is relatively new, and this article will focus on the new features that application developers and database administrators are more concerned about.

New features related to developers:

Change Data Capture (CDC): variable capture, which captures new, modified, and updated SQL Server data tables and is available in an easy-to-use format. The CDC is a very important new feature in SQL Server 2008 that is useful for extracting updated data from the business database to the Data warehouse in a BI application. A special article will then be introduced to the CDC.

DDL triggers and Event notifications: In addition to DDL statements in SQL Server 2005, SQL Server 2008 can add triggers to stored procedures that are equivalent to DDL for certain behaviors.

The new merge statement in Merge:t-sql supports the addition/update/deletion of batch data, through the join of two table type data, the target table is modified by the source table data according to the different conditions of join.

table-valued Parameters: Support for new table-valued parameter types in stored procedures and functions that can be passed into stored procedures and functions in a single parameter.

Group BY enhancement: the Rollup,cube and grouping sets clauses are added to group by for data statistics after grouping.

Insert enhancement: Supports inserting multiple data in an INSERT statement.

Table hints Enhancements: Force seek is added to force the query optimizer to use only index seek.

HierarchyID data type: The new HierarchyID datatype is used for hierarchical data support, and SQL Server 2008 provides some basic functionality for hierarchical processing on HierarchyID types.

FileStream storage, which allows SQL Server to store unstructured data on a file system.

Plan guide enhanced. You can fine-tune the query plan by hints.

Spatial data support, storage, querying, indexing, etc. Microsoft joined the OGC (Open Geospatial Consortium) in 2007.

SSMs added IntelliSense support.

When you open a table in SSMs, the function to return top n is back.

Filtered Indexes and Statistics, which supports indexing and statistics on part of a table, rather than on all data.

Full-text indexing is tightly integrated into the relational database engine, rather than being provided by another process, as SQL Server 2005 does.

New features related to database Administrator (DBA):

Dynamic Management Views (DMVs): Multiple views of memory, dependencies are added to the dynamic management view.

Data Collector: Can be collected from different data sources.

Backup compression: Only versions of enterprise support back up compression, and all versions support recovery from compressed backups.

The declarative Management framework supports the management of server group, which is implemented through configuration Servers. When applied specifically, T-SQL can be supported simultaneously on n servers at the same time.

Add CPUs at run time. This feature can be maintained when hardware upgrades are available.

Table partition Exchange function. Large data warehouses are useful, and in SQL Server 2008, a more convenient table partitioning exchange call is provided through the real extension of ALTER TABLE.

Resource management, CPU and memory resources management.

Management adds extensible Key management and transparent data encryption.

A significant increase in server management and maintenance.

PowerShell Provider and cmdlets facilitate the administration of SQL Server 2008 in PowerShell.

Other enhancements to the system's functionality, but do not need to be explicitly used are:

Optimized Bitmap Filter:oracle has an explicit BITMAP type index, SQL Server dynamically uses BITMAP filtering in a parallel query plan, and can significantly increase the invalid records in the fact table under the Star schema (stars schema).

Database Mirror: Improves log performance and increases the ability to automatically fix incorrect data pages from partner. Enhances the performance of partitioned table queries.

Sparse data storage optimization.

Enhancements, some features are gradually removed or replaced, although still supported in SQL Server 2008, will fade away in future releases. In performance counters or traces, this part of the function is clearly marked out as obsolete.

Starting with SQL Sever2005, SQL Server is no longer just a database software, but the direction of the enterprise database platform, and SQL sever in Etl,olap,data mining,reporting,application There are many improvements in the direction of server and so on.