View Enhancements in SQL Server 2000

3 4

SQL Server 2000 includes two view enhancements: partitioned views now are updatable and distributable, and views can now be indexed like tables. Let's explore these enhancements in a little more detail.

Updatable, Distributed Partitioned Views

In Microsoft SQL Server versions 7 and earlier, data in views was static and reflected the actual state of the underlying table or tables. In SQL Server 2000, an update performed on a partitioned view will modify both the view and the underlying table or tables. In addition, partitioned views can span multiple SQL Server 2000 systems. Partitioned views can be used to implement a federation of database servers. A federation is a group of servers that are each administered independently but that share the processing load of the entire system. When you create a federation of servers, you partition data across the servers, which allows you to scale out the system. A federation of database servers can grow to support the largest e-commerce Web sites or enterprise database systems. Figure 18-21 shows a sample configuration for a federation of database servers.

Figure 18-21. A federation of SQL Server systems.

Before you can implement a partitioned view, you must partition your table horizontally. You replace the original table with several smaller member tables. You give each of these member tables the same number of columns as you gave the original table, and you assign each column the same attributes (such as data type, size, and collation) as you assigned the corresponding column in the original table. If you are creating the partitioned view to be distributed, you put each member table on a separate member server. To maintain a greater degree of transparency, you should name each member table of the member databases the same on each member server. Though this is not a requirement, it makes overall management of the system easier.

