Ms SQL Server 2000 administrator manual series-18. creation and use of View tables

18. Create and use a view table
View table
View table concepts
Create a view
Modify and delete a table
Enhanced viewing table functionality in SQL Server 2000
Summary
In Chapter 17th, we learned about indexes. indexes are a secondary database structure independent of the database data structure, but they are related to accessing database data. In other words, an index is an independent structure, but is integrated with data. Next we will learn another auxiliary database structure: View table. Like an index, A View table and data are separated for use only when used. The view table filters or processes the data before the user accesses the data. In this chapter, we will learn in detail what a view is, how a view is associated with data, and how to use, create, and manage a view. In addition, you will also see the new features of the View table in Microsoft SQL Server 2000.
View table
　
You can think of a view as a virtual data table, which consists of a result set of a SELECT statement query. For the user, the view table looks like a normal data table, but the data contained in it may come from the results obtained after different data tables are queried. In fact, the method of using a view table is the same as that of using a data table. You can reference A View table in a T-SQL Statement by using the same method as referencing a data table, for example, select, insert, update, and delete can all be performed in the View table.
In fact, the view table is a pre-defined SQL statement. When you access a view table, SQL Server query optimizer merges and executes the query statement and the SQL statement of the pre-defined view table.
The advantage of using a view table is that you can create a view table with different attributes without copying data. View tables are useful in many cases. Later in this chapter, we will see that the view table can be used to ensure data security, simplify the presentation of query results, and express the logic of data presentation. In addition, you can use a view table to merge split data.
View table concepts
　
This section describes different types of View tables and their advantages and limitations.
View table Type
　
You can use different types of View tables to create any of the following forms of view:
• The subset View table of data rows in a data table only contains specific data rows. It may be the most common type of View table, used to display simplified data or for security considerations.
　
• The subset View table of Data columns in a data table only contains specific data columns and can be used for security reasons.
　
• You can create a view by joining multiple data tables to simplify the complex join operation.
　
• The summary information View table only displays the summarized data to simplify complex operations.
　
These types of views are presented in different ways later in the section create a view using a T-SQL.
View tables can also be used to merge split data. Based on ease of management, large data tables may be divided into several small data tables. You can create a view table as needed to merge several small data tables into a large virtual data table.
Advantages of viewing tables
　
The first advantage of using a view table is that the data presented by the View table is always real-time data. Because the View table is defined by the SELECT statement, the SELECT statement is executed whenever the View table is accessed, that is, the query is performed only when data is required. Therefore, although the data in the underlying data table may change, the view table always displays the latest data.
The second advantage of using a view table is that the view table has a different security level than the underlying data table. Because the query that defines the View table is performed at the security level when the table is created. Therefore, the view table can hide data that is not expected to be viewed by a certain level of users. This function is further discussed in the section "subset of data rows" in this Chapter.
View table restrictions
　
SQL Server has some restrictions on the creation and use of the View table. The restrictions are as follows:
• A data row can reference a maximum of 1024 data rows. If this limit is exceeded, you must reference it using other methods.
　
• Database limit View tables can only be created in data tables currently accessed to the database.
　
• Security restrictions the creator of a view must have the permission to access all data rows referenced in the view.
　
• Any Update, modification, or other operations on data integrity rules cannot undermine data integrity rules. For example, if the underlying data table does not allow null values, the view does not allow null values.
　
• The Nest view surface-level restricted view can be created on top of other views. In other words, you can create a view that can access other View tables. The Nest level of the View table can reach up to 32 layers.
　
• The SELECT statement of the View table cannot include the order by, compute, compute by statement and into keyword.
　
________________________________________
Description
For other restrictions on the View table, you can enter "create View" in the books online index and enter the topic of the create View table.
________________________________________
Create a view
　
You can use different methods to create a view table like an index. If more views are created in the future, we recommend that you use the create view statement (T-SQL statement) to create a view because the T-SQL statement can be stored in the script, when you want to create a view table, you can repeatedly call the instruction code to modify and use it. Other ways to create a view table are to use SQL-Server Enterprise Manager and the create View table wizard.
Like most other operations on indexes, if you want to create more than one view in the future, we recommend that you use the T-SQL command. Enter the T-SQL description in the script to edit and use the file multiple times later. If you understand the view you want to create, you can also use SQL Server Enterprise Manager to create a simple view. Finally, you can use the create View table Wizard to browse the process of creating a view table, which is equally useful for beginners or experts.
Creating a view using T-SQL
　
Creating a data table with a T-SQL is simple: Using iSQL, osql, or SQL Server Query analyzer and executing creat view creation to view the table. As described earlier, you can save the T-SQL command into the instruction code and call it out for modification later (remember to save the database definition together with the instruction code, in order to use it for rebuilding the database in the future ).
The syntax of the create view command is as follows:
Create view view_name [(column, column,...)]
[With encryption]
As
Your SELECT statement
[With check option]
When creating a view table, you can enable two options to change the behavior of the View table. One option is to use the with encryption keyword, the other option is to use with check option, or both can be used together. Now let's take a closer look at these options.
The with encryption keyword encrypts the definition of the View table. SQL Server uses the same encryption method and password. This security mechanism prevents users at specific usage levels from knowing which data tables are being accessed.
The with check option keyword specifies that any data modification Statement on The View table must comply with the benchmark set in the SELECT statement of the definition View table. For example, this modification is not allowed if a data column is created on the View table and cannot be displayed on the View table. Assume that a view is defined as "selecting information about employees of all financial departments". Generally, if you do not specify the with check option, you can change the value of the "department" Data row from "finance" to another department. If with check option is specified, this modification is not allowed because you cannot access the data column again through the View table because the value of the "department" Data row in any data column is changed. The with check option keyword can be specified and cannot be executed in the View table. As a result, changes to a column cannot be accessed in the View table.
As long as you modify the SELECT statement definition in the View table, you can create any view table you need, such as selecting a subset of data rows or columns, or joining operations. Next we will learn how to create different types of views using T-SQL.
Data row subset
　
A View table composed of a subset of data rows provides a certain degree of security for the data to be published. Now let's take a look at the following examples. Assume that a company's internal database contains a data table named "employee", as shown in Row 18-1.

　
 
