Distributed Partition views

Distributed Partition views are used to access data that has been horizontally splits or partitioned across multiple tables. Table objects can be located in different database or even separated across multiple servers.

We combine table by partition view by using UNION ALL statements that causes data on separate table to appear as if they were on one table.

There are several restrictions to create this views mentioned bellow.

1.  All the columns of the members table should be included in the views.

2.  The columns are in same ordinal positions in the SELECT statement and have the same 

      data type.

3.  The same columns cannot be used multiple times.

4.  The partition columns cannot be computed, identity, or a time stamp columns.

5. The data value of the partition columns cannot be overlap in the underlying table.

6.  The partition columns must be the members of the primary key of the members table.

7.  Members table of the partition views need CHECK constraint of the partition columns.

8.  There is no index of computed columns of the members table.

9.  The primary key columns of the members table should have the same.

10. The entire member table should have the same ANSI PADDING.

If you are creating a distributed partitioned view, each member table is on a separate member server. For the greatest location transparency, the name of the member databases should be the same on each member server, although this is not a requirement.

Adding linked server definitions on each member server containing the connection information needed to execute distributed queries on the other member servers. This gives a distributed partitioned view access to data on the other servers.

For example, you are partitioning a Customer table into three tables. The CHECK constraint for these tables is:

-- On Server1:

CREATE TABLE Tabl-A

  (CustomerID INT PRIMARY KEY CHECK (CustomerID BETWEEN 1 AND 32999),

  ... -- Additional column definitions)

-- On Server2:

CREATE TABLE Tabl-B

  (CustomerID INTE PRIMARY KEY CHECK (CustomerID BETWEEN 33000 AND 65999),

  ... -- Additional column definitions)

-- On Server3:

CREATE TABLE Tabl-C

  (CustomerID INT PRIMARY KEY CHECK (CustomerID BETWEEN 66000 AND 99999),

  ... -- Additional column definitions)

To create distributed partitioned views for the preceding example, you must:

    1.  Add a linked-server definition named Server2 with the connection information for  

        Server2,  and a linked server definition named Server3 for access to Server3

     2. Create this distributed partitioned view:

CREATE VIEW Customers

AS

SELECT * FROM CompanyDatabase.TableOwner.Tabl-A

UNION ALL

SELECT * FROM Server2.CompanyDatabase.TableOwner.Tabl-B

UNION ALL

SELECT * FROM Server3.CompanyDatabase.TableOwner.Tabl-C




3.  Perform the same steps on Server2 and Server3.

Updating Distributed Partition View

If a local or distributed partitioned view is not updatable, it can serve only as a read-only copy of the original table. An updatable partitioned view can exhibit all the capabilities of the original table.

A view is considered an updatable partitioned view if:

The view is a set of SELECT statements whose individual result sets are combined into one using the UNION ALL statement. Each individual SELECT statement references one SQL Server base table. The table can be either a local table or a linked table referenced using a four-part name, the OPENROWSET function, or the OPENDATASOURCE function. The view will not be updatable if a trigger or cascading update or delete is defined on one or more member tables.

Hope you like that.

Posted by: MR. JOYDEEP DAS