Programming SQL Server 2008 (2)

New Building Blocks: New and Enhanced Date & Time Types

In previous releases of SQL Server, the database exposed date and time data primarily through two data types: DATETIME and SMALLDATETIME (Table 1). In SQL Server 2005, these types began to show their age and developers began to bump into their limitations.

You can break down problems with the existing date/time types into five categories:

• Applications that work in terms of only date or only time data must implement a layer of abstraction on the server data often writing their own validation routines. While feasible to accomplish, this increases the burden on developers.
• Table column storage requirements for either date or time only are considerably less than combined date and time. This means that as the size of a database increases, storage costs increase at a rate faster than necessary.
• Existing ranges are often not large enough to represent the data that applications need to handle (like process control and manufacturing).
• You cannot commonly represent time-zone data. Some applications choose to work around this by defining an additional column for the offset and storing date/time in UTC. Along with that solution comes the baggage of performing all the necessary calculations to treat this as a single type and the inability to straightforwardly take advantage of built-in date/time functions in SQL Server.
• Many other database vendors (and the ANSI SQL standard) support unique date, unique time, and time-zone aware date/time types, such that, migrating an application using this functionality to SQL Server was sometimes a cumbersome process that might even require changing application requirements.

In order to address these problems, SQL Server 2008 introduces support for four new types: DATE, TIME, DATETIME2, and DATETIMEOFFSET; along with a rich set of built-in T-SQL function support for the new types:

• DATE. Provides a broader range of day value than DATETIME by starting at 1/1/1 rather than 1/1/1753, and provides better storage scalability.
• TIME. Also provides storage scalability over existing types and introduces user-defined, fractional-second precision from 0 to 7. In other words, based on your needs you can define a table column of type TIME to be accurate to the second or 100 nanoseconds and you’ll pay only for the accuracy you need in storage costs.
• DATETIME2. A composition of DATE and TIME in that it supports both a wider range and a user-defined, fractional-second precision to 100 ns.
• DATETIMEOFFSET. Includes all of DATETIME2 and additionally introduces time-zone offset support, which should significantly reduce the amount of custom code you need for time-zone-aware applications.

As you can see, all new types support a broader range, and where appropriate, user-defined, fractional-second precision (Table 2). This allows developers to tune the storage size of columns to exactly fit application needs, which for large databases, translates to significant savings in storage costs. Now let me discuss how data access stacks expose these types.

The new SQL Server types DATE and DATETIME2 correlate to existing types in all data access technologies. For ADO.NET this is DateTime for both, for ODBC it’s SQL_DATE and SQL_TIMESTAMP respectively, and for OLE DB it’s DBTYPE_DBDATE and DBTYPE_DBTIMESTAMP. The new SQL types TIME and DATETIMEOFFSET were more difficult to express in some cases because the conceptual type didn’t already exist. While you could map TIME to TimeSpan in ADO.NET, Microsoft needed to invent new provider types in ODBC and OLE DB (SQL_SS_TIME2 and DBTYPE_DBTIME2) because existing types don’t support fractional-second precision. As you’d guess, these match their pre-existing types in every way with the addition of a fraction component matching the component that already exists in SQL_TIMESTAMP/DBTYPE_DBTIMESTAMP. The server type DATETIMEOFFSET is unique in that, in addition to containing all date/time parts of other types, it also includes a time-zone offset. To accommodate this type, Microsoft introduced new types across the board. The .NET Framework 3.5 release includes a new system type conveniently named DateTimeOffset while OBDC and OLE DB introduce SQL_TIMESTAMPOFFSET and DBTYPE_DBTIMESTAMPOFFSET; all of which should look familiar to people used to working with their non-time-zone-aware equivalents. These new types are first-class citizens in every way-with the goal of being better data-type alternatives for new application development and replacement options for application enhancements. In addition to introducing new data types and programming constructs, SQL Server 2008 also removes limitations on existing types to open the door to new application scenarios.