Figure 18-1 employee data table
Most data in a data table is private data and cannot be viewed by other employees. However, some data must be reviewed by general employees. The focus here is to create a view table that allows all employees to access specific data to solve the problem. In addition, the view table can also prevent other data tables in the database from containing duplicate employee data.
Use the following T-SQL statement to create a view table that can access the name, phone, and office data rows for the employee data table:
Create view emp_vw
As
Select name,
Phone,
Office
From employee
The created table contains 18-2 rows. Although all data exists in the underlying data table, users accessing data through the View table can only see the selected data rows in the View table. Because the View table can have a different security level from the underlying data table, although the View table allows access by anyone, the underlying data table is still safe. In other words, only the human resources department can access all data in the employee data table, while employees in other departments can only use the View table.
Data column sub-set
　
A View table composed of data column subsets can restrict data columns that can be accessed by users. Assume that the employee data table is filled with data, as shown in Figure 18-3. In this example, we do not set a limit on data rows, but use the where statement to restrict data columns, as shown below:

　
 
Figure 18-2 emp_vw View table
Create view emp_vw2
As
Select *
From employee
Where dept = 1

　
 
Figure 18-3 employee data table containing data
The results of the View table only show the profile columns of employees working in department 1, as shown in 18-4. This table is useful if the human resources department must only access records of employees in a department. Like a view table composed of a subset of data rows, A View table composed of a data column subset can also be assigned a different security level than the database.

　
 
Figure 18-4 emp_vw2 View table
Join
　
By defining joins in the View table, the actual join statement is hidden for the user, which simplifies the T-SQL statement for data access. For example, there are two tables named Manager and employee2, as shown in 18-5.

　
 
Figure 18-5 manager and employee2
Use the following statement to connect two data tables to a virtual data table:
Create view org_chart
As
Select employee2.ename, manager. mname
From employee2, Manager
Where employee2.manager _ id = manager. ID
Group by manager. mname, employee2.ename
In this example, two data tables are joined by the manager_id value. The final result is grouped by the manager name and displayed in the org_chart View table, as shown in 18-6. NOTE: If an employee under a manager is not included in the employee2 data table in the Manager data table, the view table will not display the record project of the manager. If an employee in the EMPLOYEE 2 data table is not listed in the Manager data table, no project is recorded in the View table. Generally, users can see a virtual data table composed of employees and their managers.

　
 
