As we all know, to our chagrin, data expands to fill the space available (not to mention then overflowing that space). And the kinds of data we want to store is also expanding - who would have believed, a few years ago, the different sorts of information we would try to stuff into databases today, and how much there would be.

One would imagine that even the SQL Server team was slightly boggled at the possibilities when it began the design for SQL Server 2008 (code-named Katmai). Migrating from storing mundane old sales figures to capturing the actual locations of those sales, for example, increases the analytical possibilities exponentially, but also makes development more–um–interesting.

Yet in spite of the challenges, one big feature in SQL Server 2008 is a bunch of new data types, including spatial, and within it, geography and geometry types. Then stuff in data types to use for efficiently stashing audio and video, and types for handling hierarchical data. Stir well, and add analysis tools, and get a whole lot more value out of that hard-won data.

Microsoft showed off these and a collection of other new features in SQL Server 2008 during a recent reviewer's workshop. The new features run the gamut from performance and security enhancements through goodies for developers, and although critics say that much of the newness is old news to users of other databases, SQL Server has taken a considerable jump forward on its road to maturity.

Here's a look at a few of the new data types.

Consider, for example, those spatial data types. They come in two flavors:

The geography data type allows you to store planar spatial data that conforms to industry spatial standards such as those championed by the Open Geospatial Consortium (OGC) http://www.opengeospatial.org/standards/go. OGC describes the OpenGIS Geographic Objects Interface Standard (GO) as providing an open set of common, lightweight, language-independent abstractions for describing, managing, rendering, and manipulating geometric and geographic objects within an application programming environment. This allows you to store polygons, points, and lines that are associated with projected planar surfaces, as well as naturally planar data such as interior spaces.

The geometry data type lets you store geodetic spatial data and perform operations on it. You can, for example, use latitude and longitude coordinates to define areas on the earth's surface and associate geographical data with industry-standard ellipsoids such as WGS84 (http://earth-info.nga.mil/GandG/wgs84/), which is used in GPS solutions worldwide.

So, what can you do with spatial data? How about correlating customer locations with the stores they frequent to see how far people are willing to travel to purchase a product? That could point to likely locations for another store. Or perhaps you could figure out the size of the area within a particular zip code, using the STArea function for a geospatial polygon object, to calculate the catchments area. You can even plot the locations on a map from Virtual Earth, and color areas according to their population (or any other parameter), or show locations on a specific route (handy if you want to find prime spots next to a freeway). Spatial indexing makes performance brisk enough to be worth using (although, to be perfectly honest, since we saw a pre-release product running on a virtual machine, it wasn't anywhere near as fast as it could have been).

Another productivity enhancer is entities. They're business objects such as "customer" or "order", defined by the developer, that are mapped to the underlying data. This lets you program to the high level object, rather than against tables and columns. The ADO.NET Entity Framework enables this functionality; you can program against a database by using CLR objects that are managed by ADO.NET.

Developers who aren't SQL gurus aren't hung out to dry anymore, either. Microsoft Language Integrated Query (LINQ) integration lets you issue queries against data by using a managed programming language such as C# or VB.NET instead of SQL statements. LINQ enables seamless, strongly typed, set-oriented queries written in the .NET Framework languages to run against ADO.NET (LINQ to SQL), ADO.NET DataSets (LINQ to DataSets), the ADO.NET Entity Framework (LINQ to Entities), and to the Entity Data Service Mapping Provider. SQL Server 2008 features a new LINQ to SQL Provider so you can use LINQ directly on SQL Server 2008 tables and columns.

If you are acquainted with Transact-SQL (T-SQL), you'll find it has been enhanced too. For example, there are several new date and time data types, including one that is time zone aware. Reliable discovery of dependencies among objects is provided through newly introduced catalog view and dynamic management functions, keeping dependency information always up-to-date for both schema-bound and non-schema-bound objects. Dependencies are tracked for stored procedures, tables, views, functions, triggers, user-defined types,

For those who want to stash large files in a database while still retaining some file system functionality, the new SQL Server 2008 FILESTREAM data type is just what the doctor ordered. It allows large binary data such as documents and images to be stored directly in an NTFS file system, while remaining an integral part of the database and maintaining transactional consistency. The data itself is stored outside the database, as individual files that can be accessed using an NTFS streaming API, which allows efficient common file operations while still providing all of the critical database services such as security and backup.

What else? How about a hierarchyID data type that lets you model org charts and file and folder structures in an easy-to-navigate tree? You'll also be able to call SQL stored procedures in Visual Studio 2008, and enjoy Silverlight and Ajax support to let you make even more use of the enhanced data.