Very Computer

The query optimizer fails to recognize partitioning function involving
modulus function. For example a partitioned view over tables partitioned as
follows is not optimized correctly:

CREATE TABLE Table_0 (
 ID int NOT NULL CHECK (ID%10 = 0),
 FirstName varchar (60) NOT NULL ,
 LastName varchar (60) NOT NULL ,
 GenderID tinyint NOT NULL ,
 CONSTRAINT xPKTable_0 PRIMARY KEY  CLUSTERED (ID)
)

But does work correctly with this

CREATE TABLE Table_0 (
 ID int NOT NULL CHECK (ID between 0 and 10000),
 FirstName varchar (60) NOT NULL ,
 LastName varchar (60) NOT NULL ,
 GenderID tinyint NOT NULL ,
 CONSTRAINT xPKTable_0 PRIMARY KEY  CLUSTERED (ID)
)

When the partitioned view is build over tables using the modulus function,
the optimizer will access each table in the view to satisfy a SELECT query
(select * from Table_View where ID = 25). When the view is built over the
BETWEEN comparison, the optimizer will only reference the relevant table.
This can be seen by setting statistics io on.

Is this a bug? I see nothing in BOL that indicates that the first check
constraint should not work. Is there a way to make this work? Having a
dynamic partitioning function is much more flexible that hard-coded ranges.
All the examples I have seen have been hard-coded, typically to a date
range.

Thanks,
Seth

It's not a bug, see below (from books on-line):

Partitioning Column Rules
A partitioning column exists on each member table and,
through CHECK constraints, identifies the data available
in that specific table. Partitioning columns must adhere
to these rules:

Each base table has a partitioning column whose key values
are enforced by CHECK constraints. The key ranges of the
CHECK constraints in each table do not overlap with the
ranges of any other table. Any given value of the
partitioning column must map to only one table. The CHECK
constraints can only use these operators: BETWEEN, AND,
OR, <, <=, >, >=, =.

Quote:>-----Original Message-----
>The query optimizer fails to recognize partitioning
function involving
>modulus function. For example a partitioned view over

tables partitioned as

Quote:>follows is not optimized correctly:

>CREATE TABLE Table_0 (
> ID int NOT NULL CHECK (ID%10 = 0),
> FirstName varchar (60) NOT NULL ,
> LastName varchar (60) NOT NULL ,
> GenderID tinyint NOT NULL ,
> CONSTRAINT xPKTable_0 PRIMARY KEY  CLUSTERED (ID)
>)

>But does work correctly with this

>CREATE TABLE Table_0 (
> ID int NOT NULL CHECK (ID between 0 and 10000),
> FirstName varchar (60) NOT NULL ,
> LastName varchar (60) NOT NULL ,
> GenderID tinyint NOT NULL ,
> CONSTRAINT xPKTable_0 PRIMARY KEY  CLUSTERED (ID)
>)

>When the partitioned view is build over tables using the
modulus function,
>the optimizer will access each table in the view to

satisfy a SELECT query

Quote:>(select * from Table_View where ID = 25). When the view
is built over the
>BETWEEN comparison, the optimizer will only reference the
relevant table.
>This can be seen by setting statistics io on.

>Is this a bug? I see nothing in BOL that indicates that
the first check
>constraint should not work. Is there a way to make this
work? Having a
>dynamic partitioning function is much more flexible that
hard-coded ranges.
>All the examples I have seen have been hard-coded,
typically to a date
>range.

>Thanks,
>Seth

>.

I did read the restrictions. But I am using the = operator, which is
permitted. Also, BOL has examples using dates with the datepart function. So
what would be different about using the modulus function with an integer?

I used CHECK (ID%10 = 0)
BOL shows CHECK (DATEPART(yy, OrderDate) = 1998)

Why should the query optimizer recognize the partitioning scheme on the
latter but not the former?

Thanks,
Seth
=======================================

It's not a bug, see below (from books on-line):

Partitioning Column Rules
A partitioning column exists on each member table and,
through CHECK constraints, identifies the data available
in that specific table. Partitioning columns must adhere
to these rules:

Each base table has a partitioning column whose key values
are enforced by CHECK constraints. The key ranges of the
CHECK constraints in each table do not overlap with the
ranges of any other table. Any given value of the
partitioning column must map to only one table. The CHECK
constraints can only use these operators: BETWEEN, AND,
OR, <, <=, >, >=, =.

Quote:>-----Original Message-----
>The query optimizer fails to recognize partitioning
function involving
>modulus function. For example a partitioned view over

tables partitioned as

Quote:>follows is not optimized correctly:

>CREATE TABLE Table_0 (
> ID int NOT NULL CHECK (ID%10 = 0),
> FirstName varchar (60) NOT NULL ,
> LastName varchar (60) NOT NULL ,
> GenderID tinyint NOT NULL ,
> CONSTRAINT xPKTable_0 PRIMARY KEY  CLUSTERED (ID)
>)

>But does work correctly with this

>CREATE TABLE Table_0 (
> ID int NOT NULL CHECK (ID between 0 and 10000),
> FirstName varchar (60) NOT NULL ,
> LastName varchar (60) NOT NULL ,
> GenderID tinyint NOT NULL ,
> CONSTRAINT xPKTable_0 PRIMARY KEY  CLUSTERED (ID)
>)

>When the partitioned view is build over tables using the
modulus function,
>the optimizer will access each table in the view to

satisfy a SELECT query

Quote:>(select * from Table_View where ID = 25). When the view
is built over the
>BETWEEN comparison, the optimizer will only reference the
relevant table.
>This can be seen by setting statistics io on.

>Is this a bug? I see nothing in BOL that indicates that
the first check
>constraint should not work. Is there a way to make this
work? Having a
>dynamic partitioning function is much more flexible that
hard-coded ranges.
>All the examples I have seen have been hard-coded,
typically to a date
>range.

>Thanks,
>Seth

>.