Is it a "Partitioned View"?

Hello. Say you have a view and you want to know if it's
recognized by SQL Server as a local "Partitioned View".
Is there any way to see that, short of running queries to
see that it's scanning ALL table's indexes in the view,
not just the one with the specific WHERE clause criteria
you requested?
How do you know a view is treated as partitioned by SQL
Server (7 or 2000)? THanks, BruceBruce
Script out the view, it should be fairly obvious looking
at it if it is a partitioned view. I have not got 2000 bol
where I am at the moment, but in 7 you will see multiple
tables joined using union all, I would think 2000 would be
the same.
I hope that is what you meant.
Regards
John|||John, it's not that straight-forward. I was hoping
there's some SQL function (like an IsPartitionedView) that
just says that SQL Server THINKS this is a partitioned
view. I have a view and it's not being treated by SQL as
partitioned, in as far as using the separate table's
indexes, it's table scanning EVERY table's index. The
Tales have the correct CHECK constraint, and all the rules
APPEAR to be followed ok, so I was hoping there was a way
to see why SQL Server is just treating this view as a
regular UNION'd set of tables, not as a "partitioned"
view.
Scripting out the view doesn't show you the table
attributes, like the NULL PADDING setting, or the
constraints, etc...
There's probably no way to see this I'm guessing, but
thought I'd ask... THanks, Bruce
>--Original Message--
>Bruce
>Script out the view, it should be fairly obvious looking
>at it if it is a partitioned view. I have not got 2000
bol
>where I am at the moment, but in 7 you will see multiple
>tables joined using union all, I would think 2000 would
be
>the same.
>I hope that is what you meant.
>Regards
>John
>.
>|||There is no intrinsic property, it's purely a matter of query optimization.
You have to view the query plan and/or IO statistics to determine if SQL is
appropriately pruning table partitions from the query plan.
SET STATISTICS IO ON
Look for scan counts of zero on the non-qualifying tables.
Also, I've found that it is necessary to add an index on the partitioning
column(s), as non-intuititve as that seems, it helps the optimizer in
certain cases eliminate partitions
.
--
Kevin Connell, MCDBA
----
The views expressed here are my own
and not of my employer.
----
"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
news:04bb01c376c9$573808e0$a001280a@.phx.gbl...
> John, it's not that straight-forward. I was hoping
> there's some SQL function (like an IsPartitionedView) that
> just says that SQL Server THINKS this is a partitioned
> view. I have a view and it's not being treated by SQL as
> partitioned, in as far as using the separate table's
> indexes, it's table scanning EVERY table's index. The
> Tales have the correct CHECK constraint, and all the rules
> APPEAR to be followed ok, so I was hoping there was a way
> to see why SQL Server is just treating this view as a
> regular UNION'd set of tables, not as a "partitioned"
> view.
> Scripting out the view doesn't show you the table
> attributes, like the NULL PADDING setting, or the
> constraints, etc...
> There's probably no way to see this I'm guessing, but
> thought I'd ask... THanks, Bruce
>
>
> >--Original Message--
> >Bruce
> >
> >Script out the view, it should be fairly obvious looking
> >at it if it is a partitioned view. I have not got 2000
> bol
> >where I am at the moment, but in 7 you will see multiple
> >tables joined using union all, I would think 2000 would
> be
> >the same.
> >
> >I hope that is what you meant.
> >
> >Regards
> >
> >John
> >.
> >|||Kevin, yes, adding the new index on the partition column
did the trick, thanks! It seems strange I need to add an
index on a column where every value is the exact same for
that table. The partition column was the middle column of
a 3 col PK. I had also tried changing the order of the
PK, so the parition column was col #1. But I didn't see
that helped, but adding a new index ONLY on the partition
column DID work, interesting! THanks a lot... Bruce
>--Original Message--
>There is no intrinsic property, it's purely a matter of
query optimization.
>You have to view the query plan and/or IO statistics to
determine if SQL is
>appropriately pruning table partitions from the query
plan.
>SET STATISTICS IO ON
>Look for scan counts of zero on the non-qualifying tables.
>Also, I've found that it is necessary to add an index on
the partitioning
>column(s), as non-intuititve as that seems, it helps the
optimizer in
>certain cases eliminate partitions
>..
>--
>Kevin Connell, MCDBA
>----
>The views expressed here are my own
>and not of my employer.
>----
>"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
>news:04bb01c376c9$573808e0$a001280a@.phx.gbl...
>> John, it's not that straight-forward. I was hoping
>> there's some SQL function (like an IsPartitionedView)
that
>> just says that SQL Server THINKS this is a partitioned
>> view. I have a view and it's not being treated by SQL
as
>> partitioned, in as far as using the separate table's
>> indexes, it's table scanning EVERY table's index. The
>> Tales have the correct CHECK constraint, and all the
rules
>> APPEAR to be followed ok, so I was hoping there was a
way
>> to see why SQL Server is just treating this view as a
>> regular UNION'd set of tables, not as a "partitioned"
>> view.
>> Scripting out the view doesn't show you the table
>> attributes, like the NULL PADDING setting, or the
>> constraints, etc...
>> There's probably no way to see this I'm guessing, but
>> thought I'd ask... THanks, Bruce
>>
>>
>> >--Original Message--
>> >Bruce
>> >
>> >Script out the view, it should be fairly obvious
looking
>> >at it if it is a partitioned view. I have not got 2000
>> bol
>> >where I am at the moment, but in 7 you will see
multiple
>> >tables joined using union all, I would think 2000 would
>> be
>> >the same.
>> >
>> >I hope that is what you meant.
>> >
>> >Regards
>> >
>> >John
>> >.
>> >
>
>.
>