Apply distributed partitioned Views in SQL Server 2005/2008/2012

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

2. /********* Create a distributed partitioned view ***************/
4. /********* [email protected] invited the month ***************/
8. Use master
10. GO
12. EXEC sp_addlinkedserver
14. ' ap4\net2013 ',
16. N' sql Server '
18. GO
22. --Skip the check of the remote instance schema table to improve performance and invite monthly bets
24. EXEC sp_serveroption ' ap4\net2013 ', ' lazy schema validation ', ' true '
26. GO
30. --Create a test database
32. IF not EXISTS (SELECT name
34. From sys.databases
36. WHERE name = ' RemoteViewTest2012 ')
38. BEGIN
40. CREATE DATABASE RemoteViewTest2012
42. END
44. GO
46. --Open the test library
48. Use RemoteViewTest2012
50. GO
54. --Create a click table for Shanghai Branch
56. CREATE TABLE dbo. Webhits_shanghai
58. (Webhitid uniqueidentifier not NULL,
60. WebSite varchar () not NULL,
62. HITDT datetime not NULL,
64. CHECK (WebSite = ' Shanghai '),
66. CONSTRAINT pk_webhits PRIMARY KEY (Webhitid, WebSite))

A second example:

[SQL]View PlainCopy

2. /*************** instance ap4\net2013 (SQL Server 2008) *********/
4. /********* [email protected] invited the month ***************/
6. Use master
8. GO
10. EXEC sp_addlinkedserver
12. ' ap4\net2012 ',
14. N' sql Server '
16. GO
20. --Skip the check of the remote instance schema table to improve performance and invite monthly bets
22. EXEC sp_serveroption ' ap4\net2012 ', ' lazy schema validation ', ' true '
24. GO
28. IF not EXISTS (SELECT name
30. From sys.databases
32. WHERE name = ' RemoteViewTest2012 ')
34. BEGIN
36. CREATE DATABASE RemoteViewTest2012
38. END
40. GO
44. --Open the test library
46. Use RemoteViewTest2012
48. GO
52. --Create a click table for Beijing Branch
54. CREATE TABLE dbo. Webhits_beijing
56. (Webhitid uniqueidentifier not NULL,
58. WebSite varchar () not NULL,
60. HITDT datetime not NULL,
62. CHECK (WebSite = ' Beijing '),
64. CONSTRAINT pk_webhits PRIMARY KEY (Webhitid, WebSite))

II. Create a view in two instances, respectively

[SQL]View PlainCopy

2. /*************** instance ap4\net2012 (SQL Server 2008) *********/
4. /********* [email protected] invited the month ***************/
8. --Open the test library
10. Use RemoteViewTest2012
12. GO
16. --Create a partitioned view
18. CREATE VIEW dbo.v_webhits as
20. SELECT Webhitid,
22. WebSite,
24. Hitdt
26. From RemoteViewTest2012.dbo.WebHits_ShangHai
28. UNION All
30. SELECT Webhitid,
32. WebSite,
34. Hitdt
36. from [ap4\net2013]. RemoteViewTest2012.dbo.WebHits_BeiJing
38. GO
42. /*************** instance ap4\net2013 (SQL Server 2008) *********/
44. /********* [email protected] invited the month ***************/
48. --Open the test library
50. Use RemoteViewTest2012
52. GO
56. --Create a partitioned view
58. CREATE VIEW dbo.v_webhits as
60. SELECT Webhitid,
62. WebSite,
64. Hitdt
66. From RemoteViewTest2012.dbo.WebHits_BeiJing
68. UNION All
70. SELECT Webhitid,
72. WebSite,
74. Hitdt
76. from [ap4\net2012]. RemoteViewTest2012.dbo.WebHits_ShangHai
78. GO

Iii. inserting test data

We can select any one of the instances to insert, below we choose ap4\net2013

[SQL]View PlainCopy

2. /*************** instance ap4\net2013 (SQL Server 2008) *********/
4. /********* [email protected] invited the month ***************/
6. ----to ensure the insertion, you must turn on the XACT_ABORT switch and open the Distributed Transaction Coordinator to invite the monthly note
10. --Open the test library
12. Use RemoteViewTest2012
14. GO
18. SET xact_abort on
20. INSERT dbo.v_webhits
22. (Webhitid, WebSite, HITDT)
24. VALUES (NEWID (), ' Shanghai ', GETDATE ())
28. INSERT dbo.v_webhits
30. (Webhitid, WebSite, HITDT)
32. 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

2. /*************** instance ap4\net2013 (SQL Server 2008) *********/
4. /********* [email protected] invited the month ***************/
6. /***** Distributed Query **************/
10. Query on----ap4\net2013
12. --Open the test library
14. Use RemoteViewTest2012
16. GO
18. SET xact_abort on
22. SELECT Webhitid, WebSite, HITDT
24. From Dbo.v_webhits
28. SELECT Webhitid, WebSite, HITDT
30. from [ap4\net2012]. RemoteViewTest2012.dbo.WebHits_ShangHai

[SQL]View PlainCopy

1. Query on ap4\n ET2012----
2. --Open the test library
3. Use RemoteViewTest2012
4. GO
6. SET xact_abort on
8. SELECT Webhitid, WebSite, HITDT
9. From Dbo.v_webhits
11. SELECT Webhitid, WebSite, HITDT
12. from [ap4\net2013]. RemoteViewTest2012.dbo.WebHits_BeiJing

Apply distributed partitioned Views in SQL Server 2005/2008/2012