Integrating with a Remote Database and Indexes

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.

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
CREATE CLUSTERED INDEX idxMedia ON Media(MediaID)

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