This post will guide you how to use the powerful object of Distributed Partition View (aka DPV). DPV can solve many issues dealing with multiple tables management.

After understanding the steps to creating the DPV we will introduce you with a simple, hassle free alternative, stay tuned…

Distributed Partition View

Distribute Partition View is a unique database object. It gives the ability to partition your tables by giving a smart view layer on several tables instead of splitting the table into partitions.

The smart view knows how to optimize access to the designated tables, unlike a regular view that sorts all the data into memory (with minor query rewrites). DPV allows you to define different indexes to each table and the optimizer knows how to perfectly use those indexes. It is also proven that DPV works far better than table partitioning on large data queries on a single partition.

Here we have, simple example of partition view:

In this example two tables are created Test1 and Test2. A view, TestView is created, pointing to records from both tables. This configuration allows to insert records into the tables, via TestView.

This works well, but what if we need to use identity, time stamp etc. Let’s try adding an identity column to the tables:

As we can see, the partition view works but insert is failing giving us the following error:
Cannot INSERT into partitioned view ‘dbo.TestView’ because table ‘[dbo].[Test1]’ has an IDENTITY constraint.
To solve this problem we can place well-tuned triggers, here is how to do it:
 This trigger enables us to insert records to TestView and it takes care of the identity for us. Let’s try to insert again:
insert into TestView (name,partKey) values ('Test1',1);
insert into TestView (name,partKey) values ('Test2',2);
go
select * from TestView
go
update TestView set name = 'test1 new name' where id = 1 and partKey = 1
go
select * from TestView
go
And Taddam…

Actually, using trigger makes all the atomic operations (insert, update and delete) feasible. But if we need to work on multiples servers, authentication problems arise. Dealing with authentication problems is a complex task.

How can PartitionDB help?

PartitionDB SDK provides you with a smart partitioning mechanism, that will take care of everything for you. In order to run the example below you need to download PdbLogic  and attach it to your database, and there you go.

For Example, to achieve partition view abilities, you just need to configure your wishes in a simple way as below (PdbLogic should be downloaded and attached before running this example):

PdbsetDistributedObject adds tables to a view. In our example, it adds Test1 table and Test2 table to TestView. More tables can be added. Once it is done, you can add new records to the view, or update existing records. You don’t need to create triggers, or worry about identities.

Good Luck

For your reference, here is a list of optional configuration parameters:

Parameter
TargetDatabaseName Name of the target database that contain the table
TargetLinkedServerName Name of the linked server of the table (database)
TargetSchemaName Schema name of the table
SourceObjectName Name of the table that will be part of the partition view
DistributedDatabaseName Database name that contains the view
DistributedLinkedServerName Linked server name that contains the view (database)
DistributedSchemaName Schema name of the view
TargetObjectName View name
DistributedColumnName The column which will be the partition column
DistributedStringValue The string value of the partition (in case of range it will be the minimun value)
DistributedNumberValue The value (tinyint, smallint, int, bigint) of the partition (in case of range it will be the minimum value)
DistributedDecimalValue The value (decimal, flaut, real, numeric) of the partition (in case of range it will be the minimum value)
DistributedDateValue The value (date, smalldatetime, datetime) of the partition (in case of range it will be the minimum value)
DistributedRangeStringValue The maximum string value of the partition (in case of range it will be the minimum value)
DistributedRangeNumberValue The maximum value (tinyint, smallint, int, bigint) of the partition (in case of range it will be the minimum value)
DistributedRangeDecimalValue The maximum value (decimal, flaut, real, numeric) of the partition (in case of range it will be the minimum value)
DistributedRangeDateValue The maximum value (date, smalldatetime, datetime) of the partition (in case of it will be the minimum value)
IsOutputScript Decide if print the command or run it

To use this partitioning option and other PartitionDB utilities, download our PdbLogic and attach to your database. .
To learn more about PartitionDB capabilities check out our blog.