Since version 2000, the SQL Server Enterprise Edition has introduced a distributed partitioned view that allows you to create views for two or more horizontal partitioned tables that are distributed across different SQL instances.
The brief steps are as follows: splitting large tables into smaller tables based on a set of values defined in a check constraint. Check constraints ensure that each small table holds unique data that cannot be saved in another table. Then use UNION ALL to create a distributed partitioned view that joins all these small tables into separate result sets.
This is useful for performance improvements, for example, if the view is partitioned by date and the query returns rows that are saved only in a partitioned table, SQL Server intelligently searches only one partition instead of all the tables in the distributed partitioned view.
We assume a scenario in which a company establishes a Shanghai and Beijing branch, each with its own instance of SQL Server to hold the site data, and wants to use a table to track site clicks. The amount of hits is very large. At this point, you need a view to reference the individual tables in a single view. The company wants to be able to query any server and return the same data or data from its respective branch office.
Here we demonstrate the application of this scenario using an example. Suppose there are two instances: ap4\net2012 and ap4\net2013 (all examples in this article are passed in a SQL Server 2008 environment).
The creation of a linked server , of course, can also be done by OPENROWSET (http://msdn.microsoft.com/zh-cn/library/ms190312.aspx) without creating a linked server and creating a test database and tables.
[SQL]View PlainCopy
- /********* Create a distributed partitioned view ***************/
- /********* [email protected] invited the month ***************/
- Use master
- GO
- EXEC sp_addlinkedserver
- ' ap4\net2013 ',
- N' sql Server '
- GO
- --Skip the check of the remote instance schema table to improve performance and invite monthly bets
- EXEC sp_serveroption ' ap4\net2013 ', ' lazy schema validation ', ' true '
- GO
- --Create a test database
- IF not EXISTS (SELECT name
- From sys.databases
- WHERE name = ' RemoteViewTest2012 ')
- BEGIN
- CREATE DATABASE RemoteViewTest2012
- END
- GO
- --Open the test library
- Use RemoteViewTest2012
- GO
- --Create a click table for Shanghai Branch
- CREATE TABLE dbo. Webhits_shanghai
- (Webhitid uniqueidentifier not NULL,
- WebSite varchar () not NULL,
- HITDT datetime not NULL,
- CHECK (WebSite = ' Shanghai '),
- CONSTRAINT pk_webhits PRIMARY KEY (Webhitid, WebSite))
A second example:
[SQL]View PlainCopy
- /*************** instance ap4\net2013 (SQL Server 2008) *********/
- /********* [email protected] invited the month ***************/
- Use master
- GO
- EXEC sp_addlinkedserver
- ' ap4\net2012 ',
- N' sql Server '
- GO
- --Skip the check of the remote instance schema table to improve performance and invite monthly bets
- EXEC sp_serveroption ' ap4\net2012 ', ' lazy schema validation ', ' true '
- GO
- IF not EXISTS (SELECT name
- From sys.databases
- WHERE name = ' RemoteViewTest2012 ')
- BEGIN
- CREATE DATABASE RemoteViewTest2012
- END
- GO
- --Open the test library
- Use RemoteViewTest2012
- GO
- --Create a click table for Beijing Branch
- CREATE TABLE dbo. Webhits_beijing
- (Webhitid uniqueidentifier not NULL,
- WebSite varchar () not NULL,
- HITDT datetime not NULL,
- CHECK (WebSite = ' Beijing '),
- CONSTRAINT pk_webhits PRIMARY KEY (Webhitid, WebSite))
II. Create a view in two instances, respectively
[SQL]View PlainCopy
- /*************** instance ap4\net2012 (SQL Server 2008) *********/
- /********* [email protected] invited the month ***************/
- --Open the test library
- Use RemoteViewTest2012
- GO
- --Create a partitioned view
- CREATE VIEW dbo.v_webhits as
- SELECT Webhitid,
- WebSite,
- Hitdt
- From RemoteViewTest2012.dbo.WebHits_ShangHai
- UNION All
- SELECT Webhitid,
- WebSite,
- Hitdt
- from [ap4\net2013]. RemoteViewTest2012.dbo.WebHits_BeiJing
- GO
- /*************** instance ap4\net2013 (SQL Server 2008) *********/
- /********* [email protected] invited the month ***************/
- --Open the test library
- Use RemoteViewTest2012
- GO
- --Create a partitioned view
- CREATE VIEW dbo.v_webhits as
- SELECT Webhitid,
- WebSite,
- Hitdt
- From RemoteViewTest2012.dbo.WebHits_BeiJing
- UNION All
- SELECT Webhitid,
- WebSite,
- Hitdt
- from [ap4\net2012]. RemoteViewTest2012.dbo.WebHits_ShangHai
- GO
Iii. inserting test data
We can select any one of the instances to insert, below we choose ap4\net2013
[SQL]View PlainCopy
- /*************** instance ap4\net2013 (SQL Server 2008) *********/
- /********* [email protected] invited the month ***************/
- ----to ensure the insertion, you must turn on the XACT_ABORT switch and open the Distributed Transaction Coordinator to invite the monthly note
- --Open the test library
- Use RemoteViewTest2012
- GO
- SET xact_abort on
- INSERT dbo.v_webhits
- (Webhitid, WebSite, HITDT)
- VALUES (NEWID (), ' Shanghai ', GETDATE ())
- INSERT dbo.v_webhits
- (Webhitid, WebSite, HITDT)
- VALUES (NEWID (), ' Beijing ', GETDATE ())
Note that if MSDTC (Microsoft Distributed Transaction Coordinator) is not enabled on the server on which the instance resides, an error is thrown:
At this point, enter net start MSDTC on the command line to enable the service.
If you still can't start MSDTC normally, consult MSDN (http://msdn.microsoft.com/zh-cn/library/aa561924%28BTS.10%29.aspx) for help.
Four, the distributed query
At this point, the result of our query in any instance is consistent and exactly what we want.
[SQL]View PlainCopy
- /*************** instance ap4\net2013 (SQL Server 2008) *********/
- /********* [email protected] invited the month ***************/
- /***** Distributed Query **************/
- Query on----ap4\net2013
- --Open the test library
- Use RemoteViewTest2012
- GO
- SET xact_abort on
- SELECT Webhitid, WebSite, HITDT
- From Dbo.v_webhits
- SELECT Webhitid, WebSite, HITDT
- from [ap4\net2012]. RemoteViewTest2012.dbo.WebHits_ShangHai
[SQL]View PlainCopy
- Query on ap4\n ET2012----
- --Open the test library
- Use RemoteViewTest2012
- GO
- SET xact_abort on
- SELECT Webhitid, WebSite, HITDT
- From Dbo.v_webhits
- SELECT Webhitid, WebSite, HITDT
- from [ap4\net2013]. RemoteViewTest2012.dbo.WebHits_BeiJing
Apply distributed partitioned Views in SQL Server 2005/2008/2012