News

SQL Server 2008's Developer Appeal

Features a smooth upgrade from SQL Server 2005 and developer perks such as T-SQL and encryption.

• By Joshua Jones
• 09/01/2008

Almost five years had passed the last time Microsoft released an upgraded version of its flagship SQL Server database back in 2005. While SQL Server 2005 was warmly received by IT pros, Microsoft vowed to never again allow so much time to elapse between releases. As promised, the company last month officially delivered SQL Server 2008.

While the latest version of SQL Server is a less-ambitious upgrade than that presented by the 2005 cycle, there are a number of significant improvements across the board. Everything from resource governing to policy-based management has many database admins anxious to upgrade. However, some of the most dramatic changes and additions are focused squarely on developers. Having spent many months evaluating the community technology previews of SQL Server 2008, developers will find many of these improvements worth delving into.

T-SQL Turn On
For developers who spend a large portion of their time writing Transact-SQL (T-SQL), there are a couple key additions that will make life significantly easier. These are table-valued parameters and the new MERGE syntax.

Table-valued parameters are, simply, parameters that can be passed into a stored procedure or function that contains multiple rows of data. Developers who have written a stored procedure that gets called over and over again to perform the exact same operations on multiple records will see this as the solution to that problem. They also help eliminate or reduce temp tables in many situations.

Writing table-valued parameters is a little awkward for pure T-SQL developers, but not really that difficult to learn. Essentially, a user-defined data type must be created that actually contains the definition of the "table." Then, the table must be populated with data. Once populated, the table is read-only when referenced in any other code -- you can't perform data manipulation language, or DML, operations against a table-valued parameter in the code that's referencing the parameter. Once done, the table-valued parameter can be passed into a stored procedure or function.

Using a table-valued parameter, developers can extract a number of records (or build a record set) directly inside of T-SQL, and then pass that record set into a single stored procedure to apply business logic. This is much more graceful than previously available solutions.

The other T-SQL-related addition in SQL Server 2008 is the new MERGE statement, which lets developers perform insert, update or delete operations in a single statement. That allows the developer to join a data source with a target table or view, and then perform actions against the target object based on the results of the initial join. Essentially, MERGE lets developers compare data in a table against another data set, and then insert, update or delete, conditionally, the data in the target table.

Before MERGE, we typically had to iterate through record sets, comparing source data against new data, or build a query to update existing records, insert new records or delete old records one at a time.

Dishing on Data Types
SQL Server 2008 also introduces a veritable buffet of new data types. These include everything from new versions of old data types to brand-new data types to ease the support of certain complex applications. New to the updated database are the Date and Time data types, which finally depart from the classic datetime data type of the previous version. The result is that developers enjoy a few new options when storing date and time data.

Notable are the Datetime2 and Datetimeoffset types. Datetime2 stores datetime at higher resolution (100 nanosecond increments versus rounded to .000, .003 or .007) and uses a larger overall date range (starts at 0001-01-01 versus 1753-01-01). The Datetimeoffset type stores datetime data with the same precision and accuracy as Datetime2, but offers the ability to specify an hh:mm offset. The likely uses of this are for tracking date and time conversions across time zones.

Other data types and resources address streaming, hierarchical and spatial data. The FILESTREAM storage method is not technically a data type, but it allows SQL Server applications to store unstructured data such as images and documents directly on the file system. Applications can take advantage of new streaming APIs to access the file system for data, while maintaining transactional consistency with the structured, relational data that corresponds to the file being accessed. Another new data type, called Hierarchyid, manages handle hierarchical relationships within single tables. An easy example is an organizational chart. The Hierarchyid data type allows complex hierarchical relationships within the table without special query needs or extra columns.

Inside SQL Server 2008 Microsoft's new database server offers the following new features, which should appeal to developers: Transact-SQL (T-SQL) Improvements Table-valued parameters: Developers can extract a number of records (or build a record set) directly inside of T-SQL, and pass the set into a single stored procedure to apply business logic. MERGE statement, which lets developers perform insert, update or delete operations in a single statement. New Data Types Date and Time: Developers can take advantage of new options when storing date and time data such as time zone management. Hierarchyid: Manages hierarchical relationships within single tables. Spatial data: Lets developers build geographically oriented applications with less overhead to manipulate data. Securing Data Transparent Data Encryption (TDE): Performs real-time I/O encryption and decryption of data moving between the data files and log files on disk using AES and 3DES encryption algorithms. Extensible Key Management (EKM): Extends SQL Server's ability to secure data by using externally managed encryption keys.

Perhaps the most exciting new feature is the support for spatial data types. These introduce the capability to build geographically oriented applications with less application overhead to manipulate data. Spatial data is implemented with two different data types: Geometry and Geography. The Geometry data type stores planar-spatial data, using a flat coordinate system (Euclidean). This is your distance and directional data. The Geography data types stores data in the round-earth system. This is your latitude and longitude information.

Securing SQL Server
Securing data continues to pose a growing challenge to developers and administrators alike. There are more and more government regulations around how data is accessed and stored. Additionally, customers and users are becoming more reliant on the security of systems that contain their personal data, even as hackers become more motivated than ever to get at that data. SQL Server 2008 introduces a few new features to help both application developers and administrators ensure that their data is secure. Specifically, these features are Transparent Data Encryption (TDE) and Extensible Key Management (EKM).

TDE introduces a new layer of encryption in the SQL Server world. TDE secures data by using either a locally stored symmetric key or an asymmetric key protected outside of the database server. What TDE does is actually encrypt the data at rest (on disk or tape). It performs real-time I/O encryption and decryption of data moving between the data files and log files on disk. Using AES and 3DES encryption algorithms, it introduces a new layer of security and compliance into any application without requiring any application-code changes.

Because the change is implemented at the server level, once TDE has been enabled and secured, the entire database will become encrypted, but operations inside the database continue as normal. Note that this won't protect data being transmitted from server to remote apps. It simply secures the data as it sits on the disk (or on tape in a backup file).

EKM, meanwhile, extends SQL Server's ability to secure data by using externally managed encryption keys. This means that if an organization uses a hardware-based security module (HSM) or even an external software-based security module, those modules can be registered with SQL Server so that it can use those keys to encrypt and decrypt data on the fly. When used in conjunction with TDE, it's possible to completely secure all data on SQL Server, as well as the incoming and outgoing data.

A number of major advances were made for app developers when SQL Server 2005 was released, and a whole new set was introduced when Visual Studio 2008 went live. These include technologies like Language Integrated Query (LINQ), and ADO.NET Entity Framework.

Now, with SQL Server 2008, developers have a slew of new tools inside the database to help them store and manipulate data more efficiently, and even handle types of data they've had to keep outside the database until now. Now that the product has finally been released to manufacturing, it's worth trying -- as it looks to be Microsoft's best database product to date.

Click here to read "SQL Server 2008 Is Here."