SQL Server Express UPDATE--June 19, 2006:Integrating with a Remote Database, Indexes, Reader's Questions

Subscribe to SQL Server Magazine:


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.

Double-Take Software


Intercerve Migrating to SQL Server 2005?



June 19, 2006

1. Commentary

  • Integrating SQL Server Express with SQL Server 2005

    2. Jump Start

  • Using Views

    3. From the Community

  • Using the Shared Memory Provider
  • View Performance

    4. Events and Resources

  • Simplify Management and Boost Availability for SQL Server Environments
  • Special Offer: Win a Pair of Bose Triport Headphones
  • Disaster Recovery for your Windows-based Applications
  • Continuous Data Production for Exchange

    5. Featured White Paper

  • The Starter PKI Program

    6. SQL Server Express Product

  • Easily Schedule SQL Server Jobs

    7. Announcements

  • SQL Server Magazine Monthly Online Pass--Only $5.95 per month!
  • June Special--Save $80 On the Windows Scripting Solutions Newsletter


    ==== Sponsor: Double-Take Software ====
          Develop your disaster recovery program before it's too late. Define the right plan for your environment and avoid costly mistakes.

    1. ==== Commentary ====

    Integrating SQL Server Express with SQL Server 2005
          by Michael Otey

    People often ask me, "How do you integrate SQL Server 2005 Express Edition with other SQL Server installations?" Many people think that because SQL Server Express is oriented toward single-user desktop applications, you can use it only as a standalone database and can’t integrate it with other SQL Server editions. That's not the case. SQL Server Express supports several types of integration with other SQL Server editions as well as other databases.

    One of the best ways to integrate SQL Server Express with a remote database is to use the Linked Server option in SQL Server Management Studio Express (SSMSE) or the T-SQL sp_addlinkedserver built-in stored procedure. SQL Server Express uses the Linked Server option to enable seamless database connectivity to a remote database. To use this option, you must install an ODBC driver, an OLE DB Provider or a .NET Data Provider for the remote database on the SQL Server Express system. To configure a linked server, you'll enter the name of the remote database server, the name of the Data Provider that you'll use to connect to the remote system, and the authentication information required to connect to the remote database. When you issue a query on the SQL Server Express system that's using the Linked Server option, SQL Server Express uses the data provider specified in the Linked Server dialog box to make a networked connection to a remote database, execute the query on the remote system, and return the queried data to SQL Server Express. The linked server makes it appear as if the data is actually on the SQL Server Express system. One drawback to this feature is that queries are typically slower than the ones that are run against SQL Server Express's own local databases because of the time required to get the remote data.

    You can also use the SQL Server Express replication feature to integrate SQL Server Express with other SQL Server editions. SQL Server replication technology automatically sends changes in a SQL Server database to one or more remote databases. SQL Server supports three types of replication: snapshot, transactional, and merge. Snapshot replication sends a point-in-time copy of the data, transactional replication sends individual changes, and you can use merge replication for periodic batch and bi-directional replication. The system on which the data originates is called the publisher, and the system that receives the data is called a subscriber. SQL Server Express can't act as a replication publisher, but it can act as a subscriber that receives replicated data from other SQL Server systems.

    Linked servers and replication are two primary SQL Server remote database-integration technologies, but there are others. Additionally, SQL Server Express can interact with SQL Server 2005 Service Broker applications, function as a data source for SQL Server Integration Services (SSIS) and Reporting Services, and act as a witness for SQL Server database mirroring. Although SQL Server Express is designed to act as a standalone database system, these integration capabilities can make SQL Server Express part of a larger connected SQL Server infrastructure.

    ****** Sponsor: EMC ******
    Get the essential tools you need to manage a SQL Server 2005 system, SQL Server security essentials, and database backup and recovery. Download this exclusive guide today!

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

    Using Indexes
          by Michael Otey

    In this column, I show you another important piece of the SQL Server 2005 Express puzzle: indexes. Most relational database activity typically consists of queries that retrieve data from the database. Using indexes in tables speeds up the time it takes the system to retrieve data. An index can consist of 1 to 16 columns.

    SQL Server Express supports two types of indexes: clustered and nonclustered. A clustered index causes the rows in a database table to be ordered in the same manner as the index. Using clustered indexes results in faster queries because SQL Server uses the clustered index to order the rows in the base table according to the order specified in the clustered index; making it easier for the query to locate the desired rows. A nonclustered index also helps SQL Server retrieve data more efficiently because the index lets the SQL Server Express database engine quickly locate the rows it needs. However, a nonclustered index doesn't reorder the rows in a table. You can have up to 249 nonclustered indexes for a table. As you might expect, you can only have one clustered index for each table because a clustered index orders the rows in the base table.

    The following code creates a clustered index on the Media table and names the new index idxMedia. This index will force SQL Server Express to order the rows in the Media table according to the value of the MediaID column.

    IF EXISTS (SELECT name FROM sysobjects
          WHERE name = 'idxMedia' AND type = 'U')
                DROP TABLE idxMedia

    ***** HOT SPOT: Intercerve *****

    Install sqlSentry today before you upgrade to SQL Server 2005 and save yourself considerable time and effort configuring, activating, testing, and maintaining notifications on every SQL Server 2005 instance! Download a free trial now!


    3. ==== From the Community ====

    In this edition, I share some comments and suggestions about SQL Server Express from SQL Server Express users. The Check It Out column will be back in the next edition of the newsletter.

    I use the Shared Memory Provider to connect to a SQL Server database on my network. To use this facility, I type (local)\sqlexpress instead. .\. This command causes a named pipes invocation (local) that provides shared memory.
    - Richard Waymire

    Richard, thanks for the tip. You're right, the Shared Memory Provider makes a direct in-memory connection to the database and is the fastest mechanism for connecting an application running on the local system to a SQL Server Express database.
    - Michael Otey

    The question I have about view performance is, "Does it perform?" In theory, a view should perform about the same as the underlying query. But in my experience, SQL Server 2000 is sometimes slow on a complex view. This slower performance makes me wonder whether SQL Server is literally computing the entire "virtual table" before executing the query.
    - Joe Landau

    Joe, you raise an interesting question. For a standard view, SQL Server internally generates a query to access the database objects that the view defines. This process can require significant overhead for views that involve complex processing or views that need to join a large number of rows or perform aggregations involving large data sets. To improve the performance of a complex view, you can create a unique clustered index on the view. The unique clustered index will cause SQL Server to store the result in the database in the same way as a table with a clustered index. This technique will significantly improve the performance of the view for subsequent queries.
    - Michael Otey

    4. ==== Events and Resources ====

    Simplify Management and Boost Availability for SQL Server Environments
    Learn to use a database utility for SQL Server to lower operational costs, simplify management, and increase the availability of your SQL Server deployment. Live event: Tuesday, June 20

    Special Offer: Win a Pair of Bose Triport Headphones
          Download any white paper from Windows IT Pro before June 30, and you could win a pair of Bose Triport Headphones. View the full selection of papers today at

    Disaster Recovery for your Windows-based Applications
          Learn to differentiate between alternative solutions to disaster recovery for your Windows-based applications and how to ensure seamless recovery of your key systems whether a disaster strikes just one server or the whole site. Register for this on-demand Web seminar at

    Continuous Data Production for Exchange
          Any unscheduled downtime--especially of your Exchange systems--can quickly affect a company's bottom line. Learn essential skills for reducing downtime to minutes instead of hours.

    5. ==== Featured White Paper ====

    The Starter PKI Program
          Test-drive the Starter PKI program and learn how companies that need to secure multiple domains and host names can benefit.

    Bonus: Whenever you download a white paper from Windows IT Pro before June 30, you'll be entered to win Bose Triport Headphones. See the full selection of papers today at


    6. ==== New Product ====

    Easily Schedule SQL Server Jobs
          by Blake Eno

          Teratrax Job Scheduler lets you schedule SQL Server 2005 Express jobs to back up your SQL Server databases. Additionally, a few mouse clicks lets you schedule and run SQL batches and stored procedures. If you have an outgoing SMTP mail server, email notification can alert you when your jobs run or fail. Teratrax Job Scheduler also lets you schedule daily, weekly, and monthly jobs. A free seven-day trial is available. For more information, contact Teratrax at [email protected] or [email protected]

    7. ==== Announcements ====

    SQL Server Magazine Monthly Online Pass--Only $5.95 per month!
          Includes instant online access to every article ever written in SQL Server Magazine and the latest digital issue. Order now at

    June Special--Save $80 On the Windows Scripting Solutions Newsletter
          Get endless scripting techniques and expert-reviewed code. Subscribe to Windows Scripting Solutions today and save $80:

    ==== 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]

    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.

    Manage Your Account You are subscribed as %%$email%%. To unsubscribe from this email newsletter, click here

    To manage your email account, simply log on to our Email Preference Center.

    View the SQL Server Magazine Privacy Policy.

    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.



  • Hide comments


    • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

    Plain text

    • No HTML tags allowed.
    • Web page addresses and e-mail addresses turn into links automatically.
    • Lines and paragraphs break automatically.