Figure 18-6 org_chart View table
Summary
　
Aggregation View tables can be used in many aspects, such as average and total of inspection departments. For example, to prepare a budget, you can use a summary view to view the salary status of each department of the company. This task can also be achieved using a T-SQL query. The advantage of using a view table is that users can simply perform the view without having to understand the execution and operation details of the summary and T-SQL.
________________________________________
Description
The SQL Server aggregate function computes a group of values and returns a single value. Aggregate functions include AVG, Count, Max, Min, and sum.
________________________________________
The following statement uses the sum function to calculate the total number of employee data tables:
Create view sal_vw
As
Select Dept,
Sum (salary) as [sum (salary)]
From employee
Group by Dept
In this example, the view table creates a virtual data table that displays the total salary of each department. The final data is classified by department, as shown in 18-7. This summary view table is relatively simple. In fact, the view table can perform more complex functions as needed.

　
 
Figure 18-7 sal_vw View table
Merge split data tables
　
A View table is also used to merge split data tables to form a single large virtual table. We usually reduce the size of data tables and indexes by splitting data tables. To split a data table, you must first create several small data tables to replace the original large data table. Then, each small data table is assigned a specific range of values from the original large data table. For example, a company can create many small data tables. Each small data table contains sales records for the week. To view the sales history of the company, merge these small data tables using the View table, without entering all sales records into a large data table at the beginning.
The view table shown in Figure 18-8 looks like a data table in a large data table. In fact, this table is a combination of several small data tables, and each data table has its own index (in fact, the date index of the cluster is more suitable here ).

　
 
Figure 18-8 Use A View table to merge and split data tables
As described earlier, the split data table creates a more manageable system for DBA, And the merged and split data table presents the data to the user in a simplified way.
To create a view table that combines split data, you must first create a split data table. These data tables may contain sales data. Each data table stores data in a specific period (about one week or one month ). When these data tables are created, you can use the Union all statement to create a view table containing all the data. For example, if there are four data tables named table_1, table_2, table_3, and table_4 respectively, the following statement can be used to create a large virtual data table that contains all the data:
Create view partview
As
Select * From table_1
Union all
Select * From table_2
Union all
Select * From table_3
Union_all
Select * From table_4
Currently, all data is stored in the same data table (View table) and is easy to manage. However, if you create a new split table and delete the old split table, you must recreate the View table.
Use Enterprise Manager to create a view
　
This section uses Enterprise Manager to create a view table in the northwind database. The procedure is as follows:
1. In the Enterprise Manager window, expand the database data folder and click northwind database, as shown in Figure 18-9.
　
 
Figure 18-9 shows the northwind database information
2. Press the right button on the northwind database to open a shortcut menu. Select Add/view to enter the new view table window, as shown in 18-10. Use this window to define the name of the View table, view the data rows used by the table, and the underlying data table.
The New View table window contains the following four panes:
O The Chart pane displays the data tables used to create a view table. Data rows can be selected in this pane.
　
The O square pane displays the data rows that are selected from the underlying data table to form the View table. Data rows can be selected in this pane.

　
 
Figure 18-10 New View table window
O the SQL pane displays the SQL statement used to define the View table. When you drag data in the chart pane or select a row in the pane, SQL Server generates an SQL statement. You can view the generated SQL statement in the SQL pane.
　
O The result pane displays the data columns retrieved from the View table and displays the data.
　
Click related icons in the tool column of the New View table window to display or hide a pane. Other panes in the tool column also provide important options, the following describes other options in these Tool Columns from left to right:
O archive storage View table.
　
O properties can change the attributes of the View table. Click this icon to display the Properties window. Here, you can set the option to display only one repeat data and encrypt the view.
　
O display/hide pane (four icons) You can select to display or hide any four panes of the New View table window.
　
O run and query displayed in the result pane to verify whether the query is correctly executed.
　
O cancel execution and clear the result clearing result pane.
　
O. Verify that SQL queries the underlying data table and verifies that the SQL statement is correct.
　
O remove filtering remove the defined filtering conditions.
　
O use 'group by' to add a group by clause in the statement of the SQL pane.
　
O join a data table to add a data table for query.
　
3. Modify the SELECT statement in the SQL pane to conform to the s elect statement shown in Figure 18-11. This table consists of companyName, contactname, and phone data rows. After entering the SELECT statement, click the verify SQL button to check whether the query is valid. If the check succeeds, click OK in the following dialog box to allow Enterprise Manager to fill the data in the chart pane and square pane. The New View table window is shown in 18-11.
　
 
