SQL Server distributed partitioned view decomposition datasheet

Distributed partitioned views can connect data from one or more SQL Server databases. When developing a horizontal partitioned database environment, you can use distributed partitioned views to connect partitioned tables from different servers so that the data looks like it comes from the same server.

You can design these views, so if your potential data sheets are structurally designed, the query optimizer will be able to speed up by knowing the data that the query needs from that datasheet. A well-designed distributed partitioned view can also implement updates, inserts, and deletes. We'll delve into how it does this in the next section of this article.

Example

In this case, we assume that the Saleshistory table is very large and that it would be advantageous if the rows in the horizontal split table were recorded on different servers. The table structure of the Saleshistory table on each server is the same, but one server holds sales information for the eastern part of the country, while the other stores sales information in the western part of the country.

We differentiate the records in the table based on the region (region) field and the Saleid. Where the Saleid field is an integer data field, we set different saleid for different regions of the country.

This field is very important for the design concept because this is what we use as the partitioning key value field. (Note: This is extremely important to make table design in a smaller scenario, because the rows in the table are unique and can be distinguished from tables on other servers.) This field collection is the partitioning key.

Design a lot of salehistory table, according to the table Saleid is always the only distinguishable. We can do this with a check constraint.

We will use two separate instances of SQL Server, and for this example, these two instances are on the same machine. The name of the server is Chapman, and the instance is called instance A and instance B, respectively. These two instances are SQL Server 2005 development editions that allow remote connections as well as Windows and SQL Server authentication.

Use the script to create the SALESDB database, set the lazy schema validation option for each server, and use this option to improve performance in SQL Server by ensuring that server link requests are made when data is really needed on the server.

The script in List a needs to run on two database instances. List B is used to create reader logins and users in the SALESDB database, and the script also needs to run on two database instances.

列表A:
CREATE DATABASE SalesDB;
EXECUTE sp_serveroption
@server = 'CHAPMANServerA,
@optname = 'lazy schema validation',@optvalue = 'true'

列表B:
CREATE LOGIN reader WITH Password = '654asod3e**!!'
USE SalesDB
CREATE USER reader FROM LOGIN reader

列表C:
ServerA:
EXECUTE sp_addlinkedserver
'CHAPMANServerB',
'SQL Server'
EXEC sp_addlinkedsrvlogin 'CHAPMANServerB',
'false',NULL,'reader','654asod3e**!!'
ServerB:
EXEC sp_addlinkedserver
'CHAPMANServerA',
'SQL Server'
EXEC sp_addlinkedsrvlogin 'CHAPMANServerA',
'false',NULL,'reader','654asod3e**!!'

List C will be run alone on each database instance. The script creates a linked server on each server that is linked to each other. The connection server allows SQL Server to rely on an OLE DB data source to execute commands, just like any other SQL Server.

We use the login created in the above script as the security context for connecting to the server. These linked servers allow us to query another server from a single server.