Data Management

More New SQL Server 2008 Features

Microsoft updates SQL Server on a pretty regular basis. It's newest version, SQL Server 2008, includes some new features not found in older versions. Here's a list of some of them.

 In a previous article, I took a look at some cool new features included in SQL Server 2008.  Recently, the RTM (Release to Manufacturing) version of SQL Server has been released which means that it is time to look at some additional new features that have been included in the product.

Filestream Storage

As the name may suggest, Filestream enables the storage of unstructured data such as documents and videos.  This feature integrates with the NTFS files system by using the nvarchar(max) data type to store data on the file system.  This feature is great for when you're currently storing documents inside of your SQL Server system that are larger than 1 MB and fast read access is important.

Sparse Columns

Sparse columns are very similar to other types of database columns, except that they are specialized and optimized for null values.  If you have a table that holds a lot of NULL values, first consider your table design.  If your design is solid or cannot be altered, then your table may be a candidate to take advantage of sparse columns.  The rule from Microsoft is to consider using Sparse columns when 20-40 percent of the values in a column will be NULL.

New Date & Time Data Types

Prior to SQL Server 2008, there was no way to native data type to store time related data.  Time data was only available as part of the datetime (or smalldatetime) data types.  With SQL Server 2008, TIME is a separate data type, able to hold time values such as '23:59:59.9999999'.  Along with the new TIME data type, there are additional data types such as datetime2, which holds additional nanosecond data and the datetimeoffset data type, which can hold datetime data that is timezone aware.

Spatial Data Types

Two new data types are included in SQL Server 2008; geometry and geography.  These data types hold information regarding the physical location and shape of geometric objects, which allow for applications to be built that are geographically aware.

Dependency Reporting

Reporting dependencies has never been consistent or reliable in SQL Server.  The difficultly is that when objects are added, modified or dropped, special actions must be taken by the database engine to ensure that the dependency chain is correct.  In 2008, new dynamic management views are included keep track of these dependencies so that reliable reporting can be done one these objects.

Advanced Auditing Features

SQL Server 2008 includes several new features that facilitate the auditing of data.  These features include a new Auditing feature that allows you to easily create customized audits of database engine events.  Change Data Capture, while not auditing specific, makes data changes typically used for loading a data warehouse easily available in an easily used format.  SQL Server 2008 also includes a Data Collector feature that allows the DBA to gather and compare data that is gathered from several different sources.

Policy Based Administration

This is one of my favorite features of SQL Server 2008.  This feature allows a database administrator (DBA) to easily and uniformly set policies across servers that ensure system rules are met.  These policies can be rules such as simplifying administration tasks, preventing unauthorized system changes, making sure code compliance is met, ensuring best practices are met.

TSQL Intelli-sense

SQL Server Management Studio 2008 includes Intelli-sense which is a familiar feature in other Visual Studio products.  This feature presents a popup box when typing SQL statements to give options to choose columns from tables that are involved in your queries.

Central Management Servers

This feature allows the database administrator (DBA) to administer multiple SQL Servers easily and efficiently.  These servers allow for multi-server queries and for policy based management policies to be executed against multiple servers at the same time.

Compound Operators

This form of syntactic sugar is present in many other programming languages such as C++ or C#.  Consider the following TSQL statement.

SET @x = @x + 1

Using compound operators, the above statement can be rewritten in the following manner:

SET @x += 1

This statement, while not groundbreaking, takes TSQL a step forward in the evolution of programming languages.

Transparent Encryption

As data becomes more and more sensitive, the ability to keep the data secret becomes more important.  SQL Server 2008 includes the ability to encrypt data stored in the database transparently, which keeps you from having to alter applications to take advantage of the feature.  This feature prevents unauthorized users from reviewing the data even if they have the backup files.

More to Come

Today I took a look at some great additional features included in SQL Server 2008.  In the following weeks I will take a more in-depth look at each of these new features so that when you do decide to upgrade your database system to SQL Server 2008 you are equipped to hit the ground running in implementing these new features.

About

Tim Chapman is a SQL Server MVP, a database architect, and an administrator who works as an independent consultant in Raleigh, NC, and has more than nine years of IT experience.

5 comments
parksdevcorp
parksdevcorp

I remember learning the += operator in college in 1979. it's 2008 and SQl finally has it. Better late than never. :~)

stevew
stevew

When Change Data Capture isw used on an encrypted table, is the captured changes encrypted as well?

chapman.tim
chapman.tim

Sorry about that guys...that actually wasn't my comment. I agree with you...the release schedule for SQL Server is erratic at best. They try to release a new product every 3 years, but with 2005, they were way over schedule. 2008 is actually more on schedule, but because 2005 was released so late, it seems off. Anyways, I appreciate the comments.

david.tolosana
david.tolosana

Really? I always found Microsoft's lack of consistency on their relase cycle one of SQL Server's biggest drawbacks.

xyvyx
xyvyx

I haven't even read the whole article and I thought almost the exact same thing! On one hand, MS has released a large number of cumulative updates for SQL 2005... but they're not tested to the same degree as a servicepack, so I've been hesitant to put them on any of my production servers. Their own guidelines even state to not apply them to a machine unless it addresses a specific issue you're running into. I think some of the MS beancounters must have pushed them into a 3-year product cycle over the previous 5-year cycle. Hence, SQL 2005 was pretty much ignored while they were busy developing SQL '08. I'd call 2008 half a servicepack, half new features. The spatial & encryption features are the most appealing to me. I like the Intelli-sense, but it needs some configurable options.. ie: only include columns, system functions, table names, etc. Also, would it be that hard to work / partially work with SQL 2005? Speaking of which, the new activity monitor won't work with SQL 2000... so you'll have to keep good 'ole Enterprise Mangler around for awhile longer!

Editor's Picks