SQL Server Express--Creating Tables--February 6, 2006

Subscribe to SQL Server Magazine:
      http://www.windowsitpro.com/rd.cfm?s=9&code=eu215xsL

=================================

To ensure that future email messages you receive from SQL Server 2005 Express UPDATE aren't mistakenly blocked by antispam software, be sure to add [email protected] to your list of allowed senders and contacts.

==== This Issue Sponsored By ====

This email newsletter comes to you free and is supported by the following advertisers, who offer products and services that might interest you. Please take a moment to visit these advertisers' Web sites and show your support for SQL Server Magazine UPDATE.

ProClarity
      http://www.windowsitpro.com/essential/index.cfm?code=SQLExpTop0206

AppDev
      http://www.appdev.com/promo/RN00295

Thawte
      http://www.windowsitlibrary.com/ebooks/leveragingssl/index.cfm?code=SQLExpHot0206

=================================

February 6, 2006

1. Commentary
          - The Keys to a Successful Installation

2. SQL Server Express Jump Start
          - Creating Tables

3. Check It Out
          - Embedding SQL Server 2005 Express into Your Custom
          Application

4. From the Community
          - Reader Discoveries

5. Events and Resources
          - SQL Server 2005 Up & Running Roadshows Coming to Europe!
          - The Impact of Disk Fragmentation
          - Web Seminar: Leveraging Your VoIP Network to Include Fax
              for IP (FoIP)
          - Fax-Document Management

6. Featured White Paper
          - Maximizing the Potential of Your Windows Environment

7. New Product
          - Vale Software announces Express Agent

8. Announcements
          - VIP Subscribers Have It All!
          - Save 44% on Exchange & Outlook Administrator

==== Sponsor: ProClarity ====

The Essential Guide to Analytic Dashboards
      Learn how to differentiate between a portal, a dashboard, and a scorecard.
      http://www.windowsitpro.com/essential/index.cfm?code=SQLExpTop0206

1. ==== Commentary ====

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.

      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. You'll find the current release of SQL Server Express at:
      http://www.microsoft.com/downloads/details.aspx?familyid=220549b5-0b07-4448-8848-dcc397514b41&displaylang=en

      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 about this topic at:
      http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=137700&SiteID=1

      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. You can access Microsoft's main SQL Server Express Web page at:
      http://www.microsoft.com/sql/editions/express/default.mspx

      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.

**** Sponsor: AppDev ****

Learn SQL Server 2005 now -- Get a FREE training CD!
      Start learning SQL Server 2005 today with cutting edge training from AppDev. Get a FREE SQL 2005 training CD from our new course (a $115 value). Click the link below for your FREE SQL Server 2005 training.
      http://www.appdev.com/promo/RN00295

******************************

2. ==== SQL Server Express Jump Start ====

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]

==== Sponsor: Thawte ====

"Understand and Leverage SSL-TLS for Secure Communications"
      This eBook explains what you need to know about today's most popular security protocols (i.e., Secure Sockets Layer (SSL) and Transport Layer Security--TLS) for secure Web-based communications.
      http://www.windowsitlibrary.com/ebooks/leveragingssl/index.cfm?code=SQLExpHot0206

3. ==== Check It Out ====

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. If you want to learn more about embedding SQL Server Express into your custom application's installation process, check out "Embedding SQL Server Express into Custom Applications," at:
      http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsse/html/EmSQLExCustApp.asp

4. ==== From the Community ====

Reader Discoveries: Share your SQL Server discoveries, comments, problems, solutions, and experiences with products, and reach out to other SQL Server Magazine readers. Email your contributions (400 words or less) to [email protected]. Please include you phone number. We edit submissions for style, grammar, and length. If we print your submission, you'll get $50!

5. ==== Events and Resources ====

