Why You Should Build Applications Using Microsoft SQL Server 2008 Express

Microsoft® SQL Server® 2008 is everything an enterprise-class database system should be. It's fast. It's scalable. It's a Business Intelligence platform. It's a reporting platform. It slices, it dices, (ok, maybe I'm going a bit too far). The fact is, SQL Server 2008 has more features than you could ever use in a single solution. So it costs money… Microsoft offers several versions of SQL Server, but I want to focus on one in particular…THE FREE ONE!

Microsoft® SQL Server® 2008 Express Overview
Microsoft SQL Server 2008 Express (SQL Express 2008) is a fully-functional edition of the Microsoft SQL Server 2008 database product. Notice I did not say it is fully-featured. It does have limitations and is meant to be an entry-level version of the product. Still, it is a solid database that you can depend upon in your solutions.

It's solid because it provides a fast, reliable, low-overhead database solution for your development needs. If all you want is a place to store your application's data, but also provide scalable options in the future, SQL Express 2008 is a good starting point.

SQL Express 2008 is also a good fit if you are looking for a more advanced database that provides additional features while maintaining a small footprint (at least as far as database systems are concerned). For example, SQL Express 2008 provides a security model that allows you to implement security schemes that protect your data across your application's user base. Other advanced features such as full-text search, .NET integration, and Web service support are also included and provide value not normally expected from a free database.

One feature that I love in particular is the reporting capabilities the Microsoft SQL Server products provide. I believe my fondness for reporting results from my perception that reporting is an underrated and often overlooked component of application development...and I like to root for the underdog. SQL Express has included reporting capabilities since Microsoft® SQL Server™ 2005 Express, but did not provide a report writer. You could still use Microsoft® Visual Studio® to create reports, but your end-users did not have a method for creating reports themselves. Although they have not made a final decision, it does look as if the SQL Server Express team will include a report writing tool (see Figure 1) with the 2008 edition.

Figure 1. The SQL Report Writer and Its Snazzy UI

The SQL Server Express team is looking for feedback on their blog so I recommend visiting and letting them know the report writer is a good idea.

Microsoft advertises SQL Server Express 2008 to be a "free, fully functional edition of SQL Server 2008," and this is true. But the meaning of "fully functional" is open to interpretation and you can be assured that Microsoft is not providing a "fully functional, FULLY FEATURED" edition for free. SQL Server Express has the following size limitations:

• Limit of 1 CPU
• RAM limit of 1 GB
• Database size cannot exceed 4 GB.
  
  You can run SQL Express on a server with dual core CPUs, 8GB of RAM, and 500 GB hard drives. SQL Express will govern itself and not exceed the limits listed above. That said, SQL Express is intended for smaller scale servers and desktops. The recommended system configuration for SQL Express is:



• RAM: at least 512 MB
• Hard Drive: at least 600 MB of free space
• CPU: 1 GHz Pentium III or higher
• OS: Windows Server (any edition), Windows XP, Windows Vista
• Additional Software: .NET Framework , Internet Explorer 6.0 SP1 or later

As of this writing, Microsoft had not released a comparison chart that shows which features will be included in SQL Server 2008 Express. But just as you can reasonably predict today's weather based on what happened yesterday, the feature-comparison chart for Microsoft® SQL Server™ 2005 should provide an approximation of the expected features for 2008.

Developer-Focused Features
In what is being hailed as the largest product release in its history, Microsoft will release SQL Server 2008 on February 27th, 2008—along with Microsoft Visual Studio 2008, Microsoft® BizTalk™ Server and Microsoft® Windows Server 2008. Given the nature of these products, the focus here is data and a developer's ability to work with it. SQL Server Express 2008 includes compelling new development features:

New Data Types
SQL Express 2008 provides new data types that reduce the effort required to work with data in modern applications: Spatial Data, Hierarchy data, and Media formats.

SQL Express 2008 provides native support for spatial data which allows you to easily capture geographic data and build mash-up applications that integrate with the like of Microsoft® Virtual Earth or Google Maps. The data types for spatial data are geography and geometry. The former is for working with locations based on latitude and longitude values. The latter is for working locations using coordinates.

The HierarchyID data type allows you to forego complex table joins to represent a data hierarchy. The HierarchyID data type stores the structure of a hierarchy tree. This data type stores a record's position in the tree. SQL functions like GetAncestor() and GetDescendant allow you to quickly traverse the tree as needed to meet your needs.

The FileStream data type provides you with a native data type for working with all kinds of file and media formats that reside external to the database. Excel spreadsheets and Windows Media videos are good examples. The FileStream data type integrates the SQL Express with the file system. You can use SQL syntax to select, insert, update, etc a FileStream object. The media resides as a BLOB data object in the file system. This integration applies the SQL security model to the record and removes the need to maintain two security models: one in SQL and another in the files system.

.NET Language Integration (CLR)
Okay, so this one isn't exactly new but it is often overlooked. SQL Server Express 2008 includes the CLR as part of its database engine. You can build Microsoft® .NET assemblies using the .NET language of your choice and store them in the database. In essence, you can use assemblies to extend SQL Express 2008 with new functions and/or features.

ADO.NET Entity Framework
Once you build a database that implements your desired data model you have to write the code that adds, edits, and deletes data. When writing this type of code I always end up talking to myself and wondering why in the world Microsoft doesn't have a tool that will write it for me. How hard could it be?

I don't how hard it was for Microsoft to deliver but the ADO.NET Entity Framework (EF) is pretty much what I've wanted. The EF builds a data model from your database and then generates code classes for you that handle the CUD (create, update, & delete) code for your tables. It even handles relationships for you. The takeaway here is that you can stop using your code to worrying about the different table relationships in the database. EF will do that for you and you can work with the data as entities.

Let's take a look at a quick example.