Figure 18-11 the "add view table" window after data is filled in
4. Use the result pane to close the New View table window after you confirm that the view table is executed as expected. A dialog box is displayed, asking if you want to store the View table. If you click OK, You will be prompted to select a name for the view table. Enter a descriptive name and click Save.
The view table can now be used. You can use Enterprise Manager to set attributes of a New View table, including permissions. The "Modify and delete a table" section after this chapter details the "View table attributes" window.
Use the create view wizard
　
Follow these steps to use the create view Wizard:
1. In Enterprise Manager, select the wizard from the tool menu, expand the database, select create View table wizard, and click OK. Welcome to create View table wizard, as shown in Figure 18-12.
2. Click Next to display the select database window. Here you can select the database for which you want to create a view table. In this example, the database is the northwind database.
　
 
Figure 18-12 Welcome To The create view wizard window
3. Click Next to enter the selection Object window, as shown in Figure 18-13. You can select one or more tables to be referenced in the View table. Assume that you have created a simple view table. select a single data table. Assume that you want to join and create a view table, select multiple data tables.
　
 
Figure 18-13 select Object window
4. Click Next to select a data row, as shown in Figure 18-14. You can select the data rows to use in the View table. In this example, companyName, contactname, and phone data rows are used.
　
 
Figure 18-14 select data rows
5. Click Next to enter the definition restriction window, which defines the WHERE clause as needed to restrict the selected data columns in the View table.
6. Click Next to enter the name of the View table, as shown in 18-15. Enter the name of the View table in the text box.
7. Click Next to enter the create view wizard window, as shown in Figure 18-16. Here, you can click Save View table or change settings in the previous step. You can also click Cancel to cancel creating View table.
　
 
Figure 18-15 "name a view table" Window

　
 
Figure 18-16 "create view wizard completed"
Tips for using a view table
　
When creating a view table, remember that the view table is composed of an SQL statement that accesses the underlying data. The following principles can be followed to improve the database execution efficiency:
• The security mechanisms provided by the View table allow users to access data only from the View table and not directly access the underlying data table of the View table, therefore, unnecessary data will not appear on the View table. This also increases security because users can only view the data defined in the View table, but not the data in the underlying data table. When users only allow access to some information about a data table, there is no need to create a new data table for this purpose, so it does not increase the burden on the database.
　
• Make good use of indexes because when using a view table, the data in the underlying data table is accessed, including the index set for the data row. If the data table has a data row set as an index, define the WHERE clause in the SELECT statement of the View table to include this data row. This index is used only when the data row is part of the View table and used in the WHERE clause. For example, if an index is created on the dept data row of the employee data table and the data row is included in the View table, the index can be used in the View table.
　
• Split the data into a table to split the data. The advantage of Data splitting is to reduce the time it takes to create an index and to manage virtual data tables by reducing the space occupied by independent components. For example, splitting data into several small data tables before re-indexing is much shorter than rebuilding an index on a large data table. Therefore, you can clearly combine each data table into a large data table by defining the View table. This method is especially useful for large data tables that store historical data.
　
Modify and delete a table
　
Use Enterprise Manager or T-SQL commands to modify a view. Using Enterprise Manager is easier, but the advantage of using T-SQL commands is that you can reuse it once you build a command. Both methods are demonstrated in this section.
Use Enterprise Manager to modify or delete a view
　
Follow these steps to modify or delete a view table:
1. Expand the database data folder on the selected server in Enterprise Manager. Expand the database where the view table is located, and click the View table icon to display all the View tables in the right pane, as shown in Figure 18-17.
　
 
Figure 18-17 click the Enterprise Manager window after the View table
2. Click the right button after the name of the View table to be modified or deleted. A shortcut menu is displayed, as shown in 18-18. Select Delete to delete the View table. To modify the View table, select design view.
　
 
Figure 18-18 shortcut menu of the View table
3. if you select Delete, the object removal dialog box is displayed, as shown in Figure 18-19. Click the show dependency button to view the underlying architecture of the View table and view the dependency between the View table and the data table. If the selected view table is a join or union View table, all related data tables are displayed here; if the selected view table is a data row or data column View table, only one data table is displayed here. When you are sure you want to delete the table, click "delete all" to delete it.
　
 
Figure 18-19 "delete objects" dialog box
If you select design view, the 18-20 design view dialog box is displayed. This dialog box is similar to the new view table window shown in Figure 18-10. You can use this dialog box to modify the View table as you did before.

　
 
