What’s new in SQL 2008 Part 2

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