When you design the member tables, you partition them horizontally. Each member table stores a horizontal slice of the original data. This partitioning is generally based on a range of key values. The range is based on the actual data values in the partitioning column. The value range for each member table is enforced by a CHECK constraint on the partitioning column. Keep in mind when you are partitioning your data that these data ranges cannot overlap. Let's look at an example of a horizontal partition. In this example, we will partition the Customer table into four member tables, and we'll place each table on a different server. Each server will hold 3000 records of the Customer table. The constraints are shown in the following CREATE TABLE statements:

 Server 1: CREATE TABLE Customer_Table_1 (CustomerID INTEGER PRIMARY KEY CHECK (CustomerID BETWEEN 1 AND 3000), . . (Additional column definitions) . Server 2: CREATE TABLE Customer_Table_2 (CustomerID INTEGER PRIMARY KEY CHECK (CustomerID BETWEEN 3001 AND 6000), . . (Additional column definitions) . Server 3: CREATE TABLE Customer_Table_3 (CustomerID INTEGER PRIMARY KEY CHECK (CustomerID BETWEEN 6001 AND 9000), . . (Additional column definitions) . Server 4: CREATE TABLE Customer_Table_4 (CustomerID INTEGER PRIMARY KEY CHECK (CustomerID BETWEEN 9001 AND 12000), . . (Additional column definitions) . 

After you have created the member tables, you can define a distributed partitioned view on each member server. All views should have the same name to preserve application transparency. The distributed partitioned view allows any query running on any member server to execute as though all the referenced data is local to the query. In other words, if you run a query on a member server that references data on any other member server, the data is transparently returned to the query.

In order to preserve data transparency, you will need to create linked-server definitions on each member server. These definitions provide a member server with all the appropriate connection information for all the other federation member servers. This allows the partitioned view on a server to access data on the other member servers. You create the linked-server definitions via T-SQL commands or in Enterprise Manager.

Linking Servers by Using T-SQL

The syntax of the T-SQL command used to create a linked-server definition is as follows:

 sp_addlinkedserver   [ @server =   ] 'server'   [ ,   [ @srvproduct =   ] 'product_name'   ] [ ,   [ @provider =   ] 'provider_name'   ] [ ,   [ @datasrc =   ] 'data_source'   ] [ ,   [ @location =   ] 'location'   ] [ ,   [ @provstr =   ] 'provider_string'   ] [ ,   [ @catalog =   ] 'catalog'   ] 

The stored procedure sp_addlinkedserver has the following arguments:

• @server The system name of the linked server. If there are multiple instances of SQL Server on the server, you must specify the name as server_name\instance_name.
• @srvproduct The product name of the OLE DB provider. If you are linking a SQL Server 2000 system to another SQL Server 2000 system, you do not need to specify @srvproduct.
• @provider The unique programmatic identifier of the OLE DB provider previously specified in @srvproduct. If you are linking a SQL Server 2000 system to another SQL Server 2000 system, you do not need to specify @provider.
• @datasrc The name of the data source as interpreted by the OLE DB provider. If you are linking a SQL Server 2000 system to another SQL Server 2000 system, you do not need to specify @datasrc unless you are connecting to a specific instance on the linked server. If so, you must specify server_name\instance_name for the data source.
• @location The location of the database as interpreted by the OLE DB provider. If you are linking a SQL Server 2000 system to another SQL Server 2000 system, you do not need to specify @location.
• @provstr The OLE DB provider_specific string that identifies a unique data source. If you are linking a SQL Server 2000 system to another SQL Server 2000 system, you do not need to specify @provstr.
• @catalog The catalog used when making the connection to the OLE DB provider.

For example, the following T-SQL commands will create linked-server definitions for communications between Servers 1, 2, 3, and 4.

 Server 1: sp_addlinkedserver 'Server2' sp_setnetname 'Server2', 'sql-server-02' sp_addlinkedserverlogin Server2, 'false', 'sa', 'sa' sp_addlinkedserver 'Server3' sp_setnetname 'Server3', 'sql-server-03' sp_addlinkedserverlogin Server3, 'false', 'sa', 'sa' sp_addlinkedserver 'Server4' sp_setnetname 'Server4', 'sql-server-04' sp_addlinkedserverlogin Server4, 'false', 'sa', 'sa' Server 2: sp_addlinkedserver 'Server1' sp_setnetname 'Server1', 'sql-server-01' sp_addlinkedserverlogin Server1, 'false', 'sa', 'sa' sp_addlinkedserver 'Server3' sp_setnetname 'Server3', 'sql-server-03' sp_addlinkedserverlogin Server3, 'false', 'sa', 'sa' sp_addlinkedserver 'Server4' sp_setnetname 'Server4', 'sql-server-04' sp_addlinkedserverlogin Server4, 'false', 'sa', 'sa' Server 3: sp_addlinkedserver 'Server1' sp_setnetname 'Server1', 'sql-server-01' sp_addlinkedserverlogin Server1, 'false', 'sa', 'sa' sp_addlinkedserver 'Server2' sp_setnetname 'Server2', 'sql-server-02' sp_addlinkedserverlogin Server2, 'false', 'sa', 'sa' sp_addlinkedserver 'Server4' sp_setnetname 'Server4', 'sql-server-04' sp_addlinkedserverlogin Server4, 'false', 'sa', 'sa' Server 4: sp_addlinkedserver 'Server1' sp_setnetname 'Server1', 'sql-server-01' sp_addlinkedserverlogin Server1, 'false', 'sa', 'sa' sp_addlinkedserver 'Server2' sp_setnetname 'Server2', 'sql-server-02' sp_addlinkedserverlogin Server2, 'false', 'sa', 'sa' sp_addlinkedserver 'Server3' sp_setnetname 'Server3', 'sql-server-03' sp_addlinkedserverlogin Server3, 'false', 'sa', 'sa' 

Two statements were used in addition to the sp_addlinkedserver T-SQL statement. These statements are required to facilitate the distributed partitioned view processing. The call to sp_setnetname connects the linked-server name in SQL Server with the network name of the server hosting the database. In the preceding example, the linked-server name of Server2 is on the server with the network name of sql-server-02. We also specified the credentials to use for logon to the linked server. The call to sp_addlinkedsrvlogin instructs SQL Server to use the specified user ID and password for accessing the linked server.

Linking Servers by Using Enterprise Manager

Enterprise Manager also provides a method for linking servers. To use this method, follow these steps:

1. In Enterprise Manager, expand the Security folder for your server, as shown in Figure 18-22.

   

   Figure 18-22. Expanding a server's Security folder.

2. Right-click Linked Servers in the left-hand pane. Choose New Linked Server from the shortcut menu that appears. The Linked Server Properties window appears, as shown in Figure 18-23.

   

   Figure 18-23. The General tab of the Linked Server Properties window.

3. In the Linked Server text box, type the name of the SQL server you would like to link. Select the SQL Server option button, as illustrated in Figure 18-24.
4. Click the Security tab. Type the local login name to use and either select the Impersonate check box or type a remote name and password. Figure 18-25 shows a local login name entered.
5. Click OK to complete the linked-server definition.

The linked server is now available for use. You can also use Enterprise Manager to modify or delete the linked server's properties. In addition, you can use Enterprise Manager to view the tables and views available on the linked server.

Figure 18-24. Selecting the linked-server type.

Figure 18-25. The Security tab of the Linked Server Properties window.

Creating the View

Once all the linked-server definitions are in place, you can create the actual view. The following example creates a view called sales that combines sales data from the sales table on four servers:

 CREATE VIEW sales AS SELECT * FROM /*Server1.bicycle.dbo.*/l_sales UNION ALL SELECT * FROM Server2.bicycle.dbo.l_sales UNION ALL SELECT * FROM Server3.bicycle.dbo.l_sales UNION ALL SELECT * FROM Server4.bicycle.dbo.l_sales GO 

Indexed Views

SQL Server 2000 also enables you to create an index on a view. Because a view is simply a virtual table, it has the same general form as an actual database table. You create the index by using the same CREATE INDEX T-SQL command that you use to create an index on a table. (This command is covered in Chapter 17.) The only difference is that rather than specifying a table name, you specify a view name. For example, the following T-SQL command creates a clustered index on a view called partview:

 CREATE UNIQUE CLUSTERED INDEX partview_cluidx ON partview (part_num ASC) WITH FILLFACTOR=95 ON partfilegroup 

Creating indexes on views has several impacts on performance. Obviously, an index on a view will improve performance when you are accessing data in the view, just as an index on a table improves performance.

Also, when you create an index on a view, SQL Server stores the view result set in memory and will not have to materialize it for future queries. The term "materializing" refers to the process SQL Server uses to dynamically merge the data needed to build a view result set each time a query references a view. (Remember, a view is a dynamic structure.) The process of materializing a view can substantially increase the overhead required to satisfy a query. The impact on performance caused by repeatedly materializing a view can be significant if the view is complex or includes large amounts of data.

In addition, when you create an index on a view, the SQL Server query optimizer automatically uses the index in queries that do not directly name the view in the FROM clause of a SELECT statement. In other words, when an existing query in an application or a stored procedure might benefit from the indexed view, the query optimizer will apply the indexed view to satisfy the query.

These benefits do not come without a price. Indexed views can be more complex for SQL Server to maintain over time. Each time an underlying table of a view is modified, SQL Server has to update the view result set and potentially the index on that view. Because the scope of a view's index can be larger than that of any one table's index—for example, if a view encompasses several large tables—the overhead associated with maintaining a view and its index can negate any benefit that queries gain from the indexed view. Because of this additional maintenance overhead, you should create indexes only on those views where the advantage provided by the improved speed in retrieving the results outweighs the disadvantage caused by the increased maintenance overhead. Views that generally should be indexed are those in which the underlying data is rather static, those whose result set processes a large number of rows, and those referenced by a large number of queries.