Figure 18-20 design view table dialog box
4. After the modification is complete, click Close to end the window. The system will prompt you to save the modified view table.
After the modification is complete, you can set the table view permission. Open the View table Properties window first (click the View table name in Enterprise Manager and press the right button to select the content in the shortcut menu), and then click the permission to enter the permission window to modify the View table permissions, the detailed setting method will be introduced in chapter 34th of this book.
As you see it, it is easy to use Enterprise Manager to modify a view, but it is easier to use T-SQL if the view you want to modify or delete is a larger view, because the T-SQL can be used to save the T-SQL statement as a script code.
Modify and delete a view using a T-SQL
　
The alter view command can be used to modify the View table. The alter view command is similar to the create view command. The syntax is as follows:
Alter view view_name [(column, column,...)]
[With encryption]
As
Your SELECT statement
[With check option]
The difference between the alter view command and the create view command is that if the view table already exists, the create view command cannot be executed. If the specified view table does not exist, the alter view command cannot be executed. (The with encryption and with check option keywords can be used selectively, as described in the previous section "Create a view using a T-SQL" in this Chapter .)
Now let's go back to the example of merging and splitting data tables and execute the alter view command (return to the section "merging and splitting Data Tables" before this chapter ). We will add a new split after deleting the split to demonstrate how to use the alter view command:
Alter view partview
As
Select * From table_2
Union all
Select * From table_3
Union all
Select * From table_4
Union all
Select * From table_5
The modified view table looks the same as the view table before the alter view command is executed. However, you have used alter view to select different datasets. Currently, the view table references table_5 and does not use table_1 any more.
To delete a view table, run the drop view command. The syntax is as follows:
Drop view view_name
Enhanced viewing table functionality in SQL Server 2000
　
SQL Server 2000 has two improvements to the view table: an updatable distributed split view table and an index that can be created in the View table. Let's take a look at these two enhanced features.
Updatable distributed split view table
　
In SQL Server 7 and earlier versions, the data displayed in the View table is the actual status of the underlying data table, which is static data. However, in SQL Server 2000, when the split view table is updated, the view table and the underlying data table are updated. In addition, the split view table can span across multiple SQL Server 2000 systems. The split view table can be used to establish database server consortium. Federation is a group of servers managed independently but operated collaboratively to distribute the system's processing load. Database Server consortium is formed by dividing data, allowing you to expand the system. The Database Server Alliance supports large e-commerce web sites or enterprise-type database systems. Figure 18-21 shows an example of a database server alliance.

　
 
