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.
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
for Fact Table size reduction.
Governor – The Resource Governor can be used to trigger an event or stop a
runaway or resource intensive process.
– 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
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
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.
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.
Offset. This data type will store a Universal Coordinated Time (UTC) time-zone
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.
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
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
CREATE TYPE PartType
AS Table (PartID varchar(50), Descr varchar(100), createdate datetime);
CREATE PROCEDURE AddPart(@PartList PartType READONLY)
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.
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.
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:
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.
Governor can prevent runaway resource usage.
supports Hot Plug CPU.
Counters have been greatly expanded.
In Part 3 of this series, we’ll cover the following
SQL Server 2008 topics:
Features such as the MERGE statement, Parallelism, SSIS multiple processor
improvements, and look up performance improvements.
Improvements including BI Stack performance, Scale out analysis, Block
computations and Perspectives.
2007 Integration such as Exporting Reporting Service reports as Word docs, SSRS
format and font improvements, and the Office Tool Bar.