Creating Tables Using SQL Server Express

The Keys to a Successful Installation
      by Michael Otey, [email protected]

If you’ve used SQL Server 2005 Express Edition for any length of time, you know that SQL Server Express is a great lightweight database that provides excellent performance with a small footprint. However, a common question from many readers, and one that appears on Microsoft’s support forums, doesn’t deal with using the database but rather with installing it. Unlike the SQL Server 2005 Standard and Enterprise Editions, which are almost exclusively installed on server platforms where there are few other software applications installed and both the hardware and software platform is static, SQL Server Express tends to be installed on desktops and development systems with a plethora of other applications and where change is the name of the game. This makes the task of installing SQL Server Express a lot more challenging than installing a standard server-oriented application, such as SQL Server 2005. Even so, there are several things you can do to help insure a smooth installation process for SQL Server Express. Learn more at "SQL Server 2005 Express" and "SQL Server 2005 Express Tips."

First, make sure you install the latest version of software. Many of the early SQL Server Express Community Technology Preview (CTP) versions are still posted on Microsoft's Web site, and if you’re not careful, you could install an old version of the software. Get the current release of SQL Server Express.

Second, you need to uninstall any of the early beta or CTP releases of SQL Server Express that you might have installed on your system as part of an earlier Visual Studio 2005 Express installation. Microsoft’s Mark Jewett, senior product manager for Server and Tools Marketing responsible for SQL Server Express, mentioned that one of the most common reasons that Microsoft customers experience difficulty when they install SQL Server Express is because a previous version of SQL software has already been installed on their machine. In a way, this is testament to the success of the SQL Server Express product and the CTP release program that delivered early versions of the product to a large number of users. However, those prerelease versions of SQL software have the potential to derail newer installations of SQL Server Express. In an ideal world, beta products would all uninstall cleanly. But the nature of a beta product is that it isn't finished, and therefore you can’t expect it to work perfectly. Microsoft provides a warning with all their beta and CTP releases about not installing the software on production systems--and the company isn't kidding. When you install prerelease software, it's best to do so on a pure development system that you can reload or reimage when you’re finished. Alternatively, you can use EMC's VMware or Microsoft Virtual PC products to install the software to a virtual machine. To uninstall a beta release of SQL Server Express, go to Control Panel and use the Add/Remove programs option. (You should follow this same procedure to remove beta versions of the .NET Framework software, too.) For more information about uninstalling SQL Server-related software, see Microsoft's blog.

Third, check the prerequisites for installation. SQL Server Express's home page lists some of the basic requirements. For example, SQL Server Express requires that the machine on which it's installed is running Windows 2000 Service Pack 4 (SP4), Windows Server 2003 SP1, or Windows XP SP2; Windows .NET Framework 2.0; and Windows Installer 3.1. See, Microsoft's main SQL Server Express Web page.

Although Microsoft doesn’t explicitly mention it, you can’t install SQL Server or SQL Server Express on compressed or encrypted drives. Compression and encryption both add overhead that can adversely affect database performance. If you want to install SQL Server Express software on a machine that uses compression or encryption, you’ll have to create and install SQL Server Express on a separate disk partition that’s not compressed.

Creating Tables
      by Michael Otey, [email protected]

In past issues of this newsletter I’ve covered the topic of creating databases. Databases are certainly the foundation of any relational database system. However, if the database is the foundation, then tables have to be the floor. Tables group related data together. For example, in a business application, a customer table might contain all of the data related to a given customer, such as the customer’s name, employee number, address, or other pertinent information. Databases typically contain multiple tables. Following my business example, in addition to a customer table, the business database might have a table to track orders, another table to track sales, and another table to track inventory.

You can create tables by using SQL Server Management Studio for Express (SSMSE) or by executing T-SQL commands from either the Query Editor or the sqlcmd tool. For instance, let’s pick up on last week’s example of creating a database to track your media collection. I showed you how to create a database called MyMedia. Now, let’s create a table to hold the basic information that I want to track in my media collection. To build the table interactively, start SSMSE, expand the Databases node, then expand the MyMedia node. Right-click on the Table node and select New Table from the context menu to display the Table Designer. The Table Designer lets you enter the names of each column along with the data type. For starters, let’s enter the columns MediaID as an integer, MediaType as varchar(10), MediaTitle as varchar(50), and LastUpdated as datetime. Uncheck Allow Nulls for each column. For now, don’t worry too much about the differences in data types--I’ll cover them in a future issue. Close the designer and enter the name Media when the system prompts you for a table name. Then click OK. The following T-SQL listing shows you the T-SQL code that creates the Media table.

CREATE TABLE [dbo].[Media](
      [MedaiID] [int] NOT NULL,
      [MediaType] [varchar](10) NOT NULL,
      [MediaTitle] [varchar](50) NOT NULL,
      [LastUpdated] [datetime] NOT NULL
) ON [PRIMARY]

Embedding SQL Server 2005 Express into Your Custom Application
      by Michael Otey

You can use SQL Server 2005 Express Edition as a database back end for single or multi-user applications. But wouldn’t it be great if SQL Server Express were included as a part of your application's install, rather than as a separate download and installation? Well guess what--it is!

SQL Server Express can be installed as a single-setup executable that you can bundle with the installation process of your applications.

Quickly Schedule Jobs for SQL Server Express Edition
      by Blake Eno, [email protected]

Vale Software announced Express Agent, a solution that lets you schedule and run database jobs for SQL Server 2005 Express Edition. Express Agent's wizard assists you in creating regular database backup jobs and database maintenance jobs. Express Agent's UI features event logging, email notification, and the ability to import and export database job settings. The product also lets you add, edit, delete, and schedule database jobs on multiple servers. Express Agent costs $79, but it's available at $49 for a limited time. Trial versions are also available through Vale Software's website.