Figure 18-21 SQL Server System Alliance
Before creating a split view table, you must split the data table horizontally. The original data table is replaced by several smaller Member Data Tables. Each member data table has the same number of data rows as the original data table, each data row has the same attributes (such as data type, size, and order) as the corresponding data row in the original data table ). If a distributed data split table is created, each member data table is located on an individual member server. In order to easily identify the location of a member data table, the member data table of the member database should have the same name on each member server. Although this is not a necessary condition, it can be conveniently managed by the system.
After you design a member data table, let each data table store the original data according to a certain data range (horizontal cutting. The range of values in each member data table is limited by the check condition constraints of split data rows, and the range cannot overlap. Now let's look at a horizontal split example. In this example, the customer data table is divided into four data tables. These three data tables are placed on different servers, and each server contains 3000 records of the customer data table. Constraints are included in the following create table statement:
Server 1:
Create Table customer_table_1
(Customerid integer primary key
Check (customerid between 1 and 3000 ),
.
. (Additional column definitions)
.
-- In Server 2:
Create Table customer_table_2
(Customerid integer primary key
Check (customerid between 3001 and 6000 ),
.
. (Additional column definitions)
.

-- In Server 3:
Create Table customer_table_3
(Customerid integer primary key
Check (customerid between 6001 and 9000 ),
.
. (Additional column definitions)
.

-- In Server 4:
Create Table customer_table_4
(Customerid integer primary key
Check (customerid between 9001 and 12000 ),
.
. (Additional column definitions)
.
After creating a member data table, define a distributed split view table on each member server. All View tables should have the same name to facilitate application writing. Distributed split View tables make queries executed on any Member Server as if the referenced data were located on the local machine. In other words, if the query executed on the Member Server references data on other member servers, the data is still immediately transmitted back, just as it is located on the local machine.
To achieve data passthrough between member servers, you must add a connection server definition to each member server to provide the online information required by each member server in the consortium, allows distributed split View tables to access data on other servers. You can use enterprise manager or T-SQL commands to create a linked server definition.
Use T-SQL to set up a linked server
　
The following syntax is defined for creating a linked server using T-SQL commands:
Sp_addmediaserver [@ Server =] 'server'
[, [@ Srvproduct =] 'product _ name']
[, [@ Provider =] 'provider _ name']
[, [@ Datasrc =] 'data _ source']
[, [@ Location =] 'location']
[, [@ Provstr =] 'provider _ string']
[, [@ Catalog =] 'catalog ']
Sp_addmediaserver the prestored program can contain the following independent variables:
• @ Server connection server name. If SQL Server has multiple execution instances, you must set the Instance name to SERVER_NAME/instance_name.
　
• @ Srvproduct ole db Provider product name. If the SQL Server 2000 system is linked to another SQL Server 2000 system, you do not need to specify @ srvproduct.
　
• Unique programmatic identifier of the ole db Provider previously specified in @ srvproduct. If two SQL Server 2000 systems are connected, you do not need to specify @ provider.
　
• @ Datasrc refers to the data source name interpreted by ole db Provider. If there are two SQL Server 2000 connections, you do not need to specify @ datasrc. If you want to link to a specific execution instance on the linked server, you must specify @ datasrc, the format is SERVER_NAME/instance_name.
　
• @ Location refers to the database location interpreted by ole db Provider. If two SQL Server 2000 systems are connected, you do not need to specify @ location.
　
• @ Provstr the specific connection string of a specific ole db Provider. If two SQL Server 2000 systems are connected, you do not need to specify @ provstr.
　
• @ Catalog refers to the database directory used when going online to ole db Provider.
　
For example, the following T-SQL command creates a connection server definition so that four servers-1, 2, 3, and 4 can communicate with each other.
Server 1:
Sp_addmediaserver 'server2'
Sp_setnetname 'server2', 'SQL-server-02'
Sp_addmediaserverlogin sserver2, 'false', 'sa ', 'sa'
Sp_addmediaserver 'server3'
Sp_setnetname 'server3', 'SQL-server-03'
Sp_addmediaserverlogin sserver3, 'false', 'sa ', 'sa'
Sp_addmediaserver 'server4'
Sp_setnetname 'server4', 'SQL-server-04'
Sp_addmediaserverlogin sserver4, 'false', 'sa ', 'sa'
Server 2:
Sp_addmediaserver 'server1'
Sp_setnetname 'server1', 'SQL-server-01'
Sp_addmediaserverlogin sserver1, 'false', 'sa ', 'sa'
Sp_addmediaserver 'server3'
Sp_setnetname 'server3', 'SQL-server-03'
Sp_addmediaserverlogin sserver3, 'false', 'sa ', 'sa'
Sp_addmediaserver 'server4'
Sp_setnetname 'server4', 'SQL-server-04'
Sp_addmediaserverlogin sserver4, 'false', 'sa ', 'sa'
Server 3:
Sp_addmediaserver 'server1'
Sp_setnetname 'server1', 'SQL-server-01'
Sp_addmediaserverlogin server1, 'false', 'sa ', 'sa'
Sp_addmediaserver 'server2'
Sp_setnetname 'server2', 'SQL-server-02'
Sp_addmediaserverlogin server2, 'false', 'sa ', 'sa'
Sp_addmediaserver 'server4'
Sp_setnetname 'server4', 'SQL-server-04'
Sp_addmediaserverlogin sserver4, 'false', 'sa ', 'sa'
Server 4:
Sp_addmediaserver 'server1'
Sp_setnetname 'server1', 'SQL-server-01'
Sp_addmediaserverlogin sserver1, 'false', 'sa ', 'sa'
Sp_addmediaserver 'server2'
Sp_setnetname 'server2', 'SQL-server-02'
Sp_addmediaserverlogin sserver2, 'false', 'sa ', 'sa'
Sp_addmediaserver 'server3'
Sp_setnetname 'server3', 'SQL-server-03'
Sp_addmediaserverlogin sserver3, 'false', 'sa ', 'sa'
In addition to sp_addmediaserver, we also use two other statements: sp_setnetname and sp_addjavassrvlogin. These two statements can assist in the processing of distributed split View tables. Call sp_setnetname to link the connection server name in an SQL Server to the Network Name of the SQL Server. In the previous example, the linked server named Server 2 is in the server on the network named sql-server-02 and specifies the information required to log on to the linked server. Call sp_addjavassrvlogin to specify the user identity and password of SQL Server when accessing the linked server.
Use Enterprise Manager to connect to the server
　
Use the following steps to connect to the server through Enterprise Manager:
1. Expand the security data folder on the server in Enterprise Manager, as shown in 18-22.
　
 
Figure 18-22 expand the server's "security" data folder
2. Click the right button on the linked server icon in the left pane to display the shortcut menu. Select Add link server to enter the link server Properties window, as shown in Figure 18-23.
　
 
Figure 18-23 General tab of "link server properties"
3. In the text box of the linked server, enter the name of the server you want to connect to, and click SQL server in the server type, as shown in 18-24.
4. Click the Security tab. Enter the local login name, select the simulated or enter the remote user and remote password. Figure 18-25 shows the window after the local login name is entered.
5. click confirm to complete the settings defined by the linked server.
After setting, you can use the linked server to modify or delete the properties of the linked server using Enterprise Manager. Enterprise Manager can also be used to view data tables and views on the linked server.

　
 
Figure 18-24 select the connection server type

　
 
Figure 18-25 Security tab of the linked server window
Create a view
　
After defining all connected servers, you can create an actual view table. The following example creates a view named sales, which is combined with the sales data table on the other four servers.
Create view sales
As
Select * From server1.bicycle. DBO. Sales
Union all
Select * From server2.bicycle. DBO. Sales
Union all
Select * From server3.bicycle. DBO. Sales
Union all
Select * From server4.bicycle. DBO. Sales
Go
Index View table
　
SQL Server 2000 allows you to create an index on the View table. Because the View table is actually a virtual data table, its appearance is no different from that of the actual data table. The T-SQL statement for creating an index on a view table is the same as creating a data table index (as described in Chapter 1 of this book ). Of course, when setting a data table, we enter the name of the data table, but when setting a view table, we need to enter the name of the View table, which is the only difference. Now we use the T-SQL command to create a cluster index on the View table named partview:
Create unique clustered index partview_cluidx
On partview (part_num ASC)
With fillfactor = 95
On partfilegroup
Index creation on The View table has several impacts on the system execution efficiency. The most obvious thing is to improve the execution efficiency when accessing data on The View table, which is the same as the logic that previously mentioned that indexes can improve the execution efficiency of data tables.
In addition, after an index is created in the View table, SQL server stores the result set returned by the View table in the memory. You do not need to specify the View table during query after the current day. Materializing refers to the processing program required by SQL Server to dynamically Merge data in the result set of a view table every time a query needs to be referenced. (Remember that the view table is a dynamic structure ). The specific process of viewing tables will actually cause a system burden. For a complex view table or a view table that contains a large amount of data, the Materialized View table must be repeated, which affects execution efficiency.
Another benefit for creating an index for a view table is that, even if the view table name is not directly used in the query from subsentence, the SQL Server Query Optimizer) the view table index is used in the query. In other words, you do not have to rewrite the existing query to retrieve data from the index View table to improve efficiency.
Of course, using index View tables to improve system execution efficiency is not only good but also good. For example, the maintenance of index View tables is complicated for SQL Server. Each time you modify the underlying data table of a view table, SQL Server must update the indexes in the View table result set and view table. Because the index range of a view table may be more complex than that of a single data table (for example, when the View table contains data from several large data tables ), the advantage of using an index to view table queries may be far less than the maintenance of the View table and index! Based on the maintenance considerations, before using an index to View tables, you must consider whether such settings really achieve our goal of improving the execution efficiency. Generally, when the data in the underlying data table is static data, when the query result Assembly applies to a large number of data columns, or most of the queries reference the underlying data table, we only consider using indexes to View tables.
Summary
　
In this chapter, I learned how to create a virtual data table using the View table. The View table is an auxiliary data structure. Although its appearance is the same as that of the data table, the view table stores SQL queries. These queries are connected to other queries to access the data in the underlying data table.
Referencing a view table in a T-SQL statement is the same way as referencing a data table. View tables can also be used to set security levels to protect confidential data and provide a simpler way to access data. In addition, the view table makes the data presentation logic. You can also create a single virtual data table by viewing the split data table.
In this chapter, we also learn the restrictions and principles of using a view table, and use an index to view the table in a timely manner. In Chapter 19, we will learn about transaction and transaction locking.