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:

if object_id('dbo.Test1') is not null
drop table dbo.Test1
go
Create table Test1 (id int not null , name varchar(10), partKey tinyint not null check (partKey=1), primary key (partKey,id));
if object_id('dbo.Test2') is not null
drop table dbo.Test2
go
Create table Test2 (id int not null , name varchar(10), partKey tinyint not null check (partKey=2), primary key (partKey,id));
go
if object_id('dbo.[TestView]') is not null
drop view dbo.[TestView]
go
create view [dbo].[TestView]
as
select [id],[name],[partKey]
from [dbo].[Test1]
union all
select [id],[name],[partKey]
from [dbo].[Test2];
go
/*insert values into partition view */
insert into TestView (id, name,partKey) values (777,'Test1',1);
insert into TestView (id,name,partKey) values (777,'Test2',2);/*update values int partition view */
go
select * from TestView
go
update TestView set id = 7 where id = 777 and partKey = 1
go
select * from TestView
go

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:

if object_id('dbo.Test1') is not null
drop table dbo.Test1
go
Create table Test1 (id int not null identity(1,1), name varchar(100), partKey tinyint not null check (partKey=1), primary key (partKey,id));if object_id('dbo.Test2') is not null

drop table dbo.Test2

go
Create table Test2 (id int not null identity(1,1), name varchar(100), partKey tinyint not null check (partKey=2), primary key (partKey,id));
go

if object_id('dbo.[TestView]') is not null
drop view dbo.[TestView]
go
create view [dbo].[TestView]
as
select [id],[name],[partKey]
from [dbo].[Test1]
union all
select [id],[name],[partKey]
from [dbo].[Test2];
go
/*insert values into partition view */
insert into TestView (name,partKey) values ('Test1',1);
insert into TestView (name,partKey) values ('Test2',2);

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:

if object_id('dbo.[Pdb_IVW_dbo_<wbr />TestView]') is not null
drop trigger dbo.[Pdb_IVW_dbo_TestView]
go
create trigger [dbo].[Pdb_IVW_dbo_TestView] on [dbo].[TestView]
instead of insert
as
begin
set nocount on; set xact_abort on;
declare @partKeyTable table
( [partKey] bigint
);
insert into @partKeyTable
select [partKey]
from inserted
group by [partKey];
set nocount off;if exists (select top 1 1 from @partKeyTable where [partKey] = 1)
insert into [dbo].[Test1]
([name],[partKey])
select [name],[partKey]
from inserted
where [partKey] = 1;if exists (select top 1 1 from @partKeyTable where [partKey] = 2)
insert into [dbo].[Test2]
([name],[partKey])
select [name],[partKey]
from inserted
where [partKey] = 2;set nocount on;
delete from @partKeyTable;
set nocount off; set xact_abort off;
end;
go

This trigger let us 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):

/*In this example we create a testDB
If you wish to avoid creating a new database, skip on the create database lines
*/
Use Master;
Exec PdbLogic..PdbdropDatabase 'TestDISTDB';
Create database TestDISTDB;
go
use TestDISTDB
if object_id('dbo.Test1') is not null
drop table dbo.Test1
go
Create table Test1 (id int not null identity(1,1), name varchar(32), partKey tinyint not null , primary key (partKey,id));
if object_id('dbo.Test2') is not null
drop table dbo.Test2
go
Create table Test2 (id int not null identity(1,1), name varchar(32), partKey tinyint not null , primary key (partKey,id));
go
if object_id('dbo.TestView') is not null
drop view dbo.TestView
go
/*add table test1 into partition TestView */
exec PdbLogic..PdbsetDistributedObject @SourceDatabaseName='TestDISTDB', --the database that will contain the view
@SourceObjectName='Test1' ,@DistributedColumnName='partKey' ,@DistributedNumberValue=1 ,@TargetObjectName='TestView';/*add table test2 into partition TestView */
exec PdbLogic..PdbsetDistributedObject @SourceDatabaseName='TestDISTDB', --the database that will contain the view
@SourceObjectName='Test2' ,@DistributedColumnName='partKey' ,@DistributedNumberValue=2 ,@TargetObjectName='TestView' ;/*To add more tables into TestView, just run this command with the relevant params *//*Now let's run the commands*//*insert values into partition view */
insert into TestView (name,partKey) values ('Test1',1);
insert into TestView (name,partKey) values ('Test2',2);
select * from TestView/*update values int partition view */
update TestView set name = 'PartitionDBSolution' where name = 'Test1'
select * from TestView
/*
Run the line below to drop TestDISTDB
Exec PdbLogic..PdbdropDatabase 'TestDISTDB';
*/

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.

Distributed Partition View

How can PartitionDB help?

Keep Working On Prem While Expanding to the Cloud

Let’s Drive Hybrid

Database Profiling – Everything You Need and Only What You Need

The Quickest Way to Data Aggregation

Switch Databases With One Command

One Comment

Leave A Comment Cancel reply

Articles

Categories

By clicking join now, you agree to our End User License Agreement

Partition View’s Hidden Secrets

Distributed Partition View

How can PartitionDB help?

Share This Story, Choose Your Platform!

Related Posts

Keep Working On Prem While Expanding to the Cloud

Let’s Drive Hybrid

Database Profiling – Everything You Need and Only What You Need

The Quickest Way to Data Aggregation

Switch Databases With One Command

One Comment

Leave A Comment Cancel reply

Articles

Categories