MS SQL Data Warehousing - Partition views vs partition tables

Author

Partition views vs partition tables

jxstern

2005-12-08, 3:23 am

I was just going over the new partitioned tables features in SQL2005.
They are very proud of how you can move a partition from one table to
the other and it's just metadata so it happens quickly and
efficiently.

But then it struck me that with partitioned views as in SQL2000, you
put the data into one table, and you can move that table from one
partitioned view to another with very minimal fuss also.

There are various constraints in the new partitioned tables regarding
keeping indexes aligned with the partitions. The new partition
function is neat, of course.

But again, the bottom line doesn't seem to me all that clear, is there
really something to be gained with the new "partitioned tables" that
wasn't pretty nearly there already with "partitioned views"?

At this point, even putting some new stuff onto SQL2005, I seem very
tempted to use partitioned views.

Thanx.

Josh

David Browne

2005-12-08, 3:23 am

"jxstern" <jxstern@nowhere.xyz> wrote in message
news:h56fp1tjag91lgs
ahru5r43erv8tfc42hk@
4ax.com...
>I was just going over the new partitioned tables features in SQL2005.
> They are very proud of how you can move a partition from one table to
> the other and it's just metadata so it happens quickly and
> efficiently.
>
> But then it struck me that with partitioned views as in SQL2000, you
> put the data into one table, and you can move that table from one
> partitioned view to another with very minimal fuss also.
>
> There are various constraints in the new partitioned tables regarding
> keeping indexes aligned with the partitions. The new partition
> function is neat, of course.
>
> But again, the bottom line doesn't seem to me all that clear, is there
> really something to be gained with the new "partitioned tables" that
> wasn't pretty nearly there already with "partitioned views"?
>
> At this point, even putting some new stuff onto SQL2005, I seem very
> tempted to use partitioned views.
>

Some problems with partitioned views:

The seperation of the data into seperate tables was never perfectly hidden
by the partitioned view. Some transactions could be coded against the
partitioned views, but some still had to be coded against the base tables.
This required poluting application code with complicated
partition-by-partition logic, and made the partitioning scheme impossible to
change.

Partition management required a lot of custom code, and there was not
standard methodology to acomplish it. It required more of a developer than
a DBA to implement and manage it.

The text of the partitioned view definition imposed artifical limitations on
the partitioning scheme.

With partitioned views indexes _had_ to be aligned. With partitioned tables
you can have non-partitioned or differently-partitioned indexes. EG you can
have a global customer index across a partitioned sales table.

Partitioned views could not be referenced by a foreign key.

David

jxstern

2005-12-08, 1:23 pm

On Wed, 7 Dec 2005 21:03:31 -0600, "David Browne" <davidbaxterbrowne
no potted meat@hotmail.com> wrote:

>Some problems with partitioned views:
>
>The seperation of the data into seperate tables was never perfectly hidden
>by the partitioned view. Some transactions could be coded against the
>partitioned views, but some still had to be coded against the base tables.
>This required poluting application code with complicated
>partition-by-partition logic, and made the partitioning scheme impossible to
>change.

Not all views are updatable, I guess.

>Partition management required a lot of custom code, and there was not
>standard methodology to acomplish it. It required more of a developer than
>a DBA to implement and manage it.

Hmm.

>The text of the partitioned view definition imposed artifical limitations on
>the partitioning scheme.
>
>With partitioned views indexes _had_ to be aligned. With partitioned tables
>you can have non-partitioned or differently-partitioned indexes. EG you can
>have a global customer index across a partitioned sales table.

So with views, each partipating table would need a separate customer
index, and I could write a select against the view and (I hope!)
SQLServer would use all the separate indexes ... but, for instance,
you'd have trouble enforcing uniqueness across tables, right?

>Partitioned views could not be referenced by a foreign key.

Right, right, DRI would be tough, wouldn't it?

>
>David

I could generate a few more questions, but this was very helpful,
thanks!

Josh

David Browne

2005-12-08, 8:23 pm

"jxstern" <jxstern@nowhere.xyz> wrote in message
news:h50hp1hr0som02g
joo5s0rtdvgffij0amk@
4ax.com...
> On Wed, 7 Dec 2005 21:03:31 -0600, "David Browne" <davidbaxterbrowne
> no potted meat@hotmail.com> wrote:
>
>
> Not all views are updatable, I guess.
>

The problem extends beyond updates. Some SELECT statements ran so poorly
against the partitioned view that they had to be replaced by cursors, temp
tables and dynamic SQL against the base tables. Very ugly.
.. . .

> So with views, each partipating table would need a separate customer
> index, and I could write a select against the view and (I hope!)
> SQLServer would use all the separate indexes

For simple queries that usually worked, but for more complex queries you
would end up with a lot of table scans.

David

jxstern

2005-12-09, 1:23 pm

On Thu, 8 Dec 2005 20:08:32 -0600, "David Browne" <davidbaxterbrowne
no potted meat@hotmail.com> wrote:
>
>For simple queries that usually worked, but for more complex queries you
>would end up with a lot of table scans.

Well, I hear you (and have heard vague rumors of the sort before), but
this sounds more like a bug than a feature, do we know if it's any
better in 2K5?

Josh

David Browne

2005-12-09, 8:23 pm

"jxstern" <jxstern@nowhere.xyz> wrote in message
news:nhjjp19p6s4lsak
0gge7ut3sqrc6hi4tdf@
4ax.com...
> On Thu, 8 Dec 2005 20:08:32 -0600, "David Browne" <davidbaxterbrowne
> no potted meat@hotmail.com> wrote:
>
> Well, I hear you (and have heard vague rumors of the sort before), but
> this sounds more like a bug than a feature, do we know if it's any
> better in 2K5?
>

2k5 added table partitioning to replace local partitioned views, so I don't
imagine much work was done on them.

From BOL
Local partitioned views are included in SQL Server 2005 for backward
compatibility purposes only, and are in the process of being deprecated. The
preferred method for partitioning data locally is through partitioned
tables. For more information, see "Partitioned Tables and Indexes".

David

jxstern

2005-12-09, 8:23 pm

On Fri, 9 Dec 2005 17:41:34 -0600, "David Browne" <davidbaxterbrowne
no potted meat@hotmail.com> wrote:
>2k5 added table partitioning to replace local partitioned views, so I don't
>imagine much work was done on them.
>
>From BOL
>Local partitioned views are included in SQL Server 2005 for backward
>compatibility purposes only, and are in the process of being deprecated. The
>preferred method for partitioning data locally is through partitioned
>tables. For more information, see "Partitioned Tables and Indexes".

Thanks again.

Josh

Sponsored Links