18. Establishment and use of the view table
What is the viewing table
View watch concept
Establish a view table
Modification and deletion of the inspection table
SQL Server 2000 enhancement function
Summary of this chapter
In Chapter 17, we have learned an index, an index is a database auxiliary structure, independent of the data structure of the database, but is related to the information of the access database. In other words, an index is an independent structure, but is integrated with the information. Below we will learn another database auxiliary structure: viewing table. Like the index, the inspection table and data are separated, and the use is used in the use of data. The view table first screens or processes the data before accessing the information. This chapter will learn more about what is the relevance of the viewing table, the viewing table and the information, the use, establishment, and management of the viewing table. Also look at the new feature of the visual table in Microsoft SQL Server 2000.
What is the viewing table You can imagine a viewpoint (view) to be a virtual data sheet, consisting of the result set of SELECT statements. For the user, the viewing table looks like a general data table, but the information contained therein may come from the results obtained after the query of different data sheets. In fact, the method of using the viewing table can be said to be the same, the user can reference the view table, method and reference data sheet in the T-SQL statement, such as SELECT, INSERT, UPDATE, and DELETE can be Operation in the inspection table. In fact, the viewing table is the pre-defined SQL statement. When the check-in table, SQL Server Query Optimizer combines the SQL statement of the query statement and the predefined visual table. The advantage of using the viewing table is that no review table with different properties without copying the information. The viewing table is very useful in many cases. Before this chapter we will see that the viewing table can be used to ensure the security of the information, and the simplicity of the query results is present, and the logic is expressed. In addition, you can use the viewing table to merge the split information.
Viewing Table Concepts will learn different types of viewing tables in this section, as well as the advantages and restrictions on the use of the watch. Viewing the different types of visual tables can be used according to the status, and the following is established: The subset of the data lines in the data sheet includes only a specific information line. It may be the most common visual table type, and you want to display simplified information, or use it based on safety considerations. The subset of the data columns in the data sheet includes only specific information columns, and can also be used based on security. A plurality of data sheets You can establish a viewpoint by Joint Operation, use the viewing table to simplify the complicated linkage operation.
The summary information inspection table only shows the summary of the sum of the complex operation. These types of view tables are different from the T-SQL Establishment Test Sheets later in this chapter. The viewing table can also be used for combining segmentation, based on management, large-scale data sheets may be divided into several small data sheets. Users can establish a viewpoint as needed to make several small data sheets merged on a large virtual data sheet. The advantage of the inspection table The first advantage of the viewing table is that the information presented by the exam table is always an instant information. Because the viewing table is defined by the SELECT statement, the SELECT statement is executed whenever access to the inspection table, that is, when the information is required, the query is required. Therefore, although the information in the underlying information sheet may change, the viewing table is always the latest information. The second advantage of using the viewing table is that the view table has a different security hierarchy. Since the query of the defined visual table is executed according to the security hierarchy when using the established. Therefore, the viewing table can hide the information that does not want to be seen by a level user. This feature will be discussed in this section of this chapter. The viewing table restrictions SQL Server has some restrictions on the establishment and use of the inspection table. The restrictions are as follows: The data line limit checkpoint can only reference 1024 data lines. If this limit is exceeded, you must use other methods to reference. The Database Limit View table can only be established in the current access database. The security restrictions in the viewing table must have permission to access all the information rows cited by the access checklist. Data Integrity Rules Any update, modification, etc. cannot undermine the integrity rules of the data. For example, if the underlying data sheet does not allow NULL values, the NULL value is not allowed. Netoperative View Table Level Restriction Test Table can be built on other inspection tables. In other words, a visual table that can access other inspection tables can be created, and the nestline hierarchy is up to 32 floors. SELECT statement restriction test table SELECT statement cannot include ORDER BY, Compute, Compute By statement and INTO keywords. Note Other restrictions on the viewing table, you can enter the "Establishment" index in the "Online Book" index, and enter the establishment of the topic.
Establishing a viewing table as an index, you can use different methods to establish a viewing table. If more viewing tables will be established in the future, it is recommended to use the Create View statement (T-SQL statement) to establish a view table, because this can be stored in the command code in the instruction code, when the T-SQL state is to be established Repeat the command code to modify the use. Other methods for establishing a viewing table are, using SQL-Server Enterprise Manager and establish a checklist. As with indexes and other operations, if multiple view tables are created in the future, it is recommended to use the T-SQL command. In the instruction code, enter T-SQL description, and then edit and use the file multiple times. If you understand the review to be established, you can simply establish a review using SQL Server Enterprise Manager. Finally, you can use the establishment of the test watch wizard to browse the process of establishing a checkpoint, whether it is an initiator or an expert, establishing a visual watch wizard is also useful. Establishing a T-SQL Establishment Table Using T-SQL Establishing Data Sheet: Use ISQL, OSQL, or SQL Server Query Analyzer, and perform Creat View to see the table. As mentioned above, you can store the T-SQL command into the instruction code. If you want to use it, you want to use it to modify it (please remember to store the database definition into the instruction code, so that you will use it after you reconstruct the database ). The following is the syntax for the CREATE View command:
Create View View_name [(Column, Column, ...)]]
[With encryption]
AS
Your Select Statement
[WITH CHECK OPTION] When establishing a visual table, you can start two options to change the behavior of the visual table. An option is to use the with encryption keyword, and the other option is used with the use of check option, or can be used with both. Let us now take care of these options. The WINCRYPTION keyword will encrypt the definition of the test. SQL Server uses the encryption method and password of the password. This security mechanism makes the user who use the hierarchy unable to know which information table is being accessed. WITH CHECK OPTION Keyword Specifies any information modification statement on the inspection table must comply with the benchmark set in the SELECT statement defined in the visual table. For example, if a data column is established on the inspection table, the information column cannot be displayed on the inspection table, which is not allowed. Suppose a viewing table is defined as "All Financial Sector Employee Related Information", usually, if you do not specify the WITH CHECK OPTION option, you can change the value of the "Department" to other sectors. If you specify the WITH CHECK OPTION, this modification is not allowed because the value of the "Department" data line of any information column will not be able to access the data through the checkpoint. The WITH CHECK OPTION keyword can specify whether it cannot be executed in the visual table that will not be changed in the inspection table. As long as you modify the definition of SELECT statements in the checklist, you can build any view tables you need, such as the subset of the data line or the data column, or join operations. Here will learn how to build different types of view tables using T-SQL. The inspection table constructed by the data line set by the data line provides a certain degree of security for the information you want to open. Now look at the following example. Suppose a company's internal repository, there is a data sheet named Employee, and the information contained in Figure 18-1 is shown.
Figure 18-1 Employee Data Sheet
Most of the information in the data sheet belong to private information, and cannot be seen by other employees. However, some information must be aware of general employees. The focus here is to establish a viewing table that allows all employees to access specific information, which can solve the problem, in addition, the inspection table can also avoid other data sheets in the database contain repetitive employee data. Use the following T-SQL statement to establish a view table that can access Name, Phone and Office Data lines for the Employee tab:
Create View EMP_VW
AS
SELECT NAME,
Phone,
office
From Employee
The information included in the established inspection table is shown in Figure 18-2. Although all the information is present in the underlying information sheet, users who pass through the viewing table can only see the information line selected in the visitor. Since the viewing table can have different security hierarchies, although the viewing table allows anyone to access, the underlying information sheet is still safe. In other words, only the human resource department can only access all the information of the Employee task, while other department employees can only use the viewing table. Profile List of subsets The viewing table consisting of data column sets can limit the data columns that users can access. Suppose the Employee tab fills in the information, as shown in Figure 18-3. In this example, we do not limit the information list through the WHERE statement, 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 Sheet with Information
The results of the viewing table show only the information columns of the employees working in the department 1, as shown in Figure 18-4. Suppose the Human Resources Department must only access the records of employees within the department, this viewing table is useful. Like the viewing table consisting of the data line set, the inspection table composed of the data column set can also be assigned different security hierarchies.
Figure 18-4 The EMP_VW2 checklist is hidden by the actual Join statement by defining the connection in the inspection table, which simplifies the T-SQL statement of access data. For example, two data tables are named Manager and Employee2, as shown in Figure 18-5.
Figure 18-5 Manager and EmployeE2 Data Sheet
Use the following statements to link two data sheets to a virtual data sheet:
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, the two data sheets are linked through the Manager_ID value. The final result is packet in accordance with the manager's name, displayed in the ORG_CHART visual table, as shown in Figure 18-6. Note that if in the Manager tab, the employee of a manager is not included in the EmployeE2 data sheet, and the manager does not appear the manager's recording item. If an employee in the Employee2 table, its manager is not listed in the Manager data table, and there will be no record item in the inspection table. For general users, it sees a virtual data sheet consisting of employees and their managers.
Figure 18-6 ORG_CHART view table
AGGREGATION visual table can be used in many ways, such as the average, sum of the averages, and the like. For example, to prepare a budget, a summary inspection table is used to view the salary of the company. This task can also be achieved using a T-SQL query. The advantage of using the viewing table is that the user can simply perform the screen without having to understand the details of the summary and the Detail of the T-SQL.
Note The SQL Server summary function is subjected to a set of values. The summary functions include AVG, Count, Max, Min, and Sum.
The following statements use a summary function (SUM) to calculate the sum of the Employee tab:
Create View Sal_vw
AS
SELECT DEPT,
SUM (SALARY) AS [SUM (SALARY)]
From Employee
GROUP BY DEPT
In this example, the viewing table establishes a virtual data sheet to show the sum of the salary of each department. The final information is classified according to the department, as shown in Figure 18-7. This summary checklist is relatively simple. In fact, the viewing table can perform more complex functions according to actual needs.
Figure 18-7 SAL_VW view table
Merger Division Table Inspect Table is usually used to combine segmentation information tables to make a single large virtual data sheet. Usually we will use the way of dividing data sheets, reduce the size of the data sheet and the index. To split the data sheet, you must first establish several small data sheets to replace the original large-scale data sheet, and then each small data sheet allocates a specific range of values from the original large-scale data sheet. For example, a company can build many small data sheets, each of which includes sales records of the week. To view the company's sales history, use the inspection table to merge these small information tables, and No need to enter all sales records into a large-scale data sheet at the beginning. Figure 18-8 shows the view table shown in the user, like a large-scale information, in fact, the data sheet is a combination of several small data sheets, and every information table has its own Index (in fact, the date index of the cluster is more applicable here).
Figure 18-8 Data sheet data using the test table combined
As mentioned above, the divided information sheet is a more susceptible to managing system, while the combined divisions are presented to the user in a simplified manner. To establish a viewpoint of a merger division, first establish a split information table. These information sheets may contain sales information. Each information table will store a specific period (about one week or one month). When these data sheets are completed, you can use the Union ALL statement to create a view table containing all information. For example, it is assumed that there are four data sheets, named Table_1, Table_2, Table_3 and Table_4, and the following statements can be used to create a large virtual data sheet containing all materials: Create View Partview
AS
SELECT * from Table_1
Union all
SELECT * from TABLE_2
Union all
SELECT * from home_3
Union_all
SELECT * from home_4
Now all the information is in the same data sheet (ie, for the viewing table) and is easy to manage. However, if you create a new split data list and remove the old split information list, you must rebuild the view table. Using Enterprise Manager Establishment Table This section we will use Enterprise Manager to create a view table in the Northwind database, steps below:
In the Enterprise Manager window, expand the database folder, press the Northwind database, as shown in Figure 18-9.
Figure 18-9 Shows Northwind Database Information
Press the right button on the Northwind database to call the fast display function. Select a new / view table to enter the new test table window, as shown in Figure 18-10. Using this window to define the visual table name, the data line used by the table, and the underlying information sheet. The new inspection table window contains the following four panes:
The chart pane displays the information sheet data for establishing a visual table. The data line can be selected in this pane.
The square pane displays the information line from the selection table selected from the underlying data table. The data line can be selected in this pane.
Figure 18-10 New View watch window
The SQL pane displays the SQL statement for defining the visual table. When you drag the information in the chart pane or select the data line in the checkered pane, SQL Server generates SQL statements, which can be viewed in the SQL pane. The result pane displays the information that is retrieved from the inspection table, providing information that should be displayed. In the new inspection table window, you can display or hide a pane. Other panes on the tool column also provide important options, the following explains these tools by left to right other options:
Archive Storage Test Table. Properties can change the properties of the inspection table. Press this image to display the properties window, where you can set the repetition and encryption check options. The display / hidden pane (four graphics) can select any four panes that display or hide the new detection table window. Perform the execution and display in the result pane, verify that the query is executed correctly. Cancel the result pane in cancellation and clearance. Verify that SQL is correct for the underlying information sheet query and verifying whether the SQL statement is correct. Remove the filter removal of the defined filter criteria. Use 'Group By' to add a Group By clause in the SQL pane state. The addition of the data sheet can be added to the query.
Modify the SELECT statement in the SQL pane to comply with the S ELECT statements shown in Figure 18-11. This inspection table is composed of CompanyName, ContactName, and Phone. After entering the SELECT statement, press the verification SQL button to detect if the query is valid. If the detection passes, press OK in the next dialogue, allow Enterprise Manager to fill the data into the chart pane and the square pane. The new inspection table window will be shown in Figure 18-11. Figure 18-11 "New Inspection Table" window after filled in the information
With the results pane, confirm that the view table is performed according to the expected execution, close the new test table window. The dialog box will appear, and ask if you want to store the inspection table. If you are sure, you will be prompted to select the name for the visual table. Enter a descriptive name and press to determine the above operation. Now the viewing table can be used. You can use Enterprise Manager to set the properties of the new visual table, including setting permissions. The In Enterprise Manager, select the wizard from the tool function table, expand the database, select the establishment of the checkpoint wizard, then click OK to enter the welcome to use the Jewelry Wheel, as shown in Figure 18-12. Press the next step to display the selection database window, you can choose the database to establish a checkpoint, this example is the Northwind database. Figure 18-12 "Welcome to the Establishment Table Elf" window Press Next to enter the selected item window, as shown in Figure 18-13. Here you can select one or more of the items to reference to the table. Suppose is a simple viewing table, select a single data sheet. Assume that you want to connect to establish a viewpoint, select multiple data sheets. Figure 18-13 "Select the item" window Press the next step to enter the selection line, as shown in Figure 18-14. Here you can choose to the information line you want to use in the view table, this example is the company, ContactName, and Phone data lines. Figure 18-14 "Selection Data Walk" window Press Next to enter the definition limit window, this window can define the WHERE clause to limit the information column selected by the viewing table. Press the next step to naming as a viewpoint, as shown in Figure 18-15. Enter the name of the visual table in the text block. Press Next to enter the completion of the establishment of the watch wizard window, as shown in Figure 18-16. Here you can press the completion of the storage inspection table, or press the previous change setting, you can also cancel the discovery checklist. Figure 18-15 "Name Name" Figure 18-16 "Completing the Establishment Test Sheet Elf" window Using the inspection table Tips When establishing a visual table, remember that the view table is constructed by the SQL statement of the underlying material. Follow the following usage principles, you can improve the performance efficiency of the database: The safety mechanism for the viewing table can be used to check the user only through the inspection table to access the data sheet, so it is not necessary. The information will not appear on the inspection table. This can also increase security because users can only see the information defined in the checklist, and the information in the base data sheet can be seen. When the user only allows some information of the access to the table, there is no need to re-establish the new data sheet for this purpose, so it will not increase the database burden. Good use of indexing is due to the use of the viewing table, it is information to access the underlying information, including the set index of the data line. If the data table has a data set as an index, define the WHERE clause in the Select statement of the visual table including this information line. This index is only used when the data is part of the viewing table and is used in the WHERE clause. For example, if an index is established on the DEPT data line of the Employee task, the data line is included in the view table, then the index can be used in the visual table. Splitting the data can be used to divide the information. The benefit of dividing data is to reduce the time of the establishment index and achieve the purpose of managing the virtual data sheet through the space occupied by the independent component. For example, the data is divided into several small data sheets and then rebuild the index, which is shorter than the time required to reconstruct the index in a large data sheet. Therefore, it is clear that each of the data sheets can be combined into a large-scale data sheet in the way, and this method is particularly useful for the large-scale data sheet for storage historical materials. Modifications to the View Table and Delete Use the Enterprise Manager or T-SQL command to modify the visual table. The use of Enterprise Manager is simpler, but the advantage of using the T-SQL command is to reuse after establishing an command. Both methods will be demonstrated in this section. Use Enterprise Manager to modify or delete a view table. Please refer to the following steps to modify or delete the view table: In the Enterprise Manager, expand the database folder in the selected server. After expanding the database where the visual table is located, press the view table to display all the inspection tables in the right pane, as shown in Figure 18-17. Figure 18-17 Note Enterprise Manager window after viewing the test table When the name is pressed and the right button will appear after you want to modify or delete, the fast display function table will appear, as shown in Figure 18-18, select Delete to delete the view table. To modify the viewing table, select design inspection. Figure 18-18 Explicit function table If you choose to delete, you will enter the access item dialogue, as shown in Figure 18-19. Press the situation button that displays the dependencies to see the underlying architecture of the visual table, and you can see the dependency of the checklist and data sheet. If the selected view table is a Join or a Union checklist, all related information sheets are displayed here; if the selected checklist is the data line or the data list, you will only look at it. Go to a data sheet. When it is determined that the view table is to be deleted, press the removal of all to execute the deletion. Figure 18-19 "Removing the object" dialog If you select a design review, a design inspection table dialog box is shown in Figure 18-20. This dialogue is similar to the new detection table window seen in Figure 18-10, using this dialog box to modify the test table as the method of the previously established checkpoint. Figure 18-20 "Design View" dialogue After the modification is complete, press the Close button to end the window, and the system will prompt you to store the modified visual table. When the modification is complete, you can set the permissions of the visual table. First turn on the check-on properties window (click on the check table name in the Enterprise Manager, press the right button, select the content in the quick function table), then press the permission to enter the permissions window modification checkpoint permission, detailed settings will be in this book Chapter 34 introduces. As you look, use Enterprise Manager to modify the check table is very easy, but if the view table to be modified or deleted is a larger view table, use T-SQL will be more convenient, because use T-SQL can state Store as the instruction code. Use the T-SQL Modify and Delete Test Table to use the alter view command to modify the visual table. The alter view command is similar to the use of 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 check table already exists, the CREATE VIEW command cannot be executed; if the specified inspection table does not exist, the alter view command cannot be executed. (With Encryption and WITH CHECK OPTION These two keyword selectable uses, the method is described in this chapter "Use T-SQL Establishment Table> section has been introduced.) Now let us return to previous merger data sheet examples In actually executing the alter view command (return to the data table> section of the Alter View Partview AS SELECT * from TABLE_2 Union all SELECT * from home_3 Union all SELECT * from home_4 Union all SELECT * from home_5 The modified visual table appears to be the same as before the ALTER View command, but in fact, the ALTER VIEW has been used to select different data sets. Now the view is referenced by Table_5, no longer use Table_1. To delete a visual table, use the drop view command, the syntax is as follows: Drop view view_name SQL Server 2000 Enhancement Function SQL Server 2000 has two improvements in the viewing table: an updated decentralized segmentation table; and the search table can establish an index. Let's take a look at these two enhancements. The updateable distributed segmentation table is in SQL Server 7 and an earlier version, the information presented by the checklist is the actual state of the underlying information sheet, which belongs to static data. But in SQL Server 2000, update the segmentation checklist, the checklist and the underlying information sheet are updated. In addition, the segmentation checkpoint can span multiple SQL Server 2000 systems. The segmentation view table can be used to establish a database server alliance. Federation is independent management, but it works together to disperse the server's server-processed servo group. By dividing data to form a database server alliance, you can expand your system. The Database Server Alliance supports large-scale e-commerce web websites or an enterprise-based database system. Figure 18-21 Setting an example of a Database Server Alliance. Figure 18-21 SQL Server System Alliance Before you build a split test table, you must first divide the information sheet. The original data sheet is replaced by several smaller member information sheets. Each member information table has the same number of data sheets, and each data line has the same attribute as the corresponding data in the original data sheet. (Eg, data type, size, order). If it is established, a decentralized data segmentation checkpoint is established, each member information table is located on an individual member server. In order to easily identify the position of the member information table, the member data sheet of the member database should be the same in each member server, although this is not a necessary condition, but can be easily managed. After you design the member data sheet, let each of the information tables store the original information in accordance with a certain amount of information (horizontal cut). The range of each member data sheet is compared to the CHECK condition constraint of the divided data line, and the range must not overlap. Now let's see an example of horizontal segmentation. In this example, we divided the Customer Data Sheet into four data sheets, and the three data sheets are placed in different servers, and each server contains 3,000 Customer data sheets. The constraint is included 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) . - 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 establishing a member information sheet, a decentralized split checklist is defined on each member server. All inspection tables should have the same name to facilitate the application of the application, the decentralized segmentation checkpoint makes the query executed on any member server, which seems to be the same as the information referenced. In other words, if the query executed on the Member Server is the information on other member server, the information is still immediately transferred, just like the unit. In order to reach the data between each member server, the connection server must be added to each member server, providing the connection information required for each member server in the alliance to make a scattered segmentation Tables can access information on other servers. You can establish a link server definition using the Enterprise Manager or T-SQL command. Use the T-SQL Settings Connection Server The following syntax defined for the connection server using the T-SQL command: sp_addlinkedServer [@server =] 'Server' [, [@ srvproduct =] 'product_name'] [, [@ provider =] 'provider_name'] [, [@ DatasRC =] 'DATA_SOURCE'] [, [@ location =] 'location'] [, [@ provstr =] 'provider_string'] [, [@ catalog =] 'Catalog'] SP_ADDLINKEDSERVER This pre-save program can contain the following quotes: @server link server name. If SQL Server has multiple execution individuals, the name must be set to such type server_name / instance_name. @SrvProduct Ole DB Provider's product name. If the SQL Server 2000 system is connected to another SQL Server 2000 system, do not have to specify @srvproduct. @Provider previously specified the only program identification code specified by the OLE DB Provider specified in @SRVPRoduct. If you are two SQL Server 2000 systems, you don't have to specify @Provider. @DatasRC refers to a source name that is interpreted by OLE DB Provider. If it is two SQL Server 2000, it is not necessary to specify @Datasrc, but if it is to connect to the specific execution of the individual, you still want to specify @DataSRC, set the form of Server_name / instance_name. @Location refers to a database location constructed by OLE DB Provider. If you are two SQL Server 2000 systems, you don't have to specify @Location. @Provstr Specific Connection Strings for the OLE DB Provider. If you are two SQL Server 2000 systems, you don't have to specify @Provstr. @Catalog is the database directory used when connecting to OLE DB Provider. For example, the following T-SQL commands establish a connection server definition, allowing four servo-1, 2, 3, and 4 to communicate with each other. Server 1: Sp_addlinkedServer 'Server2' SP_setName 'Server2', 'SQL-Server-02' Sp_addlinkedServerLogin Sserver2, 'False', 'SA', 'SA' Sp_addlinkedServer 'Server3' sp_setnetName 'Server3', 'SQL-Server-03' SP_ADDLINKEDSERVERLOGIN SSERVER3, 'FALSE', 'SA', 'SA' sp_addlinkedServer 'Server4' Sp_setnetName 'Server4', 'SQL-Server-04' SP_ADDLINKEDSERVERLOGIN SSERVER4, 'FALSE', 'SA', 'SA' Server 2: SP_ADDLINKEDSERVER 'Server1' Sp_setnetName 'Server1', 'SQL-Server-01' SP_ADDLINKEDSERVERLOGIN SSERVER1, 'FALSE', 'SA', 'Sa'SP_ADDLINKEDSERVER' Server3 ' sp_setnetName 'Server3', 'SQL-Server-03' SP_ADDLINKEDSERVERLOGIN SSERVER3, 'FALSE', 'SA', 'SA' sp_addlinkedServer 'Server4' Sp_setnetName 'Server4', 'SQL-Server-04' SP_ADDLINKEDSERVERLOGIN SSERVER4, 'FALSE', 'SA', 'SA' Server 3: SP_ADDLINKEDSERVER 'Server1' Sp_setnetName 'Server1', 'SQL-Server-01' Sp_addlinkedServerLogin Server1, 'False', 'SA', 'SA' Sp_addlinkedServer 'Server2' SP_setName 'Server2', 'SQL-Server-02' Sp_addlinkedServerLogin Server2, 'False', 'SA', 'SA' sp_addlinkedServer 'Server4' Sp_setnetName 'Server4', 'SQL-Server-04' SP_ADDLINKEDSERVERLOGIN SSERVER4, 'FALSE', 'SA', 'SA' Server 4: SP_ADDLINKEDSERVER 'Server1' Sp_setnetName 'Server1', 'SQL-Server-01' SP_ADDLINKEDSERVERLOGIN SSERVER1, 'FALSE', 'SA', 'SA' Sp_addlinkedServer 'Server2' SP_setName 'Server2', 'SQL-Server-02' Sp_addlinkedServerLogin Sserver2, 'False', 'SA', 'SA' Sp_addlinkedServer 'Server3' sp_setnetName 'Server3', 'SQL-Server-03' SP_ADDLINKEDSERVERLOGIN SSERVER3, 'FALSE', 'SA', 'SA' In addition to using sp_addlinkedServer, we also use two other statements, one for sp_setnetname; another is sp_addlinkedsrvlogin. These two statements can assist the processing of the decentralized segmentation checkpoint. Through the call sp_setnetName statement, the connection server name in a sql server can be connected to the network name of the SQL Server. In the previous example, the connection server name is Server 2 is a server named SQL-Server-02 on the Internet and specifies the information required to log in to the linkage server. The use of SQL Server can specify the user identity and password when the SQL Server is accessing the servo. Using the Enterprise Manager linkage Server Use the following steps to use the Enterprise Manager linkage Server: In Enterprise Manager, the security data clip is expanded in the server, as shown in Figure 18-22. Figure 18-22 Expanding the "Safety" folder after the server On the linker server in the left pane, press the right button to call the fast display function table. Select a new link server to enter the link server properties window, as shown in Figure 18-23. Figure 18-23 "General" tab of "Connection Server Properties" In the text block of the connected server, enter the servo name you want to link, press SQL Server in the server type, as shown in Figure 18-24. Press the security tab. Enter the local login name, select analog or enter the remote user and the remote password. Figure 18-25 Shows the window after entering the name of the unit. Press to determine the settings of the connection server definition. After setting is complete, you can use the link server, use Enterprise Manager to modify or delete the attributes of the connection server. In addition, Enterprise Manager can be used to view the information sheets and view tables on the connection server. Figure 18-24 Selecting the type of connection servers Figure 18-25 Connecting the security page sign for the servo window Establishing a viewpoint after completing all link servo definitions, you can establish an actual inspection table. The following example will take you to create a visual table named Sales, and the viewing table combines the Sales data sheet 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 Table SQL Server 2000 allows you to establish an index on the inspection table. Since the viewing table is actually a virtual data sheet, its appearance and the actual information sheet are not different. The T-SQL statement established on the inspection table is the same as the establishment of the data table index, that is, Create Index statements (introduced in Chapter 17 of this book). Of course, when setting the information list, we have entered the name of the information sheet, but when setting the view table, you will enter the name of the view table, which is the only difference. Now, we use the T-SQL command to establish a clustered index on the view table named PartView: Create Unique Clustered Index Partview_Cluidx On Partview (Part_num ASC) WITH FILLFAACTOR = 95 On Partfilegroup