SQL Server 2005 Up & Running Roadshows Coming to Europe!
      SQL Server experts will present real-world information about administration, development, and business intelligence to help you put SQL Server 2005 into practice and learn how to use its new capabilities. Along with your registration, you get a one-year PASS membership and subscription to SQL Server Magazine. Register now for London and Stockholm.
      http://www.windowsitpro.com/roadshows/sqlservereurope/index.cfm?code=0201emailannc

The Impact of Disk Fragmentation
      Learn what impact disk fragmentation has on users and system activities and discover how quickly fragmentation accumulates as a result of these activities. Plus get the recommendations you need to manage the frequency of defragmentation across your infrastructure. Download this whitepaper at:
      http://www.windowsitpro.com/whitepapers/diskeeper/defragmentation/index.cfm?code=0201emailannc

Leveraging Your VoIP Network to Include Fax for IP (FoIP)
      Leverage your current VoIP infrastructure to integrate boardless FoIP. Live Web Seminar Tuesday, February 21, 2006, at 12:00 P.M. EST. Register now at:
      http://www.windowsitpro.com/go/seminars/faxback/foip/?partnerref=0201emailannc

Fax-Document Management
      Align compliance with business efficiency and learn how fax-document management plays a role in your strategy. Download this whitepaper at:
      http://www.windowsitpro.com/go/whitepapers/esker/docmanagement?code=0201emailannc

6. ==== Featured White Paper ====

Maximizing the Potential of Your Windows Environment
      Use server and storage consolidation to optimize your existing Windows server infrastructure. Find out how!
      http://www.windowsitpro.com/go/whitepapers/polyserve/serverconsolidation/?code=0201emailannc

7. ==== New Product ====

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 Web site.
      http://www.valesoftware.com

8. ==== Announcements ====

VIP Subscribers Have It All!
      Become a VIP subscriber and get continuous inside access to ALL of the online resources published in Windows IT Pro magazine, SQL Server Magazine, and the Exchange and Outlook Administrator, Windows Scripting Solutions, and Windows IT Security newsletters --that's more than 26,000 articles at your fingertips. You'll also get a valuable 1-year print subscription to Windows IT Pro and two VIP CD-ROMs that include the entire article database and are delivered twice a year. Don't miss out--sign up now at:
      https://store.pentontech.com/index.cfm?s=1&promocode=eu2762uv

Save 44% on Exchange & Outlook Administrator
      For a limited time, order the Exchange & Outlook Administrator newsletter and SAVE as much as $30 off the regular price. You'll discover tools and solutions you won't find anywhere else to help you migrate, optimize, administer, back up, recover, and secure Exchange and Outlook. You'll also get FREE unlimited access to the full online Exchange article database (more than 1000 articles). Subscribe now at:
      https://store.pentontech.com/index.cfm?s=1&promocode=eu2362ue

==== Contact Us ====

About the [email protected]
About the [email protected]
About technical questions-- http://sqlforums.windowsitpro.com/web/forum/default.aspx?forumid=10
About product [email protected]
About your [email protected]
About sponsoring an issue of SQL Server Express UPDATE--Richard Resnick, [email protected] or Lisa Kling, [email protected]

SQL Server Express UPDATE is brought to you by SQL Server Magazine, the only magazine devoted to helping developers and DBAs master new and emerging SQL Server technologies and issues. Subscribe today.
      http://www.windowsitpro.com/rd.cfm?s=9&code=eu215xsL

Manage Your Account

You are subscribed as %%$email%%. To unsubscribe from this email newsletter, click here
      http://lists.sqlmag.com/u?id=%%SUBSCRIBER_ID_TAG%%

To manage your email account, simply log on to our Email Preference Center.
      http://www.windowsitpro.com/email

View the SQL Server Magazine Privacy Policy.
      http://www.windowsitpro.com/aboutus/index.cfm?action=privacy

SQL Server Magazine is a division of Penton Media, Inc.
221 East 29th Street
Loveland, CO 80538
Attention: Customer Service Department

Copyright 2006, Penton Media, Inc. All Rights Reserved.