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

Source: Internet
Author: User
Tags getdate

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
  1. /********* Create a distributed partitioned view ***************/
  2. /********* [email protected] invited the month ***************/
  3. Use master
  4. GO
  5. EXEC sp_addlinkedserver
  6. ' ap4\net2013 ',
  7. N' sql Server '
  8. GO
  9. --Skip the check of the remote instance schema table to improve performance and invite monthly bets
  10. EXEC sp_serveroption ' ap4\net2013 ', ' lazy schema validation ', ' true '
  11. GO
  12. --Create a test database
  13. IF not EXISTS (SELECT name
  14. From sys.databases
  15. WHERE name = ' RemoteViewTest2012 ')
  16. BEGIN
  17. CREATE DATABASE RemoteViewTest2012
  18. END
  19. GO
  20. --Open the test library
  21. Use RemoteViewTest2012
  22. GO
  23. --Create a click table for Shanghai Branch
  24. CREATE TABLE dbo. Webhits_shanghai
  25. (Webhitid uniqueidentifier not NULL,
  26. WebSite varchar () not NULL,
  27. HITDT datetime not NULL,
  28. CHECK (WebSite = ' Shanghai '),
  29. CONSTRAINT pk_webhits PRIMARY KEY (Webhitid, WebSite))

A second example:

[SQL]View PlainCopy
  1. /*************** instance ap4\net2013 (SQL Server 2008) *********/
  2. /********* [email protected] invited the month ***************/
  3. Use master
  4. GO
  5. EXEC sp_addlinkedserver
  6. ' ap4\net2012 ',
  7. N' sql Server '
  8. GO
  9. --Skip the check of the remote instance schema table to improve performance and invite monthly bets
  10. EXEC sp_serveroption ' ap4\net2012 ', ' lazy schema validation ', ' true '
  11. GO
  12. IF not EXISTS (SELECT name
  13. From sys.databases
  14. WHERE name = ' RemoteViewTest2012 ')
  15. BEGIN
  16. CREATE DATABASE RemoteViewTest2012
  17. END
  18. GO
  19. --Open the test library
  20. Use RemoteViewTest2012
  21. GO
  22. --Create a click table for Beijing Branch
  23. CREATE TABLE dbo. Webhits_beijing
  24. (Webhitid uniqueidentifier not NULL,
  25. WebSite varchar () not NULL,
  26. HITDT datetime not NULL,
  27. CHECK (WebSite = ' Beijing '),
  28. CONSTRAINT pk_webhits PRIMARY KEY (Webhitid, WebSite))

II. Create a view in two instances, respectively

[SQL]View PlainCopy
  1. /*************** instance ap4\net2012 (SQL Server 2008) *********/
  2. /********* [email protected] invited the month ***************/
  3. --Open the test library
  4. Use RemoteViewTest2012
  5. GO
  6. --Create a partitioned view
  7. CREATE VIEW dbo.v_webhits as
  8. SELECT Webhitid,
  9. WebSite,
  10. Hitdt
  11. From RemoteViewTest2012.dbo.WebHits_ShangHai
  12. UNION All
  13. SELECT Webhitid,
  14. WebSite,
  15. Hitdt
  16. from [ap4\net2013]. RemoteViewTest2012.dbo.WebHits_BeiJing
  17. GO
  18. /*************** instance ap4\net2013 (SQL Server 2008) *********/
  19. /********* [email protected] invited the month ***************/
  20. --Open the test library
  21. Use RemoteViewTest2012
  22. GO
  23. --Create a partitioned view
  24. CREATE VIEW dbo.v_webhits as
  25. SELECT Webhitid,
  26. WebSite,
  27. Hitdt
  28. From RemoteViewTest2012.dbo.WebHits_BeiJing
  29. UNION All
  30. SELECT Webhitid,
  31. WebSite,
  32. Hitdt
  33. from [ap4\net2012]. RemoteViewTest2012.dbo.WebHits_ShangHai
  34. GO

Iii. inserting test data

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

[SQL]View PlainCopy
  1. /*************** instance ap4\net2013 (SQL Server 2008) *********/
  2. /********* [email protected] invited the month ***************/
  3. ----to ensure the insertion, you must turn on the XACT_ABORT switch and open the Distributed Transaction Coordinator to invite the monthly note
  4. --Open the test library
  5. Use RemoteViewTest2012
  6. GO
  7. SET xact_abort on
  8. INSERT dbo.v_webhits
  9. (Webhitid, WebSite, HITDT)
  10. VALUES (NEWID (), ' Shanghai ', GETDATE ())
  11. INSERT dbo.v_webhits
  12. (Webhitid, WebSite, HITDT)
  13. 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
  1. /*************** instance ap4\net2013 (SQL Server 2008) *********/
  2. /********* [email protected] invited the month ***************/
  3. /***** Distributed Query **************/
  4. Query on----ap4\net2013
  5. --Open the test library
  6. Use RemoteViewTest2012
  7. GO
  8. SET xact_abort on
  9. SELECT Webhitid, WebSite, HITDT
  10. From Dbo.v_webhits
  11. SELECT Webhitid, WebSite, HITDT
  12. 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
    5. SET xact_abort on
    6. SELECT Webhitid, WebSite, HITDT
    7. From Dbo.v_webhits
    8. SELECT Webhitid, WebSite, HITDT
    9. from [ap4\net2013]. RemoteViewTest2012.dbo.WebHits_BeiJing

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.