What's new in SQL 2008 Part 2

September 7, 2007

Introduction

This article will highlight some of the new features and benefits found in SQL Server 2008. Some of the new features include Development changes, new Business Intelligence features, Integration additions, and new Data Types. Listed below are some of the items covered that were covered in Part 1 of this series.

• Encryption - Transparent Data Encryption, which enables an entire database to be encrypted. Backup Encryption for secure database maintenance. And lastly External Key Management.
• Auditing of data changes.
• Data Compression for Fact Table size reduction.
• Resource Governor - The Resource Governor can be used to trigger an event or stop a runaway or resource intensive process.
• Performance Data - There is a new Performance Dashboard tool that can read saved performance data. In addition, there are new reports, monitoring, and tuning options.

SQL Server 2008 will be released approximately February of 2008, along with a new version of Visual Studio and Windows. A CTP (Community Technology Preview) of SQL 2008 is currently available for download from the Microsoft URL http://www.microsoft.com/sql/prodinfo/futureversion/default.mspx.

Dynamic Development

SQL 2008 leverages the new Dot Net Framework 3.0 with LINQ (Language Integrated Query). In addition, there is more efficient support for Business Data Entities along with data synchronization options. Also, there are new ADO and Visual Studio development options. Collectively, these are labeled Dynamic Development and are reviewed below.

Entity Data Services

SQL Server 2008 and ADO.NET now allow for high level business objects to be created, such as Customers or Parts. These entities can be used rather than the standard method of returning individual rows and tables. If you’re using E-R (entity relationship) modeling, your objects in SQL will now match your modeling. There are several new ADO.NET frameworks that can access these entities such as the Line-of-Business (LOB) framework and the Entity Query Language (eSQL).

LINQ

LINQ provides a standard development syntax for accessing data, regardless of where the data resides. For example, the same syntax can access either SQL Server or XML data. LINQ is used rather than TSQL inside the application language, such as C# or VB.

Data Synchronizing Features

The combination of SQL 2008, Visual Studio, and ADO.NET bring together new methods of creating synchronizing or frequently disconnected applications, making it easier to create client applications that 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.

Beyond Relational Databases

These next groups of features are collectively grouped as “Beyond Relational”. They include new location, geometry, data and time data types. In addition, there are new Full Text and File Stream options built into SQL Server 2008.

Large UDT

Previously, in SQL 2005, User Defined Types (UDT) could not be larger than 8,000 bytes. In SQL 2008 there is no longer any size restriction, allowing storage of very large UDTs.

Dates and Times

There are new Date and Time data types in SQL 2008.

• Date. This is a data type with a date only, no time.
• Time. A Time data type without a date component. Precision can be up to 100 nanoseconds.
• Date Time Offset. This data type will store a Universal Coordinated Time (UTC) time-zone aware value.

File Stream

The new data type VarBinary(Max) FileStream allows for a way to manipulate binary data using TSQL Select, Insert, Update, and Delete statements. In the past, to store binary data a BLOB, accessed by a Dot.Net application was typically used. Now, SQL functions such as triggers, Full Text Search, and backup restore can be applied to binary data.

Spatial Data

The new Spatial Data type allows Latitude, Longitude, and GPS-based data entries to be natively stored inside SQL Server. The data type conforms to several industry standards such as Open Geospatial Consortium (OGC) Simple Features for SQL and ISO 19125 Simple Feature Access.

Table Value Parameters

In previous versions of SQL Server, there wasn’t a native way to pass a table to a stored procedure. The usual workaround was to pass a large varchar or XML type and parse through it. Now, in SQL Server 2008, Table Parameters are available. The following provides a simple example of passing a table into a Stored Procedure.

CREATE TYPE PartType 
AS Table (PartID varchar(50), Descr varchar(100), createdate datetime);


CREATE PROCEDURE AddPart(@PartList PartType READONLY)
AS
SELECT * FROM @PartList 


DECLARE @PartTable PartType;
INSERT INTO @PartTable values('Part1', N'Table Test', '2007-08-20');
EXEC AddPart @PartTable

Full Text Search

There are Full Text Search changes in SQL Server 2008 including native indexes, thesaurus files stored as metadata, and the ability to perform a Backup.

Reporting Server

Memory management in SQL Server 2008 Reporting Service is improved. So running large reports will not consume all available memory. In addition, report rendering has more consistency than before.

SQL 2000 Support Ends

As explained in Part 1 of this series, Mainstream Support for SQL 2000 is coming to an end in April 2008. This includes the CE version.

Conclusion

SQL Server 2008 has many practical and useful improvements. The new Date and Time data types will help simplify some applications. Listed below is a summary of the features and improvements reviewed so far:

• Transparent Data Encryption allows for an entire database, all tables and data, to be encrypted on the fly without application programming.
• Backups can be encrypted to prevent data disclosure or tampering.
• Data changes and access can now be audited.
• Fact Tables can be compressed for performance benefits.
• The Resource Governor can prevent runaway resource usage.
• SQL 2008 supports Hot Plug CPU.
• Performance Counters have been greatly expanded.
• Installation has been simplified.

In Part 3 of this series, we’ll cover the following SQL Server 2008 topics:

• Data Integration Features such as the MERGE statement, Parallelism, SSIS multiple processor improvements, and look up performance improvements.
• Analysis Service Improvements including BI Stack performance, Scale out analysis, Block computations and Perspectives.
• Microsoft Office 2007 Integration such as Exporting Reporting Service reports as Word docs, SSRS format and font improvements, and the Office Tool Bar.

» See All Articles by Columnist Don Schlichting