SQL Server 2008 Interesting Features

Extracted from an article on DatabaseJournal.com.

Theoretically, SQL Server 2008 will be released in Q1 of 2008, along with a new version of Visual Studio and Windows.

Data Compression

According to Microsoft, while using compression will slightly increase CPU usage, overall system performance will be improved by reducing I/O and increasing buffer-hit rates. Data and indexes can both be compressed, and it doesn’t affect other features.

Performance Data

More perf counters have been added. The data collected by the counters is stored in a centralized data warehouse. Microsoft states that running the default set of performance related monitors will consume less than 5% of CPU and memory resources.

Performance Dashboard: tool that can read saved performance data. Historical and baseline comparisons can be made and used to create action triggers. For example, if memory use exceeds a threshold for more than five minutes, a more detailed data collection can be automatically triggered.

Performance Studio: a collection of performance tools. Together they can be used for monitoring, troubleshooting, tuning and reporting. The Data Collector component of the studio is configurable and low overhead. It supports several collecting methods, including queries, traces, and perf counters. Data can also be collected programmatically. Once data is collected, there are drill-down and aggregate reporting options, including suggestions for potential performance tuning.

Installation

The installer will be able to retrieve updates from Microsoft and install SQL Server, service packs, and patches as a single step. Service packs will be uninstallable.

Data Synchronizing Features

SQL 2008, Visual Studio, and ADO.NET bring together new methods of creating disconnected applications which synchronize with a central database. SQL 2005 started by providing support for change tracking by using triggers. SQL 2008 synchronizing is better integrated and optimized.

Dates and Times

New data types in SQL 2008.

  • Date: date only, no time.
  • Time: time only, no date.
  • Date Time Offset: “UTC time-zone aware value” (whatever that means)

Table-Valued Parameters

It’s sort of a hack, but you create a UDT which is a table and pass variables of that type into sprocs.

Merge

A new TSQL command, MERGE, allows a single statement to UPDATE, INSERT, or DELETE a row depending on its condition. The example below demonstrates the MEGRE being used to combine a new list of Inventory Item descriptions into the existing Inventory Master. In addition to new Descriptions, there are some new parts included in the NewInventory table. Without the Merge statement, two commands would need to run. The first would look for a match then upgrade the Description. The second statement would look for non matches and then INSERT. With MERGE, one statement can perform both tasks as shown below.

MERGE InventoryMaster AS im
USING (SELECT InventoryID, Descr FROM NewInventory) AS src
ON im.InventoryID = src.InventoryID
WHEN MATCHED THEN
UPDATE SET im.Descr = src.Descr
WHEN NOT MATCHED THEN
INSERT (InventoryID, Descr) VALUES (src.InventoryID, src.Descr);

Leave a Reply

Your email address will not be published. Required fields are marked *


*